Simpson’s O.J. Company produces three products from unprocessed orange juice – bottled juice, frozen juice concentrate and jelly. It purchases orange juice from three vineyards. The oranges are harvested at the vineyards and converted into juice at plants at the vineyard sites and stored there in refrigerated tanks. The juice is then transported to four different plants in Florida, Maine, Georgia and Minnesota, where it is processed into bottled orange juice, frozen juice concentrate and jelly. Vineyard output typically differs each month in the harvesting seasons, and the plants have different processing capacities.

In a particular month the vineyard in California has 1410 tons of unprocessed juice available, while the vineyard in Nevada has 1720 tons. The vineyard in Oregon has 1190 tons. The processing capacity per month is 1230 tons of unprocessed juice at the plant in Florida, 1140 tons of juice at the plant in Maine, 1450 tons at the plant in Georgia, and 1460 tons at the plant in Minnesota. The cost per ton of transporting unprocessed juice from the vineyards to the plant is as follows: California to Florida $830, California to Maine $710, California to Georgia $900, California to Minnesota $780, Oregon to Florida $920, Oregon to Maine $690, Oregon to Georgia $1000, Oregon to Minnesota $820, Nevada to Florida $920, Nevada to Maine $810, Nevada to Georgia $790 and Nevada to Minnesota $830.

The plants are different ages, have different equipment, and have different wage rates. Thus, the cost of processing each product at each plant ($/ton) differs as follows: juice to Florida $2190, juice to Maine $2360, juice to Georgia $2210, juice to Minnesota $1940, concentrate to Florida $4180, concentrate to Maine $4370, concentrate to Georgia $3920, concentrate to Minnesota $3950, jelly to Florida $2670, jelly to Maine $2380, jelly to Georgia $2530 and jelly to Minnesota $2860.

This month the company needs to process a total of 1190 tons of bottled juice, 810 tons of frozen concentrate and 670 tons of jelly at the four plants combined. However, the production process for frozen concentrate results in some juice dehydration, and the process for jelly includes a cooking stage that evaporates water content. To process one ton of frozen concentrate requires two tons of unprocessed juice. One ton of jelly requires 1.8 tons of unprocessed juice. One ton of bottled juice requires 1 ton of unprocessed juice.

Simpson’s management wants to determine how many tons of orange juice to ship from each of the vineyards to each of the plants and the number of tons of each product to process at each plant. Thus, management needs a model that includes both the logistical aspects of this problem and the production processing aspects. It wants a solution that will minimize total costs, including the cost of transporting orange juice from the vineyards to the plants and the product processing costs.

1. Formulate a linear programming model.

a) Define each of your decision variables clearly in the text of your report.

b) Clearly state your objective function Z and whether you are minimizing or maximizing Z with all appropriate units.

c) Clearly state all of your constraints by stating the appropriate units for each.

2. Use Excel Solver or QM for Windows to solve this linear program. If it has a single optimal solution, clearly state the values of each of the decision variables along with their units. If it does not have a single optimal solution, are there multiple optimal solutions? If so, why? Is the problem unbounded? Clearly interpret your solution in words using English that a manager or reader of your report can understand who has no knowledge of linear programming or quantitative methods.

3. What is the optimal value of Z along with its appropriate units?

4. Perform a sensitivity analysis on the results.

a) Which (if any) of the 3 vineyards are operating at capacity?

b) Which (if any) of the 3 vineyards are producing more unprocessed orange juice than is needed optimally?

c) Which (if any) of the 4 plants are operating at capacity?

d) Which (if any) of the 4 plants are processing the least orange juice for optimal production? That is, if you were forced to shut one plant down, which one would you choose and why? Explain.

e) If you could increase the available unprocessed orange juice at any of the 3 vineyards, which vineyard(s) would be most profitable? Why? What is the decrease in Z for each additional ton of unprocessed orange juice at this vineyard? Explain.

f) For this most profitable vineyard (if any), what is the upper limit on the tons of unprocessed orange juice that would decrease Z? Explain.

g) If you could increase the available unprocessed orange juice at any of the 3 vineyards, which vineyard(s) would be least profitable? Why?

h) If you could increase the capacity at one plant, which would you choose and why? For each additional ton of capacity at this plant, what would be the decrease in Z? Explain.

i) For this plant with greatest potential for increased capacity, what is the upper limit on its capacity that would guarantee a decrease in Z?

Writeup.

You will be turning in two (2) deliverables, a short writeup of the project and the spreadsheet showing your work.

Your writeup should introduce your solution to the project by describing the problem. Correctly identify what type of problem this is. For example, you should note if the problem is a maximization or minimization problem, as well as identify the resources that constrain the solution. Identify each variable and explain the criteria involved in setting up the model. This should be encapsulated in one (1) or two (2) succinct paragraphs with equations typed out clearly.

After the introductory paragraph, write out the L.P. model for the problem. Include the objective function and all constraints, including any non-negativity constraints. Then, you should present the optimal solution, based on your work in Excel or QM for Windows. Explain what the results mean.

Finally, write one or two paragraphs addressing the part of the problem pertaining to sensitivity analysis and shadow prices.

As previously noted, please set up your problem in Excel using Solver or QM for Windows and find the solution. Clearly label the cells in your spreadsheet. You will turn in the entire spreadsheet (.xlsx or .lin), showing the setup of the model, the results and sensitivity values.

Your report should be in APA style (Times New Roman 12 pt. font, 1 inch margins, double spaced). Have a title page with the assignment name, your name, course name and date. The report should be submitted in a Microsoft Word 2010 or Word 2013 document.

This assignment is worth 110 points (11% of your grade), so you should take the time to do your best effort on it.

Basic features

- Free title page and bibliography
- Unlimited revisions
- Plagiarism-free guarantee
- Money-back guarantee
- 24/7 support

On-demand options

- Writer’s samples
- Part-by-part delivery
- Overnight delivery
- Copies of used sources
- Expert Proofreading

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)