Spreadsheet Practice Problem 2
Print this page, duplicate the following table, then create the formulas. This is a practice problem and also the key. The answers are on the table.
Use the help menu to learn about creating formulas. In Excel use a PMT function for the payment, and a SUM function for the total cost.
The Lowest cost should have a MIN function, the Highest should have a MAX function, and the Average should have an AVERAGE function.
The test will be given in the current version of Excel on a PC or a MAC. The NTM Department teaches Microsoft software (Excel for spreadsheets).
Type the information as shown in the first graphic (Graphic 1).
1. Change the text and information in Cells A1:H17 to Times New Roman, 12 points
2. If using Office 2010 add a title style to the title and change the title to 14 points, bold, and merge and center across the columns.
3. If using Office 2010 add a heading 3 style to A2:H2 and center
4. Change the total row A9:H9 to the total style.
5. Change all cells that contain currency to currency style with no decimals.
6. Change cells C3:C8 and cell B14 to percentages with two decimal spaces.
7. Figure the payment using the PMT function for E3. Copy the formula to cells E4  E8. There are tricks to this function. Study the information in Help carefully. If you do not understand this function, study the formulas on the formula key sheet. Do not copy the formulas, learn them. The tests will be similar but enough different that if you do not understand the formula process you will have difficulty.
8. Figure the total cost in cell F3 by multiplying the payment by the loan period.
9. Use an IF statement in cell G3 to determine if the payment is less than or equal to 150, then use the word Yes if you can afford the car, if not then enter the word No.
10. Figure the difference between the total cost and the initial cost =F3B3
11. Figure the Average, Lowest, and Highest costs of the Total Cost column =average(), =min(), =max().
12. Use Autosum to figure the total of the Car Price, Payment, and the Total Cost columns.
13. Figure the percent of change between the total cost of Auto 1 and Auto 6  this formula uses order of operations. You will use parentheses for part of the formula =(F8F3)/F3.
14. Check the percent of change as indicated in the table below.
15. Format as follows:
 Use the 20% accent 1 cell style for the range A3 to H8 and A16 to B17
 Use the 20% accent 6 cell style for the range A10 to B12 and A14 to B14
 Change the text in cell A16 and 17 to italics
 Bold all the text and numbers
 Center the information in cells C, D, and G
 Center the row titles in cells A2:H2
 Change the row height of rows 14, 16, and 17 to 50
Look up IF commands in HELP and follow instructions. IF commands can be simple or complex. This problem uses a simple IF command. An "If" command simply asks a question and answers that question with two answers, yes and no or true and false or with whatever you want the answers to be.
Graphic 1

A  B  C  D  E  F  G  H  
Automobile Cost Comparison 2012 

2 
Used Cars Sold 
Car Price 
Interest Rate 
Loan Period 
Payment 
Total Cost  Affordable 
Difference Between Total Cost and Initial Cost 

3 
Auto 1 
$5,489 
5% 
36 

4 
Auto 2 
$4,999 
8% 
36 

5 
Auto 3 
$8,449 
6% 
48 

6 
Auto 4 
$8,299 
7% 
48 

7 
Auto 5 
$3,325 
7% 
36 

8 
Auto 6 
$6,499 
5% 
48 

9 
Total 

10 
Lowest Total Cost 

11 
Highest Total Cost 

12 
Average Total Cost 

13  
14  Percent of change between the Total Cost of Auto 1 and Auto 6 
Below is the PMT function window and below the window is the original Excel spreadsheet with the formulas explained. Graphic 3 PMT function window
Graphic 4 KEY and Formulas Explained
Graphic 5
Graphic 5 is not meant to be viewed for answers  it is how the formula page will appear when ctrl ` is pressed. Control and the accent mark will show the formulas 