Reading Time: 4 minutes

I am not sure any real people actually visit this site.  I am pretty convinced the only visitors are bots.  However, the bots seem to like the post on my views on the difference between operating models vs. financial models.

So, here are some more thoughts on operating models in the form of tips for building an operating model that I have refined over the last decade or so.  Also, for clarity, my operating model and forecasting process is all Excel based.  I realize there are some fancier tools out there, but this process gets the job done at much lower cost than any of those and is extremely versatile.

1. Budget In the Same Form as Your General Ledger

The first two steps work together, but in many ways, you should start here if you can. Your budget inputs should be in the same form as your general ledger (GL) / enterprise resource planning (ERP) exports. If you did not do this for this year, plan to use this as a template for your budgeting process next year.

I send out budget template packages to all my business leaders to guide their budgeting effort that are based on our ledger exports.  The package presents historical data and asks for inputs, by month, for the upcoming year.  In many cases, a business unit leader only has a few major line items they need to worry about, outside of headcount driven ones like salary, wages, and benefits.  

You can utilize helper sheets to aggregate detailed assumptions:

A little prep work can save everyone a bunch of time and make your life a lot easier going forward.  Having your budget and your actuals in the same detailed form will make Actual to Budget comparisons much, much easier.

2. Utilize Inputs from Your General Ledger / ERP System

Again, step one and two are very much related. I highly recommend driving your historical inputs off of exports from your GL / ERP system. This will make importing data on a monthly basis systematic and mostly a copy and paste exercise. It will also let you know if your Controller added any accounts to the GL that you need to peek at.

The exports should be monthly.  And, you will likely need separate worksheets for each department, so that you can have that granularity in the model and related operational reporting.

3. Summarize Accounts Into Categories for Analysis & Department Reporting

The ledger exports are generally fairly granular, and in my experience, too granular for most of your audience (business users). You can use some higher level categories to summarize these into easier to digest format.

Examples of the operating expense categories I use are:

So, for example, if you see that Software increased over prior month, you can dig in and go find where and which department.  For a really detailed review, you might need to go back to the ledger, but you have all the tools to easily get that done.

4. Use a First Look Report

Once we have our accounting close done, my first step is to review what I call my First Look report.  This is a comparison of the month across revenue, cost of goods sold, operating expenses, and other items to:

1. My Forecast (more on this later)
2. Budget
3. Prior Month
4. Prior Year

This is a great way to spot things that need more review, before I actually issue financials out to my team and our investors.

5. Keep The Census Separate

I use a modular approach in building my operating model. My operating model is actually several linked files:

1. Financial Model – A working, monthly financial forecast model
2. Ledger File – feeds the Financial Model for both historicals and forecasted expenses
3. Census File – fees salary wage and benefits information into the Ledger file

As a banker, I abhorred linked files.  As an operator, they become a way of life.  

Keeping these separate allows me to share the Financial Model easier.  The model file is small.  And in the case of the census, there is no confidential or sensitive payroll data.

Generally, I turn off comments on my site.  Most comments are spam.  However, I am going to leave comments open here.  If you have some questions, please post them.  And if you would like some help setting up an operating model, drop me a note.