Exploring Office 2007: Analyzing with Excel Working with Excel's Scenario Manager Helen Bradley
As a business owner you spend a lot of your decision-making time comparing different business scenarios. It might be the cost of a new product, tweaking budget figures, or it might be comparing different leases or products you're considering purchasing.
Regardless of what you're calculating you want to compare different solutions to determine which is best for you. Microsoft Excel is a handy tool for analyzing different scenarios, and its Scenario manager tool helps you manage the task of comparing options side-by-side.
Create a Worksheet Model
To use Excel's Scenario tool you first need to create a spreadsheet with formulas that make calculations you'll use as the basis for your analysis. This type of worksheet is typically called a model, and it is here that you will enter the numbers for each of the items you are comparing.
The example we'll use is a product-pricing calculator that calculates the total unit price of items purchased taking into account bulk discounts, sales tax, and shipping and handling costs. This calculator is a useful way to calculate and compare like products that come in different quantities and come from different suppliers.
The worksheet contains cells into which you place information and cells that make calculations — each is differently colored. If you're interested in creating this worksheet yourself so you can follow along with the article, here are the cells and their contents:
Cell Number
Cell Contents
B2
Product Pricing Calculator
B4
Product
B5
Supplier
B6
Number in box
B7
Price per box
B8
Price per unit
B9
Boxes for discount
B10
Bulk discount
B11
Savings per unit
B12
Discounted unit price
B13
Discounted box price
B14
Boxes required
B15
Number of items purchased
B16
Total box price
B17
Sales Tax
B18
Shipping and Handling
B19
Total shipping price
B20
Total per unit price(inc ship&tax)
C8
=C7/C6
C11
=C8*C10
C12
=C8-C11
C13
=C12*C6
C15
=C14*C6
C16
=IF(C14
C19
=(C16*(1+C17))+C18
C20
=C19/(C6*C14)
Once you have created your model, save your worksheet. Now you're ready to get started. Start by entering the data for the first product you are interested in. Here's some data to get you started:
Before actually creating your first scenario it helps if you name the cells in the worksheet so that you see a description of the data required rather than cell references later on. To do this select the model's entire data area, in this case the range B4:C20, and choose Insert, Name, Create. Select the Left Column checkbox, disable all other checkboxes and click OK. This names all the cells in column C with the entry from the adjacent cell in column B.
To set up your first scenario, click in cell C4 and choose Tools, Scenarios. Click Add to add a new scenario and type a name for the scenario that describes the information it contains. For our sample data, "Shiny bright paper, Cheap Papers" is a good title.
Click in the Changing Cells box and hold the Control key as you click on every cell in the worksheet that contains data that changes with each scenario. In our case these cells are the ones we entered the data into, cells C4, C5, C6, C7, C9, C10, C14, C17 and C18. Do not click any of the cells that contain formulas.
If desired, you can type a note in the Comment area that relates to this particular scenario. When you have completed this, click OK and the Scenario Values dialog will appear. This Scenario Values dialog displays an input box for each of the cells that you selected. Right now, because the boxes already contain the correct information, simply click OK to add this scenario as the first scenario in the Scenario Manager dialog.
You're now ready to enter details for the second product you wish to compare. While you could do this directly into the worksheet cells, you can also do it using the Scenario Manager dialog. To enter the data using the Scenario Manager dialog, click the Add button, type the scenario name, type any comments and click OK. Now in the Scenario Values dialog, type the values that relate to the various cells of the worksheet. This is more like entering data into a form than entering data direct into worksheet cells.
When you are done, click OK to add the new scenario to the Scenario Manager dialog. You can continue adding other scenarios, each of which contains information for a single product or supplier. Once you have done this you can manually compare different scenarios using the Scenarios Manager dialog by selecting a scenario to view and clicking Show. The figures you entered for that particular scenario appear in the worksheet cells and the Excel calculates the new results.
