How to Create Business Resilience through What-If Analysis in FP&A

The art of financial business planning used to be a leap into the unknown, a venture into uncharted territories of the future. Without a crystal ball in their back pocket, all CFOs could do was budget, hope for the best, and quickly patch together a response if and when an unexpected event occurred.  

But the pandemic—the epitome of unexpected events—ushered in a new era of uncertainty, forcing businesses to turbocharge their financial planning and analysis (FP&A) processes and regularly run what-if analyses. Before 2020, only 57% of organisations ran forecasts daily, weekly, or even monthly, according to a recent Workday survey. By 2022, that figure had jumped to 80%.

Today, volatility is the new normal, and organisations anticipate disruption. For example, 96% of CFOs believe climate change will impact their company’s operations over the next three years. What remains unknown is exactly how those disruptions will play out. Will nature wreak havoc on supply chains? Will the cost of raw materials go up? Will insurance expenses skyrocket?

There’s no way of knowing which of these scenarios may occur, but that doesn’t mean CFOs aren’t attempting to plan for them. The antidote to uncertainty is resilience, and the best way to build a resilient organisation is to conduct a what-if analysis to plan for likely scenarios.  

Here’s how to conduct one.

What Is What-if Analysis?

A what-if analysis looks at how changes in certain variables affect the outcome of a situation. What-if analyses don’t predict the future—they prepare organisations for several possible futures by creating a playbook for potential business disruptions and opportunities such as M&A activity.  

Consider, for example, supply chains, which have been a major source of disruption within organisations in the past few years. First, the pandemic ground imports and exports to a halt, then geopolitical strife muddied traditional trade routes. At one point in 2021, a giant cargo ship stranded in the Suez Canal was holding up $9.6 billion dollars in trade per day.

Planning for the very specific ship-stuck-in-the-Suez scenario is, of course, impossible and impractical. Still what-if analysis is concerned with the consequences, not the causes, of general events. So a CFO of a global household goods manufacturer would want to understand the consequences of having millions of dollars of product tied up in a supply chain snarl and the impact a potential trade reroute would have on the company's bottom line. A CFO could conduct a what-if analysis based on increased labour and fuel costs to figure out the former.  

Conducting a what-if analysis enables organisations to model fast-moving changes within disruptive scenarios—from the routine to the strange—with equal ease and accuracy. And as business ecosystems become more volatile, a company’s ability to quickly adjust to any external event is now paramount to its long-term success.

How to Conduct a What-If Analysis

Running a what-if analysis in Excel involves several steps to see how different events might affect your business. Before starting the process, you must determine what you want to know. Don’t waste resources gaming out responses to scenarios that don’t directly impact your bottom line. If you’re the CFO of an electric vehicle manufacturer with an all-remote workforce, for example, you don’t need to worry about an increase in commercial office space rent, but you may want to prepare for a price war.  

Set goals and ask questions

Once you’ve chosen a scenario, ask hypothetical questions related to your scenario and your target outcome if applicable. For example, if your goal in a price war is to preserve profit margins as best you can, you may want to consider whether tweaking additional inputs like manufacturing and labour costs brings you closer to or further away from your desired outcome.  

Fire up Excel

There are three ways of conducting a what-if analysis in Microsoft Excel: Goal Seek, Scenario Manager, and Data Set. All three methods are housed under the What-If icon on Excel’s data tab.

Goal Seek

The Goal Seek method allows you to identify a certain goal and test different methods of achieving it. Say you want to increase your profit margin from 75% to 80%. Select Goal Seek from the What If Analysis drop-down and enter your new desired profit margin as a decimal in the “To Value” field. In the “By Changing” field, select the cell with the “cost per item” value. Hit “OK.” If applicable, Excel will automatically run the analysis and present you with a solution for hitting your target profit margin. In this case, an 80% profit margin requires reducing your cost per item by 20%.

Scenario Manager

Scenario Manager is the second way to conduct what-if analysis in Excel. To return to an earlier example, say a price war seems likely to break out across your industry. You want to reduce your prices but are worried about its impact on your profit margins. You can simulate the consequences of a price decrease by selecting Excel’s Scenario Manager from the What If analysis drop-down menu. Click the plus symbol and add the variable you want to analyse to the “changing cell” field. Enter the new value. Click “show” on the scenario manager window, and Excel will reveal the impact that lowering the price of your product to $80 drops your profit margin to 75%.

If you want to learn what steps to take to retain a profit margin of 80%, run another scenario, adjusting additional variables like material and labour costs. You can also change the price. This time click “summary,” select the variables you want to see in comparing results, and then click “scenario summary.”

In this case, adjusting the price to $85 dollars and decreasing labour and material costs by 20% resulted in a profit margin slightly above the 80% target outcome.

Data Tables

The final method for conducting a what-if analysis in Excel is best used when you want to examine the outcomes produced by changes to a single factor, such as the interest rate on a loan. In the example below, the monthly payment on a 30-year, $100,000 loan with a 5% interest rate is $536.82.  

To determine how interest rates affect monthly payment terms, create a new list of rates. In an adjacent column, enter the formula you used to arrive at the original monthly payment amount. Select the entire new data table range, including the variable values (interest rates), the formula, and blank cells. Select “data table” from the “What-if” dropdown menu. In the column input cell field, enter B2 or 5% because the interest rate is the sole variable factor you are analysing. Hit “OK.”

The results will auto-populate underneath the empty monthly payment cells and correspond to the interest rates in the column beside them. From here, you can see that raising the interest rate to 6% will increase monthly payments to $599.55; raising it to 7% will increase it to $665.30, and so on, assuming all other variables stay the same.

Explore the Advantages of Workday Adaptive Planning

Excel functions have served CFOs well for decades, but they are also difficult to master, error-prone, and unreliable.  

FP&A teams in particular can struggle to aggregate data from multiple spreadsheets resulting in limited, stale data. Traditional methods such as Excel also don’t allow organisations—especially those with multiple users—to quickly model what-if scenarios to plan and react to market shifts, leaving organisations unprepared for the future.

Modern, cloud-based ERPs with automated data-processing technology baked into their backends can quickly produce advanced analytics and forecasts that are easy to customise, interpret, and share.  

Workday Adaptive Planning, for one, helps FP&A teams to easily compare multiple scenarios and update forecasts quickly, adapting to new market information.  The software also facilitates data sharing and democratises the planning process, giving each department the power to run a what-if analysis and see in real-time how changes they make impact other parts of the business.  

Another major upside? Results aren’t trapped in spreadsheets but easily available in a flexible, cloud-based system that operates as a single source of truth for the organisation.  

For hospitality and leisure company, Belmond, utilising Workday Adaptive Planning has boosted scenario planning across the organisation, boosting risk management. At the height of the pandemic, Belmond used Adaptive Planning to model plans based on evolving legislation, quarantine rules, and opening requirements to stay on top of evolving business needs. They’ve continued to rely on the technology to better prepare for the impact of disruptive events on revenue, expenses, labour, and more.

A business that can swiftly adapt to new opportunities and adjust to unforeseen changes is poised for enduring success. Leaders may be unable to predict the future, but they can use what-if analysis to plan for the scenarios they’re likely to encounter. And when everyone in an organisation can plan for what-ifs, resiliency is all but guaranteed.