The Mathematics of Money

Copyright © 2008, The McGraw-Hill Companies, Inc.

Here is where using formulas in a spreadsheet really pays off. Fortunately, since we set the

spreadsheet up to calculate those values with formulas, updating the spreadsheet is quite easy.

Simply go to cell B4 and type in the number 1500 to replace the 500 that is already there. As

you can see here, the values in B5, D4, and D5 will automatically change as a result.

1

2

3

4

5

A B C D

Product Units Sold Unit Price

Solar Singing Fish Gutter 1800 $23.95

Home Crash Test Kit 880 $127.95

Twelve Tone Door Chime 1500 $44.99

Totals N/A

Product Sales

$43,110

$112,596

$67,485

4180 $223,191

You can experiment for yourself by making changes in units sold, or changes in unit

prices. No matter how many changes you make to the units sold or unit prices, the spread-

sheet automatically updates all the results that depend on these values.

We can now easily make changes to the sales and prices for the three products we

included in our sheet, but what if we realize that we needed to add a fourth product to

the list? Suppose we now realize that we forgot that the company also sold 1,200 Widget

Detectors at $8.95 each and need to add those to the sheet. This requires a little more effort,

since we need to insert an entire new row above the total column.

To accomplish this, click on cell A5, and then use Insert Row. This will insert a row

above the Total row, moving the Totals down to row 6. (Once again, the formulas automati-

cally update to refl ect this change.) In the new A5, type in the product name, in the new B5

type in the number of units sold, and in the new C5 type in the unit price. We can copy the

formula for D5 from D4.

Since the row was inserted into the range of cells included in the sum formulas, those

formulas should automatically update to include the values from the new rows. There

should be no need to adjust the formulas in B6 and D6.^2 Once we make any needed format-

ting adjustments, the result should be a spreadsheet like the one here.

1 2 3 4 5 6

A B C D

Product Units Sold Unit Price

Solar Singing Fish Gutter 1800 $23.95

Home Crash Test Kit 880 $127.95

Twelve Tone Door Chime 1500 $44.99

Widget Detectors $8.95

Product Sales

$43,110

$112,596

$67,485

1200 $10,740

Totals 5380 N/A $233,931

Before moving on, let’s work through another spreadsheet example similar to the one

we’ve created here.

Example 5.1.1 A company has four employees who all work different hours and are

paid different hourly rates. Adam earns $12.75 per hour, Betty earns $11.85 per hour,

Carole makes $13.95 per hour, and Dario earns $12.50. Last week each person worked

the following hours: Adam: 20, Betty: 28, Carole: 36, and Dario: 27.5.

Set up a spreadsheet to fi nd the total gross (i.e., before deductions) pay for each

person and the total gross pay for all four employees for last week.

To create this spreadsheet, we follow essentially the same steps as in the fi rst example. In

fact, the formulas are essentially the same as in that example, though column D should be

(^2) You should make sure to check that this actually does happen though. In some versions of Excel this does not
consistently work as expected.
5.1 Using Spreadsheets: An Introduction 213