Your boss comes in and tells you that the budget meeting will be today, but he has some new totals that will vary and wants you to prepare the reports showing how the different totals will affect the final figures. You panic because you had already prepared the proposed budget with the original figures given to you and now you have to plug in new values that will affect the final totals and prepare the reports showing how these new figures compare to one another. You just don’t have time to go into your Excel spreadsheet and delete, input, save and print all the reports with the different scenarios. Well, you don’t need to panic, luckily for you and all of us, Microsoft Excel has a very useful function called Scenarios that allows you to change variable information in cells that affect the final totals of a formula in minutes and prepares scenario reports instantly. In this tutorial, I will show you how to use the Scenario function in some simple quick steps.
We’ll use data from the fictitious company Happy Tummies. You are the Training Manager working from the Corporate Office, and you’ve been asked by your boss, the Director of Training and Development to prepare a report comparing how much a Customer Training workshop would cost from four vendors. You had already prepared a report with the original vendor that you were told the company was going to use to develop the materials for the training and conduct the workshop training. So now you need to add this new data and compare it to the one that you had already created. These three vendors have different costs per student and total number of students in the workshops. These are the pieces of data that will vary. You need to also prepare a report showing these figures to present it to the management team so a final decision between the vendors can be made.
Four vendors’ data needed for the tutorial:
Vendor || Cost
Eureka To Go
# of Students= 20
Cost per Student= $80
LLX Learning
# of Students= 25
Cost per Student= $125
Jimenez Associates
# of Students= 30
Cost per Student= $100
Mc-Graw Learning Specialists
# of Students= 25
Cost per Student= $115
Let’s Get Started!
1. Go to Start>Programs>Microsoft Office> Excel to open the application. Then, go to Start>New to open a new spreadsheet. Go to File>Save as and save the new file, give it a name that relates to what you’re doing.
2. Now enter the data of the original vendor (Eureka To Go) in the spreadsheet as seen in the image below. Enter the data and format as needed. Please enter the title “Total Cost†in cell A1, then enter the formula “=B3*B4†in B5 cell and hit enter. The total cost of # of students times the cost per student will appear. This is the formula that will stay constant, but for the next steps I will show you how to quickly change the data in cells labeled “# of Students†(A3) and “Cost Per Student†(A4) and save this information.
3. Go to Tools>Scenarios and select it.
4. A pop up window named “Scenario Manager†will appear. Click on the “Add†button to create the first scenario.
5. A pop up window labeled “Add Scenario†will appear, and here you will first enter the original information to create a scenario for it (Eureka To Go - # of students=20 and cost per student=$80.) Under “Scenario Name†enter the name of the vendor, under “Changing Cells†select the cells where the information will vary, cells B3 and B4 (# of students and cost per student), and finally under “Comments†enter the name of the vendor again – here you can add a brief description of the scenario, but to keep it simple for the purposes of this tutorial, we’ll enter the vendor’s name). Next double check that the “Prevent Changes†box is checked and click OK.
6. Now, another pop up window will appear called “Scenarios Values†and here you will enter the values for the cells that will change, but since this is a scenario for the original information, just make sure that the values for Eureka To Go (cell B3=20 and cell B4=$80) are entered, and hit OK. You’ve just created the first scenario in a couple of minutes. In the next steps, you will repeat steps 5 and 6 and create three additional scenarios for the remaining three vendors.
7. Go to Tools>Scenarios, click on the “Add†button from the “Scenario Manager.†This second scenario will be for the vendor LLX Learning – please refer to the data provided at the beginning of the tutorial. Under “Scenario Name†enter the name of the vendor, under “Changing Cells†select the cells where the information will vary, cells B3 and B4 (# of students and cost per student), and finally under “Comments†enter the name of the vendor again, double check that the “Prevent Changes†box is checked, and click OK. Next, another pop up window will appear called “Scenarios Values†and here enter the values for the cells that will change (B3=25 and B4+$125), and click OK. You just created your second scenario – LLX Learning.
8. Go to Tools>Scenarios, click on the “Add†button from the “Scenario Manager.†This third scenario will be for the vendor Jimenez Associates– please refer to the data provided at the beginning of the tutorial. Under “Scenario Name†enter the name of the vendor, under “Changing Cells†select the cells where the information will vary, cells B3 and B4 (# of students and cost per student), and finally under “Comments†enter the name of the vendor again, double check that the “Prevent Changes†box is checked, and click OK. Next, another pop up window will appear called “Scenarios Values†and here enter the values for the cells that will change (B3=30 and B4=$100), and click OK. You just created your second scenario – Jimenez Associates.
Please Note that as you create new scenarios the information in cells B3 and B4 will change with each scenario and you can see the new totals on your spreadsheet immediately after you add the scenario.
9. Go to Tools>Scenarios, click on the “Add†button from the “Scenario Manager.†This fourth scenario will be for the vendor Mc-Graw Learning Specialists– please refer to the data provided at the beginning of the tutorial. Under “Scenario Name†enter the name of the vendor, under “Changing Cells†select the cells where the information will vary, cells B3 and B4 (# of students and cost per student), and finally under “Comments†enter the name of the vendor again, double check that the “Prevent Changes†box is checked, and click OK. Next, another pop up window will appear called “Scenarios Values†and here enter the values for the cells that will change (B3=25 and B4=$115), and click OK. You just created your second scenario – Mc-Graw Learning Specialists.
10. Now if you go to Tools>Scenarios and open the “Scenario Manager†pop up window menu, you see all your four scenarios listed by vendor name. Now, to quickly print a report that shows all four scenarios with the final totals, click on “Summary†- You have the option of creating a pivot table report but for the purposes of this tutorial and to keep it simple, I’m showing the summary report – I will show you the capabilities of the pivot table report in another separate tutorial.
11. Next, a pop up window called “Scenario Summary†will appear, select “Summary Report†and under result cells, select the cell or cells that you want to see in the summary report. We’ll select cell B5 “Total Cost†since we want to compare the totals of all the vendors, and click OK.
12. The summary report will look like the image below. Now to simplify the report even further before we print it, we’ll hide columns E and column C by dragging column F toward column B.
13. Finally, go to File>Page Setup and select the “Landscape†view, then go to File>Print Preview to view the report before printing – it should look like the image below. If you are happy with the way it looks, go ahead and print it.
Fantastic! If you timed yourself doing this, you’ll see it really just takes minutes to create scenarios once you become familiar with the steps. Continue practicing and you’ll become a scenarios guru in no time and set yourself apart from your colleagues.
If this tutorial has helped you, please consider donating.
Loading ...
{ 10 comments… read them below or add one }
Hi,
This really helped me,but I have a dought why its do not reflect any changes that we make in the numbers of the others sheet I mean automatically the summmary sheet alos must change that do noit happen?
sir can u send me a reply for this mail and what are the function in excel pl. let me know by mail. becuase i have to work in excel and getting difficult. i am frm india
I’m sorry, I do not understand your question.
Hi,
This is really good. Is there any option to go back and convert the same scenario to Pivort table
very useful and perfect explained
This tutorial really helped me and it is perfectly explained.
This tutorial is clear, and straight to the point. Thanks for all the help
good
Excellently explained.
Can we create kind of button to quickly change from one scenario to another? I recall seeing from somewhere that we can do so with MS Excels. Also after I create the scenarios I can not see the original anymore. How do I do that?