31. An investor is developing a portfolio of stocks. She has identified 3 stocks to invest in. She wants to earn at least 5% return but with minimum risk. The problem data is given in the following Excel spreadsheet. What formula should be entered in cell G4 of the Excel spreadsheet? A B C D E > 1 > 2 Annual Return > 3 Year A B C > 4 1 3.98% 7.38% 8.76% > 5 2 -1.51% 10.45% 6.66% > 6 3 5.36% 5.15% 6.55% > 7 4 4.98% 5.51% -1.58% > 8 5 3.12% 6.93% 8.43% > 9 6 5.58% -4.32% 9.07% > 10 7 3.49% 9.78% 9.65% > 11 8 -2.37% 5.62% 8.96% > 12 9 5.92% 6.28% -7.10% > 13 10 5.69% 5.75% 10.96% > 14 > 15 Average 3.42% 5.85% 6.03% > < F G H I J < 1 < 2 Covariance Matrix < 3 A B C < 4 A 0.00080 -0.00044 -0.00045 < 5 B -0.00044 0.00144 -0.00012 < 6 C -0.00045 -0.00012 0.00300 < 7 < 8 A B C Total < 9 Portfolio 36.8% 40.6% 22.6% 100.0% < 10 < 11 Expected Return 5.00% < 12 Required Return 5.00% < 13 < 14 Portfolio Variance 0.000274 < 15 a. =COVAR(B4:B13) b. =VARP(B4:B13) c. =COVAR(B4:D13 $D$4:$B$13) d. =COVAR(B4:B13 $B$4:$B$13) 32. An investor is developing a portfolio of stocks. She has identified 3 stocks to invest in. She wants to earn at least 5% return but with minimum risk. The problem data is given in the following Excel spreadsheet. What formula should be entered in cell H11 of the Excel spreadsheet? A B C D E > 1 > 2 Annual Return > 3 Year A B C > 4 1 3.98% 7.38% 8.76% > 5 2 -1.51% 10.45% 6.66% > 6 3 5.36% 5.15% 6.55% > 7 4 4.98% 5.51% -1.58% > 8 5 3.12% 6.93% 8.43% > 9 6 5.58% -4.32% 9.07% > 10 7 3.49% 9.78% 9.65% > 11 8 -2.37% 5.62% 8.96% > 12 9 5.92% 6.28% -7.10% > 13 10 5.69% 5.75% 10.96% > 14 > 15 Average 3.42% 5.85% 6.03% > < F G H I J < 1 < 2 Covariance Matrix < 3 A B C < 4 A 0.00080 -0.00044 -0.00045 < 5 B -0.00044 0.00144 -0.00012 < 6 C -0.00045 -0.00012 0.00300 < 7 < 8 A B C Total < 9 Portfolio 36.8% 40.6% 22.6% 100.0% < 10 < 11 Expected Return 5.00% < 12 Required Return 5.00% < 13 < 14 Portfolio Variance 0.000274 < 15 a. =SUMPRODUCT(B15:D15 G9:I9) b. =SUMPRODUCT(B15:D15 B4:D13) c. =SUMPRODUCT(B15:D15 I9) d. =SUMPRODUCT(B15:D15 G4:I6) 33. A company wants to locate a new warehouse to minimize the distance traveled by its delivery trucks. It has four stores and their coordinates are listed in the accompanying spreadsheet. Which cell(s) in the spreadsheet represent the decision variables in the problem? A B C D 1 2 X-Coordinate Y-Coordinate 3 Warehouse 163.800 101.000 Distance: 4 Store 1 100 130 70.082 5 Store 2 90 60 84.424 6 Store 3 210 80 50.749 7 Store 4 180 110 18.532 8 9 Total Distance: 223.787 a. B3 b. B3:C3 c. B4:C7 d. D9 34. A company wants to locate a new warehouse to minimize the distance traveled by its delivery trucks. It has four stores and their coordinates are listed in the accompanying spreadsheet. What formula goes in cell D4 of the spreadsheet? A B C D 1 2 X-Coordinate Y-Coordinate 3 Warehouse 163.800 101.000 Distance: 4 Store 1 100 130 70.082 5 Store 2 90 60 84.424 6 Store 3 210 80 50.749 7 Store 4 180 110 18.532 8 9 Total Distance: 223.787 a. =(B4-$B$3)^2 (C4-$C$3)^2 b. =SQRT((B4-$B$3) (C4-$C$3)) c. =SQRT((B4-$B$3)^2 (C4-$C$3)^2) d. =(B4-$B$3) (C4-$C$3) 35. A company wants to locate a new warehouse to minimize the distance traveled by its delivery trucks. It has four stores and their coordinates are listed in the accompanying spreadsheet. What formula goes in cell D9 of the spreadsheet? A B C D 1 2 X-Coordinate Y-Coordinate 3 Warehouse 163.800 101.000 Distance: 4 Store 1 100 130 70.082 5 Store 2 90 60 84.424 6 Store 3 210 80 50.749 7 Store 4 180 110 18.532 8 9 Total Distance: 223.787 a. =MIN(D4:D7)^2 b. =SUM(D4:D7) c. =SQRT(D4^2 D5^2 D6^2 D7^2) d. =SQRT(MMULT(B3:C3 D4:D7)) 36. An office supply company is attempting to determine the order quantity for laser printer toner cartridges which are sold to local businesses. Annual demand is 20 000 units and each cartridge costs the store $25. It costs $30 to place an order and the inventory carrying cost rate is 25% of the value of the item. The following spreadsheet has been set up to solve the problem. What cell is the variable cell in this problem? A B 1 2 3 Annual Demand: 20 000 4 5 Cost per Unit: $25 6 Ordering Cost: $30 7 Carrying Cost: 25% 8 9 Order Quantity: 483.73 10 11 Total Cost: $502 738.61 a. B3 b. B7 c. B9 d. B11 37. An office supply company is attempting to determine the order quantity for laser printer toner cartridges which are sold to local businesses. Annual demand is 20 000 units and each cartridge costs the store $25. It costs $30 to place an order and the inventory carrying cost rate is 25% of the value of the item. The following spreadsheet has been set up to solve the problem. What cell is the objective cell in this problem? A B 1 2 3 Annual Demand: 20 000 4 5 Cost per Unit: $25 6 Ordering Cost: $30 7 Carrying Cost: 25% 8 9 Order Quantity: 483.73 10 11 Total Cost: $502 738.61 a. B3 b. B7 c. B9 d. B11 38. An office supply company is attempting to determine the order quantity for laser printer toner cartridges which are sold to local businesses. Annual demand is 20 000 units and each cartridge costs the store $25. It costs $30 to place an order and the inventory carrying cost rate is 25% of the value of the item. The following spreadsheet has been set up to solve the problem. What formula goes in cell B11 in this problem? A B 1 2 3 Annual Demand: 20 000 4 5 Cost per Unit: $25 6 Ordering Cost: $30 7 Carrying Cost: 25% 8 9 Order Quantity: 483.73 10 11 Total Cost: $502 738.61 a. =B3*B7 B9/B3*B6 B9*2 B5*B7 b. =B3*B5 B3/B9*B6 B9/2*B5*B7 c. =SQRT(B3*B5 B3/B9*B6 B9/2*B5*B7) d. =B3*B3 B3/B9 B6*B9/2*B5*B7 39. An office supply company is attempting to determine the order quantity for laser printer toner cartridges which are sold to local businesses. Annual demand is 20 000 units and each cartridge costs the store $25. It costs $30 to place an order and the inventory carrying cost rate is 25% of the value of the item. The following spreadsheet has been set up to solve the problem. What constraint would you impose on this problem to ensure that at least one order is placed per year? A B 1 2 3 Annual Demand: 20 000 4 5 Cost per Unit: $25 6 Ordering Cost: $30 7 Carrying Cost: 25% 8 9 Order Quantity: 483.73 10 11 Total Cost: $502 738.61 a. MAX B9 b. B9 = 1 c. B9³ 1 d. MIN B11 40. An investor wants to determine how much interest he must earn to be able to make the payments on a 10-year mortgage which has increasing annual payments. The problem is summarized in the accompanying spreadsheet. The investor has enough money to make an initial investment of $9 000 and hopes he can earn 12% compounded quarterly on his investments. He would like to know how low his annual return can be and still allow him to make his payments from interest income. What formula goes in cell C7 of the spreadsheet? A B C D E F 1 2 Amount Invested: $9 000 3 Annual Return: 10% 4 5 Beginning Investment Earnings Premium Ending 6 Year Balance Earnings After Taxes Due Balance 7 1 $9 000 $971 $699 $500 $9 199 8 2 $9 199 $993 $715 $534 $9 380 9 3 $9 380 $1 012 $729 $566 $9 543 10 4 $9 543 $1 030 $741 $593 $9 691 11 5 $9 691 $1 046 $753 $607 $9 837 12 6 $9 837 $1 061 $764 $635 $9 966 13 7 $9 966 $1 075 $774 $672 $10 068 14 8 $10 068 $1 086 $782 $695 $10 155 15 9 $10 155 $1 096 $789 $737 $10 207 16 10 $10 207 $1 101 $793 $793 $10 207 a. =B7*(1 $C$3/4)^4-B7 b. =B7*(1 $C$3)-B7 c. =B7*($C$3/4)^4-B7 d. =B7*(1 $C$3/4)^4 41. An investor wants to determine how much interest he must earn to be able to make the payments on a 10-year mortgage which has increasing annual payments. The problem is summarized in the accompanying spreadsheet. The investor has enough money to make an initial investment of $9 000 and hopes he can earn 12% compounded quarterly on his investments. He would like to know how low his annual return can be and still allow him to make his payments from interest income. What constraint must be entered in the Risk Solver Platform (RSP) task pane? A B C D E F 1 2 Amount Invested: $9 000 3 Annual Return: 10% 4 5 Beginning Investment Earnings Premium Ending 6 Year Balance Earnings After Taxes Due Balance 7 1 $9 000 $971 $699 $500 $9 199 8 2 $9 199 $993 $715 $534 $9 380 9 3 $9 380 $1 012 $729 $566 $9 543 10 4 $9 543 $1 030 $741 $593 $9 691 11 5 $9 691 $1 046 $753 $607 $9 837 12 6 $9 837 $1 061 $764 $635 $9 966 13 7 $9 966 $1 075 $774 $672 $10 068 14 8 $10 068 $1 086 $782 $695 $10 155 15 9 $10 155 $1 096 $789 $737 $10 207 16 10 $10 207 $1 101 $793 $793 $10 207 a. $D$7:$D$16£ $E$7:$E$16 b. $C$7:$D$16³ $E$7:$E$16 c. $C$7:$C$16³ $E$7:$E$16 d. $D$7:$D$16³ $E$7:$E$16 42. How much must the objective function coefficient of the variable Pumpkin change before any Pumpkins are produced based on the following sensitivity report? Changing Cells Final Reduced Cell Name Value Gradient $B$4 Corn 9.52 0 $C$4 Pumpkin 0 -499.99 $D$4 Beans 10.79 0 Constraints Final Lagrange Cell Name Value Multiplier $E$8 Corn 200000 0.016 $E$9 Pumpkin 99 12 $E$10 Beans 37777.78 0 $E$11 Water 29.84 0 $E$12 Fertilizer 8000 3.49 a. increase by 12 b. decrease by 12 c. increase by 499.99 d. decrease by 499.99 43. How much are additional units of Labor worth based on the following sensitivity report? Changing Cells Final Reduced Cell Name Value Gradient $B$4 Number to make: X1 9.42 0 $C$4 Number to make: X2 1.71 0 Constraints Final Lagrange Cell Name Value Multiplier $D$8 Wood 42 0 $D$9 Labor 132 1.21 $D$10 Plywood 24 2.57 a. 0 b. 1.21 c. 2.57 d. 9.42
