Managerial Decision Modeling With Spreadsheets 3rd Edition Chapter 3 Solutions
Presentation on theme: "Managerial Decision Making Chapter 3 Modelling and Solving LP Problems in a Spreadsheet."— Presentation transcript:
1 Managerial Decision Making Chapter 3 Modelling and Solving LP Problems in a Spreadsheet
2 Introduction Solving LP problems graphically is only possible when there are two decision variables. Few real-world LPs have only two decision variables. Fortunately, there are packages for solving LP problems CPLEX (ILOG (IBM), http://www.ilog.com/),http://www.ilog.com/ Xpress-MP (Dash Optimization, http://www.dashoptimization.com/)http://www.dashoptimization.com/ NEOS Solver (http://www-neos.mcs.anl.gov/)http://www-neos.mcs.anl.gov/ GAMS (General Algebraic Modeling System, http://www.gams.com/)http://www.gams.com/ AMPL ( A Mathematical Programming Language, http://www.ampl.com/)http://www.ampl.com/ COIN-OR (COmputational INfrastrut. for OR, http://www.coin-or.org)http://www.coin-or.org Many other Relatively small LP problems can be solved using spreadsheets Solver Add-in in Excel (Frontline Systems, http://www.solver.com)http://www.solver.com 2
3 Implementing an LP Model in a Spreadsheet Organize the data for the model on the spreadsheet. Data-oriented model General form of LP model Reserve cells for all decision variables in the model. Create a formula for the objective. Create a formula for the left-hand-side (LHS) of each constraint. Use Excel Solver to solve the problem. 3
4 Implementing Blue Ridge Model in Excel X 1 = number of Aqua-Spa tubs to produce X 2 = number of Hydro-Lux tubs to produce Max350X 1 + 300X 2 (profit in dollars) Subject toX 1 + X 2 <= 200(pumps) 9X 1 + 6X 2 <= 1566(hours of labor) 12X 1 + 16X 2 <= 2880(feet of tubing) X 1, X 2 >= 0 Let's implement the model in Excel (BlueRidge.xls, Fig3-1.xls)BlueRidge.xlsFig3-1.xls 4
5 Excel Solver To use Excel Solver Add-in, go to: In Excel 2003: Menu Tools Solver In Excel 2007: Data toolbar Solver If Solver is not available in Excel, it needs to be enabled: In Excel 2003: Menu Tools Add-Ins Enable Solver Add-in In Excel 2007: Main Menu Excel Options Add-Ins Go Enable Solver Add-in If critical errors occur, disabling and enabling the Solver may help. Solver requires the following parameters: Target cell - the cell that represents the objective function Changing cells - the cells representing the decision variables Constraints – each constraint is defined by the LHS formula, the sign ( =), and the RHS value For LP models, go to Options and select "Assume Linear Model" 5
6 Make vs. Buy: The Electro-Poly Problem Electro-Poly is a leading maker of slip-rings. A $750,000 order has just been received. Model 1Model 2Model 3 Number ordered3,0002,000900 Hours of wiring per unit21.53 Hours of harnessing per unit121 Cost per unit to Make$50$83$130 Cost per unit to Buy$61$97$145 The company has to fulfill the order using 10,000 hours of wiring capacity and 5,000 hours of harnessing capacity. Can we make all required slip-rings? If not, which slip-rings should we buy? 6
7 Make vs. Buy Model 7 Decision variables: M 1 = number of model 1 slip rings to make in-house M 2 = number of model 2 slip rings to make in-house M 3 = number of model 3 slip rings to make in-house B 1 = number of model 1 slip rings to buy from competitor B 2 = number of model 2 slip rings to buy from competitor B 3 = number of model 3 slip rings to buy from competitor For short, we may write: M i = number of model i slip rings to make in-house (i=1..3) B i = number of model i slip rings to buy from competitor (i=1..3)
8 Make vs. Buy Model (continued) 8 Objective: Minimize the total cost of filling the order Min 50M 1 + 83M 2 + 130M 3 + 61B 1 + 97B 2 + 145B 3 (cost in $) Demand Constraints M 1 + B 1 = 3,000(units of model 1) M 2 + B 2 = 2,000(units of model 2) M 3 + B 3 = 900(units of model 3) Resource Constraints 2M 1 + 1.5M 2 + 3M 3 <= 10,000(hours of wiring) 1M 1 + 2M 2 + 1M 3 <= 5,000(hours of harnessing) Nonnegativity Conditions M 1, M 2, M 3, B 1, B 2, B 3 >= 0
9 Make vs. Buy: Excel Implementations The data oriented model: Fig3-17.xlsFig3-17.xls The same model in general LP form: ElectroPoly.xlsElectroPoly.xls One row for all variables One row for the objective function One row for each constraint 9
10 An Investment Problem: Retirement Planning Services, Inc. A client wishes to invest $750,000 in the following bonds. CompanyReturn Years to MaturityRating Acme Chemical8.65%111-Excellent DynaStar9.50%103-Good Eagle Vision10.00%64-Fair Micro Modeling8.75%101-Excellent OptiPro9.25%73-Good Sabre Systems9.00%132-Very Good The following restrictions have to be satisfied: No more than 25% can be invested in any single company. At least 50% should be in long-term bonds (maturing in 10+ years). At most 35% can be invested in DynaStar, Eagle Vision, and OptiPro. 10
11 Investment Model Decision variables X i = amount of money to invest in company i (i=1..6) Objective: Maximize the total return from the investment Max0.0865X 1 + 0.0950X 2 + 0.10X 3 + 0.0875X 4 + 0.0925X 5 + 0.09X 6 (return in $) Constraints X 1 + X 2 + X 3 + X 4 + X 5 + X 6 = 750,000(amount invested in $) X 1 + X 2 + X 4 + X 6 >= 375,000(min 50% long-term) X 2 + X 3 + X 5 <= 262,500(max 35% in low-rating) X i <= 0.25(750,000) for all i=1..6(max 25% in each company) X i >= 0 for all i=1..6 Implementation: see file Fig3-20.xlsFig3-20.xls 11
12 Transportation Problem: Tropicsun Tropicsun needs to transport citrus fruits from its groves to its processing plants at minimum cost. Groves and available citrus fruits: Mt. Dora – 275,000 bushels Eustis – 400,000 bushels Clermont – 300,000 bushels Processing plants and their processing capacities: Ocala – 200,000 bushels Orlando – 600,000 bushels Leesburg – 225,000 bushels Trucking company charges a flat rate for every mile that each bushel of fruits is transported.
13 Transportation Problem: Tropicsun 13 Mt. Dora 1 Eustis 2 Clermont 3 Ocala 4 Orlando 5 Leesburg 6 Distances (in miles) Capacity Supply 275,000 400,000 300,000 225,000 600,000 200,000 Groves Processing Plants 21 50 40 35 30 22 55 25 20
14 Transportation Model 14 Decision variables X ij = # of bushels shipped from node i to node j, i=1..3, j=4..6 Objective: Minimize the total number of bushel-miles Min21X 14 + 50X 15 + 40X 16 + 35X 24 + 30X 25 + 22X 26 + 55X 34 + 20X 35 + 25X 36 (total bushel-miles) Assuming the cost of transporting one bushel for one mile is constant, the above objective is equivalent to minimizing the total transportation cost.
15 Transportation Model (continued) Supply constraints X 14 + X 15 + X 16 = 275,000 (bushels taken from Mt. Dora) X 24 + X 25 + X 26 = 400,000 (bushels taken from Eustis) X 34 + X 35 + X 36 = 300,000 (bushels taken from Clermont) Capacity constraints X 14 + X 24 + X 34 <= 200,000 (bushels delivered to Ocala) X 15 + X 25 + X 35 <= 600,000 (bushels delivered to Orlando) X 16 + X 26 + X 36 <= 225,000 (bushels delivered to Leesburg) Nonnegativity conditions X ij >= 0 for all i=1..3 and j=4..6 Implementation: see file Fig3-24.xls, Tropicsun.xlsFig3-24.xlsTropicsun.xls 15
16 A Blending Problem: The Agri-Pro Company Agri-Pro has received an order for 8,000 pounds of chicken feed to be mixed from the following four feeds. Percent of Nutrient in NutrientFeed 1Feed 2 Feed 3Feed 4 Corn30%5%20%10% Grain10%30%15%10% Minerals20%20%20%30% Cost per pound$0.25$0.30$0.32$0.15 The mix must contain at least 20% corn, 15% grain, and 15% minerals. (Similar problems occur in the petrochemical industry.) 16
17 Blending Model Decision variables X i = thousands of pounds of feed i in the mix (i=1..4) Objective: Minimize the total cost of the mix Min 250X 1 + 300X 2 + 320X 3 + 150X 4 (cost in $) Parameters of the mix must be satisfied (0.30X 1 + 0.05X 2 + 0.20X 3 + 0.10X 4 )/8 >= 0.20(at least 20% of corn) (0.10X 1 + 0.30X 2 + 0.15X 3 + 0.10X 4 )/8 >= 0.15(at least 15% of grain) (0.20X 1 + 0.20X 2 + 0.20X 3 + 0.30X 4 )/8 >= 0.15(at least 15% of minerals) Other constraints X 1 + X 2 + X 3 + X 4 = 8(amount of the mix) X i >= 0 for all i=1..4 Implementation: see file Fig3-28.xlsFig3-28.xls Can you define the same problem using different variables? 17
18 Goals For Spreadsheet Design Communication – A spreadsheet's primary business purpose is communicating information to managers. Reliability – The output a spreadsheet generates should be correct and consistent. Auditability – A manager should be able to retrace the steps followed to generate the different outputs from the model in order to understand and verify results. Modifiability – A well-designed spreadsheet should be easy to change or enhance in order to meet dynamic user requirements. 18
19 Spreadsheet Design Guidelines - I Organize the data, then build the model around the data (for data-oriented models). Do not embed numeric constants in formulas. Things which are logically related should be physically related. Use formulas that can be copied. Relative (without $) and absolute (with $) references F4 key: B3 $B$3 B$3 $B3 back to B3 =SUMPRODUCT(coefficients, variables), =SUMPRODUCT(B5:C5,$B$3:$C$3) Column/row total should be close to the column/row being totaled. 19
20 Spreadsheet Design Guidelines - II The English-reading eye scans left to right, top to bottom. Use color, shading, borders and protection to distinguish changeable parameters from other model elements. Use text boxes and cell notes to document various elements of the model. 20
21 End of Chapter 3 21 Other problems in the chapter: Production Planning Problem – scheduling production over 6 months to balance the production and inventory costs Multi-Period Cash Flow Problem – establishing a sinking fund to pay for construction of a restaurant Data Envelopment Analysis – comparing efficiency of divisions
Managerial Decision Modeling With Spreadsheets 3rd Edition Chapter 3 Solutions
Source: https://slideplayer.com/slide/11479911/