## 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.]

 US Retail Sales Year Sales (B$) 1992 1,952 1993 2,082 1994 2,248 1995 2,359 1996 2,502 1997 2,611 1998 2,746 1999 2,995 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. 1. Insert a new Linear Model worksheet. 2. Copy the data into columns A and B, leaving the year values as they are. 3. Set cell H3 to the phrase “Model output in 1992” (replacing the label “Intercept”). 4. Modify the content of cell G1 by replacing the “x” in the expression by “(x-1992)”. 5. Modify the content of cell C3 by replacing “A3” by “(A3-1992)”. 6. Spread the modified formula in cell C3 down to row 10 to match the data. 7. Spread the formulas in D3 and E3 down to row 10. 8. 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. 9. Adjust the model parameters, using the following steps: 1. Set G3 to 1,952, which is the dataset value for 1992 (this may be adjusted further in step [c]). 2. Adjust the G3 parameter until the model graph closely follows the shape of the data. 3. If needed for the best fit, make small adjustments to G3 so that the model moves up or down. 10. 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”

 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)