Description
MBA 504 Module Five Excel Workbook Guidelines and Rubric
Overview
While bivariate regression is used to predict the impact of one independent variable on one dependent variable, multivariate regression is used to predict the impact of two or more independent variables on one dependent variable.
In this scenario, you will continue to work as a business consultant trainee with the TC Ice Cream client. TC Ice Cream would like to know which variables have an impact on the Quantity Sold variable. The TC Ice Cream management team would like you to create multivariate regressions to analyze the data. Remember that TC Ice Cream is interested in whether specific trends are identified that can help grow its business through improved operations and sales. You have decided that the best analysis will be to perform multivariate regressions.
For each of the multivariate regressions, your dependent variable will be Quantity Sold and you will be selecting a quantitative independent variable. Then you will create a PowerPoint presentation for the TC Ice Cream management team in which you describe the regression models, the variables, and the testing conducted.
Reminder: The division “WEST NORTH CENTRAL” cannot be used for your assignment. Use the division and two flavors you selected in Module Three.
Prompt
Your task is to perform multivariate regressions using Excel. You will also create a PowerPoint presentation that describes the regression model you used and analyze specific criteria provided by TC Ice Cream.
For each of the multivariate regressions, your dependent variable will be Quantity Sold. For the independent variable, you will be given the variables to use per the below instructions. Then you will create a presentation for the TC Ice Cream management team in which you describe the regression models, the variables, and the testing you chose to analyze.
Within the TC Ice Cream Excel Workbook, use the “Flavor One Multi. Regression” and “Flavor Two Multi. Regression” sheets to create and place the multivariate regression models. For this assignment you will be using the Excel Data Analysis “add-in” and specifically the Regression Analysis tool. The regression analysis output will provide critical information such as the R2 value, regression coefficients, linear regression equation, and p values.
Specifically, you must address the following rubric criteria:
Multivariate Regression for Flavor One:
Use the sheet “Flavor One Multi. Regression” to create and place your multivariate regression models.
Create a Multivariate regression model: the dependent variable is Quantity Sold and the independent variables are Advertising Expenses and Flavor Ratings.
Multivariate Regression for Flavor Two:
Use the sheet “Flavor Two Multi. Regression” to create and place your multivariate regression models.
Create a Multivariate regression model: the dependent variable is Quantity Sold and the independent variables are Advertising Expenses and Flavor Ratings.
Then, use the Module Five PowerPoint Template to create a presentation explaining the results of the multivariate regressions. This should include key visualizations and analysis to support your answers. The template includes specific questions to answer. Use the multivariate regression models from the “Flavor One Multi. Regression” and “Flavor Two Multi. Regression” sheets to support your analysis.
Note: You will need to refer to your analysis from Milestone One to assist you in answering the questions.
What to Submit
TC Ice Cream Excel Workbook: Submit the TC Ice Cream Excel Workbook. This should include work completed in the “Flavor One Multi. Regression” and “Flavor Two Multi. Regression” sheets. Do not remove any worksheets from the workbook.
PowerPoint Presentation: Submit a PowerPoint presentation using the Module Five PowerPoint Template. If references are included, they should be cited in APA format. Consult the Shapiro Library APA Style Guide for more information on citations.
Unformatted Attachment Preview
MBA 504 Module Five
PowerPoint Template
[Name]
[Date]
[Professor]
[Section]
Overview
[Include 3 to 4 bullet points with an
overview of the analysis.]
Flavor One
[Answer the following questions referencing the
visualization(s) on the slide:
1.For Flavor One, what is the multivariate
linear equation for Quantity Sold
(dependent variable) and independent
variables Advertising Expenses and Flavor
Rating?
2.For Flavor One, what is the R2 value
and your interpretation for Quantity Sold
(dependent variable) and independent
variables Advertising Expenses and Flavor
Rating?
3.For Flavor One, what is the p value and
your interpretation for Advertising
Expenses and Flavor Rating variables?]
[Insert visualization(s) here –
multivariate regression
model.]
Flavor One
[Answer the following questions referencing the
visualization(s) on the slide:
4. In the upcoming year, if TC Ice Cream
used the average monthly advertising
expenses for Flavor One plus an
additional 10% and assumed a flavor
rating of 9, what would be the predicted
monthly quantity sold?
It is recommended you show the
calculations used.]
[Insert visualization(s) here –
multivariate regression
model.]
Flavor Two
[Answer the following questions referencing
the visualization(s) on the slide:
1.For Flavor Two, what is the multivariate
linear equation for Quantity Sold (dependent
variable) and independent variables
Advertising Expenses and Flavor Rating?
2.For Flavor Two, what is the R2 value and
your interpretation for Quantity Sold
(dependent variable) and independent
variables Advertising Expenses and Flavor
Rating?
3.For Flavor Two, what is the p value and
your interpretation for Advertising Expenses
and Flavor Rating variables?]
[Insert visualization(s) here –
multivariate regression
model.]
Flavor Two
[Answer the following questions referencing the visualization(s) on
the slide:
4. In the upcoming year, if TC Ice Cream used
the average monthly advertising expenses
for Flavor Two plus an additional 10% and
assumed a flavor rating of 9, what would be
the predicted monthly quantity sold?
It is recommended you show the
calculations used.]
[Insert visualization(s) here –
multivariate regression
model.]
Additional Analysis
Use this slide for any additional analysis you
would like to include about the two flavors.
[Insert visualization(s) here.]
References
[Include external references if applicable.]
Thank You
Variable
Region
Data Type
Text
Division
Text
Flavor
Text
Date
Date (MM/DD/YYYY)
Advertising Expenses Integer
Flavor Rating
Integer
Social Media Posts
Integer
Event Promotions
Integer
Quantity Sold
Integer
Note: =IF(F2
Purchase answer to see full
attachment