Set up the linear program with sensitivity report with an excel spreadsheet and

Question

Set up the linear program with sensitivity report with an excel spreadsheet and please show the excel solver

You are the recently hired Chief Operations Officer at ABC Inc, a regional firm which produces specialized circuit boards used in the production of various makes and models of automobiles. The company currently owns three production plants, one much newer than the other two. Once the circuit boards are produced, the firm ships them to one of four warehouses throughout the state where they are placed in inventory until ordered by the end-user. Each circuit board sells for $150.

Being the good COO that you are, you are always looking for ways to minimize costs and increase profitability. You suspect that there is much room for improvement in this regard. Specifically, you have reason to believe that production and stocking levels of each plant and warehouse could be modified to yield the desired results.

The details of this managerial challenge are as follows:

Production Costs   -    Plant A -- $100 per circuit board

            Plant B -- $120 per circuit board

            Plant C -- $90 per circuit board

            Plant Capacity       -    Plant A – 1,200 circuit boards

                                    Plant B – 1,200 circuit boards

                                    Plant C – 500 circuit boards

Demand Forecast   -    Warehouse #1 – 700 circuit boards

                                    Warehouse #2 – 400 circuit boards

                                    Warehouse #3 – 600 circuit boards

                                    Warehouse #4 – 500 circuit boards

Shipping Costs

Warehouse #1

Warehouse #2

Warehouse #3

Warehouse #4

Plant A

$10

$15

$40

$30

Plant B

$50

$20

$25

$20

Plant C

$25

$45

$30

$22

Create a linear program that shows exactly how many circuit boards should be produced at each plant and then shipped to each warehouse in order to maximize resulting profits. Your completed model should show the optimal values of all 12 decision variables AS WELL AS the optimal value of the objective function.

Warehouse #1

Warehouse #2

Warehouse #3

Warehouse #4

Plant A

$10

$15

$40

$30

Plant B

$50

$20

$25

$20

Plant C

$25

$45

$30

$22

Explanation / Answer

The objective of the firm is to minimise the total production cost and transportation cost and thus contribute to the profit. It is a minimisation problem The new total cost ( Production and transportation matrix is given below) Plant Warehouse1 Warehouse 2 Warehouse3 Warehouse4 Capacity A 100 115 140 130 1200 B 170 140 145 140 1200 C 115 135 120 112 500 Demand 700 400 600 500 Production cost $100 is added in plant A row Production cost $120 is added in Plant B ROW Production cost of $90 is added plant C row The trasportation problem is not balanced transportation problem. Since we are solving through LPP Balancing is not required. The solver solution through linear programming problem is calculated bottom Plant Warehouse1 Warehouse 2 Warehouse3 Warehouse4 Capacity Total A 700 400 0 100 1200 1200 B 0 0 500 0 1200 500 C 0 0 100 400 500 500 Demand 700 400 600 500 Total 700 400 600 500 Total Cost of Transporation 258300 Microsoft Excel 15.0 Sensitivity Report Worksheet: [Trasporations Problems.xlsx]Sheet5 Report Created: 04/04/2016 22:39:38 Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $D$26 A Warehouse1 700 0 100 33 107 $E$26 A Warehouse 2 400 0 115 18 122 $F$26 A Warehouse3 0 2 140 1E+30 2 $G$26 A Warehouse4 100 0 130 2 18 $D$27 B Warehouse1 0 63 170 1E+30 63 $E$27 B Warehouse 2 0 18 140 1E+30 18 $F$27 B Warehouse3 500 0 145 3 7 $G$27 B Warehouse4 0 3 140 1E+30 3 $D$28 C Warehouse1 0 33 115 1E+30 33 $E$28 C Warehouse 2 0 38 135 1E+30 38 $F$28 C Warehouse3 100 0 120 2 3 $G$28 C Warehouse4 400 0 112 3 2 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $D$30 Total Warehouse1 700 107 700 100 400 $E$30 Total Warehouse 2 400 122 400 100 400 $F$30 Total Warehouse3 600 145 600 700 500 $G$30 Total Warehouse4 500 137 500 100 400 $I$26 A Total 1200 -7 1200 400 100 $I$27 B Total 500 0 1200 1E+30 700 $I$28 C Total 500 -25 500 500 100

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.