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. 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.Excel Scenario Manager
    4. On the Data tab, click What If Analysis and then click Scenario ManagerExcel 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

Free Cheatsheet

FREE ebook:
Build More Revenue with Less Follow Up Fatigue

Recent Blogs

How Do I Know Which CRM is for Me?

How Do I Know Which CRM is for Me?

So, you have decided to invest in a CRM. Congratulations! You have made a bold decision which you said will “help increase sales with less Follow-Up Fatigue (FUF).” FUF gets in the way of making sales, so way to go. How do you know which CRM to use? How can you ensure...

3 Ways to Impact Your Sales

3 Ways to Impact Your Sales

Sandy Jones owns Jones, Jones, and Jones, Inc. “3Js” sells home maintenance services and has a sterling reputation. 3Js employs 18 people with 5 in sales. Sandy and her 2 sisters own 3Js and Sandy oversees sales. Sandy’s sales team has made incredible strides in the...

Tracking Prospects To Close More Sales

Tracking Prospects To Close More Sales

Sandy Jones owns Jones, Jones, and Jones, Inc. “3Js” as it is known, has a sterling reputation, selling home maintenance services in its geographic region. 3Js employs 18 people with 5 in sales. Sandy and her 2 sisters own 3Js and Sandy oversees sales. Sandy is a...