Business & Finance dream car assignment

Dream Car Purchase Project You are doing so well in your new career that your boss has just given you a $30,000 (after

tax) bonus! You are sick of taking the bus and have decided to purchase a car. You can’t

decide whether you should buy a new or used car, but you know you will be keeping it for a

minimum of five years. You decide to gather some information on 10 cars to help you make

the decision as to the best car to buy.

1. Research Cars: Your first job is to go online and find 10 cars that interest you. Remember,

you are working with a $50,000 budget so you must not go over this amount.

Create a spreadsheet in Excel that that contains the following information for each car:

✓ Make (For example: Toyota, Nissan, etc.)

✓ Model (Corolla, Rogue, etc.)

✓ Cost

✓ New or Used

✓ If Used, how many km’s

✓ Mileage (how many km’s the car gets per litre. Use city mileage)

✓ Colour

Here is an example of what your completed table might look like:

2. Analyze costs: Choose your five top cars and highlight them in yellow. On a new sheet in

your Excel workbook, you will now do a 5-year analysis of each of the five cars under

consideration. Label your new sheet COST ANALYSIS and include the following:

NAME OF CAR 1 (EX: RED TOYOTA)

Cost of Car

Number of km’s on car when purchased

Number of km’s car will be used every year

Year 1 Year 2 Year 3 YEAR 4 YEAR 5 Total

Gasoline Costs

Insurance

Oil changes

Brakes (if needed)

Tires (if needed)

Car Payment

Total

Here is an example of the table you will need for each of the five cars. (Hint: Consider

making one table, with borders and copy/paste 4 more tables to save time.)

Instructions for filling the data for the above table:

Gas costs: Estimate how many km’s you drive in a year. If you have no idea, let’s assume you

will drive your car 16,000 km’s per year. Use today’s published gas prices. You will need to

look at the first sheet you created from your research to find out the gas mileage.

To calculate gasoline costs the formula will be #of km’s you drive, multiplied by km’s per litre

cost, divided by the car’s km per litre.

Insurance costs: $40 per $1,000 car value. If it’s a sports car, double this number.

To calculate insurance costs, you need to divide cost of car by 1,000 then multiply by 40, or

double if a sports car. Formula: =cost of car/1000*40

Oil changes: You will need to change the oil at every 15,000 km’s costing $120.00 (Hint: For a

new car that will occur in year 3 or a used car you need to evaluate the km’s on the car

when you bought it.)

Brakes: You will need a new set of brakes when the car reaches 35,000 km’s. The cost will be

$600.

Tires: You will need new tires at 40,000 km’s. The cost will be $900.

Car payments: Your bonus is $30,000 cash after taxes. You will need to payments on the

difference between the cost of the cars and $30,000. Calculate the payment at 60 months

(5 years). The interest rate is 1.9% for all cars.

a) Take the cost of the car and subtract the $30,000 cash. (Example $45,595

cost of car minus 30,000 (cash) = $15,595 amount of loan)

b) Then click the function button and choose PMT

c) Below is an example on how to fill out the Dialog Box for PMT for a car that has $15,595

to be financed. Notice the amount to be financed is put in as a negative number

because it is an outflow of cash. The car payment for this car is $273.35 for the month.

You will need to multiple this number by 12 to arrive at the yearly expense.

d) Fill in the totals in your last column and bottom row of each table.

3. Chart the Results: Label sheet three in your Excel workbook as CHARTS. You will begin by

preparing the table on sheet 3, CHARTS. See below for example:

In the first column, you will list the name of your car. In the second column, you will list the

purchase price of the car (from sheet 1) and in the third column, you will list the total five-

year operating cost for each car (from sheet 2). Your final step will be to create a column

chart that compares the cars with their purchase price and total operating costs. See

example below (use actual car names in your version):

4. Make your decision: What car are you going to buy? Using Word, create a one-page

report analyzing your data and explaining your final decision. Include all the tables, charts,

you have created in Excl as well as a picture of your dream car. Make sure you provide

details on why you have made the decision you have. Save the Word file as a PDF and

upload it to the Dropbox on SAM.

Enjoy Your Car!