220 Chapter 5 Spreadsheets
- Calculate the future value for Exercise 11, using the compound interest formula. Does the answer agree with the value
calculated by the spreadsheet? Should it?
C. Grab Bag
- Don invested $2,000 at 6% compounded annually for 10 years. Then, he found that he could earn a higher rate and for
the next 30 years his account earned 8%. Use a spreadsheet to fi nd the future value of this investment at the end of 40 years. - A bicycle store wants to use a spreadsheet to determine the retail prices for the bikes that it sells. The store uses the
following pricing formula: begin with the wholesale cost for the bike, add a 27.5% markup, and add on $20.
a. Set up a spreadsheet to allow the store manager to quickly determine the retail price of any bike, based on cost.
b. Use your spreadsheet to calculate the retail prices for these bikes (and wholesale costs given in parentheses):
model A ($349.02), model B ($189.08), model C ($201.05), model D ($505.05) and model E ($372.75).
c. Use your spreadsheet to calculate the total bikes sold for these models in a month when the store sold 13 Model
A’ s, 17 B’s, 6 C’s, 8 D’s, and 11 E’s. (Note: If you need to add columns to your spreadsheet, they can be added in
the same way that you add rows.)
d. Use your spreadsheet to determine the store’s total sales for the month.
e. Use your spreadsheet to determine the store’s total gross profi t for the month. (i.e., the difference between the
total retail sales and the total wholesale cost of the products sold.)- A law fi rm has three associate attorneys and two paralegals, each of whose time is billed at a different rate. The
associates (and hourly rates) are Dewey ($175/hour), Cheatham ($215/hour), and Howe ($275/hour). The paralegals
(and hourly rates) are Yu ($85/hour) and Bette (also $85/hour).
a. Set up a spreadsheet to determine the total bill for each client, based on the billable hours for each member of the fi rm.
b. Use your spreadsheet to calculate the total bill for a corporate client that had billable hours as follows: Dewey 3.5,
Cheatham 0, Howe 11.5, Yu 6.0, and Bette 1.5.
c. Use your spreadsheet to calculate the total bill for a corporate client that had billable hours as follows: Dewey 1.0,
Cheatham 0.5, Howe 1.75, Yu 3.0, and Bette 0.
d. The fi rm offers a discounted rate to one of its largest clients. For this client, Dewey and Howe bill $165/hour and
Cheatham bills $160/hour. Yu bills $65/hour and Bette’s rate is $60/hour. Use your spreadsheet to calculate the bill for a
month in which this client had billable hours as follows: Dewey 24.0, Cheatham 17.5, Howe 32.5, Yu 49.5, and Bette 8.0.D. Additional Exercise
- Suppose that I deposit $750 per quarter into an account earning 8.13% compounded quarterly for 30 years.
a. Set up a spreadsheet to calculate the future value of this account.
b. Suppose that each year I increase my quarterly deposits by $10 each. (So I deposit $760 per quarter in year 2,
$770 per quarter in year 3, and so on.) What would the future value of my account be then?