1 / 40

Science of Hotel Optimization Rooms Revenue Workshop

Science of Hotel Optimization Rooms Revenue Workshop. Day 1: Data Day 2: Analysis Day 3: Optimization. 50 minute periods. 10 minute break every 50 minutes. http://www.forsmarthotels.com/sohodocs. Ask every question. Dozens of Books, Lectures, Courses in 4 hrs . Contribute examples.

giza
Download Presentation

Science of Hotel Optimization Rooms Revenue Workshop

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Science of Hotel OptimizationRooms Revenue Workshop Day 1: Data Day 2: Analysis Day 3: Optimization

  2. 50 minute periods. 10 minute break every 50 minutes. http://www.forsmarthotels.com/sohodocs SOHO Day 2

  3. Ask every question. Dozens of Books, Lectures, Courses in 4 hrs. Contribute examples. Give me a Pace. I will go back. SOHO Day 2

  4. Day 2 Objectives Hour 1 • Add analysis columns • Models Hour 2 • Let’s Count - Frequency • Demand Analysis – The Manual Way Hour 3-4 • Demand Analysis – The Math Way • Dynamic Optimization (if possible) SOHO Day 2

  5. SOHODAY1.xlsx SOHODAY2.xlsx Before Class In-class Gross up rates column Days_out column Month and Weekday column Add month-weekday classification • Joined tables from Day 1 • Removed Cancel, No Shows, Comps. • Vlookup for COMPANY_NAME • Convert all to text. • Deleted columns SOHO Day 2

  6. Build for OTHERS SOHO Day 2

  7. Nested Excel functions • Function variable is another function. • Combine two or more functions to deliver one answer. • Innermost function first. • Keeps worksheet clean. • Hard to follow. • May slow calculation speed. =ROUND(A4*Sum(A1:A3),0) 1. Sum cells. 2. Multiply by A4. 3. Round to the tenth place. SOHO Day 2

  8. Analysis Column: GROSS_RATE When building nested Excel functions, go step-by-step. • Get the margin with vlookup. • Markup the Net rate • iferror then RATE_AMOUNT • Roundup to nearest 10. GROSS RATE MARKUP =ROUNDUP( IFERROR( [@[RATE_AMOUNT]]/ VLOOKUP([@COMPANY],Margin[#All],2,FALSE) ,[@[RATE_AMOUNT]]) ,0) SOHO Day 2

  9. Analysis Column: ARATE • Roundup to nearest multiple of 25. Analysis RATE =CEILING([@[GROSS_RATE]],25) SOHO Day 2

  10. Analysis Column: DAYS_OUT, ADAYS_OUT Can’t use roundup because same day bookings will become -1. • Subtract Insert from Reserv date. • Round to the next whole number. DAYS_OUT =CEILING( [@[RESERVATION_DATE]]-[@[INSERT_DATE]] ,25) Analysis DAYS_OUT • Round to the next multiple of 30. =CEILING([@[DAYS_OUT]],30) SOHO Day 2

  11. MONTH & WEEKDAY MONTH • Get month number. =MONTH([@[RESERVATION_DATE]]) WEEKDAY • Get weekday number (sun=1) =WEEKDAY([@[RESERVATION_DATE]]) SOHO Day 2

  12. WEEKPART WEEKPART • If weekday number is less than 6 2. Then show “WEEK” for weekday 2. If not, show “END” for weekend =IF([@WEEKDAY]<6,"WEEK","END") SOHO Day 2

  13. PERIOD • Get month number from MONTH column. • Add a hyphen for easier reading. • Get weekpart from WEEKPART column. MODEL PERIOD =[@MONTH]&"-"&[@WEEKPART] SOHO Day 2

  14. OWL’s vision for The Big RM Reset Clerical RM Analytical RM To take data, to be able to understand it, to process it, to extract value from it, to visualize it and to communicate it. Distribute the Right Rates and Manage Inventory. SOHO Day 2

  15. Decision Models Mental Models Math Models Benefits Less biased Complex Rules-based Reusable Benefits • Fast • Simple • Inexpensive Problems • Bias • No Rules • Personal Problems • Assumptions • Expensive SOHO Day 2

  16. The Model “The purpose of models is not to fit the data but to sharpen the questions.” “Remember that all models are wrong; the practical question is how wrong do they have to be to not be useful.” “The approximate nature of the model must always be borne in mind…” George Edward Pelham Box, Empirical Model-Building and Response Surfaces (1987) Samuel Karlin, Eleventh R. A. Fisher Memorial Lecture, Royal Society (April 1983) SOHO Day 2

  17. The Fundamental Model of RM How many units can I sell at each price point? Demand Curve High We’d like to put this relationship into a mathematical model. Quantity (Q) Low Low Prices (P) High SOHO Day 2

  18. The Fundamental Question of Hotel RM How many rooms can I sell at each rate? Hotel Demand Curve High Rooms (Q) Low Low Rate (P) High SOHO Day 2

  19. Not that simple. • Can’t you just go to your data table and count. • Unfortunately, it’s not that simple. • These data points have variation. (L,H) High Rooms (Q) (H,L) Low Low Rate (P) High SOHO Day 2

  20. Let’s Count - Random Events • Every problem in RM involves uncertainty. • Uncertainty means that a process is random. • Website visits • Conversions • Calls to reservations • Booking a room • Group sales • Restaurant visits • Check-in • No shows • Cancellations • We need to count how often we can expect a random event to occur. • How often an event occurs if the FREQUENCY. SOHO Day 2

  21. Frequency Table SQL Code MSQuery Modified Version SELECT `Elements$`.PERIOD, `Elements$`.ARATE, `Elements$`.RESERVATION_DATE, Count(`Elements$`.ROOM) AS ‘COUNT_ROOM’ FROM `C:\SOHODAY2.xlsx`.`Elements$` `Elements$` WHERE (`Elements$`.MARKET_CODE='TRANSIENT') GROUP BY `Elements$`.PERIOD, `Elements$`.ARATE, `Elements$`.RESERVATION_DATE SELECT PERIOD, ARATE, RESERVATION_DATE, Count(ROOM) AS COUNT_ROOM FROM SOHODAY2.Elements WHEREMARKET_CODE='TRANSIENT' GROUP BY PERIOD, ARATE, RESERVATION_DATE SOHO Day 2

  22. Tip: Put calculations on Manual SOHO Day 2

  23. Let’s count – Expected Rooms Sold Frequency – How often an event happens. For each Period: • Sum the number of times each rate was paid. SUMIFS(Frequency[''COUNT_ROOM''],Frequency[ARATE],$A3,Frequency[PERIOD],B$2) • Divide each count by total # of days that the rate appeared. COUNTIFS(Frequency[ARATE],$A3,Frequency[PERIOD],B$2) 3. Rounddown to the ones place and clean the errors. =+IFERROR( ROUNDDOWN( SUMIFS(Frequency[''COUNT_ROOM''],Frequency[ARATE],$A3,Frequency[PERIOD],B$2) / COUNTIFS(Frequency[ARATE],$A3,Frequency[PERIOD],B$2),0) ,0) SOHO Day 2

  24. Demand Curve – The Manual Way Core Assumption – Those that paid a higher price will pay a lower price. Start from the highest rate and add the pickup to the next highest rate. SOHO Day 2

  25. Pivot Chart Always try to graph your data. SOHO Day 2

  26. INDEX(MATCH) 1. Find the row where this is 4. In this column 3. Get the info in this row 2. In this column INDEX($A$133:$A$195,MATCH(B197,B$133:B$195,0)) SOHO Day 2

  27. WARNING We are going to make a huge modeling mistake on purpose. SOHO Day 2

  28. Demand Estimate – The Math Way Using the equation for a line, we can create an equation for demand. y=mx + b Rate Rooms Sold Slope Intercept A measure of steepness. Where the line crosses the y-axis or the number of rooms sold when the rate is $0. SOHO Day 2

  29. We need to extract two points from the data • How many rooms sold when we charge a low rate? (L,H) • How many rooms sold when we charge a high rate? (H,L) (L,H) High Rooms (Q) (H,L) Low Low Rate (P) High SOHO Day 2

  30. Defining an Equation for a Demand Line • Find the two points. • Find the Slope. • Use the slope and one data point to find the intercept. • Rework the equation to find the Price curve. • Use both equations to find the Revenue curve. SOHO Day 2

  31. ARRAY FORMULAS Turn any function into a criteria function. MIN(IF(FREQ[PERIOD]=B$2,FREQ[ARATE],"")) 1. If a row in the ARATE column is related to this PERIOD. 2. Then include it in the search for finding the minimum. SOHO Day 2

  32. Find the Slope. • Change in Rate divided by the change in Rooms Sold • Always constant, always negative. Change in Rooms Sold Change in Rate Low Rate – High Rate Slope = High Rooms Sold – Low Rooms Sold SOHO Day 2

  33. Use the slope to find the intercept For 1-Week, Slope = -.0824 Data Point (100, 102) 102=-.0824(100) + b 102+ 8.244898=b 110.25 =b Rooms = -.0824*Rate+110.25 SOHO Day 2

  34. Graph “1-Week” Demand Equation Now I can analyze any rate. SOHO Day 2

  35. Revenue Curve, 1-Week Rooms = -.0824*Rate+110.25 Revenue = Rate * Rooms Revenue = Rate * (-.0824*Rate + 102.27) Distribute Revenue = Rate * (-.0824*Rate + 102.27) Revenue = -.0824*Rate2+ 102.27*Rate SOHO Day 2

  36. Price Curve, 1-Week Rooms = Slope * Rate + Intercept Rate = Rooms - Intercept Slope Rate = Rooms - 102.27 -.0824 SOHO Day 2

  37. Revenue Graph SOHO Day 2

  38. Derivative of Revenue Curve, 1-Week The derivative of the revenue curve basically helps us calculate where the curve reaches its highest value. Revenue = -.0824*Rate2+ 102.27*Rate Revenue Change = -.1649*Rate + 102.27 SOHO Day 2

  39. Optimal Rate Solve for Revenue Change = 0 -.1649*Rate = -110.24 Rate = $668.56 SOHO Day 2

  40. The math is great, but the model is USELESS. • Aggregating data increases errors. • Break down the model even further. SOHO Day 2

More Related