Please show all steps and fully explain. If excel spreadsheet is used show cell code.

Assume the case of a single buyer and a single supplier, for a supply chain of a single product. The demand for the product is 22,000 units per year. Buyer ordering cost is $600 per order, and buyer inventory holding cost is $8 per unit, per year.

For the supplier, ordering cost is $400 per order, and inventory holding cost is $6 per unit, per year.

1. If the buyer determines the quantity Q to be supplied, compute the total cost of ordering and inventory holding cost for the buyer and also for the supplier, and the total costs for the supply chain.
2. Compute the above costs in the case of supplier setting up the Q.
3. Compute the costs of ordering, inventory holding, and total costs for the whole supply chain if the two companies decide to go for joint planning to minimize the costs.
4. Find out the differences in ordering costs and in holding costs for buyer and supplier under the two scenarios of (a) and (b). Make a table to show the different values.
5. If the current practice is dictated by the buyer, and the supply chain as a whole wants to shift to the policy where the total supply chain costs are to be minimized, what is the excess cost to be borne by the buyer?
6. What can you recommend about profit sharing or compensation for the buyer to agree to move to joint planning scheme?