LP University Orange Groves that Grow Valencia and Temple Oranges Worksheet

User Generated

ycwhaxvr_55

Business Finance

Lovely Professional University

Description

The question attached needs to be solved using the Excel Solver. Can anyone assist?

Unformatted Attachment Preview

Consider three orange groves that grow Valencia and Temple Oranges. Based on accurate and detailed historical data, we have a fairly accurate idea of how many crates of oranges we can expect to harvest each month at each grove. We are going to “design” our supply chain using this data. Grove A should produce 100 crates of Valencia and 70 crates of Temple. Grove B should produce 75 crates of Valencia and 60 crates of Temple. Grove C should produce 66 crates of Valencia and 40 crates of Temple. Grove A produces Valencia with average quality of 5.0 and Temple with average quality of 4.8. Grove B produces Valencia with average quality of 4.6 and Temple with average quality of 4.5. Grove C produces Valencia with average quality of 4.4 and Temple with average quality of 4.6. (this is on a 5 point scale and based on historical data) These groves serve three OJ production facilities in St. Augustine, Deltona and Ocala. Note that in our planned design, the groves will NOT ship crates directly to the production facilities (see more info below). Based on the average month’s anticipated juice production schedule, the following amount of oranges are needed at each location: St. Augustine: 122 crates of Valencia, 25 crates of Temple. Deltona: 110 crates of Valencia, 50 crates of Temple. Ocala: 0 crates of Valencia, 88 crates of Temple. Due to truck load limitations, at most 70 TOTAL crates of oranges (both types combined) can be shipped between any one grove and any one warehouse, AND from any one warehouse to any one production facility. In order to deal with periodic demand and supply fluctuation, oranges will be shipped to warehouses before being shipped to the OJ Production facilities (the supply chain literature talks about how to manage variance in products by having supply and demand buffers to reduce stockout risk). WE WILL DESIGN THE SUPPLY CHAIN TO EXACTLY MEET ORANGE REQUIREMENTS AT THE PRODUCTION FACILITIES. (Oranges not accounted for will be left at the groves for selling locally like apple orchards in Nebraska! Don’t model this!). There are five different warehouse sites that will be used. Each site has a capacity ‘roll-through’ limit (in crates) (the final products are round, right?). That limit represents the total number of crates of both types of oranges that flow through the site. Also note that at least 75% of the capacity must be used in the distribution of oranges. Also note that, by orange type, the quality of arriving Valencia oranges at each warehouse must average at least 4.7, while the quality of arriving Temple oranges at each warehouse must average at least 4.65. Again, that is the benchmark for each orange type at each warehouse. Below are the costs per crate between the groves and the warehouse locations, and the costs per crate between the warehouse sites and the production facilities. Also shown – warehouse capacity. Variable Cost A B C Site 1 1.31 0.87 1.76 Site 2 1.75 2.3 1.12 Site 3 1.53 0.93 2.01 Site 4 1.99 1.65 1.45 Site 5 2.13 1.66 1.55 Variable cost: St. A Deltona Ocala Site 1 2.31 0.99 2.05 Site 2 2.51 1.34 1.77 Site 3 1.75 1.13 1.93 Site 4 2.66 1.67 2.23 Site 5 2.32 1.52 1.88 Capacity 70 80 110 100 90 Create an appropriate LP model - it’s objective should minimize the sum of the shipping costs of the crates in meeting the demand requirements (and other requirements) of orange juice production throughout the entire supply chain. Note that an integer number of crates must be delivered. As usual, I’d suggest waiting until the end to add the requirement to make sure integer crates are delivered. If there are any computational issues, note it and turn in the continuous (non-integer) solution.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached is the complete solution

A
B
C

Quality of Orange
Supply of Orange
Valencia Temple
Valencia
Temple
5
4.8
100
70
4.6
4.5
75
60
4.4
4.6
66
40

Shipping Cost (Grove to Warehouse)
Site 1
Site 2
Site 3
A
1.31
1.75
1.53
B
0.87
2.3
0.93
C
...

Similar Content

Related Tags