How to Easily Use the Excel Scenario Manager

How often has your manager come to you with a request for reports that forecast sales? You have the data laid out in Excel, so now you need to create the forecast reports. Forecasting is a very common request to help predict trends and patterns in business, sales, and revenue. In forecasting or other predictive scenarios, the common theme is “what if”. Your manager would like to know a what if based on Sales and another what if based on Overhead. The question is, how do you create these scenario reports without having to manually create the formulas in multiple worksheets or spreadsheets?

One of the many great features of Excel is the Scenario Manager. With the Scenario Manager, you can create scenarios for your reports and save yourself the headache of doing the math yourself. We would like to walk you through the Scenario Manager to help you get started with this wonderful feature in Excel.

The idea behind Scenarios is to allow you to store multiple versions of data in the same cell. Therefore, you can store the different forecasts as a Scenario, print them or compare them side-by-side. By doing this, you do not need multiple files or excel sheets. It simplifies the process.

Here is a simple how-to for an Excel scenario

1. 1. Open up a new Excel worksheet
   2. Delete all sheets except Sheet1 and rename it Forecast
   3. Create the following example. In cell B7, enter =B3-B4 to get the sum.
   4. On the Data tab, click What If Analysis and then click Scenario Manager

       

   5. When the Scenario Manager opens, click the Add [+]
   6. Give your scenario a name. For this example, we’ll call it Forecast.
   7. Press the Tab key or click your cursor to move to the Changing cells
   8. On the worksheet, hold the Ctrl Key to select cells B1, B3, and B4 (for Mac, use the Cmd Key).
   9. In the comment section, describe your scenario.
   10. Click OK.
   11. Once you hit OK, the Scenario Values box opens.
   12. You can modify each value, but in our example they contain the values on the worksheet and do not need to be changed.
   13. Click OK to go back to the Scenario Manager and click Close to go back to the worksheet.
   14. Create a Second Scenario by changing the values in B1, B3, and B4.
   15. Open up the Scenario Manager again and click Add [+] Button.
   16. Name the new scenario Revenue.
   17. Leave the Changing cells field the same
   18. Updated the comments and click OK.
   19. Click OK again and click close.
   20. Now that you have created your scenarios, you can choose which one to show by going back to the Scenario Manager and clicking the Show button.

As you can see, this is an easy way to make reports all within a single spreadsheet. Not only does the Scenario Manager allow to give your manager what they need, it also reduces time and stress for you.

Scenario Manager (and Pivot Tables which we covered in our last blog) are an example of what we cover in our Excel Private Group Training. If you are interested in group training for your organizational, use the form below to contact us for a no obligation call. As a thank you for contacting us, you will receive a free download of our Excel Shortcuts Cheatsheet.

Download your free Excel Shortcut Cheatsheet