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

About the Author

Jeff has fifteen years of experience in all areas of finance and accounting, including financial close, FP&A, budgeting and forecasting, financial modeling, and financial systems and has served a variety of industries including entertainment, manufacturing, energy and consumer products. Jeff started his career at KPMG in their audit practice and from there held a variety of finance and accounting roles at Twentieth Century Fox, The Coffee Bean & Tea Leaf, and Warner Bros. Jeff holds a Bachelor of Business Administration from East Tennessee State University and a Master of Accountancy with a concentration in Financial Systems from The University of Tennessee.

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Financial Reporting & Accounting

How to Improve Accounting Processes Using ESOAR (Part 1)

Organizations pursue process improvements to achieve a combination of higher efficiency, higher quality or better accuracy within their operations. If you’re struggling with how to improve accounting processes, you might take advantage of ESOAR, a process improvement methodology that can help you drive long-term value. When approaching processes using this methodology, one should: Eliminate wasteful… View Article

July 22, 2021Justin Vu

Financial Planning & Analysis

Accounting & Finance as a Strategic Business Partner or: 3 Ways to Shift from Bean Counter to Bean Grower

The bean counter is dead. Long live the bean grower. Finance and Accounting professionals’ role within the corporate ecosystem over the decades has evolved from the proverbial bean counter to emerge as a bean grower. No longer is the finance team viewed as the pocket-protector-sporting, 10-key-toting, necessary annoyance relegated to the back rooms of the… View Article

July 14, 2021Jo Ann Eilers

Project Management

About Project Scope Management in Finance and Accounting Projects

A project’s scope includes everything needed to get from the objectives to the results – all the work required to complete the project’s deliverables. “Project Scope Management” is the discipline composed of the processes required to ensure that a given project is on track to complete successfully. It involves defining and controlling what is included… View Article

June 29, 2021Olga Christodoulides

See All

Back to Insights