Tweaking the Numbers

The good news is that Excel contains functions that make tweaking not only easy but fun. I’ll demonstrate how to set up a worksheet that illustrates graphically variable changes in a scenario—and it will do it in real time.

For this example, I’ll use a classic labor/capital management problem. Labor costs are always present and variable, usually increasing linearly with production volume. Therefore, the ongoing costs of labor reduce a product’s net margin. On the other hand, capital equipment is eventually paid off and capital costs tend to decrease when production volume rises. Further, the debt to buy capital equipment to manufacture the product is incurred before production even begins—delaying the day when a profit is turned.

So the problem to be solved is: How many years will it take to recover an initial capital investment given a certain interest rate, initial sales volume and sales growth rate? And what will be the long-term return on investment?

If you wish to follow along as I set up the solution, you can create the necessary spreadsheet using the directions that follow or download the completed file from www.igetit.net/joa .

GETTING UNDER WAY

To create the file, open a new Excel workbook with two worksheets. Label one Graph and the other Data , as shown in exhibit 1 . Graph will contain the variables to tweak and the charted results. Data will store the underlying data and formulas for calculating the results.

For convenience, create a copy image of the file, setting the sheets side by side on the screen. To do that, go to Window, New Window and then return to Window and click on Arrange , which brings up the menu shown in exhibit 2

Click on the Vertical radio button; that will display the sheets vertically, side by side, as shown in exhibit 3 . If you have other workbooks open at the same time, be sure to check the Windows of active workbook box so that you see only the new workbook in the panes.

Set up the Graph worksheet as shown in exhibit 4 labeling cells for Labor, Capital, Initial Outlay, Profit Margin, Annual Growth and First Year Revenues.

For reasons that will become clear, I added /100 to the formulas so the results will display as percentages. However, they don’t need to be displayed as formulas; that’s just for this illustration.

Go to the Data worksheet and set up the time period during which you want to observe the changes as you tweak the data. I’ve selected 10 yearly intervals. It could be longer or shorter and, depending on your industry, you may wish the intervals to be other time periods, such as quarters or months.

Label the A column Year, and add the numbers 1 through 10 in cells A2 through A11, so it looks like exhibit 5 .

Label the B column Labor and the C column Capital. Then, in cell B2, enter this formula

=(Graph!$B$6*((1+Graph!$B$5)^(A2-1)))*Graph!$B$3-Graph!$B$2

This represents first-year revenues times the first-year’s growth (0%) times the labor profit margin minus the labor capital outlay.

In cell B3, enter

=(Graph!$B$6*((1+Graph!$B$5)^(A3-1)))*Graph! $B$3+B2

This formula accumulates profit from the current year, accounting for growth, and adds it to the previous year.

Use the AutoFill feature to drag the formula all the way to B11. (See “How to Use AutoFill,” below.)

In cell C2, enter

=(Graph!$B$6*((1+Graph!$B$5)^(A2-1)))*Graph!$D$3-Graph!$D$2

This represents first-year revenues times the first year’s growth (0%) times the capital profit margin minus the labor capital outlay.

Finally, in cell C3, enter

=(Graph!$B$6*((1+Graph!$B$5)^(A3-1)))*Graph! $D$3+C2

This formula accumulates profit from the current year, accounting for growth, and adds it to the previous year. Again, use AutoFill to drag the formula all the way to C11, as shown in exhibit 6 .

ADDING THE CONTROLS

In this step you will add the controls to adjust the tweaking.

Return to the Graph sheet and go to View, Toolbars, Control Toolbox. Click on the blue designer’s triangle in the top left to indicate you want to design controls, as shown in exhibit 7 .

Next, click on the Spin Button and, as you hold down the Alt key, drag the icon to cell C2 and then release the mouse button. (Holding down Alt maintains the size of the Spin Button even if the cell size changes.) If the Spin Button does not show in the menu, click on the down arrow at the upper left corner of the menu to bring up an expanded menu and put a check next to the Spin Button icon to add it to the Control Toolbox menu.

Now adjust the properties of the Spin Button so a click on it will produce the desired results. To do that, return to the Control Toolbox and click on the Properties icon . That will evoke a menu, similar to the one in exhibit 8 ; it will be labeled SpinButton1 .

Adjust the following properties, leaving untouched the rest of the default settings (as shown in exhibit 8 ):

LinkedCell: Type B2 in this space because we want the value to appear in cell B2.

Max: Type 1000000 because we don’t plan to spend more than $1 million on a project.

Min: Type 10000 because none of the projects will cost less than $10,000.

SmallChange: Type 10000 so that each single click, up or down, will change the value by $10,000 increments.

Value: Type 10000 . That sets the initial value at $10,000 when the worksheet is first loaded.

Since the controls will not function in Design mode, when you’re finished adding the buttons and setting their properties, click on the blue designer’s triangle again to get out.

Close or move the toolbar out of the way so you can see the cells on the Data sheet. Now if you click on the buttons, the values will change.

The button can handle only whole numbers, so if you want to use percentages, you have to “trick” Excel; that’s why we entered the formulas on the Graph sheet divided by 100. Also, set the linked cell to be the same one that is occupied by the control button so it’s hidden behind it.

Repeat these steps for cells C3, C5 and C6, E2 and E3, following the table below for the values.

GRAPH IT

The next step is to make the solution visual by converting the numbers into a graph on the Graph worksheet. To begin, close the copy image of your workbook as you would any file. Then highlight all the data on the Data sheet—that’s right, the Data sheet, not the Graph worksheet—and click on Insert, Chart to bring up the Chart Wizard . Follow the wizard’s steps to create your favorite type of chart; in this case I used a line chart. When the wizard asks you where to place the chart, select the Graph worksheet as the target.

When you open the Graph sheet, you’ll see both the control information and the chart (see exhibit 9 ). Size the chart to fit neatly under the control buttons.

START TWEAKING

To begin tweaking, click on any of the control buttons—either up to increase or down to decrease the amount—and watch how each change affects the relationship between the Labor and Capital graph lines.

While it seems like a lot of work, once you’ve created a few what-if charts, you’ll be amazed how quickly you can do them. Moreover, you’ll discover how powerful they are because they take a complex subject and make it easy to understand with a real-time illustration tool. You’ll soon discover how many scenarios you can examine using this approach.

THEO CALLAHAN is president of I Get It! Development, a consulting firm based in Los Gatos, California, that develops custom software and offers custom business-process training programs. His e-mail address is theo@igetit.net .