3 OVERALL QUESTIONS IN ONE: Assume the demand data given is normally distributed. Average costs for A, B, and C books are $5.05, $7.39, and $10.25, respectively, for a single copy. It costs 45% per year, per unit, for annual holding costs. The batch size is 3,500 for an A book, 1,000 for a B book, and 500 for a C book. The administrative cost for ordering a batch of a book has been estimated to be $125. The holding cost for A, B, and C is $2.27, $3.33, and $4.61, respectively. I calculated EOQ for A, B, and C to be 1709, 960, and 663 respectively. Calculate the EOQ and reorder point (ROP) for each of the book types. Lead time for any type of book is 5 weeks. Assume ETPI targets a fill rate of 95%. 1. Using the 52 Weeks of demand data points given below, calculate the EOQ, ROP, and safety stock for each book individually and show your work and answers. When using EXCEL show FORMULA so that I know how an answer was derived using excel. Also please do not use a previously generated CHEGG answer as this will not help me understand. I need a new breakdown of how this is done, step by step, for each calculation. Thanks. If any information is missing, please ask and I will provide it (no assumptions for this), but I think I have covered it all. Demand Data Points for Book A: 525, 517, 562, 455, 399, 526, 584, 542, 511, 602, 289, 602, 355, 329, 535, 571, 384, 641, 382, 655, 581, 435, 447, 399, 367, 487, 579, 545, 617, 364, 582, 499, 568, 486, 323, 623, 459, 452, 728, 631, 483, 619, 393, 421, 636, 600, 462, 683, 659, 431, 501, 494 Demand Data Points for Book B: 154, 147, 134, 145, 108, 139, 203, 154, 132, 168, 177, 212, 172, 154, 140, 154, 147, 269, 220, 253, 187, 243, 232, 202, 206, 273, 239, 203, 219, 292, 251, 289, 246, 238, 286, 216, 248, 326, 275, 336, 315, 318, 289, 359,278, 347, 299, 368, 271, 346, 391, 308 Demand Data Points for Book C: 75, 151, 77, 78, 79, 80, 81, 231, 82, 233, 159, 84, 160, 237, 87, 163, 164, 90, 90, 241, 92, 243, 169, 95, 171, 96, 172, 248, 99, 249, 175, 102, 102, 103, 254, 180, 255, 256, 107, 183, 259, 185, 186, 111, 112, 113, 264, 114, 265, 117, 267, 118

Here is what is "given" (IN BOLD) and what I have already calculated (FOLLOWED BY "???"); please double-check my work (Which is not bolded AND is followed by "???"): I started working BOOK A's calculations: For Book Type A considering a normal distribution: Total Annual Demand for Book Type A (D) = 26520 ??? Book A Cost per Unit = $5.05 Annual Holding Cost per unit of inventory (H) = $2.27 ??? Total Ordering Cost per batch of Book A (S) = $125.00 Batch Size for Book A = 3,500 Lead Time for all products = 5 weeks EOQ = 1709 Units per order ??? Standard Deviation = 103.1647 ??? Fill Rate for all book types = 95% N = 52 (weeks) Standard Deviation with Lead Time = ???

TO HELP BETTER UNDERSTAND THE NEEDED FIGURES - Attached is a template provided that will help auto do some of the work; however, the cells highlighted in red are the figures I'm having trouble with calculating. The cells highlighted in BLUE are the needed inputs in order to auto generate the outcome cells highlighted in YELLOW.

2. Given these calculations, now consider the batch size dictated by the supplier for each book. Taking both your inventory models into account, as well as the batch size, what is your recommendation for each type of book in terms of order quantities, ROP, and safety stock? Explain your logic considering the tradeoffs between batch size and optimal EOQ.

3. What is the total cost of your recommendation? The final total cost answer should reflect all products, on an annual basis, as well as safety stock based on your recommendations for each product. The text explains how total costs are based on average inventory over the year including safety stock.

he values for your problem in Ie cells in the INPUT table. sulting values will be displayed OUTPUT table