Instructions: Answer the following questions using the Analytic Solver Platform Excel add-in.

Jim Johnson operates a party bus service to take college students on the tour of "The Big City" on Friday nights and bring them back to school on Sunday night. The bus has 45 seats but sometimes there are empty seats. His records show that about 5% of ticket holders do not show up for their ride. Tickets cost $20 and are non-refundable. If Jim overbooks the bus and more than 45 passengers show up, some of them will be bumped and have to miss the trip. This bumping costs the company $40 because Jim has a double-your-money back policy for bumped passengers. Jim has maintained the historical demand data for the seats and the analysis of the data reveals the following demand distribution:

Demand for Seats	41	42	43	44	45	46	47	48	49
Probability	0.04	0.07	0.11	0.13	0.20	0.23	0.10	0.06	0.06

1. Prepare a spreadsheet model to help Jim Johnson analyze expected marginal profit for various number of seats booking/overbooking policy.
2. Clearly label your answers and spreadsheet cells.
3. Use a seed number value of "1234" to run your simulation and run 1000 trials of simulation for each run
4. How many seats Jim should book to maximize his expected marginal profit?
5. Create a table of simulation runs with each number of seats and corresponding expected marginal profits along with minimum and maximum marginal profits.
6. Clearly identify the optimal number of seats which will allow Jim to maximize his expected marginal profits.