Forecast Operating Expenses

In a previous post, I discussed how I forecast headcount expenses and the related physical heads and benefits. Of course, this is just one piece of the corporate expense puzzle. It is also important to have an easy and quick way to forecast operating expenses without hard coding numbers month-by-month.

Excel Template Can Be Downloaded Below

Model Setup

In my financial statement model, I have Excel tabs dedicated to certain functionality. For example, a tab dedicated to forecast bookings, accounts receivable, deferred revenue and so on. For the P&L, I tend to have dedicated Excel tabs for bookings, revenue, wages, operating expenses, and non-operating expenses.

Notes on the Balance Sheet

On the balance sheet side, it is worth mentioning that I don’t trend all of the balance sheet captions or categories. If the category is large and complex enough such as deferred revenue or accounts receivables, I will have a tab dedicated to just that category. It is a little more work but it makes your model more accurate and helps develop your knowledge of these numbers.

Non-wage Operating Expenses

I have a tab dedicated to budgeting and forecasting department operating expenses excluding wages. I set up the spreadsheet so that at the top of the page I can sum my expenses by different expense categories (i.e. Gross Wages, Travel), by department (i.e. R&D, Sales) and by major expense type (i.e. Operating Expense, Non-operating expense). Depending on the level detail you require, you may use all or just one of these summary tables.

Below my summaries I have rows dedicated to forecasting each department’s expense. In past models, I had tabs for each department but that becomes inefficient if you need to make formula changes and/or add/delete departments.

I don’t like trending, but I find that most of the smaller expenses can be trended with relative accuracy. I created five formula types that help me automate the spreading of the expenses over the periods. The great thing about some of these formulas is that the expense will move to the correct month if you change the start month of the forecast.

Formulas to Spread Expenses

Fixed – enter an amount and it forecasts that same amount forever

EOQ – End of Quarter – enter an amount and it only places that amount at the end of each quarter, regardless if you change the date range of the model. I’ve built it so that if you change the dates at the top, these formulas will place the expense in the correct month(s).

Spread Amount – enter an amount and the start and end dates and it will spread this amount over that time frame.

Annually – enter an amount and date (only the month matters) and the formula will place the amount one time per year in the specified month.

Odd Month – enter an amount and it will place the amount in the odd months (Jan, Mar, etc.)

Spread Evenly – enter an amount and start/end date and it will place the same amount in each month over the specified period.

Conclusion

I’ve found that a combination of these formulas will reduce the hard coding needed forecast operating expenses at a detailed level. With this tab it makes it quick to update your forecast and the roll up of expenses takes place automatically with SUMIF formulas. However, I find that marketing expenses (too variable in my forecast) and some travel expenses will not work well with these formulas.

I would love to hear your feedback. Please share your comments below so that we can all improve our forecast methodology.

Download below.

Please enter your email address (no spam) below to download the model. I’ll keep you updated on future models and posts.

Subscribe & Download