Question 1: (30 marks)

Star Motors is considering whether to introduce a new car model called the Cheetah. The profitability of the Cheetah will depend on the following factors:

a. Fixed cost of developing the Cheetah: Fixed cost is equally likely to be $3 or $4 billion.

b. Sales: Assume that year 1 sales will be normally distributed with mean 150,000 and standard deviation 55,000. Sales are equally likely to increase for 2, 3, 4 years, with the average percentage increase during those years following a discrete random variable having 5% with probability 0.4, 6% with probability 0.3, and 7% with probability 0.3.

c. Price: Assume year 1 price is $13,000. The sales price will be increased by 5% with probability 0.4 and 8% with probability 0.6 each year.

d. Variable cost per car: variable cost is equally likely to be $6000, $7000, $8000, or $9000 during year 1 and is assumed to increase by 5% each year.

Simulate 500 trials and estimate the mean and standard deviation of the net profit for the first four years of sales and development for the Cheetah. What is the percentage that the total net profit is exceeding 0.5 billion?

EXCEL FUNCTIONS: NORMINV(), STDEV(), AVERAGE()

Question 2. (20 marks)

Consider the Walton Book Case, suppose that

the demand distribution is as follows:

D=200 with probability 0.15, D=300 with probability 0.25, D=400 with probability 0.3,

D=500 with probability 0.2, D=600 with probability 0.1.

The selling price is $12, the ordering cost is $6 and refund price for unsold units is $3.

Find the optimal quantity so as to maximize the average profits.

2.i) Input the problem data (prices and the initial order quantity, say 400)

ii) Construct the vlookup table based on the cumulative distribution starting with 0;

iii) Use the vlookup function and Rand() to simulate demand;

v) Then use the two-way data table to generate the profits under ordering quantities,200, 300, 400, 500, 600 with 500 samples for each order quantity

vi) Find the optimal order quantity and plot the histogram of the profits under the optimal order quantity.

Once you are done, please upload two excel files.

Question 3. (50 marks) At a product assembly line, jobs arrive to the facility with an inter-arrival time exponential (2) minutes. Each job must pass through two production stages: stage 1 and stage 2. Stage 1 takes an exponential (1.2) minutes to complete and 1 worker is available to perform stage 1. After completing stage 1, the job immediately passes to stage 2. Stage 2 has two machines, machine 1 and machine 2, working on the jobs. Each machine at stage 2 takes a triangular (1.1, 1.2, 1.5) minutes to complete a job. The machines have separate lines, and the jobs will be routed to the line with the fewest number of jobs waiting in queue, ignoring whether any job is in service. If there is a tier, it will be routed to machine 1. After completing stage 2, each job is inspected. Inspection takes an exponential (1.2) minutes, and 1 worker is available to perform inspection. After inspection, 8% of the jobs must be returned to stage 2 for reprocessing. The rest 92% of the jobs are finished products and leave the system. The assembly line is running 12 hours a day.

(20 marks) Run the simulation for 60 days. Report the following performance indicators, both mean and 95% confidence interval: the cycle time (or) total time for each job. (Save your model as q2a.doe) Interpret the meaning of 95% confidence interval.

(5 marks)What percentage of time the worker at stage 1 is busy (or utilization)?

(15 marks) Now suppose that a consultant recommends the assembly line to use a single line for the two machines in stage 2. Run the simulation for 60 days. What is the average cycle time for this setting? (Save your model as q2c.doe)

(10 marks) Now that at stage 1, due to the space constraint the queue length for stage 1 cannot exceed 10 jobs. If there is already 10 jobs in the queue at stage 1, the arriving job will be discarded. Under this modification, run the simulation for 60 days. Report the following performance indicators, both mean and 95% confidence interval: the cycle time (or) total time for each job. (Save your model as q2d.doe)

TIPS: we can use NQ(Process 1.Queue) for the number of customer in Process 1.Queue.