O all of the percentages except for the combined

This preview shows page 12 - 13 out of 13 pages.

oAll of the percentages except for the combined shipbuilding inflation growth rate, gc, and the labor shipbuilding inflation growth rate, gl, need to be constrained to be between0% and 100%. For example, column J is the share of shipyard revenue spent on materials as a percentage of the total value of shipments. This must be between 0% and 100%. It can be constrained by using the following modification to the random draw formula above=MIN(1,MAX(NORM.INV(RAND(),J$5,J$6),0))For all the variables calculated using an equation, simply enter that equation in row 21 and copyit down to row 10020.oColumn Z, the unemployment estimate, may also have to be constrained. This variable iscalculated using an equation rather than a random draw, but the formula entered to constrain it is similar to the one listed for a random draw=MIN(1,[EQUATION],0))For any variable which have a fixed value, simply enter that value in row 21 and copy it down to row 10020.
Cost-Benefit Analysis Fall 2017Assignment #212
In rows 9 through 15, calculate the mean, standard deviation, minimum, maximum, 5thpercentile, 50thpercentile (i.e., the median), and the 95thpercentile for each variable using the values in rows 21 through 10020. The can all be calculated using standard Excel functions.On rows 19 and 20 where the cell has not been greyed out, calculate the percent of iterations in lines 21-10020 that are positive and those that are non-positive. For example, column F is the benefits. To calculate the percent of iterations that are positive, use the command=COUNTIF(F$21:F$10020,">0")/10000The division by 10000 is used to scale the count to range between 0 and 1 so that it can be stated as a percentage.Submit your Excel spreadsheet with your Monte Carlo analysis.b.(15 points) Report the summary statistics for the present value of net benefits, PV(NB): Column AH is the calculation of Net Benefits for this Monte Carlo analysis. Rows 9–18 contain the summary statistics and percent of the observations that are positive and non-positive. Report the summary statistics and percentages here. c.(15 points) Provide a commentary (a paragraph or two) on this Monte Carlo analysis. (There is no specific answer to this part. You will be evaluated on how well you analyze this as an economist.)Please include the follow points in your discussionHow would you describe these results of this analysis to a decision maker?Does this analysis justify a recommendation of eliminating the WSF requirement?How would you characterize your certainty of this recommendation?Are there reasons beyond pure benefit-cost analysis and welfare calculations that might affectthe choice of whether or not this recommendation makes sense?Cost-Benefit Analysis Fall 2017Assignment #213

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture