## CSc 050 Assignment 3 - Spreadsheets Part II Due Mon, Oct. 3, 2005

### Goals

• To see how good design helps when you modify a spreadsheet
• To practice writing three-dimensional references
• To practice with named cells
• To practice creating charts and graphs

In this assignment, you'll modify your spreadsheet from the last homework to make a comparison of sales between several musical artists. To do this assignment, make a copy of your last spreadsheet file and work on the copy. Don't modify the original! You'll need it in case you really mess up. Again, label everything neatly and use Master Rule #1 whenever possible.

Question 1 (7 pts.)
We wish to compare profits from Gopher Buckets sales here in America to those of Yanni in Italy and the Three Tenors in England. Thus, you'll be repeating Question 1 from the previous assignment with the other artists. (You can erase Questions 2 and 3 from the previous assignment.) Place each artist's information on a separate worksheet. It may help to use the Move or Copy Sheet... command from the Edit menu.

Note: this part of the assignment should be relatively easy provided that you designed the original spreadsheet well in the first place. If you lost points for your design on the last assignment, correct those design flaws first before proceeding. If you don't correct them, you will lose points for those design flaws again.

The following table lists the relevant info about the new artists.

Yanni Three Tenors
currency Euro Pound
retail price 16.99 £14.25
initial cost 8.15 £12
discount percentage
if threshold reached
5% 1%
monthly discount threshold 175 400
monthly sales increase 2% 3.5%

Calculate the same information for each artist that you did for Question 1 in the previous assignment. Don't do currency conversion yet. Each set of numbers should show totals in its own monetary unit (with correct formatting).

Question 2 (5 pts.)
On a new worksheet, show the monthly profits for the three artists. Use three-dimensional references to obtain each month's figures from the other worksheets. All values should be in US dollars which means you'll need to do some currency conversion (that is, Excel will need to.) Look on the Web for today's exchange rate. Check out the Excel Part II Example to see the layout I'm expecting.

Then create a chart which shows how the profits from the three artists compared month by month over the course of a year.

Question 3 (5 pts.)
For each artist's worksheet you used for Question 1, give the cell that holds the total annual profit a meaningful name. Then show these annual total profits for all the artists on the worksheet you used for Question 2. Use the cell names in your formulas instead of their addresses. Finally, create another chart on the Question 2 worksheet that compares the total annual profits of the artists.