First, have a look at the example for this lesson titled “Dream Boat.”
In this lesson you are required to do the same presentation in Excel and in PowerPoint.
Assume you are the sales person who has a wealthy client with $3M a year disposable income. The client has asked you to provide information on a particular luxury vehicle including cost, payments, interest, based on a loan period of ten years. You will want to include pictures in both presentations, and at least two of these pictures should be about the car you are marketing.
To create the Excel Worksheet
Set up the column widths and row heights and merge cells similar to the Dream Boat example to suit your needs for the number of pictures (4 to 6) you are going to use. (pages 126, 161).
Use the Format toolbar to set your font size, colour, style; and fill colour for the cell.
Set up the eight row titles as shown in the Dream Boat example (Today’s Price, Loan Period in Years, Loan Period in Months, Annual Interest Rate, Monthly Interest Rate, Monthly Payments, Total Payments, Interest paid.) Format these cells according to the instructions to the right of these cells in the Dream Boat example.
The loan period is 10 years which is 120 months.
The annual interest rate is 7%
The monthly interest rate is 7% divided by 12, represented by 7%/12
Calculating the Numbers
(page
203)
Fill in the given number information in the first five cells.
Click in the “Monthly Payment cell (where the number goes, not the title)
Click on the fx at the top just to the left of the formula bar
You will see this dialog box
You will now see this dialog box:
Please not that as you click in each datum box, the description will appear at the bottom of the dialog box.
For the Dream Boat example the dialog box was filed in like this:
Reminders: The Rate is the interest rate per year given (7%) divided by 12; the Nper is the “number of periods in months” (120); the Pv is the present value, or today’s price.
Enter your data and click OK. The monthly payment amount will appear in the designated cell.
This number will appear as a negative number in brackets. That’s OK, it represents a cash outflow.
TOTAL
OF ALL THE PAYMENTS
Now you want to know the amount of all the payments to buy the dream car
Click in the cell to the right of the title “Total Payments”
Tap =
Click in the cell where the monthly payment amount is (you just calculated it)
Tap the
times sign *
Click in the cell where the total number of payments (in the payback period) is. In the dream boat example this figure is 300.
Tap Enter
You now have the total amount of cash paid for the dream car.
CALCULATE
THE AMOUNT OF INTEREST PAID
Click in the cell to the right of “Total Interest Paid”
Tap =
Click in the cell to the right of “Total Payments” (where the total $ payments appear)
Tap + (because total payments is a negative number)
Click in the cell to the right of “Today’s Price (where the $ amount is)
Tap Enter
This is how much interest was paid. It is a negative number because it represents a cash outflow
[--In the Dream Boat example, there are pictures of the boat across the top of the first sheet. Some of you were not sure if you needed to include pictures of the car in your Excel sheet or just the PowerPoint file.
Answer: You need to include pictures of your car in the Excel file just like in the Dream Boat, example, except you will use only 4-6 pictures and your descriptive text in the cells will be different based on what the pictures show.]
2nd Sheet (Income Effect) on the Dream Boat example:
-- Some of you weren't sure whether or not you had to also include a 2nd sheet in your Dream Car Excel file showing the Income effect (ie. Annual payments, Annual Income, etc.).
Answer: Yes, you have to create this sheet in your Dream Car Excel file as well. The only difference between the example shown in Dream Boat and your file will be the dollar values shown. The amounts in the Income Effect sheet for your Dream Car will be based on the values that you calculated in the first sheet (ie. the Annual Payment amount in the Income Effect sheet will be based on your monthly payments that you calculated in the first sheet, etc.). There are no instructions for this sheet, however, in terms of structuring the sheet, follow the example in the Dream Boat file -- use your common sense to determine any formulas that should be calculated, etc.
The Learning Outcomes Sheet in the Dream Boat file:
-- Do you need to include this in the Dream Car file?
Answer: No, this is for information purposes only -- it is only a listing of the learning outcomes for this assignment and the pages in the textbook that demonstrate how to do certain tasks.