Financial Planning & Analysis

Planning Model Flexibility: Building Better Original Plans

In a past client engagement, I was brought on to help with an Excel planning model. When working on projects involving planning models, I usually can expect to be handed a model with tons of broken links, bad formulas or incomplete sections. This time, however, that was not the case. The model the client was using during their planning cycle was amazing. Everything was properly linked and referenced. Complex calculations, scenario analysis, color-coded cells – this model had it all. It was so smart that I was worried it was going to achieve sentience. It was magnificent.

The model could do just about everything – except allow the business to quickly forecast for the upcoming year. The model had been built years ago, and while it worked perfectly at first, the business had changed. New revenue streams were introduced. Simple planning modules had become more complex; complex modules had become simpler. And due to the interwoven complexity of this model, slight modifications were near impossible to make without multiple downstream effects. There was very little flexibility.

It seemed daunting to say the least. Luckily, this same client had a great strategy when creating (or changing) planning models, which I’ve used ever since and will share with you now.

When building (or changing) planning models, group your tabs into one of these three types:

  1. Input
  2. Calculation
  3. Presentation

This simple classification will allow for greater flexibility when an inevitable change is needed. And it will also allow for a quicker change-over, as we all know changes almost always come when time is limited.

Adding Input Tabs to Planning Models

Input tabs are the home bases of your planning model. They are where the planner will input any amounts (e.g., dollars, units) as well as any assumptions (e.g., growth, tax rates) that will be used in the model. As many non-finance departments are responsible for completing these tabs, they should be the simplest and most flexible.

Additionally, make sure to add extra input fields for one-offs. No matter how precise you think a planning module is or should be, it’s been my experience that there’s always something that is important but just doesn’t quite fit.

Input tabs within planning models can have calculations in them, but should be limited only to the information found on the tab. Input tabs should be self-contained modules that are not dependent on other tabs for information. If you find that you are referencing other tabs for information to complete a portion of the planning process, think about moving that to a Calculation tab.

Input Tabs Tip

While Input tabs should be the most free-form, they are also the ones used by non-finance personnel and, therefore, should be some of the most restricted within the model. Lock all cells that don’t need input, limit input options with dropdown lists and so on.

How Calculation Tabs Should Work in Planning Models

Calculation tabs are where the magic happens. They gather and group information from the Input tabs, perform all the calculations, and return forecasted amounts that can be sent to the Presentation tabs. They can be as simple (e.g., summing amounts from multiple Input tabs) or as complex (e.g., adjusting sales based on New Zealand cheese exports to Europe as a percentage of GDP) as needed.

These tabs will also be changed the most as your business needs change. If a business need changes, a new Input tab can easily be created to account for this need and the Calculation tab can then be modified to take in these inputs. This can all happen without rearranging your Presentation tab or references. Calculation tabs can (and should) have added flexibility added in as well. For me, it helps to add an adjustment line below each forecasted amount to get a final forecasted amount. This way, you have the flexibility to account for any top-down forecast requests.

Calculation Tabs Tip

When creating calculation tabs, I find it best to add a section at the top that feeds the Presentation tabs. For example, when building a complex A/R reserve calculation tab, I would save a section at the top (usually rows 11-15) where the results can be summarized and fed to the Presentation tabs. These cells (for the most part) shouldn’t have any calculations in them. They should just reference the calculated results below. This does two things. One, it provides a quick and easy way to view the results of a planning module without having to scroll around the complicated tab looking for the answer. Two, it standardizes the cell references that will feed the Presentation tab.

How to Leverage Presentation Tabs in Planning Models

Presentation tabs are your finished product. These tabs will be those like your lead financials (e.g., Balance Statement, Income Statement, Cash Flows, KPI Dashboard). They will have minimum calculations in them (other than the obligatory sum or +/- formula).

Also try to avoid adding many tabs together in a Presentation tab; this should happen in the Calculations tab. It’s much easier to track down a formula like “=Sheet1!G47” than “=(Sheet1!G15+Sheet24!G19-Sheet1!G47)*(GrowthRate!D15)”. While there must be some references between Presentation tabs (e.g., Net Income from the Income Statement feeding the Cash Flow Statement), it should be limited to amounts that were created on the Presentation tabs.

In other words, if a Presentation tab references a Calculation tab, and this amount is also needed elsewhere on another Presentation tab, reference the Calculation tab again – not the first Presentation tab.

Presentation Tabs Tip

Use standard column references across all tab types to simplify future changes. These should be set up in the Presentation tabs and propagated across the Calculation and Input tabs. For example, if Column J represents January 2020 on your income statement, Column J should represent January 2020 on your Calculation and Input tabs as well. As a personal preference, I start my first month on my Income Statement in Column AA. If I have extra columns (and I do almost always), I can just hide the unused columns. If the business changes to require more input than expected, a column or two can always be unhidden without disruption.

Learn More About Forecasting Process

Forecasting is a vital process to any business and can be extremely simple to exceedingly complex. And many times, that simplicity/complexity changes from one planning cycle to the next. But by grouping your tabs into Input, Calculation and Presentation groups, you can adapt and modify your planning model to meet the ever-changing needs of your business.

If you need support or assistance, we invite you to leverage our growing team of financial planning and analysis services consultants. You can also learn more about forecasting processes in our free guide:

financial forecasting process guide

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Financial Planning & Analysis

CPG Data Insights: Mastering Future Prediction with Six Foundational Tools for CFOs

A CFO’s role involves enhancing transparency in current financial processes while guiding the long-term strategic plan.

June 8, 2023Branden Faust

Financial Systems

Customizing NetSuite Approval Workflows to Meet Unique Requirements 

NetSuite approval workflows can be customized to meet an organization’s unique approval requirements, ensuring that all transactions undergo appropriate scrutiny before they are posted to the system.

June 6, 2023Julian de Luna

Financial Planning & Analysis

3 Stages of Effective Cost Savings Initiatives

Cost cutting – it’s a goal that nearly every company aspires to achieve. However, unlike other areas of business improvement, there is no public playbook on how to accomplish these desired targets. Whether a firm is undergoing a full restructuring plan mandated by a Chapter 11 filing, maximizing the profit and loss statement prior to… View Article

May 31, 2023Aniv Nayar

See All

Back to Insights