Whether the business environment is firing on all cylinders or there is economic turmoil, a successful business responds to any type of change. Financial forecasting is critical when times change. This article offers examples of both Sensitivity Analysis and Scenario Analysis, explanations of each type, when it should be used, and the advantages of each. It is critical to have a linked 3-Statement Financial Model (Income Statement, Balance Sheet, and Cash Flow) in Excel before beginning either type of analysis. A linked model will dynamically show how your assumptions affect your financial statements.
What is Scenario Analysis?
Scenario Analysis is the method of predicting the future value of an investment based on changes that may occur to existing variables. It requires one to explore the impact of different market conditions on the project or investment as a whole. This type of analysis is often used to estimate changes in cash flow or business value.
Characteristics of Scenario Analysis
Multiple Inputs Changing at One Time
Typically, Scenario Analysis involves examining multiple inputs. For example, the user may consider accelerating growth through changes in production, price changes, additional financing, and capital expenditures.
Tries to Tell a “Story” About the Future
Scenario Analysis is not about changing one variable (e.g., if interest rate increases 1% or the cost of raw materials increase by 10%). Scenario Analysis is about laying out the likely outcomes for wholesale changes in the business whether they are plans for expansion or planning for some type of disaster which results in dramatic slow-down to the business.
Presents Several Business Cases
At a minimum, one should consider a base case (current state), an upside case (best-case scenario) and a downside case (worst-case scenario). Additional cases can and should be considered, but an excess number of cases can cause confusion.
Benefits of Performing Scenario Analysis
Planning for the Future
Scenario Analysis allows investors to have any understanding of expected returns when planning for future investments. Before investing in any business, one should understand their forecasts and the assumptions used to generate those forecasts.
A company can avoid or limit losses that result from uncontrollable factors by analyzing events and situations that may lead to negative outcomes and incorporating aggressive preventive measures during worst-case scenarios.
Avoiding Risk and Failure
Scenario Analysis allows businesses or independent investors to assess investment prospects in order to avoid bad investment decisions. Scenario Analysis takes the best and worst probabilities into account, so investors or potential investors can make better informed decisions.
Learn what should be in every 13-week cash flow forecast in our free guide for operating executives:
What is Sensitivity Analysis?
Sensitivity Analysis is used to analyze how the different values of independent variables affect dependent variables under certain specific conditions. The use of Sensitivity Analysis extends beyond financial forecasting. It is used in a wide range of fields, ranging from biology to economics and engineering and food preparation.
Characteristics of Sensitivity Analysis
One Assumption at a Time
One of the simplest and most common approaches is that of changing one factor at a time. Whether the assumption is about the change in a borrowing rate or a change in the working capital metrics (Days Sales, Days Payable, or Day of Inventory), one can change each assumption individually to see the outcome on working capital, profitability or loan covenants.
No Story About Why Inputs Go Up or Down
Unlike Scenario Analysis, where multiple variables are used to outline detailed settings of both positive or negative, Sensitivity Analysis looks at only one variable at a time. It is less of a story and more of a what if. What if sales decline by “x” percent or what if the costs of raw materials increases by X. These can be looked at one at time but don’t provide a complete picture.
Used to Determine Which Assumptions Matter Most
Sensitivity Analysis changes variables/assumptions one at a time, which makes it possible to see which variables/assumptions will have the greatest impact on a business. For example, the borrowing rate can be changed by one basis point at time, or the gross margin percentage can change by specific increments. The results of one could be drastically different than the other and lead to different causes of action.
Benefits of Performing Sensitivity Analysis
When Sensitivity Analysis is performed, each independent variable and its effect on the dependent variable is analyzed. Their movements are studied, and the results are compared. This in-depth analysis will aid in providing a more accurate forecast.
Sensitivity Analysis provides management with an understanding of which variables have a high impact on success or failure to a project. For example, in a Sensitivity Analysis of a company, management may discover that a certain raw material used in their product affects the costs by up to 20%. Therefore, management can concentrate on sourcing this material from other vendors or look for alternatives to improve profitability.
Strengthen “Weak Spots”
As Sensitivity Analysis assesses each variable independently, it can identify critical variables that may act as a weakness. For example – it may be discovered that raw materials are extremely volatile to changes in currency. Measures can be taken to reduce the impact, say by hedging. Thus, it can be said – weak spot is identified and strengthened.
Excel Tools to Assist in Performing Scenario Analysis and Sensitivity Analysis
The CHOOSE function is very useful when creating scenarios in financial models. By using the CHOOSE formula, an analyst is able to select between 5 different scenarios that can flow through the entire model.
Goal Seek Function
The Goal Seek Excel tool is for what-if analysis that tells us what value an assumption needs to be in order to reach a desired output or result. It is a form of reverse engineering, where the user starts with the outcome and answer that they want and Excel works backward to find the solution.
Data tables are used to display a range of outputs given a range of different inputs. They are commonly used to assess a range of different possibilities for a company, given uncertainty about what will happen in the future.
Learn more about cash flow forecasting.
If you’d like to learn about how we’ve worked with companies to improve their cash flow forecasts, download our cash flow forecasting services explainer. To learn more about what to expect in your forecast, you can also download our simple guide for operating executives:
Thanks for reading!
About the Author
David is a CPA with more than 20 years of diverse finance, accounting, and financial systems implementation experience across various industries. He is an alumnus of Grant Thornton and PWC. Prior to joining 8020, he was Finance Director for Westcon Group, a $7B global IT distributor, where he was responsible for global inventory and supply chain reporting, Capex, and net working capital. He has led implementations of Hyperion Financial Management, SAP BPC, and JD Edwards. He holds a Bachelor of Science degree in accounting from Binghamton University and a Master of Business Administration degree with a concentration in accounting from the University of Buffalo.