This is an excel grid that needs to be filled. please use excel to solve this problem

Problem Imagine you own and operate a bakery that specializes in Victoria Sponge Cakes (google Victoria Sponge Cakes to learn more). You decide to use your Lean Production knowl- edge to help in planning your inventory and production schedule based on your demand. The recipe for the Victoria Sponge is provided in the accompanying spreadsheet along with cost of ingredients. 1. Based on prices provided of each product, calculate the cost in ingredients to make one cake. 2. Assuming labor cost of $12 per hour and it takes on hour to drive to the grocery, pick ingredients and return, calculate your fixed setup cost. 3. Assume your APR is 12% compounded weekly, what is your inventory cost? 4. Using the appropriate model, calculate your lot size assuming that demand is deterministic at 23 cakes per week. Suppose demand is now variable. You decide to purchase flour and butter from a supplier that will deliver these two products to you every 2 weeks. Assume the unit cost is the same as provided in the spreadsheet and you now use a (Q,r) approach to control stock levels. 5. If you want to restrict replenishment orders to once per month, what batch size Q should you use? 6. Using the batch size calculated above, what reorder point r should you use to ensure a service level of at least 98 percent? Conversions Conversions 1 ounce 28.35 g 1 lb 454 g 1 fl oz 1 fl oz 0.125 cup 1 Gallon 256 tbsp 225 g 225 g 16 oz Filling Filling Recipe Butter 113.5 g Icing Sugar 60 g Vanilla Extract 0.5 tsp Strawberry Jam 0.25 cup Cake Cake Recipe Granulated Sugar Butter Eggs All Purpose Flour Baking Powder Vanilla Extract Milk 6 tsp 4 ct 8 oz 225 g 3 tsp 2 tsp 6 Tbsp Ingredient Size Unit Price Ingredient Size Price Granulated Sugar 4 lb $ 2.00 Butter $ 3.19 Cage Free Eggs 12 ct $ 2.49 All Purpose Flour 5 lb $ 2.09 Baking Powder $ 1.35 Vanilla Extract 2 fl oz $ 6.15 2% Milk 1 gal $ 2.19 Icing Sugar 32 oz $ 1.89 Strawberry Jam 18 oz $ 1.99 Labor 1 hour $12 Electricity 1 kWh $ 0.11 Current Inventory Icing Sugar 16 oz Butter 2 lbs Eggs 12 Flour 3 lbs Baking Powder 4 oz Vanilla Extract 2 fl oz Strawberry Jam 18 oz Granulated Sugar 2 lbs 2% Milk 0.5 gal Oven wattage 3000 Watts/hr Stand mixer 250 Watt/hr Batter and filling tim 15 minutes Bake time 35 minutes Cakes (frozen) 20 Completed o 8 1 15 2 20 3 30 4 10 5 30 6 25 7 30 25 0 1 2 3 4 5 6 7 8 0 1 2 3 4 5 6 7 8 Cake Demand On-hand Net Requirements Planned Receipt LT = 1 week Flour Demand On-hand Net Requirements Planned Receipt (EOQ) LT = 1 week Butter Demand On-hand Net Requirements Planned Receipt LT = 1 week Sugar Demand On-hand Net Requirements Planned Receipt LT = 2 weeks Milk Demand On-hand Net Requirements Planned Receipt LT = 1 week 0 1 2 3 4 5 6 7 8 0 1 2 3 4 5 6 7 8 Problem Imagine you own and operate a bakery that specializes in Victoria Sponge Cakes (google Victoria Sponge Cakes to learn more). You decide to use your Lean Production knowl- edge to help in planning your inventory and production schedule based on your demand. The recipe for the Victoria Sponge is provided in the accompanying spreadsheet along with cost of ingredients. 1. Based on prices provided of each product, calculate the cost in ingredients to make one cake. 2. Assuming labor cost of $12 per hour and it takes on hour to drive to the grocery, pick ingredients and return, calculate your fixed setup cost. 3. Assume your APR is 12% compounded weekly, what is your inventory cost? 4. Using the appropriate model, calculate your lot size assuming that demand is deterministic at 23 cakes per week. Suppose demand is now variable. You decide to purchase flour and butter from a supplier that will deliver these two products to you every 2 weeks. Assume the unit cost is the same as provided in the spreadsheet and you now use a (Q,r) approach to control stock levels. 5. If you want to restrict replenishment orders to once per month, what batch size Q should you use? 6. Using the batch size calculated above, what reorder point r should you use to ensure a service level of at least 98 percent? Conversions Conversions 1 ounce 28.35 g 1 lb 454 g 1 fl oz 1 fl oz 0.125 cup 1 Gallon 256 tbsp 225 g 225 g 16 oz Filling Filling Recipe Butter 113.5 g Icing Sugar 60 g Vanilla Extract 0.5 tsp Strawberry Jam 0.25 cup Cake Cake Recipe Granulated Sugar Butter Eggs All Purpose Flour Baking Powder Vanilla Extract Milk 6 tsp 4 ct 8 oz 225 g 3 tsp 2 tsp 6 Tbsp Ingredient Size Unit Price Ingredient Size Price Granulated Sugar 4 lb $ 2.00 Butter $ 3.19 Cage Free Eggs 12 ct $ 2.49 All Purpose Flour 5 lb $ 2.09 Baking Powder $ 1.35 Vanilla Extract 2 fl oz $ 6.15 2% Milk 1 gal $ 2.19 Icing Sugar 32 oz $ 1.89 Strawberry Jam 18 oz $ 1.99 Labor 1 hour $12 Electricity 1 kWh $ 0.11 Current Inventory Icing Sugar 16 oz Butter 2 lbs Eggs 12 Flour 3 lbs Baking Powder 4 oz Vanilla Extract 2 fl oz Strawberry Jam 18 oz Granulated Sugar 2 lbs 2% Milk 0.5 gal Oven wattage 3000 Watts/hr Stand mixer 250 Watt/hr Batter and filling tim 15 minutes Bake time 35 minutes Cakes (frozen) 20 Completed o 8 1 15 2 20 3 30 4 10 5 30 6 25 7 30 25 0 1 2 3 4 5 6 7 8 0 1 2 3 4 5 6 7 8 Cake Demand On-hand Net Requirements Planned Receipt LT = 1 week Flour Demand On-hand Net Requirements Planned Receipt (EOQ) LT = 1 week Butter Demand On-hand Net Requirements Planned Receipt LT = 1 week Sugar Demand On-hand Net Requirements Planned Receipt LT = 2 weeks Milk Demand On-hand Net Requirements Planned Receipt LT = 1 week 0 1 2 3 4 5 6 7 8 0 1 2 3 4 5 6 7 8