Note: I want the question to be solved in excel like the one given below otherwise I will have to dislike your answer.

solve like this in excel

Assignment - Solution - key notes ( solution explanation ) Question No 1- large internet retailer A large internet retailer is about to place an order for LEDS . Each LED is purchased for 1000 and can be sold at a 20 % markup (1200). however, due to the short shelf life of electronics goods. If any of the LED are unsold atthe end of season ,they must be sold off for 600 at a loss of 400. The demand for this product this season is estimated at 150 units with a standard deviation of 50 LEDS. Q 1: how many LEDs should the retailer order in order to maximize expected profit? Q 2: Suppose the supplier decides to offer a buy back contract so that any unsold LEDs are returned to the supplier who refunds the retailer 800 per LEDs. How many more LEDs would the retailer order under the buy back contract relative to the original ? A B 1 2 3 Formula 4 5 6 7 Q.1 Gas Station Average Daily Demand 8000 standard deviation in daily demand 2500 Lead Time 3 Stockout Probability 2% Service level 98.00% z-value 2.054 standard deviation in demand during lead time 4330.127019 Safety Stock 8892.99 Reorder Point 32892.99 8 9 1-Stockout Probability Norm.s.inv(service level) standard deviation in daily demand * sqrt(Lead time) standard deviation of demand during lead time * z-value Daily Demand * (Lead Time) + Safety Stock 10 11 12 13 14 15 16 17 Formula Gas Station 8000 3000 3 18 Q.2 Average Daily Demand standard deviation in daily demand Lead Time Reorder Point Safety Stock standard deviation in demand during lead time z-value Service level Stockout Probability 19 20 Reorder Point - (Average Daily Demand * Lead Time) standard deviation in daily demand * sqrt(Lead time) 32892.99 8892.99 5196.15 1.711 95.65% 4.35% 21 22 23 24 NORM.S.DIST(z, TRUE) 1-Service Level