In the previous tutorial we discussed the fundamental ideas of building models and doing “What-If?” analysis. Now we need to take these ideas and put them into practice so you can see how this works. To do this I will create  a simple model of savings over time. Now, I do want to be clear that this is a very over-simplified model and should not be taken as a good predictor of actual results. The idea is to illustrate the techniques involved in building a model and doing “What-If?” analysis.

So. what are the variables, parameters, assumptions, etc. that we need? I have identified these in my model:

• An initial amount of money already saved. This is the starting amount you have.
• An amount of money you add to your savings each year.
• The rate of return on your savings

And that is it! As I said, this is a very simple model. And to make it even simpler I will assume that the added savings you put in your account is always added as a lump sum on December 31. This let’s me avoid the added complication of compounding. I know this is not realistic, but again I want to remember that the purpose of this exercise is to show you how to build the model and do the analysis.

These numbers will vary when we do our “What-If?” analysis, but for now I will use these numbers:

• Initial amount = 0
• Annual addition = 1,000
• Rate of Return = .05

So, here is how I do it:

First, I click on cell I1 to select it, and I enter the word Assumptions. Then I bold the word. But when I enter my assumptions I will need two columns, one for the Description, and one for the actual number. So while I have cell I1 selected, I hold down the Shift key and click on cell J1. With both cells selected, I can use the Merge and Center Cells button. This is on the Formatting toolbar, just above the Input Line, and looks like this:

 

 

 

 

Now, this button is normally greyed-out unless you have selected several adjacent cells. It does what it says it does, which is to combine the cells and center whatever is in the cells. This is a good technique for putting a heading on top of a group of columns, which is what I am doing here.

Then, in cell I2 I enter Initial Amount, and in cell J2 I enter 0. In cell I3 I enter Annual Addition, and in cell J3 I enter 1000. And in cell I4 I enter Rate of Return, and in cell J4 I enter .05. Now I have completed entering all of my assumptions, and I can move to the main model and start building that.

This simple model is actually pretty easy. In cell A1 I enter the word Year, in B1 I enter Beginning Amount, in C1 I enter Interest Earned, in D1 I enter Annual Addition, and in E1 I enter Ending Amount. So I have put in a heading row that covers the first 5 columns of the sheet. But to make it a little prettier I click on cell A1 to select it, hold down the Shift key, and click on Cell E1. I now have all five of my header cells selected. and then I  click the Bold button and the Center button on the Formatting Toolbar. That just makes it look a little better in my opinion.

Now, to fill in the model I start in cell A2. This will be the first year of the savings program so I can enter 1st here. Calc actually knows all about ordinal numbers (numbers like 1st, 2nd, 3rd are called Ordinals; numbers like 1,2,3 are called Cardinals).

Then in Cell B2 I want to put in the initial amount. Since that is in my Assumptions area I can enter the cell address, so it goes in as “=J2”. Or it could, but one useful thing to remember is that any reference to the Assumptions area is one that should never change if you later do a Fill. So in fact cell B2 should contain “=$J$2”.

In Cell C2 I want to calculate a return on my savings. In our simplified model, there is no compounding going on. Whatever money we have at the beginning of the year earns interest at the rate of return and is deposited into our account in December 31. So the formula is simple. We multiply the amount in column B by the Rate of Return, and that is the Interest Earned. In the first year, that is the amount in cell B2, multiplied by the Rate of Return, which is in our Assumptions area in cell J4. As before, any cell reference to the Assumptions area must be be a fixed reference, so the formula that goes into cell C2 should read “=B2*$J$4”.

Cell D2 is where we add to our savings each year, which we assume for simplicity is a lump sum added on December 31. This amount is stored in our Assumptions area in cell J3, so in cell D2 we make a reference to this cell, and since it is in our Assumptions we make it a fixed reference. So Cell D2 will contain “=$J$3).

The last cell on this row is E2, and it adds up the amount of money you had at the beginning of the year, plus the interest you earned on December 31, plus the amount you added to your savings as a lump sum on December 31. When these are added together you get your ending amount for the year. So cell E2 will read “=B2+C2+D2”.

This ends the first year, and completes row 2 of the model. We could go cell-by-cell through the spreadsheet filling in each cell manually, but that is not the best way to do this. We want to do a fill on this, but we have one more thing to do. You see, cell B3 is not like Cell B2. Cell B2 was filled with an initial amount from the Assumptions, but at the beginning of the second year we need to take into account what happened during the first year. And that means that the amount we have at the beginning of the second year is equal to the amount we had when the first year ended. And that is in cell E2. So in cell B3 we enter the address “=E2”.

Now, we can click-and-drag to fill the columns. When I first developed this simple model for college students, students were at an average age of 20, and I wanted to make a point about how savings over time works. So I usually carried the model out to 40 years. So I will go to cell A2, which right now has the label “1st”. If I click on it to select it, then use the fill handle to fill the column, it turns out that Calc is ready for this, and fills in the column with successive Ordinal numbers. By watching the black square that goes with my cursor I can easily do a fill up to the 40th year. When I do this, I get a column full of ordinals, this looks great.

Now, I go to Cell B3, select it, and do a fill of this column. But this is puzzling. In Cell B3 I had the number 1000, but all of the other cells read “0”. Why is this? Well, let’s click on Cell B4, which has a zero in it, and see what it contains. If we do, we see that it reads  “=E3”. And Cell B5 has “=E4, and so on. What happened is that the B column is referencing other cells that are empty right now. Each cell in this model is tied to the other cells, and the model won’t make any sense at all until it is complete. So, let’s fill the other columns. To make this go more quickly, I click on Cell C2 to select it, hold down the Shift key, and click on Cell E2. With all three cells  (C2, D2, E2) selected, the fill handle is on cell E2, so I fill all three columns.

Now I have the model completed, and all of the cells are filled with meaningful data. But it looks like I have way too many decimal places here. Money never needs more than two decimal places. I want to change how the numbers are formatted. In this case, I think the best thing is to change it for the columns. So I click in the letter B at the top of column B. This selects the entire column. Then I hold down the Shift key, and click on the letter E. I now have all four columns selected. I go to the Format menu, select Cells, then Currency. I click OK, and now my numbers are proper monetary amounts.

Now, one thing that can go wrong if you are not careful. If you did not make a fixed (or absolute) cell reference when first entered your formulas in cells C2 and D2, when you did the fill your numbers would get bad in a hurry. If you had left out the absolute reference in cell C2, for instance, when you did your fill Cell C3 would contain “=B3*J5”, instead of “=B3*$J$4” And since cell J5 is empty, Cell C3, and indeed all of the other cells in column C, would be showing Zeros. So if you get funny looking results, you have to check your cell contents to see where the problem occurred. Remember that we made a distinction between what a cell contains, and what it displays. And by clicking on a cell and looking at the the Input Line on the Formula Bar you can quickly find the error.

Returning to our spreadsheet, we see that by saving $1,000 per year for forty years, and receiving a 5% rate of return, we end up with $120,000 at the end of the forty years. So we tripled our money. But $120,000 is not a lot if you plan to live off of it in your retirement. But what is the best way to improve our result? We have three numbers in our Assumption area, let’s do some analysis

What-If? Analysis

To do the What-If Analysis all we need to do is copy our sheet. To examine changes to all three of our Assumptions we need 3 more copies. So hold down the Control key (or Option key if on a Mac), click on the tab for the sheet you just completed. It probably says “Sheet 1” if you didn’t rename it. Then drag it to the right and let go the mouse. You should have a duplicate that says “Sheet 1_2”. Do it one more time and you get “Sheet 1_3”, and one last time gets you “Sheet 1_4”. Since these are not very descriptive, I will rename Sheet 1 to “Base Model”, Sheet 1_2 to “High Start”, Sheet 1_3 to “High Savings”, and Sheet 1_4 to “High Return”.

I won’t make any changes to “Base Model”. That stays the way I built it as a reference against which I will make comparisons. For High Start I will put in a beginning amount in cell J2. For this example, I will put in $5,000 as a starting point. Then I go to the High Savings sheet, and double the amount I save each year, so now in cell J3 I will put $2,000. Finally, for the High Return sheet I will double the rate of return. So in Cell J4 I will enter .10, or 10%.

Now I can compare the results. In my Base case, I started with nothing, added $1,000 per year, earned 5%, and at the end of 40 years I have $120,800. If I started with $5,000, but still added $1,000 per year and earned 5%, I end up with $156,000. So adding $5,000 at the start adds about $35,000 to the final result. Looking at doubling the amount I save each year, going to $2,000 instead of $1,000, but keeping everything else the same, gets me to $241,600. In fact, what happens is that by doubling the amount saved I get double the payoff at the end. Finally, looking at doubling the rate of return, from 5% to 10%, but keeping everything else constant, my payoff climbs to $442,593! This is almost 4 times the base model amount, and nearly twice the payoff from saving twice as much.

So our “What-If?” analysis has revealed something interesting, which is that the best way to get a good retirement nest-egg is to focus on getting a high rate return rather than focusing on saving more. And for my college students that really was the point I was making. You would not want to use this overly-simplistic model to actually forecast how much money you will have, but using “What-If” analysis you can get valuable insights. And that is why this technique is so powerful.

I have placed a copy of the Calc spreadsheet I created for this tutorial here: Savings Model. Please feel free to download and inspect this file.

Listen to the audio version of this post on Hacker Public Radio!