Strategic Decision-Making with Scenario Analysis in Excel

6.3 min read|Last Updated: March 2nd, 2024|Categories: excel|
table of content

Have you ever wondered how it is possible to analyze your data based on different scenarios? For example, what may happen if you increase the budget of your marketing programs by 50 percent? What if it is less than that amount? What if it’s more? So, here you may have different scenarios to check. How is it possible? Scenario analysis Excel is the tool that can help you see the results of such schemes.

What is scenario analysis Excel?

When it comes to using scenario analysis in Excel, we 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. That’s the power of the What-If Analysis tool, which is accessible from the Data tab on Excel.

How to check different scenarios in Excel? 

First, we need to create different scenarios based on the available data. So, we’ll start with the simplest example. Let’s say we have put our money into a marketing plan: we have some products to sell, and we need to know how many products we ought to sell to have a profit. Here we can have different scenarios, how many sold items are good, how many are bad, and how many are enough? In the end, we need to check if we should increase the price of the products to have profit. Let’s see what happens.

In our example, we keep 10,000$ as our marketing budget. We have one item to sell. Assume the price of this product is 100$. Here, the scenario is based on the number of products sold. First, look at this table.

Example of one scenario in Excel, a table with price and number of cells as columns and products as rows marketing expenses is 10000$ and profit is 0$
Figure 1- Example of one scenario in Excel

In this example, we know if we sell 100 products, we won’t have any profit, and we won’t have any loss either. So, here is one scenario we can write. To write a scenario in Excel, you should go to the Data tab and click on What-if Analysis in the Forecast section. You will see three options. We start with Scenario Manager.

In Excel, click on the Data tab, then click the What-If Analysis in the Forecast group and choose Scenario analysis
Figure 2- What-if Analysis Excel


 

What does Scenario Manager do on Excel? 

Scenario Manager is where we can add different scenarios, each with a different value and result. When you click on this option, a dialogue box opens where you can add your preferred scenario. Follow these steps: 

  1. Click on Add to see the next step, where you can add the value.
In the scenario management window in Excel, you can see the "No scenarios defined. Choose add to" message. Click on Add to open the "Add Scenario" box
Figure 3- Scenario Manager Excel example
  1. Here, you can write a name for the scenario you are setting. In our example, we’ll call it “Enough“. It’s when our profit is equal to zero, so we know we have no loss on our project. 
  2. Our variable cell is D7, which we’ll select and add the value. In our example, we’ll assume that selling 100 products is enough.