Driver-based Modeling
Driver-based modeling presents major advantages when budgeting, forecasting, and running scenario analysis. A driver-based model allows you to test out individual assumptions and see how minor (or major) changes may affect your business. Further, a driver-based model allows for more detailed variance analysis and dynamic forecasting. For instance, if you are a hotelier and your hotel’s occupancy changes, then you would expect variable expenses such as housekeeping to change. A great driver of expense in this scenario would be rooms occupied. If your occupancy changes, then the difference in rooms occupied times the cost per housekeeping clean is the amount of variance you would expect to see at month-end.
How do you build a driver-based model?
First, identify the cost and revenue drivers in your business. What data can you get ahold of, and what impact does that data have on your business. While initial drivers may be statistics captured as part of your month-end close (e.g., rooms occupied, manhours, units produced, kWh consumed), you may find it useful to pull data from outside of your business to evaluate the business. A restaurant with outdoor seating may see a decrease in sales based on rainfall totals. Learning how to perform and interpret a simple regression analysis will help you to determine whether a statistic is relevant to your business. More on that in another article.
A particularly important aspect of driver-based modeling is being able to validate that the drivers actually are drivers of expense and that their denominators are the correct denominators. For instance, water consumption can be difficult to pin down. If water is a major constituent component of cost for your business (e.g., in beverage production), you should be able to tie your water expense to volume. However, measuring water expense on a per unit basis for a golf course (e.g., water expense per round played) may be a nonsensical measure. Even in the case of a golf course, water expense might be mostly fixed (the course must be well maintained and properly watered), but periods of heavy rain must be taken into account. Then there is the water consumed by restrooms or food & beverage outlets, or washing golf carts. When trying to pin down a formula for relating consumption to expense, running a simple regression in a tool like Excel may be sufficient to measure the variability of the expense.
Once you’ve collected all the relevant drivers, start putting the model together. This means placing all relevant drivers into the modeling tool (we’ll assume Excel for now), then use formulas to calculate the impact each driver has on your revenue or operating expenses. Let’s take a hotel for example. I’ve listed some of the variables that we may model in our driver:
Expense/Revenue | Driver Calculation | Drivers |
Rooms Revenue | Rooms Sold x Daily Rate | Rooms Sold Average Daily Rate |
Rooms Expense (Housekeeping, Front Desk, etc.) | Cost per Occupied Room x Rooms Sold | Rooms Sold Cost per Occupied Room |
Food & Beverage Revenue | F&B Revenue per Occupied Room x Rooms Sold | F&B RevPOR Rooms Sold |
Food & Beverage Expense | (1-Food & Beverage Margin) x Food & Beverage Revenue | Food & Beverage Margin Food & Beverage Revenue |
Putting this together to calculate our variable profits in this simple example would be
Departmental Profit = (Average Daily Rate – Cost per Occupied Room) x Rooms Sold + (Rooms Sold x F&B Revenue per Occupied Room x Food & Beverage Margin)
Rooms Sold | 3,600 |
| Rooms Sold | 3,600 |
Average Daily Rate | $150.00 |
| Food & Beverage Revenue Per Occupied Room | $60 |
Rooms Revenue | $540,000 |
| Food & Beverage Revenue | $216,000 |
|
|
|
|
|
Rooms Expense per Occupied Rooms | $50.00 |
| Food & Beverage Margin | 25% |
Rooms Expense | $180,000 |
| Food & Beverage Expense | $162,000 |
|
|
|
|
|
Rooms Profit | $360,000 |
| Food & Beverage Profit | $54,000 |
|
|
|
|
|
|
|
| Departmental Profit | $414,000 |
One thing to keep in mind as we work through this model is that our variable expenses may fluctuate over time whether due to some element of fixed expense (e.g., full-time salary exempt employees and related benefits cost), or the level of work involved in the variable component (i.e., more housekeeping required during summer months at a beach-based hotel due to having to vacuum more sand out of the carpets)
What’s next?
Simply put – run the model. Build a budget or forecast, and when you close the books for the month, evaluate the changes in the drivers, and plug those changes into your model. What is the result? Does your updated model result match your month-end? In reality, it likely won’t. However, this disconnect will give you the information you need to fine-tune your model. Do some of your drivers move the needle more than others? Are you missing elements of the business? Use this opportunity to refine your modeling.
While you’ll never be able to perfectly model your business, what a well-tuned model will do is help you to project your business mid-period. If you see changes in your drivers in the middle of the month, you can identify how business will change and use the model output get drive the operations team to action to course correct.
Conclusion
Obviously, there is more to building a model using drivers, but building such a model for your business doesn’t have to be complicated. In fact, I believe it is more valuable to hone in on as few measures as possible to build your model, then agree to those measures with your operations teams. In that way, everyone can understand how to interpret forecast/budget/long-range projections and can even build their own conclusions on the status of the business.
What are your experiences with drivers? How have you refined your modeling over time? Let us know in the comments below!
Comments
Post a Comment