PowerPoint Presentation

Session 6a

Decision Models -- Prof. Juran1Decision Models -- Prof. Juran2OverviewMultiple Objective OptimizationTwo DimensionsMore than Two DimensionsFinance and HR ExamplesEfficient FrontierPre-emptive Goal ProgrammingIntro to Decision AnalysisDecision Models -- Prof. Juran2Decision Models -- Prof. Juran3Scenario Approach Revisited

Use the scenario approach to determine the minimum-risk portfolio of these stocks that yields an expected return of at least 22%, without shorting.Decision Models -- Prof. Juran3Decision Models -- Prof. Juran4Using the same notation as in the GMS case, the percent return on the portfolio is represented by the random variable R.

In this model, xi is the proportion of the portfolio (i.e. a number between zero and one) allocated to investment i. (In the GMS case, we used thousands of dollars as the units.)

Each investment i has a percent return under each scenario j, which we represent with the symbol rij.

Decision Models -- Prof. Juran4Decision Models -- Prof. Juran5

Decision Models -- Prof. Juran5Decision Models -- Prof. Juran6The portfolio return under any scenario j is given by:

Decision Models -- Prof. Juran6Decision Models -- Prof. Juran7Let Pj represent the probability of scenario j occurring.

The expected value of R is given by:

The standard deviation of R is given by:Decision Models -- Prof. Juran7Decision Models -- Prof. Juran8In this model, each scenario is considered to have an equal probability of occurring, so we can simplify the two expressions:

Decision Models -- Prof. Juran8Decision Models -- Prof. Juran9Decision VariablesWe need to determine the proportion of our portfolio to invest in each of the five stocks.

ObjectiveMinimize risk.

ConstraintsAll of the money must be invested.(1)The expected return must be at least 22%.(2)No shorting.(3)

Managerial FormulationDecision Models -- Prof. Juran9Decision Models -- Prof. Juran10Mathematical FormulationDecision Variablesx1, x2, x3, x4, and x5 (corresponding to Ford, Lilly, Kellogg, Merck, and HP).ObjectiveMinimize Z = Constraints(1)

(2)

For all i, xi 0(3)

Decision Models -- Prof. Juran10Decision Models -- Prof. Juran11

Decision Models -- Prof. Juran11Decision Models -- Prof. Juran12The decision variables are in F2:J2.The objective function is in C3. Cell E2 keeps track of constraint (1).Cells C2 and C5 keep track of constraint (2).Constraint (3) can be handled by checking the assume non-negative box in the Solver Options.

Decision Models -- Prof. Juran12Decision Models -- Prof. Juran13

Decision Models -- Prof. Juran13Decision Models -- Prof. Juran14

Decision Models -- Prof. Juran14Decision Models -- Prof. Juran15Invest 17.3% in Ford, 42.6% in Lilly, 5.4% in Kellogg, 10.5% in Merck, and 24.1% in HP.

The expected return will be 22%, and the standard deviation will be 12.8%.

ConclusionsDecision Models -- Prof. Juran15Decision Models -- Prof. Juran162. Show how the optimal portfolio changes as the required return varies.

Decision Models -- Prof. Juran16Decision Models -- Prof. Juran17

Decision Models -- Prof. Juran17Decision Models -- Prof. Juran18

Decision Models -- Prof. Juran18Decision Models -- Prof. Juran193. Draw the efficient frontier for portfolios composed of these five stocks. Decision Models -- Prof. Juran19Decision Models -- Prof. Juran20

Decision Models -- Prof. Juran20Decision Models -- Prof. Juran21Repeat Part 2 with shorting allowed.

Decision Models -- Prof. Juran21Decision Models -- Prof. Juran22

Decision Models -- Prof. Juran22Decision Models -- Prof. Juran23

Decision Models -- Prof. Juran23Decision Models -- Prof. Juran24GMS Case RevisitedAssuming that Torelli's goal is to minimize the standard deviation of the portfolio return, what is the optimal portfolio that invests all $10 million? Decision Models -- Prof. Juran24Decision Models -- Prof. Juran25Formulation

Decision Models -- Prof. Juran25Decision Models -- Prof. Juran26Optimal Solution

Decision Models -- Prof. Juran26Decision Models -- Prof. Juran27Efficient Frontier for GMS

Decision Models -- Prof. Juran27Decision Models -- Prof. Juran28Parametric Approach Revisited(a) Determine the minimum-variance portfolio that attains an expected annual return of at least 0.12, with no shorting of stocks allowed.

(b) Draw the efficient frontier for portfolios composed of these three stocks.

(c) Determine the minimum-variance portfolio that attains an expected annual return of at least 0.12, with no shorting of stocks allowed.

From Session 5a:Decision Models -- Prof. Juran28Decision Models -- Prof. Juran29Formulation

Decision Models -- Prof. Juran29Decision Models -- Prof. Juran30Optimal Solution

Decision Models -- Prof. Juran30Decision Models -- Prof. Juran31SolverTable

Decision Models -- Prof. Juran31Decision Models -- Prof. Juran32

Decision Models -- Prof. Juran32Decision Models -- Prof. Juran33SolverTable Output

Decision Models -- Prof. Juran33Decision Models -- Prof. Juran34

Decision Models -- Prof. Juran34Decision Models -- Prof. Juran35Parametric Approach, cont.(c) Determine the minimum-variance portfolio that attains an expected annual return of at least 0.12, with shorting of stocks allowed.

All we need to do here is remove the non-negativity constraint and re-run SolverTable.

Decision Models -- Prof. Juran35Decision Models -- Prof. Juran36

Decision Models -- Prof. Juran36Decision Models -- Prof. Juran37Preemptive Goal Programming:Consulting ExampleThe Touche Young accounting firm must complete three jobs during the next month. Job 1 will require 500 hours of work, job 2 will require 300 hours, and job 3 will require 100 hours. At present the firm consists of five partners, five senior employees, and five junior employees, each of whom can work up to 40 hours per month. Decision Models -- Prof. Juran37Decision Models -- Prof. Juran38The dollar amount (per hour) that the company can bill depends on the type of accountant assigned to each job, as shown in the table below. (The "X" indicates that a junior employee does not have enough experience to work on job 1.)

Decision Models -- Prof. Juran38Decision Models -- Prof. Juran39All jobs must be completed. Touche Young has also set the following goals, listed in order of priority:Goal 1: Monthly billings should exceed $74,000.Goal 2: At most one partner should be hired.Goal 3: At most three senior employees should be hired.Goal 4: At most one junior employee should be hired.Decision Models -- Prof. Juran39Decision Models -- Prof. Juran40Decision VariablesThere are three types of decisions here. First, we need to decide how many people to hire in each of the three employment categories. Second, we need to assign the available human resources (which depend on the first set of decisions) to the three jobs. Finally, since it is not apparent that we will be able to satisfy all of Touche Youngs goals, we need to decide which goals not to meet and by how much.

Managerial FormulationDecision Models -- Prof. Juran40Decision Models -- Prof. Juran41ObjectiveIn the long run we want to minimize any negative difference between actual results and each of the four goals. Of course, our optimization methods require that we only have one objective at a time, so we will use a variation of goal programming to solve the problem four times.The approach here will be to treat each of the goals as an objective until it is shown to be attainable, after which we will treat it as a constraint. For example, we will solve the model with the goal of minimizing any shortfall in the $74,000 revenue target. Once we find a solution that has no shortfall, we will solve the problem again, with an added constraint that the shortfall be zero.Decision Models -- Prof. Juran41Decision Models -- Prof. Juran42ConstraintsThe numbers of people hired must be integers.(1)Each project must receive its required number of man-hours. (2)Our model must take into account any difference between the actual performance of the plan and the four targets.(3)We cant assign people to jobs unless we hire them.(4)

Decision Models -- Prof. Juran42Decision Models -- Prof. Juran43

Mathematical FormulationDecision Models -- Prof. Juran43Decision Models -- Prof. Juran44Decision Variablesxi (three decisions), Aij (nine decisions), k (up to three decisions)ObjectiveMinimize Z = ConstraintsAll xi are integers.(1)Aij = Rij for all i, j.(2) Goals k = vk for all goals < k(3)

for all i.(4)

Decision Models -- Prof. Juran44Decision Models -- Prof. Juran45

Decision Models -- Prof. Juran45Decision Models -- Prof. Juran46The tk targets are in cells G10:G13.The k negative difference variables will be in B16:B19. We use the range C10:D13 to track all deviations (both positive and negative), and then refer to the undesirable one in B16:B19. For example, it is undesirable to have billings under 74,000, so B16 refers to C10. It is undesirable for the number of new partners to be over 1, so B17 refers to D11.The vk best achieved variables will be in D16:D19.The xi are in K7:K9. The Aij assignments are in B2:D4. The Rij requirements are in B7:D7. Cells G2:G4 keep track of constraint (4).We constrain B4 to be zero.

Decision Models -- Prof. Juran46Decision Models -- Prof. Juran47First Iteration At first well ignore all of the goals except the billing target of $74,000. Decision Variables xi (three decisions, cells K7:K9), Aij (nine decisions, cells B2:D4) Objective Minimize Z = 1d (the shortfall, if any, between planned billings and $74,000) Constraints All xi are integers. (1) Aij = Rij for all i, j. (2) A11 = 0. (4) ijijxA4031= for all i. (5) Note that constraint (3) doesnt matter in this iteration. Also note the balance equation constraint, forcing E10 = G10. Decision Models -- Prof. Juran47Decision Models -- Prof. Juran48

Decision Models -- Prof. Juran48Decision Models -- Prof. Juran49We have verified that it is feasible to have billings of $74,000.

Decision Models -- Prof. Juran49Decision Models -- Prof. Juran50

Decision Models -- Prof. Juran50Decision Models -- Prof. Juran51

Decision Models -- Prof. Juran51Decision Models -- Prof. Juran52Now we know that it is feasible to achieve both of the first two goals.

Decision Models -- Prof. Juran52Decision Models -- Prof. Juran53Third Iteration Meeting both the first and second goals will now be constraints, and well focus on the third goal. Decision Variables xi (three decisions), Aij (nine decisions), k (two decisions) Objective Minimize Z = 3d (the number of senior employees hired above the goal of 3) Constraints All xi are integers. (1) Aij = Rij for all i, j. (2) Goals k = vk (for goals 1 and 2) (3) A11 = 0. (4) ijijxA4031= for all i. (5) Note that v1 = v2 = 0 from the previous iteration. Decision Models -- Prof. Juran53Decision Models -- Prof. Juran54

Decision Models -- Prof. Juran54Decision Models -- Prof. Juran55All of the first three goals are feasible.

Decision Models -- Prof. Juran55Decision Models -- Prof. Juran56Fourth Iteration Meeting all of the first three goals will now be constraints, and well focus on the fourth goal. Decision Variables xi (three decisions), Aij (nine decisions), k (for the first three goals) Objective Minimize Z = 4d (the number of new juniors hired above the goal of 1) Constraints All xi are integers. (1) Aij = Rij for all i, j. (2) Goals k = vk for goals 1, 2, 3 (3) A11 = 0. (4) ijijxA4031= for all i. (5) Note that v1 = v2 = v3 = 0 from the previous iteration. Decision Models -- Prof. Juran56Decision Models -- Prof. Juran57

Decision Models -- Prof. Juran57Decision Models -- Prof. Juran58

Decision Models -- Prof. Juran58Decision Models -- Prof. Juran59

Decision Models -- Prof. Juran59Decision Models -- Prof. Juran60

Decision Models -- Prof. Juran60Decision Models -- Prof. Juran61

There are two constraints that dont show in the window: $E$2:$E$4