Transcript Harry`s Auto Tire Shop

Managerial Decision Modeling with
Spreadsheets
Chapter 10
Simulation Modeling
Learning Objectives
• Understand basic steps of conducting a simulation.
• Explain advantages and disadvantages of simulation.
• Tackle wide variety of problems by simulation.
• Set up and solve simulation models using Excel’s
standard functions.
• Use Crystal Ball add-in for Excel to solve simulation
models.
2
10.1 Introduction
• What is Simulation?
– Try to duplicate features, appearance, and
characteristics of real system.
• Idea behind Simulation
– Imitate real-world situation mathematically.
– Study its properties and operating characteristics.
– Draw conclusions and make action decisions based
on results of simulation.
3
Process of a Simulation
4
10.2 Advantages And Disadvantages
Of Simulation
Advantages
• Relatively straightforward and flexible.
• Used to analyze large and complex real-world
situations.
• Allows “what-if ? ” types of questions.
• Does not interfere with real-world system.
• Allows study of interactive effects of individual
components or variables to determine which ones are
important.
• Time compression.
• Allows for inclusion of real-world complications. 5
10.2 Advantages And Disadvantages
Of Simulation
Disadvantages
• Good models can be very expensive.
• Often it is a long, complicated process to develop
model.
• Does not generate optimal solutions to problems.
• Managers must generate all of conditions and
constraints for solutions to be examined.
• Each simulation model is unique and not easily
transferable.
6
10.3 Monte Carlo Simulation
• Applicable when system contains elements that
exhibit chance behavior.
• Experimentation based on chance elements through
random sampling.
• Steps of Monte Carlo Simulation – Set up probability distribution for each variable in
model subject to chance.
– Use random numbers to simulate values from
probability distribution for each variable in Step 1.
– Repeat process for series of replications or trials.
7
Step 1 - Establish Probability Distribution
for Each Variable
• Basic idea in Monte Carlo simulation is to generate
values for variables in model being studied.
• Example of these variables are:
–
–
–
–
–
–
–
Product demand.
Lead time for orders to arrive.
Times between machine breakdowns.
Times between arrivals at a service facility.
Service times.
Times to complete project activities.
Number of employees absent from work each day.
• To establish probability distribution for given variable,
one can examine historical outcomes of variable.
8
Harry's Auto Tire Shop Example
•
•
•
•
•
Monthly demand for radial tires over past 60 months.
Assume past demand rates will hold in future.
Convert data to probability distribution.
Divide each demand frequency by total number of months 60.
Distributions can either be empirical or known such as normal,
binomial, Poisson, or exponential patterns.
Demand for Tires
Frequency
Probability
300
3
3/60 = 0.05
320
6
6/60 = 0.10
340
12
12/60 = 0.20
360
18
18/60 = 0.30
380
15
15/60 = 0.25
400
6
6/60 = 0.10
60
1
9
Step 2 - Simulate Values From the
Probability Distributions
Harry’s Auto Tire Shop
• Simulate demand for a specific month?
• Actual demand value is 300, 320, 340, 360, 380, or
400.
• There is 5% chance monthly demand is 300,
– 10% chance that it is 320.
– 20% chance that it is 340.
– 30% chance that it is 360.
– 25% chance that it is 380.
– 10% chance that it is 400.
10
Step 2 - Simulate Values From the
Probability Distributions
Harry’s Auto Tire Shop
• For long run Expected monthly demand= S (demand Di) x
(probability of Di)
= (300)(0.05) + (320)(0.10) + (340)(0.20) +
+ (360)(0.30) + (380)(0.25) + (400)(0.10)
= 358 tires
• In short term, occurrence of demand may be quite
different from these probability values.
11
Step 2 - Simulate Values From Probability
Distributions
Harry’s Auto Tire Shop
• Procedure needed to do following:
• Generate random demand values that do not exhibit
any specific pattern.
• Expected value need not necessarily equal 358 tires
per month.
• Generate random demand values that conform exactly
to required probability distribution.
• Expected value must equal 358 tires per month.
12
Random Numbers
• In simulation, use random numbers to achieve
preceding objectives.
• Random number is number that has been selected by
totally random process.
• Assume generate an integer valued random number
from set 0, 1, 2, …, 97, 98, 99.
• One way to do this would be:
1. Take 100 identical balls and mark each one with
unique number between 00 and 99.
2. Put all balls in large bowl and mix thoroughly.
3. Select one ball from bowl and write down number.
4. Replace ball in bowl and mix again. Go to step 2. 13
Random Numbers
• Instead of balls in bowl, one could have used spin of
roulette wheel that has 100 slots to accomplish this
task.
• Another commonly used means is to choose numbers
from table of random digits such as table of random
numbers.
• Table of random numbers appears on next slide.
14
Table of Random Numbers
15
Using Random Numbers to
Simulate Demand
Harry’s Auto Tire Shop
• Converting probability distribution in table to
cumulative probability distribution.
• Cumulative probability for each demand level is sum
of probability of demand and all demands less than
that demand.
• Cumulative probability for demand of 340 tires is sum
of probabilities for 300, 320, or 340 tires.
• Obviously cumulative probability for demand of 400
tires ( maximum demand) is 1.
16
Using Random Numbers to
Simulate Demand
Harry’s Auto Tire Shop
Demand for Tires
Probability
Cumulative Probability
300
0.05
0.05
320
0.10
0.05+0.10=0.15
340
0.20
0.15+0.20=0.35
360
0.30
0.35+0.30=0.65
380
0.25
0.65+0.25=0.90
400
0.10
0.90+0.10=1.00
17
Using Random Numbers Simulate Demand
Harry’s Auto Tire Shop
• Use cumulative probabilities to assign random
numbers.
• Random numbers are two-digit numbers from 00 to
99.
• Create random number intervals by assigning these
100 random numbers to represent different possible
demand values.
• Since there is 5% probability that demand is 300 tires,
assign 5% of random numbers to denote this level of
demand.
• Assign first five random numbers possible (namely,
00, 01, 02, 03, and 04) to denote demand of 300 tires.
18
Step 3 - Repeat Process for
Series of Trials
Harry’s Auto Tire Shop
• Very risky to draw any hard and fast conclusion
regarding simulation model from only few simulation
trials.
• Expected demand is 358 tires per month.
• It is likely one will get different values for average
from short simulation of few months.
• Run simulation model for several thousand trials in
order to gather meaningful results.
19
10.4 Role Of Computers In Simulation
Easier to simulate by computer than manual procedure.
1. Computer software have built-in procedures for
generation of random numbers.
2. Easy to simulate values from many probability
distributions rather then manual process.
3. For simulation results to be valid, it is necessary to
replicate process thousands of times which is matter
of seconds using software packages.
4. Keep track of several input parameters and output
statistics is easier with software packages in any
simulation model.
20
Types of Simulation Software Packages
• General-Purpose Programming Languages.
– VisualBasic, C++, and FORTRAN
• Special-Purpose Simulation Languages and
Programs.
– GPSS/H, Simscript II.5, SLAM II, and GASP, Extend,
MicroSaint, BuildSim, AweSim, ProModel, and Xcell.
• Spreadsheet Models.
– Generate random numbers and use them to select values
from several probability distributions makes spreadsheets
excellent tools for conducting simulations.
21
Random Generation From Probability
Distributions Using Excel
• Generate Random Numbers in Excel.
– Excel uses RAND function to generate random numbers.
– Format for using function is: = RAND ( )
– It returns random value between 0 and 1 (actually between
0 and 0.9999...) each time calculate key (F9 key) is
pressed.
• Generate various distributions in Excel.
– Uniform Distribution: = a + ( b - a ) * RAND ( )
– Normal Distribution: = NORMINV (RAND ( ), m, s )
– Exponential Distribution: = - m * LN (RAND ( ) )
22
10.5 Using Simulation To Compute
Expected Profit
Harry’s Auto Tire Shop
• Monthly demand of auto tires is 300, 320, 340, 360,
380, or 400.
• Average selling price follows discrete uniform
distribution between $60 and $80.
• Profit margin follows continuous uniform distribution
between 20% and 30%.
• Fixed operating cost is $2,000 per month.
• Using this information, simulate and calculate
average profit per month from sale of auto tires.
23
10.5 Using Simulation To Compute
Expected
Profit
Harry’s Auto Tire Shop
• Using this information, simulate and calculate
average profit per month from of auto tires.
Probability of Demand for Radial Tires
Demand
Probability
300
320
340
360
0.05
0.10
0.20
0.30
380
400
0.25
0.10
24
Setting Up Model
Harry’s Auto Tire Shop
25
Analyzing Results
Harry’s Auto Tire Shop
• Cells G4 to G203 show monthly profit for 200
replications (months).
• Calculate following statistics.
– Average monthly profit (cell G204) = $4,277.89
– Standard deviation of monthly profit (cell G205)
= $1024.88
• Requires monthly profit of at least $4,000.
• What is the probability for this level of profit?
• Number of months (of the 200 months) in which
profit exceeded $4,000 (shown in cell G206) divided
200 gets probability value (cell G207).
• Shows 59% chance of monthly profit in excess of
26
$4,000.
10.6 Using Simulation For
Inventory Problem
• There are usually two main questions in most
inventory problems:
(1) how much to order, and
(2) when to order.
• In many inventory situations several inventory
parameters are random variables (demand, lead
time).
• This implies that inventory for the item may run out
before next consignment is received, causing
stockout.
27
Simkin’s Hardware Store Example
• Sells electric drills.
• Daily demand for drill is relatively low but subject to
some variability.
• Over past 300 days, actual demand is shown in column 2
of table.
• Actual frequency is converted into a probability
distribution for the variable daily demand (column 3).
Demand for Drills
Frequency
Probability
0
15
15/300 = 0.05
1
30
30/300 = 0.10
2
60
60/300 = 0.20
3
120
120/300 = 0.40
4
45
45/300 = 0.15
5
30
30/300 = 0.10
300
1
28
10.6 Using Simulation For
Inventory Problem
Simkin’s Hardware Store
• Lead time is probabilistic variable.
• Based on past 100 orders, lead time follows discrete
uniform distribution between 1 and 3 days.
• 7 drills in stock, and no orders due.
• Identify order quantity, Q, and reorder point, R, to
reduce total monthly costs.
• Total cost includes following components:
– Fixed ordering cost.
– Holding cost for each drill held in inventory from
one period to next.
– Stockout cost for each drill not available to satisfy
29
demand.
10.6 Using Simulation For
Inventory Problem
Simkin’s Hardware Store
• Fixed cost of placing order with supplier is $20.
• Cost of holding a drill in stock is $0.50 per drill per
month ( $0.02 per drill per day).
• Cost of a stockout is $8 per drill.
• Two decision variables (Q [order quantity], and R
[reorder point] and two probabilistic components
(demand and lead time).
• Using simulation, try different (Q,R) combinations to
see which combination yields lowest total cost.
– First examine policy that has Q = 10 and R = 5.
– Each time inventory at end of day drops to 5 or
less, place an order for 10 drills with supplier. 30
10.7 Using Crystal Ball To Simulate
Inventory Problem
• Crystal Ball 2000, an add-in for Excel, is published
by Decisioneering Inc.
• Reasons for Using Add-In Programs for
simulation.
1. Built-in functions simulate not only probability
distributions but also many other distributions
(such as the binomial, triangular, and lognormal
distributions).
2. Make it easy to replicate simulation several
hundred or several thousand times.
3. Make it easy to collect information on various
31
output measures.
10.8 Using Crystal Ball To Find Best
Reservation Policy
Judith’s Airport Limousine Service.
• Use Crystal Ball to simulate problem to find optimal
number of reservations for trip.
• Nearest airport is 50 miles away.
• On average there are 45 people from Six Mile, South
Carolina (and its vicinity) who need rides to, or rides
from, airport each day.
• Complete data regarding this problem is in text.
32
10.8 Other Types Of Simulation Models
• Simulation models often broken into three categories.
Monte Carlo method, operational gaming, and
systems simulation.
• Operational Gaming
– Simulation involving two or more competing players.
– Examples are military games and business games.
– Allow participants to match management and decisionmaking skills in hypothetical situations of conflict.
• Systems Simulation
– Similar to business gaming allows users to test various
managerial policies and decisions to evaluate effect on
the operating environment.
– Variation of simulation models dynamics of large
systems.
33
Summary
• Discussed concept of simulation as problem-solving
tool, building mathematical model to describe realworld situation.
• Monte Carlo method uses random numbers to
generate random variable values from probability
distributions.
• Used Excel’s functions and Data Table to run
replications of simulation models.
• Used Crystal Ball to develop and run simulation
models.
34
Summary
• Advantages of using add-ins were:
(1) Availability of easy formulas for many
probability distributions.
(2) Ability to set up and run many replications of
model.
(3) Ability to easily collect statistical information.
• Discussed operational gaming and systems
simulation.
35