Other Links

NTM Department

Wildcat Email Setup

WSU Catalog

 

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.   Change the total row A9:H9 to the total style.
3.   Change all cells that contain currency to currency style with no decimals.
4.   Change cells C3:C8 and cell B14 to percentages with two decimal spaces. 
5.    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.
6.   Figure the total cost in cell F3 by multiplying the payment by the loan period.
7.   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. 
8.  Figure the difference between the total cost and the initial cost =F3-B3
9.  Figure the Average, Lowest, and Highest costs of the Total Cost column =average(), =min(), =max().
10.  Use Autosum to figure the total of the Car Price, Payment, and the Total Cost columns.
11.  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 =(F8-F3)/F3.
12.  Check the percent of change as indicated in the table below.
13.  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              
1.   Create and format a 3-D pie chart from the Used Cars Sold and Total Cost information. Place the chart on the same page as the spreadsheet
information as shown in the key.  Use the control key to block columns of information.
2.   Format the chart area to have a light blue background.
3.   Add a title to the chart, "Total Cost ", and add your full name under the title, enlarge Title and name to 24 points, and change the color to
dark blue.
4.   Add the data labels, color dark blue, place outside end, show the labels, and the percentage.
5.   Delete the legend.
6.   Change the label text and percentages to 10 points.
7.   Change the chart border style to rounded corners and 3 points in size.
8.   Explode the smallest slice.
9.   Practice creating a column chart and a bar chart.

 

Graphic 2     KEY

Below is the PMT function window and below the window is the original Excel spreadsheet with the formulas explained.

Graphic 3 PMT function window

Rate = the interest rate divided by 12 as most loan interest rates are calculated on a yearly basis

NPER = the number of payment periods in the loan

PV = the present value of the loan (the cost of the loan)

FV = the future value of the loan after all payments have been made (this should be 0)

Type = leave blank.  Do not put anything in the type box.  Type is a logical value and means the payment at the beginning of the payment period would normally equal 1and the payment at the end of the payment period would normally equal 0 (unless there is a balloon payment)

Graphic 4 KEY and Formulas Explained

  A B C D E F G H
1 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

$164.51

$5,922.37

No

$433.37

4

Auto 2

$4,999

8%

36

$156.65

$5,639.42

No

$640.42

5

Auto 3

$8,449

6%

48

$198.43

$9,524.40

No

$1,075.40

6

Auto 4

$8,299

7%

48

$198.73

$9,539.03

No

$1,240.03

7

Auto 5

$3,325

7%

36

$102.67

$3,695.99

Yes

$370.99

 

Auto 6

$6,499

5%

48

$149.67

$7,184.03

Yes

$685.03

8 Total

$37,060

 

 

$970.65

$41,505.24

 

$4,445.24

9   Total - use the Autosum option Can use the numbers 0.05 and so on or the % sign for the percentages in these cell =PMT(C3/12,D3,-B3,0)

RATE: Notice that the rate or 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.

NPER: D3 is the loan period or number of payment periods for the loan.

PV: -B3 is the PV or Present value - Notice the - sign in front of B3.  The - sign is used to ensure that the numbers are not negative.

FV: Future value (FV) should be 0.

=E3*D3
(formula)

 

=IF(E3<=150,"Yes","No")

If the payment is less than $150 then Yes, if not then No

E3 is the payment amount

=F3-B3

10

Lowest Total Cost

$3,695.99 =MIN(F3:F8)
(function)
11

Highest Total Cost

$9,539.03 =MAX(F3:F8)
(function)
Check the percent of change as indicated: 
The difference in cost is $1,261.67, which is 21.30%.  It is a good idea to check the change to ensure that you have the correct percentage.

12

Average Total Cost

$6,917.54 =Average(F3:F8)
(function)
13

Percent of change between the total cost of Auto 1 and  Auto 6

21.30% =(F8-F3)/F3 Notice the parentheses, which denotes order of operations  (F8-F3) will be completed before dividing by F3

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


Weber State UniversityOgden, Utah 84408

Privacy PolicyTerms of UseNondiscrimination Policy