A report
INeal
LansingConventionCenter.docxLansing Convention Center (LCC)
Case 4, Group 4
BIS 255
Due: 4/23/18
Executive Summary:
The assignment given to group 4 was to provide data analysis through Excel to help Phil Vischer, the coordinator for Lansing Convention Center (LCC). This is necessary because it is an easier way of tracking and analyzing the rooms they rent out through Excel instead of the Reservation database Phil uses now. The reason they are so high in demand and wanting to make a switch to Excel is because of LCC’s: location, facilities, and outstanding reputation. With more companies booking months in advance the switch is needed now; by using Excel Phil will now be able to do analysis for things like Daily rate, Total discount, and a Total Charge more efficiently. It will also allow Phil to analyze the pivot tables that will be made to help him see which rooms are used most and bring in the most money to LCC.
To help group 4 with the data Phil provided an access file that allowed us to copy and paste the current data into excel. From this we were able to find the data through the given formulas. In excel we were tasked with finding the Daily rate, how long the visit was, the discount rate, total charge. By using various tools such as VLOOKUP and IF statements we were able to fill in the blanks for him. After completing the main reservation table, we were able to complete specific tasks asked of us by Phil. Some of these tasks using the filter tool on Excel were showing reservations on January 4 and also showing how often the auditorium and boardroom were booked. After completing this step, we were able to make pivot tables. Pivot tables are great for showing the data that is relevant to what is being asked of us. The Pivot tables we made included: The count of reservations by room number and we also added a bar graph for visual representation. After that we completed a table showing the count of reservations by seating codes; we also included a pie graph with percentage labels to show which were thriving. Finally, we made pivot tables to show the average discount given by room, and how many times and how much money made from a client who booked with us.
After completing the analysis that was required Phil wanted us to complete one last task for him. The task that was to be completed was to find out how much more money LCC would have made if reservations were at standard rate. To do this we made a pivot table showing the total days by room ID for reservations with advance rates. After calculating the advance discount, we used the VLOOKUP function to show this advance discount and finally multiplied it with the days to give us a total. After adding all of this up we learned that Phil could gained $9,250 more if the rooms were at standard rate. In conclusion, we have provided LCC and Phil with the materials that will help them in their day-to-day operations, allowing them to better analyze numbers and look at trends while continuing their wonderful excellent service.
Introduction-Background:
Phil Vischer, the event coordinator for the Lansing Convention Center (LCC) is responsible for scheduling the center’s meeting rooms and fulfilling client’s requests. LCC is a popular venue for meetings and special events. LCC is very popular among the Midwest due to the convention centers outstanding reputation, facilities, and location. The center itself has five meeting rooms, an exposition hall, a boardroom, and an auditorium. When a room is booked the client also picks one of the four seating arrangements: Lecture, Presentation, Seminar, or Small Groups. Phil has reached out to our group because LCC is becoming booked more and more months in advance, and the system he is using now is not allowing for him to analyze the data the way he wants. This project will allow our group to help LCC and Phil be able to analyze any trends that come along with their data using Excel and allow for their company to flourish further.
Purpose:
The purpose of the project is to help Phil to be able to analyze the data he possesses at a faster rate than what he is able to do now. The tasks assigned to completing the project were finding the daily rate, total days of stay, the discount, and the total charge. From this we were able to use the filter tool to allow for Phil to see certain days and room bookings. Finally, we created pivot tables allowing for him to a representation of his data. After completing the tasks assigned we showed Phil how much money he could have earned if he charged strictly standard rate and not advanced. By doing this we are helping him invest into the success of his company and analyze data more effectively.
Analysis:
With the help of Excel, our group was able to help Phil use the data he has provided and take it even further. Before beginning the tasks, we were first required to go to Phil’s records on Access and then copy and paste them into Excel. The records that were given looks like this:
From the 64 reservations that were made we were able to see reservation number, the room ID, client number, rate code, reservation date with when they started and ended, and finally the seating code. Besides giving us this information we were also given a lookup table for the room ID’s pictured below:
|
RoomID |
RoomDescription |
AdvanceRate |
StandardRate |
|
AU |
Auditorium |
$2,250.00 |
$2,500.00 |
|
BD |
Boardroom |
$1,575.00 |
$1,750.00 |
|
EH |
Exposition Hall |
$2,925.00 |
$3,250.00 |
|
M1 |
Meeting Room 1 |
$1,800.00 |
$2,000.00 |
|
M2 |
Meeting Room 2 |
$1,800.00 |
$2,000.00 |
|
M3 |
Meeting Room 3 |
$1,800.00 |
$2,000.00 |
|
M4 |
Meeting Room 4 |
$1,800.00 |
$2,000.00 |
|
M5 |
Meeting Room 5 |
$1,800.00 |
$2,000.00 |
After we were given this information we were tasked with finding the daily rate, the total amount of days, the discount, and the total charge. To do this we use the information provided by Phil and the lookup table to complete the analysis. You are able to find the daily rate by using the VLOOKUP function and the IF function within the VLOOKUP to retrieve the rate based on room ID and Rate Code. You are able to see how the function is supposed to be properly set up below.
For days, you take the (End Date-Start Date) +1. To find Discount you use the IF function again, and if the reservation is more than 5 days you are able to receive a $100 discount a day. This function is also pictured below.
Finally, you find the total charge by taking (Days * Daily Rate)- Discount. Below you will see a preview on how the results should look as a whole.
After completing this task for Phil, we were able to use the filter tool on Excel and find the reservations just made on January 4, 2015. We then copy and pasted the results onto a new sheet. Similarly, we were able to use the filter tool again to find all the reservations made just in the boardroom and auditorium. We also copied and pasted these results onto a new sheet for quick access. The results from both these tasks are shown below:
Once we completed the first 5 tasks assigned of us, Phil wanted to know how far in advance, on average, clients make their reservations. First, we added the title, days in advance, onto our reservation worksheet. Next, we calculated each number by using the start date minus the reservation date then adding 1. After finding each number we took the average by using the AVERAGE function. The results are shown below.
The group was then asked to make 4 pivot tables to help Phil see various data points. To create a pivot table all you have to do is highlight the table you want selected, in our case the Reservation table, and then go up to the top and select insert and then pivot table. After clicking this you will be able to choose the relevant data and Excel will do the magic for you.
The first pivot table we made was a count of reservation number by room ID. In this table, we needed the reservation number and room ID. We are then able to see the total number of reservations made by room type. We then created a bar graph for him to see this information visually.
The second table we created was a count of reservation number by seating code. Similarly, to the first table we need the reservation section, but this time we need the seating code section as well. After selecting this we were able to see a count of reservations by the seating code. After completing the table, we made a pie chart with percentages to show Phil how often each type is being used.
For the third table, we found the average discount by room ID. The criteria needed is the room ID and discount. The information found will allow Phil to see what room is being discounted the most on average.
The final pivot table we made we were able to see the total number of rooms each client reserves. After we find this we will be able to see the sum of the total charge by client. The criteria that is needed is client number, reservation number, and total charge. This allows Phil to see what customers bring in the most profit and possibly offer them deals in the future. Below is a snippet of the findings.
After finishing the work Phil had for us, he asked to complete one more task. The task assigned was to find out how much more money LCC would have made if the reservations were standard rate. To find this we first started by making a pivot table. This pivot table included the days section and room ID section. For this pivot table, we also have to include the rate code: advance so the information doesn’t show standard. After completing the pivot table, we went back to the lookup table and added a column titled advance discount. We found this “advance discount” by taking the advance rate minus the standard rate. Once this was completed we went back to our pivot table and created a column off to the side labeled advance discount. From this we used the VLOOKUP function once more to retrieve the advance discount from the lookup table. Finally, we added a total discount column and calculated this rate by multiplying the days by the advance discount rate. After finding each we added them together to get the total sum. We found that LCC could have gained $9,250 more if they were just using the standard rate.
Conclusion:
In conclusion, our group helped Phil learn how to utilize the data he collected and see various forms of it. After collecting the data and finding how much each room costs per booking we were able to create pivot tables full of data. It was from the pivot tables we created that Phil will now be able to see what room is being utilized the most as long with the seating arrangement. Also, he will be able to now see the total average discount by room showing what rooms will get booked for the longest period of time due to discounts only starting after 5 days. The last pivot table shows the revenue being brought in by each client. This allows Phil to cater to their needs in the future and make sure they are taken care of first and foremost due to their high volume of booking. Lastly, we helped Phil realize how much more money he could have if he had not used the advanced rate and used the standard rate. It is with the material we have provided LCC and Phil that will help them in their day-to-day operations, allowing them to better analyze numbers and look at trends while continuing their impeccable service.
