16 September 2013

Excel Scenarios In Two Ways In New Tutorial

Excel icon The Tips section has a new tutorial about how to show different scenarios in Excel. I recommend using the Scenario Manager to display various options.

Appropriate caption
What-If-Analysis buttonMicrosoft has put the caption "What-If-Analysis" on the button we should use for scenario calculations, and this is really to the point. When we create scenarios we are asking Excel "What will be the result if this or that happens?".

Identify cells that might change
To create various scenarios for your data you must identify what cells in a table have values that can vary in different scenarios. These are the cells you should use in the Scenario Manager.

A company might need to now how much they will save if their sales representatives stay at cheaper hotels when they are travelling. Or maybe they want to give them more luxury accomodations? How much would that cost?

In a private budget you can do the same. What if I reduce the amount I'm spending on clothes? How much will I have left then, at the end of the month?
Scenario Values screen
In my demo I show an example with housing costs, the same as I used in the tutorial about Excel formulas that multiply and divide. What might change here is the interest rate and the taxes, so I select these cells to build a worse case scenario.

Save and edit
Scenarios can be saved, so that you can apply them later with a click of the mouse. You may of course also edit them when needed, and it is easy to add new scenarios to the Manager.

The copy method
In the demo below I am also showing another way to save scenarios: copy a worksheet and change the relevant figures. This method works if you only have two or three scenarios, but if you have more the Scenarios Manager is a smoother solution. And even if you start with only two scenarios you will probably want to add more later, and that is also a reason to use the Manager from start.

In the Tips section you can find more tips and pointers, each with an introductory text. Welcome to browse around among the other Excel demos!

By Peter Kalmstrom
CEO and Systems Designer Business Solutions

No comments:

Post a Comment