ALL DONE IN EXCEL WITH FORMULAS SHOWN ; ILL LIKE (:

During the COVID-19 pandemic, the US Government has used their lawful powers to enlist 6 of your medical lab facilities to manufacture COVID-19 test kits. They will compensate you well for your service to the general good of mankind.

The following table provides the estimated parameters you will use to help build a supply chain plan for producing COVID-19 test kits (CTK) and distributing them to 4 designated hospital facilities.

Each production facility (PA thru PF) has a cost/CTK, the quality of CTK produced, and the maximum number of CTKs that can be produced (everything is on a weekly basis).

Each hospital facility (H1 thru H4) has the requested number of CTKs, and a pandemic points value, which is set by the CDC (Center of Disease Control) that is a surrogate measure of criticality of supply that must reach that hospital facility.

The per unit shipping cost of CTKs is shown in the 6x4 table.

Create a supply chain model (LP model) that determines the least cost way to ship CTKs from production facilities to hospital facilities. Note there are two costs production and transportation.

Requirements of your model:

1. There is a maximum allowed to be shipped from each Production facility (shown in table).
2. There is a maximum allowed to be shipped TO each hospital facility (shown in table as REQUESTED).
3. There is a minimum amount that can be shipped to each hospital facility (80% of the MAX).
4. Additionally, your supply chain solution must total at least 3400 pandemic points (shown in table). This number is calculated by the points shown per hospital multiplied by the total number of CTKs sent.
5. Each hospital must receive CTKs such that their average quality is at least 3.65. (Quality is shown in the table it varies by production facility because of different ages of equipment, etc.).
6. Due to transportation limitations, no more than 350 CTKs can be sent from any one production facility to any one hospital facility.
7. Integer numbers of products must be sent.

In words, describe the solution you find that optimizes (minimizes total costs) the distribution of CTKs.

A C D E F H1 H2 H3 H4 Quality Cost/Unit MAX 4.2. 9.1 7.5 4 2 565 9.2 7.8 3.8 750 PA PB PC PD 4.8 5.1 5.6 5.25 8.3 9.2 3.6 525 6.2 5.7 5.9 3.4 2.2 2.15 2.13 2.33 2.11 10.4 3.8 7.1 6.2 475 PE 4.7 8.3 4.1 6.4 8.2 1250 PF 540 325] 5.8 7.7 3.75 Requested 900 750 690 1.1 0.9 1.2 1.3 Pandemic Points A C D E F H1 H2 H3 H4 Quality Cost/Unit MAX 4.2. 9.1 7.5 4 2 565 9.2 7.8 3.8 750 PA PB PC PD 4.8 5.1 5.6 5.25 8.3 9.2 3.6 525 6.2 5.7 5.9 3.4 2.2 2.15 2.13 2.33 2.11 10.4 3.8 7.1 6.2 475 PE 4.7 8.3 4.1 6.4 8.2 1250 PF 540 325] 5.8 7.7 3.75 Requested 900 750 690 1.1 0.9 1.2 1.3 Pandemic Points