Excel Exercise 2

CS 102

Dec. 8, 2003

You are the manager of Fine Things Jewelry Store. You are having a sales campaign that will reward the salesperson who has the highest average sales over a four-week period. The winner of the contest will receive a bonus of five percent on all sales made during the four weeks. The regular percent earned is ten percent. The campaign is in its last week.

Create the following spreadsheet to keep track of total sales. The column labels are all right aligned. Set the spreadsheet up so that the ``Week 1'' column heading is in cell C4. Leave the row above it (row 3) and the column to the left of it (column B) empty.

1. Using Autosum, total each week column. Also, add rows and formulas for the min, max, and average for each week. Use the built-in statistical functions available from the ``Paste Function'' button to the right of Autosum.

2. Add formulas to calculate the total sales and average sales for each salesperson.

3. The winner of the contest is the salesperson with the largest average sales over the four weeks. That person will earn 15 percent on their total sales. All other salespersons will earn ten percent on their total sales. Enter a formula in the percent column to compute this percentage. Assuming that ``Sally'' is in row 5 and ``Average'' is in column H, the formula is:

      =IF(H5=MAX($H$5:$H$9), .15, .10)
   

4. Add a column for earnings. This formula should be percent earned multiplied by total sales.

5. Use one of the date/time functions of the date in cell A2. TODAY() is probably the best.

6. Insert an empty row at row one. Insert another empty row below the spreadsheet title. Finally, insert two empty rows below the column headings for the weeks.

7. Underline the ``week'' column headings in row six. Format the spreadsheet to display currency with two decimals. The percentage column should be formatted with a percentage format. Try different point sizes and fonts for some of the labels (especially the title). If there is time, try out Format|Auto Format.

8. Size the columns to fit the widest entry in each column.

9. Save the spreadsheet as CONTEST. Show me your spreadsheet once you've completed it.