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 Hartmann’s 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 ∑ (LMP_i* Net Sale_i)

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

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 10^th 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

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 10^th period, from 0 to 60 kWh by 10 kWh, we get,

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

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,

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,

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

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