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

Contact us for a FREE X2CRM Demo

Learn how X2CRM can help you sell more with less effort

FREE ebook:
Build More Revenue with Less Follow Up Fatigue

Recent Blogs

7 things to look for in CRM software for small business

7 things to look for in CRM software for small business

Shopping for CRM software can be like shopping for a vehicle. When you look for a vehicle, you need to decide if you are looking for a sedan, SUV, or truck. Do you need all-wheel drive or front-wheel drive? Do you need extra safety features like lane-change indicators...

How to Introduce a New Product to your Customers with your CRM

How to Introduce a New Product to your Customers with your CRM

In our last blog, we shared the story of Gerald Manes who owns a security system installation company called GMInstalls. From the time he implemented his company’s CRM, the quality of leads increased and the cycle to close a sale shortened. Gerald was happy and began...

Marketing and Sales Pain Points and How to Overcome them

Marketing and Sales Pain Points and How to Overcome them

Gerald Manes, known as GM, owns a security system installation company and employs 5 salespeople, 10 technicians, and 2 administrators. GM also oversees marketing and sales. Over 2 years ago, he decided to implement a CRM (customer relationship software). He was...

Request an X2CRM Demo