XYZ Inc is a lumber wholesaler located in the USA. It is crucial to choose a portfolio of suppliers in order to minimizes any risks of stock outs. Current market research has indicated 8 possible suppliers. The table below shows the relevant information on the available suppliers. For example, Supplier A can delivery at $49.50/unit (container) up to their capacity of 300 (300,000 units) per year. The supplier A is unionized, and it uses ships to deliver the merchandise. The deliveries from supplier A have an average of 15% damage upon delivery.

According to market research and last year's performance, XYZ requires 1,225,000 units of lumber for the upcoming year. In order to maintain the quality and minimize losses, the average damage per delivery should not exceed 19%. In addition, XYZ should procure at least 50% of its coking coal from unionized suppliers in order to avoid potential complaints. Finally, XYZ should make sure that the infrastructure is capable of handling its purchases. Currently, the port has granted a maximum throughput of 650,000 units per year, and capacity for delivering wood by trucks is limited to 720,000 units per year.

	A	B	C	D	E	F	G	H
Price ($/unit)	49.50	50.00	61.00	63.50	66.50	71.00	72.50	80.00
Unionization	Yes	Yes	No	Yes	No	Yes	No	No
Truck/Ships	Ship	Truck	Ship	Truck	Truck	Truck	Ship	Ship
Damaged delivery (%)	15	16	18	20	21	22	23	25
Capacity (thousands/year)	300	600	510	655	575	680	450	490

Considering the information above, provide a solution that would answer the following questions:

1. How much wood should XYZ purchase from each supplier in order to minimize the cost of supply and maintaining desired quality?
2. What is total cost of supply?

How can this be put in excel