The Audiofile company produced boomboxes. However, management has decided to subcontract out the production of the speakers needed for the boomboxes. Three vendors are available to supply the speakers. Their price for each shipment of 1,000 speakers and supply capacity is shown below.

Vendor	Price	Capacity (in shipment)
1	$2,250	28
2	$2,270	25
3	$2,230	13

Each shipment would go to one of the Audiofile company’s four factories. In addition to the price for each shipment, each vendor would charge a shipping cost. There are two types of costs associated with the shipping: set-up costs (fixed costs) of shipping and transportation costs which depend on the distance. Each vendor has its own formula based on the mileage to the factory. The fixed sipping cost, three formulas, and the mileage data are shown below. Certain shipping routes that are impossible are marked with the infinity symbol (∞).

Vendor	Fixed charge	Cost per mile	Factory 1	Factory 2	Factory 3	Factory 4
1	$4,000	40cents/mile	1,600 miles	400 miles	1100 miles	∞
2	$9,000	50cents/mile	500 miles	600 miles	∞	900 miles
3	$10,000	20cents/mile	2,000 miles	1,000 miles	500 miles	∞

There are estimations for demand of each factory which are shown below.

Factory	1	2	3	4
Monthly demand (shipments)	15	9	8	16

Considering that the demand of a factory needs to be satisfied and a factory may be delivered to from several vendors.

1. Formulate algebraically the corresponding model that will determine which vendors should be purchased from to fulfill the objective which is to minimize the sum of the purchase costs and the shipping costs, while satisfying all demands. Define the decision variables, objective function, and constraints. (25 points)
2. Formulate this problem on a spreadsheet and use Excel’s Solver to determine which vendor(s) the Audiofile company should purchase from and how shipments should be sent from each vendor to each factory to minimize the total cost (Provide the corresponding “Excel Spreadsheet” and the “Answer Report”). Include “managerial statements” that communicate the results of the analyses (i.e., describe verbally the results). 

Answer rating: 100% (QA)

Certainly heres how you can formulate the problem algebraically create an Excel spreadsheet and then use Solver to find the optimal solution Decision ... View the full answer