Q1) Mat Damon owns an independent bookstore in Dubai. Mat sells merchandise items that has steady...

  

Transcribed Image Text:

Q1) Mat Damon owns an independent bookstore in Dubai. Mat sells merchandise items that has steady demand in his store. One of these items are notebooks, which Mat manages using a continuous review policy. Notebooks are purchased from an overseas supplier for $10 per item. Minimization of inventory costs is his objective. Demand for notebooks in the last 5 years are as follows: Demand Year 2013 2014 2015 2016 2017 2018 1700 1880 1689 1835 1758 1860 Mat thinks that demand is stable over the years and plans to use either 2 years moving average or exponential smoothing with smoothing constant a=0.3 to forecast the demand. He starts exponential smoothing method with smoothed value S2013-1700 for 2013. Mat is planning for 2019 deliveries taking the inventory cost into account. Other relevant data for Mat is as follows: Lead time is 9 weeks Annual holding cost is 24% of item cost Ordering cost is $100 per order a) Which forecasting method should Mat use (2 years moving average or exponential smoothing)? Please check Bias, MAD and MAPE of each forecasting method and choose the best performing one. b) What is Mat's annual and weekly demand forecast for 2019 based on the best performing method? c) What is the economic order quantity? EOQ= d) What would be the average time between orders (in weeks)? e) After studying the weekly demand forecast errors, Mat concludes that his weekly demand is normally distributed with mean equaling to weekly demand forecast (your weekly demand forecast in (b)) and standard deviation of 10 (i.e., standard deviation of the weekly forecast error is 10) f) How much safety stock should Mat keep to achieve 90% cycle service level (i.e., stockout probability)? g) What is reorder point Mat should use? h) What is the total cost of managing the inventory? i) How much safety stock should Mat keep if he wants to achieve 80% service level instead of 90%? j) Mat considers using a local supplier for the notebooks. With the new supplier, the ordering cost will increase to $83 but the lead time will be 1 week. How much safety stock should Mat keep to achieve 90% service level with the new supplier? k) What is the total cost of managing the inventory if Mat uses the new supplier? FORMULAE 1 year = 365 days; 1 year = 12 months; 1 month=4 weeks; 1 month=30 days; 1 week=7 days Inventory Formulas EOQ=Q* = 2DS -, Total Cost (TC) = S*D/Q + H*(Q/2 +ss), ss = 2 H NORM.S.INV(0.95) = 1.65 NORM.S.INV(0.90) = 1.28 NORM.S.INV(0.85) = 1.04 = 2√LSD NORM.S.INV(0.92) = 1.41 NORM.S.INV(0.88) = 1.17 NORM.S.INV(0.80) = 0.84 Q1) Mat Damon owns an independent bookstore in Dubai. Mat sells merchandise items that has steady demand in his store. One of these items are notebooks, which Mat manages using a continuous review policy. Notebooks are purchased from an overseas supplier for $10 per item. Minimization of inventory costs is his objective. Demand for notebooks in the last 5 years are as follows: Demand Year 2013 2014 2015 2016 2017 2018 1700 1880 1689 1835 1758 1860 Mat thinks that demand is stable over the years and plans to use either 2 years moving average or exponential smoothing with smoothing constant a=0.3 to forecast the demand. He starts exponential smoothing method with smoothed value S2013-1700 for 2013. Mat is planning for 2019 deliveries taking the inventory cost into account. Other relevant data for Mat is as follows: Lead time is 9 weeks Annual holding cost is 24% of item cost Ordering cost is $100 per order a) Which forecasting method should Mat use (2 years moving average or exponential smoothing)? Please check Bias, MAD and MAPE of each forecasting method and choose the best performing one. b) What is Mat's annual and weekly demand forecast for 2019 based on the best performing method? c) What is the economic order quantity? EOQ= d) What would be the average time between orders (in weeks)? e) After studying the weekly demand forecast errors, Mat concludes that his weekly demand is normally distributed with mean equaling to weekly demand forecast (your weekly demand forecast in (b)) and standard deviation of 10 (i.e., standard deviation of the weekly forecast error is 10) f) How much safety stock should Mat keep to achieve 90% cycle service level (i.e., stockout probability)? g) What is reorder point Mat should use? h) What is the total cost of managing the inventory? i) How much safety stock should Mat keep if he wants to achieve 80% service level instead of 90%? j) Mat considers using a local supplier for the notebooks. With the new supplier, the ordering cost will increase to $83 but the lead time will be 1 week. How much safety stock should Mat keep to achieve 90% service level with the new supplier? k) What is the total cost of managing the inventory if Mat uses the new supplier? FORMULAE 1 year = 365 days; 1 year = 12 months; 1 month=4 weeks; 1 month=30 days; 1 week=7 days Inventory Formulas EOQ=Q* = 2DS -, Total Cost (TC) = S*D/Q + H*(Q/2 +ss), ss = 2 H NORM.S.INV(0.95) = 1.65 NORM.S.INV(0.90) = 1.28 NORM.S.INV(0.85) = 1.04 = 2√LSD NORM.S.INV(0.92) = 1.41 NORM.S.INV(0.88) = 1.17 NORM.S.INV(0.80) = 0.84

Answer rating: 100% (QA)

a To choose the best forecasting method we will compare the bias MAD and MAPE of 2 years moving average and exponential smoothing with alpha 03 2 years moving average Year Forecast Actual Error Bias M... View the full answer