Spring 2019 Excel Assignment #3 Page 2 of 7
- Convention Center Management / IF and VLOOKUP functions
Background You have started working in the Information Systems Department for The Conference
Center, Megan Davis Convention Center at Waltham Woods. The center offers five meeting rooms,
including a 400-seat amphitheater. Each room has three rates: The advance rate, when an organization
reserves the room six or more months in advance; the corporate rate, a negotiating tool to attract larger or
highly recognizable companies to the center; and the standard rate for all other reservations.
To book reservations and conference rooms, the center uses the Hotel Information Systems package.
Your responsibility is to assist the events coordinator with meeting room scheduling and to help the
center plan special events based on recorded information. The reservation agents enter the room
reservations into the system.
You will perform some calculations and manipulation of the data, and answer questions based on the
information you created.
2.1 – The files: Download from Blackboard the Assignment 3 – Convention Center.xlsx workbook if you
have not already done so.
2.2 – Open the Assignment 3 – Convention Center.xlsx spreadsheet.
Make sure to select the first worksheet (Sheet1).
2.3 – Rename the Sheet1 worksheet to Reservations.
Spring 2019 Excel Assignment #3 Page 3 of 7
Background - After looking at the data you realize it does not include the rate for the room rental, the
number of days or the total charge per rental. After inquiring about this, the system manager tells you it
is inefficient to store any data that can be calculated based on existing data.
Your job is to add the formulas and necessary calculations. The daily room charge is based on the room
code and rate code. After viewing the table in the Room Rates sheet you determine that using a vlookup
function would be the best option
Hint: In case you need a Review of VLOOKUP() function, see the Appendix at the end of the
instructions
2.4 – Create a Table Lookup for Room Rates
- Create your lookup table. For this assignment the look up table has been provided for you
on the Room Rates worksheet. Click this worksheet now. - Sort your lookup table in ascending order by lookup code. Here, it’s the room code.
Use then . - Name your table
select the data A5:E9 (we do NOT include column labels)
enter the name Rates in the Name Box (directly above column A).
2.5 – Create the VLOOKUP. - The lookup value is the room code in cell B2,
the table array is the sorted table Rates created above,
the col_index is the column number that will return the standard rate. - Copy the formula down to the last reservation entry
(Hint: double click the fill handle).
BEFORE YOU GO ON – Check that the Room
Spring 2019 Excel Assignment #3 Page 4 of 7
In the Megan Davis case we actually have three pricing choices. An IF statement may still be used, but
it will need to be nested within another IF statement.
If the room rate code (column C) is "Advance" the statement should return $1225.00. If it is "Standard"
it should return $1470.00, and if it is "Corporate" it should return $955.50.
Here is a Model of the Logic.
(Hint: Line comments (not part of logic) start with //)
IF(room rate code="advance", 3, // 3 is the table column for advance
IF(room rate code="standard",4, // 4 is the table column for standard
5) // 5 gets chosen otherwise
(Hint: be careful with spelling!! this IF has to match the values in the rate_code column exactly
e.g. "advanced" is NOT SAME as "advance")
2.7 – Add the Nested IF() to the VLOOKUP() so it will choose the proper Rate_Code - Rewrite the Model above, replacing room rate code, with the proper cell reference for data
in row 2. - Insert the Nested IF into the VLOOKUP().
- Copy the revised formula down to the last reservation. (Hint: Use the fill handle)
- Format the room rate column to currency format two decimal places.
2.8- Compute the Number of days in the reservation from start date and end date
In cell I1 put the Label: Number of Days if it does not already exist. - In I2 insert a formula to calculate the total number of elapsed days between the start and
end date. Format the column a number with no decimal places.
(Hint: Dates are stored as number of days since 1/1/1900, so you can subtract them)
2.9 – Create a Total Charge Column. - In cell J1 type the text: Total Charge if it does not already exist.
- In cell J2 enter a formula that will calculate the rate * the number of days.
- Copy this formula for all reservations.
- Format column as currency with two decimal places.
2.10 Create Column Summaries - At the bottom of the table show the average length of time a client reserves a room (show 2
decimal places) - At the bottom of the table show the average room rate (not revenue) as currency with 2
decimal places. - At the bottom of the table show the average room rate with a Group Discount (not
revenue) as currency with 2 decimal places. - At the bottom of the table show the average room rate with a Additional Discount (not
revenue) as currency with 2 decimal places.
Spring 2019 Excel Assignment #3 Page 5 of 7
- Convention Center Management Additional Discount
Background - The Convention Center has various offers to incentivize their meeting space; this month,
they have opened up a discounted offer for companies that bring a larger group, and also for groups
booking a longer stay. These lower rates are included on the worksheet Discounted Rates. You will need
to sort the data in the Discounted Rates worksheet and give the data range a name, just like you did on the
Room Rates worksheet.
3.1 – Create Large Group Discount
- Sort the Discounted Rates Table and name it discountrates
- In cell E2, enter a formula that
if the number of attendees is < 250, put the regular rate (D2) in the cell.
Otherwise, they qualify for a discount,
so use a VLOOKUP formula to look up the room code in the discountrates table.
(Hint: Copy the vlookup from the room rates column and edit it)
BEFORE YOU GO ON: the Group discount rate for Acura would be $975.50 (unchanged)
and the rate for Cole Muffler would be $1200.
3.2 – Create a special additional discount for long stays. - In Cell F2, Additional Discount data write a formula that will deduct 10% from the best
rate (E2) if the number of days of the booking is >4.
If the number of days is 4 or less, use the best rate given in cell E2.
For example, the Additional Discount for Acura would be $975.50 (unchanged), and the rate
for Air France would be $513 (10% discount).
This is the end of the Assignment
Be sure to upload your completed workbook to the Blackboard link provided.
Spring 2019 Excel Assignment #3 Page 6 of 7
Appendix: Lookup Function Tutorial Page
in case you need a review
Excel has three lookup functions: LOOKUP (depricated so don't use), VLOOKUP and HLOOKUP. The
VLOOKUP (or Vertical Lookup) function is used for lookup tables in which the compare values are
placed in the table’s first column. The HLOOKUP (or horizontal lookup) function is used when the
compare values are placed in the table’s first row. In this lab we will work with a VLOOKUP function.
There are several steps that you will always need to follow in order to successfully use the VLOOKUP
function, so we include the following chart for you:
Example of the VLOOKUP formula:
=VLOOKUP(lookup_value, table_array,col_index, [range_lookup])
lookup_value = the value you want to compare against the list
table_array = the name of the entire table you wish to use to evaluate your lookup value
col_index = the number of the column in the lookup table_array containing the value you
want to retrieve.
Range_lookup = is optional and is used if you wish to lookup values within a range.
To return the name of the course in cell B1 in the example below use this formula:
=VLOOKUP(A1,Courses,2)
To return the number of credits in cell B2 in the example above use this formula:
=VLOOKUP(A1, Courses,3)
Create a lookup table
Sort the table by lookup value (in ascending order) by lookup
code
Name the table (data only – no headings)
Build the LOOKUP function
Copy formula to the rest of the range
Check to make sure all values are correct: do they make sense?
Spring 2019 Excel Assignment #3 Page 7 of 7
Appendix - Nested IF Tutorial Page
in case you need a review
This section is a tutorial on writing nested IF statements. Read and understand this overview; further
the instructions will guide you when you’ll actually build the IF function in Excel.
Let’s review the standard IF conditional statement.
Let’s assume all rooms have two prices: standard and corporate.
In this simplified case, if the room isn’t standard it is corporate.
The standard price is $600 and the corporate price is $500.
This conditional statement can be expressed as an Excel IF function.
IF(quote = “standard”, 600, 500)
The statement above contains three comma separated parts:
- A condition, which can be true or false IF(quote = “standard”, 600, 500)
- What to put in the cell if the condition is true IF(quote = “standard”, 600, 500)
- What to put in the cell if the condition is false IF(quote = “standard”, 600, 500)
The revised If statement would look like this:
=If(quote = "advance", 1225.00, If(quote = "standard", 1470.00, 955.50 ))
You can use this sort of nesting to build a three way test for the rate column in the vlookup table.
IF(quote="advance",3, IF(quote="standard",4, IF(quote="corporate",5, 4) ) )
This spells out all three choices. Can you see that if the quote is not any of them, the rate defaults to 4
which is standard?
Sample Solution