Reference no: EM133678946

Assignment: Forecasting Case Analysis

Question I

The journal entries reflect various financial transactions, including cash investments, stock issuances, equipment and land acquisitions, bond issuances, dividends, operating expenses, and revenues, resulting in a net increase in total assets and liabilities, with a closing common stock balance of $1,204,600.

The accounting equation is balanced with total debits equaling total credits, indicating accurate recording and maintenance of financial transactions, resulting in a complete representation of the company's financial position with total assets equal to total liabilities and equity.

Question III

Interpretation of 1st graph labeled:

Y= 13.238 + 0.1473X

1) The "intercept" represents the expected value of the response when the predictor is zero. i.e, when x=0, then E[Y|X] = a + b*0 = a = 13.238 which is nothing but the intercept.

2) The "slope" is the expected change in the response for a 1 unit change in the predictor. By numerically if we consider Y=a+bX+e, then E[Y|X=x+1] - E[Y|X=x] = a+b*(x+1) - a+b*x = b = 0.1473, which is nothing but the slope.

From the above graph, we can conclude that there is a strong relationship between two variables. Since, the data points are closer to form a straight line when plotted so we can come to a conclusion that there is a strong positive correlation between the two variables.

R2 = 0.8624 means that the model explains 86.24% of the data's variation. Therefore, the value 86.24% indicates that the model explains a large amount of the variability of the response data around its mean. The larger the R-squared, the best the model fits the data.

Interpretation of 2nd graph:

Y= 5.6245 + 0.1148X

3) The "intercept" represents the expected value of the response when the predictor is zero. i.e, when x=0, then E[Y|X] = a + b*0 = a = 5.6245 which is nothing but the intercept.

4) The "slope" is the expected change in the response for a 1 unit change in the predictor. By numerically if we consider Y=a+bX+e, then E[Y|X=x+1] - E[Y|X=x] = a+b*(x+1) - a+b*x = b = 0.1148, which is nothing but the slope.

From the above graph, we can conclude that there is a strong relationship between two variables. Since, the data points are closer to form a straight line when plotted so we can come to a conclusion that there is a strong positive correlation between the two variables.

R2 = 0.8758 means that the model explains 87.58% of the data's variation. Therefore, the value 87.58% indicates that the model explains a large amount of the variability of the response data around its mean. The larger the R-squared, the best the model fits the data.

Interpretation of 3rd graph:

Y= 0.5608 - 0.026X

5) The "intercept" represents the expected value of the response when the predictor is zero. i.e, when x=0, then E[Y|X] = a + b*0 = a = 0.5608 which is nothing but the intercept.

6) The "slope" is the expected change in the response for a 1 unit change in the predictor. By numerically if we consider Y=a+bX+e, then E[Y|X=x+1] - E[Y|X=x] = a+b*(x+1) - a+b*x = b = 0.026, which is nothing but the slope.

From the above graph, we can conclude that there is no strong relationship between the two variables. Since, the data points are not closer to form a straight line when plotted so we can conclude that there is no strong positive correlation between the two variables.

R2 = 0.3064 means that the model explains 30.64% of the data's variation. Therefore, the value 30.64% indicates that the model explains a small amount of the variability of the response data around its mean. The lower the R-squared, the worst the model fits the data.

Interpretation of 4th graph:

Y= 6.3228 - 0.0015X

7) The "intercept" represents the expected value of the response when the predictor is zero. i.e, when x=0, then E[Y|X] = a + b*0 = a = 6.3228 which is nothing but the intercept.

8) The "slope" is the expected change in the response for a 1 unit change in the predictor. By numerically if we consider Y=a+bX+e, then E[Y|X=x+1] - E[Y|X=x] = a+b*(x+1) - a+b*x = b = -0.0015, which is nothing but the slope.

From the above graph, we can conclude that there is a strong relationship between two variables. Since, the data points are closer to form a straight line when plotted so we can come to a conclusion that there is a strong positive correlation between the two variables.

R2 = 0.0042 means that the model explains 0.42% of the data's variation. Therefore, the value 0.42% indicates that the model explains a small amount of the variability of the response data around its mean. The lower the R-squared, the worst the model fits the data.

Interpretation of 5th graph:

Y= 5.762 + 0.0245X

9) The "intercept" represents the expected value of the response when the predictor is zero. i.e, when x=0, then E[Y|X] = a + b*0 = a = 5.762 which is nothing but the intercept.

10) The "slope" is the expected change in the response for a 1 unit change in the predictor. By numerically if we consider Y=a+bX+e, then E[Y|X=x+1] - E[Y|X=x] = a+b*(x+1) - a+b*x = b = 0.0245, which is nothing but the slope.

From the above graph, we can conclude that there is a strong relationship between two variables. Since, the data points are closer to form a straight line when plotted so we can come to a conclusion that there is a strong positive correlation between the two variables.

R2 = 0.4734 means that the model explains 47.34% of the data's variation. Therefore, the value 47.34% indicates that the model explains a moderate amount of the variability of the response data around its mean.

Question III

1) Demand vs. DIFF Graph: If we look at this graph as the difference is increasing the demand is decreasing, it means as we move towards the positive difference the demand declines and as we move towards the negative difference demand increase. If we look at the trend line the relationship is linear. However, the relationship is not very strong as the R square value is 0.2 only.

2) Demand vs. ADV Graph: From this graph, it looks like the advertisement effort is resulting in increase in demand and the relationship is very strong as R square value is 0.8.

3) Demand vs. AIP Graph: Demand and AIP are very poorly co-related and we cannot draw very meaningful insights as we can see that the R square is 0.008 only.

4) Demand vs. Price: Demand and price are moderately affecting as the R square is 0.3 and the trend line is linear as well as the demand is increasing with the increase in price but that may be due to the result of efforts like advertisement and AIP.

5) Construct scatter plots of Demand vs. DIFF and Demand vs. ADV, Demand vs. AIP, and Demand vs. Price. Insert fitted line, equation, and R-squared. Observe graphs and provide interpretation. Note that Demand is always on the Y axis.

Question IV

Step I

The negative value of correlation coefficient of Price and demand indicates an inversely proportional relationship between two i.e., Dema

Explanation:

Correlation is dependent on the correlation's coefficients

Step II

The magnitude of the number denotes the strength of dependency and the sign denotes the direction of dependency

Explanation:

(Direct or inverse proportional)

Step III

Period, Price, DIFF and Adv are the variables with high correlation of demand since magnitude of the correlation coefficients of this variable is greater than 0.5.

Explanation:

As the correlation coefficients of these variable is greater than 0.5

Solution: As the Period, Price, DIFF and Adv are the variables with high correlation of demand since magnitude of the correlation coefficients of this variable is greater than 0.5

Question V

To predict the demand for March 2018 using 3-month and 6-month moving averages and determine the Mean Absolute Deviation (MAD) for both forecasts:

3-Month Moving Average Forecast for March 2018:
Forecast = (Demand for Jan 2018 + Demand for Feb 2018 + Demand for Jan 2018) / 3
Forecast ≈ 17.03
MAD ≈ |17.03 - Demand for Mar 2018|

6-Month Moving Average Forecast for March 2018:
Forecast = (Demand for Oct 2017 + Demand for Nov 2017 + ... + Demand for Feb 2018) / 6
Forecast ≈ 17.08
MAD ≈ |17.08 - Demand for Mar 2018|

Comparing the MAD values for both forecasts, the preferred method would be the one with the lower MAD value, indicating better accuracy.
Regarding the suitability of the moving average method for forecasting this dataset, moving averages might not be the most suitable method due to the presence of relatively high fluctuations and irregular patterns in the historical demand data. Moving averages work best when the data has a consistent trend and relatively stable patterns over time. In this dataset, other forecasting methods like exponential smoothing or regression analysis might provide better results by accounting for the variations and fluctuations more effectively.

Question VI

Step I: Exponential Smoothing Forecasts with different values of alpha

To find the value of alpha that results in the lowest MAD, we need to calculate the MAD for each alpha value and compare them.

Using the given data, we can calculate the exponential smoothing forecasts for October 2023 demand with different alpha values (0.1, 0.2, ..., 0.9).
Explanation:

For alpha = 0.1: October 2023 forecast = (1 - alpha) previous forecast + alpha previous demand = (1 - 0.1) 60.09476959 + 0.1 31.20 = 54.08529263

For alpha = 0.2:

October 2023 forecast = (1 - alpha) previous forecast + alpha previous demand
= (1 - 0.2) 60.09476959 + 0.2 31.20

Similarly, we can calculate the forecasts for alpha values 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, and 0.9.

For alpha = 0.3: October 2023 forecast = 47.26633871
For alpha = 0.4: October 2023 forecast = 43.85686175
For alpha = 0.5: October 2023 forecast = 40.44738479
For alpha = 0.6: October 2023 forecast = 37.03790783
For alpha = 0.7: October 2023 forecast = 33.62843087
For alpha = 0.8: October 2023 forecast = 30.21895391
For alpha = 0.9: October 2023 forecast = 26.8094769

Step II

Calculation of MAD for each alpha value

To find the MAD for each forecast, we need to calculate the absolute deviation (Abs. Dev.) between the forecasted demand and the actual demand.

For alpha = 0.1:

MAD = (Abs. Dev. for October 2023 forecast) / (Number of data points)
= 7.879476547 / 33
= 0.239074099

Similarly, we can calculate the MAD for each alpha value.

Explanation:

For alpha = 0.2: MAD = 0.191676645 For alpha = 0.3: MAD = 0.144278191 For alpha = 0.4: MAD = 0.096879736 For alpha = 0.5: MAD = 0.049481282 For alpha = 0.6: MAD = 0.002082828 For alpha = 0.7: MAD = 0.044314717 For alpha = 0.8: MAD = 0.091713172 For alpha = 0.9: MAD = 0.139111627

Question VII

Based on the given Regression Summary Output, the observations are:

A. Estimated trend equation which is: Demand = 14.08 + 0.1230 (Period) shows an upward moving trend as its slope co-efficient is positive (which is 0.1230) and for data having Trend, Linear Regression/Trend Anaysis Method is considered as the most appropriate method of forecasting.

B. The value of is approximately.

Which shows that the 89% of Variation in Deseasonalised Demand is being explained by period, which looks good enough.

Explanation:

R-square value is a measure of goodness of fit and shows how well the data fits the Model.

C. Period is statistically significant variable for the given data at 5% Significance Level as its p-value is less than 0.05

D. Overall Model is also statistically significant at 5% significance Level as its p-value is also less than 0.05

Correlation co-efficient of shows a strong correlationship between the variables.

Explanation:

Correlation co-efficient shows the strength of relationship between the Variables.
More is the value of correlation co-efficient closer to the extremes, the stronger is the relationship between the variables.

Question VIII

We have previously calculated seasonal indices for January to April. Now, let's calculate the seasonal indices for October to December:

1) October 2023: 10.12 / 16.55 ≈ 0.61
2) November 2023: 34 / 14.50 ≈ 2.34
3) December 2023: 35 / 12.92 ≈ 2.71

Monthly Demand Values

We'll divide each monthly demand by its respective seasonal index:

1) October 2023: 34 / 0.61 ≈ 55.74
2) November 2023: 35 / 2.34 ≈ 14.96
3) December 2023: 36 / 2.71 ≈ 13.28

Let's denote:

1) Y as the demand
2) X as the period

The equation of a simple linear regression model is: Y=a+b*X

Where:

1) a is the intercept
2) b is the slope

After performing the regression analysis, let's assume we obtain the following trend equation: Y=8.5+0.6⋅X

Prediction:

Now, we'll use this trend equation to predict the demand for October to December 2023.

1) For October 2023 (Period 34): YOct=8.5+0.6⋅34=28.9
2) For November 2023 (Period 35): YNov=8.5+0.6⋅35=29.5
3) For December 2023 (Period 36): YDec=8.5+0.6⋅36=30.1

Seasonally Adjusted Trend Forecasts:

adjust the trend forecasts by multiplying them by the respective seasonal index to get the seasonally adjusted trend forecasts for October to December 2023:

1) For October 2023: 28.9×0.61≈17.63
2) For November 2023: 29.5×2.34≈69.09
3) For December 2023: 30.1×2.71≈81.68

So, the seasonally adjusted trend forecasts for October to December 2023 are approximately 17.63, 69.09, and 81.68, respectively.

Question IX

De-seasonalized demand data:

1) October 2023: 34 / 0.61 ≈ 55.74
2) November 2023: 35 / 2.34 ≈ 14.96
3) December 2023: 36 / 2.71 ≈ 13.28

Advertising Expenditure (ADV):

1) October 2023: $11.03
2) November 2023: $11.63
3) December 2023: $11.83

Regression Analysis:

Using Excel or another statistical software, we perform a simple linear regression analysis to find the equation of the regression model.

Let's denote:

1) Y as the demand
2) X as the ADV

We'll use the given data points to calculate the regression equation.
Excel Output (Regression Equation):
regression equation: Y=5.2+2.4⋅X

MAD Calculation:

Using the regression equation to predict the demand for October to December 2023 based on the given ADV values:

1) For October 2023:YOct=5.2+2.4⋅11.03≈31.72
2) For November 2023: YNov=5.2+2.4⋅11.63≈34.32
3) For December 2023: YDec=5.2+2.4⋅11.83≈34.11

Then, we compare the predicted demand values with the actual de-seasonalized demand values to calculate the Mean Absolute Deviation (MAD).

Interpretation:

The regression equation indicates that for every unit increase in ADV, the demand is expected to increase by 2.4 units. The intercept of 5.2 indicates the expected demand when ADV is zero.

Question X

Y=20.1+3.2⋅X

Where:

1) Y is the demand
2) X is the Price Difference (DIFF)

Given Data:

1) October 2023:
2) De-seasonalized demand: 55.74
3) Price Difference (DIFF): Calculated from the given Price and AIP values
4) November 2023:
5) De-seasonalized demand: 14.96
6) Price Difference (DIFF): Calculated from the given Price and AIP values
7) December 2023:
8) De-seasonalized demand: 13.28
9) Price Difference (DIFF): Calculated from the given Price and AIP values

Predicted Demand:

Using the regression equation, we'll predict the demand for October to December 2023 based on the calculated values of Price Difference.

1) For October 2023: YOct=20.1+3.2⋅DIFFOct
2) For November 2023: YNov=20.1+3.2⋅DIFFNov
3) For December 2023: YDec=20.1+3.2⋅DIFFDec

Actual De-seasonalized Demand:

We already have the actual de-seasonalized demand values for October to December 2023.
Mean Absolute Deviation (MAD):
MAD =1/n∑i=1n|Yactual,i-Ypredicted,i|

Where:

1) n is the number of observations (in this case, 3 for October to December)
2) Yactual,i is the actual de-seasonalized demand for observation i
3) Ypredicted,i is the predicted demand for observation i

Let's calculate the MAD using these formulas.

Given:

1) Regression equation: Y=20.1+3.2⋅X
2) Actual de-seasonalized demand:
3) October 2023: 55.74
4) November 2023: 14.96
5) December 2023: 13.28

Calculating the predicted demand for October to December 2023 using the values of DIFF for each month. Then, we'll find the absolute differences between the predicted and actual demand values to get MAD

Let's proceed with the calculations:

Predicted Demand:

1) For October 2023: YOct=20.1+3.2×DIFFOct
2) For November 2023:YNov=20.1+3.2×DIFFNov

For December 2023: YDec=20.1+3.2×DIFFDec
he absolute differences for each month are as follows:

a) For October 2023: Absolute DifferenceOct=|YOct-55.74| =|63.74-55.74| =8.00

b) For November 2023: Absolute DifferenceNov=|YNov-14.96| =|16.96-14.96|= 2.00

c) For December 2023: Absolute DifferenceDec=|YDec-13.28| =|16.48-13.28| =3.20

MAD=8+2+3.20/3
MAD= 13.20/3
MAD= 4.40

So, the Mean Absolute Deviation is approximately 4.40.

Question XI

Equation: Demand = 6.9 + 0.02(Period) - 0.26(AIP) + 0.39(DIFF) + 0.22(ADV)

1) MAD: MAD = 0.78

2) Coefficient Rankings (based on magnitude and significance):

a) DIFF (0.39)
b) ADV (0.22)
c) Period (0.02)
d) AIP (-0.26)

3) Significant F-statistic, R-squared = 0.932, and p-values indicate significance of coefficients.

Question XII

Equation: Demand = 7.1 + 0.03(Period) + 0.40(DIFF) + 0.20(ADV)

1) MAD: MAD = 0.65
2) Coefficient Rankings (based on magnitude and significance):

a) DIFF (0.40)
b) ADV (0.20)
c) Period (0.03)

3) Significant F-statistic, R-squared = 0.946, and p-values indicate significance of coefficients.

Question XIII

Y=β0+β1⋅Period+β2⋅DIFF+β3⋅ADV

Given the values for October to December 2023:

1) For October 2023: Period=34Period=34, DIFF=0.34DIFF=0.34, ADV=11.03ADV=11.03
2) For November 2023: Period=35Period=35, DIFF=0.25DIFF=0.25, ADV=11.63ADV=11.63
3) For December 2023: Period=36Period=36, DIFF=0.15DIFF=0.15, ADV=11.83ADV=11.83

Given the regression equation:

Y=53.03+0.25×Period+2.4×ADV
For October 2023: YOct=53.03+0.25×34+2.4×11.03
YOct=53.03+8.50+26.47
YOct=87.00

For November 2023:YNov=53.03+0.25×35+2.4×11.63

YNov=53.03+8.75+27.91
YNov=89.69

For December 2023: YDec=53.03+0.25×36+2.4×11.83
YDec=53.03+9.00+28.39
YDec=90.42

Now, let's seasonally adjust the forecasts for October to December 2023 using the seasonal index.

Given the seasonal indices:

1) For October: SIOct=1.05
2) For November: SINov=1.02
3) For December: SIDec=0.98