Computer Science and Computer Engineering 120
Computerized Information Systems
Fall 2006



Lab 4

Charts

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 a 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.

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 text.
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.

   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 if you make the columns narrower.

   To help you determine which hotels have an adequate number of rooms for the convention, use conditional formatting in the two "Number of ..." columns to make the values meeting the corresponding needs bold italics.

   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 arrows to point out the hotels that have an inadequate 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/csce120/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.

4. You have changed jobs and now are working for a consultant who is studying the hotel industry. Your employer want 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 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.

   * 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. Resize the chart to have a width nearly equal to the table width.

   * Add an embedded bar chart that shows the Average Room Cost for each hotel. Put the chart under the previous one and size it to be about the same.

   ** Create a chart showing Available Rooms by Hotel and Type on its own chart page. Hint: to move the tall bars 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.

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 (csce120-brink-secX, Fall 2006 where X is 1 or 3) 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 for the Movies that contains both worksheets.
6. Select "Lab 4" for the assignment and then click "Upload".
7. Repeat the upload for the memo for the Convention problem.
8. Double check to make sure both files have been uploaded.
9. Click "Sign Off" near the top right hand corner of the window.

Due date

Midnight, Wednesday, Oct. 4. 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