Assignment 3: Simulation

Instructions for Standard Printouts of @Risk Simulation Models

The first four problems 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 should add annotations indicating the answer to whatever problem was posed in the homework problem. These annotations may be done by hand or in Excel. An example annotation: "Scenario 5 has the highest average profit, and corresponds to a stocking level of 75. Therefore I would recommend stocking 75 calendars to achieve the highest expected profit."

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.

Problem 1 (Team): Computer Support Problem

Your company has 200 personal computers. Each computer has a 5% probability, independent of all other computers, of encountering a hardware problem on any given day. Most hardware problems require 3/4 of an hour of attention from a computer support technician (CST). Each problem that occurs, however, has a 30% chance (independent of all other problems) of developing into a more serious problem requiring an extra 2 hours of attention from a CST.

Each CST can spend up to 6 hours per day fixing problems, and costs the company $300 per day. Every CST hired must be paid their full salary each day, whether or not they have sufficient work to keep them busy. If the company does not have enough CST's to solve all the problems occurring on a given day, it hires an outside firm that charges $95 per hour. You are trying to decide whether to hire 1, 2, 3, or 4 CST's.

Which option gives the lowest average cost per day? With this number, what is the average number of hours of support per day you must buy from the outside firm?