aph & F W Qucation 2. puto Arrange THOUT THE SA A battery manufacturer is considering developing a new electric-vehicle battery, called ATL battery. Based on a preliminary study, the manufacturer made the following estimates. Pane The fixed cost of developing the ATL battery follows a triangular distribution with a minimum of $110 million, most likely $120 million, and a maximum of $135 million; note that fixed cost is incurred only once. The manufacturer will produce and sell ATL batteries for 5 years. See more details below. Rotate a) Demand. The demand for the battery in year 1 follows a Normal distribution with a mean of 100,000 and a standard deviation of 5,000. In each year after year 1, the demand follows a normal distribution with a mean equal to the actual demand in the previous year and a standard deviation of 5,000. * b) Selling price (per unit) in year y (where y = 1, 2, 3, 4, 5) is denoted by py. The selling price in year 1 is $950, i.e., P? = $950. In each year after year 1, ie., y ? 2, the selling price py is Py = 1.04 [Py-1 + $500 * qy-1)], where qy-1 is calculated by [actual demand - expected demand] / expected demand in the previous year, i.e., y ? 1, y ? 2. MS3106 Final.docx ... For example, if year I's actual demand is 90,000 and year I's expected demand is 100,000, then q? = (90000 100000)/100000 = -10%, and year 2's selling price is 1.04 *[$950+$500*(-10%)]=$936. c) Production quantity is equal to the expected demand in each year. If production quantity exceeds the actual demand, the exceeded batteries are sold at the actual selling price of that year multiplied by a deflation factor. The deflation factor is 0.8 with a probability 0.35, 0.75 with a probability of 0.4, and 0.7 with a probability of 0.25. d) Variable production cost (per unit) in year 1 is $400 with a probability of 0.3, $420 with a probability of 0.45, and $440 with a probability of 0.25. In each year after year 1, the variable production cost is equal to the actual variable production cost in the previous year times an inflation factor; and this inflation factor follows a Normal distribution with mean 1.05 and standard deviation 0.01. Hint: EXCEL FUNCTIONS: NORM. INV(), STDEV(), AVERAGE(), VLOOKUP(), and others (Save your model Q1.xlsx and submit it on Canvas. Your results reported here might differ from your submitted Excel file, which is fine.) Q1.xlsx - Excel

A battery manufacturer is considering developing a new electric-vehicle battery, called ATL battery. Based on a preliminary study, the manufacturer made the following estimates. The fixed cost of developing the ATL battery follows a triangular distribution with a minimum of $110 million, most likely $120 million, and a maximum of $135 million; note that fixed cost is incurred only once. The manufacturer will produce and sell ATL batteries for 5 years. See more details below. a) Demand. The demand for the battery in year 1 follows a Normal distribution with a mean of 100,000 and a standard deviation of 5,000. In each year after year 1 , the demand follows a normal distribution with a mean equal to the actual demand in the previous year and a standard deviation of 5,000 . b) Selling price (per unit) in year y (where y=1,2,3,4,5 ) is denoted by py?. The selling price in year 1 is $950, i.e, p1?=$950. In each year after year 1 , i.e, y?2, the selling price py? is py?=1.04?[py?1?+$500?qy?1?)], where qy?1? is calculated by [actual demand - expected demand] / expected demand in the previous year, i.e, y?1,y?2. For example, if year 1 's actual demand is 90,000 and year 1 's expucted demand is 100,000 , then q1?= (90000?100000)/100000=?10%, and year 2 's selling price is 1.04?[5950+5500?(?10%)]=$936. c) Production quantity is equal to the expected demand in each year. If production quantity exceeds the actual demand, the exceeded batteries are sold at the actual selling price of that year multiplied by a deflation factor. The deflation factor is 0.8 with a probability 0.35,0.75 with a probability of 0.4 , and 0.7 with a probability of 0.25 . d) Variable production cost (per unit) in year 1 is $400 with a probability of 0.3,$420 with a probability of 0.45 , and $440 with a probability of 0.25 . In each year after year 1 , the variable production cost is equal to the actual variable production cost in the previous year times an inflation factor, and this inflation factor follows a Normal distribution with mean 1.05 and standard deviation 0.01 . Hint: EXCEL FUNCTIONS: NORM.INVO, STOEVQ, AVERAGEQ V, VLOOKUPQ, and others (Save your model Q1.xlsx and submit it on Canvas. Your results reported here might differ from your submitted Excel file, which is fine.) (a)(30pts) Run the simulation for 500 replications, estimate the mean, standard deviation, and 95% confidence interval of the manufacturer's net profit in developing and selling the ATL battery for five years. Report your results below. Answer: Mean: Standard deviation: 95% Confidence interval: (b)(10pts) Calculate the frequency of the manufacturer's net profit, plot a bar chart, and estimate the probability that the net profit is greater than $150 million. Report your results below. Answer: Freuquency: = FREQUCENCY( The probability that the net profit is greater than $150 million is %. (c) (10pts) Suppose a new production plan is to produce ? times the expected demand in each year, where ? can be 0.8,1.0,1.2,1.4,1.6,1.8,2.0. Note that all other parameters remain unchanged and run the simulation for 500 replications for each case. Report the average net profit and the corresponding risk for each ?. Make a bar plot of the average net profit and the corresponding risk for each ?. Which production plan is recommended? Answer: average net profit and the corresponding risk for each