Comp 100 - Exam #3

Rice University - Spring 2008 (April 21, 2008) 100 points total

This exam can be found at 

http://www.owlnet.rice.edu/~comp100/08-spring/03

1. The examination is a take-home exam.  You are allowed to use the EXCEL textbook, EXCEL and its online/offline help and all materials provided to you in class and on the course Web pages and Owlspace and nothing else.  Any violation of this exam rule is a violation of Rice Honor Code.
2. You are to work to this exam on your own.
3. Do not discuss the exam with anyone else until you are given the permission by the instructors to do so.
4. Fill in and sign the honor pledge below.
5. You have three hours to complete the exam in one sitting.  The time to download and upload the files does not count as part of the exam time.

• Be sure to save all of your work on your U: drive.  After you are all done, 
  •  Close EXCEL (A VERY IMPORTANT STEP!)
  •  Upload ALL your solution files to comp 100 OWLSPACE: Assignment page, exam3.
  • Submit your signed exam papers in class on April 23, 2008.
• Do not modify the solutions on your U: drive until you are given permission to do so.

Preliminary Setup (5 pts)

1. Create a blank EXCEL workbook named Exam3.  Save it in an appropriate location of your drive U:.  Be sure to note where you save it so that you can upload it to OWLSPACE after your are done.
2. (5 pts) Rename Sheet1 to Documentation.  In the Documentation sheet, enter
   1. your name in cell A1,
   2. "Exam 3" in cell A2 and
   3. the date you do the exam in cell B2. 
   4. Remove all grid lines.

Problem I - Using Solver (30  points total)

A manufacturing company makes three types of water skis: a trick ski, a slalom ski and all-purpose ski.

• The trick  ski requires 6 work-hours for fabricating and 1 work-hour for finishing.
• The slalom ski requires 4 work hours for fabricating and 2 work-hours for finishing.
• The all-purpose ski requires 5 work-hours and 1.5 work-hours for finishing.
• The maximum work-hours available per day for fabricating and finishing are 131and 40, respectively.
• The profit for each trick ski is $40, for each slalom ski is $30, and for each all-purpose ski is $37.

How many of each type of ski should be manufactured each day to realize a maximum profit?

This problem can be solved by Sovler with an appropriate spreadsheet representing all the data and computation
needed for the problem. Rename Sheet2 SKI and in the SKI worksheet, set up the spreadsheet shown below using the information given in the problem description. Do not concern yourself with the table format.  Make sure the appropriate formulas are entered. 

1. (25 pts) Use Solver to find the maximum profit and how many skis of each type must be manufactured to obtain this profit.
2. (5 pts) Create and save an Answer Report for your Solver solution.

Be sure to save your work periodically.

Problem II - Financial Functions (20 points total)

Rename Sheet3 Finance.  In the Finance worksheet, set up an appropriate spreadsheet with appropriate financial functions to answer the following problem. 

I borrow the bank $30,000.00 to buy a car at 7.25% annual interest rate and make a monthly payment $900 at the end of each month to pay the loan.

1. (15 pts) Use an appropriate financial function to compute the total number of payments I have to make in order to pay off the loan completely. Your formula has to be in such a way that it automatically recalculates the answer if any of the above numbers changes.
   
    
2. (5 pts) Use Goal seek to find out what the annual interest rate should me in order for me to pay off the loan in 36 payments.  Save the Goal seek result.  You must use the same formula as done in part 1.

Problem III - Lookup functions and tables (15 points total)

FastCopy, a photocopy store, needs a way to automatically calculate the total copy charge based on the number of copies made.  Its photocopy pricing is as follows:

(15 pts) Rename Sheet3 Lookup.  In the Lookup worksheet create the above lookup table and the following cell entries, so that when you enter the number of copies made, its automatically shows the corresponding price per page and displays the computed total charge.

Problem IV - Charts (30 points total)

1.  (10 pts) Rename Sheet4 Charts.  In the Charts worksheet, create and format the table shown below.

Be sure to enter appropriate formula for the average in the Average column and in the Monthly Average row.

2.(10 pts) Create a column chart from the above table as shown below.

3.  (10 pts) Create a pie chart from the above table as shown below.