undefined

Microsoft Excel Sensitivity Report (Obtained Using Standard LP/Quadratic Engine) Adjustable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease 0 55 7.000000003 1E+30 $B$4 Number to Make 30,000 Model 1 0 85 10 14.00000001 $C$4 Number to Make 10,000 Model 2 0 7 67 1E+30 7.000000003 $B$5 Number to Buy Model 1 5,000 0 95 14.00000001 10 $C$5 Number to Buy Model 2 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease 30,000 60 30000 20000 10000 $B$10 Total Available Model 1 15,000 95 15000 1E+30 5000 $C$10 Total Available Model 2 $D$14 Process 1 Used 10,000 -25 10000 800 4000 $D$15 Process 2 Used 14,000 OL 15000 1E+30 1000 $D$16 Process 3 Used 4,000 0 5000 1E+30 1000 The ABC Company manufactures two types of product: Model 1 and Model 2. The company has been contracted to supply a total of 30,000 of Model 1 and 15,000 Model 2. However, ABC's production capability is limited in three departments: Process 1, Process 2, and Process 3. The following table summarizes the hours of processing time available and the processing time required by each department, for both models: Process 1 Process 2 Hours Required per Trimmer Model 1 Model 2 0.20 0.40 0.30 0.50 0.10 0.10 Hours Available 10,000 15,000 5,000 Process 3 The company makes its model 1 in-house for $55 and its model 2 for $85. Alternatively, it can buy model 1 and model 2 from another source for $67 and $95, respectively. How many model 1 and model 2 should ABC make and how many should it buy from its competitor in order to fulfill its contract in the least costly manner? A co-worker has created the spreadsheet and run the sensitivity report for you. Use the sensitivity report to answer the following questions. a) Is this solution unique? Yes b) Is this solution degenerate? No c) How much would model 1 have to cost in order for the company to consider purchasing these items rather than them? The optimal solution would change when the co-efficient for making model 1 decreases by according to the column. d) If the cost to make model 2 increased to $90 per unit, how would the optimal solution change? The optimal solution change because the cost of $90 per unit for model 2 is the of The total cost would be e) Explain the shadow price for process 3 used. The shadow price is because the constraint is The amount on slack on this constraint is f) How much should the company be willing to pay to acquire additional capacity in the process 2 area? Process 2 is a constraint with a slack of and a shadow price of The company should be willing to pay to acquire more process 2 time up to a maximum of hours