Spreadsheet Practice Problem
Print this page, duplicate
the following table, then create the formulas that end in the payment, total cost, lowest,
highest, and average results. 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 Quattro Pro and 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 Quattro Pro or Excel. The TBE
Department teaches Microsoft software, which for spreadsheets is Excel. 1. Change the text and information in Cells
B2-F13 to Times New Roman, 12 points
2. Change the title to 14 points, bold, and center across the columns.
3. Italicize the subtitles A2-F2.
4. Change the font color to blue for the text and numbers in rows B3 - F12.
5. Change the Font color of the titles to purple.
6. Change the total column cell color to pale yellow.
7. Change cells B3 - B8 to currency, no decimals.
8. Change cells C3 - C8 to percentages, no decimal spaces. This may cause you
problems if you leave the numbers as they are. What would you type in to gain a
single digit percentage number?
9. 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.
10. Figure the total cost using the SUM function or a formula.
11. Figure the Average, Lowest, and Highest costs of the Total Cost column.
12. Add two columns of information to the spreadsheet. In Column G add "Interest" and in column H add the word "Affordable". Wrap the
title in column G.
13. In column G, row 3 create a formula that will give you the total cost of the
interest.
14. In column H, row 3 create an IF command that will tell you if you can afford the
payments for the car at Brad's Auto and copy the formula to Rows H4 through H8. If
the payment is under $230 then "Yes", if over $230 then "No".
Look up IF commands in HELP and follow instructions. IF commands can be simple or
complex. This is a simple IF command. "If" commands simply ask a question
and answer that question with two answers, yes and no or true and
false or with whatever you want the answers to be. Link to
Functions and Formulas
NOTE: When you create and copy functions or formulas to other cells in a row or
column, and indicate specific cells
within a function or a formula, such as column F row 3, =SUM(F3-B3), you can make changes to any number in the
column or row that has been specified in the formula and it will automatically change all the numbers in the row or column.
For
example if you had a formula that specified =E3*D3 and changed
the percentage rate in the C4 column, Excel or QP would automatically
change the payment and total cost figures for you. If on the
other hand you keyed in =250*24 then you could not copy the
formula. Excel or QP would copy 250*24 down the column no matter
what numbers were in the cells.
|
A |
B |
C |
D |
E |
F |
| Automobile Cost Comparison
Spring, 2002 |
| 2 |
Dealership |
Car Price |
Interest Rate |
Loan Period |
Payment |
Total Cost |
| 3 |
Brad's Auto |
$5,489 |
5% |
24 |
|
|
| 4 |
Fairway Auto |
$4,999 |
8% |
24 |
|
|
| 5 |
First Choice Auto |
$8,449 |
6% |
36 |
|
|
| 6 |
Great Auto |
$8,299 |
7% |
36 |
|
|
| 7 |
Lindsey Auto |
$3,325 |
7% |
24 |
|
|
| 8 |
My Town Auto |
$6,499 |
5% |
36 |
|
|
| 9 |
|
|
|
|
|
|
| 10 |
Lowest Total Cost |
|
|
|
|
|
| 11 |
Highest Total Cost |
|
|
|
|
|
| 12 |
Average Total Cost |
|
|
|
|
|
|
1. Create and format a bar chart from the
Dealership, Car Price, and Total Cost information. Place the chart on a separate page from
the spreadsheet information. You may use the Chart Wizard in Excel for this
practice. Use the Help function if you do not know how to block columns of
information.
2. Format the chart to have a colored or black background and change the default settings
for the bars to another color of your choice.
3. Add a title to the chart, "Automobile Cost Comparisons", enlarge to 28 points
and change the color to red.
4. Add a title to the Y category, "Cost", change the color to blue, size 14
points.
5. Add a title to the X category, Dealerships", change the color to blue, 14 points.
6. Rotate the X axis information to 39 degrees.
7. Add a legend to the chart. Place it on the bottom and change the size of the text
to 14 points.
The Chart will look something like
this:
Below is the original excel
spreadsheet with the two
columns of information and formulas added.
| |
A |
B |
C |
D |
E |
F |
G |
H |
| 1 |
Automobile Cost Comparison Summer,
2002 |
| 2 |
Dealership |
Car Price |
Interest Rate |
Loan Period |
Payment |
Total Cost |
Interest |
Affordable
|
| 3 |
Brad's Auto |
$5,489 |
5% |
24 |
$240.81 |
$5,779.44 |
$290.44 |
No |
| 4 |
Fairway Auto |
$4,999 |
8% |
24 |
$226.09 |
$5,426.19 |
$427.19 |
Yes |
| 5 |
First Choice Auto |
$8,449 |
6% |
36 |
$257.03 |
$9,253.26 |
$804.26 |
No |
| 6 |
Great Auto |
$8,299 |
7% |
36 |
$256.25 |
$9,224.96 |
$925.96 |
No |
| 7 |
Lindsey Auto |
$3,325 |
7% |
24 |
$148.87 |
$3,572.85 |
$247.85 |
Yes |
| 8 |
My Town Auto |
$6,499 |
5% |
36 |
$194.78 |
$7,012.11 |
$513.11 |
Yes |
| 9 |
|
|
|
|
=PMT(C3/12,D3,-B3)
Notice that the 5% interest is
divided by 12. That means that the 5 % interest is spread out
over 12 months. You pay 5% a year, not a month. |
=E3*D3
(formula) |
=F3-B3
(formula) |
=IF(E3<230,"Yes","No")
If the payment is less than $230 then Yes
E3 is the payment amount
(IF function)
|
| 10 |
Lowest Total Cost |
$247.85 |
=MIN(G3:G8)
(function) |
|
=SUM(E3*D3)
(function) |
| 11 |
Highest Total Cost |
$925.96 |
=MAX(G3:G8)
(function) |
|
|
|
|
|
| 12 |
Average Total Cost |
$534.80 |
=Average(G3:G8)
(function) |
|
|
|
|
|
|
