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.