using Excel solver LP

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.

H1 H2 H3 H4 Quality Cost/Unit MAX

PA 4.2 5.6 9.1 7.5 4 2 565

PB 4.8 5.25 9.2 7.8 3.8 2.2 750

PC 5.1 8.3 10.4 6.2 3.6 2.15 525

PD 5.9 9.2 3.8 5.7 3.4 2.13 475

PE 6.4 4.7 7.1 8.3 4.1 2.33 540

PF 8.2 5.8 6.2 7.7 3.75 2.11 325

Requested 1250 900 750 690

Pandemic 1.1 0.9 1.2 1.3

Points

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.

we need 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