Description
Use data and Excel tool to complete two problems. Show works in excel, and complete the doc, which consists of two multi-question problems.
Unformatted Attachment Preview
Name: ________________________________________________________________
Midterm Exam: DESC656
Spring Trimester, 2023
Excel tools and data should be used to complete the exam
problems.
Please save your answers as a PDF and submit using the
Assignments link.
Problem 1.
Using the data set on the Excel regression tool provided, create a regression model
capable of forecasting 5-year average return (DV) of a mutual fund with expense
ratio, net asset value, and Morningstar rank. Morningstar rank has been recoded
into an indicator variable. (2 and 3 star ranks are coded 0, 4 and 5 star ranks are
coded 1).
a. Examine the effects of each independent variable (IV) on the dependent
variable. If you had to select only one IV, what would it be?
b. Using all 3 IVs, what is your % of explained variability?
c. What is the regression equation, using all 3 IVs, to predict 5-year average
return?
d. Using all 3 IVs: Your particular fund has a star ranking of 4-star (coded 1), a
net asset value of 68.11, and an expense ratio of .63. Estimate your 5-year
average return with 95% confidence.
e. Using all 3 IVs: What is your estimate of the mean 5-year average return for
all funds as described in part d (4-star, NAV 68.11, expense ratio .63)? Use
95% confidence.
f. Using all 3 IVs: Is this model significant overall? Be sure to support your
answer with specific results.
g. Remove any insignificant variables. What variables did you remove? What
variable(s) is/are left?
h. After removing all insignificant variables, do you suspect any problems due
to collinearity? Yes/No. Briefly explain.
Problem 2. Remember, you may use the exam Excel tools (and anything in our
Sakai course).
A company is planning a plant expansion. They can build a large or small plant. The
payoffs for the plant depend on the level of consumer demand for the company’s
products. For the large plant, the company expects $85 million in profit if demand is
high and $35 million if demand is low. For the small plant, the company expects $54
million in profit if demand is high and $19 million if demand is low.
The company believes that there is a 72% chance that demand for their products
will be high and a 28% chance that it will be low.
Construct a payoff matrix based on the given information. Remember, you may use
the exam Excel tools
Payoff
What is the decision according to the EMV criterion? What is the MAX EMV?
What is the EVPI and what does this indicate? What does it suggest about this
decision being made?
Is your decision above ‘sensitive’ to the probability of HIGH demand? In other
words, would your decision change if the probability of HIGH demand were less?
Yes/No. Briefly explain and support your answer.
The company may conduct a survey to make a better prediction of demand for their
products. The following probability information has been provided regrading
survey outcomes that might help to predict the probability of demand.
Favorable
Unfavorable
Total
High Demand
.66
.06
.72
Low Demand
.10
.18
.28
Total
.76
.24
1
Using these probabilities and the payoffs, complete the decision tree model in your
Excel file. Input the correct information into the GREEN cells. The spreadsheet
will calculate all EMVs for you once you input the needed information.
Please add a screen shot of your completed tree here:
What would be the EMV assuming that the survey is done? How does this EMV
compare to your MAX EMV on the payoff table, that is, the EMV without the survey?
Should the market research firm be hired at a cost of $100,000? Is the survey
helpful in this case? Be sure to support your answer with specific results from your
models.
payoff
Plant
Large
Small
Payoff Matrix
Demand
High
Low
Probability
Large
Small
Max
0
0
Min
0
0
Min
0
0
EOL
0
0
EVwPI
0
EVoPI
0
1
High
0
0
Regret Matrix
Low
0
0
EVPI
MAX
0.0
0.0
Page 1
EMV
High Demand
Low Demand
Total
Favorable
Unfavorable
Total
Column Conditional Distributions
Total
Row Conditional Distributions
Total
Survey
Favorable
Prob
MAX
0
Do Survey
EMV
0
Unfavorable
Prob
MAX
0
Size
Demand
Payoffs
High
Large
EMV
Prob
0
Low
Prob
High
Small
EMV
Prob
0
Low
Prob
High
Large
EMV
Prob
0
Low
Prob
High
Small
EMV
Prob
0
Low
Prob
1
NAV
2.6
19.47
16.45
12.1
12.51
11.02
13.23
10.95
10.56
23.59
40.37
46.39
24.94
28.88
32.26
54.83
32.12
45.6
35.41
42.45
17.44
15.3
31.92
37.53
16.92
35.73
31.07
49.81
50.67
22.46
13.47
36.58
15.29
24.32
68.54
31.86
13.44
32.58
23.71
48.39
2
Exp Rat
0.45
0.16
0.33
0.58
0.41
0.49
0.45
0.62
0.6
1.1
1.05
1.03
0.97
0.67
0.9
0.86
0.29
0.89
1.29
0.56
1.31
1.32
1.08
1.27
1.18
1.2
1.01
1.44
1.31
0.8
0.53
1.08
1.02
1.32
0.89
1
1.25
0.23
0.64
0.9
3
Morning *
0
0
1
0
1
1
1
0
1
0
0
0
0
0
0
0
1
0
1
1
1
0
1
1
0
1
0
1
1
1
0
1
0
1
1
1
0
0
1
1
4
5-yr
3.49
3.78
4.25
4.31
4.31
4.34
4.41
4.63
5.14
10.47
11.23
12.09
12.32
12.39
12.81
12.99
13.41
13.5
13.98
14.4
15.16
15.31
15.33
15.46
15.67
15.85
15.91
16.7
16.77
16.91
17.23
17.23
17.25
17.77
17.99
18.23
21.35
19.63
21.77
23.46
5
6
7
26.27
25.52
27.86
14.37
53.89
1.36
1.23
1.16
2.1
1.38
1
0
0
0
1
23.68
24.95
32.7
43.27
55.27
8
9
10
Input Column Number
Simple Linear Regression
I.V. (x)
D.V. (y)
Multiple Regression
X1
X2
X3
X4
X5
DV (y)
Simple Linear Regression
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
0
50
100
Regression Equation: Predicted y = slope (x) + intercep
Slope
Intercept
r2
Mean X
Std. Dev. X
Regression Standard Error
s
150
200
250
n: Predicted y = slope (x) + intercept
if x =
y-hat =
r
Mean Y
Std. Dev. Y
Number of Observations
n
0
Inference for Simple Regression
Confidence Interval for Slope
1-a
Slope
±
±
95%
t * SEb 1
=
=
Significance Test for Slope
Test Stat (t)
Null Hypothesis
Ho: b 1 = 0
Alternative Hypothesis
Ha: b 1 not = 0
p -value
Confidence Interval for Mean Response
1-a
95%
if X
y-hat
±
±
t * SE
y-hat
±
±
t * SE
SS
df
MS
F
0
0
Prediction Interval for Single Observation
1-a
95%
if X
ANOVA Test
Source
Regression
Residuals
Total
Interval
=
=
Interval
=
=
Interval
p -value
Residual Analysis and Outlier Screening (Simple)
y-hat
NQ Plot
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
0
0.2
0.4
0.6
Residuals
Residual Plot
1
0.9
0.8
0.7
Residuals
Residuals
0.6
0.5
0.4
0.3
0.2
0.1
0
X
0.8
1
Standardized
1.2
Multiple Regression Data
X Variables must be sequential and begin with X1
x1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
x2
x3
x4
x5
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
y
Inference for Multiple Regression
R2
Adjusted R 2
Standard Error
Y Intercept
Signifcance Tests of Slopes
slope (b )
SE (b )
ANOVA Test of Model Signifcance
Source
SS
df
Regression
Residuals
Total
0
0
t-stat
MS
#DIV/0!
Correlation Matrix: Collinearity Check
Forecasting with Regression Equation
95%
Prediction
Confidence
y-hat
+/- ME
Interval
tiple Regression
p-value
F
Interval
Confidence Interval 95%
p -value
Residual Analysis and Outlier Screening (Multiple)
y-hat
NQ Plot
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
0
0.2
0.4
0.6
Residuals
Residual Plot
1
0.9
0.8
Residual
Residuals
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
Predicted y
0.8
1
Standardized
1
1.2
Purchase answer to see full
attachment
