Level Of Activity Profit
2 16 Pr
4 24 35
5 28
f(x) = − 0.300242130750605 x² + 5.66
7 30 30 R² = 0.986211998923864 28
10 33 24
25
20
16
Profit
15
10
0
1 2 3 4 5
Le
Profit
33
30
= − 0.300242130750605 x² + 5.66101694915254 x + 6.14769975786925
= 0.986211998923864 28
24
3 4 5 6 7 8 9 10 11
Level Of Activity
Doors Windows
Unit Profit 375 700
Hours Used Per Unit Produced Hours Used Hours Available
Plant 1 1 0 3.214 <= 4
Plant 2 0 2 8.357 <= 12
Plant 3 3 2 18 <= 18
Units Produced 3.214 4.178
Gross Profit From Sales
Marketing Cost 258 1164 Total Marketing Cost
25*X1^2 66.67*X2^2 Total Profit
4130
1422
2708
Stock Stock 1 Stock 2 Stock 3
Risk Standard Deviation 25% 45% 5%
Expected Return 21% 30% 8%
Joint Risk Stock 1 Stock 2 Stock 3
Stock 1 0.040 -0.005
Stock 2 -0.010
Stock 3
Stock 1 Stock 2 Stock 3 Total
No. Of Stock 40.2% 21.7% 38.1% 100% =
Portfolio Minimum expected return
Expected Return 18% >= 18%
Risk(variance) 0.02379223
Risk Standard Deviation 15.4%
100%
Oneway analysis for Solver model in Portfolio selection worksheet
ER (cell $D$13) values along side, output cell(s) along top
Risk(stand. Devi)
Expected Return
Risk(vari)
Stock 1
Stock 2
Stock 3
#N/A
8% 7.1% 3.7% 89.1% 0.0015 9.7% 3.9%
10% 8.1% 4.3% 87.6% 0.0015 10.0% 3.9%
12% 16.2% 8.6% 75.2% 0.0032 12.0% 5.6%
14% 24.2% 13.0% 62.8% 0.0074 14.0% 8.6%
16% 32.2% 17.3% 50.5% 0.0143 16.0% 12.0%
18% 40.2% 21.7% 38.1% 0.0238 18.0% 15.4%
20% 48.2% 26.1% 25.7% 0.0359 20.0% 18.9%
22% 56.2% 30.4% 13.4% 0.0506 22.0% 22.5%
24% 64.2% 34.8% 1.0% 0.0680 24.0% 26.1%
26% 44.4% 55.6% 0.0% 0.0946 26.0% 30.8%
28% 22.2% 77.8% 0.0% 0.1394 28.0% 37.3%
30% 0.0% 0.0% 0.0% 0.0000 0.0% 0.0%
Sensitivity of $B$11 to ER
Data for chart
When you select an output from the
dropdown list in cell $K$4, the chart will
adapt to that output.
$B$11
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A Sensitivity of $B$11 to ER
#N/A
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
8% 10% 12% 14% 16% 18% 20% 22% 24% 26% 28% 30%
ER ($D$13)
Unit Profit Doors Windows
Regular Time 375 700
Overtime 275 300
Hours Used Per Unit Produced Hours Used Hours Available
Plant 1 1 0 4 <= 4
Plant 2 0 2 6 <= 12
Plant 3 3 2 18 <= 18
Maximum
Units Produced Doors Windows Doors Windows
Regular Time 3 3 <= 3 3
Overtime 1 0 <= 1 3
Total Produced 4 3
Gross Profit from sales
Marketing Cost 400 600 Total Marketing Cost
25*totalproduced^2 66.67*totalproduced^2 Total Profit
3500
1000
2500
Unit Profit Power saws Power drills
Regular Time 150 100
Overtime 50 75
Units Produced Total Used
Power Supplies 1 1 10000 <=
Gear Assemblies 2 1 13000 <=
Maximum
Units Produced Power saws Power drills Power saws
Regular Time 3000 5000 <= 3000
Overtime 0 2000 <= 2000
Total Produced 3000 7000
Total Profit
Available
10000
15000
Maximum
Power drills
5000
3000
Total Profit 1100000
Sales Profit
0 0
Profit
100000 15
200000 18 80
300000 13 70
400000 4 f(x) = 9.58754208754208E-16 x³ − 9.59487734487734
R² = 0.998444307214965
500000 1 60
600000 6
50
700000 30
800000 70 40
Profit
30
18
20 15
13
10 4
0
0
0 100000 200000 300000 400000
Sal
Profit
70
8754208E-16 x³ − 9.59487734487734E-10 x² + 0.000242421837421838 x − 0.10101010101018
7214965
30
18
13
6
4
1
200000 300000 400000 500000 600000 700000 800000 900000
Sales
