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

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

·   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:

I.  as a normally distributed random variable with a mean equal to the actual demand in the previous year and standard deviation of 1,000.

II. 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

a)  Create a spreadsheet model for this scenario, for each of the 2 demand distributions – Normal and Discrete                                                                                                           (2 + 2 = 4 Marks)

b) Replicate each of the models 500 times, tracking Total NPV (1-way Data Table).

(1 + 1 = 2 Marks)

c) 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)

d)  For both models, calculate the probability of M&M earning a positive NPV over the next 10 years?                                                                                                                        (2 Marks)

e) Should they produce this car? Why? Why not?                                               (1 Mark)

 

f)  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.

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme