The next topic is extremely important because it addresses where most beginning users of spreadsheets get into trouble. First, understand that building models and doing “What-If” analysis is fundamental to the success and widespread adoption of spreadsheets all over the world. A model can be thought of as a mathematical representation of a process of some kind. It could be financial, such as projecting my sales over the next year, or perhaps working out when my car loan will be paid off. Or it could be scientific, such as projecting out the reaction times and quantities in a chemical reaction. The only real requirement is that whatever you are modeling has to be something that can be represented using mathematical formulas of some kind.
“What-If” analysis takes this model and lets you experiment to see how how changes in different variables affect the results in your model. If I am figuring out when my car loan will be paid off, I might ask how paying an extra $20 per month against the principle would affect my results (presumably, it should lead to getting it paid off sooner if I set the model up correctly.) Or in the case of the chemical process, how would different temperatures or pressures affect the reaction times and quantities? By experimenting with different values in my model I can do this comparison easily. But only if I built the model properly in the first place.
And the key here is to understand when to use numbers, and when to use cell addresses and formulas. And almost always the answer is to use cell addresses and formulas. When I was teaching Corporate Finance to college students I assigned them a project of creating a business plan for a hypothetical business. I didn’t particularly care about what the business was, I was really trying to teach them proper technique. So if their business involved selling snow to Eskimos, I did not care. But they had to build a business plan in a spreadsheet. And they had to do “What-If” analysis as part of this project.
All Constants Go In An Assumptions or Constants Area
The first rule is that the only place I would let them enter raw numbers was in a special, designated area called Assumptions, though if you wanted to name it Constants, or Parameters that is not a big deal. The point is that this is where you would place variables, starting numbers, or whatever. For my Corporate Finance students doing a business plan, this would be where they would place the price of the product, the cost-per-unit of producing it, the interest rate at which they could finance operations, etc. I would usually have them place this to the side of the main model, where you could see it, but out of the way of the main rows and columns of the spreadsheet. Then they were to use these numbers by creating a cell address that pointed to the particular number in the assumptions area. So if the price of the product was in cell I4, they could use that anywhere in the model simply by referencing this address. If they wanted it to appear in a calculation, they would use, for example, something like
=A1*I4
Now this is good as far as it goes, but suppose you then wanted to do a fill. You would not want this address to change, since this single cell is a constant in the calculations. So the way to fix this is to put in dollar signs:
=A1*$I$4
Now, now matter whether you filling down a column or across a row, the cell for the product price will not change.
But suppose you wanted to allow for inflation? Let’s say we assume that prices will rise by .25% each month, and we are doing a business plan with 12 months in it. You might think you could just plug in a multiplier. Suppose you had an number for each month, so the first month of the fiscal year was 1, the second month as 2, and so on. And lets say that these numbers appear in row 2 of the spreadsheet, and that there are 12 columns for the 12 months. Just adjust the formula something like this:
=A1*(A2*.25*$I$4)
This would work, but if you were my student you just lost credit. Remember, every single one of your assumptions or parameters must go in the Assumptions area. And the rate at which prices rise each month is an assumption. It should go there. So now go back to the spreadsheet, put an entry in the Assumptions are of .25 and let’s say it ends up in cell I7. Now I can rewrite the formula correctly as
+A1*(A2*$I$7*$I$4)
Now I have a formula made up entirely of cell addresses and mathematical operators, things are good.
When I was teaching, I explained all of this and told my students that any time I saw a raw number outside of the Assumptions area they would lose credit. And I would literally click on every cell looking for this mistake. Theoretically, a student could wind up with a negative number for a grade on this project. But I am not as heartless as this may sound. I also had the policy that at any time during the semester I would, on request, review what they had done so far and give them feedback on whether they were on the right track. After all, in the world of business you are usually expected to check if you are on the right track if the boss gives you an assignment, and not taking the time to check marks you as a fool. So I liked teaching multiple lessons at one time.<g>
“What-If?” Analysis
This is the other part of this powerful technique, and you can’t do it properly if you don’t get the model building part right. The idea there is to have multiple copies of the model, but change one or more parameters each time. And this is not hard at all.
The way to do this is to build the initial model on the first sheet of your spreadsheet, which is the one that on the bottom has a tab saying “Sheet 1”. If you built it following the principles I outlined above, you can now do the magic. You can copy the entire sheet, and it is easy. Just hold down the Control key (or Option key if you are on a Mac), click on the Tab that I will assume says Sheet 1, and drag it to the right. Release the mouse button, and you will have a copy that is named Sheet 1_2.
With a copy, you now can do the first “What-If?” analysis. For instance, suppose inflation is higher than .25% per month? Well, just go to cell I7 in your copy and change it to what you think is a plausible alternative, such as .35% per month. The original model on Sheet 1 is still there, untouched, but on this copy as soon as you change cell I7 the entire spreadsheet recalculates to incorporate this new number.
And this is as good a place as any to mention that you don’t need to leave your sheets with names like Sheet 1, Sheet 1_2, or whatever. Just right-click on the tab, in the menu that comes up you can rename the sheet. I would give it something descriptive based on what you do with your copy. For instance, you might name Sheet 1 something like “Base Model”. Then if you go to your copy you can call it the “High Inflation” version, and so on.
The point is that you can explore as many different scenarios as you can think of, and see just how big a difference it makes to the end result. Maybe your business plan is very sensitive to changes in the finance rate of borrowing, but less sensitive to changes in the cost of production. That might be very valuable information to have, and by doing “What-If?” analysis you can bring that out. But it should also be clear by now that if you were not totally rigorous in building your model, if you let some parameters slip into the body of the model instead of being in a separate Assumptions area, you would be seriously hampered in doing this analysis. This is probably the single most important principle in spreadsheet use that you need to understand. And to illustrate, in our next lesson we will build a simple model of savings over time and use it to do some “What-If?” analysis.
Listen to the audio version of this post on Hacker Public Radio!
