Explore BrainMass
Share

Explore BrainMass

    EXCEL Solver - Linear Programming

    This content was COPIED 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 10, 2019, 7:05 am ad1c9bdddf
    https://brainmass.com/math/linear-programming/excel-solver-linear-programming-567532

    Solution Summary

    Linear Programming

    $2.19