Skip to main content

Driver-Based Modeling

 

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.

 

A picture containing text, person, indoor

Description automatically generated

 

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

Popular posts from this blog

Game of Data - A Story of Muffins and Metrics

I’ll begin this post with a confession: I don’t have the best dietary habits (though I swear I’m working on it). My office has a self-service cantina stocked with a variety of foods including candy bars, soft drinks, frozen meals, and, stored in a round wicker basket, the most addictive muffins on the planet. Many mornings, despite my best efforts, I cannot fight the urge to grab my $1.80 muffin from downstairs before plowing into the depths of spreadsheets and ad hoc reports. It’s an addiction, but at least it’s not crack… right? Addictions and self-control aside, nothing frustrates me more than when that little wicker basket is empty when I’ve already committed to quenching my muffin madness. What’s even more frustrating is when I see the vendor’s truck parked outside the loading dock and the rep inside the cantina early in the morning only for the muffin basket to be empty… again. I should take that as sign that it’s time for me to squash my sweet tooth once and for all, but

Making the Budget Process Effective

For many of us in the Finance world, “budget season” is a meaningless measurement of time because we always seem to be in the middle of working on The Budget. Whether we’re looking at capital requirements for the next one, three, five, or even ten years out, planning head counts, or building pro formas, finance professionals spend a considerable amount of time planning for the future. Unfortunately, our prognostications rarely hold true. As actor-director-writer extraordinaire Woody Allen once said “if you want to make God laugh, tell him your plans.” With all the time spent on planning and sharpening the pencil just to miss the mark, why do we even bother with budgeting?  As always, the reality is clouded in shades of grey. No business can truly operate for long without some semblance of a plan. Try taking your latest app idea to the angels of Silicon Valley promising your potential investors that they’ll get their money back “eventually”. Ultimately, we’re forced to admit that p

Explaining Variances

As FP&A professionals, we’re tasked with relating the story of the business to the numbers in the P&L. Often, however, the story has many twists and turns that can be hard to break down. Seeing average cost per unit rising drastically over a short period of time can be alarming, but breaking it into the component (i.e. rate, volume, mix, etc.) can help us understand whether the change is a matter of poor management or favorable shift in business volume.   One of my favorite ways to present variance analysis is by breaking the variances down to their components – or drivers. For instance, in a manufacturing scenario, your drivers of expense might be wages & benefits, material costs, and hours worked. I’ve broken such a scenario down for you below which can explain the approach.   In this scenario, a manufacturer ran production for an order of one of its products   Budget Production Cost $73,000  Actual Production Cost $71,100  Cost Higher/(Lower) ($1,900)   On the surface, p