From: Nia Foster, Revenue Manager, Lakeshore Stays, a fictional hotel group.
Nia wants to understand cancellations before changing booking terms. A large channel may have the most cancellations without the highest rate. Booked room revenue includes reservations that later cancel and is not realized revenue.
Data: one row per booking, with a unique Booking ID, Hotel, Channel, Booking Date, Arrival Date, Cancelled, Lead Days, Nights and Room Revenue. The Boolean Cancelled flag records the final booking outcome. All observations in this exercise have matured.
Decision: compare channels and lead-time bands, distinguish room revenue from revenue kept after cancellations, and recommend a controlled policy test. This observational source doesn't show whether a deposit policy causes better results.
Start with the denominator. There is one row per booking, so the average of a zero-or-one cancellation flag is a booking cancellation rate. If a future source contains one row per night, the same expression would weight longer stays more heavily.
Build the investigation in the order requested. Each step continues from your previous workbook; clearing a worksheet keeps the calculations you have created. Examine channel volume before its rate, then separate booked revenue from revenue tied to noncanceled reservations.
The final decision has to respect what the source can show. A channel comparison can suggest where to test a change. It can't prove that a new deposit rule will work.