Auto Purchase
Build a spread sheet that will help you determine which new car to buy. You are specifically interested in determining the annual cost of the car. The factors in determining the cost are:
car payments
fuel costs
maintenance costs
insurance
Your spreadsheet should allow for playing "what if" with the following factors:
years to payoff an interest free loan
number of miles driven per year
cost of regular gasoline
You will allow the following input for each car (see data below):
car name/model
purchase price
MPG of the car
Below are the rules for computing the cost factors.
1) A car whose selling price is above $30,000 uses premium gasoline while all other cars use regular. (Assume that the cost of premium gasoline is $0.15 more than regular.)
2) The cost of maintenance is .0375 cents per mile for the first 10,000 miles driven per year, .045 cents per mile for each mile driven after 10,000. The minimum maintenance cost per year is $300. That is, if the car was not driven at all there would still be a $300 maintenance cost.
3) Insurance costs per year are based on the following table:
Purchase Price Annual Insurance
------------- ----------------
$0 - 14,999 $950
$15,000 - 21,999 $1,100
$22,000 - 34,999 $2,300
$35,000 and above $3,200
4) Assume you will make twelve monthly payments per year to pay for the purchase of the car. The interest rate for the loan is an annualized 7% rate.
Construct a spreadsheet which will compute the four cost factors described (car payments, fuel costs, maintenance costs, insurance) and also display
- the total annual cost for each of the cars under consideration
- the average annual cost for all vehicles.
Assume that your friend will evaluate these 3 vehicles:
|
Car
|
Price
|
MPG
|
|
Camry
|
$25,000
|
30.1
|
|
Audi
|
$50,000
|
18.6
|
|
Porche
|
$100,000
|
10.5
|
Assume
You will drive 12,000 miles per year.
Regular gas costs $1.95 per gallon.
You will pay the car off in 3 years.
In addition be sure:
a) you have an assumptions section of the spreadsheet that isolates the constants of the problem.
b) you use absolute addressing (using the $ when referencing a cell) when appropriate.
c) I should be able to change the data for any car or any assumption and your spreadsheet will still give the correct answer based on the rules I have given for the 4 costs.
c) formatting
1) Round all large amounts (greater than $100) to whole dollars. That is, do not display cents for these values. Also do not display amounts with a $.
2) Make sure the spreadsheet is laid out in an organized, logical manner.
3) Use text wrapping for column headings that are too wide.
4) The spreadsheet should have a title in 20 point font and a blue background that is centered across all the columns that have data.
5) Put a box (borders) around the assumption section.