Excel Case #3: Megan Davis Convention Center1

CMIS 342 Revised: 02/14/2016

Page 1 of 16

Excel Case #3: Megan Davis Convention

Center

Case Description and Instructions

SKILLS CHECK

You should review the following areas:

SPREADSHEET SKILLS

 AVERAGE, IF, VLOOKUP Functions

 Get External Data From MS Access

 Cell and Worksheet Formatting

 Microsoft Query Wizard

 Date Calculations

 Nesting Functions

 Complex Formulas

 Pivot Tables & Pivot Charts

Case Overview

The Megan Davis Convention Center (MDCC), located on the outskirts of a metropolitan area, is

a popular attraction for corporate meetings, special events, and educational seminars. The

convention center's location, view, facilities, and outstanding reputation attract individuals and

corporations from all over the United States. To ensure a reservation, the convention center's

clients will often book rooms a year or more in advance.

Mavis Billingsley, the convention center's events coordinator, is responsible for scheduling the

meeting rooms and helping MDCC's clients plan their special events. Ms. Billingsley currently

uses the MDCC Reservations database to track the convention center's meeting room

reservations. She would like to have this information in a form that she could analyze. You

offer to import the reservation data from the Access database into an Excel workbook for her.

She thinks that would be great!

Scenario Details

The Megan Davis Convention Center currently has five standard meeting rooms, a conference

center, a boardroom, and an auditorium. A client may book one or all of the rooms. When

booking a meeting room, the client may request a particular seating arrangement, such as

circular, classroom, lecture, or U-shape. The number of available seats within a given meeting

room is dependent upon the seating arrangement. Figure 1 shows the types of seating

arrangements and the seating codes used for these in the database.

This case is based on Lisa Miller (2009), MIS Cases Decision Making with Application Software, Fourth Edition,

Pearson Prentice Hall, Upper Saddle River, New Jersey.

CMIS 342 Revised: 02/14/2016

Page 2 of 16

Figure 1: Available Seating Arrangements

MDCC Available Seating Arrangements

Seating Arrangement

Code

Circular

Classroom

Lecture

U-Shape

Each day, Ms. Billingsley fields calls from potential clients requesting information about room

availability, room capacity, and charges. Convention center clients are quoted either a

standard, advance, or special rate. The standard rate applies to all bookings that are made less

than six months in advance. The advance rate applies to clients who book six months or more

in advance, and the special rate is a negotiating tool used to attract large, highly recognizable

companies or organizations to the Megan Davis Convention Center. Figure 2 summarizes the

meeting room rates.

Figure 2: Meeting Room Rates

MDCC Daily Meeting Room Rates

Room Code

Meeting Room

Advance Rate

Standard Rate

Special Rate

Auditorium

$2,459.75

$2,966.00

$1,776.00

Boardroom

$1,953.50

$2,591.00

$1,371.00

Conference Center

$2,516.00

$3,241.00

$1,941.00

Alabama

$1,888.37

$2,346.00

$1,328.00

California

$2,028.50

$2,391.00

$1,463.50

Colorado

$2,009.75

$2,366.00

$1,416.00

Florida

$1,988.25

$2,341.00

$1,431.00

Georgia

$1,972.12

$2,316.00

$1,472.25

The reservation data that Ms. Billingsley needs to analyze are currently stored in the

Reservation Details table of the MDCC Reservations database. In designing your spreadsheet,

you sit down with Ms. Billingsley to discuss the data contained in the Reservation database,

Reservation Details table. Through that discussion, the two of you determine that the

spreadsheet should include some columns that the database table does not; these are

elements that can be calculated from the data in the Reservation Details table.

Specifically, the two of you decide that you should add these columns after you import the

database table: how many days in advance each reservation was made, the daily room charge

for the reservation, number of days the room is reserved, and the total charge for the room

reservation.

In the Reservation Details database table, each reservation carries the date the

reservation was made, along with the start date and the end date of the room

reservation. So you know you can calculate the days in advance and the days rented

from those dates.

CMIS 342 Revised: 02/14/2016

Page 3 of 16

The daily room charge column that you are to add specifies the daily meeting room rate

that the customer is charged for a particular meeting room. To determine the daily

room charge, you can use the room code (e.g., AU or CO) and the rate (i.e., Advance,

Standard, or Special) to look up what the daily charge would be. You envision Figure 2

as the look-up table and decide to place it as a separate sheet in the workbook.

The number of days the room is reserved can be calculated using the reservation start

and end dates. For example, if the reservation start date is

The total charge column is to show the total charges for the meeting room, based on

the number of days that the room is reserved by the client and the daily room charge.

For instance, if the daily room charge is $1,735.00 per day and the days rented is 2 days,

then the total charge amount is $3,470.00.

Design Considerations

To complete the workbook needed, you will open a “starter” workbook with two worksheets.

Then, you will import data from a Microsoft Access database and create additional worksheets

as required in the instructions provided.

Case Questions and Deliverables

You are to prepare the workbook for Ms. Billingsley. When you show it to her, she will likely

have some analysis questions that you can help her answer using the workbook.

Your instructor will provide specific directions for completing this case. In addition to an Excel

Workbook, you may be asked to respond to specific questions that will require you to interpret

the outputs of your worksheet, to perform additional analyses by modifying key inputs, to

create new charts or graphs, or to add additional worksheets.

CMIS 342 Revised: 02/14/2016

Page 4 of 16

Excel Case #3: Megan Davis Convention

Center

Instructions

Be sure to begin this assignment by downloading the “starter” file

(Excel Case 3 Start File.xlsx) and the Microsoft Access database

(MDCCReservations.accdb) containing the data you will import into

your worksheet! Download and save a copy of that file to begin your work. REMEMBER:

unless you have Microsoft Access installed on your computer, you will not be able to open the

database file (MDCCReservations.accdb) – but that’s okay! You will simply download the .accdb

file, save to a location where you can find it, then import into Excel.

Case Background and Scenario: Read the background information about Excel Case 3: Megan

Davis Convention Center (MDCC) to grasp the business situation you are to address. This fourth

Excel assignment requires you to use and expand your Excel skills to address Ms. Billingsley’s

questions.

Design Specifications:

Download the Excel “starter” workbook and the MDCC Reservations Access database files – be

sure to save these files with meaningful names in a location where you can find them! The first

thing you need to do is import the reservations data from the Access database file into your

Excel worksheet. To do this:

1. Open the Excel “starter” workbook and select the A1 cell in Sheet1. A1 will become the

beginning cell for the import that you are about to perform. Import the Reservation Table from

the MDCCReservations.accdb using the Microsoft Query wizard. The Microsoft Query wizard is

one way to import data into an Excel worksheet, and it offers you more control over the import.

For example, it lets you select a portion of the data to import.

To access Microsoft Query in Excel 2013, from the Data menu select Get External Data From

Other Sources then From Microsoft Query (see Figure 3).

CMIS 342 Revised: 02/14/2016

Page 5 of 16

Figure 3: Begin import of Access data

In the Choose Data Source dialog box: Choose “MS Access Database*” and make sure that Use

the Query Wizard to create/edit queries is checked, then click OK (see Figure 4).

Figure 4: Choose Data Source

In the Select Database dialog box: Navigate to the location where you saved your copy of the

MDCC Reservations Access file, select the database in the left column, then click OK (see Figure

5).

CMIS 342 Revised: 02/14/2016

Page 6 of 16

Figure 5: Select Database

In the Query Wizard – Choose Columns dialog box, first select the Expand button (+ sign) so that

you can see all of the fields in the table (See Figure 6).

Figure 6: Query Wizard – Choose Columns

You want to bring all of the fields into your Excel sheet. When the field list is expanded and

tblReservationDetails is selected, pressing the > button should choose all of the fields for

insertion into your Excel worksheet. If not, just select each field and move it over into the

insertion list with the > button (see Figure 7). Click Next.

CMIS 342 Revised: 02/14/2016

Page 7 of 16

Figure 7: All columns selected

The Query Wizard – Filter Data dialog box allows you to specify only certain rows (i.e., records)

of data be inserted into your worksheet. You want all of the records from the database, so you

do not need a selection parameter here (see Figure 8). Click Next.

Figure 8: Query Wizard – Filter Data

The Query Wizard – Sort Order allows you to sort the records before you bring them into your

worksheet. Let’s sort this data by meeting room. Select “Room#” from the Sort by drop-down,

and indicate Ascending order (see Figure 9). Click Next.

CMIS 342 Revised: 02/14/2016

Page 8 of 16

Figure 9: Query Wizard – Sort Order

The Query Wizard – Finish defaults to Return Data to Microsoft Excel (see Figure 10). Sounds

good – Click Finish.

Figure 10: Query Wizard – Finish

The Import Data dialogue box defaults to placing the data into a regular Table in the selected

worksheet. Notice that the default also specifies the cell that you selected prior to starting the

wizard (see Figure 11). Sounds good – Click OK.

CMIS 342 Revised: 02/14/2016

Page 9 of 16

Figure 11: Import Data

The data returned to Microsoft Excel will include a header row (with filter enabled) and

additional formatting. You want to remove the header row and the “banded” formatting of the

rows. To do this, go to the Table Tools/DESIGN tab, Table Style Options, to deselect Header Row

(which will also deselect Filter Button) and to deselect Banded Rows (see Figure 12).

Figure 12: Remove Header, Banded Rows, and Filter Button

Then, if your rows have a background color, use the Table Styles area to select a style that does

not have a background color (see Figure 13).

CMIS 342 Revised: 02/14/2016

Page 10 of 16

Figure 13: Background color removed

Name this worksheet Reservation Data. You will notice there is another worksheet already

there. Ah yes, you asked your ever dependable colleague (that would be me ) to create that

sheet. Don’t delete the Rate Table sheet! You are going to use it very, VERY soon.

Import complete! Good time to save your Excel file!

Next, you need to format the worksheet and provide the columns of data requested by Ms.

Billingsley:

2. You will be adding columns to the data that was imported – do that first. Then format the

worksheet title and add column titles (see Figure 14):

 “Reservation Days in Advance” column, place it between the Reservation Date

column and the Reservation Start Date column.

 Add three columns on the right: Daily Charge, Days Rented, and Total Charge.

Format the currency columns appropriately.

Select a date format for the three Date columns that does NOT show hours:minutes.

Set your borders in the heading and the body of the sheet appropriately.

Figure 14: Add and format columns and headers

3. Calculate Daily Charge:

While you were at lunch, your ever dependable colleague  created a Rate Table sheet,

containing the three rates (advance, standard, special) for each room in the MDCC. Using

the Rate Table, you need to construct a nested function formula (oh boy!) to compute the

Daily Charge for each reservation. Your ever dependable colleague already set up the range

name “RateData” in the Rate Table; this makes your VLOOKUP function a little easier to

create.

CMIS 342 Revised: 02/14/2016

Page 11 of 16

Essentially, the ROOM Code of the row you are working on in the main sheet directs you to

the appropriate row of the lookup Rate Table, and the RATE Code of the row you are

working on in the main sheet tells you which of the three price columns in the look-up table

you should use (ADvanced, STandard, or SPecial). So your nested IF logic goes like this:

IF Rate Code is “AD”, you head to the lookup Rate Table with the ROOM Code to find the row

of the lookup table for that ROOM Code; you bring back the value in the Advance rate

column of that row of the lookup table.

IF Rate Code is “ST”, you head to the lookup Rate Table with the ROOM Code to find the

row of the lookup table for that ROOM Code; you bring back the value in the Standard

rate column of that row of the lookup table.

IF Rate Code is “SP”, you head to the lookup Rate Table with the ROOM Code to

find the row of the lookup table for that ROOM Code; you bring back the value in

the Special rate column of that row of the lookup table.

If the Rate Code is something other than AD, ST, SP, well, that shouldn’t happen but if it did,

you’d want to show a Daily Charge of $0.

As an example of this logic, when Rate Code is AD and Room Code is AU, the Daily Charge is

found in the cell of the lookup table at the row for Room Code AU and column for Advance

Rate. The value in that cell is $2,459.75.

Below is a quick summary of the IF function:

IF (Test, What happens if test is true, What happens if test is false)

In this particular application, if the first IF tests false, you want to go on to the next IF test.

And thus begins your nesting!

The following is a quick summary of the VLOOKUP function:

VLOOKUP (Value in worksheet to lookup, Lookup table range, Column Number

containing the data that you want to return from the lookup table, False or True

)

This parameter of the VLOOKUP function is Column Number, as in column 1, 2, 3, etc.

In this particular application, we are looking for an exact match of Room Code so you

would code False.

Some syntax rules to keep in mind:

 You are coding a nested formula so don’t forget to begin with an = sign.

 The quotation marks (“ ”) around the Rate Code tests are required because we are

searching for alphabetical values in our lookup table. Technically, anything other

than number values in computer terminology are referred to as nonnumeric literals.

CMIS 342 Revised: 02/14/2016

Page 12 of 16

All searches involving nonnumeric literals must use quotes. NOTE: If you type this

formula in Word, Microsoft adds a lot of behind-the-scenes formatting that causes

problems with copy and paste between Word to Excel – especially with quotation

marks – depending on the font you are using. I recommend developing this formula

using Notepad to work through the logic, then copy and paste your formula into

Excel.

 The comma placement in the nested function above is critical. The commas separate

each required part of a function as well as separating one function from another.

 The parentheses must be properly paired. The good thing about this is that Excel is

smart enough to try to correct this for you. Accept its help  !

4. Calculate Reservation Days in Advance and Days Rented:

Subtract Reservation Date from Reservation Start Date to determine Reservation Days in

Advance.

Use similar logic to compute Days Rented, BUT in order to arrive at the number of days the

room was rented you need to be counting both the Start Date and the End Date. So you will

need to add 1 to the result to arrive at the number of days the room was rented:

(Reservation End Date – Reservation Start Date) + 1

Show results in both of these columns as numbers (no decimal places).

5. Calculate Total Charge: Create a formula for this column that computes total charge (which

is days rented x daily charge).

6. Add a row at the bottom of the Reservations Data worksheet and label it Averages.

Compute averages for these columns: Daily Charge, Total Charge, Reservation Days in

Advance, Days Rented. Show averages for the “Days” columns as integers (no decimal

places). Bold all cells in this row.

This is a good time to save your Excel file! Because of the number of rows in this worksheet, I

show you only what rows 1-13 and 58-68 should look like after you have completed all

calculations and formatting (see Figure 15).

CMIS 342 Revised: 02/14/2016

Page 13 of 16

Figure 15: Completed Reservation Data worksheet

…

Finally, use the Excel workbook to help Ms. Billingsley’s analyze the data by Room:

7. Ms. Billingsley is interested in looking at information by room code. Specifically, she would

like to see BY ROOM

 How many reservations where made under each Rate Code

 How many reservations were made with each Seating Code

 Average Total Charge for each room

You can set her up with a pivot chart and table for each of those (Rate Code, Seating Code,

and Total Charge). Each “PivotChart & PivotTable” should be placed in a New Worksheet.

The worksheet names should be: Rate Frequencies, Seating Frequencies, and Total Charge

Averages.

When creating a pivot table and chart, remember to select the entire data area and column

headers. Do NOT include the report title at the top and do NOT include the summary rows

at the bottom.

For the two “count” pivot charts/tables:

 Designate Room Code as the Filter in the Pivot Chart Fields, so Ms. Billingsley can

select which room she wants to see the count for (or can select “All” to see the

count for all rooms).

 Drag the data field to be analyzed (Rate Code or Seating Code) to both the AXIS

(CATEGORY) and the VALUES. Be sure to select the appropriate action in VALUES.

 Display these pivot charts as PIE charts. HINT: PivotChart Tools DESIGN tab gives you

a way to Change Chart Type.

CMIS 342 Revised: 02/14/2016

Page 14 of 16

 Provide a meaningful title on each pie chart. Have the counts display in each pie

slice.

For the Total Charge pivot chart/table:

 You want the Room Code in AXIS(CATEGORY). You want the average of Total Charge

in VALUES.

 Format the average values displayed in the pivot table to proper currency format.

 Display the Total Charge pivot chart as a bar chart.

 Provide a meaningful title on the bar chart. Have the average Total Charge display at

the top of each bar.

In each of these new worksheets, move your pivot chart over to sit under the pivot table.

Below are screen captures for each of the Pivot Chart & Pivot Table worksheets see Figures

16-18).

Figure 16: Rate Frequencies by Room

CMIS 342 Revised: 02/14/2016

Page 15 of 16

Figure 17: Seating Style Frequencies by Room

Figure 18: Average Total Charge by Room

CMIS 342 Revised: 02/14/2016

Page 16 of 16

Case Deliverables:

Your Excel file should consist of 5 worksheets; move them so they are in this order:

Reservation Data

Rate Table

Rate Frequencies

Seating Frequencies

Total Charge Averages

Submit your Excel workbook through the Blackboard assignment link.