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

3 Tips to Help You Close More Sales

3 Tips to Help You Close More Sales

As an owner or VP of Sales, your watchful eyes and sales processes are keys to success. You have to close your own sales, guide sales reps, and know whether you are on track. Your purpose is to exceed your sales goals. As a sales leader, how do you: Track your sales...

Now I LOVE My CRM’s Secret Sauce

Now I LOVE My CRM’s Secret Sauce

How can someone LOVE their CRM? First, there are reasons to hate CRMs. Some are too limited, promise the world but deliver headaches and no support. Others are complicated and hard to use.   CRMs come in all sizes and 3 broad levels:   Electronic rolodex...

“I Used to Hate CRMs”

“I Used to Hate CRMs”

“Oh yeah, I hated CRMs” said so many of our clients. While funny, it is a recurring theme. So many prospects focus on what if it fails. Others focus on what if it succeeds and builds revenue.    Many clients confess to resisting their consultants and sales teams...