Join Curt Frye for an in-depth discussion in this video Creating a scenario, part of Up and Running with Excel What-If Analysis.
Businesses face a lot of uncertainty, whether it's consumer demand for products or the price that you pay for raw materials. Planners often think of a best case, worst case, and middle case scenario for the next quarter or year. In this movie, I will show you how to store alternative data sets in the same worksheet by using scenarios. For this movie, I will use the Create Scenario sample file, which you can find in the chapter one folder of your exercise files archive. This workbook contains a single worksheet, and on it, I have four cost centers.
Labor, warehouse, inventory, and utilities. For three calendar years. 2013, 2014 and 2015. And for each of the calendar years I have associated costs for each of the categories and a total. What I would like to do is create a scenario that provides alternative values. Let's say for example that I wanted to create a scenario for inventory where the inventory costs were a bit higher. Say, instead of 1.6 million, it would be 1.7 million, and the same for 2014 and 2015.
To do that, I go to the data tab. And then in the data tools group, click what if analysis. And then click scenario manager. Doing so displays the scenario manager dialogue box. And to create a scenario, I click the add button. Which displays the add scenario dialogue box. Now I can type in the name for the scenario. And I will call it high inventory. You can name your scenario just about anything you want but there are a couple of rules. The first is that the first character must be a letter.
The name of the scenario may only contain letters and numbers, and also the underscore character. That means your scenario's name may not have a space in it. So I have high inventory with no space. Now I can identity the cells that have values I want to change as part of the scenario. And in this case, those are the inventory sales in E8 through E10. So I'll click the collapse dialoge box button, next to the changing cells box. Select cells E8 through E10. Click the expand dialogue box button.
And my comment gives information about when it was created. I could edit that if I want to, but in this case, I won't bother. And I'll click Okay. Now this scenario's dialogue box appears and I can enter values for each of the cells that I want to change and very helpfully Excel displays the existing values already in the box. So for cell E8, it's currently 1.6 million, let's make that 1,700,000. Then I'll press tab. And for E9 it's 1.4 million. Now, let's make it 1.5.
So 1,500,000. Press tab again. And for calendar year 2015 instead of 1.3 million, let's make it 1.4. So 1,400,000 and click okay. When I do, the scenario manager dialogue box appears and my scenario appears in the list. And that is how you create a scenario in Excel.
- Creating a scenario
- Defining data tables
- Finding a target value with Goal Seek
- Defining constraints and running Solver
- Creating a configurable model
- Manipulating your model
- Running a Monte Carlo simulation