Computer Science and Computer Engineering 120
Computerized Information Systems
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/csce120/fall06/download/excel/ElectronicsCenter.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 sell. 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 average annual sales (in dollars)
  • The profit for each unit sold
  • The percent markup (= profit/unit cost)
  • Average annual profit
  • The ratio of the units on hand to the average annual sales
  • The desired inventory level
• In addition in the findings area, calculate the total units sold each year, average annual sales, the total profit, and percent total profit is of the total average annual sales.
• 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 color to red 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 and other seems low. Currently she typically reorders when the current inventory falls below 1/24 (one half month) of the annual unit sales per year. 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 reordered, conditionally format the "on-hand to annual sales ratio" to have a red font on a light blue background. If the inventory is too large, conditionally format the value as black on a pale red background.
• Currently she believes the most desirable level for the inventory is 1/12 (one months) of the annual sales
.
• 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 four 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 four values, the spreadsheet must be automatically updated. Provide formulas and format the "Findings" table.

Requirements:

• Format the spreadsheets in an appropriate way using the standards of Chapter 1 and 2.
• 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 ration 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 (csce120-brink-secX, Fall 2006 where X is 1 or 3) 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:

Section 1: Midnight, Thursday, Sept 21; Section 3: Midnight, Tuesday, Sept. 19. 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 and will be subject to a 20% per day. (Late assignments turned in on after the grace period are at least 2 days late and are subject to a 40% penalty or more.)

Save the spreadsheet:

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

Revised: Sept. 21, 2006