Here is my work for part A:
Sam's Cat Hotel operates 49 weeks per year, 6 days per week, and uses a continuous review inventory system. It purchases kitty litter for $12.50 per bag. The following information is available about these bags: > Demand = 85 bags/week > Order cost = $50.00/order > Annual holding cost = 40 percent of cost > Desired cycle-service level = 80 percent > Lead time = 2 weeks (12 working days) > Standard deviation of weekly demand = 15 bags > Current on-hand inventory is 320 bags, with no open orders or backorders. a. Suppose that the weekly demand forecast of 85 bags is incorrect and actual demand averages only 65 bags per week. How much higher will total costs be, owing to the distorted EOQ caused by this forecast error? The costs will be $ 13.3 higher owing to the error in EOQ. (Enter your response rounded to two decimal places.) b. Suppose that actual demand is 65 bags but that ordering costs are cut to only $13.00 by using the internet to automate order placing. However, the buyer does not tell anyone, and the EOQ is not adjusted to reflect this reduction in S. How much higher will total costs be, compared to what they could be if the EOQ were adjusted? The costs will be higher owing to the error in EOQ. (Enter your response rounded to two decimal places.) Working Weeks Demand (Weekly Order Cost Holding Cost Cycle-Service Le Lead Time (Weel SD of Demand Current Inventor Cost per Bag Correct Demand 49 85 $50 0.4 0.8 0.841621 2 15 320 $12.50 65 Incorrect EOQ Correct EOQ 289 252 Total Incorrect Total Incorrect Total Total Correct Cos Total Correct Cos Total Difference $720.59 $722.50 $1,443.09 $826.39 $630.00 $1,456.39 $13.30 Working Weeks 49 Demand (Weekly) 85 Order Cost 50 Holding Cost 0.4 Cycle-Service Level (%, Z-Score) 0.8 Lead Time (Weeks) 2 SD of Demand 15 Current Inventory 320 Cost per Bag 12.5 Correct Demand (Weekly) 65 Incorrect EQ Correct EQ =ROUND(SQRT{(2*(B2*B1)*83)/((B4*89))),0) =ROUND(SQRT{(2*(810*81) *B3)/((B4*B9))),0) Total Incorrect Costs 1 Total Incorrect Costs 2 Total = Total Correct Costs 1 = Total Correct Costs 2 Total Difference =(((B2*B1)/812) *B3) =(B12/2)* (B9*B4) =B15+B16 ={[(B2*B1)/B13) *B3) = (B13/2)* (B9*B4)) =B18+B19 =B20-B17 Sam's Cat Hotel operates 49 weeks per year, 6 days per week, and uses a continuous review inventory system. It purchases kitty litter for $12.50 per bag. The following information is available about these bags: > Demand = 85 bags/week > Order cost = $50.00/order > Annual holding cost = 40 percent of cost > Desired cycle-service level = 80 percent > Lead time = 2 weeks (12 working days) > Standard deviation of weekly demand = 15 bags > Current on-hand inventory is 320 bags, with no open orders or backorders. a. Suppose that the weekly demand forecast of 85 bags is incorrect and actual demand averages only 65 bags per week. How much higher will total costs be, owing to the distorted EOQ caused by this forecast error? The costs will be $ 13.3 higher owing to the error in EOQ. (Enter your response rounded to two decimal places.) b. Suppose that actual demand is 65 bags but that ordering costs are cut to only $13.00 by using the internet to automate order placing. However, the buyer does not tell anyone, and the EOQ is not adjusted to reflect this reduction in S. How much higher will total costs be, compared to what they could be if the EOQ were adjusted? The costs will be higher owing to the error in EOQ. (Enter your response rounded to two decimal places.) Working Weeks Demand (Weekly Order Cost Holding Cost Cycle-Service Le Lead Time (Weel SD of Demand Current Inventor Cost per Bag Correct Demand 49 85 $50 0.4 0.8 0.841621 2 15 320 $12.50 65 Incorrect EOQ Correct EOQ 289 252 Total Incorrect Total Incorrect Total Total Correct Cos Total Correct Cos Total Difference $720.59 $722.50 $1,443.09 $826.39 $630.00 $1,456.39 $13.30 Working Weeks 49 Demand (Weekly) 85 Order Cost 50 Holding Cost 0.4 Cycle-Service Level (%, Z-Score) 0.8 Lead Time (Weeks) 2 SD of Demand 15 Current Inventory 320 Cost per Bag 12.5 Correct Demand (Weekly) 65 Incorrect EQ Correct EQ =ROUND(SQRT{(2*(B2*B1)*83)/((B4*89))),0) =ROUND(SQRT{(2*(810*81) *B3)/((B4*B9))),0) Total Incorrect Costs 1 Total Incorrect Costs 2 Total = Total Correct Costs 1 = Total Correct Costs 2 Total Difference =(((B2*B1)/812) *B3) =(B12/2)* (B9*B4) =B15+B16 ={[(B2*B1)/B13) *B3) = (B13/2)* (B9*B4)) =B18+B19 =B20-B17
