Using excel

Chapter 4. Tyler is hoping to get a lot of custom cake and cookie orders in his new bakery for various parties and celebrations. He considers keeping a stock of celebratory helium balloons in his bakery so that he can sell them alongside the cakes and cookies to add more value for his customers. However, he knows that the demand for custom goods as well as the balloons is like to be probabilistic in nature instead of deterministic. He is trying to create a model of how many balloons to order to keep in stock to minimize the costs of inventory in his little bakery. He identifies the following characteristics and needs your help in filling out the table given below the information. . . Ordering Cost is $14.00 per order Cost of balloons is $4.00 per balloon The bakery uses the 20% annual holding cost rate for all inventory The lead time for a new order of helium balloons is 14 days. Data from other bakeries indicate that the demand during the 14-day lead time follows a normal probability distribution with a weekly mean of 25 balloons and a standard deviation of 7 balloons per week. The number of working days per year is 300 Acceptable probability of a stock-out, for Tyler is 10% or 0.10. Fill out the table below + . Optimal Inventory Policy Economic Order Quantity O* balloons Annual Inventorv Holding Cost H dollars Annual Ordering Cost O dollars Total Annual Cost TC dollars Maximum Inventory Level O*+S balloons Average Inventory Level (O*/2US balloons Reorder Point balloons Number of Orders per Year (D/O) Cycle Time (Days) T Safety Stock S Expected Stockouts per Year SO days balloons