Explore BrainMass

Explore BrainMass

    Transportation problem: Excel solver

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

    Looking for assistance in setting up the problem. I am trying to establish the constraints and the table for solver. (See attached)

    Transportation System

    A company which manufactures product in five plants ships locally using its own transportation system, but has orders which must be sent to locations too far to be serviced by the local fleet. It therefore contracts with a middle distance carrier to complete its shipping. The locations of the manufacturing plants and the amount of product available to be shipped per week are show in the chart below.

    Manufacturing Locations Units Available per Week
    Columbia 450
    Macon 370
    Huntsville 550
    Greensboro 290
    Knoxville 360
    Total Available 2020

    The seven locations and their weekly demand are shown in the chart below.

    Destination Cities Weekly Demand
    Norfolk 305
    Charleston 253
    Gainesville 328
    Mobile 225
    Memphis 420
    Louisville 158
    Roanoke 210
    Total Available 1899

    Shipping costs per unit (in dollars) between plants and the destination cities are as follows:

    Shipping Costs Norfolk Charleston Gainesville Mobile Memphis Louisville Roanoke
    Columbia 27 13 31 42 48 51 44
    Macon 42 27 19 23 18 36 43
    Huntsville 42 31 23 16 20 39 36
    Greensboro 28 25 36 48 37 32 17
    Knoxville 47 31 43 39 16 14 34

    The transportation company wants to identify its optimal shipping plan that will satisfy demand at the lowest aggregate shipping cost.


    a. What is the transportation company trying to optimize? Are they trying to maximize or minimize?
    b. Write the objective function to support this analysis.
    c. What inputs do you need to support your analysis?
    d. Is there any extraneous data you have been given that you will not need?
    e. What criteria has the transportation company given you to support the analysis?
    f. Create a spreadsheet model that supports your analysis.
    g. How would you change your model if one of the locations was temporarily unavailable due to severe weather conditions? On a separate sheet in your Excel file, show how you might do this.

    © BrainMass Inc. brainmass.com February 24, 2021, 5:02 pm ad1c9bdddf


    Solution Summary

    This posting contains solution to following transportation problem using excel solver.