Nothing is absolute both in business and in life. Scenario analysis is the strategic process for negotiating the unpredictability of the future by analyzing the potential business impacts of future events and considering alternative possible outcomes. Also, most business owners use the example of scenario analysis during their decision-making process to find out the best-case scenario as well as the worst-case scenario while expecting profits or potential losses. Read on to learn about scenario analysis in Excel.
Overview
Scenario analysis is a practice that allows financial experts to examine events likely to happen in the future to be able to predict potential outcomes. This method helps them perform a risk analysis and better understand their company’s finances. Typically, the goal of scenario analysis is to understand possible changes in cash flow or business valuation. Also referred to as “scenario planning” or “scenario thinking,” this method also helps organizations make long-term to avoid potential losses while attracting more investors.
Steps Involved in Scenario Planning and Analysis
The process of developing scenarios and then conducting business according to the information that the scenarios reveal makes it easier to identify and challenge questionable hypotheses. It also exposes areas of vulnerability and allows for robust planning and contingency preparation. This is because makes it possible to test and compare strategic options. Scenarios also help firms focus their attention on the trends and uncertainties that are likely to have the greatest potential impact on their future. Follow these steps when performing scenario planning and analysis for your organization:
#1. Choose a Time Frame
To perform your scenario analysis, first set a time frame for your evaluation. For instance, David, the owner of a phone company, may choose to look at a five-year scenario since he hopes to see his phone company double its profits over the next five years. Then he reflects on the conditions for the chosen set of times to see what changes occurred to help him predict future changes. So, David would look at the past five years and consider changes that occurred in the environment, such as the economy or government. When choosing your time frame, consider what you hope to achieve. There are factors that could affect your time frame, which include,
- Your product’s life cycle
- Technological advancements
- Political conditions in your country
- Your competitor’s analysis
#2. Identify External Forces
Determine what big shifts are likely to occur in society that may impact your company. To do this, conduct an environmental or PESTLE analysis to look at factors such as governmental policies. David may look at factors such as social trends that cause more people to buy phones.
#3. Find Critical Uncertainties
From your list of driving factors, select two critical uncertainties or risks with the largest potential impact on your business. Then consider the extreme for each uncertainty. In David’s situation, he may highlight the following uncertainties:
- The potential of phones competitor
- Disposable income of consumers
#4. Originate a Scenario
Create a graph with your first critical uncertainty on the x-axis and your second critical uncertainty on the y-axis. Each end of the graph represents a different extreme of uncertainty, creating four separate scenarios. On David’s graph, he would put the presence of a phone competitor on the top of his y-axis and the absence of a phone competitor on the bottom of his y-axis. For David’s x-axis, he would put consumers with no disposable income on the left side and consumers with a lot of disposable income on the right side.
#5. Evaluate a Scenario
After creating a scenario, meet with your team to evaluate the implications of each scenario. Try to determine what scenario your organization is currently in and in what direction you may move in your designated time frame. Once you have a general idea of the future of your organization, consider what preparations you can make to handle the upcoming scenario.
#6. Update Policies and Strategies Accordingly
Make adjustments to your current policies and strategies based on the evaluation of your scenario. Your scenarios can help you make decisions based on the direction your company appears to be going. By looking at your future scenario, you can plan how your business may need to adjust. Generally, scenario planning as a process is not just about preparing for disaster, it can also be used very effectively to identify better ways of doing things, improve operational efficiency, and find cost savings.
Excel Scenario Analysis
A scenario is a set of values that Excel saves and substitutes automatically on your worksheet. You can create and save different groups of values as scenarios, and then switch between these scenarios to view the different results. When it comes to using scenario analysis in Excel, you first need to know what a scenario is and how it’s used. A scenario is a set of different values in Excel that you create and save to see results based on each of them. You can have up to 32 different values for each scenario, and whenever one value changes, the result will automatically change based on that.
Hence, Microsoft Excel lets you manage, analyze, and compare data to help advance your career. Some functions of Excel, like the scenario manager, can allow you to make strong business decisions and predictions based on various situations. That is to say, understanding how to use this function can help you advance your career in business as you develop data analysis skills.
How to Use Scenario Analysis in Excel
Follow the steps to use scenario analysis in Excel,
#1. Find the scenario Manager
To use scenario analysis in Excel begin using the scenario manager, to locate it in Excel. At the top of the screen, you can select “Data.” From there, identify the “What If Analysis” icon and click the drop-down menu next to it. From within the menu, select the “Scenario Manager” option.
#2. Input Information into the Spreadsheet
To turn a set of values into a scenario, enter your data into the spreadsheet. A scenario can have a maximum of 32 changing cells, so it’s often helpful to prepare this information before starting this project. You can start by entering the changing cells, which represent the values that make up the scenario. After this is done, enter these changing cells, and you can add a formula to reflect how the changing cells relate to each other. To enter a formula, select the cell where you’d like to insert your calculations and type the equal sign. Then, you can add the rest of the formula.
#3. Create a Scenario
To use scenario analysis in Excel select the “Add” option in the scenario manager. In some versions of Excel, this option may look like a plus sign. Once the “Add” screen opens, you can enter the name of your scenario in the “Scenario name” section. Consider a descriptive name that you can easily recognize as you analyze each set of values. Then, to name your scenario, you can enter the names of your changing cells in the “Changing Cells” section, separated by a comma. Depending on a company’s preferences, you can also select “Prevent Changes” to protect against scenario edits. Once you’ve done this, press the “OK” button to generate the scenario.
#4. Create Another Scenario
After you’ve created one scenario, you can follow similar steps to create another. To begin, you can add the new values to the changed cells from before. For example, you can replace the highest possible operating costs with the lowest. Since the formula cell would remain the same for each value set, you can leave it unedited. From there, you can open the scenario manager, and add or name a new scenario.
#5. Combine the Scenarios
With the use of a scenario manager, you can add together multiple scenarios from different sources to compare and analyze the data. To begin, gather all relevant spreadsheets containing scenarios. Check that all participants wrote scenarios in the same cell format so you can streamline a data comparison. Afterward. you can open the scenario manager and select the “Merge” option. When a list of potential scenarios appears, choose the ones you’d like to merge and select the “OK” button. This action places all scenarios on one worksheet.
#6. Generate a Scenario Summary Report
Before using the scenario analysis in Excel, create a scenario summary report to compare the results of several scenarios side-by-side. This can create a clear representation of the potential results of each scenario. The summary information can help an organization visualize the effects of different values or variables.
To do this, open the scenario manager and select “Summary.” From there, you can choose a “scenario summary” or “pivot table,”. This allows you to place large data sets within rows or columns in the same cell. The next step is to check that the “Results cells” section contains the formula cells from within your scenario and click “OK” to generate the report.
Example Scenario Analysis
Being able to think through different scenarios and then bring the financial analysis to life in a model quickly is key. And while that’s not easy, the best finance teams can get a good feel for where the business is headed with agile, flexible planning. Here are some scenario analysis examples that show how you can think through and fit different situations into your planning cycles.
Example 1: Sales Headcount Planning
Revenue forecasting is the starting point of all financial planning, which is why sales headcount is one of the most common use cases for scenario analysis. Hence, when creating your sales capacity model, first discover your revenue parameters based on the number of new sales representatives you plan to hire.
If you set your assumption for quota attainment based on historical data, you will have one scenario set as a base case for revenue growth. But what if your hiring plan falls short? This is where you start to develop different examples of scenario analysis. With these findings, you can expand into multiple scenarios with a more granular what-if analysis:
- What if we don’t hit the hiring goals in our time frame?
- What if onboarding and ramp speeds are slower or faster?
- What if we hired five additional representatives (above the original headcount plan)?
- What if our sales representative ramp rate is 20% longer than expected—and how does that affect overall revenue goals?
No matter how good you are at forecasting, any of these what-if questions could derail your plans. And it’s finance’s job to provide executives, business partners, and key stakeholders with a complete picture of all potential outcomes and future scenarios. When you can quickly update a model to answer these questions, you put yourself in a position to make more informed decisions about when to be aggressive with hiring and when it’s a better time to be more conservative.
Example 2: Office Space Planning
Office space planning is a classic example of scenario analysis, where financial analysts and leaders conduct scenario analysis. But in the process of a global pandemic and the shift toward remote work, the thought process has changed. That said, office space and expansion are still important considerations. What if you were to open new offices in two different cities? You would need to determine the costs of new personnel (such as office managers), new equipment costs for expanded headcount, and the square footage cost to accommodate headcount plans.
You can set the worst-case example scenario analysis by asking, “What if no one uses the space?” which leads to considering what it would cost to break the lease. Your best case, of course, is that people utilize the space, where they would book a time to visit an office space or come in regularly, and you could renegotiate a better lease down the line. And if your team is remote, you need to consider the costs of transportation and accommodations for any in-person collaborations at these new spaces.
Example 3: Product Release Planning
This scenario analysis example is crucial for startups. There’s no early revenue, but you must prove product-market fit. Because you’re not making money as you prove product-market fit, scenario thinking is all about projecting your cash runway as you plan out product releases and go-to-market motions. There are two sides to consider in this scenario analysis example developing your product and promoting it.
Planning starts with having deep conversations with engineering about the headcount. This is because they’ll need to hit product release goals and market their plans to generate demand. It’s not easy to strike that balance, but running through scenario analysis exercises on both the engineering and marketing sides will help you understand how all possible future outcomes impact your runway.
How are Sensitivity and Scenario Analysis Similar?
Scenario and sensitivity analysis are both useful methods that enable finance managers and CFOs to assess the impact of these potential changes. In other words, it enables them to understand the key drivers of a company and how they will respond to the changes that are likely to occur.
Importance of Performing Scenario Analysis
There are many reasons why managers and investors perform this type of analysis. Predicting the future is an inherently risky business, so it’s important to explore as many different cases of what could happen as is reasonably possible.
#1. Future planning
This gives investors a look into the expected returns and risks involved when planning for future investments. The goal of any business venture is to increase revenue over time, and it is best to use predictive analysis when deciding to include an investment in a portfolio.
#2. Proactive
Companies can avoid or decrease potential losses that result from uncontrollable factors by being aggressively proactive during worst-case scenarios and analyzing events and situations that may lead to unfavorable outcomes. As the saying goes, it is better to be proactive than reactive when a problem arises.
#3. Avoiding Risk and Failure
To avoid poor investment decisions, scenario analysis enables businesses or independent investors to assess investment prospects. Scenario analysis also takes the best and worst probabilities into account so that investors can make an informed decision.
#4. Projecting Investment Returns or Losses
The analysis makes use of tools to calculate the values or figures of potential gains or losses from an investment. This gives concrete, measurable data on which investors can base the approaches they take for (hopefully) a better outcome.
References
- .indeed.
- .mosaic
- corporatefinanceinstitute
- sciencedirect.
- ibm.
- CRISIS MANAGEMENT PLAN: Steps To Managing Crisis
- FREE PROJECT MANAGEMENT TOOLS EXCEL: All You Need to Know
- Key Money Definition: What is Key Money? (+ Quick Guide)
- Key Money Definition: What is Key Money? (+ Quick Guide)
- Project Management Tools Excel Free: All You Need To Know, Types, and Free Tools To U
