Practical Spreadsheet Modeling Using @Risk by Lehman Dale; Groenendaal Huybert;

Author:Lehman, Dale; Groenendaal, Huybert;
Language: eng
Format: epub
Publisher: CRC Press LLC
Published: 2019-11-04T00:00:00+00:00

4.8 Simulation within Regression Models

Returning to our advertising effectiveness case, Figure 4.21 suggests that the regression model may be a good description of how an increasing advertising budget increases sales. The R squared is relatively high, the P-value that the advertising coefficient is zero is very low (P-value = .000006), the relationship makes logical sense, and the residuals appear to be randomly scattered around the regression line.33 Suppose we wish to forecast the sales level that would result from a monthly advertising level of $7500. We could use the estimated linear regression, Sales = 1.76 + 0.33*Advertising, to derive the Sales forecast. But because of the limited amount of data, and because of chance, there is uncertainty about the true relationship between advertising and sales. Fortunately, the regression analysis output also provides evidence regarding the level of uncertainty in the relationship. Since we have the standard deviations for the intercept (0.33 with standard error = 1.43) and slope (1.76 with standard error = 0.23) and the regression assumptions imply that the true intercept and slope will follow a Student-t distribution (in this case, with seven degrees of freedom) around the sample estimates, we have the information required to simulate the possible intercepts and slopes of the true advertising–sales relationship. There is one complication, however. The uncertainty distributions of the intercept and slope are not independent—as the intercept varies, the slope of the least-squares regression line though the original data points will depend on this intercept. If an intercept higher than 0.33 were simulated, we would find that the least-squares regression line through the data points would need to be flatter, resulting in a expected slope lower than 1.76. Conversely, if we simulate a intercept lower than 0.33, then the line will tend to be steeper (i.e., have a higher slope).

To correctly simulate the uncertainty in the relationship, we need to adopt a different procedure than simply simulating the intercept and slopes independently. The method we use and illustrate here is called a parametric bootstrap: viewing the current data as one random sample of data points from the underlying process, we will generate additional random samples of new data (a.k.a. bootstrap samples) and estimate the least-squares regression model for each of these bootstrap samples. The resulting simulation then considers full variability inherent in our data.

Regression Model4.xlsx contains this parametric bootstrap model for our advertising-sales data. Generating random samples of sales data relies on the use of the standard error of the residuals around the regression line. This is shown in Figure 4.21 as the Standard Error in the Regression Statistics table (2.09 in this case). It can also be found by using the STEYX function in Excel. Figure 4.22 shows the simulation model.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.