The M&M Inc is a Caribbean-based car manufacturer, located in Trinidad and Tobago. The company has been exploring the prospect of introducing a new hybrid car (which uses gas & electricity).

M&M Inc

The M&M Inc is a Caribbean-based car manufacturer, located in Trinidad and Tobago. The company has been exploring the prospect of introducing a new hybrid car (which uses gas & electricity). Top management of the company decided that the introduction of the car will materialise only if there is an 85% chance of a positive net present value, over the next 10 years. There is high start-up cost, estimated between US$300M and US$900M, and follows Uniform probability distribution. Complete the Excel sheets labelled INFORMATION DO NOT change the format in the Excel Sheet.

Forecast production is 100,000 hybrid cars in the first year. But there is some uncertainty in demand during the first year, and management has decided to take 2 approaches to model demand, given below:

1. demand is normally distributed with mean 95,000 and standard deviation 7,000.
2. demand follows a discrete distribution, as given in Table 1

Table 1: Demand for hybrid cars

Other Details

Any year in which

• demand exceeds production, production will be increased by 5% in the following year.
• production exceeds demand, production will be decreased by 5% in the following year and the surplus (cars) will be sold to rental car companies at a 20% discount.
• After year 1, demand will be modelled, using two approaches:
  1. as a normally distributed random variable with a mean equal to the actual demand in the previous year and standard deviation of 1,000.
  2. as a Discrete distributed random variable (see Table 1)
• In year 1, the sales price for the hybrid car is expected to be US$35,000, and the variable cost per car is expected to be US$26,000.
• Both the selling price and variable cost are expected to increase each year at the rate of inflation, which is assumed to be between 3% and 6%.
• M&M uses a discount rate of 9% to discount future cash flows.

Required

1. Create a spreadsheet model for this scenario, for each of the 2 demand distributions – Normal and Discrete (2 + 2 = 4 Marks)
2. Replicate each of the models 500 times, tracking Total NPV (1-way Data Table).

(1 + 1 = 2 Marks)

1. Based on the replications for both cases – when demand is normal and when demand is discrete(done for (b)), calculate the minimum, average, and maximum total NPV M&M can expect if the company decides to produce this car. (HINT: might prove useful if the NPV( ) function is used to discount the profits M&M would earn each year.) (2 Marks)
2. For both models, calculate the probability of M&M earning a positive NPV over the next 10 years? (2 Marks)
3. Should they produce this car? Why? Why not? (1 Mark)
4. Conduct sensitivity analysis if there is variation in the standard deviation, when demand is normally distributed. Do this by generating a two-way Data Table for 500 replications of the when standard deviation ranges from 5,000 to 10,000, in increments of 1000.

(2 Marks)

1. From the two-way Data Table done in part (f), generate an automatic highlight the standard deviation that gives the maximum total NPV (Note: this could result in a change of the value highlighted when the simulation is refreshed) (2 Marks)

HINTS:

1. Among pieces of information given: Production adjustment, Inflation rate, Discount rate & Surplus discount
2. All that is required can be done on the same sheet