What If Modeling – A Great Tool for Budgeting and Forecasting in Excel
Should You Be Using ‘What If’ Tools for Budgeting and Forecasting in Excel?
‘What if’ modeling uses built-in tools for budgeting and forecasting in Excel. Financial planning and forecasting tools help you prepare for whatever the future may bring because they empower your corporation to outline the best paths through a variety of potential scenarios.
This year, we are seeing severe inflation and skyrocketing labor costs. A few years ago, we saw the lightning-fast onset of a new, remote work world. Though none of us could have predicted such events in advance, businesses using ‘what if’ financial modeling software would have been better prepared to understand the best ways to move forward when confronted with surprise situations.
The Benefits of ‘What If’ Modeling
‘What if’ modeling can be worth its weight in gold when your business needs to chart a course through uncertain times, but it doesn’t take a seismic event to make ‘what if’ modeling useful.
Many Finance & Accounting professionals rely on ‘what if’ modeling to quickly and easily determine the impact of workforce headcount changes or business investments – or to craft achievable KPIs. The sky is the limit when it comes to using your modeling skills for answering complex business questions because ‘what if’ modeling creates an unlimited number of projections that you can adjust to determine the impact of different decisions.
Interested? You can start ‘what if’ modeling right now, using tools you already have.
Budgeting and Forecasting in Excel Using ‘What If’ Modeling Tools
Microsoft Excel provides a basic set of ‘what if’ modeling tools that are already built in.
Scenario Manager
Scenario Manager helps you create projections for what might happen in the future (known as “scenarios”) without changing the real data. By crafting multiple scenarios, you can easily determine the outcome of various financial adjustments, which is why this tactic is also known as Multi-Scenario Forecasting. Some people also refer to this as performing a Sensitivity Analysis.
When using Scenario Manager, always make sure that the first scenario you create is your actual data with no changes, so you can compare your various projections to the current reality.
Example: Adjust your quarterly expenses using different scenarios, so you can see which reductions would have the greatest impact on your annual profitability.
Data Tables
If you don’t want to take lots of time to create multiple scenarios in Excel, you can streamline the process by creating a Data Table. A Data Table lets you apply 1 or 2 variables to determine potential outcomes.
Data Tables are great because they will automatically calculate (and update) projections for a couple of variables at a time, which saves you from having to write and validate formulas. Plus, since Data Tables display all the results in a single table, it is easy to compare outcomes and choose the right path for your needs.
Example: Discover the impact to your revenues with discounts versus sales volumes. If you were to discount all your products by 2.5% to boost sales and that change increased your volume by 12%, would that positively impact your revenues more than if you discounted your products by 5% and increased volume by 19%? Are either of these scenarios more beneficial than if you applied no discounts?
Goal Seek
Not sure what your initial numbers should be to reach a desired outcome? Goal Seek helps you understand what it takes to reach a specific goal by working backwards to fill in the missing data.
This modeling tool is great for running a “gut check” on reality. Some companies may use it to determine the break-even quota for a new sales hire and then compare that number to realistic production rates to see if the salesperson can actually promise that many sales. Some companies may use the Goal Seek tool to build better KPIs because it fills in the gaps of what is needed to reach a goal. The beauty of the Goal Seek feature is that it helps you see whether your goals are achievable, or if you need to adjust them.
Example: Determine an acceptable interest rate for a business loan, with the flexibility to adjust your monthly payment amount, loan term, and other factors. Alternatively, use the Goal Seek tool to see how much revenue would need to increase quarterly in order to achieve a 5-year strategic goal.
Who Should Use Excel’s Built-In ‘What If’ Modeling Tools?
The built-in tools for financial budgeting and forecasting in Excel are a great way for any Finance & Accounting professional to get started familiarizing themselves with financial ‘what if’ modeling. With practice, F&A professionals can improve their intuition, skills, and speed in crafting insightful scenarios. Because the ‘What If’ Analysis tools are already available in Excel, any F&A professional would benefit from learning what these tools can do.
Already familiar with Excel’s ‘what if’ tools? Even if you have already used ‘what if’ modeling before, it is a good idea to keep building your skills and confidence level. Being able to quickly calculate accurate, insightful projections can save time on your daily tasks and boost your value to the company.
However, ‘what if’ planning in Excel does have some limitations, so some Finance & Accounting professionals may NOT benefit from using Excel’s ‘what if’ tools.
Limitations of Financial Planning and Forecasting in Excel
Would Excel’s tools benefit you? That depends on the size of your datasets, your data sources, and your company’s collaboration needs. It also depends on your current frustration level with spreadsheet errors.
Since the ‘what if’ analysis tools we’ve discussed here are built-in Excel tools, you can expect the standard Excel limitations to apply: as spreadsheet data, your ‘what if’ modeling features can fall prey to frustrating manual-entry errors, copy/paste errors, and overwriting errors that can make you look bad by corrupting your projections without your knowledge.
Additionally, relying on built-in tools for financial budgeting and forecasting in Excel may also limit your collaboration capabilities because tracking down various versions of a spreadsheet that multiple departments have modified is a slow and irritating task. This is a shame because one of the biggest benefits that ‘what if’ modeling brings is the ability for different departments to view and understand the impact of their changes on other areas of the company.
Finally, Excel’s built-in ‘what if’ modeling tools can only be used with a limited (and rather small) number of cells, and larger data sets will run very slowly. Plus, you have to enter the data yourself, which can be time consuming.
When you are just getting started with ‘what if’ modeling, these limitations of financial planning and forecasting in Excel are not a big deal, but as your data sets get larger or your company wants to involve more departments in the decision-making process, it is probably time to start investigating spreadsheet alternatives. Many companies choose to step up to a more robust solution such as Corporate Performance Management (CPM) software to meet their agile planning needs.
Purpose-built planning software in a CPM solution should connect to key data sources like your ERP, operational databases, or data warehouses and automatically update your actuals and budget drivers for you. It should also make cross-departmental collaboration seamless by having all departments work in the same version on the cloud. Finally, it should audit all updates and changes to your data while quickly rendering results for massive data sets with nearly unlimited complexity. These combined capabilities will help you ensure that your insight into the future is always right at your fingertips.
Be Prepared for Whatever the Future Brings
None of us can ever know for sure what the future holds, but we have all seen that everything can change in a day.
F&A professionals who use ‘what if’ modeling will have the power to act faster, smarter, and more strategically to unexpected events than their counterparts can, whether they use tools for budgeting and forecasting in Excel or whether they boost their planning capabilities with a purpose-built CPM solution.
Solver gives you access to hundreds of financial planning templates and reports, plus our extensive template glossary and experienced staff who are always happy to share their expertise. Solver is committed to helping you with all your planning and reporting needs, so you can proceed confidently into your business’s financial future.
Comments