A Mathemtically formulate the above problem as a minimum cos

A) Mathemtically formulate the above problem as a minimum cost flow problem.

B) Formulate and solve a spread sheet model for the above problem using Excel.

The Audiofile company produces boomboxes. However, management has decided to subcontract out the production of speakers needed fortheboomboxes. Three vendors are available to supply the speakers Their price for each shipment of 1,000 speakers is shown below. Vendor Price 1 $22,500 2 22,700 22,300 Each shipment would go to one of the company\'s two warehouses. In addition to the price for each shipment, each vendor would charge a shipping cost for which it has its own formula based on the mileage to the warehouse. These formulas and the mileage data are shown below charge Vendor per shipment Warehouse 1 Warehouse 2 1 $300+40c/mile 1,600 miles 400 miles 2 $200+50c/mile 500 miles 600 miles 3 $500+20c/mile 2,000 miles 1,000 miles Whenever one of the company\'s two factories needs a shipment of speakers to assemble into the boomboxes, the company hires a trucker to bring the shipment in from one of the warehouses. The cost per shipment is given next, along with the number of shipments needed per month at each factory.

Solution

The minimum cost flow problem is as follows:

There are three Vendors, two Warehouses and two Factories. Let us say vendor1, vendor2 and vendor3 are nodes numbered as 1, 2 and 3. Warehouse1 is represented by node numbered 4 and number 5 represents Warehouse2, finally Factory1 as node 6 and Factory2 as node 7.

Decision variables are defined as Xij that quantity flows from node i to node j. Objective function coefficients are defined as Cij , unit cost of transfer from ith node to jth node,

Therefore Objective function is to minimize Double summation Cij * Xij   For all values of i and j (from 1 to 7)

Constraints are about the availabilities with the sources ( Vendors) and final demands at sinks/destinations (Factories) and inflows = outflows for intermediate nodes (Warehouses)

Calculation using Excels sheet are as f

Unable to transfer data/information from word and excel documents