‘What if’ analysis uses built-in tools for budgeting and forecasting in Excel. Financial planning and forecasting tools help your company determine the best paths through various potential scenarios, enabling you to be ready for whatever the future holds.
This year, we are seeing significant inflation and rapidly rising labor prices. A few years ago, we saw the lightning-fast onset of a new, remote work world. Even though none of us could have foreseen such catastrophes, organizations that used “what if” financial analysis software would have been better prepared to know how to proceed in unexpected circumstances.
The Benefits of ‘What If’ Analysis
When your company needs to steer through a period of uncertainty, “what if” analysis can be valuable, but a seismic event is not required for it to be effective.
Many Finance & Accounting professionals rely on ‘what if’ analysis to quickly and easily determine the impact of workforce headcount changes or business investments – or to craft achievable KPIs. When it comes to applying your analysis expertise to solving complex business problems, the possibilities are virtually endless because ‘what if’ analysis generates an infinite number of projections that you may modify to assess the effects of various choices.
Interested? You can start “what if” analyzing right now, using tools you already have.
Utilize Excel’s ‘What If’ Analysis Tools
Microsoft Excel provides a basic set of ‘what if’ analysis tools that are already built in.
Scenario Manager
With the help of Scenario Manager, you can 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.
Always ensure that the initial scenario you create in Scenario Manager uses your actual data and has no changes so that you can compare your various projections to the present situation.
For instance, adjust your quarterly expenses using various scenarios to see which reductions would have the biggest effects on your annual profitability.
Data Tables
If you don’t want to spend time in Excel establishing various scenarios, you can streamline the process by creating a Data Table. You can use one or two variables in a data table to predict potential outcomes.
The benefit of using data tables over manually writing and validating formulas is that they will automatically generate (and update) projections for a few variables at a time. Additionally, it is simple to compare results and select the best course of action for your needs since Data Tables display all the data in a single table.
For instance, discover the impact of 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 starting points should be to achieve the desired result? Goal Seek uses a backward approach to fill in the gaps in the data to help you comprehend what it takes to accomplish a specific goal.
This analysis 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. Because it fills in the gaps of what is needed to achieve a goal, some businesses utilize the Goal Seek tool to create better KPIs. The great thing about the Goal Seek function is that it makes it easier to determine whether your goals can be met or if they need to be adjusted.
For instance, 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’ Analysis Tools?
Excel’s built-in financial planning and forecasting capabilities are an excellent way for any Finance & Accounting professional to begin familiarizing themselves with financial ‘what if’ analysis. 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 previously employed “what if” analysis, it is a good idea to continuously advance your knowledge and confidence level. Your daily activities can be completed faster and your value to the organization will increase if you can swiftly produce accurate, insightful projections.
However, Excel’s “what if” planning features do have some restrictions, so some Finance & Accounting professionals might not profit from using them.
Restrictions of Financial Planning and Forecasting in Excel
Would you profit from Excel’s tools? That relies on the scale of your datasets, the sources of your data, and the need for collaboration inside your organization. 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’ analysis 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.
Relying on Excel’s built-in features for financial budgeting and forecasting may also restrict your ability to work with others because it takes time and effort to track down various spreadsheet versions that multiple departments have modified. This is unfortunate because one of the main advantages of “what if” analysis is that it allows other departments to see and comprehend how their changes will affect other parts of the business.
Finally, larger data sets will run extremely slowly when using Excel’s built-in “what if” analysis capabilities, which can only be used with a small (and rather limited) number of cells. Additionally, you have to enter the data yourself, which can be time-consuming.
These Excel financial planning and forecasting limitations are not a big deal when you are first starting out with “what if” analysis, but as your data sets grow or your business wants to involve more departments in the decision-making process, it is likely time to start looking into spreadsheet alternatives. Many companies choose to step up to a more robust solution such as financial planning and analysis (FP&A) software to meet their agile planning needs.
An FP&A solution connects to key data sources like your ERP, operational databases, or data warehouses and automatically updates your actuals and budget drivers for you. Having all departments operate in the same version in the cloud, also makes cross-departmental collaboration simple. Finally, it will 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
We can never know with certainty what the future will bring, but we have all witnessed how quickly things can change.
Whether they employ Excel’s budgeting and forecasting tools or an FP&A solution to improve their planning capabilities, F&A professionals that use “what if” analysis will be able to respond to unforeseen events faster, smarter, and more strategically than their peers.
