You work for OJ's Orange Juice. Currently there are 10 million customers, and each week a customer buys 1 gallon of orange juice from OJ or a competitor. The current profi t margin is $2 per gallon. Last week six million customers bought from you, and four million bought from the competition. The fi le OJdata.xlsx gives the purchase history for a year for several customers.
For example, in week three, Person 5 did not buy from you (0 = bought from competition, 1 = bought from you). The data is scrambled so you need to manipulate it. From this data you should figure out the chance customers will buy from you next week if they bought from you last week and the chance customers will buy from you next week if they bought from a competitor last week.
Solve the following situations:
a. Evaluate the profitability of the status quo (for 52 weeks including the current week). No need for discounting!
b. OJ Orange Juice is considering a quality improvement. This improvement will reduce per-gallon profitability by 30 cents. This will increase customer loyalty, but you are not sure by how much. Assume it is equally likely that the customer retention rate will increase by between 0 percent and 10 percent.
Use 10,000 iterations of a Monte Carlo simulation to determine whether OJ should make the quality improvement. Use a single RANDBETWEEN random variable to model the average improvement in customer loyalty created by the quality improvement.