Problem $1(45$ points$)$: This problem is a combination of binary integer programming and

transportation problem

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,25028$

$2$ $$2,27025$

$3$ $$2,23013$

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 $(\backslash$infty $).$

ADM $2302$ Assignment $2$

$2|$ P a g e

Spring$/$Summer $2024$

Vendor Fixed charge Cost per mile Factory $1$ Factory $2$ Factory $3$ Factory $4$

$1$ $$4,00040$cents$/$mile $1,600$ miles $400$ miles $1100$ miles $\backslash$infty

$2$ $$9,00050$cents$/$mile $500$ miles $600$ miles $\backslash$infty $900$ miles

$3$ $$10,00020$cents$/$mile $2,000$ miles $1,000$ miles $500$ miles $\backslash$infty

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

Factory $1234$

Monthly demand $($shipments$)159816$

Considering that the demand of a factory needs to be satisfied and a factory may be delivered to

several vendors.

a$.$ 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$)$

b$.$ 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$).(20$ points.$)$ Set this up on excel and provide all the formulas used. Problem $1(45$ points$)$: This problem is a combination of binary integer programming and

transportation problem

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.

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 $().$

Spring$/$Summer $2024$

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

Considering that the demand of a factory needs to be satisfied and a factory may be delivered to

several vendors.

a$.$ 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$)$

b$.$ 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$).(20$ points$)$