NEED TO CREATE AN EXCEL MODEL TO SOLVE THIS USING EXCEL SOLVER.

You manage the supply chain for Girl Scout cookie delivery in a portion of the southwest US region. You've added 4 new small regions for the upcoming year, and you need to fold this into the existing already committed production and distribution plan in a least cost mariner. You manage the production and distribution of two different Girl Scout Cookies - Thin Mints (TM), Silky Sarnogs. (SS). The following is the available production capacity (in cases) you can utilize to make Thin Mints and Sarngas at your three cookie making plants (Alpha, Beta, Gamma). MAX MAX MAX ProdLocAlpha ProdLocteta ProdLocGamma Thin Mints Sarroas 325 255 270 215 260 The forecast demand for Thin Mints and Samoas. (in cases) for the 4 new regions is estimated below. Thin Mints Samoas PLAN Region 1 forecast 115 75 PLAN Region 2 forecast 85 135 PLAN Region 3 forecast 250 185 |PLAN Region 4 forecast 240 355 The usual plan is to produce the cookies, send them to a warehouse for storage, then release the cookies to the regions as demanded. Presently, you are going to have to 4 warehouses (increased capacity) to handle this new demand. The warehouse locations each have a storage capacity of cookies they can handle. Note: Cookie storage capacity is found by taking 1.2*cases of Thin Mints plus 0.8* cases of Sarngas. The following table provides the 4 warehouse locations, characterized by capacity, and then the cost per case for cookies to be shipped TO the warehouse and the cost per case for cookies to be shipped FROM the warehouse (to the centroids of the new regions). These costs are the same regardless of cookie type. Capacity 405 Wichita 465 Ardmore 315 Little Rock 525 Amarillo Alpha Beta Gamma Regioni Region2 Region Region 2.3 2.6 1.8 3.2 5.7 3.2 3.44 4.33 2 4.6 7.3 4.5 5.3 3.22 2.11 1.75 3.9 6.3 5.7 3.9 3.2 1.1 5.7 4.6 5.2 6.1 82 The goal for your supply chain decisions: Find the least cost way of meeting cookie demand. Costs are the costs to ship cookies. Demand should be met exactly. There are additional requirements for your supply chain plan beyond the core constraints: 1) At least 80% of the available cookie storage capacity (the weighted storage capacity using the 1.2 and the 0.8 factors) must be utilized in each warehouse. 2) Similarly, at least 80% of the production capacity (by type) must be used at each of the three cookie making plants. 3) The age of the cookie plants is varied - and this impacts the quality of the cookies. Alpha has a 6 rating. Beta a '5' rating, and Gamma a 6.5" rating. Each cookie type arriving at each warehouse must have quality ratings averaging 5.8. 4) Due to size limitations in distribution vehicles, the plan for shipping cookies from the warehouse to the regions must not allow a combined total of 200 cases of cookies to go from one warehouse to a region. This is not weighted cases a la warehouse capacity, just the actual number of cases of Thin You manage the supply chain for Girl Scout cookie delivery in a portion of the southwest US region. You've added 4 new small regions for the upcoming year, and you need to fold this into the existing already committed production and distribution plan in a least cost mariner. You manage the production and distribution of two different Girl Scout Cookies - Thin Mints (TM), Silky Sarnogs. (SS). The following is the available production capacity (in cases) you can utilize to make Thin Mints and Sarngas at your three cookie making plants (Alpha, Beta, Gamma). MAX MAX MAX ProdLocAlpha ProdLocteta ProdLocGamma Thin Mints Sarroas 325 255 270 215 260 The forecast demand for Thin Mints and Samoas. (in cases) for the 4 new regions is estimated below. Thin Mints Samoas PLAN Region 1 forecast 115 75 PLAN Region 2 forecast 85 135 PLAN Region 3 forecast 250 185 |PLAN Region 4 forecast 240 355 The usual plan is to produce the cookies, send them to a warehouse for storage, then release the cookies to the regions as demanded. Presently, you are going to have to 4 warehouses (increased capacity) to handle this new demand. The warehouse locations each have a storage capacity of cookies they can handle. Note: Cookie storage capacity is found by taking 1.2*cases of Thin Mints plus 0.8* cases of Sarngas. The following table provides the 4 warehouse locations, characterized by capacity, and then the cost per case for cookies to be shipped TO the warehouse and the cost per case for cookies to be shipped FROM the warehouse (to the centroids of the new regions). These costs are the same regardless of cookie type. Capacity 405 Wichita 465 Ardmore 315 Little Rock 525 Amarillo Alpha Beta Gamma Regioni Region2 Region Region 2.3 2.6 1.8 3.2 5.7 3.2 3.44 4.33 2 4.6 7.3 4.5 5.3 3.22 2.11 1.75 3.9 6.3 5.7 3.9 3.2 1.1 5.7 4.6 5.2 6.1 82 The goal for your supply chain decisions: Find the least cost way of meeting cookie demand. Costs are the costs to ship cookies. Demand should be met exactly. There are additional requirements for your supply chain plan beyond the core constraints: 1) At least 80% of the available cookie storage capacity (the weighted storage capacity using the 1.2 and the 0.8 factors) must be utilized in each warehouse. 2) Similarly, at least 80% of the production capacity (by type) must be used at each of the three cookie making plants. 3) The age of the cookie plants is varied - and this impacts the quality of the cookies. Alpha has a 6 rating. Beta a '5' rating, and Gamma a 6.5" rating. Each cookie type arriving at each warehouse must have quality ratings averaging 5.8. 4) Due to size limitations in distribution vehicles, the plan for shipping cookies from the warehouse to the regions must not allow a combined total of 200 cases of cookies to go from one warehouse to a region. This is not weighted cases a la warehouse capacity, just the actual number of cases of Thin