Problem 3: Simulating a sales plan

The business that Jane (from Problem 1 and 2) owns is a store that sells electronic equipment that includes tablet devices. A particular model of tablet computer that she sells has been discontinued, and Jane is planning a 10-day sale on this item, during which she hopes to sell all 40 tablets that she has in stock. Any tablets that she has not sold at the end of the sale will be sold to another retailer for $250. She would like to know what price to charge customers for tablets in order that in the average case she would break even.

Jane is not sure what the daily demand will be over the 10-day sales period, but estimates that for a price in the range $400 to $500 dollars, 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%. She believes that there is a zero probability of selling any more than 5 tablets on any one day.

Create a simulation model to help Jane decide what price she should charge customers for the item. Run a sufficient number of simulations to help answer the following questions for Jane:

 What price will allow Jane to break even; i.e., to recoup the total of $14,000 she paid her supplier for the tablets?

 What is the worst case scenario; i.e., what is the biggest loss that Jane can expect to make?

 What is the best case scenario; i.e., what is the biggest profit that Jane can expect to make?

Make sure you explain how you arrived at these answers.

What to submit

Submit the following:

 a brief report containing your results from the simulation, and describing, on the basis of these results, the answers that you would give to Jane, and how you arrived at these answers.

 the spreadsheet containing your simulation model (this spreadsheet should appear in a separate tab of the single EXCEL spreadsheet file that you submit for this assignment).

Your submission will be marked according to the completeness and correctness of your response.