Spreadsheet Exercise #3
For this exercise, we will use the spreadsheet
loans2.xls:
- Save the file to your Documents folder.
- Open the file in MS Excel.
The following are step by step instructions to complete this spreadsheet.
This exercise gives you practice in using spreadsheets, but it also
demonstrates how a spreadsheet can be used to assist people
in decision making.
e.g. You are about to purchase a car and are confused by all the payment options.
You can enter formulas into a spreadsheet so that you can see
the total cost for each option.
Another important aspect of this exercise is to learn about the use of Absolute
vs Relative References. Make sure to observe the different uses of these two
types of references.
Step #1: Formatting Text:
Select the cells B1 through C1 by using the shift key. Then,
open the Formatting Menu by clicking the small arrow in the bottom
right corner of the Font toolbar. Use the Formatting Menu to
Underline and Bold the title. Then, change the colour
to Blue and the font size to 16. Next, click on the "Fill" Tab, and Select
both a Fill Pattern and a Background Colour.
Step #2: Entering Data:
- Select cell B3 and type the number 22400 and
press the Enter key.
This is the selling price.
- Select cell B4 and type the number 5000 and
press the Enter key.
This is the down-payment amount.
Step #3: Entering Formulas: (Absolute and Relative References)
- The first formula will go into cell B5.
Select that cell and enter the following formula: =B3-B4
- The second formula will go into cell B12.
Select that cell and enter the following formula:
=B11+$B$5
- Select the cells B12, C12 and D12 by selecting cell B12 and dragging the cursor to D12 while holding shift.
- Now click on the Fill button, in the Editing toolbar
and select Fill Right from the drop down menu.
- You will notice the dollar sign ($) in the second formula. This is an absolute reference. No matter where you copy the formula to it will always refer back to that specific cell (B5 in this case).
- In the first formula, there were no dollar signs ($). This is refered to as a relative reference. If this formula is copied to a different cell, the formula will change its cell references accordingly.
Don't forget to use the = sign before entering a formula.
The letters do not need to be capital letters.
Step #4: Entering a Function:
- The first function that we will use is a financial function. It will determine the monthly payment for us.
- First you will need to select cell B10 by clicking on it.
- Click on the Formulas tab and then click on Insert Function. Now you should see
the Insert Function menu.
- Click on the "Category" Box and select the Financial category from the drop down menu.
Then, scroll down and select the PMT(Principal, Rate, Term) function.
- Click "OK"
- Now the Function Arguments Window will appear. Complete the function arguments
as follows:
- In the space labeled "Rate" (Interest Rate) type: "B9/12."
- In the space labeled "Nper" (Number of Payments) type: "B8."
- In the space labeled "PV" (Present Value) type: "$B$5"
- Click "OK."
- Next, select cells B10, C10 and D10 and then use the Fill Right
process, detailed above, to replicate this formula for each of the cells.
- The next formula that we will use is a multiplication formula. It will multiply two cells for us.
- Select cell B11
- Type in the following: =B10*B8
The asterisk (*) is the multiplication symbol.
- Select the cells B11, C11 and D11 and use the Fill Right process
to replicate this formula for each cell.
Now you should be finished editing your spreadsheet and it should look something like this:
Spreadsheet Exercise Index Page