The material requirements and the CO2 intensity of the mater…
The material requirements and the CO2 intensity of the materials used in the production of Product A are normally distributed and are as follows. The normal distribution in excel can be simulated as follows =NORMINV(RAND(),mean,sd) Please email an excel with file name “CEE582_Exam 2_FirstName_Lastname” and with the Montecarlo values and scatter and tornado plots. There should be two tabs in the excel: (i) “Exam 2 Q2 Scatter plot” (ii) “Exam 2 Q2 Tornado plot” Input item Mass requirement – mean (kg per piece of product A) Mass requirement – sd (kg per piece of product A) CO2 intensity – mean (kg/kg item) CO2 intensity – sd (kg/kg item) Item 1 120 20 40 10 Item 2 60 15 30 3 Item 3 22 5 8 1 a) Using a Monte Carlo simulation of 2000 runs, plot a scatter plot of the total CO2 footprint of A versus the CO2 footprint of the three input items. Plot the X axis to vary from 0 to 14000 (item1, item 2 and item 3) and the Y axis (total CO2 footprint of A) from 0 to 14000. Based on a visual examination, determine which input item drives the total CO2 footprint of Product A the most. Justify your answer. (8 points) b) Using the mean values of the mass requirement and the CO2 intensity, plot a tornado plot and determine which input item drives the total CO2 footprint of Product A the most. Justify your answer (7 points) c) Is there is a discrepancy in your observations for the scatter and tornado plots. (yes or no)? If yes, which plot would you agree with? Please justify your answer (5 points).