The CitriSun Corporation ships frozen orange juice

One Excel file with 8 worksheets.

Use one worksheet for one spreadsheet model or one output report. Before you submit your file, make sure that you have:

- 2 worksheets for 4.6 (original model, sensitivity report)

- 1 worksheet for the cotton problem

- 1 worksheet for 5.33

Problem 4.6:

The CitriSun Corporation ships frozen orange juice concentrate from processing plants in Eustis and Clermont to distributors in Miami, Orlando, and Tallahassee. Each plant can produce 20 tons of concentrate each week. The company has just received orders of 10 tons of concentrate from Miami for the coming week, 15 tons for Orlando, and 10 tons for Tallahassee. The cost per ton for supplying each of the distributors from each of the processing plants is shown in the following table:

The company wants to determine the least costly plan for filling their orders for the coming week.

Note: Show your spreadsheet model and the output report in Excel.

Answer questions with explanations or reference to specific numbers in the reports, and by using information from the output reports without re-running solver.

a.) Formulate an LP model for this problem. Implement the model in a spreadsheet and solve it. Paste your Sensitivity Report as a picture to your file.

b.) What is the optimal solution?

c.) Is the optimal solution degenerate? Why?

d.) Is the optimal solution unique? Why?

e.) What would the optimal objective function value be if the processing capacity in Eustis was reduced by five tons?

g.) Interpret the reduced cost for shipping from Eustis to Miami 22.

A cotton grower in South Georgia produces cotton on farms in Statesboro and Brooklet, ships it to cotton gins in Claxton and Millen where it is processed, and then sends it to distribution centers in Savannah, Perry, and Valdosta where it is sold to customers. Presently, 700 and 500 tons of cotton are available in Statesboro and Brooklet, respectively. The cost of transporting the cotton from the farms to the gins is shown in the following table:

The cost of shipping a ton of cotton from each gin to each distribution center is summarized in the following table:

The demand for cotton in Savannah, Perry, and Valdosta is 400, 300, and 450 tons, respectively. The trucks used to ship the cotton can hold a maximum of 400 tons. The cotton grower would like to meet the demand with the minimal shipping cost.

a.) Draw a network flow model to represent this problem. Make sure that you note all the numerical information about each node and each arc appropriately on the graph using network modeling convention (if you prefer, you can take a picture of your drawing and paste it to your file).

b. Implement your model in Excel and solve it.

c. What is the optimal solution? You can summarize your solution in a table or in a graph.

Problem 5.33

The US Department of Transportation (DOT) is planning to build a new interstate to run from Detroit, Michigan, to Charleston, South Carolina. A number of different routes have been proposed and are summarized in Figure 5.44, where node 1 represents Detroit and node 12 represents Charleston. The numbers on the arcs indicate the estimated construction costs of the various links (in millions of dollars). It is estimated that all of the routes will require approximately the same total driving time to make the trip from Detroit to Charleston. Thus, the DOT is interested in identifying the least costly alternative:

a.) Define the decision variables of the LP model (please define your decision variables such that the meanings of their possible values are clearly defined)

b.) Implement your model in Excel and solve it.

c.) What is the optimal solution?