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:

BUS520 SLP1V1.docx

Assignment

Review the information and data regarding the different

alternatives for a new restaurant location.

Then do the following in Excel:

Table 1: Develop an MADM table with the raw data.

Table 2: Convert the raw data to utilities (scaled on 0 to

1). Show the utility weights in a second table.

Table 3: Develop a third table with even weights (16.7%) for

each variable.

Evaluate Table 3 for the best alternative.

Table 4: Complete a sensitivity analysis by assigning

weights to each variable.

In a Word document, do the following:

Discuss the process used to put together Tables 1–4 above.

Provide the rationale you used for choosing for each of the

weights you used in Table 4.

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.

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 one of 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.

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

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.

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.

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.

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 equation
in predicting

future sales?) What other recommendations do you have for the
client?

Module 3 – SLP

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.75 million

High NPV: $5 million, Pr = 0.5

Medium NPV: $2 million, Pr = 0.3

Low NPV: $0, Pr = 0.2

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.55 million

High NPV: $3 million, Pr = 0.75

Medium NPV: $2 million, Pr = 0.15

Low NPV: $1 million, Pr = 0.1

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.75 million

NPV: $1.5 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 in 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).

Module 4 – Case

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:

Calculate the MAPE for Year 2 Linear Regression forecast

(use the first spreadsheet tab labeled “Year 2 Forecast –
MAPE”).

Calculate forecasted sales for Year 2 using SES (use the
second

spreadsheet tab labeled “SES – MAPE”). Use 0.15 and 0.90
alphas.

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 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.

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.

Module 4 – SLP

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 among 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: $7.5 million, Pr = 0.5

Unfavorable NPV: $2.0 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: $4.5 million, Pr = 0.75

Unfavorable NPV: $2.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: $2.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 State of the Market

Expert Prediction

Favorable

Unfavorable

Predicts “Favorable”

.9

.3

Predicts “Unfavorable”

.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 Estate

0.50

0.50

B: Just Hats

0.75

0.25

Joint Probabilities

A Fav, B Fav (A+, B+)

0.375

A Unf, B Unf (A-, B-)

0.125

A Fav, B Unf (A+, B-)

0.125

A Unf, B Fav (A-, 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 Estate

Just Hats

F

U

F

U

0.45

says “F/F”

0.75

0.25

0.90

0.10

0.15

says “F/U”

0.75

0.25

0.30

0.70

0.30

says “U/F”

0.125

0.875

0.90

0.10

0.10

says “U/U”

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 SLP 4 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 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.

Written analysis that supports Excel analysis and provides

thorough discussion of assumptions, rationale, and logic
used.

Complete, meaningful, and accurate recommendation(s).

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:

BUS520 SLP1V1.docx

Assignment

Review the information and data regarding the different

alternatives for a new restaurant location.

Then do the following in Excel:

Table 1: Develop an MADM table with the raw data.

Table 2: Convert the raw data to utilities (scaled on 0 to

1). Show the utility weights in a second table.

Table 3: Develop a third table with even weights (16.7%) for

each variable.

Evaluate Table 3 for the best alternative.

Table 4: Complete a sensitivity analysis by assigning

weights to each variable.

In a Word document, do the following:

Discuss the process used to put together Tables 1–4 above.

Provide the rationale you used for choosing for each of the

weights you used in Table 4.

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.

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 one of 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.

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

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.

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.

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.

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 equation
in predicting

future sales?) What other recommendations do you have for the
client?

Module 3 – SLP

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.75 million

High NPV: $5 million, Pr = 0.5

Medium NPV: $2 million, Pr = 0.3

Low NPV: $0, Pr = 0.2

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.55 million

High NPV: $3 million, Pr = 0.75

Medium NPV: $2 million, Pr = 0.15

Low NPV: $1 million, Pr = 0.1

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.75 million

NPV: $1.5 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 in 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).

Module 4 – Case

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:

Calculate the MAPE for Year 2 Linear Regression forecast

(use the first spreadsheet tab labeled “Year 2 Forecast –
MAPE”).

Calculate forecasted sales for Year 2 using SES (use the
second

spreadsheet tab labeled “SES – MAPE”). Use 0.15 and 0.90
alphas.

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 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.

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.

Module 4 – SLP

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 among 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: $7.5 million, Pr = 0.5

Unfavorable NPV: $2.0 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: $4.5 million, Pr = 0.75

Unfavorable NPV: $2.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: $2.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 State of the Market

Expert Prediction

Favorable

Unfavorable

Predicts “Favorable”

.9

.3

Predicts “Unfavorable”

.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 Estate

0.50

0.50

B: Just Hats

0.75

0.25

Joint Probabilities

A Fav, B Fav (A+, B+)

0.375

A Unf, B Unf (A-, B-)

0.125

A Fav, B Unf (A+, B-)

0.125

A Unf, B Fav (A-, 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 Estate

Just Hats

F

U

F

U

0.45

says “F/F”

0.75

0.25

0.90

0.10

0.15

says “F/U”

0.75

0.25

0.30

0.70

0.30

says “U/F”

0.125

0.875

0.90

0.10

0.10

says “U/U”

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 SLP 4 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 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.

Written analysis that supports Excel analysis and provides

thorough discussion of assumptions, rationale, and logic
used.

Complete, meaningful, and accurate recommendation(s).

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:

BUS520 SLP1V1.docx

Assignment

Review the information and data regarding the different

alternatives for a new restaurant location.

Then do the following in Excel:

Table 1: Develop an MADM table with the raw data.

Table 2: Convert the raw data to utilities (scaled on 0 to

1). Show the utility weights in a second table.

Table 3: Develop a third table with even weights (16.7%) for

each variable.

Evaluate Table 3 for the best alternative.

Table 4: Complete a sensitivity analysis by assigning

weights to each variable.

In a Word document, do the following:

Discuss the process used to put together Tables 1–4 above.

Provide the rationale you used for choosing for each of the

weights you used in Table 4.

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.

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 one of 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.

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

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.

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.

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.

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 equation
in predicting

future sales?) What other recommendations do you have for the
client?

Module 3 – SLP

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.75 million

High NPV: $5 million, Pr = 0.5

Medium NPV: $2 million, Pr = 0.3

Low NPV: $0, Pr = 0.2

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.55 million

High NPV: $3 million, Pr = 0.75

Medium NPV: $2 million, Pr = 0.15

Low NPV: $1 million, Pr = 0.1

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.75 million

NPV: $1.5 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 in 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).

Module 4 – Case

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:

Calculate the MAPE for Year 2 Linear Regression forecast

(use the first spreadsheet tab labeled “Year 2 Forecast –
MAPE”).

Calculate forecasted sales for Year 2 using SES (use the
second

spreadsheet tab labeled “SES – MAPE”). Use 0.15 and 0.90
alphas.

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 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.

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.

Module 4 – SLP

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 among 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: $7.5 million, Pr = 0.5

Unfavorable NPV: $2.0 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: $4.5 million, Pr = 0.75

Unfavorable NPV: $2.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: $2.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 State of the Market

Expert Prediction

Favorable

Unfavorable

Predicts “Favorable”

.9

.3

Predicts “Unfavorable”

.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 Estate

0.50

0.50

B: Just Hats

0.75

0.25

Joint Probabilities

A Fav, B Fav (A+, B+)

0.375

A Unf, B Unf (A-, B-)

0.125

A Fav, B Unf (A+, B-)

0.125

A Unf, B Fav (A-, 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 Estate

Just Hats

F

U

F

U

0.45

says “F/F”

0.75

0.25

0.90

0.10

0.15

says “F/U”

0.75

0.25

0.30

0.70

0.30

says “U/F”

0.125

0.875

0.90

0.10

0.10

says “U/U”

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 SLP 4 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 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.

Written analysis that supports Excel analysis and provides

thorough discussion of assumptions, rationale, and logic
used.

Complete, meaningful, and accurate recommendation(s).

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:

BUS520 SLP1V1.docx

Assignment

Review the information and data regarding the different

alternatives for a new restaurant location.

Then do the following in Excel:

Table 1: Develop an MADM table with the raw data.

Table 2: Convert the raw data to utilities (scaled on 0 to

1). Show the utility weights in a second table.

Table 3: Develop a third table with even weights (16.7%) for

each variable.

Evaluate Table 3 for the best alternative.

Table 4: Complete a sensitivity analysis by assigning

weights to each variable.

In a Word document, do the following:

Discuss the process used to put together Tables 1–4 above.

Provide the rationale you used for choosing for each of the

weights you used in Table 4.

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.

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.

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.

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 one of 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.

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

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.

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.

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.

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 equation
in predicting

future sales?) What other recommendations do you have for the
client?

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.75 million

High NPV: $5 million, Pr = 0.5

Medium NPV: $2 million, Pr = 0.3

Low NPV: $0, Pr = 0.2

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.55 million

High NPV: $3 million, Pr = 0.75

Medium NPV: $2 million, Pr = 0.15

Low NPV: $1 million, Pr = 0.1

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.75 million

NPV: $1.5 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 in 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:

Calculate the MAPE for Year 2 Linear Regression forecast

(use the first spreadsheet tab labeled “Year 2 Forecast –
MAPE”).

Calculate forecasted sales for Year 2 using SES (use the
second

spreadsheet tab labeled “SES – MAPE”). Use 0.15 and 0.90
alphas.

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 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.

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 among 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: $7.5 million, Pr = 0.5

Unfavorable NPV: $2.0 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: $4.5 million, Pr = 0.75

Unfavorable NPV: $2.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: $2.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 State of the Market

Expert Prediction

Favorable

Unfavorable

Predicts “Favorable”

.9

.3

Predicts “Unfavorable”

.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.

Favorable

Unfavorable

A: Real Estate

0.50

0.50

B: Just Hats

0.75

0.25

Joint Probabilities

A Fav, B Fav (A+, B+)

0.375

A Unf, B Unf (A-, B-)

0.125

A Fav, B Unf (A+, B-)

0.125

A Unf, B Fav (A-, 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 Estate

Just Hats

U

F

U

0.45

says “F/F”

0.75

0.25

0.90

0.10

0.15

says “F/U”

0.75

0.25

0.30

0.70

0.30

says “U/F”

0.125

0.875

0.90

0.10

0.10

says “U/U”

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.

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 SLP 4 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 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.

Written analysis that supports Excel analysis and provides

thorough discussion of assumptions, rationale, and logic
used.

Complete, meaningful, and accurate recommendation(s).