Solve using excel solver and show all cell values:

The Convoy of Hope organization is producing hygiene kits $($HYK$)$ at $4$ U$.$S$.$ locations $($New York, Boston, DC and Ft$.$ Lauderdale$),$ then shipping them to four European locations $($London$,$ Berlin, Paris and Vienna$)$ for final processing, prior to distributing these kits to $8$ Ukrainian refugee camps in safe NATO territory. Our goal is to find the least cost way to get the HYK from US to Europe to Ukrainian camps.

The per kit cost from the US production locations to the European coordination sites are shown below $($along with the $$quality$$ measure of HYK$$s produced at each US location$).$

The per kit cost from the processing location to the camps:

Assume New York will produce $500$ kits, Boston $375$ kits, DC $425$ kits and Ft$.$ Lauderdale $350$ kits. All kits must be sent to the European coordination sites.

The European final processing$/$coordination sites each has a limited capacity. London can process no more than $500$ kits, Berlin no more than $400$ kits, Paris no more than $420$ kits and Vienna no more than $450$ kits. Additionally, each processing$/$coordination site must use at least $80\%$ of their capacity. Also, the weighted quality of kits arriving at each European site must average at least $8\mathrm{.}55.$

The need for hygiene kits at each camp is shown below. Consider this the maximum number of kits you can send to each camp. An additional requirement of your model is that you must send at least $85\%$ of the maximum number of kits to each camp.

For security and safety reasons, there is a $$plane size limit$$ for HYK$$s traveling from the European sites to the Ukrainian refugee camps. No more than $125$ kits can be shipped from any one European site to any one refugee camp.

On the front end of the supply chain, due to capacity restrictions, each US location can send no more than $250$ kits to any one European location. Again, this is each US location to each European location, not overall.

Create a linear programming model to determine the least cost way to get the hygiene kits from the $4$ US production sites, to the $4$ final processing$/$coordination European sites, then to the $8$ refugee camps, meeting the stated distribution requirements. Once you get your model running, force integer solution variables.

Consider attacking the problem modularly, and double check that kits are not magically appearing and$/$or disappearing in your supply chain.

Cost data is provided in a separate spreadsheet.