Analyzing with Excel

By Helen Bradley | Posted August 23, 2007

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. 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 the Scenario tool you first need to create a spreadsheet with formulas that make calculations that 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:


Cell Number Cell Contents
C4 Shiny bright paper
C5 Cheap papers
C6 10
C7 385
C9 10
C10 15%
C14
4
C17 7.5%
C18 26

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.

Excel scenario screen shot – Naming cells
Scenarios are easier to work with if you name your cells first.
(Click for larger image)
.

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.

Excel scenario screen shot – Add scenario
Use the Add Scenario dialog to give your scenario a name and to configure the cells that will contain data.
(Click for larger image)
.

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.

Excel scenario screen shot – Adding data
Scenarios offer you the option of entering data using a small data form.
(Click for larger image)
.

When you are done, click OK to add the new scenario to the Scenario Manager dialog. You can continue and add 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.

Add Scenarios to Your Toolbar
You can add a Scenario dialog to your toolbar by right-clicking a toolbar and choosing the Customize tab and then Commands. From the Tools category, locate and select the Scenario listbox and drag and drop it onto a toolbar. Close the Customize dialog. You can now view a Scenario of your choice by selecting it in the Scenario list.

Edit a Scenario
To edit a scenario, choose Tools and then Scenario, select the scenario to edit and click the Edit button and then OK. Change any values that require alteration and click OK to save the changes. To view the results, click the Scenario in the list and click Show to view it.

Excel scenario screen shot – Compare scenarios
You can view any scenario from the Scenario Manager dialog by selecting it and clicking Show.
(Click for larger image)
.

Side-By-Side Scenario Reports
While the Scenario Manager makes it easy to view one scenario at a time, you may prefer to view the results side-by-side. To do this, create a Scenario Report by choosing Tools and then Scenarios and clicking the Summary button. Select the Scenario Summary option and click the Result Cells box.

Here you will enter the cells that you want to use as the basis for your comparison. In our situation we will choose the product name and supplier cells as this provides identifying information about the scenario, the number of items that we will purchase, the box price, shipping price and price per unit which lets us compare how much we will spend per item and overall.

To select these cells, click the first cell and then hold down the Control key while you click on the others in turn. You will select cells C4, C5, C15, C16, C19, and C20. Click OK when you are done. This creates a new sheet that displays the cells you selected as well as the cells that contain the data on which the scenario is based.

Excel scenario screen shot – Summary comparison
Choose the results you want to see and that will give you the best basis for your comparison.
(Click for larger image)
.

The first set of data in the report displays the current values in the worksheet. If these are simply duplicates, you can hide this column. The worksheet itself is an outlined worksheet so you can expand or collapse various areas of the worksheet using the buttons on the far left of the worksheet.

Notice that the order in which you clicked the cells you wanted to show is the order in which the cells are displayed in the scenario summary. You can use this feature to select cells in the order that you want to see them in the resulting report. You will find the scenario summary is useful tool for comparing side-by-side results for different items you are considering purchasing.

Excel scenario screen shot – Summary format
You can collapse and format your summary to get a report that makes comparison easy.
(Click for larger image)
.

You will need to delete the scenario summary worksheet and recreate the summary if the values in any of your scenarios change, since the summary report contains static figures and does not automatically update if you add new information.

Scenarios make a smart addition to your Excel toolkit and are a handy feature of the program that you can put to use when employing comparisons to aid in business decision-making.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!


Comment and Contribute


     

    Get free tips, news and advice on how to make technology work harder for your business.

    Submit
    Learn more
     
    You have successfuly registered to
    Enterprise Apps Daily Newsletter
    Thanks for your registration, follow us on our social networks to keep up-to-date