Reference no: EM131473972

Question: Joe is the manager of an electronics store that sells TVs, HiFis, computers, and various other electronic devices. For next month Joe is planning a promotion on a discontinued model of a popular tablet computer, which has been a good seller over the last few months. He plans to run the promotion for 10 days. Joe is able to purchase the tablets from the manufacturer for $350, and he will sell them to his customers for $600. Any tablets that have not been sold at the end of the promotion will be sold to another retailer for $250.Joe can only place one order with the manufacturer, and he must do this before the promotion begins. He doesn't know exactly what the demand will be, and estimates that on any particular day the probability of selling no tablets will be 10%; the probability of selling one tablet will be 15%; the probability of selling two tablets will be 25%; the probability of selling three tablets will be 30%; the probability of selling four tablets will be 15%; and the probability of selling five tablets will be 5%.

He believes that there is a zero probability of selling any more than 5 tablets on any one day.Obviously Joe would like to maximise his profit over the period of the promotion, and in order to do this he must order an appropriate number of tablets from the manufacturer. If he orders too few, he may not have a sufficient number to meet customer demand; if he orders too many, then his stock may exceed customer demand, and he will be forced to pass the tablets on to the other retailer.Create a simulation model in EXCEL to assist Joe in determining how many tablets he should order.Use your simulation model to calculate the average net profit Joe would make for various order quantities, and present your findings in a graph. (You do not need to try each possible order quantity; rather, consider incrementing order quantities in lots of, say, 5. But do simulate over a large range of order quantities; say, from 10 to 50).

You should make sure that you perform enough trials to obtain a reliable estimate of the mean, but also a reasonable estimate of the spread in profits that result from some order quantity (i.e., for each order quantity calculate the standard deviation as well as the mean).Based on your results, what advice would you give Joe? Make sure that you comment not only the mean profit, but also the variability that arises from different order quantities.