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



Lab 4

Charts, OLE, and IF Function

The goal for this lab is to help you develop and demonstrate your skills creating Excel charts as well as developing Excel spreadsheets. You will also create a Microsoft Word memo that includes an Excel spreadsheet and chart. An important part of this lab is picking an appropriate type of chart for particular situations. Before doing this lab you are strongly urged to work though Chapter 3 actually carrying out Hands-On Exercises 1 through 3. The lab also includes the IF function.

This lab is a variation of the MiniCase "The Convention Planner.xls" on page 158. You will develop two spreadsheets: One assumes you are planning a convention for a major company. The second assumes you are working for a consultant studying the hotel industry in the same city.

1. Open the file "Chapter 3 Mini Case - Convention Planner.xls" from the download file for the textbook.
2. After opening the workbook, make a copy of the "Room Capacities" page in the same workbook and rename the copy "Hotel Analysis".
3. Assume you have been hired by CSCE Reality, Inc to plan their annual convention. The first task is to recommend the hotel in which to hold the convention. Using the original "Room Capacities" page, add new column in the table called "Cost for Rooms Needed". Underneath the current data table, add a new table as follows:

   Rooms needed
   Standard	160
   Deluxe	22

   Provide formulas the columns "Total Number of Rooms" and "Cost for Rooms Needed" and calculate sums in the "Totals" row for those columns where a sum is appropriate. The "Cost for Rooms Needed" gives the total cost of the needed rooms.

   To help you determine which hotels have an adequate number of rooms for the convention, add two additional columns next to the "Cost for Rooms Needed" column. Label one of them as "Enough standard rooms" and "Enough deluxe rooms". In the columns use an IF function to automatically say "Yes" or "No.

   Extra credit: 3 points. Investigate the AND function and add another column with the heading "Enough rooms". This column will say "Yes" if they have both enough standard rooms and enough deluxe rooms. Otherwise it will say "No".

   Format the page in an attractive way using techniques from the first 3 chapters. Because the data in this problem is subject to change, make sure the data values that can be changed are colored differently than the formulas using that data items. Note the table will fit in the memo better to use if you make the columns narrower!

   Now add the following charts. Each chart must be designed to convey information and include a chart title as well as titles for the category and value axes. Legends are required when needed.

   1. * Embed a bar chart called "Cost for Rooms Needed" that shows the cost of the rooms at the various hotels. Place the chart under the data tables and resize it to be about the same width as the larger table. Hint: Is a legend useful in this chart?
   2. ** Embed a chart that shows the room cost (both standard and deluxe) for each hotel. Place under the first chart and resize it to match.
   3. ** On its own chart sheet, create a chart that shows both the Number of Standard Rooms and the Number of Deluxe Rooms in such a way that it emphasizes the total number of rooms in each hotel. Add a text box and arrow to point out the hotels that have an adequate number of rooms. Rename the page appropriately and move its tab right after this problem's spreadsheet.

   Analyze the table and charts and pick the hotel meeting the requirements that will be the cheapest. In the table, format its row with a red font to emphasize it. * In the first chart, add an arrow and text box that points out your hotel choice.

   *** A partially completed memo
   http://www.cs.plu.edu/courses/csce115/fall06/download/excel/Chapter 3 Mini Case - Convention Planning.doc
   is available on the class web site. Complete the memo by replacing the question marks by your name, date, and recommended hotel and link the data tables and first chart to the memo. (The columns with "Yes" or "No" is optional. They can be omitted in order to make the tables fit the memo.

4. You have changed jobs and now are working for a consultant who is studying the hotel industry. Your employer wants you to use the Hotel Analysis page to carry out the following:

   Add a columns titled "Total Daily Revenue (100% capacity), and "Average room cost".

   Write formulas for the three columns without data and for totals for those columns for which totals are appropriate

   Under the total row, add rows for Average, Median, Maximum, and Minimum and write formulas for each column

   Format the page in an attractive way using techniques from the first 3 chapters. Because the data in this problem is subject to change, make sure the data values that can be changed are colored differently than the formulas using that data items.

   Add a label "Number of Hotels" and a formula for it below the Minimum row.

   * Add an embedded bar chart that shows the Average Room Cost for each hotel. Resize the chart to have a width nearly equal to the table width.

   * Create an embedded chart that shows for each of the hotels, the proportion of total number of rooms in that hotel to the to the total number of rooms available in all the hotels. Label each item with the hotel name and percentage. Put the chart under the previous one and size it to be about the same size.

   ** Create a chart showing Available Rooms by Hotel and Type on its own chart page. Hint: to move the tall columns to the back, after creating the chart double click on one of the "Number of ... Rooms" labels. On the "Scale" tab page, check :"Series in reverse order". Rename the page appropriately and move its tab right after this problem's spreadsheet.

* Uses techniques from hands-on exercise 1. ** Uses techniques from hand-on exercise2. *** Uses techniques from hand-on exercise 3. The formulas and formatting are based on techniques from Chapters 1 and 2.

Additional Requirement

• 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.
• Your name must appear on each sheet in the workbook.

Grading:

Your spreadsheets will be graded on meeting the requirements, correctness, general looks, and impression they leave the reader. The memo for the "Convention" problem will be graded on picking the right hotel and readability as well as correctly adding the links to the table and chart.

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 the worksheets.
6. Select "Lab 4" 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.

Midnight, Monday, October 9. 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: Oct 2, 2006