Question

Smith Construction Homework Assignment

Smith’s Construction firm currently has three project sites underway in various counties in Iowa. Each requires a specific supply of gravel. Three gravel pits are available to provide for the company’s needs, but shipping costs vary by location. The following table summarizes the data. The $costs shown in the table are the unit shipping costs to ship a ton of the gravel from a specific pit location to a specific project site.

TO FROM	Project Site 1	Project Site 2	Project Site 3	Pit Capacity (Tons)
Ames pit	$9	$8	$7	1,500
Davenport pit	$7	$11	$6	1,750
Des Moines pit	$4	$3	$12	2,750
Project Requirements (Tons)	2,000	3,000	1,000	6,000

1. Determine the optimal shipping quantities between the pits and the project sites to minimize overall shipping costs
2. Examining the situation, Smith recognizes that the sites for Projects 1 and 2 are very close to Des Moines. Rather than shipping gravel directly from the Ames and Davenport sites to these project sites, Smith wonders if it would be better for him to consolidate shipping at Des Moines. This would also bring the consolidation point closer to the project sites. Given the excellent roads, he believes he can ship gravel between Ames and Des Moines and between Davenport and Des Moines for as little as $2 per ton. Set up and solve Smith’s problem with this new information.
3. What could change the decision made using the optimization model that you have used?
4. Are there any factors missing in the information provided that Smith may need for the decision?

Please submit an Excel file containing your work. Your work must include the following:

• Structure the problem in the way we have discussed similar problems in class.
• Setup the parameters in Excel Solver. Your submitted file must show these parameters.
• Save the file with the solution you get after you solve the problem.
• Create a separate sheet to answer question (b).
• For questions (c) and (d) you must type in your answer on the same spreadsheet.

Please show formulas and calculations step by step

Answer 1

a)

First let’s solve the simple one

The model is shown below

The formulas are shown below

The solver parameters are shown below

The result is shown below. The second table shows the optimal shipping plan and cost.

b)

Now, if we ship from Ames and Davenport to Des Moines and consolidate it then we need to consider the inflow and outflow of the De Moines pit. In order to do that we need to make a few modifications to our older model. The new model will be a 4x3 matrix instead of 3x3.

The new model is shown below. The modified formula for the total cost is seen in the formula bar.

The formulas for total cost and the total extraction for Des Moines site has been modified. They are shown below

The solver parameters are shown below.

The result is shown below.

c)

There are various factors that could change the decisions. These are

The cost coefficients or the shipping cost. For example, if the shipping cost between Davenport and Site 2 reduced to $2 then it would definitely change the solution.

The requirements. If the requirements in sites changes, it changes the solution

The capacity. If the capacity of the pits change, it changes the solution as well

d)

One key information that is missing is the extraction cost. The shipping cost are one component of the overall cost but we may also need to consider the extraction cost (or setup cost) in practical scenarios.