1) Mrs. Marilyn Finch is planning on investing in stocks. She is considering three different types stocks, ChevRom, ExRom, and TexRom to choose from under two possible states, good and bad. As a result of favorable stock market conditions, there is a 0.7 probability of good and 0.3 probability of bad state. The table below indicates the profits and losses of investing in the following three stocks under the given states-good and bad.
STOCKS GOOD (0.7) BAD (0.3)
ChevRom $70,000 $10,000
ExRom $95,000 -$35,000
TexRom $40,000 $30,000
Set up a spreadsheet to find the best decision using:
a) Maximax.
b) Maximin.
c) Equal likely.
d) Expected value.
2) LongTailPublication.com, a small online publisher, is considering buying a firewall for its website. The cost of the firewall is $20,000. There is an 80% chance that the site will not be hacker attacked during the life of the firewall, a 12% chance of a minor attack occurring resulting in $24,000 in damage, and an 8% chance of a major attack occurring resulting in $140,000 in damage. Use Table below for analysis.
a) Compute the expected damage due to hacker attack.
b) Compare the expected damage and cost of firewall ($20,000) and recommend if to purchase firewall.
c) Would you recommend purchasing the firewall at cost of $10,000?
d) Discuss the risks involved with basing the decision strictly on expected cost.
Severity of HackDamage (X)Probability P(X)
No Hack00.80
Minor Hack$24,0000.12
Major Hack$140,0000.08
3) Sanford and sons are planning to invest in advertising to sell three of their products. There are three different outcomes: A sells the most, B sells the most, or C sells the most. Two possible states exist, good and bad. The table below indicates the profits and losses of investing in the advertising of the three products under given states-good and bad. Construct an Excel spreadsheet to answer the following:
PRODUCT GOOD (0.6) BAD (0.4)
A $5.5 million $0.5 million
B $3.5 million $2.5 million
C $6.5 million -$3.5 million
a) Determine which product they should invest in this year based on maximizing expected value.
b) Create a sensitivity graph comparing the different alternatives as the probability of Good changes.
4) Ferry Holmes has been thinking about starting his own independent gasoline station. Ferry’s problem is to decide how large his station should be. The annual returns will depend on both the size of the station and a number of marketing factors related to the oil industry and demand for gasoline. After a careful analysis, Ferry developed the following table:
SIZE OF FIRST STATION GOOD MARKET MODERATE MARKET POOR MARKET
Small $60,000 $20,000 $10,000
Medium $80,000 $40,000 -$10,000
Large $120,000 $30,000 -$30,000
Very Large $250,000 $35,000 -$170,000
Probability 0.50 0.30 0.20
Set up a spreadsheet to find the best decision using:
a) Maximax.
b) Maximin.
c) Equally likely.
d) Expected value.
e) Minimax Regret.
Chapter 6 homework
1- The number of bottles of water sold in a machine each day is recorded below:
DAY 1 2 3 4 5 6 7 8 9 10 11 12
Bottles Sold 126 83 90 102 108 106 119 100 93 102 103 123
DAY 13 14 15 16 17 18 19 20 21 22 23 24
Bottles Sold 89 104 138 88 90 83 82 103 109 156 138 106
f) Using Excel, find the equation of best linear trend line that fits the data using Excel Regression function. Make sure to find the residuals output.
g) Use the trend line to make forecast for periods 25,26, and 27.
h) Observe the value of R-squared and interpret.
i) Using the values of residuals, find MAD.
2- An accountant at the firm Gober Anderson, Arthur believed that several traveling executives were submitting unusually high travel vouchers when they returned from business trips. Arthur took a sample of 300 vouchers submitted from the past year. Then he developed the following multiple regression equation relating expected travel cost (Cost) to number of days on the road (Days) and distance traveled (Distance) in miles:
Cost = $110.00 + $52.30 Days + $0.55 Distance
The coefficient of correlation computed was 0.74.
j) If Ken Lay returns from a 350-mile trip that took him out of town for 7 days, what is the expected amount he should claim as expenses (use the regression equation to predict this value)?
k) Ken submitted a reimbursement request for $1012. Based on model above, is this amount reasonable? Explain.
l) Should any other variables be included? Which ones? Why?
3- Consider the following data on demand (in 10,000) of bars of soap. The independent variables are Time Period (PERIOD), Price, Average Industry Price (AIP), and Advertising (in $1000). We are interested in building different models to forecast demand.
m) Using Excel, construct the correlation matrix and interpret (relation of all variables with demand). Rank variables based on degree of absolute values of correlation with Demand.
n) Using Excel linear regression analysis, find the Trend line to predict demand based on Time Period. Observe R-squared value and Significant F and interpret.
o) Construct multiple linear regression model using all independent variables to predict Demand (Y). Provide the equation and interpretation of R-squared, Significant-F.
p) Based on P-values of independent variables, rank the variables based on degree of contribution to the model. Hint: The lower the P-value, the higher the significance of a given variable.
q) Use the equation in part (c) to forecast demand for September 2023 if Price = $3.90, AIP = $4.25, and
ADV= $7.3.
Copy and paste the data below in an Excel file for analysis.
Month/Yr. PERIOD PRICE AIP ADV DEMAND
March 2021 1 3.85 3.8 5.5 7.38
2 3.75 4 6.75 8.1
3 3.7 4.3 7.25 9.52
4 3.7 3.7 7.5 9.2
5 3.6 3.85 7 9.33
6 3.6 3.8 6.5 8.28
7 3.6 3.75 6.75 8.2
8 3.8 3.85 5.25 7.87
9 3.8 3.65 5.25 7.1
10 3.85 4 6 8
Jan. 2022 11 3.9 4.1 6.5 7.89
12 3.9 4 6.25 8.15
13 3.7 4.1 7 9.1
14 3.75 4.2 6.9 9.26
15 3.75 4.1 6.8 9.5
16 3.8 4.1 6.8 9.72
17 3.7 4.2 7.1 9.26
18 3.8 4.3 7 9
19 3.7 4.1 6.8 8.75
20 3.8 3.75 6.5 7.95
21 3.8 3.75 6.25 7.65
22 3.75 3.65 6 8.3
Jan. 2023 23 3.7 3.9 6.5 9.2
24 3.55 3.65 7 9.5
25 3.6 4.1 6.8 9.75
26 3.65 4.25 6.8 10.21
27 3.7 3.65 6.5 9.27
28 3.75 3.75 5.75 8.67
29 3.8 3.85 5.8 8.45
Aug. 2023 30 3.7 4.25 6.8 8.2
Sep. 2023 31
Chapter 7 homework
1- Using the data in following table,
MONTH SALES
January 908
February 1235
March 804
April 1019
May 1151
June 1383
July 894
August 1255
September 1087
October 1382
November 957
December 1254
a) Compute a 3-month moving forecast of Sales from April through December and for the next month, January. Compute the MAD for the forecast.
b) Compute a 6-month moving forecast of Sales from July through December and for the next month, January. Compute the MAD for the forecast.
c) Compute a weighted 3-month moving forecast of Sales from April through December and for the next month, January, using weights of 0.50 (most recent data), 0.30, and 0.20 (most distant data). Compute the MAD for the forecast.
D) Compare the forecasts you computed by using moving forecast model from part a, b, and c. Which forecasting model does a better job?
2- Using the data from problem 1,
A) Compute an exponentially smoothed forecast with alpha= 0.80 through December and create a forecast for the next month, January. Use initial forecast of 908 for January. Find MAD.
B) Compute an exponentially smoothed forecast with alpha= 0.60 through December and create a forecast for the next month, January. Use initial forecast of 908 for January. Find MAD.
C) Compare the forecasts you computed by using an exponential smoothing model from part a and b. Which forecasting model does a better job?
3- Quarterly gas usage in MCF is shown below.
SEASON USAGE
Year 2020 2021 2022
Fall 1091 1071 1104
Winter 1138 1161 1146
Spring 966 912 903
Summer 831 830 825
A) What are the seasonal indices for the four seasons?
B) Find the de-seasonalized values of the above gas usages.
4- Passenger miles, in thousands flown, on Commuter Odessa Express Airlines, a commuter firm serving the Midland TX, are shown for the past 12 weeks.
Week 1 2 3 4 5 6 7 8 9 10 11 12
Miles Flown
1000’s 14 25 13 22 16 18 25 18 23 22 17 26
A) Assuming forecast value of 16 for week 1, use Excel to find exponential forecasts miles for weeks 2 through 13 using alpha = 0.6. Calculate MAD for this model.
B) Redo part (a) using alpha = 0.90.
C) Which forecasting model is better? Why?
5- Attendance at Old-Time Spring, Bonnie and Clyde attraction, has been as follows:
QUARTER ATTENDEES 1000’S
Spring ‘20 10.4
Summer ‘20 16.8
Fall ‘20 7.4
Winter ‘20 6.5
Spring ‘21 8.2
Summer ‘21 12.4
Fall ‘21 5.2
Winter ‘21 8.9
Spring ‘22 14.6
Summer ‘22 20.5
Fall ‘22 9.8
Winter ‘22 7.3
A) Compute seasonal indices for this data.
B) Find the de-seasonalized values and construct a linear graph of both data over time.