Excel Exercise 2
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.
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
Add formulas to calculate the total sales and average sales for each
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)
Add a column for earnings. This formula should be percent earned
multiplied by total sales.
Use one of the date/time functions of the date in cell A2. TODAY()
is probably the best.
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.
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
Size the columns to fit the widest entry in each column.
Save the spreadsheet as CONTEST. Show me your spreadsheet once
you've completed it.
Thomas P. Kelliher
Mon Dec 8 10:44:33 EST 2003