Computer Science and Computer Engineering 115
Solve It With Computers
Fall 2006



Lab 2

Intermediate Excel

This lab covers material in Chapters 1 and 2 including: basic Excel, formulas, simple functions, relative and absolute references, formatting, and condtional formatting.

The goal is to help you develop and demonstrate your Excel and problem solving skills presented in Chapter 2 by completing a spreadsheet. Before doing this lab you are strongly urged to work though Chapter 2 actually carrying out Hands-On Exercises 1 and 2 (pages 65 - 86, 88). Also read and study about conditional formatting on pages 81 and 93.

Download the file http://www.cs.plu.edu/courses/csce115/fall06/download/excel/ElectronicsCenter(115).xls

The scenario is as follows:

Susan Brown, owner of Value Electronics Center, has hired you to complete a spreadsheet that will help the company analyize and control their inventory. They have created a spreadsheet with data concerning the products they cell. For each item in the inventory, they have provided their cost per unit, the price they charge, the number of units currently in the inventory, and the average number of units sold each year. Susan explains that the initial steps are as follows:

• For each item in the inventory, compute
  • The retail value of the inventory (current inventory)
  • The markup percentate of the item
  • Unit B and O tax
  • Unit profit (unit sales price less unit cost and unit B and O tax)
  • Average annual sales
  • Average annual cost
  • Average annual Band O tax
  • Average annual profit
• In addition in the findings area, calculate the total units sold each year, total retail value of the current inventory, total annual (retail) sales, total averarage annual cost, total average B and O tax, and total average annual profit
• Format the spreadsheet in a style appropriate for chapters 1 and 2. The supplied data must be shaded a special color to make it easy update the data on a daily basis as items are bought, sold, or the prices change.
• Susan tells you that she would like to mark up items 20%. Competition often does not allow her to do so. So that she can easily tell which items have a smaller markup, use conditional formatting to change font for "Markup percentage" to bold white with a red background on each item with a profit margin of less than the amount.specified in the assumptions (initially 20%).
• Susan is concerned that the current inventory on some items seems high. If the current inventory is more than 3/24 (three half months) of the annual sales, she normally will put the item on sale to reduce the inventory. To make it easy for her to determine which items need to be put on sale, conditionally format the "Current inventory" to have a bold red font on the normal background if the inventory is too large.
• On the bottom of the spreadsheet, add your name and section, last update date, and current date.

Note: Observe that near the top of the spreadsheet, there is a beginning of a table for the three values given above. Add a column to the table for those values. Format this table appropriately and refer to the values in the table in your formulas. If Susan changes any of the three values, the spreadsheet must be automatically updated. Provide formulas and format the "Findings" table.

Requirements:

• Use formulas and cell references whenever appropriate. Assume the papergrader will change the data and assumptions before grading the sheet. For example, the paper grader might enter change the desired profit ratio to 15% and values of the current inventory and prices.
• Make sure that your name and section number appear on the spreadsheet.

Extra credit bonus:

Add a hyperlink to the ValueElectronics Center's web site: http://www.cs.plu.edu/courses/csce120/fall06/download/html/ValueElectronics.html (1 point)

Grading:

Your spreadsheets will be graded on correctness, general looks, impression the sheet has on the reader, meeting the requirements, and following instructions.

Submitting your your assignments:

Submit the workbook.

1. Use the URL http://www.cs.plu.edu/submit/login.php
2. Select your class (csce115, Fall 2006) from the drop down box.
3. Enter your submit system username and password as provided by the instructor.
4. Click "Login".
5. In the add Assignment box, browse to find the workbook.
6. Select "Lab 2" for the assignment and then click "Upload".
7. Double check to make sure the file has been uploaded.
8. Click "Sign Off" near the top right hand corner of the window.

Due date:

Midnight, Monday, Sept. 25. There will be a 24 hour grace period. After the grace period late submittals will not be accepted except with written permission from the instructor. Even with permission, there will be a significant penalty for late submitals.

Save the spreadsheet:

This spreadsheet may be used in other labs. Please save copy to make this possible.

Revised: Sept. 14, 2006