Spreadsheets II Exercise: Sales Summary

What to do How to do it
Save a copy of the exercise file excel2.xls to your floppy disk.
  1. Right-click the link excel2.xls and click "Save Target As ..."
  2. Select 3 1/2 Floppy (A:) from the "Save In:" pull-down menu
  3. Double-click your folder
  4. Double-click the Spreadsheets folder
  5. Click the Save button
Open your copy of the file.
  1. Choose a file to be opened using one of the following methods:
    Open Microsoft Excel and either click File, Open, click the Open button, or Press Ctrl-O
    Open Windows Explorer or My Computer
  2. Double-click 3 1/2 Floppy (A:) or select it from the Look In: menu
  3. Double-click your folder
  4. Double-click the Spreadsheets folder
  5. Double-click excel2.xls
Type Sales Summary in the upper left cell. Click in cell A1, type Sales Summary and press Enter.
Put your name in cell A2. Type your first and last name and press Enter.
Put today's date in cell A3. Type in the date and press Enter.
Save your changes often. Press Ctrl-S.
Sum the sales for the first salesperson (Rosco). Click in cell F6, click the AutoSum button, and press Enter.
Copy this formula for the other salespeople. Click in cell F6 and drag the fill handle from F6 down to F11.
Calculate commissions for each salesperson.
Multiply the sales of each product by the commission rate.
  1. Click in cell G6 and type =.
  2. Click cell C6, type *, click cell C19, press the F4 key, and type +.
  3. Click cell D6, type *, click cell D19, press the F4 key, and type +.
  4. Click cell E6, type *, click cell E19, press the F4 key, and press Enter.
  5. Check that the resulting formula is =C6*$C$19+D6*$D$19+E6*$E$19.
Copy this formula for the other salespeople. Click in cell G6 and drag the fill handle from G6 down to G11.
Compute totals for the first product (Crown). Click in cell C13, click the AutoSum button, and press Enter.
Copy this formula to the right and commissions. Click in cell C13 and drag the fill handle from C13 across to G13.
Compute the average, high, low and range of sales values for each product.
  1. Click in cell C14, click the fx button, select the Average function, type or select the range C6:C11, and click OK.
  2. Click in cell C15, click fx, select MAX, type or select C6:C11 and click OK.
  3. Click in cell C16, click fx, select MIN, type or select C6:C11 and click OK.
  4. Click in cell C17, type =C15-C16 and press Enter.
Copy all these formulas to the right. Select cells C14:C17 and drag the fill handle from C17 to G17.
Format all currencies with comma format. Drag from C6 to G17 and click the currency button on the formatting toolbar ($ or notes and coins)
Commissions should have two decimal places; the other currencies should have none.
  1. Click the decrease decimal button until there are no decimal places.
  2. Click column G and click the increase decimal button two times.
Format commission rates as percentages with one decimal place. Select cells C19:E19, click the % button, and click the increase decimal button one time.
Make the main heading larger and boldface. Click in cell A1, click the Bold button, and select a larger font size e.g. 20 point.
Make column and row headers boldface and shaded.
  1. Click row button 5, click the Bold button, and select a fill color from the formatting tool bar.
  2. Select cells A13:A19, click the Bold button, and select a fill color from the formatting tool bar.
Add a border around the table. Select cells A5:G17, click Format, Cells, Border, click the Outline preset and click OK.
(You can also use the Borders menu on the formatting toolbar.)
Change the rate of commission for Crown from 5.5% to 6.0% and watch the change in the totals. Click in cell C19, type 6% or 0.06, and press Enter.
Sort the data alphabetically by salesperson. Click row button 5 and drag down to row 11 to select rows 5-11. Click the A>Z button.
Sort the data in descending order by commission.
  1. With rows 5-11 selected, click Data, Sort.
  2. Select Commission from the Sort By menu.
  3. Click the Descending radio button and click OK.
Add sheets named February and March which assume that all sales data will increase by 1% per month.
  1. Click Edit, Move or Copy Sheet.
  2. Check the box "Create a copy", click (move to end) and click OK.
  3. Double-click January (2), type February and press Enter.
  4. Select C6:E11 in sheet February and press Del.
  5. Click in cell C6, type =January!c6*101% and press Enter.
  6. Drag the fill handle down to C11 and across to E11.
  7. Repeat the above steps to create a sheet named March which increases by 1% over February.
Add a sheet named Totals which adds the numbers from January through March.
  1. Click Edit, Move or Copy Sheet.
  2. Check the box "Create a copy", click (move to end) and click OK.
  3. Double-click March (2), type Totals and press Enter.
  4. Select C6:E11 and press Del.
  5. Click in cell C6, type =sum(January:March!c6) and press Enter.
  6. Drag the fill handle down to C11 and across to E11.
Save your changes to the document and exit.
  1. Press Ctrl-S to save the document.
  2. Click File, Exit to close Excel.