Microsoft Excel - Sophisticated


Preliminaries

This lab will demonstrate the use of a spreadsheet to solve a simple, real problem.

Goals

  1. Get more practice Microsoft Excel 
  2. Let Excel perform tedious calculations for you 
  3. Learn about Page Set up 
  4. Apply what we know about relative/absolute addressing 
  5. Apply what we know about formula copying to determine the result of copying formulas and when they can be copied. 
  6. Learn about how to let the spreadsheet make decisions (change a formula based on data within the spreadsheet)
  7. Learn about goal seeking

To Do

1. Start Microsoft Excel

(As described in class)

2.Develop a spreadsheet to solve the following problem:

You are a sales manager for WELLSELLCOMPUTERS. The sales people work on commission. WESELLCOMPUTERS has a small product line: laser printers, inkjet printers, impact printers, full-size PC's, and notebook PC's. 
  1. Given the retail price and cost, calculate gross profit for each item. 
  2. Given a list of sales people, the number of items sold for each sale item calculate the total gross revenue and gross profit. 
  3. Given the commission rate for each item, calculate the commission for each sales person for each item plus the total commission for each sales person. 
  4. Calculate the total commission. 
  5. Calculate the net profit for each item sold. 
  6. Calculate the net profit for WESELLCOMPUTERS. 
Note: In order to reduce the width of columns and ensure that column headings can always be read, some of the column headings should appear on two lines instead of one. e.g.: 

Base 

Commission 

Instead of 

Base Commission

To obtain two lines within a single cell, type the data you want to appear on the first line, then press alt enter (this will cause the next data enered to appear on the next line). 

Data must properly formatted. Where contains currency, it must be appropriately formatted. To format a cell, click format, cells, number tab, etc.

3.Title

Make up a title. It should be in A1. It should be in font size of 14 and bold. 

Your name should go in A2. 

The heading products should be in A3. It should be size 12 and in italics. 

4. Product Data

Sale Item  Sale Price  Cost  Base Commission 
Laser Printer  $2880  $1550  12.5% 
Inkjet printer  $380  $250  9% 
Impact Printer  $180  $85  8% 
Full-size PC  $3950  $2500  12% 
Notebook PC  $3875  $2250  13% 

Coumn headings should appear in row 4. They should be bold. 

The product data should appear in A5 through D9. Column E Rows 5 through 9 should conain a formula to calculate the gross profit for the associated item. For example, the gross profit for an item should be equal to the sale price minus the cost. For Laser printer: 

=b5-c5

Once you write the formula for the laser printer, all you have to do is drag it through the remainder of the sale items. 

5. Number Sold

The next section of the spreadsheet contains the number of items sold of each type by the sales people. Row 10, Column A should have the label Sales in italics and font size of 12. Row 11 Column A should contain the lable Number Sold in bold. Row 12 columns A through F should contain column headings: Sales Person, Laser Printers, Inkjet Printers, Impact Printers, Full-Size PC, and Notebook PC. 

In Rows 13 through 18, fill in the following information:   
Sales PersonLaser PrintersInkjet Printers Impact PrintersFull-size Pc'sNotebook PC
Benjamin Franklin Pierce 10 10 13 23 33
Walter O'Reilly 12 18 15 16 29
Margaret Houghlihan 9 11 16 18 25
Henry Blake 10 6 9 11 17
Max Klinger6 9 10 15 32
Sherman Potter 15 25 19 14 25
Ener the data in exactly the order given. 

Row 19 should contain the total number of items sold for each item. This should be labeled in Column A and the label should be bold. Row 20 should contain the total gross revenue for each item sold. This should be labeled in Column A and the label should be size 12 and italics. The formula is made up of the total in row 19 times the sale price in column B. For example, the formula for thegross revenue for laser printers would be: 

=b19*b5

Row 21 should contain the gross profit. This should be labeled in Column A in italics and size 12. The Gross profit consists of the number sold times the gross profit. For example, the formula for the gross profit for laser printers would be: 

=b19*e5

G20 should have the total gross revenue. G21 should have the total gross profit. Both of these totals should appear in bold. 

6. Commission

Remember to take advantage of copy/paste to avoid retyping labels. 

This section contains the commission. Row 22 column A should conain the label Commission in bold. Row 23 should contain the following labels: Sales Person, Laser Printers, Inkjet Printers, Impact Printes, Ful-Size PC, and Notebook PC.Lines 24 through 29 should contain the commission data for each sales person. The order of the sales people should be exactly as shown above. Each cell should contain a formula for calculating the commission for the given sales person for the given sale item. For example, the commission for Benjamin Frnaklin Pierce for Laser Printers:

=b13*$b$5*$d$5

Note: The commission for laser printers, inkjet printers, and impact printers is exactly as described above. The commission for full-size PC's and notebook PC's changes based on the number sold. The more sold, the higher the commission. The rules: 

You must use the if function in your formula to make these calculations. Also, you must use D8+2.5% and D9+4% rather than hardcoding in a value. This is important because if you want to change the base commission assumptions, you only have to make a change in one place instead of anywhere you may have used it. 

The format of the if function: 

if (test_value, true_value,false_value)

Remember, you must appropriately use relative and absolute addressing for formula copying. 

Column G must contain the total commission for each sales person. Row 30 must contain the total commission for each sale item. G30 should contain the total commission. Row 30 column A should be labeled Totals in bold. The Total commission in G30 sould also be bold. 

Row 31 must contain the net profit for each item and G31 contains the total net profit. Row 31 column A should have the label net Profit in bold and font size of 14. The total net profit in column G should also be in bold. 

The net profit for laser printers: 

=b21-b30

7. Hand In

  1. Before printing out any data, you must change the page setup. Click file, page setup, click on the circle for landscape. This will turn yor spreadsheet 90 degrees so it will fit on one page. You also must add grid lines and column headings. Click sheet tab and check gridlines and row and column headings. 
  2. Be sure that you have gridlines and row/column headings set 
  3. Print your spreadsheet and hand it in. Display formulas, print the spreadsheet with formulas and hand it in. 
  4. The next step is goal seeking. You want to find out how many full-size PC's Margaret Houghlihan must sell to make her total commission$50,000. To do goal seeking, select the sell with the formula that you want to calculate $50K. Click tools on the menu bar, point and click goal seek. Click to value text box and set it to $50000. Fill in the cell reference. The cell reference is the number of full-size PC's Margaret Houghlihan sold. Print the results of goal seeking and hand it in. 
  5. Using Microsoft Word, produce a document that answers the following questions:
    1. Why couldn't you drag the formula from B20 through to F20 and the formula from B21 through to F21?
    2. Why could you drag the formula from B24 through B29 and not from B24 through D24?
You should have 3 print outs to hand in. 

8. Bonus points. 

If you know the answers to the following questions, hand write the answers in the margin of your first print out. You will receive one point for each question: 
  1. What is the name of the show that from which the names of the sales persons as taken? 
  2. Which character never made it home because his plane was shot down over the Sea of Japan? 

9.You should save your worksheet in your server space or on diskette.

Results

[CS 115 Home Page] [CS 115 Lab Page]

Maintained by Barbara Bracken
Last Modified 12/13/2004

This page is copyright protected © by Barbara Bracken