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:
- Input
- Calculation
- 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:
