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

Getting Prospects to See The Need

Getting Prospects to See The Need

Here’s the scenario. You’ve met a new prospect who might need your services. How do you know? You’ve engaged, talked about common interests, and the company seems to have the right characteristics and vibe.  Now it’s time to find out if the prospect has a problem you...

Gamify Your CRM for Success

Gamify Your CRM for Success

How can you make your CRM fun to use? Whether you are a solopreneur or VP of Sales, you are more likely to get the team on board when it is fun to use the CRM.  Who Likes a CRM? Many salespeople shudder when you mention CRMs. They think drudgery; it’s another system...

More Sales Less Fatigue

More Sales Less Fatigue

Charlie Winston owns a residential cleaning service. She has 15 crews serving 5 zip codes, 5 sales reps, and she was frustrated. She needed to be everywhere at once and hated missing commitments. Yet, sales were flat for 2 years. Follow Up Fatigue Blocks Sales Charlie...