please help me set this up using Excel. (Class is CHE230)
4. The plant is considering an upgrade to one of the reactors. The managers are considering taking out a loan with a payment of $50,000 per year to pay for the upgrade. The table below describes the current operating conditions and the expected operating conditions once the upgrade is complete. Before After Std. Dev. Before Std. Dev. After Total Production 100000 kg/yr 115000 kg/yr 1000 kg/yr 2000 kg/yr Price of Product $5.00/kg $5.00/kg $1.20/kg $1.20/kg 110000 kg/yr 125000 kg/yr 4000 kg/yr 2000 kg/yr Price of Reactants $1.40/kg $1.40/kg $0.25/kg $0.25/kg $40000/yr $36000/yr $2000/yr $1000/yr Labor $78000/yr $78000/yr $2000/yr $4000/yr Total Reactants Utilities A. Develop an equation describing the revenue, costs, and profit of the reactor. Solve for the yearly profit of the before conditions without considering variations in the cost. (5 points) B. Generate a Monte Carlo simulation with at least 100 runs. Use the NORM.INVO function to generate random numbers for each of the variables. For the price of the product and the price of the reactants, force them to be the same within each run. For example, the price of the product before and the price of the product after for Run I will be equal. The other variables should vary independently of one another in a given run. What is the minimum, maximum, mean, median, 95 percentile, and 5th percentile results for change in profit among these 100+ runs? (10 points) C. Based upon these data, is the investment high, medium, or low risk? Why? (5 points) 4. The plant is considering an upgrade to one of the reactors. The managers are considering taking out a loan with a payment of $50,000 per year to pay for the upgrade. The table below describes the current operating conditions and the expected operating conditions once the upgrade is complete. Before After Std. Dev. Before Std. Dev. After Total Production 100000 kg/yr 115000 kg/yr 1000 kg/yr 2000 kg/yr Price of Product $5.00/kg $5.00/kg $1.20/kg $1.20/kg 110000 kg/yr 125000 kg/yr 4000 kg/yr 2000 kg/yr Price of Reactants $1.40/kg $1.40/kg $0.25/kg $0.25/kg $40000/yr $36000/yr $2000/yr $1000/yr Labor $78000/yr $78000/yr $2000/yr $4000/yr Total Reactants Utilities A. Develop an equation describing the revenue, costs, and profit of the reactor. Solve for the yearly profit of the before conditions without considering variations in the cost. (5 points) B. Generate a Monte Carlo simulation with at least 100 runs. Use the NORM.INVO function to generate random numbers for each of the variables. For the price of the product and the price of the reactants, force them to be the same within each run. For example, the price of the product before and the price of the product after for Run I will be equal. The other variables should vary independently of one another in a given run. What is the minimum, maximum, mean, median, 95 percentile, and 5th percentile results for change in profit among these 100+ runs? (10 points) C. Based upon these data, is the investment high, medium, or low risk? Why? (5 points)
