I need answer in excel sheet with formulaes.

Q$1.$ CAU store sells $1,000$ jackets each month. Each jacket costs the store $$50$ and the company has an annual

holding cost of $20$ percent. The fixed cost of a replenishment order $($including transportation$)$ is $$200.$

a$)$ The store currently places a replenishment order every month for $1,000$ jackets. What is the annual

holding and ordering cost? $(10$ points$)$

b$)$ If the retail store wants to minimize ordering and holding cost, what order size do you recommend? How

much would the optimal order reduce the annual holding and ordering costs relative to the current

policy? $(10$ points$)$

Q$2.$ Yoda has a large warehouse in Seoul and is deciding on a policy for inbound shipping. Three shipping policies

available for inbound shipping are LTL$,$ TL$,$ and hybrid. LTL shipping costs $$1$ per unit. TL shipping costs $$750$ per

truck. Hybrid shipping policy costs $$150$ as a fixed cost per shipment along with a variable cost of $$0\mathrm{.}40$ per unit

shipped. A truck can carry up to $2,000$ units. Yoda incurs a fixed cost of $$50$ for each order placed with a supplier.

Each unit costs $$400,$ and Yoda uses a holding cost of $15$ percent. Demand at Yoda is $2000$ per month. $(30$ points$)$

Part A$.$ Assume that LTL shipping policy is used.

a$)$ What is the optimal order size? $(5$ points$)$

b$)$ What is the annual total cost of the optimal policy $($Do not include annual material cost$)?(4$ points$)$

Part B$.$ Assume that TL shipping policy is used.

c$)$ What is the optimal order size? $(5$ points$)$

d$)$ What is the annual total cost of the optimal policy $($Do not include annual material cost$)?(4$ points$)$

Part C$.$ Assume that hybrid shipping policy is used.

e$)$ What is the optimal order size? $(5$ points$)$

f$)$ What is the annual total cost of the optimal policy $($Do not include annual material cost$)?(4$ points$)$

Part D$.$ Comparison.

g$)$ Which shipping policy would you recommend and why? $(3$ points$)$

Q$3.$ Groot purchases components from three suppliers. Components purchased from Supplier A are priced at $$5$

each and used at the rate of $3,000$ units per month. Components purchased from Supplier B are priced at $$4$ each

and are used at the rate of $2,000$ units per month. Components purchased from Supplier C are priced at $$3$ each

and used at the rate of $1,000$ units per month. Currently, Groot purchases a separate truckload from each supplier.

As part of its JIT drive, Groot has decided to aggregate purchases from the three suppliers. The trucking company

charges a fixed cost of $$500$ for the truck with an additional charge of $$100$ for each stop. Thus, if Groot asks for a

pickup from only one supplier, the trucking company charges $$600$; from two suppliers, it charges $$700$; and from

three suppliers, it charges $$800.$ Assume a holding cost of $25$ percent per year.

a$)$ Suggest a replenishment strategy for Groot that minimizes annual cost. $(10$ points$)$

b$)$ Compare the cost of your strategy with Groot$$s current strategy of ordering separately from each

supplier. $(15$ points$)$

Q$4.$ Rocket and Gamora carry spare parts for their dealers at a third$-$party warehouse in Michigan$$s Upper

Peninsula. Demand for Rocket spare parts is $2500$ units per month, whereas demand for Gamora parts is $3500$ per

month. Each spare part costs $$200$ and both companies have a holding cost of $25$ percent.

a$)$ Currently, each company uses a separate truck to ship these parts. Each truck has a fixed cost of $$2000.$

What is the optimal order size and frequency for Rocket? And for Gamora? What is the annual ordering

and holding cost for each company? $(10$ points$)$

b$)$ Dr$.$ Strange, a third$-$party logistics provider has offered to combine shipments for each of the two

companies on a single truck. This will increase the cost of each truck to $$2500.$ If the two companies agree

to the joint shipment, what is the optimal order frequency and size? What is the annual ordering and

holding cost for the two companies combined? Should Rocket and Gamora accept Dr$.$ Strange$$s proposal?

$(15$ points$)$