Problems:
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.