_Idl}? East Extension Phase 2 Project Quantity Requirements. Granular A & Granular B Type 1 Granular A Granular B Segment Quantity (tn) Segment Quantity (tn) Segment C1 497 911 Segment C1 76,740 Segment D1a 374,351 Segment D1a 48 251 Segment Db 520,675 Segment Db 43,255 Segment C2a 456,533 Segment C2a 71,055 Segment C2b 507,005 Segment C2b 65,797 Segment D2 482,096 Segment D2 58,218 Granular Base Supply Quotes. Granular A & Granular B Type 1 Supplier 1 Supplier 2 Supplier 3 Supplier 4 Supplier 5 Supplier 6 Supplier 7 $/tn $/tn $/tn $/tn $/tn $/tn $/tn Supply of Granular A (Quarry) 7.96 6.37 6.75 7.85 6.95 7.85 13.30 Hauling from Quarry to Segment C1 10.58 10.06 9.10 9.50 9.50 11 3.35 to Segment D1a 10.74 10.32 9.10 9.50 9.50 11.5 3.55 to Segment D1b 11.64 10.50 10.30 9.50 9.50 12 2.60 to Segment C2a 11.12 9.10 9.25 10.25 10.25 12 3.95 to Segment C2b 12.44 9.10 9.65 10.25 10.25 13 4.85 to Segment D2 12.70 10.50 11.25 10.50 10.50 13.25 2.60 Supply of Granular A (Gravel Pit) 9.37 8.50 13.00 Hauling from Gravel PIT to Segment C1 4.96 6.00 6.25 to Segment D1a 4.46 6.00 6.00 to Segment D1b 4.80 5.36 6.25 to Segment C2a 4.02 6.00 5.25 to Segment C2b 3.73 6.00 4.75 to Segment D2 5.20 5.36 6.75 Supply of Granular B Type 1 3.72 4.52 6.10 6.28 6.18 6.95 14.00 Hauling from Quarry to Segment C1 5.71 4.96 9.75 6.00 4.70 7.15 3.35 to Segment Dia 5.44 4.46 11.65 6.00 4.70 6.90 3.55 to Segment Dib 5.70 4.80 10.95 5.36 4.70 7.15 2.60 to Segment C2a 4.69 4.02 9.90 6.00 3.50 6.15 3.95 to Segment C2b 4.24 3.73 10.30 6.00 3.50 5.65 4.85 to Segment D2 6.3 5.20 11.90 5.36 5.50 7.65 2.60 Qualifications to the quotes: Supplier 2: Granular A from Gravel Pit delivery capacity is limited to a maximum of 300,000 tn Supplier 3: If total quantity awarded to Miller is greater than 600,000 tn, there will be a 3% discount to all the prices from Miller Supplier 4: Maximum capacity to deliver is aproximately 1,000,000 tn Supplier 7: Maximum delivery capacity is 300,000 tn Comercial restrictions: Granular A can come from eather a quarry of a gravel pit. Some companies quote both options. No suplier should have more than 50% of the total quantity.Granular A Granular B Supplier 3 Supplier 2 Supplier 4 Supplier 2 C1 497,941 76,740 Dia (Quarry) 74,351 48,251 D1a (Gravel Pit) 300,000 D1b 520,675 43,255 C2a 456,533 71,055 C2b 507,005 65,797 D2 482,096 58,218 TOTAL Qt (ton) 572,291 1,263,538 1,002,771 363,315 Total Cost $ 9,070,816 $ 19,054,929 $ 13,898,406 $ 3,279,434 $ 45,303,585Objectives Demonstrate how linear programming can be used to develop an optimal sourcing plan Apply the Excel Solver to develop the base case solution Conduct sensitivity analysis on key model variables using basic solver reports and SolverTable to develop alternatives Communicate the results of your analysis in a clear and concise manner Approach You should conduct your analysis by following the steps we have discussed as part of our structured decision modeling process: Framing a problem Building a model Analyzing the results of the model and testing alternatives Communicating the results of your analysis and making a recommendation To help you analyze this case, you can reflect on the following questions: 1. 4. What is partnerhsip's overall objective? What are the decision variables? What type of constraints do we need to consider? Formulate an LP model and obtain a solution that minimizes cost. Start by developing a base model that uses only the total demand for gravel as a constraint. You can then add additional constraints to the model such as supplier capacities, maximum supplier share of gravel quantity, discounts, etc. Does your solution save any cost compared to the original proposed procurement plan? Are there ways to improve this solution? If so, what are they and how would you go about implementing them