ADM2302XAnalytics1013PM.zip

__MACOSX/._ADM2302[X] Analytics 20215 – 6112021 – 1013 PM

ADM2302[X] Analytics 20215 – 6112021 – 1013 PM/S5_1_What-if analysis for LP.pdf

What-if analysis for LP

ADM2302: Analytics

Presented by

Danial Khorasanian

University of Ottawa, Telfer School of Management

Spring/Summer 2021

Telfer.uOttawa.ca
Analytics, spring/summer 2021

2

Wyndor glass company model

Max Z = 300? + 500?
Subject to

? ≤ 4
2? ≤ 12

3? + 2? ≤ 18

? ≥ 0, ? ≥ 0 Right-hand side (RHS) values

Objective
function
coefficients

Telfer.uOttawa.ca
Analytics, spring/summer 2021

3

• So far, we have assumed that we know the
real values of the input parameters in our
modeling.

• However, many of the parameters of a model
are only rough estimates that cannot be
determined precisely at this time.

• Changes in values of these parameters may
change the optimal solution.

Introduction

Telfer.uOttawa.ca
Analytics, spring/summer 2021

4

• What-if analysis (or sensitivity analysis)
involves conducting analyses to determine
which parameters of the model are most
critical (the “sensitive parameters”) in
determining the optimal solution.

• Sensitive parameters need extra care to
refine their estimates because even small
changes in their values can change the
optimal solution.

Introduction …

Telfer.uOttawa.ca
Analytics, spring/summer 2021

5

We investigate the following in the next slides:
• Changes in one objective function coefficient
• Simultaneous changes in objective function

coefficients
• Changes in the RHS value of one constraint
• Simultaneous changes in the RHS values of

constraints

Outlines

Telfer.uOttawa.ca
Analytics, spring/summer 2021

6

• Changes in one objective function coefficient
• Simultaneous changes in objective function

coefficients
• Changes in the RHS value of one constraint
• Simultaneous changes in the RHS values of

constraints

Telfer.uOttawa.ca
Analytics, spring/summer 2021

7

• Two key parameters in the Wyndor model are the

coefficients in the objective function that

represent the unit profits of the two new

products.

• These parameters were estimated to be ?1 = 300

for the doors and ?2 = 500 for the windows.

Wyndor glass company model

Telfer.uOttawa.ca
Analytics, spring/summer 2021

8

• However, these unit profits depend on many
factors—the costs of raw materials, production,
shipping, advertising, and so on, as well as such
things as the market reception to the new products
and the amount of competition encountered.

• Some of these factors cannot be estimated with real
accuracy until long after the linear programming
study has been completed and the new products
have been on the market for some time.

Wyndor glass company model …

Telfer.uOttawa.ca
Analytics, spring/summer 2021

9

Wyndor glass company model …

Telfer.uOttawa.ca
Analytics, spring/summer 2021

10

Wyndor glass company model …

The profit per Product 1 has been revised from $300 to $200.
No change occurs in the optimal solution.

Telfer.uOttawa.ca
Analytics, spring/summer 2021

11

Wyndor glass company model …

The profit per Product 1 has been revised from $300 to $500.
No change occurs in the optimal solution.

Telfer.uOttawa.ca
Analytics, spring/summer 2021

12

Wyndor glass company model …

The profit per Product 1 has been revised from $300 to $1000.
The optimal solution changes.

Telfer.uOttawa.ca
Analytics, spring/summer 2021

13

Sensitivity analysis with Excel

Telfer.uOttawa.ca
Analytics, spring/summer 2021

14

• The sensitivity report for the objective
function coefficients reveals an allowable
range for each of them.

• Allowable range for a coefficient in the
objective function is the range of values over
which that coefficient can change without
causing a change in the optimal solution.

Sensitivity analysis with Excel …

Telfer.uOttawa.ca
Analytics, spring/summer 2021

15

• Allowable range for a coefficient ? is:
Objective coefficient – Allowable decrease ≤ ? ≤ Objective

coefficient + Allowable increase
• 1E + 30 is shorthand in Excel for 1030. This tremendously

huge number is used by Excel to represent infinity.
For the Wyndor problem, we have:
• The allowable range for ?1: 0 ≤ ?1 ≤ 750
• The allowable range for ?2: 200 ≤ ?2

Wyndor problem: Sensitivity analysis with
Excel

Telfer.uOttawa.ca
Analytics, spring/summer 2021

16

The two dashed lines that pass through the solid constraint boundary lines are the
objective function lines when ?2 (the unit profit for Product 1) is at an endpoint of
its allowable range, 0 ≤ ?2 ≤ 750. The ?? in the figure represents for ?2.

Wyndor model: Graphical insights into the
allowable range for the unit profit for Product 1

W

D

(2, 6) is optimal for 0 < PD < 750 PD = 0 (Profit = 0 D + 500 W) PD = 300 (Profit = 300 D + 500 W) PD = 750 (Profit = 750 D + 500 W) Line A Line C Line B 0 2 4 6 2 4 6 8 Production rate for doors Production rate for windows Feasible region Telfer.uOttawa.ca Analytics, spring/summer 2021 17 • The allowable range is quite wide for both objective function coefficients. • Thus, even though ?1 = 300 and ?2 = 500 were only rough estimates of the true unit profit for the doors and windows, respectively, we can still be confident that we have obtained the correct optimal solution. Wyndor problem: Sensitivity analysis … Telfer.uOttawa.ca Analytics, spring/summer 2021 18 Example: Advertising-mix problem Costs Cost Category Each TV Commercial Each Magazine Ad Each Sunday Ad Advertisement (in thousands $) $300 $150 $100 Planning (in thousands $) $90 $30 $40 Expected number of exposures (in thousands) 1,300 600 500 Telfer.uOttawa.ca Analytics, spring/summer 2021 19 • TV = Number of commercials for separate spots on television • M = Number of advertisements in magazines. • SS = Number of advertisements in Sunday supplements. Maximize Exposure = 1,300TV + 600M + 500SS subject to Ad Spending: 300TV + 150M + 100SS ≤ 4,000 ($thousand) Planning Cost: 90TV + 30M + 40SS ≤ 1,000 ($thousand) Number of TV Spots: TV ≤ 5 and TV ≥ 0, M ≥ 0, SS ≥ 0. Example: Mathematical formulation Telfer.uOttawa.ca Analytics, spring/summer 2021 20 Example. Spreadsheet formulation Telfer.uOttawa.ca Analytics, spring/summer 2021 21 Does the optimal solution change if the number of exposures per TV ad changes from 1300 to any value in the range [1320 – 1400]? Example: Question Telfer.uOttawa.ca Analytics, spring/summer 2021 22 • Changes in one objective function coefficient • Simultaneous changes in objective function coefficients • Changes in the RHS value of one constraint • Simultaneous changes in the RHS values of constraints Telfer.uOttawa.ca Analytics, spring/summer 2021 23 • The allowable ranges described in the preceding section deal with this uncertainty by focusing on just one coefficient at a time. • The allowable range for a particular coefficient assumes that the original estimates for all the other coefficients are completely accurate so that this coefficient is the only one whose true value may differ from its original estimate. Simultaneous changes in objective function coefficients Telfer.uOttawa.ca Analytics, spring/summer 2021 24 • This section focuses on how to determine, without solving the problem again, whether the optimal solution might change if certain changes occur simultaneously in the coefficients of the objective function (due to their true values differing from their estimates). Simultaneous changes in objective function coefficients … Telfer.uOttawa.ca Analytics, spring/summer 2021 25 Question: What would happen if the estimate of the unit profit for Product 1 (i.e. $300) was too low and the corresponding estimate for Product 2 (i.e. $500) was too high? Wyndor problem Telfer.uOttawa.ca Analytics, spring/summer 2021 26 The profit per door has been revised from $300 to $450. The profit per window has been revised from $500 to $400. No change occurs in the optimal solution. Wyndor problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 27 The profit per door has been revised from $300 to $600. The profit per window has been revised from $500 to $300. The optimal solution changes. Wyndor problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 28 If simultaneous changes are made in the coefficients of the objective function: • calculate for each change the percentage of the allowable change (increase or decrease) for that coefficient to remain within its allowable range. • if the sum of these percentage values does not exceed 100 percent (i.e. it is less than or equal to 100%), the original optimal solution definitely will still be optimal. • if the sum of these percentage exceeds 100 percent, then we cannot be sure that the original optimal solution will still be optimal. 100 percent rule for simultaneous changes in objective function coefficients Telfer.uOttawa.ca Analytics, spring/summer 2021 29 ?1: $300 → $450 Percentage of allowable increase = 100 × (450−300) 450 = 33.33% ?2: $500 → $400 Percentage of allowable decrease = 100 × (500−400) 300 = 33.33% Since the sum of the percentages does not exceed 100 percent, the original optimal solution (2, 6) definitely is still optimal. Wyndor problem: Example 1 Telfer.uOttawa.ca Analytics, spring/summer 2021 30 ?1: $300 → $600 Percentage of allowable increase = 100 × (600−300) 450 = 66.66% ?2: $500 → $300 Percentage of allowable decrease = 100 × (500−300) 300 = 66.66% Since the sum of the percentages exceeds 100 percent, 100 percent rule does not guarantee the original optimal solution (2, 6) is still optimal. In fact, we know that the optimal solution has changed to (4, 3). Wyndor problem: Example 2 Telfer.uOttawa.ca Analytics, spring/summer 2021 31 ?1: $300 → $525 Percentage of allowable increase = 100 × (525−300) 450 = 50% ?2: $500 → $350 Percentage of allowable decrease = 100 × (500−350) 300 = 50% Since the sum of the percentages does not exceed 100 percent, the original optimal solution (2, 6) is still optimal. Both (2, 6) and (4, 3) are now optimal, as well as all the points on the line segment connecting these two points. Wyndor problem: Example 3 Telfer.uOttawa.ca Analytics, spring/summer 2021 32 ?1: $300 → $150 Percentage of allowable increase = 100 × (300−150) 300 = 50% ?2: $500 → $250 Percentage of allowable decrease = 100 × (500−250) 300 = 83.33% The sum of the percentages exceeds 100 percent, and 100 percent rule does not guarantee the original optimal solution (2, 6) is still optimal. But it is actually still an optimal solution. Wyndor problem: Example 4 Telfer.uOttawa.ca Analytics, spring/summer 2021 33 • When the model has a large number of decision variables, by dividing each coefficient’s allowable increase or allowable decrease by the number of decision variables, the 100 percent rule immediately indicates how much each coefficient can be safely changed without invalidating the current optimal solution. 100 percent rule … Telfer.uOttawa.ca Analytics, spring/summer 2021 34 • Changes in one objective function coefficient • Simultaneous changes in objective function coefficients • Changes in the RHS value of one constraint • Simultaneous changes in the RHS values of constraints Telfer.uOttawa.ca Analytics, spring/summer 2021 35 Question: What happens if a change is made in the number of hours of production time per week (RHS values) being made available to Wyndor’s new products in one of the plants? Wyndor problem Telfer.uOttawa.ca Analytics, spring/summer 2021 36 Wyndor problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 37 Constraints that hold with equality at the optimal solution are called binding. Binding constraint Telfer.uOttawa.ca Analytics, spring/summer 2021 38 • We want to investigate the effect of changing this RHS for one of the plants. • With the original optimal solution (2, 6), only 2 of the 4 available hours in Plant 1 are used, so changing this number of available hours by 1 or 2 hours would have no effect on either the optimal solution or the resulting total profit from the two new products. • However, it is unclear what would happen if the number of available hours in either Plant 2 or Plant 3 were to be changed. Wyndor problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 39 Wyndor problem: Excel report Telfer.uOttawa.ca Analytics, spring/summer 2021 40 • When the functional constraints are in ≤ form, we interpreted the RHS values as the amounts of the respective resources being made available for the activities under consideration. • The shadow price for a resource constraint reveals the rate at which the objective cell can be increased by increasing the right-hand side of that constraint. • This remains valid as long as the RHS is within its allowable range. Shadow price Telfer.uOttawa.ca Analytics, spring/summer 2021 41 The allowable range for the RHS of a functional constraint is the range of values for this RHS over which this constraint’s shadow price remains valid. Therefore, • The shadow price of $0 is valid for the constraint of Plant 1, when 2 ≤ ???1 • The shadow price of $150 is valid for the constraint of Plant 2, when 6 ≤ ???2 ≤ 18 • The shadow price of $100 is valid for the constraint of Plant 3, when 12 ≤ ???3 ≤ 24 Allowable range for RHS Telfer.uOttawa.ca Analytics, spring/summer 2021 42 Wyndor problem The hours available in plant 2 have been increased from 12 to 13. The total profit increases by $150 per week. Telfer.uOttawa.ca Analytics, spring/summer 2021 43 Wyndor problem The hours available in Plant 2 have been increased from 13 to 18. The total profit increases by $750 per week, i.e. 150$ increase per 1 hour increase in hours available in Plant 2. Telfer.uOttawa.ca Analytics, spring/summer 2021 44 Wyndor problem The hours available in plant 2 have been further increased from 18 to 20. The total profit does not increase any further. Telfer.uOttawa.ca Analytics, spring/summer 2021 45 We investigate the following in the next slides: • Changes in one objective function coefficient • Simultaneous changes in objective function coefficients • Changes in the RHS value of one constraint • Simultaneous changes in the RHS values of constraints Telfer.uOttawa.ca Analytics, spring/summer 2021 46 Question: What happens if simultaneous changes are made in the number of hours of production time per week being made available to Wyndor’s new products in all the plants? Wyndor problem Telfer.uOttawa.ca Analytics, spring/summer 2021 47 According to the shadow prices, the effect of shifting one hour of production time per week from Plant 3 to Plant 2 would be as follows: • ???2:12 → 13; Change in total profit = Shadow price = $150 • ???3:18 → 17; Change in total profit = − Shadow price = − $100 Therefore, net increase in total profit = $50. However, we don’t know if these shadow prices remain valid if both right-hand sides are changed by this amount. Wyndor problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 48 • A quick way to check this is to substitute the new RHS values into the original spreadsheet and run Solver again. • The net increase in total profit (from $3,600 to $3,650) is indeed $50, so the shadow prices are valid for these particular simultaneous changes in right- hand sides. Question: How long will these shadow prices remain valid if we continue shifting production hours from Plant 3 to Plant 2? Wyndor problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 49 • The shadow prices remain valid for predicting the effect of simultaneously changing the RHS values of some of the functional constraints as long as the changes are not too large. • To check whether the changes are small enough, calculate for each change the percentage of the allowable change (decrease or increase) for that RHS to remain within its allowable range. • If the sum of the percentage changes does not exceed 100 percent, the shadow prices definitely will still be valid. (If the sum does exceed 100 percent, then we cannot be sure.) The 100 Percent Rule for Simultaneous Changes in RHS values Telfer.uOttawa.ca Analytics, spring/summer 2021 50 ???2: 12 → 13 Percentage of allowable increase = 100 × (13−12) 6 = 16.67% ???3: 18 → 17 Percentage of allowable decrease = 100 × (18−17) 6 = 16.67% Since the sum of the percentages does not exceed 100 percent, the shadow prices definitely are valid for predicting the effect of these changes. Wyndor problem: Example 1 Telfer.uOttawa.ca Analytics, spring/summer 2021 51 ???2: 12 → 15 Percentage of allowable increase = 100 × (15−12) 6 = 50% ???3: 18 → 15 Percentage of allowable decrease = 100 × (18−15) 6 = 50% Because the sum does not exceed 100 percent, the shadow prices are still valid, but these are the largest changes in the RHS values that can provide this guarantee. Wyndor problem: Example 2 Thanks for Your Attention __MACOSX/ADM2302[X] Analytics 20215 - 6112021 - 1013 PM/._S5_1_What-if analysis for LP.pdf ADM2302[X] Analytics 20215 - 6112021 - 1013 PM/S4_1_LP Applications-Part 2.pdf LP applications: Part 2 ADM2302: Analytics Presented by Danial Khorasanian University of Ottawa, Telfer School of Management Spring/Summer 2021 Telfer.uOttawa.ca Analytics, spring/summer 2021 2 After discussing about “Resource-allocation problems”, we proceed with the following categories of the LP applications: • Cost-benefit-trade-off problems • Mixed problems Introduction Telfer.uOttawa.ca Analytics, spring/summer 2021 3 LP applications • Resource-allocation problems • Cost-benefit-trade-off problems • Mixed problems Telfer.uOttawa.ca Analytics, spring/summer 2021 4 • Cost–benefit–trade-off problems are LP problems where the mix of levels of various activities is chosen to achieve minimum acceptable levels for various benefits at a minimum cost. • The identifying feature is that each functional constraint is a benefit constraint, which has the form: Level achieved ≥ Minimum acceptable level for one of the benefits. Cost-benefit-trade-off problem Telfer.uOttawa.ca Analytics, spring/summer 2021 5 Three kinds of data are needed: 1. The minimum acceptable level for each benefit (a managerial policy decision). 2. For each benefit, the contribution of each activity to that benefit (per unit of the activity). 3. The cost per unit of each activity. Cost-benefit-trade-off problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 6 Excel template for cost-benefit-trade-off problems Telfer.uOttawa.ca Analytics, spring/summer 2021 7 We investigate the following problems for this category: • Problem 2.1: Profit & Gambit Co. advertising- mix problem • Problem 2.2: Personnel scheduling Cost-benefit-trade-off problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 8 LP applications • Resource-allocation problems • Cost-benefit-trade-off problems • Problem 2.1: Profit & Gambit Co. advertising-mix problem • Problem 2.2: Personnel scheduling • Mixed problems Telfer.uOttawa.ca Analytics, spring/summer 2021 9 • The Profit & Gambit Co. produces cleaning products for home use. • Management has decided to undertake a major new advertising campaign that will focus on the following three key products: • A spray prewash stain remover. • A liquid laundry detergent. • A powder laundry detergent. Problem 2.1. Profit & Gambit Co. advertising- mix problem Telfer.uOttawa.ca Analytics, spring/summer 2021 10 • This campaign will use both television and the print media. • A commercial has been developed to run on national television that will feature the liquid detergent. • The advertisement for the print media will promote all three products. Problem 2.1. Profit & Gambit Co. advertising- mix problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 11 Management has set the following goals for the campaign: • Sales of the stain remover should increase by at least 3 percent. • Sales of the liquid detergent should increase by at least 18 percent. • Sales of the powder detergent should increase by at least 4 percent. The objective is to determine how much to advertise in each medium to meet the sales goals at a minimum cost. Problem 2.1. Profit & Gambit Co. advertising- mix problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 12 Problem 2.1. Data • Table shows the estimated increase in sales for each unit of advertising in the respective (A unit is a standard block of advertising but other amounts also are allowed.) • The reason for −1 percent for the powder detergent in the Television column is that the TV commercial featuring the new liquid detergent will take away some sales from the powder detergent. • The bottom row of the table shows the cost per unit of advertising for each of the two outlets. Telfer.uOttawa.ca Analytics, spring/summer 2021 13 Decision variables: • TV = Number of units of advertising on television • PM = Number of units of advertising in the print media Problem 2.1. Mathematical formulation Telfer.uOttawa.ca Analytics, spring/summer 2021 14 Minimize Cost = TV + 2 PM (in millions of dollars) subject to PM ≥ 3 (Stain remover increased sales) 3 TV + 2 PM ≥ 18 (Liquid detergent increased sales) − TV + 4 PM ≥ 4 (Powder detergent increased sales) and TV ≥ 0 PM ≥ 0 Problem 2.1. Mathematical formulation … Telfer.uOttawa.ca Analytics, spring/summer 2021 15 Problem 2.1. Spreadsheet model Telfer.uOttawa.ca Analytics, spring/summer 2021 16 LP applications • Resource-allocation problems • Cost-benefit-trade-off problems • Problem 2.1: Profit & Gambit Co. advertising-mix problem • Problem 2.2: Personnel scheduling • Mixed problems Telfer.uOttawa.ca Analytics, spring/summer 2021 17 • Union Airways is adding more flights to and from its hub airport and so needs to hire additional customer service agents. • There five authorized eight-hour shifts with different salaries: • Shift 1: 6:00 AM to 2:00 PM • Shift 2: 8:00 AM to 4:00 PM • Shift 3: Noon to 8:00 PM • Shift 4: 4:00 PM to midnight • Shift 5: 10:00 PM to 6:00 AM • Objective: How to schedule the agents to provide satisfactory service with the smallest personnel cost Problem 2.2. Personnel scheduling Telfer.uOttawa.ca Analytics, spring/summer 2021 18 Problem 2.2. Data Telfer.uOttawa.ca Analytics, spring/summer 2021 19 Problem 2.2. Mathematical formulation Telfer.uOttawa.ca Analytics, spring/summer 2021 20 Problem 2.2. Spreadsheet model Telfer.uOttawa.ca Analytics, spring/summer 2021 21 LP applications • Resource-allocation problems • Cost-benefit-trade-off problems • Mixed problems Telfer.uOttawa.ca Analytics, spring/summer 2021 22 • Many LP problems do not fit completely into any of the previously discussed categories (pure resource-allocation problems, cost– benefit–trade-off problems). • The problem’s functional constraints include more than one type. • Such problems are called mixed problems. Mixed problem Telfer.uOttawa.ca Analytics, spring/summer 2021 23 Excel template for mixed problems Telfer.uOttawa.ca Analytics, spring/summer 2021 24 We investigate the following problem for this category: • Problem 3.1: Grain corp. advertising-mix problem Mixed problem … Telfer.uOttawa.ca Analytics, spring/summer 2021 25 LP applications • Resource-allocation problems • Cost-benefit-trade-off problems • Mixed problems • Grain corp. advertising-mix problem Telfer.uOttawa.ca Analytics, spring/summer 2021 26 • A Super Grain Corporation needs to develop a promotional campaign for the company’s new breakfast cereal. • Three advertising media have been chosen for the campaign: • TV • Magazine • Sunday supplement of major newspapers Problem 3.1. Grain corp. advertising mix problem Telfer.uOttawa.ca Analytics, spring/summer 2021 27 Constraints include: • a limited advertising budget, • a limited planning budget, and • a limited number of TV commercial spots available Problem 3.1. Constraints Telfer.uOttawa.ca Analytics, spring/summer 2021 28 They want to target two types of audiences: young children and parents of young children. Goals are: • The advertising should be seen by at least five million young children. • The advertising should be seen by at least five million parents of young children. Furthermore, exactly $1,490,000 should be allocated for cents-off coupons. Problem 3.1. Constraints … Telfer.uOttawa.ca Analytics, spring/summer 2021 29 How much of each medium should be used to maximize exposures while meeting the constraints? Problem 3.1. Question Telfer.uOttawa.ca Analytics, spring/summer 2021 30 Problem 3.1. Data Costs Cost Category Each TV Commercial Each Magazine Ad Each Sunday Ad Advertisement $300,000 $150,000 $100,000 Planning 90,000 30,000 40,000 Expected number of exposures 1,300,000 600,000 500,000 Telfer.uOttawa.ca Analytics, spring/summer 2021 31 Problem 3.1. Data Number Reached in Target Category (millions) Blank Each TV Commercial Each Magazine Ad Each Sunday Ad Minimum Acceptable Level Young children 1.2 0.1 0 5 Parents of young children 0.5 0.2 0.2 5 Contribution Toward Required Amount Blank Each TV Commercial Each Magazine Ad Each Sunday Ad Required Amount Coupon redemption 0 $40,000 $120,000 $1,490,000 Telfer.uOttawa.ca Analytics, spring/summer 2021 32 TV = Number of commercials for separate spots on television M = Number of advertisements in magazines SS = Number of advertisements in Sunday supplements Problem 3.1. Mathematical formulation Telfer.uOttawa.ca Analytics, spring/summer 2021 33 Maximize Exposure = 1,300TV + 600M + 500SS subject to Ad …