(Get Answer)

data analytics multi-part question and need an explanation and answer to help me learn. I need help with 2 inventory management problems. Example problems (and solutions) are provided to follow. This requires someone skilled with operations / inventory management, thank you. Requirements: Explanation written with screenshots / spreadsheet calculations supporting just like the example. You can use the inventory spreadsheet to perform necessary calculations, but please don’t submit just the spreadsheet. Explain how you reach your conclusion (for example, explain how various parameters were obtained; and please also explain your conclusion using calculation results from the spreadsheet). Note that the spreadsheet calculates total cost without purchase cost. For both problems, you need to include it. So please add the purchase cost rate which is R*C where C is the unit purchase cost of the product. A computer cable manufacturing company is evaluating two outside suppliers for a connector that is used in its production. The following data are known: The daily usage for the connectors follows a Normal distribution with an average of 1000 and a standard deviation of 100. Assume 365 working days every year. Annual inventory holding cost of this connector is assessed as 15% of the purchase cost. It wants to achieve a 95% service level for the connector. Each unit of shortage costs $500. Supplier A is local, so it provides a faster (and more reliable) lead time, and the shipping cost is also lower. Supplier B is a bigger, national supplier, and it can offer a better price on each unit (purchase cost). The relevant parameters are given in the following table. Which supplier should the company choose that will give it the lowest total cost (purchase + ordering + holding + shortage)? What will the company’s inventory order quantity and safety stock be if it goes with the afore-chosen supplier? RistoranteDiRoma offers two types of pizza (cheese or pepperoni) in its menu. Since, the restaurant does not specialize in pizza, the pizzas are ordered from another prepared food store. Luigi, the owner of the restaurant, estimates that the monthly demand for the cheese pizza is normal with a mean of 150 and standard deviation of 30. Similarly monthly demand for pepperoni is also normal with mean 180 and standard deviation 40. It costs $50 to place an order at the supplier (food store) and takes 5 days for the order to arrive at the restaurant. Cheese and pepperoni pizza costs $6 and $8, respectively. Assume that the annual interest rate is 12%. Luigi sets the maximum chance of stockout to 10% not to upset his customers and estimates that it costs $15 per pizza short including the loss of goodwill. (Assume 30 days/month). Determine the economic order quantity (EOQ), reorder point (R), and the average number of shortages per month for each type of pizza. Also find the average monthly total cost for this system. Luigi claims that it would help reduce the cost to order only the pizza dough from the prepared food store. He claims they could add the toppings when the demand is realized (when a customer orders a pizza) at an additional cost of $2. It costs $3.5 for a pizza dough and ordering cost remains the same at $50 per order. The delivery times will remain as before. Find the average and standard deviation of the monthly demand for the pizza dough. Also determine the economic order quantity (EOQ) and the reorder point (R) for the pizza dough. Find the average cost associated with this alternative. Do you think Luigi should take this action? (Hint: If σR,C and σR,P are the standard deviations of demand rate for cheese and pepperoni pizzas, then you can calculate the standard deviation of demand rate for all pizzas as .)