DUE FRIDAY 12 NOON
THIS ASSIGNMENT COMES FROM MICROSOFT OFFICE EXCEL 2007
Independent Challenge 1 follow a-g do not miss a step; HARDWARE PAYMENT MODEL
Independent Challenge 2 follow a-e do not miss a step; CAPITAL LOAN PAYMENT MODEL
Advanced Challenge Exercise follow all bullet points
Independent Challenge 3 follow a-h do not miss a step; VEHICLE PURCHASE
Notes are there to help with the assignment; PLEASE REVIEW before you say you can do the assignment
IF YOU CAN NOT DO THIS ASSIGNMENT PLEASE LET ME KNOW ASAP
Projected Sales
2011 Projected Sales | ||||||||||||||
Jan | Feb | Mar | Apr | May | Jun | Total | Percent of Total Sales | |||||||
U.S. | $75,598 | $53,876 | $55,722 | $51,706 | $85,063 | $75,409 | $397,374 | 30.32% | ||||||
Canada | $53,776 | $59,774 | $63,012 | $58,970 | $56,211 | $57,346 | $349,089 | 26.64% | ||||||
U.K. | $50,722 | $54,344 | $53,092 | $60,096 | $51,398 | $34,987 | $304,639 | 23.25% | ||||||
Australia | $30,449 | $45,998 | $50,870 | $49,346 | $38,734 | $43,987 | $259,384 | 19.79% | ||||||
$210,545 | $213,992 | $222,696 | $220,118 | $231,406 | $211,729 | $1,310,486 | ||||||||
16.07% | 16.33% | 16.99% | 16.80% | 17.66% | 16.16% |
Budgets
2011 Budgets | |||||||
Communications | Equipment | Advertising | Salaries | Rent | Entertainment | Travel | |
$62,863 | $18,456 | $371,706 | $65,409 | $81,457 | $61,946 | $717,559 | |
$68,765 | $19,451 | $43,012 | $388,970 | $55,346 | $79,435 | $59,834 | $714,813 |
$69,522 | $14,567 | $43,092 | $370,096 | $59,987 | $74,579 | $57,345 | $689,188 |
$68,489 | $10,435 | $379,346 | $61,987 | $68,934 | $50,123 | $690,184 | |
$2,811,744 | |||||||
Constraints | |||||||
Total region budget | 750,000 | ||||||
Minimum travel budget | 80,000 | ||||||
Minimum entertainment budget | 93,000 | ||||||
Total budget | 3,000,000 |
Sheet3
Lawn Mower Repair
Lawn Mower Repair Model | ||
Labor cost per hour | $85.00 | |
Parts cost per job | $70.00 | |
Hours per job | 2.00 | |
Cost to complete job: | $240.00 |
Vehicle Repair
Rear Brake Pad Replacement Model | |||
Van | Sedan | Compact | |
Labor Cost Per Repair | $110.00 | $100.00 | |
Parts Cost Per Repair | $175.00 | $130.00 | |
Repairs Scheduled | 25 | 35 | 15 |
Labor Cost Per Model | $2,750.00 | $3,500.00 | $1,275.00 |
Parts Cost Per Model | $4,375.00 | $4,550.00 | $1,500.00 |
Total Repair Cost Per Model | $7,125.00 | $8,050.00 | $2,775.00 |
Total Labor Cost | $7,525.00 | ||
Total Parts Cost | $10,425.00 | ||
Total Repair Costs | $17,950.00 |
Sheet3
Loan
Computer Loan Model | |
Loan Amount | $ 100,000 |
Annual Interest Rate | 6.00% |
Term in Months | 48 |
Monthly Payment: | $ 2,348.50 |
Total Payments: | $ 112,728.14 |
Total Interest: | $ 12,728.14 |
Sheet2
Sheet3
Loan
Soft Solutions | |
Loan Amount | €1,000,000.00 |
Annual Interest Rate | 6.00% |
Term in Months | 60 |
Monthly Payment: | €19,332.80 |
Total Payments: | €1,159,968.09 |
Total Interest: | €159,968.09 |
Sheet2
Sheet3
>Vehicle Loan
.75%
6 2 2 2 20 .00
0.00
9 55
2
Poppies
Annual Interest Rate
6
Term in Months
48
Van
Sedan
Compact
Price
$ 30,000
$ 21,000
$ 17,000
Vehicle Capacity
20
10
Quantity to Purchase
Loan Amount
$ 60,000
$ 42,000
$ 34,000
Monthly Payment
$1,429.83
$1,000.88
$810.23
Delivery Capacity
40
12
Total Loan Amount
$ 136,000
Total Monthly Payments
$3,240.94
Total Delivery Capacity
72
Sheet2
Sheet3
Loan
Car Loan Model
Loan Amount
$ 20,000
Annual Interest Rate
6.94%
Term in Months
48
Monthly Payment:
$ 478.37
Total Payments:
$ 22,961.68
Total Interest:
$ 2,961.68
Sheet2
Sheet3
Profit
Custom Bookcases
Hourly labor cost
$70.00
Component cost
$
55
Hours
per UnitComponents
per UnitCost to
ProduceRetail
PriceUnit
ProfitUnits
ProducedTotal
Profit
Model A
8.00
6
$ 8
9
$ 1,695.00
$ 805.00
51
$ 41,055.00
Model B
10.00
$ 1,195.00
$ 2,200.00
$ 1,005.00
73
$ 73,365.00
Model C
15.00
12
$ 1,710.00
$ 2,900.00
$ 1,190.00
67
$ 79,730.00
Model D
17.00
14
$ 1,960.00
$ 3,100.00
$ 1,140.00
$ 62,700.00
Total Profit
$ 194,150.00
Sheet2
Sheet3