Computer Science and Computer Engineering 115
Solve it with Computers
Fall 2006


Lab 3

Date and Statistical Functions, Problem Solving

The goal for this lab is to help you develop and demonstrate your skills developing Excel spreadsheets using date and statistical functions. Before doing this lab you are strongly urged to read the material on pages 88 and 185-186, as well as studying class notes about additional statistical functions, and problem solving.

For part of this lab you will use the spreadsheet you developed in Lab 2. For the other parts of this lab you will use the spreadsheet http://www.cs.plu.edu/courses/csce115/fall06/download/excel/lab03-CandyCo.xls

1. In the spreadsheet you developed for lab 2, insert 4 rows immediately above the Assumptions and Findings tables. Then move the those tables up 4 rows to leave space under those tables. Then add the following: immediately below the Assuptions table:

   Dates
   Report date			?
   Today's date			?

   Extend the Findings table by adding the following rows immediately below the existing table:

   Number of products in the inventory			?
   Maximum average annual cost			?
   Minimum average annual cost			?
   Average average annual cost			?

   Replace the question marks by appropriate values or formulas. The "Report date" is a constant (the day you completed the assignment) while "Today's date" should always be the date the spreadsheet is viewed. Format the new sections in the same style as the existing "Assumptions" and "Findings" sections are formatted. The dates should be formatted in the style "16-Sep-06".
2. Complete the CandyCo workbook.
   You been hired as an intern by the Old Fashion Candy Company. Your task is to produce two worksheets. The first sheet involves completing a statistical study in a partially completed in sheet in the CandyCo.xls workbook. The spreadsheet contains data about the cost, price, and production of the company's candy. Add formulas for the profit and change of profit columns. For each of the columns on yearly production and profit and the increase in profit columns, you are asked to calculate
   • Count of the number kinds of candies sold
   • Total
   • Average
   • Median
   • Maximum
   • 2nd largest item
   • 2nd smallest item
   • Minimum

   In addition rank the items on the 2005 profit in such a way that ranking 1 is the item with the most profit

   Format the sheet in a appropriate manner that will impress the company president.

3. Add a new sheet to the workbook that will recommend a pricing on a new candy the company is considering selling. You will design the sheet completely on you own using the data given below. The recipe, prices, costs, are all subject to change as the development cycle continues. Your supervisor hints that the if successful, your worksheet will be used to price other products as well. The current, very tentative costs associated with making a 20 pound batch of "Dreamy Fudge" are as follows:

   Item	Quantity	Unit	Price per unit		Item	Quantity	Unit	Price per unit
   Ingredients					Labor
   Sugar	15.0	lb	$.50		Candy Chef	0.25	hr	$22.00
   Milk	1.0	gal	$4.00		Helper	0.6	hr	$10.00
   Corn syrup	0.2	gal	$4.5		Packer	0.2	hr	$9.00
   Unsweetened chocolate	2.0	lb	$4.50
   Butter	2.2	lb	$2.00		Overhead and other expenses
   Vanilla	0.3	gal	$13.00		Labor	30%	of total labor expenses
   Chopped nuts	3.5	lb	$4.20		Building	$0.10	per pound
   					Management	$0.25	per pound
   					Advertising	$0.10	per pound
   					Gas for cooking	0.9	hr	$1.00
   					1 lb. container	$0.20	per pound box

   The desired markup (profit) is 20% of the total cost. Determine the suggested selling price of the entire batch and of a 1 pound box. Include your name and the date of the last update (not the current date.) Also include the number of days since the last update. Format the sheet in a professional manner making sure it is easy to change the name of the candy, size of the batch, quantities, costs, profit margin, and update date. It should be easy to add new items should that be necessary. You boss suggests that if the spreadsheet cannot be printed on one page, it would be best if resulting spreadsheet is only one printed page wide and strongly suggestions that you develop the layout of the sheet on paper before inputting it into the spreadsheet.

Save the workbooks:

These spreadsheets may be used in other labs. Please save copies for possible future use.

Additional Requirements:

• Remember to use formulas whenever appropriate. Assume the paper grader will change the data before grading the sheet. Your spreadsheet should correctly solve the problem with the new data.
• Combine all the worksheets into one workbook so you will only have to submit one workbook for grading.
• Your name must appear on each sheet.

Grading:

Your spreadsheets will be graded on meeting the requirements, correctness, general looks, and impression they leave the reader.

Submitting your presentation:

Submit the workbook and your memo.

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.
4. Click "Login".
5. In the add Assignment box, browse to find the workbook that contains all three worksheets.
6. Select "Lab 3" 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, October 2. 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 over 24 hours after the due date are 2 days late and are subject to a 40% penalty.)

Revised: Sept 30, 2006