MMA 861 MARCH 2023
Individual Assignment 3: Simulation
If you have any questions regarding this assignment, please feel free to contact Andy Hu
the teaching assistant for this course. Feel free to cc me in your email.
Instructions for Standard Printouts of @Risk Simulation Models
The @Risk 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 simulation, 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 the problems below, submit a non-technical consulting report (approximately ½ a page for each
problem) accompanied by a technical appendix to the portal dropbox. The report should highlight your
findings (e.g. business implications) and be written in language that could be easily understood by an
audience with little knowledge of quantitative models. The technical appendix of the first two questions
should include the formulation of the model, as laid out in class (decisions, objective, constraints), and
the screenshots of both the numerical values, as well as the formulae (accessed by pressing Ctrl + ~) for
your model in Excel, and your actual Excel file. For the last problem, the technical appendix should
include standard printouts of simulation models and your actual Excel file.
Please note that this assignment is due by 8:30 am, April 1st, 2023.
Page 2 of 3
Problem 1 Computer Support
Company XYZ has 200 personal computers. Each working 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. You can assume that a CST always needs to
spend an initial 3/4 hour to know whether a problem is minor or major.
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 CSTs to solve all the problems occurring on a given day, it
hires an outside firm that charges $110 per hour. However, computers being fixed by the outside firm
can only be returned to the company by the end of the next day. For example, if the company sends two
computers to the outside firm on day 1, these two computers will be fixed and returned to Company
XYZ by the end of day 2, and can be operated on day 3.
Each computer not in use (e.g., either being fixed by a CST or the outside firm or on the way to be
returned) results in a daily loss of $250 in profit for the company. Company XYZ’s strategy is to fix the
computers with minor issues with priority each day and send any remaining problematic computers to
the outside firm. You can assume that the outside firm will continue fixing the computers so that the
total number of hours needed to fix the computers is unaffected by who is performing the maintenance.
You are trying to decide whether to hire 1, 2, 3, or 4 CSTs.
Simulate a 100-day period with a sample size of 1000. You can assume that all 200 computers are
working properly at the beginning of day 1.
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?
Problem 2. Coffee Shop (SIMUL8)
(Part 1) Your friend Alan manages a coffee shop and has gathered the following information. During the
opening hours (8:00 am to 5:00 pm, Monday to Sunday), customers arrive at a rate of 10 per hour
according to a Poisson distribution. On average, Alan can serve 12 customers per hour. His service times
can be described by an exponential distribution. Alan is concerned about the number of customers waiting
in line. He has asked your help in figuring out the following system characteristics:
(a) Average system utilization (i.e. percentage of time that Alan is busy).
(b) Average number of customers waiting in line.
(c) The average time a customer spends waiting in line.
(d) The average time a customer spends in the system.
(Part 2) Alan decides to hire his niece Catherine to work part-time this summer. Catherine will greet each
customer, help the customer place an order and make payments, and relay the order information to Alan.
Alan will focus solely on fulfilling the coffee order. Alan estimates that because of this streamlined
operation, he can now serve 14 customers per hour, while Catherine can serve 18 customers per hour.
Their service times are independent and each follows an exponential distribution. Once again, he has asked
your help in figuring out the following system characteristics:
Page 3 of 3
(a) Average utilization of Catherine.
(b) Average utilization of Alan.
(c) Average number of customers waiting in line (Hint: customers may need to wait at Catherine’s
station and/or at Alan’s station).
(d) The average time a customer spends waiting in line
Build a SIMUL8 model to answer the above questions. Set the “number of runs in trial” to 1,000. Please
append printout(s) of your SIMUL8 setup (including Clock Property, any routing rules, and properties of
building blocks and resources) and results (KPIs) along with answers to the above questions.