Spreadsheet Hands-On: Build a model to make predictions

Let’s use the model of our silver dollar collection to learn how a spreadsheet can help make predictions quickly and efficiently with a model we have already defined.

The examples shown below will use the Microsoft Excel spreadsheet but you can also use an open source spreadsheet such as Apache OpenOffice Calc or Google Sheets.

Step 1: Storing formulas in cells

Open your spreadsheet and click the plus-sign in a circle at the bottom of the window to get a new sheet. You can double-click on the name of the sheet to rename it if you wish.

1. In cell A1, type “Starting coins.”
2. In cell A2, type “Annual increase.”
3. In cell A3, type “Years owned.”
4. You can play around with various styles of decoration in the Home tab. The width of a column can be increased to the width of the longest cell contents by double-clicking the gray box at the top of the column. Try that on column A. You can also hover your mouse over the edge of the gray area until you get a side-to-side arrow that you can click into an pull the width of the column out or push it in. Here is an example of what it could look like at this stage.

    

   

    

5. We will input the formula into cell B5. Formulas in Excel start with an equal symbol “=” that tells the spreadsheet to make a calculation. For example, if you type =5*7 in cell A7 and Enter, the cell will display the result of the multiplication, 35. But if you just type 5*7 without the equal symbol, it will just display 5*7. Try it out now to see how it looks. The “=” tells Excel to do a calculation or perform a formula shortcut. When you are done, delete the contents of A7. We’ll need to use the cell in a little while.
6. In cell B5, type =B$2*B$3+B$1 and Enter. The green box should have populated with the number [latex]47[/latex] because we’ve asked it to multiply the value in cell B2 by the value in cell B3 then add the amount in cell B1. And right now, the value in cell B3 should be zero since that cell is empty.
7. Now you can use your formula to predict the number of coins owned any number of years after inheriting your collection, assuming you add 12 coins per year to it. How many coins will you have 5 years after inheriting the collection? To answer this question, type the number 5 into cell B3 and Enter. You will have 107 coins 5 years later if you continue diligently to add 12 coins per year.
8. You can also use this formula to predict different values. What if you sell 20 of them right away then increase by 12 per year? Or perhaps you keep all 47 but increase by 5 per year instead.  The formula will adjust to the new parameters you enter, but it will always return linear growth.

Step 2: Predicting input for a particular output

1. 1. Let’s write a new formula that returns the number of years it will take to reach a certain goal for the total number of coins in the collection.
   2. In cell A7 (delete any information you put there earlier), type “Coin goal.”
   3. In cell A8, type “Years required.”
   4. In our formula [latex]y=12x+47[/latex], we will let [latex]y[/latex] represent the Coin total we desire and solve for [latex]x[/latex].

       

      [latex]
      \begin{array}{rcl} y&=&12x+47 & \\
      y-47&=&12x & \text{subtract 47 on both sides} \\
      \dfrac{\left(y-47\right)}{12}&=&x & \text{divide by 12 on both sides} \\
      \end{array}[/latex]

       

   5. Using the formula solved for input, [latex]x=\dfrac{\left(y-47\right)}{12}[/latex], populate cell B8, with the appropriate substitutions for initial value (B1), slope (B2), and desired output (B7). Into cell B8 type, =(B$7-B$1)/B$2 and Enter.
   6. Set the Coin goal to 500 in cell B7, then Enter. Years required should be 37.75. Then, try adjusting the starting coins and annual increase to see how many or how few years you could reach your goal of 500 total coins.Storing a formula in a spreadsheet is useful for trying out different scenarios and making quick predictions. If the formula is one you use often, a spreadsheet can save you time and energy in the calculations.