MBA 504 Module Five: Analyzing Advertising Impact on Sales

MBA 504 Module Five PowerPoint Template Kelsey Tiberi October 22 nd , 2023 Professor Linda Beach 5-1 Excel Workbook: Multivariate Regression

Overview • Both Cookie Doug and Chocolate ice cream quantity sold can be explained by the advertising expenses and flavor rating as a set, there is a high correlation and it can account for the a high variance for the total quantity of ice cream sold • The R2 of Cookie Dough ice cream is .82 or 82%, with a P-Value of .00033 • The R2 of Chocolate ice cream is .81 or 81%, with a P-value of .00022

Flavor One 1. yˆ = 6,428.27 + 2.14x 1 - 391.1x 2 2. R2 = .83 this indicates that at 83% of the quantity of Cookie Doug Ice Cream sold can be explained by the advertising expenses and flavor rating as a set. 3. P-Value = .00033 which indicates that the overall regression model is significant since p < .001. Since the p-value for the total advertising expenses is .0001 this indicates a strong correlation between advertising expenses and quantity of Cookie Dough ice cream sold. Compared to the p-value of the flavor rating at 0.412, this indicates there is not a significant relationship between the flavor rating and quantity of Cookie Dough ice cream sold that is less likely to have a correlation with the quantity sold Regression Statistics Multiple R 0.912 R Square 0.832 Adjusted R Square 0.794 Standard Error 1358.463 Observations 12.000 ANOVA df SS MS F Significance F Regression 2 82167562.5 41083781.2 22.3 0.00033 Residual 9 16608789.8 1845421.1 Total 11 98776352.3 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 6428.268 4672.106 1.376 0.20213 -4140.771 16997.307 -4140.771 16997.307 Total Advertising Expenses 2.141 0.326 6.564 0.00010 1.403 2.879 1.403 2.879 Sum of Flavor Rating -391.100 455.533 -0.859 0.41287 -1421.586 639.387 -1421.586 639.387

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Flavor One 4. The predicted monthly quantity of Cookie Dough ice cream sold is 12,202 Forecast Values x Coefficient = Output Values Unit Sales = Sum of Output Values 1x6428.268 = 6,428.27 $4,341.13 x 2.141 = $9,293.79 9 x (391.100) = -3,519.90 6,428.27 + 9,293.79 – 3,519.90 = 12,202 unit sales Unit Sales Forecast Variable Coefficent Forecast Values Output Values Intercept 6428.268 1 6,428.27 Total Advertising Expenses 2.141 $4,341.13 $9,293.79 Sum of Flavor Rating -391.100 9 (3,519.90) Unit Sales 12,202 Average Monthly Advertising Expenses $3,946 0.10 $394.65 Average Monthly Advertising Exp + 10% $4,341.13

Flavor Two 1. yˆ = 12,110.28 + 1.73x 1 - 713.51x 2 2. R2 = .81 this indicates that at 81% of the quantity of Chocolate Ice Cream sold can be explained by the advertising expenses and flavor rating as a set. 3. P-Value = .00022 which indicates that the overall regression model is significant since p < .001. Since the p-value for the total advertising expenses is .0003 this indicates a strong correlation between advertising expenses and quantity of Chocolate ice cream sold. Compared to the p-value of the flavor rating at 0.2406 that is less likely to have a correlation with the quantity sold SUMMARY OUTPUT Regression Statistics Multiple R 0.92 R Square 0.85 Adjusted R Square 0.81 Standard Error 1089.71 Observations 12 ANOVA df SS MS F Significance F Regression 2 58,628,137.0 29,314,068.5 24.7 0.00022 Residual 9 10,687,271.6 1,187,474.6 Total 11 69,315,408.6 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 12,110.28 6,442.87 1.88 0.0929 (2,464.50) 26,685.06 (2,464.50) 26,685.06 Total Advertising Expenses 1.73 0.31 5.56 0.0003 1.02 2.43 1.02 2.43 Sum of Flavor Rating (713.51) 567.85 (1.26) 0.2406 (1,998.08) 571.05 (1,998.08) 571.05

Flavor Two 4.The predicted monthly quantity of Chocolate ice cream sold is 17,643 Forecast Values x Coefficient = Output Values Unit Sales = Sum of Output Values 1x12,110.28 = 12,110.28 $6,921.20 x 1.73 = $11,954.11 9 x (713.51) = -6,421.63 12,110.28 + 11,954 – 6,421.63= 17,643 unit sales Unit Sales Forecast Variable Coefficent Forecast Values Output Values Intercept 12,110.28 1 12,110.28 Total Advertising Expenses 1.73 $6,921.20 $11,954.11 Sum of Flavor Rating (713.51) 9 (6,421.63) Unit Sales 17,643 Average Monthly Advertising Expenses $6,292 0.10 $629.20 Average Monthly Advertising Exp + 10% $6,921.20

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Additional Analysis Cookie Dough Ice Cream: Average Monthly Advertising Expenses-$5,924 Average Flavor Rating - 9 Average Quantity Ice Cream Sold per Month -15,559 The ANOVA below also supports this data Chocolate Ice Cream: Average Monthly Advertising Expenses - $6,292 Average Flavor Rating -9 Average Quantity of Ice Cream Sold per Month -16,319 The ANOVA below also supports this data ANOVA df SS MS F Significance F Regression 2 58,628,137.0 29,314,068.5 24.7 0.00022 Residual 9 10,687,271.6 1,187,474.6 Total 11 69,315,408.6 ANOVA df SS MS F Significance F Regression 2 82,167,562.5 41,083,781.2 22.3 0.00033 Residual 9 16,608,789.8 1,845,421.1 Total 11 98,776,352.3

References [Include external references if applicable.]

Thank You

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Kelsey Tiberi_MBA 504 Module Five PowerPoint Template

Related Documents