Big Fig Case Study

Please use the provided spreadsheet to get the HW data.
The capacities of the locations are given below:
Stockton, CA – 25,000
Rockwall, TX – 15,000
Joliet, IL – 60,000
Atlanta, GA – 30,000
York, PA – 20,000
Problem VII.1.
Establish the base case and answer the following questions:
The total distance traveled (in truck miles) in the big fig’s current network (2017 base case) is the sum of RAW miles and FGI miles.
Please note: With reference to the case study, We allow fractional truckloads on each link for the planning purposes. So you should not round the number of trips to integers. All the demand and capacity numbers are per week, and this is a planning model. The demand numbers are averages per week. Some weeks will be more and some weeks will be less. And it is not clear how actual truck loads would be scheduled. The factors 7000 and 1296 (details are in the case study) can be viewed as average numbers of how many cases (or case equivalents) get loaded per truck. Typically these numbers would reflect actual scheduling practices and the resulting truck utilizations. (e.g., trucks going to customers might only be 70% full on average). So for planning purposes we would then allow fractional truckloads on each link, where you should think of 5.5 as the average number of trucks per week on that link – sometimes it’s more, say 6 or 7; and some times it will be less, say 4 or 3. But on average we expect it to be how much are the RAW miles? How much are the FGI miles?
For problem parts b and c, optimize the 2017 network (keep the bottling network fixed but optimize the assignment of customer locations to bottling plants) and answer the following questions:
Problem VII.1.b
The optimized total distance traveled (truck miles) in the optimized plant is the sum of RAW miles and FGI miles.
How much are the RAW miles?How much are the FGI miles?
Problem VII.1.c
How much is the volume produced (cases) at each of the following plant locations in the optimized plan?
Stockton, CA
Rockwall, TX
Joliet, IL
Atlanta, GA
York, PA
Problem VII.1.d
If there is no capacity constraint (i.e. existing capacities are removed on the current plants), how much is the volume produced at following plants? (Hint: You could add a very high capacity to each plant in the model to make is unconstrainted. For example, 150,000).
Stockton, CARockwall, TX
Joliet, IL
Atlanta, GA
York, PA
For problem parts e, f, and g, optimize the network for 2019 data. The capacity for the Stockton, Joliet, Atlanta and York plants remains unchanged. i.e. Stockton is 25,000, Joliet is 60,000, Atlanta is 30,000 and York is 20,000, but increase the capacity of Rockwall to 25,000 cases/week. Furthermore, add a candidate plant in Nashville, TN with a capacity of 25,000 cases/week.
Problem VII.1.e
The total distance traveled (truck miles) in the optimal plan is the sum of RAW miles and FGI miles.
How much are the RAW miles?How much are the FGI miles?
Problem VII.1.f
How much is the volume produced at each of the following plant locations in the optimal 2019 plan?
Stockton, CARockwall, TX
Joliet, IL
Atlanta, GA
York, PA
Nashvile, TN