Simulation on Excel

l3o0hd_s

modf6.ppt

Home >Mathematics homework help >Statistics homework help >Simulation on Excel

Operations Management
Simulation
Module F

Outline

What is Simulation?
Advantages and Disadvantages of Simulation
Monte Carlo Simulation
Simulation of a Queuing Problem
Simulation and Inventory Analysis
The Role of Computers in Simulation

Learning Objectives

When you complete this chapter, you should be able to Identify or Define:

Monte Carlo simulation

Random numbers

Random number interval

Simulation software

Explain or be able to use:

The advantages and disadvantages of modeling with simulation

The use of Excel spreadsheets in simulation

Simulation

Numerical technique of experimentation
Attempts to duplicate a system

Features

Behavior

Requires description of system
Many application areas

Operations management

Finance & economics

Some Applications of Simulation

Ambulance location and dispatching	Bus scheduling
Assembly-line balancing	Design of library operations
Parking lot and harbor design	Taxi, truck, and railroad dispatching
Distribution system design	Production facility scheduling
Scheduling aircraft	Plant layout
Labor-hiring decisions	Capital investments
Personnel scheduling	Production scheduling
Traffic-light timing	Sales forecasting
Voting pattern prediction	Inventory planning and control

Simulation

The idea behind simulation is to:

Imitate a real-world situation mathematically
Study its properties and operating characteristics
Draw conclusions and make action recommendations based on the results of the simulation

The Process of Simulation

Define the Problem

Introduce important variables

Construct simulation model

Specify values of variables to be tested

Conduct the simulation

Examine the results

Select best course of action

Advantages of Simulation

Simulation

flexible, straightforward

can analyze large, complex real-world problems for which no closed-form analytical solutions exists

can include real-world complications which most other techniques cannot

enables “time compression”

allows “what if” type questions

does not interfere with the real-world system

allows study of relationships

Disadvantages of Simulation

Simulation:

Can be expensive
and time consuming
Does not yield optimal solution
Requires good managerial input
Results not generalizable to other situations

The Monte Carlo
Simulation Technique

Setup probability distribution for important variables
Build cumulative distribution for each variable
Establish interval of random numbers for each variable
Generate random numbers
Simulate a series of trials

Partial Table of Random Numbers
(upper left corner)

52	06	50	88	53	30	10	47	99	37	66	91	35
37	63	28	02	74	35	24	03	29	60	74	85	90
82	57	68	28	05	94	03	11	27	79	90	87	92
69	02	36	49	71	99	32	10	75	21	95	90	94
98	94	90	36	06	78	23	67	89	85	29	21	25
96	52	62	87	49	56	49	23	78	71	72	90	57
33	69	27	21	11	60	95	89	68	48	17	89	34
50	33	50	95	13	44	34	62	63	39	55	29	30
88	32	18	50	62	57	34	56	62	31	15	40	90
90	30	36	24	60	82	51	74	30	35	36	85	01
50	48	61	18	85	23	08	54	17	12	80	69	24
27	88	21	62	69	64	48	31	12	73	02	68	00
45	14	46	32	13	49	66	62	74	41	86	98	92

Real World Variables Which Are Probabilistic in Nature

Inventory demand
Lead time for orders to arrive
Time 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

Simulation and Inventory Analysis - the Basic Model

Begin

Increase

current inv

by qty order

end inv =

begin-demand

# of

lost sales

End inv = 0

Generate Random lead time

Place

order

Compute averages

Enough Days

in simulation?

Order placed

& not arrived?

End inv < reorder

point?

demand >

begin inv?

Order

arrived?

random #

for today's

demand

Simulation – An Example

Following long trips down the Mississippi River from industrial mid-western cities, fully loaded barges arrive in New Orleans. The inter-arrival times for the barges are given in Dist. 1. In the same table, the cumulative probabilities and corresponding random number intervals are also given. Dist. 2. provides similar information regarding the times taken to unload a barge.

Example: Dist. 1 – Inter-Arrival Times

Time Between Arrivals (Hours)	Probability	Cumulative Probability	Random -Number Interval
36	0.13	0.13	01 – 13
24	0.17	0.30	14 – 30
12	0.15	0.45	31 – 45
8	0.25	0.70	45 – 70
6	0.20	0.90	71 - 90
4	0.10	1.00	91 - 00

Example: Dist. 2 – Unloading Times

Unloading Times (Hours)	Probability	Cumulative Probability	Random-Number Interval
24	0.05	0.05	01 – 05
12	0.15	0.20	06 – 20
8	0.50	0.70	21 – 70
6	0.20	0.90	71 – 90
4	0.10	1.00	91 - 00

Example: Simulating

From Dist. 1

From Dist. 2

From Random

Number Table

Rn	Int Arr Time	Arrival Time	Unloading Starts	Rn	Unloading Time	Unloading Ends	Waiting Time
52	8	8	8	37	8	16	0
06	36	44	44	63	8	52	0
50	24	68	68	28	8	76	0
88	6	74	76	02	24	98	2
53	8	82	98	74	6	104	16
30	24	106	106	35	8	114	0
10	36	142	142	24	8	150	0
47	8	150	150	03	24	174	0
99	4	154	174	29	8	182	20

Example: Some Simple Statistics

Average Time Between Arrivals (Hours)	Average Time to Unload (Hours)	Total Wait Time (Hours)	Average Wait Time (Hours)	Average Time in Port
154/9 hrs	102/9 hrs	38 hrs	38/9 hrs	11.3 + 4.2 hrs
17.1 hrs	11.3 hrs	4.2 hrs	15.5 hrs

52	06	50	88	53	30	10	47	99	37	66	91	35
37	63	28	02	74	35	24	03	29	60	74	85	90
82	57	68	28	05	94	03	11	27	79	90	87	92
69	02	36	49	71	99	32	10	75	21	95	90	94
98	94	90	36	06	78	23	67	89	85	29	21	25
96	52	62	87	49	56	49	23	78	71	72	90	57
33	69	27	21	11	60	95	89	68	48	17	89	34
50	33	50	95	13	44	34	62	63	39	55	29	30
88	32	18	50	62	57	34	56	62	31	15	40	90
90	30	36	24	60	82	51	74	30	35	36	85	01
50	48	61	18	85	23	08	54	17	12	80	69	24
27	88	21	62	69	64	48	31	12	73	02	68	00
45	14	46	32	13	49	66	62	74	41	86	98	92

52	06	50	88	53	30	10	47	99	37	66	91	35
37	63	28	02	74	35	24	03	29	60	74	85	90
82	57	68	28	05	94	03	11	27	79	90	87	92
69	02	36	49	71	99	32	10	75	21	95	90	94
98	94	90	36	06	78	23	67	89	85	29	21	25
96	52	62	87	49	56	49	23	78	71	72	90	57
33	69	27	21	11	60	95	89	68	48	17	89	34
50	33	50	95	13	44	34	62	63	39	55	29	30
88	32	18	50	62	57	34	56	62	31	15	40	90
90	30	36	24	60	82	51	74	30	35	36	85	01
50	48	61	18	85	23	08	54	17	12	80	69	24
27	88	21	62	69	64	48	31	12	73	02	68	00
45	14	46	32	13	49	66	62	74	41	86	98	92

52	06	50	88	53	30	10	47	99	37	66	91	35
37	63	28	02	74	35	24	03	29	60	74	85	90
82	57	68	28	05	94	03	11	27	79	90	87	92
69	02	36	49	71	99	32	10	75	21	95	90	94
98	94	90	36	06	78	23	67	89	85	29	21	25
96	52	62	87	49	56	49	23	78	71	72	90	57
33	69	27	21	11	60	95	89	68	48	17	89	34
50	33	50	95	13	44	34	62	63	39	55	29	30
88	32	18	50	62	57	34	56	62	31	15	40	90
90	30	36	24	60	82	51	74	30	35	36	85	01
50	48	61	18	85	23	08	54	17	12	80	69	24
27	88	21	62	69	64	48	31	12	73	02	68	00
45	14	46	32	13	49	66	62	74	41	86	98	92