This is a simulation question. The question should use a risk and simulation standard printouts.

Here are the instructions followed by the question.

Along with your word file, please include an excel file with 3 sheets.

Each question should use @Risk and the Simulation standard printouts should consist of three sheets.

The first sheet is a values printout of your spreadsheet, showing your model as it normally appears on the screen. Note that the numbers shown on this printout reflect the outcome of a single recalculation of the spreadsheet, that is, a sample of size 1. That means it doesn't tell you what the best answer is, the way the values printout does for an optimization model -- it's just a single possible realization of what might happen.

The next sheet is a formulas printout of your spreadsheet model, with each cell showing a formula rather than a value. Unlike optimization models, it is not necessary to put annotations on this sheet.

For simulations that contain large numbers of similar rows, it is OK to omit the repetitive rows from the values and formulas sheets by truncating the printout or using the "hide rows" command (select the rows to hide, right-click your selection, and then click Hide).

Both the values and formulas printout sheets should have row and column headings (A, B, C, ... along the top and 1, 2, 3, ... along the side).

The third sheet is the @RISK simulation output report for your model.

To the output report printout, you may add annotations indicating the answer to whatever problem was posed in the problem.

Question.

The automobile insurance division of the Mutual insurance company expects an average of 1,000 claims in the forthcoming year, with the actual number of claims being random and well described by a Poisson distribution. The value of each claim is a random variable, independent of all other claims, with a mean of $5,200 and a standard deviation of $1,500.

The division has $7 million of capital, which is split into two parts. The first part is the reserve capital needed to pay claims over the next year. The remainder is invested in short-term bonds, which provide a random return, equally likely to be any value between 3% and 8%. If the reserve capital turns out to be less than the total value of claims for the year, the division has to borrow enough money, at a cost of 10% of the amount borrowed, to make up the difference.

The firm would like to find a capital allocation that maximizes the expected amount of cash they have left at the end of the year. Suppose they have narrowed down their choice to the following possible amounts of reserve capital: $5.0 million, $5.1 million, $5.2 million, $5.3 million and $5.4 million.

1. Based on 1,000 simulation trials (iterations) each, which option is the best?

2. Brief summary of your results, describe your findings

3. What additional considerations should management make