Problem Description for the Assignment Solution:
Analysis of Promotional Variables and Sales Forecasting
In this data analysis assignment, we aimed to explore the impact of various promotional-related independent variables, namely Consumer Packs (CP) and Dealer Allowances (DA), on sales. We used backward stepwise regression to identify which of these variables were significant in explaining the variation in sales. Additionally, we examined the presence of trends and seasonal factors in the data.
Assignment Solution:
1. Identification of Significant Variables
We began by assessing the significance of Consumer Packs (CP) and Dealer Allowances (DA) at different time points. Our stepwise regression analysis revealed the following significant variables:
- CP(t)
- CP(t-2)
- DA(t)
- DA(t-1)
The overall model had a P-value below 0.05, indicating that it effectively represented sales.
SUMMARY OUTPUT
| Regression Statistics | |
|---|---|
| Multiple R | 0.9752234 |
| R Square | 0.9510607 |
| Adjusted R Squ' | 0.9184345 |
| Standard Error | 17169.714 |
| Observations | 11 |
ANOVA
| df | SS | MS | F | Significance F | |
|---|---|---|---|---|---|
| Regression | 4 | 3.4374E+10 | 8593454329 | 29.1502075 | 0.00045164 |
| Residual | 6 | 1768794478 | 294799080 | ||
| Total | 10 | 3 6143F+10 |
| Coefficients | standard Error | t Stat | P - value | Lower 95 % | Upper 95 % | Lower 95.0 % | Upper 95.0 % | |
|---|---|---|---|---|---|---|---|---|
| Intercept | 334824 | 19673.66 | 17.01889 | 2.6323E - 06 | 286684.3 | 382963.7 | 286684.3 | 382963.7 |
| CP ( t ) | 0.550573 | 0.109532 | 5.026583 | 0.002388 | 0.282557 | 0.818589 | 0.282557 | 0.818589 |
| CP ( t - 2 ) | -0.32422 | 0.086762 | -3.73686 | 0.009658 | 0.536519 | -0.11192 | -0.53652 | -0.11192 |
| DA ( t ) | 0.106077 | 0.020753 | 5.111315 | 0.002197 | 0.055295 | 0.156858 | 0.055295 | 0.156858 |
| DA ( t - 1 ) | -0.08973 | 0.021088 | -4.25492 | 0.005351 | -0.14133 | -0.03813 | -0.14133 | -0.03813 |
Table:significant variables for the promotional-related variables
2. Analysis of Time Trend
We investigated whether time (i.e., the month) had a significant effect on our final model. However, the results indicated that time did not add significant explanatory power to the model, as the P-value for time was greater than 0.05.
SUMMARY OUTPUT
| Rtgrtssion Statistics | |
|---|---|
| Mu tiple R | 0.9809184 |
| R Square | 0.9622009 |
| Adjusted RSqu• | 0.9244018 |
| Standard Error | 16S29.718 |
| Observations | 11 |
ANOVA
| df | SS | MS | F | Significance F | |
|---|---|---|---|---|---|
| Regression | 5 | 3.4776E+10 | 6955290781 | 25.4556624 | 0.00144838 |
| Residual | 5 | 1366157886 | 273231577 | ||
| Total | 10 | 3.6143E+10 |
| Coefficients ;tondotd Error t Stat P-¥olue Lower95% Upptr 951' Lower 95.0% Upper 95.0% | ||||||||
|---|---|---|---|---|---|---|---|---|
| Intercept | 334839.183 | 18940.3389 | 17.6786268 | 1.0624E-05 | 286151.492 | 383526.874 | 286151.492 | 383526.874 |
| CP(t) | 0.55685888 | 0.10557654 | 5.2744565 | 0.00325979 | 0.28546574 | 0.82825201 | 0.28546574 | 0.82825201 |
| CP(t-2) | -0.4001217 | 0.10433871 | ·3.8348346 | 0.01218756 | -0.6683329 | -0.1319105 | -0.6683329 | -0.1319105 |
| DAM | 0.08774139 | 0.02504643 | 3.5031501 | 0.0172263 | 0.0233575 | 0.15212529 | 0.0233575 | 0.15212529 |
| DNt l) | -0.1137401 | 0.02834449 | -4.0127768 | 0.01019344 | -0.1866019 | -0.0408783 | -0.1866019 | -0.0408783 |
| 3711.13652 | 3057.14301 | 1.2139231 | 0.27898233 | -4147.4998 | 11569.7728 | -4147.4998 | 11569.7728 |
Table 2: Model Output to Determine Whether Time is a Significant Factor
3. Examination of Seasonal Factors
Next, we explored the presence of seasonal factors in the final model using monthly indices. Initially, we included all monthly indices, but the results showed insignificance. Subsequently, we reduced the number of monthly indices, but the outcome remained the same. In both cases, no monthly index variables were significant in predicting sales.
Model 1 :
OV :Sales
IV = CP(t).CP(t-2), OA(t), OA(t l), M l,M2,M3,M4, MS, M6, M7, M8, M9, MlO, M U
Result:There are no seasonealfactor, adding seasonality to the finalmodel did not yield to significant p-·value
SAMMARY OUTPUT
| Regression Statistics | |
|---|---|
| MultipleR | 1 |
| RSquare | 1 |
| Adjusted R Sq1 | 65535 |
| Standard Etr'Or | 0 |
| Observations | 11 |
ANOVA
| Regression Statistics | |
|---|---|
| MultipleR | 1 |
| RSquare | 1 |
| Adjusted R Sq1 | 65535 |
| Standard Etr'Or | 0 |
| Observations | 11 |
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
|---|---|---|---|---|---|---|---|---|
| Intercept | 346224.935 | 0 | 65535 | #NUM! | 346224.935 | 346224.935 | 346224.935 | 346224.935 |
| CP(t) | 0.51984802> | 0 | 65535 | #NUM! | 0.51984802 | 0.51984802 | 0.51984802 | 0.51984802 |
| CP(t-2) | -0.296151 | 0 | 65535 | #NUM! | -0.296151 | -0.296151 | -0.296151 | -0.296151 |
| DA(t) | 0.09548863 | 0 | 65535 | #NUM! | 0.09548863 | 0.09548863 | 0.09548863 | 0.09548863 |
| DA(t-1) | -0.093865 | 0 | 65535 | #NUM! | -0.093865 | -0.093865 | -0.093865 | -0.093865 |
| M1 | -3978.2617 | 0 | 65535 | #NUM | -3978.2617 | -3978.2617 | -3978.2617 | -3978.2617 |
| M2 | -20063.295 | 0 | 65535 | #NUM! | -20063.295 | -20063.295 | -20063.295 | -20063.295 |
| M3 | -27484.64 | 0 | 65535 | #NUM! | -27484.64 | -27484.64 | -27484.64 | -27484.64 |
| M4 | 24283.1716 | 0 | 65535 | #NUM! | 24283.1716 | 24283.1716 | 24283.1716 | 24283.1716 |
| M5 | -19061.43 | 0 | 65535 | #NUM! | -19061.43 | -19061.43 | -19061.43 | -19061.43 |
| M6 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 |
| M7 | -8241.3654 | 0 | 65535 | #NUM! | -8241.3654 | -8241.3654 | -8241.3654 | -8241.3654 |
| M8 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 |
| M9 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 |
| M10 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 |
| M11 | 0 | 0 | 65535 | #NUM! | 0 | 0 | 0 | 0 |
Table 3: Examination of Seasonal Factors
4. Sales Forecast for 2023
To forecast sales for 2023, we utilized the regression formula generated from our analysis. The forecasted sales for each month in 2023 based on the expected Consumer Packs and Dealer Allowances are as follows:
- January: 506,676
- February: 64,680
- March: 96,396
- April: 113,134
- May: 12,515
- June: 18,696
- July: 108,904
- August: 28,786
- September: 69,996
- October: 54,145
- November: 124,096
- December: 74,197
Regression Formula and Approach
The regression formula used to forecast sales was developed based on the analysis of historical data. It's important to note that the data recorded since 2018 initially contained NULL values for Sales. To improve predictor accuracy, we focused on modeling data from 2019 onwards. The model, along with the selected independent variables, demonstrated a significant effect. This allowed us to confidently predict future sales based on our regression model formula.
Regression Model Formula
Sales= 279676 +(Consumer Packs •0.56575474)+ (Dealer Allowances •0.091864016)