# EXCEL Solver - Linear Programming

1. 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 in Excel and solve with Excel Solver 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.

© BrainMass Inc. brainmass.com October 10, 2019, 7:05 am ad1c9bdddfhttps://brainmass.com/math/linear-programming/excel-solver-linear-programming-567532

#### Solution Summary

Linear Programming