Get tutorials for this question at:
http://www.homeworkminutes.com/question/view/31049/BUS-204-Below-are-the-scores-achieved-during-the-first-half-of-Bus-204-in-a-prior-semester-and-the-final-overall-total
QuestionQ1
Below are the scores achieved during the first half of Bus 204 in a
prior semester and the final overall total course score. How well do
these averages predict the final course score?
Q1 and 2
Q Average
13%
26%
52%
93%
73%
55%
82%
68%
62%
30%
88%
86%
63%
42%
68%
28%
59%
61%
60%
50%
35%
41%
58%
64%
36%
48%
64%
62%
22%
48%
83%
55%
51%
64%
31%
57%
44%
29%
66%
93%
68%
34%
66%
18%
63%
60%
65%
78%
70%
63%
First half grades
HW 1-6
HW Average MT Score
58%
49%
106%
102%
96%
102%
102%
94%
94%
94%
102%
101%
97%
76%
95%
61%
103%
103%
74%
67%
83%
96%
94%
94%
83%
100%
98%
97%
87%
78%
101%
103%
77%
95%
34%
71%
73%
53%
98%
102%
101%
82%
106%
15%
103%
102%
73%
98%
98%
97%
81%
69%
68%
80%
81%
68%
96%
85%
77%
86%
72%
82%
85%
65%
82%
47%
70%
111%
82%
97%
81%
82%
86%
59%
59%
61%
92%
53%
49%
62%
74%
76%
38%
93%
43%
65%
55%
65%
92%
103%
76%
62%
73%
41%
80%
81%
49%
100%
69%
66%
Total
75%
67%
69%
86%
92%
79%
92%
77%
83%
88%
90%
92%
78%
70%
88%
61%
73%
102%
78%
88%
80%
79%
86%
77%
66%
77%
93%
76%
61%
65%
87%
78%
61%
90%
50%
68%
61%
51%
93%
93%
91%
64%
81%
49%
85%
93%
63%
91%
78%
84%
Question 2
The data below show the value of a $1 investment in the S&P 500 at the end of 1926 and how it would have grown to the end of 2009 with dividends reinvested.
Graph these values on a line chart, then use Excel's Trendline and test every regression option it offers, including all the polynomial options. Of all the
regressions, which form yields the highest R Squared?
Year
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
Return
21.7%
-17.7%
-28.3%
-41.0%
-2.9%
106.5%
12.1%
32.9%
37.9%
-27.5%
15.6%
-8.7%
-0.1%
10.2%
20.2%
39.8%
23.9%
39.4%
-14.9%
-0.2%
-6.7%
7.9%
29.0%
13.2%
7.7%
2.5%
42.4%
16.4%
6.2%
-8.1%
41.0%
26.9%
5.1%
14.6%
-2.5%
17.2%
10.3%
21.0%
-7.0%
43.4%
34.9%
-6.2%
-0.7%
28.5%
22.0%
-14.7%
-20.0%
47.8%
32.9%
21.6%
25.2%
32.0%
32.5%
4.4%
10.5%
35.5%
4.3%
41.9%
24.0%
17.3%
33.8%
30.5%
-16.0%
34.9%
8.4%
43.4%
3.2%
14.2%
14.6%
7.2%
-3.6%
28.2%
1.1%
7.9%
-7.5%
61.7%
27.8%
16.0%
24.3%
8.9%
-41.5%
55.6%
24.7%
-11.9%
18.4%
29.1%
Example: Grippo
Grippo Golf Glove Company maeks two different brands of golf gloves. One is a full-fingered glove
and the other is a half-fingered model. Grippo currently has orders for more gloves than it can produce
in time for the upcoming golf season. The scarace resource in the manufacture of these gloves is
labor time. Grippo has available 400 hours in the cutting and sewing department, 250 hours in the
finishing department, and 150 hours in the pacckaging and shipping department. The department
time requirements and the profit per box (1gross) are given below:
Cutting and
Sewing
Full Finger
Half Finger
Finishing
Packaging
and Shipping
Profit
3
1.5
1.5
2
1
1
$20
$25
Find the optimal product mix for
Grippo, assuming Co. wants to
Maximize profit
Linear Programming On Excel
Example: Doc's Dogs
Doc's Dog Kennels, Inc., provides overnight lodging for a variety of pets. A particular
feature at Doc's is the quality of care the pets receive, including excellent food. The
kennel's dog food is made by mixing two brand-name dog food products to obtain what
Doc's calls the "well balanced dog diet" The data for the two dog foods are below:
Dog Food
Bark Bits
Canine Chow
Cost per
Ounce
Protein (percent)
Fat (Percent)
$0.025
$0.030
30
40
20
10
If Doc wants to be sure that his dogs receive at least 6 ounces of protein and 2 ounces
of fat per day, what is the minimum cost mix of the two dog food products?
Mary Annette's Puppet Shop spends $10,000 per month on internet advertising to sell its toys. Google costs $.50 per click, Yahoo, $.30 per click, and Facebook,
$.40 per click. The CEO wants to make sure the company spends at least 25% of its budget on each vendor. Each click on Google yields $3 in profit, Yahoo,
$2, Facebook, $1. The Shop must sign a contract specifying the maximum number of clicks it will buy per month. What is the maximum number of clicks it
should buy from each vendor to maximize its profits? (Hint: Let X1 = dollars spent on Google, X2 = dollars spent on Yahoo, X3 = dollars spent on Facebook.)
Question 4
The manager of a "6/10 Market" opens his store at 6AM, closes at 10PM. He wants at least 4 workers on duty during every
hour of the week, 6 on weekends. Each worker works the standard 5-8 plan. Use Solver to solve this staff scheduling problem
(see "Demand Matrix ") using the integer constraint. How many workers will be needed? (Hint - This will not have a perfect
solution with zero surpluses and shortages.)
INPUTS:
DEMAND MATRIX A: Enter the number of workers needed each 2 hour span.
Shift
Shift
Sun
Mon
Tue
Wed
12AM-2AM
1
0
0
0
0
2AM-4AM
2
0
0
0
0
4AM-6AM
3
0
0
0
0
6AM-8AM
4
6
3
3
3
8AM-10AM
5
6
3
3
3
10AM-12PM
6
7
4
4
4
12PM-2PM
7
7
4
4
4
2PM-4PM
8
7
4
4
4
4PM-6PM
9
7
4
4
4
6PM-8PM
10
7
4
4
4
8PM-10PM
11
7
4
3
3
10PM-12AM
12
0
0
0
0
Thu
Fri
0
0
0
0
0
0
3
3
3
3
4
4
4
4
4
4
4
5
4
5
3
5
0
0
Total Workerhours:
Unadjusted Workers Required:
Sat
0
0
0
7
7
7
7
7
7
7
7
0
520
13
Question 5
The Blisters Saddle Company manufactures its saddles in three plants and sells them in four
regions. The cost to transport a unit from each plant to each region is shown. Also shown is
the anticipated demand this quarter from each region and the capacity that each plant will
have in terms of how many units it can supply this quarter. Determine the optimal shipping
plan to minimize shipping costs.
Transportation Cost Matrix: Cost per Unit Shipped From
Each Plant to Each Region
Plant 1
Plant 2
Plant 3
Demand
East
$21.55
$11.29
$15.34
210
North
$10.00
$18.79
$11.38
160
South
$22.67
$22.93
$10.52
110
West
$23.97
$10.34
$15.52
200
Supply
200
250
350
Based on the asset allocation problem solved in class, start with a 100% allocation to the S&P 500. Assume the investor wants to
maximimze the minimum return she would have experienced over all 30 year-periods 1928-2013 using the equity asset
classes shown below. She also wants to track the expected returns and the worst one year loss. Use Solver to determine
the best allocation to achieve this. (Hints: Use GRG Nonlinear as the solution method and only constraint needed is that
the allocations must sum to 100%.)
Q6.
Optimization
"How should I allocate the money in my portfolio to maximize minimum returns?"
INPUTS:
Objective: Maximize Minimum Return over 30 years
Subject to Constraints:
Sum of Allocations = 100%
OUTPUTS:
Minimum 30-year return (based on all 30-year returns):
Expected 30-Year Return (based on all 30-year returns):
Worst one year loss:
Allocation:
100%
0%
0%
0%
0%
US Stocks
S&P 500
-Large Cap Large Cap Small Cap
Year
Blend
Growth
Growth
37.5%
44.2%
28.4%
1928
-10.8%
-10.1%
-35.5%
1929
-28.2%
-29.5%
-35.3%
1930
-43.6%
-37.2%
-45.8%
1931
-8.5%
-6.3%
-0.3%
1932
51.0%
47.1%
108.0%
1933
3.0%
10.8%
18.6%
1934
43.1%
39.7%
54.1%
1935
30.1%
24.5%
47.1%
1936
-33.4%
-34.1%
-44.2%
1937
26.4%
36.7%
37.3%
1938
3.0%
1.3%
3.7%
1939
-7.6%
-9.0%
-5.0%
1940
-10.4%
-13.4%
-8.1%
1941
14.9%
15.6%
25.2%
1942
25.4%
24.0%
52.9%
1943
18.8%
16.0%
40.0%
1944
34.8%
33.3%
59.2%
1945
-5.6%
-7.9%
-9.9%
1946
3.7%
1.2%
-2.3%
1947
2.4%
2.9%
-3.4%
1948
19.8%
21.2%
19.9%
1949
28.3%
20.3%
33.9%
1950
20.9%
19.8%
14.6%
1951
13.5%
12.5%
9.3%
1952
0.7%
3.5%
-2.2%
1953
48.8%
42.8%
53.0%
1954
25.5%
22.5%
19.4%
1955
7.9%
9.6%
6.4%
1956
-9.9%
-8.8%
-15.9%
1957
42.8%
42.0%
57.1%
1958
11.7%
9.2%
16.2%
1959
1.1%
3.6%
-2.7%
1960
26.3%
25.9%
27.4%
1961
-9.5%
-10.2%
-15.0%
1962
21.1%
20.0%
17.5%
1963
15.6%
14.4%
15.5%
1964
11.9%
12.7%
32.5%
1965
-9.3%
-9.9%
-6.1%
1966
23.5%
22.6%
58.9%
1967
11.1%
6.6%
29.6%
1968
-9.4%
-1.5%
-19.9%
1969
1.3%
-3.4%
-8.6%
1970
15.5%
23.2%
19.0%
1971
17.8%
16.7%
5.5%
1972
-16.9%
-25.2%
-31.0%
1973
-27.9%
-25.3%
-23.3%
1974
36.2%
34.7%
69.6%
1975
24.5%
15.9%
41.1%
1976
-5.7%
-6.8%
21.3%
1977
6.5%
9.3%
22.3%
1978
21.1%
17.9%
48.7%
1979
32.1%
38.9%
50.3%
1980
-4.1%
-7.9%
-1.3%
1981
20.3%
28.9%
27.6%
1982
21.6%
18.4%
28.7%
1983
5.7%
2.3%
-11.7%
1984
32.2%
38.2%
34.1%
1985
17.4%
14.7%
9.0%
1986
2.9%
1.4%
-8.0%
1987
17.2%
16.7%
24.2%
1988
30.2%
30.6%
20.1%
1989
-4.6%
-1.3%
-13.3%
1990
32.6%
47.7%
46.6%
1991
8.2%
5.5%
15.3%
1992
9.6%
-0.6%
11.5%
1993
0.1%
2.9%
-0.7%
1994
37.0%
39.7%
25.1%
1995
21.7%
27.3%
17.4%
1996
31.9%
31.1%
23.0%
1997
27.5%
34.1%
-1.2%
1998
21.7%
22.6%
28.8%
1999
-9.3%
-9.7%
2.4%
2000
-13.0%
-12.8%
9.9%
2001
-22.1%
-21.3%
-13.8%
2002
28.5%
21.8%
39.5%
2003
10.7%
8.4%
20.7%
2004
5.8%
2.2%
7.0%
2005
14.8%
10.9%
14.8%
2006
6.4%
10.3%
2.2%
2007
-36.7%
-30.0%
-36.9%
2008
27.2%
26.7%
31.5%
2009
15.5%
17.9%
29.1%
2010
1.1%
6.4%
1.8%
2011
15.9%
15.3%
15.6%
2012
31.9%
33.3%
41.8%
2013
Avg.
Max
Min
Stdev
0%
0%
International - Developed
Total
Market
Developed Total Market
0%
0%
International - Emerging
34.9%
-16.1%
-33.8%
-50.0%
-7.9%
83.5%
4.9%
46.5%
38.6%
-38.5%
29.4%
-1.8%
-6.3%
-7.6%
19.7%
36.4%
28.3%
47.2%
-7.0%
2.8%
-0.6%
18.7%
36.1%
18.0%
11.9%
-2.4%
55.0%
21.4%
8.4%
-13.4%
51.4%
12.6%
-0.4%
27.4%
-10.7%
20.3%
17.4%
21.8%
-8.0%
38.2%
23.4%
-18.2%
-0.7%
15.6%
10.7%
-22.6%
-25.4%
46.0%
38.5%
4.2%
11.7%
29.6%
30.7%
2.2%
23.8%
29.1%
2.8%
30.9%
14.2%
-0.8%
21.8%
24.0%
-12.5%
35.1%
15.4%
16.2%
-0.6%
34.4%
20.7%
30.2%
10.0%
19.8%
1.0%
-0.7%
-18.8%
39.5%
15.8%
7.4%
17.0%
1.0%
-38.5%
32.1%
21.3%
-2.5%
17.4%
37.1%
35.2%
-22.4%
-41.1%
-54.3%
-10.6%
127.0%
1.7%
52.2%
58.9%
-48.0%
33.6%
-7.6%
-6.1%
-5.1%
28.9%
56.8%
41.4%
63.7%
-11.9%
2.5%
-3.9%
18.3%
50.0%
12.3%
11.0%
-8.1%
62.1%
19.8%
5.4%
-18.7%
64.3%
16.8%
-7.7%
28.1%
-10.5%
24.3%
22.3%
36.5%
-9.6%
63.1%
42.6%
-25.8%
-3.4%
18.7%
6.8%
-28.4%
-21.5%
62.0%
53.9%
16.5%
18.4%
35.1%
24.6%
13.4%
31.5%
41.7%
-1.2%
27.9%
7.5%
-2.8%
29.9%
15.4%
-23.7%
44.3%
30.4%
22.7%
1.0%
30.6%
23.1%
32.9%
-3.2%
12.7%
19.5%
19.3%
-15.5%
60.7%
22.6%
7.6%
18.6%
-7.8%
-36.2%
46.0%
27.5%
-6.9%
19.2%
42.1%
9.2%
-9.9%
-20.4%
-34.8%
4.1%
77.0%
10.3%
6.4%
12.4%
-7.9%
-8.1%
-12.4%
7.4%
27.7%
1.8%
14.4%
-7.6%
4.5%
-23.3%
-4.5%
-8.3%
-6.7%
6.7%
11.3%
0.8%
12.3%
31.0%
8.0%
-1.0%
0.7%
21.7%
44.7%
13.5%
5.4%
-5.4%
7.4%
-1.8%
-3.3%
-9.7%
23.5%
24.4%
5.4%
-7.4%
31.8%
36.8%
-10.4%
-21.1%
33.8%
5.3%
20.0%
32.2%
9.8%
25.3%
-1.7%
-3.7%
24.6%
9.2%
53.7%
63.7%
25.6%
31.1%
18.6%
-21.0%
19.3%
-8.3%
39.3%
11.3%
4.9%
5.3%
-6.8%
14.0%
26.6%
-8.6%
-16.8%
-9.0%
51.6%
26.6%
18.5%
26.9%
9.2%
-45.1%
39.5%
14.1%
-15.2%
17.0%
22.6%
Small Cap
11.2%
-8.6%
-19.3%
-34.2%
5.8%
82.9%
13.0%
8.7%
14.9%
-6.5%
-6.7%
-11.4%
9.3%
30.6%
4.2%
17.2%
-5.4%
7.0%
-22.4%
-2.8%
-7.1%
-5.2%
8.9%
13.6%
2.7%
14.6%
33.4%
10.4%
1.1%
2.5%
24.0%
47.9%
16.1%
6.9%
-3.1%
9.7%
-0.2%
-1.2%
-8.1%
26.1%
27.5%
8.0%
-5.3%
35.4%
40.3%
-8.4%
-20.1%
36.7%
7.7%
23.0%
35.3%
12.9%
28.7%
-0.1%
-3.3%
35.7%
10.6%
72.0%
55.3%
52.4%
33.3%
36.8%
-17.9%
4.2%
-22.1%
44.2%
8.2%
3.0%
2.5%
-15.9%
8.6%
20.3%
-3.0%
-7.4%
2.8%
65.2%
33.8%
22.9%
26.3%
5.2%
-43.1%
46.4%
18.1%
-15.9%
17.1%
31.1%
Total Market
1.7%
-9.0%
-8.8%
-16.7%
-3.5%
85.0%
24.6%
11.2%
18.3%
-4.5%
-4.4%
-15.4%
-0.2%
19.7%
19.7%
24.8%
18.3%
20.1%
-12.3%
-1.4%
-14.3%
-3.5%
8.8%
10.6%
5.6%
7.3%
1.9%
13.4%
13.4%
2.3%
2.5%
17.8%
17.7%
-12.7%
22.3%
10.8%
-3.9%
11.9%
-1.0%
12.3%
28.5%
21.0%
16.1%
39.2%
31.7%
15.4%
-12.9%
13.8%
17.7%
26.0%
20.9%
38.5%
36.8%
-4.8%
-25.1%
23.3%
14.5%
26.4%
12.5%
24.1%
43.2%
69.3%
-10.4%
64.0%
12.7%
79.6%
-1.5%
-12.8%
8.1%
-22.9%
-22.1%
60.1%
-31.8%
1.3%
-3.8%
64.1%
31.6%
27.8%
34.8%
45.6%
-53.6%
89.6%
24.4%
-21.5%
20.0%
-3.5%
Value
6.3%
-8.9%
-12.0%
-23.3%
0.2%
99.0%
26.3%
13.6%
21.5%
-4.4%
-4.4%
-15.2%
4.1%
27.5%
19.4%
27.7%
15.3%
20.6%
-15.9%
-0.7%
-12.9%
-3.2%
11.6%
14.5%
7.0%
12.0%
13.3%
15.9%
13.1%
4.1%
10.9%
31.2%
21.4%
-7.3%
19.4%
13.5%
-2.0%
11.1%
-2.0%
19.9%
34.1%
21.7%
13.4%
45.6%
40.7%
11.9%
-15.8%
24.4%
18.7%
30.6%
30.0%
38.1%
41.5%
-2.7%
-20.8%
29.6%
17.3%
41.4%
32.6%
30.6%
48.8%
53.0%
1.0%
39.5%
-5.5%
105.4%
5.3%
-8.3%
12.7%
-21.9%
-19.1%
66.2%
-27.4%
3.9%
-0.1%
75.9%
35.5%
29.0%
39.2%
48.1%
-51.7%
101.0%
24.7%
-25.7%
20.3%
-4.6%
11.1%
47.7%
-37.2%
15.0%
108.0%
-45.8%
13.0%
83.5%
-50.0%
11.0%
51.0%
-43.6%
16.5%
127.0%
-54.3%
9.5%
77.0%
-45.1%
12.0%
82.9%
-43.1%
13.3%
89.6%
-53.6%
16.2%
105.4%
-51.7%
19.7%
27.2%
22.9%
19.6%
29.8%
20.9%
22.8%
25.5%
27.5%
Based on the asset allocation problem solved in class, start with a 100% allocation to the S&P 500. Assume the investor wants to
maximimze the minimum return she would have experienced over all 30 year-periods since 1927. She also wants the worst one year
return to be no more than -35%. Use Solver to determine the best allocation to achieve this.
Q7
Optimization
"How should I allocate the money in my portfolio to maximize returns?"
INPUTS:
Objective: Maximize Minimum Return over 30 years
Subject to Constraints:
Sum of Allocations = 100%
Worst one year loss permitted:
35%
OUTPUTS:
Minimum 30-year return (based on all 30-year returns):
Expected 30-Year Return (based on all 30-year returns):
Worst one year loss:
100%
0%
0%
0%
0%
US Stocks
S&P 500
-Large Cap Large Cap Small Cap
Year
Blend
Growth
Growth
37.5%
44.2%
28.4%
1928
-10.8%
-10.1%
-35.5%
1929
-28.2%
-29.5%
-35.3%
1930
-43.6%
-37.2%
-45.8%
1931
-8.5%
-6.3%
-0.3%
1932
51.0%
47.1%
108.0%
1933
3.0%
10.8%
18.6%
1934
43.1%
39.7%
54.1%
1935
30.1%
24.5%
47.1%
1936
-33.4%
-34.1%
-44.2%
1937
26.4%
36.7%
37.3%
1938
3.0%
1.3%
3.7%
1939
-7.6%
-9.0%
-5.0%
1940
-10.4%
-13.4%
-8.1%
1941
14.9%
15.6%
25.2%
1942
25.4%
24.0%
52.9%
1943
18.8%
16.0%
40.0%
1944
34.8%
33.3%
59.2%
1945
-5.6%
-7.9%
-9.9%
1946
3.7%
1.2%
-2.3%
1947
2.4%
2.9%
-3.4%
1948
19.8%
21.2%
19.9%
1949
28.3%
20.3%
33.9%
1950
20.9%
19.8%
14.6%
1951
13.5%
12.5%
9.3%
1952
0.7%
3.5%
-2.2%
1953
48.8%
42.8%
53.0%
1954
25.5%
22.5%
19.4%
1955
7.9%
9.6%
6.4%
1956
-9.9%
-8.8%
-15.9%
1957
42.8%
42.0%
57.1%
1958
11.7%
9.2%
16.2%
1959
1.1%
3.6%
-2.7%
1960
26.3%
25.9%
27.4%
1961
-9.5%
-10.2%
-15.0%
1962
21.1%
20.0%
17.5%
1963
15.6%
14.4%
15.5%
1964
11.9%
12.7%
32.5%
1965
-9.3%
-9.9%
-6.1%
1966
23.5%
22.6%
58.9%
1967
11.1%
6.6%
29.6%
1968
-9.4%
-1.5%
-19.9%
1969
1.3%
-3.4%
-8.6%
1970
15.5%
23.2%
19.0%
1971
17.8%
16.7%
5.5%
1972
-16.9%
-25.2%
-31.0%
1973
-27.9%
-25.3%
-23.3%
1974
36.2%
34.7%
69.6%
1975
24.5%
15.9%
41.1%
1976
-5.7%
-6.8%
21.3%
1977
6.5%
9.3%
22.3%
1978
21.1%
17.9%
48.7%
1979
32.1%
38.9%
50.3%
1980
-4.1%
-7.9%
-1.3%
1981
20.3%
28.9%
27.6%
1982
21.6%
18.4%
28.7%
1983
5.7%
2.3%
-11.7%
1984
32.2%
38.2%
34.1%
1985
17.4%
14.7%
9.0%
1986
2.9%
1.4%
-8.0%
1987
17.2%
16.7%
24.2%
1988
30.2%
30.6%
20.1%
1989
-4.6%
-1.3%
-13.3%
1990
32.6%
47.7%
46.6%
1991
8.2%
5.5%
15.3%
1992
9.6%
-0.6%
11.5%
1993
0.1%
2.9%
-0.7%
1994
37.0%
39.7%
25.1%
1995
21.7%
27.3%
17.4%
1996
31.9%
31.1%
23.0%
1997
27.5%
34.1%
-1.2%
1998
21.7%
22.6%
28.8%
1999
-9.3%
-9.7%
2.4%
2000
-13.0%
-12.8%
9.9%
2001
-22.1%
-21.3%
-13.8%
2002
28.5%
21.8%
39.5%
2003
10.7%
8.4%
20.7%
2004
5.8%
2.2%
7.0%
2005
14.8%
10.9%
14.8%
2006
6.4%
10.3%
2.2%
2007
-36.7%
-30.0%
-36.9%
2008
27.2%
26.7%
31.5%
2009
15.5%
17.9%
29.1%
2010
1.1%
6.4%
1.8%
2011
15.9%
15.3%
15.6%
2012
31.9%
33.3%
41.8%
2013
Avg.
Max
Min
Stdev
0%
0%
International - Developed
Total
Market
Developed Total Market
0%
0%
International - Emerging
34.9%
-16.1%
-33.8%
-50.0%
-7.9%
83.5%
4.9%
46.5%
38.6%
-38.5%
29.4%
-1.8%
-6.3%
-7.6%
19.7%
36.4%
28.3%
47.2%
-7.0%
2.8%
-0.6%
18.7%
36.1%
18.0%
11.9%
-2.4%
55.0%
21.4%
8.4%
-13.4%
51.4%
12.6%
-0.4%
27.4%
-10.7%
20.3%
17.4%
21.8%
-8.0%
38.2%
23.4%
-18.2%
-0.7%
15.6%
10.7%
-22.6%
-25.4%
46.0%
38.5%
4.2%
11.7%
29.6%
30.7%
2.2%
23.8%
29.1%
2.8%
30.9%
14.2%
-0.8%
21.8%
24.0%
-12.5%
35.1%
15.4%
16.2%
-0.6%
34.4%
20.7%
30.2%
10.0%
19.8%
1.0%
-0.7%
-18.8%
39.5%
15.8%
7.4%
17.0%
1.0%
-38.5%
32.1%
21.3%
-2.5%
17.4%
37.1%
35.2%
-22.4%
-41.1%
-54.3%
-10.6%
127.0%
1.7%
52.2%
58.9%
-48.0%
33.6%
-7.6%
-6.1%
-5.1%
28.9%
56.8%
41.4%
63.7%
-11.9%
2.5%
-3.9%
18.3%
50.0%
12.3%
11.0%
-8.1%
62.1%
19.8%
5.4%
-18.7%
64.3%
16.8%
-7.7%
28.1%
-10.5%
24.3%
22.3%
36.5%
-9.6%
63.1%
42.6%
-25.8%
-3.4%
18.7%
6.8%
-28.4%
-21.5%
62.0%
53.9%
16.5%
18.4%
35.1%
24.6%
13.4%
31.5%
41.7%
-1.2%
27.9%
7.5%
-2.8%
29.9%
15.4%
-23.7%
44.3%
30.4%
22.7%
1.0%
30.6%
23.1%
32.9%
-3.2%
12.7%
19.5%
19.3%
-15.5%
60.7%
22.6%
7.6%
18.6%
-7.8%
-36.2%
46.0%
27.5%
-6.9%
19.2%
42.1%
9.2%
-9.9%
-20.4%
-34.8%
4.1%
77.0%
10.3%
6.4%
12.4%
-7.9%
-8.1%
-12.4%
7.4%
27.7%
1.8%
14.4%
-7.6%
4.5%
-23.3%
-4.5%
-8.3%
-6.7%
6.7%
11.3%
0.8%
12.3%
31.0%
8.0%
-1.0%
0.7%
21.7%
44.7%
13.5%
5.4%
-5.4%
7.4%
-1.8%
-3.3%
-9.7%
23.5%
24.4%
5.4%
-7.4%
31.8%
36.8%
-10.4%
-21.1%
33.8%
5.3%
20.0%
32.2%
9.8%
25.3%
-1.7%
-3.7%
24.6%
9.2%
53.7%
63.7%
25.6%
31.1%
18.6%
-21.0%
19.3%
-8.3%
39.3%
11.3%
4.9%
5.3%
-6.8%
14.0%
26.6%
-8.6%
-16.8%
-9.0%
51.6%
26.6%
18.5%
26.9%
9.2%
-45.1%
39.5%
14.1%
-15.2%
17.0%
22.6%
Small Cap
11.2%
-8.6%
-19.3%
-34.2%
5.8%
82.9%
13.0%
8.7%
14.9%
-6.5%
-6.7%
-11.4%
9.3%
30.6%
4.2%
17.2%
-5.4%
7.0%
-22.4%
-2.8%
-7.1%
-5.2%
8.9%
13.6%
2.7%
14.6%
33.4%
10.4%
1.1%
2.5%
24.0%
47.9%
16.1%
6.9%
-3.1%
9.7%
-0.2%
-1.2%
-8.1%
26.1%
27.5%
8.0%
-5.3%
35.4%
40.3%
-8.4%
-20.1%
36.7%
7.7%
23.0%
35.3%
12.9%
28.7%
-0.1%
-3.3%
35.7%
10.6%
72.0%
55.3%
52.4%
33.3%
36.8%
-17.9%
4.2%
-22.1%
44.2%
8.2%
3.0%
2.5%
-15.9%
8.6%
20.3%
-3.0%
-7.4%
2.8%
65.2%
33.8%
22.9%
26.3%
5.2%
-43.1%
46.4%
18.1%
-15.9%
17.1%
31.1%
Total Market
1.7%
-9.0%
-8.8%
-16.7%
-3.5%
85.0%
24.6%
11.2%
18.3%
-4.5%
-4.4%
-15.4%
-0.2%
19.7%
19.7%
24.8%
18.3%
20.1%
-12.3%
-1.4%
-14.3%
-3.5%
8.8%
10.6%
5.6%
7.3%
1.9%
13.4%
13.4%
2.3%
2.5%
17.8%
17.7%
-12.7%
22.3%
10.8%
-3.9%
11.9%
-1.0%
12.3%
28.5%
21.0%
16.1%
39.2%
31.7%
15.4%
-12.9%
13.8%
17.7%
26.0%
20.9%
38.5%
36.8%
-4.8%
-25.1%
23.3%
14.5%
26.4%
12.5%
24.1%
43.2%
69.3%
-10.4%
64.0%
12.7%
79.6%
-1.5%
-12.8%
8.1%
-22.9%
-22.1%
60.1%
-31.8%
1.3%
-3.8%
64.1%
31.6%
27.8%
34.8%
45.6%
-53.6%
89.6%
24.4%
-21.5%
20.0%
-3.5%
Value
6.3%
-8.9%
-12.0%
-23.3%
0.2%
99.0%
26.3%
13.6%
21.5%
-4.4%
-4.4%
-15.2%
4.1%
27.5%
19.4%
27.7%
15.3%
20.6%
-15.9%
-0.7%
-12.9%
-3.2%
11.6%
14.5%
7.0%
12.0%
13.3%
15.9%
13.1%
4.1%
10.9%
31.2%
21.4%
-7.3%
19.4%
13.5%
-2.0%
11.1%
-2.0%
19.9%
34.1%
21.7%
13.4%
45.6%
40.7%
11.9%
-15.8%
24.4%
18.7%
30.6%
30.0%
38.1%
41.5%
-2.7%
-20.8%
29.6%
17.3%
41.4%
32.6%
30.6%
48.8%
53.0%
1.0%
39.5%
-5.5%
105.4%
5.3%
-8.3%
12.7%
-21.9%
-19.1%
66.2%
-27.4%
3.9%
-0.1%
75.9%
35.5%
29.0%
39.2%
48.1%
-51.7%
101.0%
24.7%
-25.7%
20.3%
-4.6%
11.1%
47.7%
-37.2%
15.0%
108.0%
-45.8%
13.0%
83.5%
-50.0%
11.0%
51.0%
-43.6%
16.5%
127.0%
-54.3%
9.5%
77.0%
-45.1%
12.0%
82.9%
-43.1%
13.3%
89.6%
-53.6%
16.2%
105.4%
-51.7%
19.7%
27.2%
22.9%
19.6%
29.8%
20.9%
22.8%
25.5%
27.5%
Q8 A
Joe Cool just landed his first serious job at Cook, Books & Hyde, a
major accounting firm in his home town. The HR Department has
asked how much he wants to save each month into his retirement
account. He wonders how much will be in his account if he earns
10% and can save various amounts amounts over various time
periods. He wants to test monthly savings of $25, 50, 75, 100,
125, 150, 175, and $200 and asks you to compute much will be in
the retirement account after spans of 20, 25, 30, 35, and 40 years.
Q8B
Tom Swift is an investor but the stock market scares him. He just
learned he can invest some of the money he just inherited in
lottery winnings. Sometimes people who have won a lottery and
opted for a stream of payments change their minds and want to
sell the stream of income for a lump sum. These people can put
up their lottery winnings in an auction where investors can bid on
them. Tom is considering bidding on a stream of income that will
pay $10,000 a year for the next 20 years. He is considering bids
of $50,000, $55,000, $60,000, $65,000... $100,000. He asks you
to compute what return he would get with each bid if he won.
Help Tom out.
Question 4
The manager of a "6/10 Market" opens his store at 6AM, closes at 10PM. He wants at least 4 workers on duty during every
hour of the week, 6 on weekends. He is exploring the idea of using a "4-10 plan." That is, workers work 4 consecutive days, 10
consecutive hours each. Use Solver to solve this staff scheduling problem (see "Demand Matrix ") using the integer constraint.
How many workers will be needed? What do you conclude about the efficiency of the 4-10 plan vs. the 5-8 plan?
INPUTS:
DEMAND MATRIX A: Enter the number of workers needed each 2 hour span.
Shift
Shift
Sun
Mon
Tue
Wed
12AM-2AM
1
0
0
0
0
2AM-4AM
2
0
0
0
0
4AM-6AM
3
0
0
0
0
6AM-8AM
4
6
3
3
3
8AM-10AM
5
6
3
3
3
10AM-12PM
6
7
4
4
4
12PM-2PM
7
7
4
4
4
2PM-4PM
8
7
4
4
4
4PM-6PM
9
7
4
4
4
6PM-8PM
10
7
4
4
4
8PM-10PM
11
7
4
3
3
10PM-12AM
12
0
0
0
0
Thu
Fri
0
0
0
0
0
0
3
3
3
3
4
4
4
4
4
4
4
5
4
5
3
5
0
0
Total Workerhours:
Unadjusted Workers Required:
Sat
0
0
0
7
7
7
7
7
7
7
7
0
520
13
Get tutorials for this question at:
http://www.homeworkminutes.com/question/view/31049/BUS-204-Below-are-the-scores-achieved-during-the-first-half-of-Bus-204-in-a-prior-semester-and-the-final-overall-total
http://www.homeworkminutes.com/question/view/31049/BUS-204-Below-are-the-scores-achieved-during-the-first-half-of-Bus-204-in-a-prior-semester-and-the-final-overall-total
QuestionQ1
Below are the scores achieved during the first half of Bus 204 in a
prior semester and the final overall total course score. How well do
these averages predict the final course score?
Q1 and 2
Q Average
13%
26%
52%
93%
73%
55%
82%
68%
62%
30%
88%
86%
63%
42%
68%
28%
59%
61%
60%
50%
35%
41%
58%
64%
36%
48%
64%
62%
22%
48%
83%
55%
51%
64%
31%
57%
44%
29%
66%
93%
68%
34%
66%
18%
63%
60%
65%
78%
70%
63%
First half grades
HW 1-6
HW Average MT Score
58%
49%
106%
102%
96%
102%
102%
94%
94%
94%
102%
101%
97%
76%
95%
61%
103%
103%
74%
67%
83%
96%
94%
94%
83%
100%
98%
97%
87%
78%
101%
103%
77%
95%
34%
71%
73%
53%
98%
102%
101%
82%
106%
15%
103%
102%
73%
98%
98%
97%
81%
69%
68%
80%
81%
68%
96%
85%
77%
86%
72%
82%
85%
65%
82%
47%
70%
111%
82%
97%
81%
82%
86%
59%
59%
61%
92%
53%
49%
62%
74%
76%
38%
93%
43%
65%
55%
65%
92%
103%
76%
62%
73%
41%
80%
81%
49%
100%
69%
66%
Total
75%
67%
69%
86%
92%
79%
92%
77%
83%
88%
90%
92%
78%
70%
88%
61%
73%
102%
78%
88%
80%
79%
86%
77%
66%
77%
93%
76%
61%
65%
87%
78%
61%
90%
50%
68%
61%
51%
93%
93%
91%
64%
81%
49%
85%
93%
63%
91%
78%
84%
Question 2
The data below show the value of a $1 investment in the S&P 500 at the end of 1926 and how it would have grown to the end of 2009 with dividends reinvested.
Graph these values on a line chart, then use Excel's Trendline and test every regression option it offers, including all the polynomial options. Of all the
regressions, which form yields the highest R Squared?
Year
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
Return
21.7%
-17.7%
-28.3%
-41.0%
-2.9%
106.5%
12.1%
32.9%
37.9%
-27.5%
15.6%
-8.7%
-0.1%
10.2%
20.2%
39.8%
23.9%
39.4%
-14.9%
-0.2%
-6.7%
7.9%
29.0%
13.2%
7.7%
2.5%
42.4%
16.4%
6.2%
-8.1%
41.0%
26.9%
5.1%
14.6%
-2.5%
17.2%
10.3%
21.0%
-7.0%
43.4%
34.9%
-6.2%
-0.7%
28.5%
22.0%
-14.7%
-20.0%
47.8%
32.9%
21.6%
25.2%
32.0%
32.5%
4.4%
10.5%
35.5%
4.3%
41.9%
24.0%
17.3%
33.8%
30.5%
-16.0%
34.9%
8.4%
43.4%
3.2%
14.2%
14.6%
7.2%
-3.6%
28.2%
1.1%
7.9%
-7.5%
61.7%
27.8%
16.0%
24.3%
8.9%
-41.5%
55.6%
24.7%
-11.9%
18.4%
29.1%
Example: Grippo
Grippo Golf Glove Company maeks two different brands of golf gloves. One is a full-fingered glove
and the other is a half-fingered model. Grippo currently has orders for more gloves than it can produce
in time for the upcoming golf season. The scarace resource in the manufacture of these gloves is
labor time. Grippo has available 400 hours in the cutting and sewing department, 250 hours in the
finishing department, and 150 hours in the pacckaging and shipping department. The department
time requirements and the profit per box (1gross) are given below:
Cutting and
Sewing
Full Finger
Half Finger
Finishing
Packaging
and Shipping
Profit
3
1.5
1.5
2
1
1
$20
$25
Find the optimal product mix for
Grippo, assuming Co. wants to
Maximize profit
Linear Programming On Excel
Example: Doc's Dogs
Doc's Dog Kennels, Inc., provides overnight lodging for a variety of pets. A particular
feature at Doc's is the quality of care the pets receive, including excellent food. The
kennel's dog food is made by mixing two brand-name dog food products to obtain what
Doc's calls the "well balanced dog diet" The data for the two dog foods are below:
Dog Food
Bark Bits
Canine Chow
Cost per
Ounce
Protein (percent)
Fat (Percent)
$0.025
$0.030
30
40
20
10
If Doc wants to be sure that his dogs receive at least 6 ounces of protein and 2 ounces
of fat per day, what is the minimum cost mix of the two dog food products?
Mary Annette's Puppet Shop spends $10,000 per month on internet advertising to sell its toys. Google costs $.50 per click, Yahoo, $.30 per click, and Facebook,
$.40 per click. The CEO wants to make sure the company spends at least 25% of its budget on each vendor. Each click on Google yields $3 in profit, Yahoo,
$2, Facebook, $1. The Shop must sign a contract specifying the maximum number of clicks it will buy per month. What is the maximum number of clicks it
should buy from each vendor to maximize its profits? (Hint: Let X1 = dollars spent on Google, X2 = dollars spent on Yahoo, X3 = dollars spent on Facebook.)
Question 4
The manager of a "6/10 Market" opens his store at 6AM, closes at 10PM. He wants at least 4 workers on duty during every
hour of the week, 6 on weekends. Each worker works the standard 5-8 plan. Use Solver to solve this staff scheduling problem
(see "Demand Matrix ") using the integer constraint. How many workers will be needed? (Hint - This will not have a perfect
solution with zero surpluses and shortages.)
INPUTS:
DEMAND MATRIX A: Enter the number of workers needed each 2 hour span.
Shift
Shift
Sun
Mon
Tue
Wed
12AM-2AM
1
0
0
0
0
2AM-4AM
2
0
0
0
0
4AM-6AM
3
0
0
0
0
6AM-8AM
4
6
3
3
3
8AM-10AM
5
6
3
3
3
10AM-12PM
6
7
4
4
4
12PM-2PM
7
7
4
4
4
2PM-4PM
8
7
4
4
4
4PM-6PM
9
7
4
4
4
6PM-8PM
10
7
4
4
4
8PM-10PM
11
7
4
3
3
10PM-12AM
12
0
0
0
0
Thu
Fri
0
0
0
0
0
0
3
3
3
3
4
4
4
4
4
4
4
5
4
5
3
5
0
0
Total Workerhours:
Unadjusted Workers Required:
Sat
0
0
0
7
7
7
7
7
7
7
7
0
520
13
Question 5
The Blisters Saddle Company manufactures its saddles in three plants and sells them in four
regions. The cost to transport a unit from each plant to each region is shown. Also shown is
the anticipated demand this quarter from each region and the capacity that each plant will
have in terms of how many units it can supply this quarter. Determine the optimal shipping
plan to minimize shipping costs.
Transportation Cost Matrix: Cost per Unit Shipped From
Each Plant to Each Region
Plant 1
Plant 2
Plant 3
Demand
East
$21.55
$11.29
$15.34
210
North
$10.00
$18.79
$11.38
160
South
$22.67
$22.93
$10.52
110
West
$23.97
$10.34
$15.52
200
Supply
200
250
350
Based on the asset allocation problem solved in class, start with a 100% allocation to the S&P 500. Assume the investor wants to
maximimze the minimum return she would have experienced over all 30 year-periods 1928-2013 using the equity asset
classes shown below. She also wants to track the expected returns and the worst one year loss. Use Solver to determine
the best allocation to achieve this. (Hints: Use GRG Nonlinear as the solution method and only constraint needed is that
the allocations must sum to 100%.)
Q6.
Optimization
"How should I allocate the money in my portfolio to maximize minimum returns?"
INPUTS:
Objective: Maximize Minimum Return over 30 years
Subject to Constraints:
Sum of Allocations = 100%
OUTPUTS:
Minimum 30-year return (based on all 30-year returns):
Expected 30-Year Return (based on all 30-year returns):
Worst one year loss:
Allocation:
100%
0%
0%
0%
0%
US Stocks
S&P 500
-Large Cap Large Cap Small Cap
Year
Blend
Growth
Growth
37.5%
44.2%
28.4%
1928
-10.8%
-10.1%
-35.5%
1929
-28.2%
-29.5%
-35.3%
1930
-43.6%
-37.2%
-45.8%
1931
-8.5%
-6.3%
-0.3%
1932
51.0%
47.1%
108.0%
1933
3.0%
10.8%
18.6%
1934
43.1%
39.7%
54.1%
1935
30.1%
24.5%
47.1%
1936
-33.4%
-34.1%
-44.2%
1937
26.4%
36.7%
37.3%
1938
3.0%
1.3%
3.7%
1939
-7.6%
-9.0%
-5.0%
1940
-10.4%
-13.4%
-8.1%
1941
14.9%
15.6%
25.2%
1942
25.4%
24.0%
52.9%
1943
18.8%
16.0%
40.0%
1944
34.8%
33.3%
59.2%
1945
-5.6%
-7.9%
-9.9%
1946
3.7%
1.2%
-2.3%
1947
2.4%
2.9%
-3.4%
1948
19.8%
21.2%
19.9%
1949
28.3%
20.3%
33.9%
1950
20.9%
19.8%
14.6%
1951
13.5%
12.5%
9.3%
1952
0.7%
3.5%
-2.2%
1953
48.8%
42.8%
53.0%
1954
25.5%
22.5%
19.4%
1955
7.9%
9.6%
6.4%
1956
-9.9%
-8.8%
-15.9%
1957
42.8%
42.0%
57.1%
1958
11.7%
9.2%
16.2%
1959
1.1%
3.6%
-2.7%
1960
26.3%
25.9%
27.4%
1961
-9.5%
-10.2%
-15.0%
1962
21.1%
20.0%
17.5%
1963
15.6%
14.4%
15.5%
1964
11.9%
12.7%
32.5%
1965
-9.3%
-9.9%
-6.1%
1966
23.5%
22.6%
58.9%
1967
11.1%
6.6%
29.6%
1968
-9.4%
-1.5%
-19.9%
1969
1.3%
-3.4%
-8.6%
1970
15.5%
23.2%
19.0%
1971
17.8%
16.7%
5.5%
1972
-16.9%
-25.2%
-31.0%
1973
-27.9%
-25.3%
-23.3%
1974
36.2%
34.7%
69.6%
1975
24.5%
15.9%
41.1%
1976
-5.7%
-6.8%
21.3%
1977
6.5%
9.3%
22.3%
1978
21.1%
17.9%
48.7%
1979
32.1%
38.9%
50.3%
1980
-4.1%
-7.9%
-1.3%
1981
20.3%
28.9%
27.6%
1982
21.6%
18.4%
28.7%
1983
5.7%
2.3%
-11.7%
1984
32.2%
38.2%
34.1%
1985
17.4%
14.7%
9.0%
1986
2.9%
1.4%
-8.0%
1987
17.2%
16.7%
24.2%
1988
30.2%
30.6%
20.1%
1989
-4.6%
-1.3%
-13.3%
1990
32.6%
47.7%
46.6%
1991
8.2%
5.5%
15.3%
1992
9.6%
-0.6%
11.5%
1993
0.1%
2.9%
-0.7%
1994
37.0%
39.7%
25.1%
1995
21.7%
27.3%
17.4%
1996
31.9%
31.1%
23.0%
1997
27.5%
34.1%
-1.2%
1998
21.7%
22.6%
28.8%
1999
-9.3%
-9.7%
2.4%
2000
-13.0%
-12.8%
9.9%
2001
-22.1%
-21.3%
-13.8%
2002
28.5%
21.8%
39.5%
2003
10.7%
8.4%
20.7%
2004
5.8%
2.2%
7.0%
2005
14.8%
10.9%
14.8%
2006
6.4%
10.3%
2.2%
2007
-36.7%
-30.0%
-36.9%
2008
27.2%
26.7%
31.5%
2009
15.5%
17.9%
29.1%
2010
1.1%
6.4%
1.8%
2011
15.9%
15.3%
15.6%
2012
31.9%
33.3%
41.8%
2013
Avg.
Max
Min
Stdev
0%
0%
International - Developed
Total
Market
Developed Total Market
0%
0%
International - Emerging
34.9%
-16.1%
-33.8%
-50.0%
-7.9%
83.5%
4.9%
46.5%
38.6%
-38.5%
29.4%
-1.8%
-6.3%
-7.6%
19.7%
36.4%
28.3%
47.2%
-7.0%
2.8%
-0.6%
18.7%
36.1%
18.0%
11.9%
-2.4%
55.0%
21.4%
8.4%
-13.4%
51.4%
12.6%
-0.4%
27.4%
-10.7%
20.3%
17.4%
21.8%
-8.0%
38.2%
23.4%
-18.2%
-0.7%
15.6%
10.7%
-22.6%
-25.4%
46.0%
38.5%
4.2%
11.7%
29.6%
30.7%
2.2%
23.8%
29.1%
2.8%
30.9%
14.2%
-0.8%
21.8%
24.0%
-12.5%
35.1%
15.4%
16.2%
-0.6%
34.4%
20.7%
30.2%
10.0%
19.8%
1.0%
-0.7%
-18.8%
39.5%
15.8%
7.4%
17.0%
1.0%
-38.5%
32.1%
21.3%
-2.5%
17.4%
37.1%
35.2%
-22.4%
-41.1%
-54.3%
-10.6%
127.0%
1.7%
52.2%
58.9%
-48.0%
33.6%
-7.6%
-6.1%
-5.1%
28.9%
56.8%
41.4%
63.7%
-11.9%
2.5%
-3.9%
18.3%
50.0%
12.3%
11.0%
-8.1%
62.1%
19.8%
5.4%
-18.7%
64.3%
16.8%
-7.7%
28.1%
-10.5%
24.3%
22.3%
36.5%
-9.6%
63.1%
42.6%
-25.8%
-3.4%
18.7%
6.8%
-28.4%
-21.5%
62.0%
53.9%
16.5%
18.4%
35.1%
24.6%
13.4%
31.5%
41.7%
-1.2%
27.9%
7.5%
-2.8%
29.9%
15.4%
-23.7%
44.3%
30.4%
22.7%
1.0%
30.6%
23.1%
32.9%
-3.2%
12.7%
19.5%
19.3%
-15.5%
60.7%
22.6%
7.6%
18.6%
-7.8%
-36.2%
46.0%
27.5%
-6.9%
19.2%
42.1%
9.2%
-9.9%
-20.4%
-34.8%
4.1%
77.0%
10.3%
6.4%
12.4%
-7.9%
-8.1%
-12.4%
7.4%
27.7%
1.8%
14.4%
-7.6%
4.5%
-23.3%
-4.5%
-8.3%
-6.7%
6.7%
11.3%
0.8%
12.3%
31.0%
8.0%
-1.0%
0.7%
21.7%
44.7%
13.5%
5.4%
-5.4%
7.4%
-1.8%
-3.3%
-9.7%
23.5%
24.4%
5.4%
-7.4%
31.8%
36.8%
-10.4%
-21.1%
33.8%
5.3%
20.0%
32.2%
9.8%
25.3%
-1.7%
-3.7%
24.6%
9.2%
53.7%
63.7%
25.6%
31.1%
18.6%
-21.0%
19.3%
-8.3%
39.3%
11.3%
4.9%
5.3%
-6.8%
14.0%
26.6%
-8.6%
-16.8%
-9.0%
51.6%
26.6%
18.5%
26.9%
9.2%
-45.1%
39.5%
14.1%
-15.2%
17.0%
22.6%
Small Cap
11.2%
-8.6%
-19.3%
-34.2%
5.8%
82.9%
13.0%
8.7%
14.9%
-6.5%
-6.7%
-11.4%
9.3%
30.6%
4.2%
17.2%
-5.4%
7.0%
-22.4%
-2.8%
-7.1%
-5.2%
8.9%
13.6%
2.7%
14.6%
33.4%
10.4%
1.1%
2.5%
24.0%
47.9%
16.1%
6.9%
-3.1%
9.7%
-0.2%
-1.2%
-8.1%
26.1%
27.5%
8.0%
-5.3%
35.4%
40.3%
-8.4%
-20.1%
36.7%
7.7%
23.0%
35.3%
12.9%
28.7%
-0.1%
-3.3%
35.7%
10.6%
72.0%
55.3%
52.4%
33.3%
36.8%
-17.9%
4.2%
-22.1%
44.2%
8.2%
3.0%
2.5%
-15.9%
8.6%
20.3%
-3.0%
-7.4%
2.8%
65.2%
33.8%
22.9%
26.3%
5.2%
-43.1%
46.4%
18.1%
-15.9%
17.1%
31.1%
Total Market
1.7%
-9.0%
-8.8%
-16.7%
-3.5%
85.0%
24.6%
11.2%
18.3%
-4.5%
-4.4%
-15.4%
-0.2%
19.7%
19.7%
24.8%
18.3%
20.1%
-12.3%
-1.4%
-14.3%
-3.5%
8.8%
10.6%
5.6%
7.3%
1.9%
13.4%
13.4%
2.3%
2.5%
17.8%
17.7%
-12.7%
22.3%
10.8%
-3.9%
11.9%
-1.0%
12.3%
28.5%
21.0%
16.1%
39.2%
31.7%
15.4%
-12.9%
13.8%
17.7%
26.0%
20.9%
38.5%
36.8%
-4.8%
-25.1%
23.3%
14.5%
26.4%
12.5%
24.1%
43.2%
69.3%
-10.4%
64.0%
12.7%
79.6%
-1.5%
-12.8%
8.1%
-22.9%
-22.1%
60.1%
-31.8%
1.3%
-3.8%
64.1%
31.6%
27.8%
34.8%
45.6%
-53.6%
89.6%
24.4%
-21.5%
20.0%
-3.5%
Value
6.3%
-8.9%
-12.0%
-23.3%
0.2%
99.0%
26.3%
13.6%
21.5%
-4.4%
-4.4%
-15.2%
4.1%
27.5%
19.4%
27.7%
15.3%
20.6%
-15.9%
-0.7%
-12.9%
-3.2%
11.6%
14.5%
7.0%
12.0%
13.3%
15.9%
13.1%
4.1%
10.9%
31.2%
21.4%
-7.3%
19.4%
13.5%
-2.0%
11.1%
-2.0%
19.9%
34.1%
21.7%
13.4%
45.6%
40.7%
11.9%
-15.8%
24.4%
18.7%
30.6%
30.0%
38.1%
41.5%
-2.7%
-20.8%
29.6%
17.3%
41.4%
32.6%
30.6%
48.8%
53.0%
1.0%
39.5%
-5.5%
105.4%
5.3%
-8.3%
12.7%
-21.9%
-19.1%
66.2%
-27.4%
3.9%
-0.1%
75.9%
35.5%
29.0%
39.2%
48.1%
-51.7%
101.0%
24.7%
-25.7%
20.3%
-4.6%
11.1%
47.7%
-37.2%
15.0%
108.0%
-45.8%
13.0%
83.5%
-50.0%
11.0%
51.0%
-43.6%
16.5%
127.0%
-54.3%
9.5%
77.0%
-45.1%
12.0%
82.9%
-43.1%
13.3%
89.6%
-53.6%
16.2%
105.4%
-51.7%
19.7%
27.2%
22.9%
19.6%
29.8%
20.9%
22.8%
25.5%
27.5%
Based on the asset allocation problem solved in class, start with a 100% allocation to the S&P 500. Assume the investor wants to
maximimze the minimum return she would have experienced over all 30 year-periods since 1927. She also wants the worst one year
return to be no more than -35%. Use Solver to determine the best allocation to achieve this.
Q7
Optimization
"How should I allocate the money in my portfolio to maximize returns?"
INPUTS:
Objective: Maximize Minimum Return over 30 years
Subject to Constraints:
Sum of Allocations = 100%
Worst one year loss permitted:
35%
OUTPUTS:
Minimum 30-year return (based on all 30-year returns):
Expected 30-Year Return (based on all 30-year returns):
Worst one year loss:
100%
0%
0%
0%
0%
US Stocks
S&P 500
-Large Cap Large Cap Small Cap
Year
Blend
Growth
Growth
37.5%
44.2%
28.4%
1928
-10.8%
-10.1%
-35.5%
1929
-28.2%
-29.5%
-35.3%
1930
-43.6%
-37.2%
-45.8%
1931
-8.5%
-6.3%
-0.3%
1932
51.0%
47.1%
108.0%
1933
3.0%
10.8%
18.6%
1934
43.1%
39.7%
54.1%
1935
30.1%
24.5%
47.1%
1936
-33.4%
-34.1%
-44.2%
1937
26.4%
36.7%
37.3%
1938
3.0%
1.3%
3.7%
1939
-7.6%
-9.0%
-5.0%
1940
-10.4%
-13.4%
-8.1%
1941
14.9%
15.6%
25.2%
1942
25.4%
24.0%
52.9%
1943
18.8%
16.0%
40.0%
1944
34.8%
33.3%
59.2%
1945
-5.6%
-7.9%
-9.9%
1946
3.7%
1.2%
-2.3%
1947
2.4%
2.9%
-3.4%
1948
19.8%
21.2%
19.9%
1949
28.3%
20.3%
33.9%
1950
20.9%
19.8%
14.6%
1951
13.5%
12.5%
9.3%
1952
0.7%
3.5%
-2.2%
1953
48.8%
42.8%
53.0%
1954
25.5%
22.5%
19.4%
1955
7.9%
9.6%
6.4%
1956
-9.9%
-8.8%
-15.9%
1957
42.8%
42.0%
57.1%
1958
11.7%
9.2%
16.2%
1959
1.1%
3.6%
-2.7%
1960
26.3%
25.9%
27.4%
1961
-9.5%
-10.2%
-15.0%
1962
21.1%
20.0%
17.5%
1963
15.6%
14.4%
15.5%
1964
11.9%
12.7%
32.5%
1965
-9.3%
-9.9%
-6.1%
1966
23.5%
22.6%
58.9%
1967
11.1%
6.6%
29.6%
1968
-9.4%
-1.5%
-19.9%
1969
1.3%
-3.4%
-8.6%
1970
15.5%
23.2%
19.0%
1971
17.8%
16.7%
5.5%
1972
-16.9%
-25.2%
-31.0%
1973
-27.9%
-25.3%
-23.3%
1974
36.2%
34.7%
69.6%
1975
24.5%
15.9%
41.1%
1976
-5.7%
-6.8%
21.3%
1977
6.5%
9.3%
22.3%
1978
21.1%
17.9%
48.7%
1979
32.1%
38.9%
50.3%
1980
-4.1%
-7.9%
-1.3%
1981
20.3%
28.9%
27.6%
1982
21.6%
18.4%
28.7%
1983
5.7%
2.3%
-11.7%
1984
32.2%
38.2%
34.1%
1985
17.4%
14.7%
9.0%
1986
2.9%
1.4%
-8.0%
1987
17.2%
16.7%
24.2%
1988
30.2%
30.6%
20.1%
1989
-4.6%
-1.3%
-13.3%
1990
32.6%
47.7%
46.6%
1991
8.2%
5.5%
15.3%
1992
9.6%
-0.6%
11.5%
1993
0.1%
2.9%
-0.7%
1994
37.0%
39.7%
25.1%
1995
21.7%
27.3%
17.4%
1996
31.9%
31.1%
23.0%
1997
27.5%
34.1%
-1.2%
1998
21.7%
22.6%
28.8%
1999
-9.3%
-9.7%
2.4%
2000
-13.0%
-12.8%
9.9%
2001
-22.1%
-21.3%
-13.8%
2002
28.5%
21.8%
39.5%
2003
10.7%
8.4%
20.7%
2004
5.8%
2.2%
7.0%
2005
14.8%
10.9%
14.8%
2006
6.4%
10.3%
2.2%
2007
-36.7%
-30.0%
-36.9%
2008
27.2%
26.7%
31.5%
2009
15.5%
17.9%
29.1%
2010
1.1%
6.4%
1.8%
2011
15.9%
15.3%
15.6%
2012
31.9%
33.3%
41.8%
2013
Avg.
Max
Min
Stdev
0%
0%
International - Developed
Total
Market
Developed Total Market
0%
0%
International - Emerging
34.9%
-16.1%
-33.8%
-50.0%
-7.9%
83.5%
4.9%
46.5%
38.6%
-38.5%
29.4%
-1.8%
-6.3%
-7.6%
19.7%
36.4%
28.3%
47.2%
-7.0%
2.8%
-0.6%
18.7%
36.1%
18.0%
11.9%
-2.4%
55.0%
21.4%
8.4%
-13.4%
51.4%
12.6%
-0.4%
27.4%
-10.7%
20.3%
17.4%
21.8%
-8.0%
38.2%
23.4%
-18.2%
-0.7%
15.6%
10.7%
-22.6%
-25.4%
46.0%
38.5%
4.2%
11.7%
29.6%
30.7%
2.2%
23.8%
29.1%
2.8%
30.9%
14.2%
-0.8%
21.8%
24.0%
-12.5%
35.1%
15.4%
16.2%
-0.6%
34.4%
20.7%
30.2%
10.0%
19.8%
1.0%
-0.7%
-18.8%
39.5%
15.8%
7.4%
17.0%
1.0%
-38.5%
32.1%
21.3%
-2.5%
17.4%
37.1%
35.2%
-22.4%
-41.1%
-54.3%
-10.6%
127.0%
1.7%
52.2%
58.9%
-48.0%
33.6%
-7.6%
-6.1%
-5.1%
28.9%
56.8%
41.4%
63.7%
-11.9%
2.5%
-3.9%
18.3%
50.0%
12.3%
11.0%
-8.1%
62.1%
19.8%
5.4%
-18.7%
64.3%
16.8%
-7.7%
28.1%
-10.5%
24.3%
22.3%
36.5%
-9.6%
63.1%
42.6%
-25.8%
-3.4%
18.7%
6.8%
-28.4%
-21.5%
62.0%
53.9%
16.5%
18.4%
35.1%
24.6%
13.4%
31.5%
41.7%
-1.2%
27.9%
7.5%
-2.8%
29.9%
15.4%
-23.7%
44.3%
30.4%
22.7%
1.0%
30.6%
23.1%
32.9%
-3.2%
12.7%
19.5%
19.3%
-15.5%
60.7%
22.6%
7.6%
18.6%
-7.8%
-36.2%
46.0%
27.5%
-6.9%
19.2%
42.1%
9.2%
-9.9%
-20.4%
-34.8%
4.1%
77.0%
10.3%
6.4%
12.4%
-7.9%
-8.1%
-12.4%
7.4%
27.7%
1.8%
14.4%
-7.6%
4.5%
-23.3%
-4.5%
-8.3%
-6.7%
6.7%
11.3%
0.8%
12.3%
31.0%
8.0%
-1.0%
0.7%
21.7%
44.7%
13.5%
5.4%
-5.4%
7.4%
-1.8%
-3.3%
-9.7%
23.5%
24.4%
5.4%
-7.4%
31.8%
36.8%
-10.4%
-21.1%
33.8%
5.3%
20.0%
32.2%
9.8%
25.3%
-1.7%
-3.7%
24.6%
9.2%
53.7%
63.7%
25.6%
31.1%
18.6%
-21.0%
19.3%
-8.3%
39.3%
11.3%
4.9%
5.3%
-6.8%
14.0%
26.6%
-8.6%
-16.8%
-9.0%
51.6%
26.6%
18.5%
26.9%
9.2%
-45.1%
39.5%
14.1%
-15.2%
17.0%
22.6%
Small Cap
11.2%
-8.6%
-19.3%
-34.2%
5.8%
82.9%
13.0%
8.7%
14.9%
-6.5%
-6.7%
-11.4%
9.3%
30.6%
4.2%
17.2%
-5.4%
7.0%
-22.4%
-2.8%
-7.1%
-5.2%
8.9%
13.6%
2.7%
14.6%
33.4%
10.4%
1.1%
2.5%
24.0%
47.9%
16.1%
6.9%
-3.1%
9.7%
-0.2%
-1.2%
-8.1%
26.1%
27.5%
8.0%
-5.3%
35.4%
40.3%
-8.4%
-20.1%
36.7%
7.7%
23.0%
35.3%
12.9%
28.7%
-0.1%
-3.3%
35.7%
10.6%
72.0%
55.3%
52.4%
33.3%
36.8%
-17.9%
4.2%
-22.1%
44.2%
8.2%
3.0%
2.5%
-15.9%
8.6%
20.3%
-3.0%
-7.4%
2.8%
65.2%
33.8%
22.9%
26.3%
5.2%
-43.1%
46.4%
18.1%
-15.9%
17.1%
31.1%
Total Market
1.7%
-9.0%
-8.8%
-16.7%
-3.5%
85.0%
24.6%
11.2%
18.3%
-4.5%
-4.4%
-15.4%
-0.2%
19.7%
19.7%
24.8%
18.3%
20.1%
-12.3%
-1.4%
-14.3%
-3.5%
8.8%
10.6%
5.6%
7.3%
1.9%
13.4%
13.4%
2.3%
2.5%
17.8%
17.7%
-12.7%
22.3%
10.8%
-3.9%
11.9%
-1.0%
12.3%
28.5%
21.0%
16.1%
39.2%
31.7%
15.4%
-12.9%
13.8%
17.7%
26.0%
20.9%
38.5%
36.8%
-4.8%
-25.1%
23.3%
14.5%
26.4%
12.5%
24.1%
43.2%
69.3%
-10.4%
64.0%
12.7%
79.6%
-1.5%
-12.8%
8.1%
-22.9%
-22.1%
60.1%
-31.8%
1.3%
-3.8%
64.1%
31.6%
27.8%
34.8%
45.6%
-53.6%
89.6%
24.4%
-21.5%
20.0%
-3.5%
Value
6.3%
-8.9%
-12.0%
-23.3%
0.2%
99.0%
26.3%
13.6%
21.5%
-4.4%
-4.4%
-15.2%
4.1%
27.5%
19.4%
27.7%
15.3%
20.6%
-15.9%
-0.7%
-12.9%
-3.2%
11.6%
14.5%
7.0%
12.0%
13.3%
15.9%
13.1%
4.1%
10.9%
31.2%
21.4%
-7.3%
19.4%
13.5%
-2.0%
11.1%
-2.0%
19.9%
34.1%
21.7%
13.4%
45.6%
40.7%
11.9%
-15.8%
24.4%
18.7%
30.6%
30.0%
38.1%
41.5%
-2.7%
-20.8%
29.6%
17.3%
41.4%
32.6%
30.6%
48.8%
53.0%
1.0%
39.5%
-5.5%
105.4%
5.3%
-8.3%
12.7%
-21.9%
-19.1%
66.2%
-27.4%
3.9%
-0.1%
75.9%
35.5%
29.0%
39.2%
48.1%
-51.7%
101.0%
24.7%
-25.7%
20.3%
-4.6%
11.1%
47.7%
-37.2%
15.0%
108.0%
-45.8%
13.0%
83.5%
-50.0%
11.0%
51.0%
-43.6%
16.5%
127.0%
-54.3%
9.5%
77.0%
-45.1%
12.0%
82.9%
-43.1%
13.3%
89.6%
-53.6%
16.2%
105.4%
-51.7%
19.7%
27.2%
22.9%
19.6%
29.8%
20.9%
22.8%
25.5%
27.5%
Q8 A
Joe Cool just landed his first serious job at Cook, Books & Hyde, a
major accounting firm in his home town. The HR Department has
asked how much he wants to save each month into his retirement
account. He wonders how much will be in his account if he earns
10% and can save various amounts amounts over various time
periods. He wants to test monthly savings of $25, 50, 75, 100,
125, 150, 175, and $200 and asks you to compute much will be in
the retirement account after spans of 20, 25, 30, 35, and 40 years.
Q8B
Tom Swift is an investor but the stock market scares him. He just
learned he can invest some of the money he just inherited in
lottery winnings. Sometimes people who have won a lottery and
opted for a stream of payments change their minds and want to
sell the stream of income for a lump sum. These people can put
up their lottery winnings in an auction where investors can bid on
them. Tom is considering bidding on a stream of income that will
pay $10,000 a year for the next 20 years. He is considering bids
of $50,000, $55,000, $60,000, $65,000... $100,000. He asks you
to compute what return he would get with each bid if he won.
Help Tom out.
Question 4
The manager of a "6/10 Market" opens his store at 6AM, closes at 10PM. He wants at least 4 workers on duty during every
hour of the week, 6 on weekends. He is exploring the idea of using a "4-10 plan." That is, workers work 4 consecutive days, 10
consecutive hours each. Use Solver to solve this staff scheduling problem (see "Demand Matrix ") using the integer constraint.
How many workers will be needed? What do you conclude about the efficiency of the 4-10 plan vs. the 5-8 plan?
INPUTS:
DEMAND MATRIX A: Enter the number of workers needed each 2 hour span.
Shift
Shift
Sun
Mon
Tue
Wed
12AM-2AM
1
0
0
0
0
2AM-4AM
2
0
0
0
0
4AM-6AM
3
0
0
0
0
6AM-8AM
4
6
3
3
3
8AM-10AM
5
6
3
3
3
10AM-12PM
6
7
4
4
4
12PM-2PM
7
7
4
4
4
2PM-4PM
8
7
4
4
4
4PM-6PM
9
7
4
4
4
6PM-8PM
10
7
4
4
4
8PM-10PM
11
7
4
3
3
10PM-12AM
12
0
0
0
0
Thu
Fri
0
0
0
0
0
0
3
3
3
3
4
4
4
4
4
4
4
5
4
5
3
5
0
0
Total Workerhours:
Unadjusted Workers Required:
Sat
0
0
0
7
7
7
7
7
7
7
7
0
520
13
Get tutorials for this question at:
http://www.homeworkminutes.com/question/view/31049/BUS-204-Below-are-the-scores-achieved-during-the-first-half-of-Bus-204-in-a-prior-semester-and-the-final-overall-total
