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

Please note this spreadsheet is designed so that some formulas can be replicated and some cannot be. This is to stress spreadsheet design and ensure your understanding of formula replication as well as instill the need for checking formulas.

To Do

Part 1

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. 
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 entered 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.

Spreadsheet data

Obtain all of the fixed data for this lab from the template file provided. This will save considerable time. Use Firefox, not Internet Explorer to download the file. Right click on the template link and save the file. Double click on it to open it.

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. Even though you have downloaded the file, you must fill in your name.

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

4. Product Data

Column 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 contain 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. 

The sales data for each sales person for each sale item is provided in the template file.

Row 19 should contain formulas for the total number of items sold for each item. You must write the formula. It is best to use the sum built-in function. The sum function will take a cell range as a parameter and return the sum of the cells in the range:

=sum((B13:B18)

This formula can be replicated.

Row 20 should contain the total gross revenue for each item sold. The formula is made up of the total in row 19 times the sale price in column B. For example, the formula for the gross revenue for laser printers would be: 

=b19*b5

Check your results

Row 21 should contain the gross profit. 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

Check your results!

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

6. Commission

A22 is a title for the next section.

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. 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

Replication without editing can be done vertically, but not horizontally. Why? Think about what happens during replication.

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 $D$8+2.5% and $D$9+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)

iftemplate.xlsx is an example that illustrates the use of the if function. It is similar to the formulas you must write for full size and notebook PC's.

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. 

Part 2

Create a Word document answering 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?

Submit your work for Parts 1 and 2 via D2L. This is due by 11:59 p.m.

Results

This page is maintained by Barbara Bracken
Last Modified 12/31/2021

This page is copyright protected © by Barbara Bracken