Time Series
Forecasting and Regression
Authors:
Pat Dotson, Trent Halford, Michelle Ragghianti
Objective Hierarchies Mathematical Representation
Variables and Attributes Testing & Validation
Influence Diagram Implementation and Use
Objective
Hierarchies
|
The objective of this exercise is to run time series forecasting and regression on the data in the spreadsheet named demand.xls. Time series regression is a forecasting method that involves extrapolating historical data in order to create a basis for decision-making in the future. Extrapolation methods are quantitative methods that use past data of a time series variable to forecast future values of the variable. It looks for trends and seasonality in the data and builds a formula that can be used to predict values. Time series forecasts are helpful in business for predicting consumer demand, projecting expenditures, and estimating future revenues. Regression analysis is the study of relationships between variables to establish a mathematical model to predict dependent variable, such as demand, by substituting value(s) of independent variable(s).
These analyses enable us to forecast the next four periods for the variables average demand, average price, and average advertising and create an equation that ultimately predicts normalized share of market demand to assist in manufacturing and financial decisions of the organization. CB Predictor will be used to run the time series forecast and Excel will be used to run the regression.
This objective hierarchy is a graphical representation of the internal factors that drive the business model to profitability.
Variables
& Attributes
|
We know that demand is expressed as:
Firm Demand = Average Firm Demand x Normalized Share of Market
|
Variable |
How Measured |
Related to |
|
Time Series Forecasting |
||
|
Period |
In numerical order |
None |
|
Season |
Quarter of year (1, 2, 3, 4) |
Time of year |
|
Average Demand |
Units demanded |
Total demand divided by the number of firms in industry. Dependent on economy, industry, and competitive forces. |
|
Average Price |
Price in dollars |
Total sales divided by units sold. Dependent on economy, industry, and competitive forces. |
|
Average Advertising |
Dollars spent |
Total advertising expenditures divided by number of firms in industry. Dependent on economy, industry, and competitive forces. |
|
Average Advertising 1 |
Dollars spent |
Total advertising expenditures last year divided by number of firms in industry. Dependent on economy, industry, and competitive forces. |
|
Average Research & Development 1 |
Dollars spent |
Total research and development expenditures last year divided by number of firms in industry. Dependent on economy, industry, and competitive forces. |
|
Average Advertising 2 |
Dollars spent |
Total advertising expenditures two years ago divided by number of firms in industry. Dependent on economy, industry, and competitive forces. |
|
Average Research & Development 2 |
Dollars spent |
Total research and development expenditures two years ago divided by number of firms in industry. Dependent on economy, industry, and competitive forces. |
Regression Analysis |
||
|
Quarter |
1, 2, 3, 4 |
|
|
Firm |
Number |
Firm identifier |
|
NSOM |
Ratio: share of market |
Normalized share of market, measure of demand, determined by quality, reputation, advertising, research and advertising, and other factors. |
|
Prel |
Ratio: relative price |
Price divided by average price of industry for the quarter indicated. |
|
Arel |
Ratio: relative advertising expenditures |
Total advertising expenditures divided by average advertising expenditures of industry for the quarter indicated. |
|
NSOM1 |
Ratio: share of market |
Normalized share of market for last quarter. See NSOM. |
|
Rrel1 |
Ratio: relative R&D expenditures |
Total research and development expenditures of previous quarter divided by average research and development expenditures of industry for the previous quarter. |
|
Arel1 |
Ratio: relative advertising expenditures |
Total advertising expenditures of previous quarter divided by average advertising expenditures of industry for the previous quarter. |
|
Rrel2 |
Ratio: relative R&D expenditures |
Total research and development expenditures of two quarters previous divided by average research and development expenditures of industry for the two quarters previous. |
|
Arel2 |
Ratio: relative advertising expenditures |
Total advertising expenditures of two quarters previous divided by average advertising expenditures of industry for the two quarters previous. |
Influence
Diagram
|
This influence diagram is a graphical representation of the endogenous and exogenous factors that interface with each other to create demand for a product.
Mathematical
Representation
|
Average Firm Demand:
In order to develop a mathematical model to predict firm demand, two regressions need to be run, time series and least squares regression. The summary of the results of the time series forecast (run in CB Predictor) are as follows:
|
Methods |
RMSE |
MAD |
MAPE |
Durbin-Watson |
Theil's U |
Alpha |
Beta |
Gamma |
|
Holt-Winters'
Multiplicative |
320.9 |
236.87 |
13.892 |
1.445 |
0.587 |
0.19 |
0.001 |
0.001 |
CB Predictor determined that the Holt-Winters’ Multiplicative method was the best method for completing this time series forecast because it had the lowest Root Mean Square Error (RMSE), lowest Mean Absolute Deviation (MAD), and Mean Absolute Percentage Error (MAPE). Holt-Winters’ Multiplicative method accounts for level and trends as well as seasonality.
Level of the Series: Lt
= α*Yt/St-M + (1-α)(Lt-1 + Tt-1)
Trend: Tt = β(Lt - Lt-1) + (1 - β)Tt-1
Seasonality: St = γ* Yt/Lt + (1-γ) St-M
Forecast of Yt+k Ft+k = (Lt + kTt)St+k-M
M = number of seasons 4
k is the period ahead 3 months
Initialized Values – Seasonal Factor
1 1.273529
2 1.241947
3 0.922325
4 0.905081
Forecast:
|
Date |
Lower 5% |
Forecast |
Upper 95% |
|
25 |
2949.516688 |
3502.526156 |
4055.535625 |
|
26 |
2700.979192 |
3281.639134 |
3862.299076 |
|
27 |
2191.426408 |
2802.6474 |
3413.868391 |
|
28 |
2178.799599 |
2823.977313 |
3469.155026 |
After completing this time series regression, the residuals are then used in a least squares regression against the other variables in the AFD model. This exercise found that the only variables that were significant in the prediction were average advertising and average price.
|
Regression
Statistics |
|
|
Multiple R |
0.9187 |
|
|
0.8440 |
|
Adjusted |
0.8276 |
|
Standard Error |
134.5969 |
|
Observations |
22 |
|
ANOVA |
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance
F |
|
Regression |
2 |
1862591.38 |
931295.69 |
51.41 |
0.00 |
|
Residual |
19 |
344210.20 |
18116.33 |
|
|
|
Total |
21 |
2206801.58 |
|
|
|
|
|
Coefficients |
Standard
Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
18060.683 |
2276.76 |
7.93 |
0.00 |
13295.38 |
22825.99 |
13295.38 |
22825.99 |
|
Avg_Price |
-49.130 |
5.95 |
-8.26 |
0.00 |
-61.58 |
-36.68 |
-61.58 |
-36.68 |
|
Avg_Adv |
0.003 |
0.00 |
2.63 |
0.02 |
0.00 |
0.01 |
0.00 |
0.01 |
Models:
Level of the Series: Lt
= 0.19*Yt/St-M + (1-0.19)(Lt-1 + Tt-1)
Trend: Tt = 0.001(Lt - Lt-1) + (1 – 0.001)Tt-1
Seasonality: St = 0.001* Yt/Lt + (1-0.001) St-M
Forecast of Yt+k Ft+k = (Lt + kTt)St+k-M
Residual: 18,060.683 + (49.13)Avg_Price + 0.003Avg_Adv
Complete Model: Ft+k = (Lt + kTt)St+k-M + 18,060.683 + (49.13)Avg_Price + 0.003Avg_Adv
Normalized Share of
Market:
In order to predict the normalized share of the market, we ran least squares regression on the nsom spreadsheet data. All variables were found significant, here are the results:
|
Regression
Statistics |
|
|
Multiple R |
0.993207601 |
|
|
0.986461339 |
|
Adjusted |
0.985910347 |
|
Standard Error |
0.03190622 |
|
Observations |
180 |
|
ANOVA |
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance
F |
|
Regression |
7 |
12.75803 |
1.82258 |
1790.33686 |
0.00000 |
|
Residual |
172 |
0.17510 |
0.00102 |
|
|
|
Total |
179 |
12.93312 |
|
|
|
|
|
Coefficients |
Standard
Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|
Intercept |
15.9472 |
0.2598 |
61.3918 |
0.0000 |
15.4345 |
16.4600 |
15.4345 |
16.4600 |
|
Prel |
-16.7934 |
0.2559 |
-65.6164 |
0.0000 |
-17.2985 |
-16.2882 |
-17.2985 |
-16.2882 |
|
Arel |
0.7312 |
0.0156 |
46.9668 |
0.0000 |
0.7005 |
0.7620 |
0.7005 |
0.7620 |
|
NSOM1 |
0.4448 |
0.0110 |
40.4168 |
0.0000 |
0.4231 |
0.4665 |
0.4231 |
0.4665 |
|
Rrel1 |
0.1789 |
0.0260 |
6.8755 |
0.0000 |
0.1276 |
0.2303 |
0.1276 |
0.2303 |
|
Arel1 |
0.2570 |
0.0172 |
14.9033 |
0.0000 |
0.2229 |
0.2910 |
0.2229 |
0.2910 |
|
Rrel2 |
0.1521 |
0.0278 |
5.4803 |
0.0000 |
0.0973 |
0.2069 |
0.0973 |
0.2069 |
|
Arel2 |
0.0794 |
0.0170 |
4.6645 |
0.0000 |
0.0458 |
0.1130 |
0.0458 |
0.1130 |
Residual Model:
NSOM
= 15.9472 + (16.7934)Prel + 0.7312Arel + 0.4448NSOM1 + 0.1789Rrel1 +
0.2570Arel1 + 0.1521Rrel2 + 0.0794Arel2
Using the models from the time series forecasting, least squares regression on the AFD residuals, and the least squares regression on the NSOM data, we can create a DSS interface that provides the user the needed data for business decisions. A screenshot of this interface is below:
Interface for DSS:
|
Inputs |
Outputs |
||||||||||
|
|
|
|
|
|
|
|
|
|
|||
|
|
Macro |
|
|
|
|
|
|
|
|||
|
|
Period |
25 |
|
|
|
||||||
|
|
Number of Firms |
10 |
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
AFD |
3,241 |
|
|||
|
|
Industry Inputs |
|
|
|
|
|
|
|
|||
|
|
Avg. Price |
380 |
|
|
|
NSOM |
1.91 |
|
|||
|
|
Avg. Advertising |
100,000 |
|
|
|
|
|
|
|||
|
|
Avg. R&D |
30,000 |
|
|
|
Firm Demand |
6,182 |
|
|||
|
|
|
|
|
|
|
|
|
|
|||
|
|
Firm Inputs |
|
|
|
|
Market Share |
19.07% |
|
|||
|
|
Price |
365 |
|
|
|
|
|
|
|||
|
|
Advertising |
115,000 |
|
|
|
Firm Revenue |
$
2,256,430 |
|
|||
|
|
R&D (t-1) |
30,000 |
|
|
|
|
|
|
|||
|
|
Advertising (t-1) |
105,000 |
|
|
|
|
|
|
|||
|
|
R&D (t-2) |
25,000 |
|
|
|
|
|
|
|||
|
|
Advertising (t-2) |
100,000 |
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|||
Testing
& Validation
|
To test the model, and to view the behind the scenes calculations and regression results, go to the following link:
This model is designed to calculate the outputs for the four periods following the end of the historical data (25, 26, 27, and 28.) If periods higher than these are input, the user will get an error message and no data in the output section. This interface allows the user to change many inputs to see the effect on AFD, NSOM, Firm Demand, Market Share, and Firm Revenue. These inputs include number of firms (cannot be less than 2), industry inputs (these change with new historical data and firms entering and exiting the industry), and firm inputs such as price and expenditures. The industry inputs cannot be controlled by individual firms, but the firm inputs can be controlled and are used in the model to evaluate expected outputs. These outputs help managers make decisions about these inputs.
Implementation
& Use
|
This model could be used in a much larger model that incorporates the firm’s proforma financial statements and manufacturing data. By changing the inputs, the user could see not only the outcome of demand, but also what affect this would have on the firm’s financial statements. Also, a manufacturing module could be linked to the DSS that provided data on capacity and costs.
Many reports could be written to use with this system, including proforma financial statements, demand forecasts, recommended advertising and research and development expenditures and cost data. Analyses could be conducted to answer any questions about how much to spend on advertising and expenditures, what to price the product to get the desired market share and resulting revenue. This would be an example of goal seeking where the desired output is entered and the variables are found that create that result. This module could also be used to evaluate scenarios such as good economy or recession. The module could also be used to run what-if analyses, for example to see the result in a big increase in advertising executives salaries that cause advertising expenditures to increase for the same amount of advertising.