Please complete in excel and show formulas.

3) For this question you will need to calculate 2 different EOQ and ROP values and interpret the results. For both EOQ and ROP, give your final answer in full sock sets (ROUND UP to the next whole number). The specific questions to answer are indicated in items a, b, and c below. Additional tips to calculate the EOQ and ROP are provided in the bullet points that follow.

a) Calculate EOQ and ROP for FY2022 based on the FY2022 forecast value that you determined to be most accurate in Question #1.

b) Calculate the EOQ and ROP based upon actual demand for FY2022

c) Use the total inventory cost calculation to determine total inventory costs for each of the three EOQ results: EOQ from part a, EOQ from part b, and the Q that Throx is currently using (given in the case). When determining the costs use 2022 actuals for your Demand (D) for all three scenarios Qs).

i. Compare the total inventory costs from the three results and analyze what this shows you.

d) What are the implications of the ROP Throx is currently using versus the ROP you calculated based on Actual Demand?

Additional tips: -Use a service level of 95% (z = 1.65) when calculating the ROP. -For the standard deviation of weekly demand, use the data provided in the table on page 2 of the case with the FY2022 forecast for all approaches. -Annual Inventory management costs will include the following: Annual Holding Costs, Annual Ordering Costs, and Purchase Cost.

Please complete in excel and show formulas.

Product Orders (Demand) Information The company provides you with the following information for the past two fiscal years: Product Forecasting Information Throx uses two main forecasting methods based on annual data to predict orders for the following year, a weighted moving average and exponential smoothing. The table below provides forecast calculations for FY2017 through FY2020, along with actual demand experienced in each of those years. \begin{tabular}{|c|c|c|c|} \hline & ActualDemand(ThreeSockSets) & WeightedMovingAverageFcst & ExponentialForecast \\ \hline 2019 & 19,525 & 15,500 & 16,300 \\ \hline 2020 & 23,750 & 17,215 & 19,203 \\ \hline 2021 & 30,722 & 21,155 & 23,295 \\ \hline 2022 & 34,950 & 27,088 & 29,979 \\ \hline \end{tabular} Weighted Moving Average uses Wt=0.6,Wt1=0.2,Wt2=0.2 Exponential Smoothing uses =0.9 and 1=0.1 The initial inventory for all sock styles combined at the beginning of FY2021 is 2,250 units. You also have information on current costs, which includes: - Order cost to Throx for an order placed with its current supplier, $/ order =$=$200 - Holding cost per set per year =H=$2.95 - The company currently pays (P) \$10.00 for each set of socks The company uses a continuous review replenishment policy and has IT systems in place that allow constant monitoring of key information. Last year, the company used a ROP under this policy of 2,500 units for all sock styles and an order quantity (Q) of 5,000 units for all sock styles. Potential Alternatives to Current Supply Chain Management The company has asked you to evaluate a number of alternatives to their current SCM practices, including at a minimum their choice of supplier, transportation modes, order quantities and safety stock, and warehouse capacity and location. SupplierInformation The table below provides information relating to important characteristics of the current supplier and the same types of information for two potential alternative suppliers in China ( A and B ) that have been contacted by Throx. The last column indicates the weights to be considered for each supplier characteristic. Quality is TBD based on your Capability Index calculations (see below). * TBD= to be determined (calculated) by students