Intro

A look into my past.

When it comes to updating the financials in your model each month, it should only take a few minutes.

There might be other support schedules, KPIs, or other inputs that take more time (that's fine).

But the financials portion should be quick.

I'll explain my process today...

Building Your Model the Right Way

It all starts with structuring your model correctly.

Which means something like this:

• Actual Data
• Support Schedules
• Three Statement Model
• Summaries
• Control Panel

\Actual Data/ Tab

It all starts here.

This is a tab that sits in the back of my model, that has all the actual data by month.

I usually put the Income Statement on the top, Balance Sheet on the bottom.

Here's an example:

Actual data on the right, helper columns on the left.

Each month, I compare account names (to see if anything was added or deleted), and then do a quick copy/paste to get the latest data into the file.

I might be against the grain here, but I don't use PowerQuery much.

I like the data to be "as-is" and build helper formulas instead.

(that's just me. You do you.)

\Three Statement Model/ Tab

Do you see the "Summary Mapping" column in the Actuals image above? (in blue, on the left)

Each of those "tags" matches a line item in the three statement model.

This is critical to update the model quickly.

The Critical Formula (in Excel terms):

With that infrastructure in place, I build all Income Statement and Balance Sheet line items with the following formula:

You can see the formula in the image below:

Everything is based on an IF statement.

The Formula (in simple terms):

The =IF trigger identifies if the current period has "Actual" results or is still looking at a "Forecast."

If Actual results are available, I ask the formula to pull that result directly from my Chart of Accounts (Actuals tab).

Otherwise, if we're still in Forecast mode, I ask the IF statement to pull my forecast formula.

\Control Panel/ Tab

This whole process is triggered by a LATEST_ACTUALS named range that is sitting on my Control Panel.

When I enter a new date (i.e., 8/31/2024), this updates the headers on the Income Statement, Balance Sheet, and any other support schedules that require ongoing actual information (see the image above where "Actual" is in green and "Forecast" is in yellow).

(in other words, I just change the date in one cell and the entire model updates)

💡Reminder: The Statement of Cash Flows updates automatically because its calculation is always the difference between the two periods (using the indirect method), so there's nothing to update.

Lastly, I will always have "double-checks" or "validations" linking to my Control Panel to make sure everything worked out as it should.

Once the infrastructure is built, this whole process takes just a few minutes.

That's it for today. See you next time.

—Chris

p.s., if you enjoyed this post, then please consider checking out my Financial Modeling Courses.  As featured by Wharton Online, Wall Street Prep, and LinkedIn Learning, you'll learn to build the exact models I use with Private Equity and FP&A teams around the world.  👉 Click here to learn more.