Decision Analysis Company Pfeiffer Manages

Question: Discuss about the Decision Analysis for Company Pfeiffer Manages. Answer: Given, the company Pfeiffer manages approximately $15 M for clients. The client can choose between growth stock fund, income fund and money market fund or a mix of the three investments. Each client has an investment objective and tolerance for risk. The company invests in each of the three funds based on the portfolio risk index. The yield of growth stock fund, income fund and money market fund are 20%, 10% and 6% respectively. For client Hartmann, portfolio risk index = 0.05 and minimum investment in growth stock fund, income fund and money market fund must be 10% , 10% and 20% respectively. The total amount available for investment = 300000. Let X1, X2 and X3 be the investment in growth stock fund, income fund and money market fund respectively. Objective: Maximize yield: 20% * X1 + 10% * X2 + 6% * X3 s.t. Fund Constraint: X1 + X2 + X3 = 300000 Risk Constraint: 0.1* X1 + 0.05* X2 + 0.01* X3 = 0.05 Growth stock fund constraint: X1/ (X1 + X2 + X3) = 10 % , Income fund and constraint: X2/ (X1 + X2 + X3) = 10 % , Money market fund X3/ (X1 + X2 + X3) = 20 % , X1, X2 and X3 = 0 (Vanderbei, 2005) Solving the linear program developed in excel we get, X1 = 120000 X2 = 30000 X3 = 150000 Thus objective function = 20%* 120000 + 10%* 30000 + 6%* 150000 = 36000 Mr. Hartmann can earn 36000 with his investment of 300000 if the company uses linear program and optimally allocates the fund. To find the range of yield, the company will not modify portfolio. Using the sensitivity report generated in excel, we get, Allowance increase for growth fund = 0.4, Allowance decrease for growth fund = 0.05, Thus range of Yield of growth fund = (0.2 0.05 = 0.15) to (0.2 + 0.4 = 0.6) Similarly, Allowance increase for income fund = 0.022, Allowance decrease for income fund = e^30, Thus range of Yield of income fund = 0 to 0.122 Allowance increase for monetary fund = 0.14, Allowance decrease for monetary fund = 0.04 Thus range of Yield of monetary fund = 0.02 to 0.2 In this range of yield, the company will not modify portfolio To find if Hartmanns portfolio risk increases to 0.06 from 0.05, what will be the increase in the yield. We can use shadow price of portfolio risk. Shadow price of a constraint is defined as the increase in objective function for unit increase in constraint limit. Using the sensitivity report generated in excel, we get, shadow price of portfolio Risk = 466666.67. If the risk of the portfolio increases to 0.06, the additional benefit to the objective function = 0.0 1* 466666.67 = 4666.67 If the growth fund yield decreases to 10%, the objective function will change to New Objective function : Maximize yield: 10% * X1 + 10% * X2 + 6% * X3 Fund Constraint: X1 + X2 + X3 = 300000 Risk Constraint: 0.1* X1 + 0.05* X2 + 0.01* X3 = 0.05 Growth stock fund constraint: X1/ (X1 + X2 + X3) = 10 % , Income fund and constraint: X2/ (X1 + X2 + X3) = 10 % , Money market fund X3/ (X1 + X2 + X3) = 20 % , X1, X2 and X3 = 0 (Vanderbei, 2005) Thus solving the above linear program in excel, we get X1 = 48000 X2 = 192000 X3 = 60000 Thus objective function = 10% * 48000+ 10% * 192000 + 6% * 60000 = 27600 To manage the portfolio of each client the company must have the information about the risk index of each client and the amount they have. Using these two input parameters and the yield of the three funds, the portfolio can be designed. The company can use linear programming to determine the modification each week for all the clients. The company can take weighted average of the total portfolio risk for the 50 clients and based on the risk obtained, allocate funds as solved by the linear programming. After the yield is obtained, the company can provide return to each of the clients based on the amount they invested and their portfolio risk. 2: Given, the company can produces Product 1 and product 2. The profit of the Product 1 and product 2 are 30 and 15 respectively. The products have to pass through three departments A. B, C and the total labor hours available for each department is fixed. Product 1 takes 1, 0.3, 0.2 labor hours in the departments A, B, and C whereas Product 2 takes 0.35, 0.2, 0.5 labor hours the departments A, B, and C. The company wants to find the amount of Product 1 and product 2 that has to be produced to maximize its profit Let the quantity of Product 1 and product 2 be X1 and X2 respectively. Thus Objective function: Max 30 X1 + 15 X2 s.t. Labor Constraint: Department A: X1 + 0.35 X2 = 100 Department B: 0.3 X1 + 0.2 X2 = 36 Department C: 0.2 X1 + 0.5 X2 = 50 X1, X2 = 0 and are integers (Solow, 2014) Thus solving the linear program in excel, we get, X1 = 78, X2 = 62. Thus, No. of units of Product 1 to be produced = 78, No. of units of Product 2 to be produced = 62 Objective function = 30 * 78 + 15 * 62 = 3270 The company wants to find out which department should be scheduled for overtime. This can be found using the shadow price of labor hours in each department. Using the sensitivity report generated in excel, we get, shadow price of Department A = 15, shadow price of Department B = 47 and shadow price of Department C = 0. Thus increasing labor hours of department B by 1 unit will increase objective by 47 while increasing labor hours of department A will increase objective by 15. So we should increase the labor hours of department B. The price the company should be willing to pay for the overtime should be less than $47. Given overtime cost of the three departments A, B and C is 18, 22.5 and 12 respectively. The maximum overtime that can be scheduled in the three departments A, B and C is 10, 8, 6 hours respectively. Let Y1, Y2 and Y3 be the overtime labor hours of department A, B and C respectively Thus the objective function will be Max 30X1 + 15 X2 18 * Y1 - 22.5 * Y2 12 * Y3 Labor Constraint: Department A: X1 + 0.35 X2 = 100 + Y1 Department B: 0.3 X1 + 0.2 X2 = 36 + Y2 Department C: 0.2 X1 + 0.5 X2 = 50 + Y3 Overtime constraint Y1 = 10 Y2 = 6 Y3 = 8 X1, X2 = 0 and are integers (Solow, 2014) Solving it we get X1 = 87, X2 = 65. Y1 = 10 Y2 = 4 Y3 = 0 Thus, No. of units of Product 1 to be produced = 87, No. of units of Product 2 to be produced = 65. Overtime hours in department A = 10 Overtime hours in department B = 4 Overtime hours in department C = 0 Thus Objective function = 30 * 87 + 15 * 65 -10 * 18 - 22.5 * 4 0 = 3315 Increase in profit due to overtime = 3315 3270 = 45 3. Given, the two river oil company supplies oil to southern Ohio and has a budget of $600000. The company can purchase super tanker, regular line and econo tanker. Each of the three tankers has different capacity and cost. The monthly operating cost of Super tanker, regular line and econo tanker are 550, 425 and 350 respectively. The company wants to optimize the monthly operating cost. Let X1, X2 and X3 be the number of super tanker, regular line and econo tanker respectively. The objective function is to Minimize 550 * X1 + 425 * X2 + 350 * X3 s.t. Fund Constraint: 67000 * X1 + 55000 * X2 + 46000 * X3 = 600000 Demand Constraint: 5000 * 15 * X1 + 2500 * 20 * X2 + 1000 * 25 * X3 = 550000 Driver constraint: (X1 + X2 + X3) = 15 Econo constraint: X3 = 3 Super tanker constraint: X1 = 0.5 * (X1 + X2 + X3) X1, X2 and X3 = 0 and are integers (Ferguson, 2011) Thus solving using excel we get, X1 = 5 X2 = 2 X3 = 3 The company will purchase 5 Super tanker, 2 regular line and 3 econo tanker. Thus Objective function = 550 * 5 + 425 * 2 + 350 * 3 = 4650 If the Econo constraint and Super tanker constraint are removed, we get, The objective function is to Minimize 550 * X1 + 425 * X2 + 350 * X3 s.t. Fund Constraint: 67000 * X1 + 55000 * X2 + 46000 * X3 = 600000 Demand Constraint: 5000 * 15 * X1 + 2500 * 20 * X2 + 1000 * 25 * X3 = 550000 Driver constraint: (X1 + X2 + X3) = 15 X1, X2 and X3 = 0 and are integers (Ferguson, 2011) Thus, solving the linear program using excel we get, X1 = 6 X2 = 2 X3 = 0 The company will purchase 6 Super tanker, 2 regular line and no econo tanker. Thus Objective function = 550 * 6 + 425 * 2 + 350 * 0 = 4150 4. Given, Star Power company has 60kWh battery. The company can buy and sell power and the prices are forecasted for the next 10 periods. The company can buy or sell 20kWh per period. To find the period when the company should sell and when it should purchase so that the profit is maximized. The company can buy or sell 20kWh per period. Let 1 charge unit be defined as 20kWh. Thus the company has 3 charge units as the battery is fully charged. (1 unit = 20 kWh). The locational marginal prices per kWh are given. Thus LMP per unit can be obtained by multiplying with 20. Let S1, S2., S10 be the units sold in period 1, 2, ..10 and P1, P2., P10 be the units purchased in period 1, 2, .., 10. Thus net sale in a period i = Si Pi. Charge left in battery at the end of period i = Charge left in period (i-1) Net Sales in period i Charge left in battery at the end of period 1 = 3 units - Net Sales in period 1 Objective: Max (LMPi* Net Salei) Constraints: Charge left in battery at the end of period i = 0 Charge left in battery at the end of period i = 3 Where Si and Pi can take values 0 or 1 for i = 1, 2, .., 10 Thus solving the linear program using excel, we get Time period Sell Buy Net Sales 1 0 0 0 2 1 0 1 3 0 1 -1 4 1 0 1 5 0 1 -1 6 1 0 1 7 1 0 1 8 0 1 -1 9 1 0 1 10 1 0 1 Thus in the period 2, 4, 6, 7, 9, 10 the company should sell the power and in the period 3, 5, 8 it should buy the power. Thus Profit earned by the company = (27 + 25 + 29 + 24 + 61 + 66)* 20 (2 + 22 + 20)* 20 = 3760. If the battery should have full power at the end of the 10th time period, then constraint Charge left in battery at the end of period 10 = 3 Charge left in battery at the end of period 10 = 0 will change to Charge left in period 10 = 3 Solving the linear program using excel, we get Time Period Sell Buy Net 1 0 0 0 2 1 0 1 3 0 1 -1 4 1 0 1 5 0 1 -1 6 1 0 1 7 0 0 0 8 0 1 -1 9 0 0 0 10 0 0 0 Thus in the period 2, 4, 6, the company should sell the power and in the period 3, 5, 8 it should buy the power. Thus Profit earned by the company = (27 + 25 + 29)* 20 (2 + 22 + 20)* 20 = 740 Varying the charge left in the battery at the end of the 10th period, from 0 to 60 kWh by 10 kWh, we get, Charge left at the end Profit 0 3760 10 3520 20 3280 30 2670 40 2060 50 1400 60 740 The graph of profit is given below The company should have 20 kWh at the end of the time period as the rate of increase in profit is slower at the 0 and 10kWh. Thus the ending battery level of 20 will allow the company to sell power if the price is too high and buy if the price is too low. 5. Given, Tri County utilities supplies natural gas from Sothern gas and North west gas to Hamilton, Butler and Clermont. The company wants to minimize the total distribution cost it incurs. The network diagram is given below The linear program model is given below Let X1s, X2s, X3s are the units of gasoline transferred from Southern Gas to Hamilton, Butler and Clermont respectively and X1n, X2n, X3n are the units of gasoline transferred from Southern Gas to Hamilton, Butler and Clermont respectively. Objective: Minimize 10 * X1s + 20 * X2s + 15 * X3s + 12 * X1n + 15 * X2n+ 18 * X3n Demand Constraint: Hamilton: X1s + X1n = 400 Butler: X2s + X2n = 200 Clermont: X3s + X3n = 300 Supply Constraint Southern Gas: X1s + X2s + X3s = 500 Northwest Gas: X1n + X2n + X3n = 400 Solving the linear program in excel we get, X1s = 200 units (From southern gas to Hamilton) X2s = 0 units (From southern gas to Butler) X3s = 300 units (From southern gas to Clermont) X1n = 200 units (From southern gas to Hamilton) X2n = 200 units (From southern gas to Butler) X3n = 0 units (From southern gas to Clermont) Thus total cost = 11900 From the sensitivity report obtained from excel, we get, the shadow price of Southern gas = 15 while the shadow price of Northwest gas = 17. Thus the company should use southern gas for additional capacity to reduce the total cost 6. Given, Aggie Power generation has three power generation plants and can supplies to the 10 cities. The demand of each city is given and the distribution cost from the plant to the city is given. The company wants to minimize the distribution cost. Let X1L, X2L,., X10L be the units supplied from Los Angeles to the Seattle, Portland . and Durango respectively. X1T, X2T,., X10T be the units supplied from Los Angeles to the Seattle, Portland . and Durango respectively. And X1S, X2S,., X10S be the units supplied from Los Angeles to the Seattle, Portland . and Durango respectively. Objective Min 356.25 * X1L + 356.25 * X2L +. + 356.25 * X10L + 593.75 * X1T + 593.75 * X2T + . + 593.75 * X10T + 950 * X1T + 831.25 * X2T + . + 1543.75 * X10T Constraint: Seattle: X1L + X1T + X1S = 950 Portland: X2L + X2T + X2S = 831.25 San Francisco: X3L + X3T + X3S = 2375 Boise: X4L + X4T + X4S = 593.75 Reno: X5L + X5T + X5S = 950 Bozeman: X6L + X6T + X6S = 593.75 Laramie: X7L + X7T + X7S = 1187.50 Park City: X8L + X8T + X8S = 712.50 Flagstaff: X9L + X9T + X9S = 1187.50 Durango: X10L + X10T + X10S = 1543.75 X1L, X2L,., X10L, X1T, X2T,., X10T, X1S, X2S,., X10S = 0 (Pollington) Solving the linear program in excel, we get, City Los Angeles Tulsa Seattle Seattle 0 0 950 Portland 0 0 831.25 San Francisco 2375 0 0 Boise 0 0 593.75 Reno 950 0 0 Bozeman 0 0 593.75 Laramie 1187.5 0 0 Park City 712.5 0 0 Flagstaff 1187.5 0 0 Durango 0 1543.75 0 Thus Los Angeles will supply to San Francisco, Reno, Laramie, Park City and Flagstaff Tulsa will supply to Durango and Seattle will supply to Seattle, Portland, Boise and Bozeman. Total cost = $ 2552423.19 B Additional Constraints: Los Angeles: X1L + X2L + .+ X10L = 4000 Tulsa: X1T + X2T + .+ X10T = 4000 Seattle: X1S + X2S + .+ X10S = 4000 (Pollington) Thus solving the linear program in excel, we get, City Los Angeles Tulsa Seattle Seattle 0 0 950 Portland 0 0 831.25 San Francisco 2375 0 0 Boise 0 0 593.75 Reno 437.5 0 512.5 Bozeman 0 0 593.75 Laramie 0 668.75 518.75 Park City 0 712.5 0 Flagstaff 1187.5 0 0 Durango 0 1543.75 0 Thus Los Angeles will supply to San Francisco, Reno, and Flagstaff Tulsa will supply to Laramie, Park City and Durango and Seattle will supply to Seattle, Portland, Boise and Bozeman. Total cost = $ 2652992.24 Increased cost = 2652992 - 2552423.19 = 100569.75 References Vanderbei, R. (2005). Linear Programming. Publication: Springer. Solow,D. (2014). Linear Programming: An Introduction to Finite Improvement Algorithms. Publication: Dover. Ferguson, T. (2011). Linear Programming. Retrieved on Aug 13, 2016 from https://www.math.ucla.edu/~tom/LP.pdf Excel Easy. (2010). Solver. Retrieved on Aug 13, 2016 from https://www.excel-easy.com/data-analysis/solver.html Pollington, B.(n.d.). Using Excel to solve linear programming problems. Retrieved on Aug 13, 2016 from https://www.msubillings.edu/asc/resources/math/tutorials/finitemathhelps/Lin%20Prog%20with%20Excel.pdf