128 J1.09: Section 4 Part 1
Section 4: Building redefinition of variables into model formulas
Redefinition of input parameters
In several different modeling problems so far (including those using population and sales data in this topic), we have redefined the input variable (e.g., to “Years since 1992” or “Years since 1790”) so that the parameter values for the model did not refer to projected values at 0 AD. We did this by replacing the “Year” column with a “Years since …” column, changing the numerical values appropriately by subtracting the first year in the dataset. This approach produces correct answers with reasonably-sized parameter values for the model, but having to change back and forth between the “year” and the “years since” version of the parameter is awkward, and graphs made with the “years since” version do not directly display the year, as would be convenient.
|
These problems can be avoided by some simple changes to the formula of the model. The basic idea is that for a problem where we previously would have used “Years since 1992” and subtracted 1992 from each year listed in the data table, we will now leave the data table alone but instead replace each “x” in the model formula by the expression “(x–1992)”. The examples below use the retail sales data from an earlier section to show what effect this model-modification strategy would have for linear and exponential models of that dataset. Remember that the formula in cell C3 is expressed in spreadsheet format, so that what we do there is replace “A3” with “(A3-1992)”. [Note: Always include the parentheses shown, so that the subtraction is done before any other operation.]
|
|
Example 8: Modify the linear model for US Retail Sales so that the parameters are easy to find, without subtracting 1992 from each of the input x values in column A.
- Insert a new Linear Model worksheet.
- Copy the data into columns A and B, leaving the year values as they are.
- Set cell H3 to the phrase “Model output in 1992” (replacing the label “Intercept”).
- Modify the content of cell G1 by replacing the “x” in the expression by “(x-1992)”.
- Modify the content of cell C3 by replacing “A3” by “(A3-1992)”.
- Spread the modified formula in cell C3 down to row 10 to match the data.
- Spread the formulas in D3 and E3 down to row 10.
- Make a scatter plot of columns A, B, and C (select the rectangle A1:C10) to show the model and the data together so that you can watch how well they match while you adjust the parameters.
- Adjust the model parameters, using the following steps:
- Set G3 to 1,952, which is the dataset value for 1992 (this may be adjusted further in step [c]).
- Adjust the G3 parameter until the model graph closely follows the shape of the data.
- If needed for the best fit, make small adjustments to G3 so that the model moves up or down.
- Use the model to predict sales for any specified year (e.g., 2000) by entering the year number for which you want a prediction at the bottom of column A.
Resulting worksheet:
| A | B | C | D | E | F | G | H | |
| 1 | x | y data | y model | Linear model: y = m * (x-1992) + b | ||||
| 2 | Year | Sales (B$) | prediction | y = 140 * (x – 1992) + 1952 | ||||
| 3 | 1992 | 1,952 | 1,952 | 1,952 | b: Model output in 1992 | |||
| 4 | 1993 | 2,082 | 2,092 | 140 | m: Slope | |||
| 5 | 1994 | 2,248 | 2,232 | |||||
| 6 | 1995 | 2,359 | 2,372 | |||||
| 7 | 1996 | 2,502 | 2,512 | |||||
| 8 | 1997 | 2,611 | 2,652 | |||||
| 9 | 1998 | 2,746 | 2,792 | |||||
| 10 | 1999 | 2,995 | 2,932 | |||||
The formula in C3 is now “=$G$4*(A3-1992)+$G$3”
Things to notice about this process:
- The values on the x-axis of the graph are the actual year numbers, not years since 1992.
- The final parameter values are the same values that were found by the subtract-1992 method.
- When making predictions with the model, there is no need to subtract 1992 from the x value.
- There is nothing in this process that makes use of the fact that the formula was linear—this means that exactly the same instructions can be followed for any model whose G3 parameter gives the prediction of the model when x = 0.
Example 9: Modify the exponential model for US Retail Sales so that the parameters are easy to find, without subtracting 1992 from each of the input x values in column A.
Follow the instructions for Example 8, except use a copy of the Exponential Model worksheet
Top of the resulting spreadsheet:
| A | B | C | D | E | F | G | H | |
| 1 | x | y data | y model | Exponential model: y = a * (1+r)^(x-1992) | ||||
| 2 | Year | Sales (B$) | prediction | y = 1952 * (1 + 0.06) ^ (x – 1992) | ||||
| 3 | 1992 | 1,952 | 1,952 | 1,952 | a: Model output in 1992 | |||
| 4 | 1993 | 2,082 | 2,069 | 0.06 | r: Growth rate | |||
The formula in C3 is now “=$G$3*(1+$G$4)^(A3-1992)
