Building Monte Carlo Simulation Models in Excel – Prof. Setu Chokshi

Problem 1: MakeMeMoney Inc.
MakeMeMoney Inc. has created a new gizmo. Since they are new in the market. They need to spend time to go from door-to-door to market their gizmo. There are a lot of uncertainties associated with the profitability (market size, expense and revenue). Can you create a Monte Carlo Simulation to help estimate profit and evaluate risk. Nominal Leads per Month (L) Cost Per Lead (C) Conversion Rate (R) Profit per Sale (P) Overhead per Month (H) 1500 $0.50 3.0% $50.00 $800.00 Min 1200 Max 1800 Distribution Uniform

$0.20 $0.80 Uniform 1.0% 5.0% Uniform

$47.00 $53.00 Uniform

Problem 2: MoreRisk Inc.
MoreRisk Inc. too is introducing a product in the market. Imagine that as a marketing manager, you are trying to estimate the profit that will result from the product introduction. Low Probability Units Price Cost 50% 60,000 $10 $6.00 High 50% 100,000 $9 $9.00 80,000 $8 $7.50 Average

Problem 3: Capital Investment in the face of Uncertain Demand
Consider an e-Commerce service for which you can charge $1 with no marginal cost. However, a server facility must be constructed, which will require $0.80 capital for each unit of monthly capacity. Sadly the new service is a fad expected to last for only one month. Demand for your one month of business is uncertain and estimated to be normal with mean = 2 million and sigma = 500,000. Planning for the average demand, you invest $1,600,000 for a capacity of 2,000,000 transactions.

Page | 1

March 22, 2014

Building Monte Carlo Simulation Models in Excel – Prof. Setu Chokshi

Problem 4: Newsvendor Problem
A coffee shop must decide how many newspapers it should order. Each newspaper costs $2 and is sold for $4.25. Any unsold newspapers at the end of the day are returned for a refund of $0.50. The daily demand for newspapers is 100, 150, 200, 250 with probabilities 0.35, 0.2, 0.3, 0.15, respectively.

Problem 5: Reservation Management Example
Marty Ford is an operations analyst for Piedmont Commuter Airlines (PCA). Recently, Marty was asked to make a recommendation on how many reservations PCA should book on Flight 343—a flight from a small regional airport in New England to a major hub at Boston’s Logan airport. The plane used on Flight 343 is a small twin engine turbo-prop with 19 passenger seats available. PCA sells nonrefundable tickets for Flight 343 for $150 per seat. Industry statistics show that for every ticket sold for a commuter flight, a 0.10 probability exists that the ticket holder will not be on the flight. Thus, if PCA sells 19 tickets for this flight, there is a fairly good chance that one or more seats on the plane will be empty. Of course, empty seats represent lost potential revenue to the company. On the other hand, if PCA overbooks this flight, and more than 19 passengers show up, some of them will have to be bumped to a later flight. To compensate for the inconvenience of being bumped, PCA gives these passengers vouchers for a free meal, a free flight at a later date, and sometimes also pays for them to stay overnight in a hotel near the airport. PCA pays an average of $325 (including the cost of lost goodwill) for each passenger that gets bumped. Marty wants to determine if PCA can increase profits by overbooking this flight, and, if so, how many reservations should be accepted to produce the maximum average profit. To assist in the analysis, Marty analyzed market research data for this flight: Seats Demanded Probability 14 0.03 15 0.05 15 0.07 17 0.09 18 0.11 19 0.15 20 0.18 21 0.14 22 0.08 23 0.05 24 0.03 25 0.02

For the first attempt, assume everyone who buys the ticket, shows up, i.e. 0% no-shows. For the second attempt take into account the probability of no-shows.

Page | 2

March 22, 2014