This is an individual assignment and no aspect of it should be discussed any anyone else, including teammates. Use @Risk to analyze the situation described in the file below by answering each of the

Based on historical data, the company knows the tablets average time to failure is 1.85 years with a standard deviation of 1 year. They estimate that the time to failure of a new tablet can be modeled with the gamma distribution. Recall from SCM 842 that the gamma distribution requires two parameters, a and b , which can be calculated from the mean (MD) and standard deviation (SD) using the formulas a = MD^2/SD^2 and b = SD^2/MD.

1. Build an @Risk simulation model and use it to find the mean number of replacements under warranty HuffCo can expect to provide for a given sale, using 10,000 iterations.

2. Calculate the expected NPV of profit (revenue minus cost, including for any replacements) from a sale, using a discount rate of 6%.

Note: As always, it may be helpful to create an Influence diagram to help you then create the Excel model for this problem. It is especially useful to do this when a team is involved, as it is an easier place to discover differences of opinion in what should be involved in the model than in the model itself. You should not include the influence diagram with your submission, however.

Recall the formula for NPV: future cost( 1 + disc. rate ) time . (Don’t use the native Excel function for NPV as it only works with equally-spaced future costs. Instead, enter the NPV formula directly for the cost of each replacement in your model and then sum the costs for all tablet’s given to a customer in one simulated run.

Note : After defining the gamma distribution using @Risk, you see the distribution (see screenshot below) along with the upper and lower bounds of the 95% confidence interval at [0.563, 3.741]. You can double click the lower bound, 0.563 (years), and change it to 1 to see there is a 20% probability that a tablet will fail within the 1 year warranty period. We can use this to calculate the chances of getting n bad tablets in a row:

1: Original, 0.2 1=20 % 2: 1 st replacement, 0.2 2 = 4 % 3: 2 nd replacement, 0.2 3= 0.8 % 4: 3 rd replacement, 0.2 4=0. 16 % , which is very small Thus, you need only simulate four “lifetimes” in your model since the possibility of more replacements than four is so remote as to have little to no financial impact. But be careful to only include the cost of a replacement when a replacement will actually be given. This means you’ll need to use an =IF() function in column G of the table—actually, a nested =IF() function, since you will need two conditions to each be true before a replacement is given. Otherwise, the cost in column G should be 0.

Use the Excel shell file given in Canvas to get started. Notice that in cells G11 and G12 we are using the =RiskMean() function, even though we haven’t defined a RiskTable like we did in the Build More Docks? practice problem. Here we aren’t trying to run multiple simulations with different input parameters, we’re just running one version of the model. So we don’t need a RiskTable. But we would still like to see the results of running the simulation. That can be done either by opening up the Output report (like we did for the practice problem) or by using =RiskMean() (or similar summary functions in @Risk) with the second argument as “1”. This means to give us the mean from the first (and in this case, only) simulation.