linear programming model

(See attached file for full problem description)

1. Dave Carter, the loading master for DeMonier International Cargo Company in New Orleans, LA, is responsible for preparing a loading plan for a freighter bound for Ghana. An agricultural commodities dealer wants to charter the freighter and transport the following products aboard this ship.

Commodity Amount Available (tons) Volume per Ton (cubic feet) Profit per Ton ($)
1 4800 40 70
2 2500 25 50
3 1200 60 60
4 1700 55 80

Dave can elect to load any amount of the available commodities?provided the capacity and loading restrictions are not violated. The ship has three cargo holds with the following capacity restrictions:

Cargo Hold Weight Capacity (tons) Volume Capacity (cubic feet)
Forward 3000 145000
Center 6000 180000
Aft 4000 155000

Only one type of commodity can be placed into a particular cargo hold. Furthermore, because of balance considerations, the weight in the forward cargo hold must be within 10% of the weight in the aft cargo hold. Likewise, the weight in the center cargo hold must be between 40% and 60% of the total weight of the commodities loaded onto the ship.

a. Formulate a linear programming model to determine the manner in which the freighter should be loaded so as to maximize the profit. Clearly define your decision variables, your objective function, and your constraints. Place your algebraic formulation.
(15pts)

b. Using Excel Solver, solve the model you developed in part (a). learly identify the manner in which the ship should be loaded and indicate the profit associated with your loading plan.

Use the Page Setup feature of Excel to size your output to fit on one page and attach a copy of it to this examination. Then, use the Tools/Options/View/Formula command to insert the formulas that you used to create your spreadsheet model. Please ensure that the formulas are readable. Size this formula sheet to fit on one page and attach a copy of it to this examination. Use the Page Setup/Sheet feature to ensure that you include the row and column headings.
(10pts)
2. Ryan Patrick operates a fishing boat out of Newburyport, Massachusetts. He estimates that the total operating expenses of his boat are $10,000 per fishing day. Each day during which he fishes, Ryan leaves port early in the morning and fishes until he catches 3500 pounds of codfish. He then brings his catch to either the port in Gloucester or in Rockport. Gloucester is a major port for codfish with a well established market. The price paid for codfish in Gloucester is $3.25 per pound and this price is stable. The price paid in Rockport tends to be a bit higher than in Gloucester but tends to vary. Ryan has established that the daily price paid for codfish in Rockport is normally distributed with a mean of $3.65 per pound and a standard deviation of $0.20 per pound.

Since Gloucester is such a large port, Ryan never has a problem selling his entire haul of fish there. However, in Rockport he is sometimes unable to sell part or all of his daily catch. If he decides to unload at Rockport and is unable to sell his entire load, any unsold fish is then dumped in the open ocean while returning to his berth in Newburyport. Based on past history, Ryan has estimated that the demand for his codfish in Rockport follows the discrete probability distribution given below. Of course, while the buyers are often willing to buy more fish from him, Ryan can sell at most 3500 pounds of codfish?the size of his daily catch.

Demand (lbs of codfish) 0 1000 2000 3000 4000 5000 6000
Probability 0.02 0.03 0.05 0.08 0.33 0.29 0.20

At the start of any given day, the decision Ryan faces is to determine which port to use for selling his daily catch. The price of codfish that his catch might command in Rockport is only known if and when Ryan docks his boat there and negotiates with the buyers. After the boat docks at one of the two ports, it must sell its catch at that port or not at all, since it takes too much time to pilot the boat out of one port and sail to the other.

Because the average price he can earn at Rockport is higher, Ryan wonders if a smart strategy might be to sell his daily catch there. Although he recognizes that the standard deviation of the price introduces some risk, and the demand for his fish at Rockport is uncertain, he is not opposed to taking chances if they make good sense. However, given the certainty of being able to off-load his entire catch and the stability of the price paid at Gloucester, Ryan is unsure about where he should unload his boat.

He knows that if he docks at Gloucester, the earnings for his daily catch would be ($3.25)(3500) - $10000 = $1375. Of course, his earnings at Rockport are dependent on the revenue he receives for his catch there, and this is a product of the demand for his codfish and the price per pound he can realize?both of which are uncertain. Because of the difficulty of deriving the distribution of revenue at Rockport, Ryan has asked you to develop a simulation model to analyze the implication of selling his catch at Rockport.

a. Create an Excel spreadsheet model to simulate the net daily earnings that Ryan could achieve if he sold his catch at Rockport. Clearly label your spreadsheet. Replicate your simulation 500 times. You may want to use the procedure outlined on the REPLICATING A SIMULATION USING EXCEL handout, or you may prefer to use the copy and drag technique that we utilized when we modeled the Freddy Doll problem we discussed in class.

Compute the average and standard deviation of the replication results. [=average and =stdev or the Tools/Data Analysis/Descriptive Statistics feature of Excel may be helpful.] Using the Hide Rows command, hide enough rows on the worksheet you used to develop the simulation model so that it fits on one page. Attach a copy of your output to this exam.

Additionally, use the Edit/Move or Copy Sheet command to make a copy of your output spreadsheet with hidden rows. Then, use the Tools/Options/View/Formula command to insert the formulas that you used to create your spreadsheet model on the copied sheet. Size this formula sheet to fit on one page and attach a copy of it to this examination. Use the Page Setup/Sheet feature to ensure that you include the row and column headings.]

Report the mean and standard deviation values based on your replications in the space below. (15pts)

The Expected Net Daily Earnings based on replications = _______________________

The Standard Deviation of Net Daily Earnings based on replications = _________________

b. Using the results of your 500 replications, on any given day, what is the probability that Ryan will earn more money from using Rockport instead of Gloucester? Justify your answer and place it in the space below. (5pts)

c. Using the results of your 500 replications, on any given day, what is the probability that Ryan will lose money if he uses Rockport to off-load his catch? Justify your answer and place it in the space below. (5pts)

d. What is a 95% confidence interval for the mean of the distribution of daily earnings if Ryan off-loads his catch at Rockport? Justify your answer and place it in the space below. [=average and =stdev or the Tools/Data Analysis/Descriptive Statistics feature of Excel may be helpful.] (5pts)

95% confidence interval lower bound = ______________________

95% confidence interval upper bound = ______________________

e. Where would you recommend that Ryan off-load his catch if he had to choose between Gloucester and Rockport? Justify your answer and place it in the space below.