Explore BrainMass
Share

EXCEL Solver - Linear Programming

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

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 25, 2018, 9:13 am ad1c9bdddf
https://brainmass.com/math/linear-programming/excel-solver-linear-programming-567532

Solution Summary

Linear Programming

$2.19
See Also This Related BrainMass Solution

Excel Solver - linear programming model

Need help with the following problem, am stuck trying to set it up correctly. Can you help with the objective function and constraints, also please provide the formulas for the solver. Need to know what MIN statement should be and all Constraints for linear program (see "example_solution" for notation).

View Full Posting Details