Module 1 – Case
ASSUMED CERTAINTY: PIVOT TABLES AND
MULTI-ATTRIBUTE DECISION MAKING
Pivot Tables and Pivot
Charts
Assignment
Overview
You are
the lead consultant for the Excellent Consulting Group. It is mid-October. One
of your top clients, Buddy’s Floor Barn, has just closed the books for the
first three quarters of the year (January through September). Buddy’s Floor
Barn requests that you analyze the sales performance of its 5 product lines
over this 3-quarter period. From past consulting work you have done for the
company, you know that Buddy’s Floor Barn has 4 regions and 18 total store
locations.
Each
Regional Manager at the company has compiled the data for his/her region. The
raw data provided consists of the sales revenue for each of the 5 premium
flooring lines for all 4 regions and 18 locations for the first three quarters
of the current year.
Case
Assignment
The data
have been provided in list format. Generate a Pivot Table Report with Charts.
Use the Pivot Table and Charts to analyze the data. Following your in-depth
analysis of the data, write a report to Buddy’s Floor Barn in which you discuss
and analyze the data, and make appropriate recommendations relative to how
Buddy’s Floor Barn should improve its sales performance going forward.
Assignment
Expectations
Data:To begin, download the
list data here: Data chart for BUS520 Case 1
Excel
Analysis:
·
Provide accurate and complete Excel analysis (Pivot Table with
Charts).
Written
report:
·
Length requirement: 4–5 pages minimum (not
including Cover and Reference pages). NOTE: You must have 4–5
pages of written discussion and analysis. This means you should avoid
use of tables and charts as “space fillers.”
·
Provide a brief introduction to/background of the problem.
·
Using the Pivot Table and Pivot Charts, discuss and analyze the
data, noting key highs and lows, trends, etc.
·
Include charts from your Pivot Table to support your written
analysis. (Please do not use charts as “space fillers.” Instead, use them
strategically to support your written analysis.)
·
In a “Recommendations” section, give clear, specific, and
meaningful recommendations that Buddy’s Floor Barn should use to improve
overall company sales.
·
Be sure to consider highs, lows, and trends in the data. Which cities
are the highest performers? Lowest? Which regions and quarter had the highest
sales? Lowest sales? Consider what may be driving the numbers: Poor marketing?
Outstanding marketing strategies? Inventory management? Seasonal sales? Other?
There are innumerable possibilities. Your role is to reflect on the data, and
ultimately, to use the data to give useful recommendations.
Module 1 – SLP
ASSUMED CERTAINTY: PIVOT TABLES AND
MULTI-ATTRIBUTE DECISION MAKING
Assumed Certainty:
Multi-Attribute Decision Making (MADM)
Scenario:You are the Vice
President of Franchise Services for the Happy Buns restaurant chain. You have
been assigned the task of evaluating the best location for a new Happy Buns
restaurant. The CFO has provided you with a template that includes 6 criteria
(attributes) that you are required to use in your evaluation of 5 recommended
locations. Following are the 6 criteria that you will use to evaluate this
decision:
·
Traffic counts (avg. thousands/day)—the more traffic, the more customers,
and the greater the potential sales.
·
Building lease and taxes (thousands $ per year)—the lower the
building lease and taxes, the better.
·
Size of building (square feet in thousands)—a larger building is more
preferable.
·
Parking spaces (max number of customers parking)—more customer parking is
preferable.
·
Insurance costs (thousands $ per year)—lower insurance costs are
preferable.
·
Ease of access (subjective evaluation from observation)—you will need to
“code” the subjective data. Use Excellent = 4, Good = 3, Fair = 2, and Poor =
1.
Now that
you have collected the data from various sources (your CFO and COO, local real
estate listings, personal observation, etc.), you have all the data you need to
complete an analysis for choosing the best location. Download the raw data for
the 5 locations in this Word document: Raw data for BUS520 SLP 1
Assignment
1.
Review the information and data regarding the different
alternatives for a new restaurant location.
2.
Then do the following in Excel:
a.
Table 1: Develop an MADM table with the raw data.
b.
Table 2: Convert the raw data to utilities (scaled on 0 to 1).
Show the utility weights in a second table.
c.
Table 3: Develop a third table with even weights (16.7%) for
each variable.
d.
Evaluate Table 3 for the best alternative.
e.
Table 4: Complete a sensitivity analysis by assigning weights to
each variable.
3.
In a Word document, do the following:
a.
Discuss the process used to put together Tables 1–4 above.
b.
Provide the rationale you used for choosing for each of the
weights you used in Table 4.
c.
Give your recommendation of which location the company should
choose (based on results of Table 4).
SLP
Assignment Expectations
Excel
Analysis
·
Complete Excel analysis using MADM (all four tables noted above
must be included).
·
Accurate Excel analysis (Excel file includes working
formulas showing your calculations; all calculations and results must
be accurate).
Written
Report
·
Length requirements: 2–3 pages minimum (not
including Cover and Reference pages). NOTE: You must submit
2–3 pages of written discussion and analysis.This means that
you should avoid use of tables and charts as “space fillers.”
·
Provide a brief introduction to/background of the problem.
·
Discuss the steps you used to compile the Excel analysis (i.e.,
the four tables).
·
Discuss the assumptions used to assign weights to each variable
of your sensitivity analysis (Table 4). That is, provide the rationale for your
choice of weights for each variable.
·
Provide a complete and meaningful recommendation related to the
location that should be chosen as a new site.
Upload
both your Excel file and written Word report to the SLP 1 Dropbox by the
assignment due date.
Module 2 – Case
RISK: FREQUENCY DISTRIBUTION,
PROBABILITIES, AND EXPECTED VALUE
Risk: Frequency
Distribution, Probabilities, and Expected Value
Assignment
Overview
In the
Module 2 Case, you are again engaged on a consulting basis by Buddy’s Floor
Barn. This time, in order to get a better idea of what might have motivated
customers’ buying habits you are asked to analyze the ages of the customers who
have purchased oak flooring over the past 12 months. Past research done by the
Excellent Consulting Group has shown that different age groups buy certain
products for different reasons. Buddy’s Floor Barn has sent a survey to 200
customers who have previously purchased oak premium flooring, and 124 customers
have responded. The survey includes age data of past customers who purchased
oak flooring in the past year.
Case
Assignment
Using
Excel, create a frequency distribution (histogram) of the age data that was
captured from the survey. You should consider the width of the age categories
(e.g., 5 years, 10 years, or other). That is, which age category grouping
provides the most useful information? Once you have created this histogram,
determine the mean, median, and mode.
After you
have reviewed the data, write a report to your boss that briefly describes the
results that you obtained. Make a recommendation on how this data might be used
for marketing purposes.
Data: Download the
Excel-based data file with the age data of the 124 customers: Data chart for BUS520 Case 2.
Use these data in Excel to create your histogram.
Assignment
Expectations
Excel
Analysis
·
Accurate and complete analysis in Excel using the Histogram
function.
Written
Report
·
Length requirements: 4–5 pages minimum (not
including Cover and Reference pages). NOTE: You must submit 4–5
pages of written discussion and analysis. This means
that you should avoid use of tables and charts as “space fillers.”
·
Provide a brief introduction to/background of the problem.
·
Provide a written analysis that supports your Histogram age
groups (bins).
·
Based on your analysis of the histogram data, provide complete
and meaningful recommendations as the data relates to Buddy’s Floor Barn
marketing strategy.
Privacy Policy | Contact
Module 2 – SLP
RISK: FREQUENCY DISTRIBUTION,
PROBABILITIES, AND EXPECTED VALUE
Risk: Probabilities and
Expected Value
Scenario: You work for a
private investment company that currently has numerous business investments in
real estate development, restaurant franchises, and retail chains. Following an
exhaustive search for new investment opportunities, you have found three
possible alternatives, each of which will pay off in exactly 10 years from the
date of initial investment. Because you only have enough money to invest in oneof
the three options, you recognize that you will need to complete a quantitative
comparison of the three alternatives:
Option A: Real estate
development.
Option B: Investment in the
retail franchise “Just Hats,” a boutique that sells hats for men and women.
Option C: Investment in
“Cupcakes and so forth,” a franchise that sells a wide variety of cupcakes and
a variety other desserts.
Download the raw data
for the three investments in this Excel document: Raw data for BUS520 SLP 2
Assignment
Develop an analysis of
these three investments in Excel. Use expected value to determine which of the
three alternatives you should choose.
Write a report to your
private investment company, explaining your Excel analysis, giving your
recommendation, and justifying your decision.
SLP Assignment
Expectations
Excel Analysis
·
Using Excel, make an accurate and complete analysis of the three
investment alternatives.
Written Report
·
Length requirements: 2–3 pages minimum (not
including Cover and Reference pages). NOTE: You must submit
2–3 pages of written discussion and analysis.This means that
you should avoid use of tables and charts as “space fillers.”
·
Provide a brief introduction to/background of the problem.
·
Discuss the steps you used in completion of your Excel analysis.
·
Based on your Excel analysis, give your recommendation as to
which of the three investment alternatives should be pursued.
Upload both your written
report and Excel file to the SLP 2 Dropbox.
Privacy Policy | Contact
Module 3 – Case
LINEAR REGRESSION FORECASTING AND
DECISION TREES
Linear Regression
Forecasting
Assignment
Overview
Scenario: You are a
consultant who works for the Excellent Consulting Group. Your client, the ABC
Furniture Company, believes that there may be a relationship between the number
of customers who visit the store during any given month (“customer traffic”)
and the total sales for that same month. In other words, the greater the
customer traffic, the greater the sales for that month. To test this theory,
the client has collected customer traffic data over the past 12-month period,
and monthly sales for that same 12-month period (Year 1).
Case
Assignment
1.
Using the customer traffic data and matching sales for each
month of Year 1, create a Linear Regression (LR) equation in Excel. Use the
Excel template provided (see “Module 3 Case – LR –Year 1” spreadsheet tab), and
be sure to include your LR chart (with a trend line) where noted. Also, be sure
that you include the LR formula within your chart.
2.
After you have developed the LR equation above, you will use the
LR equation to forecast sales for Year 2 (see the second Excel spreadsheet tab
labeled “Year 2 Forecast”). You will note that the customer has collected
customer traffic data for Year 2. Your role is to complete the sales forecast
using the LR equation from Step 1 above.
3.
After you have forecast Year 2 sales, your Professor will
provide you with 12 months of actual sales data for Year 2. You will compare
the sales forecast with the actual sales for Year 2, noting the monthly and
average (total) variances from forecast to actual sales.
4.
To complete the Module 3 Case, write a report for the client
that describes the process you used above, and that analyzes the results for
Year 2. (What is the difference between forecast vs. actual sales for Year 2—by
month and for the year as a whole?) Make a recommendation concerning how the LR
equation might be used by ABC Furniture Company to forecast future sales.
Data: Download the
Module 3 Case template here: Data chart for BUS520 Case 3.
Use this template to complete your Excel analysis.
Assignment
Expectations
Excel
Analysis
·
Accurate and complete Linear Regression analysis in Excel.
Written
Report
·
Length requirements: 4–5 pages minimum (not
including Cover and Reference pages). NOTE: You must submit
4–5 pages of written discussion and analysis.This means that
you should avoid use of tables and charts as “space fillers.”
·
Provide a brief introduction to/background of the problem.
·
Your written (Word) analysis should discuss the logic and
rationale used to develop the LR equation and chart.
·
Provide complete, meaningful, and accurate recommendation(s)
concerning how the ABC Furniture Company might use the LR equation to forecast
future sales. (For example, how reliable is the LR
LINEAR REGRESSION FORECASTING AND
DECISION TREES
Decision
Trees
Scenario:You are a consultant who
works for the Excellent Consulting Group. You have learned about three
different investment opportunities and need to decide which one is most
lucrative. Following are the three investment options and their probabilities:
Option A:Real Estate development.
This is a risky opportunity with the possibility of a high payoff, but also
with no payoff at all. You have reviewed all of the possible data for the
outcomes in the next 10 years and these are your estimates of the cash payoff and
probabilities:
Required
initial investment: $0.85 million
High
NPV: $6 million, Pr = 0.50
Medium
NPV: $2.5 million, Pr = 0.35
Low
NPV: $0, Pr = 0.15
Option B:Retail franchise for
Just Hats, a boutique-type store selling fashion hats for men and women. This
also is a risky opportunity but less so than Option A. It has the potential for
less risk of failure but also a lower payoff. You have reviewed all of the possible
data for the outcomes in the next 10 years and these are your estimates of the
payoffs and probabilities.
Required
initial investment: $0.50 million
High
NPV: $3.5 million, Pr = 0.70
Medium
NPV: $2.1 million, Pr = 0.20
Low
NPV: $1 million, Pr = 0.10
Option C:High Yield Municipal
Bonds. This option has low risk and is assumed to be a Certainty. So there is
only one outcome with probability of 1.0:
Required
initial investment: $0.65 million
NPV:
$1.7 million, Pr = 1.0
Assignment
Develop
an analysis of these three investments, and determine which of them you should
choose.Be sure to account
for cash paid for each of the three alternatives. If you do not
recall how to do this, review the practice exercises at the Background page. Do
your analysis in Excel using the Decision Tree add-in.
Write
a report to your private investment company and explain your analysis and your
recommendations. Provide a rationale for your decision.
Upload
both your written report and Excel file with the decision tree analysis to the
SLP 3 Dropbox.
SLP Assignment Expectations
Analysis
·
Accurate and complete Excel analysis.
Written Report
·
Length requirements:2–3 pages minimum(not including Cover and Reference
pages)
·
Provide a brief introduction to/ background of the problem.
·
Written analysis that supports Excel analysis and provides
thorough discussion of assumptions, rationale, and logic used.
·
Complete, meaningful, and accurate recommendation(s).
RISK: EXPONENTIAL SMOOTHING FORECASTING AND VALUE OF
INFORMATION
Risk: Simple Exponential
Smoothing (SES)
Assignment Overview
Scenario:You are a consultant for the Excellent Consulting Group (ECG).
You have completed the first assignment, developing and testing a forecasting
method that uses Linear Regression (LR) techniques (Module 3 Case). However,
the consulting manager at ECG wants to try a different forecasting method as
well. Now you decide to try Single Exponential Smoothing (SES) to forecast
sales.
Case Assignment
Using this Excel template:Data chart for BUS520 Case 4,
do the following:
1.
Calculate the MAPE for Year 2 Linear Regression forecast (use
the first spreadsheet tab labeled “Year 2 Forecast – MAPE”).
2.
Calculate forecasted sales for Year 2 using SES (use the second
spreadsheet tab labeled “SES – MAPE”). Use 0.15 and 0.90 alphas.
3.
Compare the MAPE calculated for the LR forecast (#1 above) with
the MAPEs calculated using SES.
Then write a report to your boss in which you discuss the
results obtained above. Using calculated MAPE values, make a recommendation
concerning which method appears to be more accurate for the Year 2 data: SES or
Linear Regression.
Assignment Expectations
Analysis
·
Accurate and complete SES analysis in Excel.
Written Report
·
Length requirements:4–5 pages minimum(not including Cover and Reference
pages).NOTE:You must submit 4–5 pages ofwritten
discussion and analysis.This
means that you should avoid use of tables and charts as “space fillers.”
·
Provide a brief introduction to/background of the problem.
·
Complete a written analysis that supports your Excel analysis,
discussing the assumptions, rationale, and logic used to complete your SES
forecast.
·
Give complete, meaningful, and accurate recommendation(s)
relating to whether LR or SES is more accurate in predicting sales.
RISK: EXPONENTIAL SMOOTHING FORECASTING
AND VALUE OF INFORMATION
Risk:
The Value of Information
Scenario:Using the same situation
from the Module 3 SLP, recall that you are deciding between three investments.
You have heard of an expert who has a highly reliable “track record” in the
correct identification of favorable vs. unfavorable market conditions. You are
now considering whether to consult this “expert.” Therefore, you need to
determine whether it would be worth paying the expert’s fee to get his
prediction. You recognize that you need to do further analysis to determine the
value of the information that the expert might provide.
In
order to simplify the analysis, you have decided to look at two possible
outcomes for each alternative (instead of three). You are interested in whether
the market will be Favorable or Unfavorable, so you have collapsed the Medium
and Low outcomes. Here are the three alternatives with their respective payoffs
and probabilities.
Option A:Real estate development.
This is a risky opportunity with the possibility of a high payoff, but also
with no payoff at all. You have reviewed all of the possible data for the
outcomes in the next 10 years and these are your estimates of the Net Present
Value (NPV) of the payoffs and probabilities:
High/Favorable
NPV: $6 million, Pr = 0.5
Unfavorable
NPV: $1.5 million, Pr = 0.5
Option B:Retail franchise for
Just Hats, a boutique-type store selling fashion hats for men and women. This
also is a risky opportunity but less so than Option A. It has the potential for
less risk of failure, but also a lower payoff. You have reviewed all of the possible
data for the outcomes in the next 10 years and these are your estimates of the
NPV of the payoffs and probabilities.
High/Favorable
NPV: $3.0 million, Pr = 0.75
Unfavorable
NPV: $1.5 million, Pr = 0.25
Option C:High Yield Municipal
Bonds. This option has low risk and is assumed to be a certainty. So there is
only one outcome with probability of 1.0:
NPV:
$1.25 million, Pr = 1.0
You
have contacted the expert and received a letter stating his track record which
you have checked out using several resources. Here is his stated track record:
|
True |
||
|
Expert |
Favorable |
Unfavorable |
|
Predicts |
.9 |
.3 |
|
Predicts |
.1 |
.7 |
You
realize that this situation is a bit complicated since it requires the expert
to analyze and predict the state of two different markets: the real estate
market and the retail hat market. You think through the issues of probabilities
and how to calculate the joint probabilities of both markets going up, both
going down, or one up and the other down. Based on your original estimates of
success, here are your calculations of the single probabilities and joint
probabilities of the markets.
|
Probabilities |
Favorable |
Unfavorable |
|
A: Real |
0.50 |
0.50 |
|
B: Just |
0.75 |
0.25 |
|
Joint |
|
|
A Fav, B |
0.375 |
|
A Unf, B |
0.125 |
|
A Fav, B |
0.125 |
|
A Unf, B |
0.375 |
Finally,
after a great deal of analysis and calculation, you have determined the
Posterior probabilities of Favorable and Unfavorable Markets for the Real
Estate business and the boutique hat business.
|
Real |
Just Hats |
||||
|
F |
U |
F |
U |
||
|
0.45 |
says |
0.75 |
0.25 |
0.90 |
0.10 |
|
0.15 |
says |
0.75 |
0.25 |
0.30 |
0.70 |
|
0.30 |
says |
0.125 |
0.875 |
0.90 |
0.10 |
|
0.10 |
says |
0.125 |
0.875 |
0.30 |
0.70 |
For
example, this table says that there is 45% chance that the expert will predict
Favorable for both markets (F/F), and when he makes this prediction, there is a
75% chance that the Real Estate market will be favorable and 25% chance that it
won’t, and also a 90% chance that the Hat market will be Favorable and 10%
chance it won’t.
You
have developed a decision tree showing the original collapsed solution and also
showing an expanded decision tree for evaluating the value of the expert’s
information. You need to enter the probabilities into this tree to see if the
expert’s information will increase the overall expected value of your decision.
Download the Excel file with the incomplete decision tree:Decision Tree for BUS520 SLP 4
Assignment
Complete
the information in the decision tree in the Excel file. Determine the Expected
NPV of the decision if you were to consult the expert. Does use of the expert
increase the value of your analysis? If so, by how much?
Write
a report to your private investment company and explain your analysis and your
recommendation. Provide clear rationale/ justification for your decision.
Upload
both your written report and Excel file with the Decision Tree analysis to the
SLP4 Dropbox.
SLP Assignment Expectations
Analysis
·
Accurate and complete analysis in Excel.
Required:
·
Length requirements:2–3 pages minimum(not including Cover and Reference
pages).NOTE:You must submit 2–3 pages ofwritten
discussion and analysis.This
means that you should avoid use of tables and charts as “space fillers.”
·
Provide a brief introduction to/ background of the problem.
·
Written analysis that supports Excel analysis and provides
thorough discussion of assumptions, rationale, and logic used.
·
Complete, meaningful, and accurate recommendation(s).
