Analysis of Promotional Variables and Sales Forecasting: A Comprehensive Report

August 18, 2023
Samantha Barker
Samantha Barker
🇬🇧 United Kingdom
Data Analysis
Samantha Barker, a data analysis expert with 10+ years experience, holds a master's from Anderson University. She specializes in guiding students to complete their statistical assignments effectively.
Key Topics
  • Problem Description for the Assignment Solution:
  • Assignment Solution:
    • 1. Identification of Significant Variables
    • 2. Analysis of Time Trend
    • 3. Examination of Seasonal Factors
    • 4. Sales Forecast for 2023
  • Regression Formula and Approach

In this comprehensive report, we delve into the intricate world of promotional variables and their influence on sales forecasting. We begin by scrutinizing the significance of various promotional-related independent variables, such as Consumer Packs and Dealer Allowances, utilizing a rigorous backward stepwise regression approach. The findings reveal which variables have a substantial impact on sales, enabling effective decision-making for marketing strategies. Moreover, we investigate the presence of trends and seasonal factors, providing valuable insights into the data's behavior over time. To top it off, we present a meticulous sales forecast for the year 2023 based on our regression model.

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 R0.9752234
R Square0.9510607
Adjusted R Squ'0.9184345
Standard Error17169.714
Observations11

ANOVA

dfSSMSFSignificance F
Regression43.4374E+10859345432929.15020750.00045164
Residual61768794478294799080
Total103 6143F+10
Coefficientsstandard Errort StatP - valueLower 95 %Upper 95 %Lower 95.0 %Upper 95.0 %
Intercept33482419673.6617.018892.6323E - 06286684.3382963.7286684.3382963.7
CP ( t )0.5505730.1095325.0265830.0023880.2825570.8185890.2825570.818589
CP ( t - 2 )-0.324220.086762-3.736860.0096580.536519-0.11192-0.53652-0.11192
DA ( t )0.1060770.0207535.1113150.0021970.0552950.1568580.0552950.156858
DA ( t - 1 )-0.089730.021088-4.254920.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 R0.9809184
R Square0.9622009
Adjusted RSqu•0.9244018
Standard Error16S29.718
Observations11

ANOVA

dfSSMSFSignificance F
Regression53.4776E+10695529078125.45566240.00144838
Residual51366157886273231577
Total103.6143E+10
Coefficients Standard Error t Stat P - value Lower 95 % Upper 95 % Lower 95.0% Upper 95.0%
Intercept334839.18318940.338917.67862681.0624E-05286151.492383526.874286151.492383526.874
CP(t)0.556858880.105576545.27445650.003259790.285465740.828252010.285465740.82825201
CP(t-2)-0.40012170.10433871·3.83483460.01218756-0.6683329-0.1319105-0.6683329-0.1319105
DAM0.087741390.025046433.50315010.01722630.02335750.152125290.02335750.15212529
DNt l)-0.11374010.02834449-4.01277680.01019344-0.1866019-0.0408783-0.1866019-0.0408783
3711.136523057.143011.21392310.27898233-4147.499811569.7728-4147.499811569.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
MultipleR1
RSquare1
Adjusted R Sq165535
Standard Error0
Observations11

ANOVA

Regression Statistics
MultipleR1
RSquare1
Adjusted R Sq165535
Standard Error0
Observations11
CoefficientsStandard Errort StatP-valueLower 95%Upper 95%Lower 95.0%Upper 95.0%
Intercept346224.935065535#NUM!346224.935346224.935346224.935346224.935
CP(t)0.51984802065535#NUM!0.519848020.519848020.519848020.51984802
CP(t-2)-0.296151065535#NUM!-0.296151-0.296151-0.296151-0.296151
DA(t)0.09548863065535#NUM!0.095488630.095488630.095488630.09548863
DA(t-1)-0.093865065535#NUM!-0.093865-0.093865-0.093865-0.093865
M1-3978.2617065535#NUM-3978.2617-3978.2617-3978.2617-3978.2617
M2-20063.295065535#NUM!-20063.295-20063.295-20063.295-20063.295
M3-27484.64065535#NUM!-27484.64-27484.64-27484.64-27484.64
M424283.1716065535#NUM!24283.171624283.171624283.171624283.1716
M5-19061.43065535#NUM!-19061.43-19061.43-19061.43-19061.43
M60065535#NUM!0000
M7-8241.3654065535#NUM!-8241.3654-8241.3654-8241.3654-8241.3654
M80065535#NUM!0000
M90065535#NUM!0000
M100065535#NUM!0000
M110065535#NUM!0000

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)

You Might Also Like

Explore our diverse sample collection for insightful assignments covering fundamental concepts to advanced methodologies. Dive into a wealth of topics and solutions tailored to your needs. Unlock valuable knowledge and inspiration through our curated samples.