Here are the instructions followed by the question. You can make an assumption. It is allowed. I will appreciate if you can help solve it asap as I need to review so as not to make it looks as if I am submitting your work. Thanks

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.

Note that, due to the random nature of these simulations, it is possible for two people with correct solutions to get slightly differing answers for things like average profit or average cost.Generally, however, you should get the same choice for the optimal strategy unless there are two choices that are very close in average profit/cost.

For each problem below, submit a page consulting report (non-technical), accompanying by a technical appendix. The report should highlight your findings (e.g. business implications) and be prepared as if to be presented to an audience that has little knowledge of quantitative models. The technical appendix should include standard printouts of simulation models .

Question

You have a business heat-treating specialty industrial castings. The number of castings you receive for treatment each day is a Poisson random variable with a mean value of 4.1. You process the castings in a super-high-temperature oven that can hold up to 5 castings. This oven uses a heating element that sometimes fails; the probability of failure is as follows:

Day of Use Failure Probability

1 1%

2 7%

3 9%

4 15%

5 25%

After the fifth day of use, the safety regulations for the oven require that the heating element be replaced even if it is still functioning. On days that the heating element fails, you must wait until tomorrow to reprocess all the castings for that day. Thus, on days that the heating element is working, you have a total processing capacity of up to 5 castings, but on days that it fails, your capacity is effectively 0 castings. You process the castings on a first-come, first-served basis -- The castings waiting to be processed on a given day may be saved in a queue and try to process as many as possible the next day.

You are considering 5 possible policies, parameterized by a number d = 1, 2, 3, 4, or 5. At the end of the day, if the heating element has been in use for d days and has not failed, you replace it. On days when the element fails, you also replace it at the end of the day.

The economics of the operation are as follows:

The heating element costs $800 to replace if it did not fail

When the element fails, it costs $1500 to replace

You receive $200 in revenue each time you finish processing a casting

You estimate that each day that each casting spends waiting to be processed costs you $40 in loss of goodwill, storage costs, etc.

You may assume all other costs and revenues to be negligible.

1. Determine by simulation which value of d gives you the highest expected profit over a 60-day period. You may ignore any costs and revenues from castings left in queue at the end of the period. Use a sample size of at least 500, and assume that you start with a new heating element on the first day.

2. You are also interested in whether the queue of unprocessed castings left at the end of the day exceeds 10 at any time during the 60-day period. With the optimal value of d, what is the probability of this event