Show me the steps to solve Inventory Management Tools

Task $1$: Answer the following questions. Provide answers in a $.$docx file and show screen captures if you used a software tool $($such as Excel$).$ Upload supporting files.

$1.$ Aggregating products $($See Chopra section $11\mathrm{.}4$ and examples $11-3$ and $11-4)$

Harley purchases components from three suppliers. Components purchased from Supplier A are priced at $$5$ each and used at the rate of $20,000$ units per month. Components purchased from Supplier B are priced at $$4$ each and are used at the rate of $2,500$ units per month. Components purchased from Supplier C are priced at $$5$ each and used at the rate of $900$ units per month. Currently, Harley purchases a separate truckload from each supplier. As part of its JIT drive, Harley has decided to aggregate purchases from the three suppliers. The trucking company charges a fixed cost of $$400$ for the truck with an additional charge of $$100$ for each stop. Thus, if Harley asks for a pickup from only one supplier, the trucking company charges $$500$; from two suppliers, it charges $$600$; and from three suppliers, it charges $$700.$ Suggest a replenishment strategy for Harley that minimizes annual cost. Assume a holding cost of $20$ percent per year.

a$.$ Compare the cost of your strategy with Harley$$s current strategy of ordering separately from each supplier.

b$.$ What is the cycle inventory of each component at Harley?

$2.$ Quantity discount $($See Chopra section $11\mathrm{.}5$ and examples $11-7$ and $11-8)$

Prefab, a furniture manufacturer, uses $20,000$ square feet of plywood per month. Its trucking company charges Prefab $$400$ per shipment, independent of the quantity purchased. The manufacturer offers an all$-$unit quantity discount with a price of $$1$ per square foot for orders under $20,000$ square feet, $$0\mathrm{.}98$ per square foot for orders between $20,000$ square feet and $40,000$ square feet, and $$0\mathrm{.}96$ per square foot for orders larger than $40,000$ square feet. Prefab incurs a holding cost of $20$ percent. What is the optimal lot size for Prefab?

a$.$ What is the annual cost of such a policy?

b$.$ What is the cycle inventory of plywood at Prefab?

c$.$ How does it compare with the cycle inventory if the manufacturer does not offer a quantity discount but sells all plywood at $$0\mathrm{.}96$ per square foot?

Now consider the case in which the manufacturer offers a marginal unit quantity discount for the plywood. The first $20,000$ square feet of any order are sold at $$1$ per square foot, the next $20,000$ square feet are sold at $$0\mathrm{.}98$ per square foot, and any quantity larger than $40,000$ square feet is sold for $$0\mathrm{.}96$ per square foot.

d$.$ What is the optimal lot size for Prefab given this pricing structure?

e$.$ How much cycle inventory of plywood will Prefab carry given the ordering policy?

$3.$ Trade promotions $($See Chopra section $11\mathrm{.}6$ and examples $11-11)$

Dominick$$s supermarket chain sells Nut Flakes, a popular cereal manufactured by the Tastee cereal company. Demand for Nut Flakes is $1,000$ boxes per week. Dominick$$s has a holding cost of $25$ percent and incurs a fixed trucking cost of $$200$ for each replenishment order it places with Tastee. Tastee normally charges $$2$ per box of Nut Flakes.

a$.$ How much should Dominick$$s order in each replenishment lot?

b$.$ Tastee runs a trade promotion for a month, lowering the price of Nut Flakes to $$1\mathrm{.}80.$ How much should Dominick$$s order, given the short$-$term price reduction?

$4.$ Aggregation and safety inventory $($See Chopra section $12\mathrm{.}5$ and examples $12-8$ and $12-9)$

Croma is an Indian retail chain for consumer electronics. The company currently has $25$ stores located in major metropolitan areas. Weekly demand for smartphones at each store is normally distributed, with a mean of $300$ and a standard deviation of $300.$ The supplier currently takes four weeks to fulfill a replenishment order, which is placed separately by each store. Croma is targeting a CSL of $95$ percent and monitors its inventory continuously.

a$.$ How much safety inventory of smartphones should Croma carry at each retail store?

Croma is considering moving smartphones to the online channel, where they would be stocked in a single national warehouse. Assume that Croma can move smartphones to the online channel without losing demand $($the online demand is a sum of demand at each retail store$).$

b$.$ How much saving in safety inventory can Croma expect from going online if demand across stores is independent?

c$.$ How much saving in safety inventory can Croma expect from going online if demand across stores has a correlation coefficient of $=0\mathrm{.}5?$

$5.$ Safety inventory size $($See Chopra section $12\mathrm{.}6$ and example $12-13)$

Weekly demand for handbags at Liverpool, a Mexican department store chain, is normally distributed with a mean of $3,000$ and a standard deviation of $1,000.$ The replenishment lead time from the supplier is $4$ weeks. Liverpool uses a periodic review policy under which it reorders handbags every $12$ weeks. It currently uses an order$-$up$-$to level of $50,000.$

a$.$ What