UNIVERSITY OF EL SALVADOR

SCHOOL OF ENGINEERING AND ARCHITECTURE

SCHOOL OF INDUSTRIAL ENGINEERING

OPERATIONS RESEARCH II

SIMULATION IN SPREADSHEETS

Ing. Georgeth Rodriguez

AR14009 Ábrego Rivas, Francisco Andrés

MP14014 Diana Michelle Márquez Portillo

PH14003 Pérez Hidalgo, David Alejandro

group_number 33

University City, June 2, 2017

 Index

Introduction

Objectives....................................................................................................................4

Simulation in Spreadsheets:................................................................................5

Exercise 1:..............................................................................................................9

Exercise 2:............................................................................................................14

Exercise 3:............................................................................................................19

Conclusions:..........................................................................................................22

2
Introduction

The simulation is the contextual artifice that references the research of a

hypothesis or a set of working hypotheses using models. Nowadays with
the high growth rate of technologies can simulate almost anything
thing, from the number of people that will be in a bank line to the
trajectory of a celestial body over the years. In simple terms, simulate
it is to recreate a phenomenon under certain conditions that allow us to control it and
vary the parameters that the analyst wishes to determine which are the
ideal conditions to bring that model to real life. There are different
ways to simulate, some more complex than others; on this occasion we
will expand the use of spreadsheets for this purpose. As its name
This tool is useful for obtaining and managing data.
quantitative, such as the price of a product, the growth of a country, the number
of people who use a certain service, etc. The simulation in sheets of
calculations are based on the management of different probability distributions,
depending on the type of problem, the probabilistic model will be selected accordingly
adequate. Another feature of the simulation is that data can be used
random to get a complete overview of the different scenarios
that can be developed when running a model. That is why
The spreadsheet tool has a feature that generates numbers.
random based on the parameters that the analyst deems relevant. Of
In the same way, a series of examples are developed to clarify and deepen the
methodology to follow for obtaining data that aids in decision-making
decisions.

3
Objectives

General objective:

Predicting the behavior of stochastic systems with runs of

simulation in spreadsheets when new designs are to be used or
operation policies

Specific objectives:

Facilitate the appreciation of the nature and behavior of a

determined system.
Provide statistical estimates and compare alternatives for
generate an optimal solution.
Understand the most common features of spreadsheets in the
simulation to generate random numbers, perform logical tests,
statistics, financial, etc.
Obtain graphs, tables, and other data that allow for analysis of the
behavior of a system under different parameters and sets
conditions.
Present consistent solutions to problems where techniques
analyses are inadequate or that they are supplemented with simulation in
spreadsheets.
Provide a way to experiment with policies or systems
proposed without having to make changes to the real system.

4
 Simulation in Spreadsheets:
Spreadsheet software turns out to be very useful tools when
it talks about simulations, as they greatly facilitate the generation processes
of random numbers and control through their different functions. For
start working with spreadsheets to solve models of
we will start by answering the following questions:

What is a simulation?

Simulation is the development of a logical-mathematical model of a system,

way to obtain an imitation of a system process through the
time. Therefore, the simulation involves the generation of an artificial story
of the system and the observation of this story through manipulation
experimental; moreover, it helps us infer the operational characteristics of such
system.

Consequently, it turns out that simulation is one of the quantitative processes.

more widely used in decision-making, as it serves to learn the
related to a real system through experimentation with the model that it
represents. Thus, the objective will be to create an environment in which one can
obtain information about possible alternative actions through the
experimentation using the computer. In management, the models
mathematics are constructed and used to verify the results of
decisions before applying them to reality.

Where are simulations applied?

It is very important to have a clear understanding of the scope of application of the simulation; among the
many possible financial applications where it has been successfully applied
we can mention:

Introduction of new products:

The central objective of this simulation is to determine that probability which

It has a new product that generates a profit. A model is developed.
that relates utility with the different probabilistic inputs such as, for
example, the demand, the cost of the parts or raw materials and the cost of the
labor, etc. The only input that is controlled is whether it is entered or not
the product in the market. By generating a diversity of possible values in
The function of probabilistic inputs calculates the resulting utility.
Inventory Policies The objective of this simulation is to be able to choose a
appropriate inventory policy that results in good service to the
5
 clients and at a reasonable cost. Therefore, a model is developed that
involves cost and service level with probabilistic inputs, such as the
product demand and the delivery time of suppliers, and with
controllable inputs, such as the quantity to be ordered and the point of
request. For each game of controllable tickets, various would be generated
possible values for the probabilistic inputs and the cost would be calculated
resulting service levels.

Waiting lines or queue systems:

The goal is to determine the waiting times at an ATM,

example in a bank. A model is developed that relates the times of
waiting of clients with probabilistic entries (a distribution
Poisson), such as the arrival of customers and service times (a
exponential distribution), and a controllable input, the number of cashiers
installed automatics. For each controllable input value (in this case
the number of machines), a diversity of values would be generated for the
probabilistic inputs would be calculated the waiting times of the
customers, as well as their expected number in the queue and the service time.

Different models can be simulated, such as project scheduling.

inventories and financials, however, exercises will be carried out later
about the models that are intended to be simulated so we will not go into
detail of the same.

What is Monte Carlo simulation?

Monte Carlo simulation is a quantitative technique that makes use of the

statistics and computers to imitate, through mathematical models, the
random behavior of non-dynamic real systems (generally, when
these are systems whose state changes over time, one resorts to
either to discrete event simulation or to system simulation
continuous).

The key to Monte Carlo simulation is to create a mathematical model of

system, process or activity to be analyzed, identifying those
variables (model inputs) whose random behavior determines the
global behavior of the system. Once these inputs or variables have been identified
randomly, an experiment is conducted consisting of (1) generating (with help
from the computer) random samples (specific values) for those inputs, and (2)
analyze the system's behavior in response to the generated values. After repeating n
Sometimes in this experiment, we will have n observations about the
system behavior, which will be useful for us to understand the

6
functioning of the same (obviously, our analysis will be more precise as
let n be the number of experiments that we carry out.

Use of Excel in simulation:

Nowadays, simulation models can be created and run on a PC. The

level of computer and mathematics knowledge required to design and
running a useful simulator has decreased significantly. The capacity of the
simulation models to deal with complexity, manage variability
the performance measures and reproducing short-term behavior allows
may the simulation be a powerful tool.

In addition, the power of the 'PCs' has recently made it possible for the
administrator uses spreadsheets to assess investment risk
financial, project evaluation, retirement plans, and other types of decisions
business. The above is due to the flexibility and statistical capacity of the sheet
of calculus, which makes it special for the development of models of
simulation, particularly in the use of Monte Carlo simulation. In fact, it is
perhaps the most elegant spreadsheet of all software applications,
Well, it gives the user great power in conducting the analyses.
financial.

The RANDOM() function in Excel

Spreadsheets like Excel (and any standard programming language)

they are capable of generating pseudo-random numbers from a
uniform distribution between 0 and 1. These types of pseudo-random numbers are
the basic elements from which any simulation develops
computer. In Excel, it is possible to obtain a pseudo-random number -coming from
from a uniform distribution between 0 and 1 - using the RANDOM function:

The numbers generated by the RANDOM function have two properties.

that make them comparable to completely random numbers:

Every time the RANDOM function is used, any real number between 0 and 1
it has the same probability of being generated (hence the name of distribution
uniform.

7
2. The different generated numbers are statistically independent of each other.
from others (that is, the value of the number generated at a given moment does not
depends on those generated earlier).

The RANDBETWEEN function is a volatile function in Excel. This means that each
whenever we press the F9 key or change any of the model's inputs,
all the cells where the RANDOM function appears will be recalculated from
automatic form.

Examples of the use of RANDOM can be found in the menu itself.

Excel help.

Benefits of simulation:

Simulated models are easier to understand than many models.

analytical.

You gain 'experience' cheaply by simulating on the computer without

to take real risks. • Results are obtained quickly.

With simulation models, it is possible to analyze very systems

complex, where analytical models cannot reach.

8
Exercise 1:

A company wants to analyze the possibility of manufacturing a new

product for 4 years.

The data to analyze financial viability is as follows:

Startup cost of the project $150,000.00

Selling price $35,000.00
Fixed Costs $15,000.00
Variable Costs of Income 75%
Annual amortization $10,000.00
Capital Costs 10%
Tax Rate 35%
Average annual demand Uniform Distribution (8,12)

Solution

200 tests are conducted

It starts with a consecutive number that represents the simulation number.

2. The demand for each of the 4 years that is desired is located in the demand.
manufacture the product following, this will follow a uniform distribution
between 8 and 12.
3. In the column corresponding to year 1, the function is introduced.
"[Link]" which returns the rounded random number within the range
of specified numbers, this is used for the type of distribution that is
present in this case.

=RANDBETWEEN(8,12)

This cell containing the function is copied to the remaining 3 years because it
will maintain the uniform distribution throughout the study period and the generated
set of random numbers that change with any type of update
that takes place in the spreadsheet.

4. Revenues will be equal to: =Year 1 demand * Selling price. Fixing the
cell that contains the selling price because it will always be the same,
then proceed to copy the argument from cell year 1 (=D15*$E$4)
in the following years.

5. The fixed costs are already provided in the statement, therefore they are fixed for all the
cells of the 4 years
6. Variable costs are given by = Year 1 Revenues * Variable Costs, they are set the
second cell used in the argument for being constant. (=H15*$E$6)

9
7. Annual amortization will be equal to the fixed amount of $10,000, so the cell is anchored.
in which that value is found and moves through the other years. (=$E$7)
8. Profit before tax, in this section is when the profit is obtained
the company before the government arrives and collects its respective taxes. The
first year is = first year income - fixed cost of year 1 - variable cost of
first year - annual amortization. (=H15-L15-P15-T15) And it shifts through the
cells that correspond to the other years.
Net income before tax is given by =Income before tax
year1 - (Profit before tax year1 * Cost of capital), the cell is fixed that
it contains the cost. (=X15-(X15*$E$8)) Then its argument is shifted three
cells to the right.
10. Net profit after tax, which is when it has already gone to the tax authority and has been completed.
the respective discount. This cell will contain =Net profit before the
tax year1 - (net profit before tax year1 * tax rate) and this
the last one is set as a constant value. (=AB15-(AB15*$E$9)) It is dragged down
argument for the following years.
11. With the net cash flow, a year 0 is added which means that it will be
where it starts when initiating production, that is, the investment that is made and is
It appears as an expense, so it should be multiplied by -1.
It will be necessary to place in this cell: =Project start-up cost*(-1), fixing
the entire argument since it will be a value that will not change (=$E$3*-1)

The first year of net cash flow is given by: =Net income of the first
year + amortization of year 1. (=AF15+T15) The argument is shifted in the
cells of the remaining 3 years.

It is so because depreciation is considered the wear and tear of machines and each
year is increasing, which results in a money outflow on the part of the
company but in terms of net cash flow the machine wears out one
amount that is not deducted from the company but is only a measurement for
that in the cash flow is contemplated since that money is still in the
bank.

12. Net present value is given by the Excel function NPV() which returns the
net present value of an investment based on a discount rate and a
series of future payments and entries. In this case, it will be =startup cost of
project + NPV(0.06, NPV year1, NPV year2, NPV year3, NPV year4) is used
generally the rate del 6% in some banks.
(=AJ15 + VNA(0.06, AK15, AL15, AM15, AN15))
13. Average NPV given by =AVERAGE($AO$15:AO15) function that
returns the average of the selected range of cells, focusing on the first
cell for accumulation.

10
14. Now that the table is complete, it is moved down 200 rows and is
they obtain the following data:

It is important to highlight that this result will vary every time it is

interact with the spreadsheet because that is how [Link](8,12) works.
Only a part of the 200 tests performed is shown.

11
12
 Analysis of results.

The company's focus will be on the current net present value because it is the
that reflects the profits they obtain in those 4 years

In this line graph with 200 simulations, a transient period is observed.

in the first tests but starting from approximately 85 it begins to
stabilize and during this period an evaluation can be made, the average of
VNA will be around $14,000 in the manufacturing of the new product for 4
Years, since a negative value is not obtained, it means that the investment produces
profits and it is recommended that the company carry out this production.

13
Exercise 2:

An airline wants to make a projection about passenger influx in

each of the quarters in six years from now. For
it is available with 5 different rates that users can choose from, each one
It has a growth rate obtained from historical data. To make the
necessary simulations to determine which rate service should be prioritized.

solution

The first step is to establish the different rate services offered by the
airline with their respective growth rates.

Subsequently, the values for the first period are determined, that is, the data
current passengers using the airline.

In this data, the year of study can be seen in the first column, the
period of the year subject to analysis which for this occasion are quarters, the data
current passengers using the airline per quarter and a
sum of the total passengers who use the services of the company.

Using the following formula for the other boxes:

=INT([Link](RAND(),E4*(1+E$2)^0.25,E4/20))

14
Due to the fact that we are working with people, the units must be discrete.
for that reason the INTEGER function is used to approximate the result
closest lower. The next part of the formula is the [Link] function that
returns the inverse of the normal cumulative function for a mean and deviation
specific standards. Within the parameters of the [Link] function are
the probability, the mean and the standard deviation. For the parameter of the
probability uses the RANDOM function which generates numbers between 0 and 1
every time you click on the spreadsheet, this is very convenient for
these types of procedures that require a probability value. The
the mean parameter defined by the equation E4*(1+E$2)^0.25 refers to
to the value of the previous period plus a variation determined by the rate of
growth previously defined in cell E2. And finally a standard deviation
defined by E4/20 that complies with the growth that the rate has year after year.

15
The advantage of this arrangement is that the number of years that can be projected is
analyst desires, as was previously established, the simulation is carried out
for each of the periods from the current year until 2023.

16
Using the RANDOM function, another set of data was determined, in
In a few words, another simulation was made. The variability of the data can be seen.
that have a tendency to increase over the years, this is due to the rate
of growth or decline which can vary according to the data
statisticians.

A change in rates is carried out to observe the effect it has.

it has about the data of different airlines.

17
Drastic changes can be observed in the numbers of Rate 1, Rate 2 and
Rate 3 due to the change in its growth rates.

In addition to the table, this system has a graphics area that also varies.
in each simulation, which makes it easier to visualize the trends of each one
of the rates.

18
Growth rates can vary, as can the number of users.
in the first period. This makes it possible for the same model to be applied to
different situations, such as sales projections for a product line.
Using population growth rates, it is also possible to determine the
number of people who will use public services in the coming years.
19
20
Exercise 3:

Let's suppose we work in a large computer warehouse, and we are asked

advice for deciding on the number of licenses for a certain system
operational that is convenient to acquire - the licenses will be provided with the
computers that will be sold during the next quarter, and it is logical to think that
In a few months, there will be a new operating system on the market.
superior features. Each operating system license costs you
The store has a total of $75, while the selling price is $100.
When the new version of the operating system is released, the warehouse
he will be able to return the remaining licenses to the distributor, receiving a total in exchange
from $25 each. Based on the historical data from the last few months, the
warehouse managers have been able to determine the following distribution
of probabilities concerning the sales of licenses for the new operating system
it refers to

Solution

Simulation for the purchase of 100 licenses

Pseudo-random values are generated with the Rand() function in Excel, it was used
from the IF(condition, true, false) function, to determine the event
corresponding associated with the pseudo-random value obtained (note that we use
also the MIN function, since in no case will we be able to sell more licenses
that the available ones). The rest of the formulas are quite clear

21
Next, a sample of the table is shown for n iterations.
with the autocomplete function in Excel. Note that it is necessary to anchor the
reference cells for logical conditions.

Finally, it is possible to estimate the expected value of the random variable that
provide the benefits without more than finding the average of the 100 observations
that we just carried out. Likewise, we will use the functions STDDEV and
[Link] to find, respectively, the standard deviation of
the obtained sample and the confidence interval (at a 95% level) for the value
expected.

22
The sample mean is calculated using the average function and for the limits of the
confidence interval for the sample mean - the value of the interval amplitude
trustworthy.

Based on this analysis, we can perform the simulation for the purchase of 150,
200, 250 y 300 licencias.

Finally, a final comparison of the results for 50 iterations is made.

It is concluded that the optimal number of licenses to purchase is 150 because

it presents an average benefit of $3014.71 being the highest, in addition to 95%
The profit will be between $2602.2 and $3427.39.

23
Conclusions:

The simulation in spreadsheets is a flexible, powerful, and intuitive tool.

in operations research since in a matter of seconds or
minutes, it can even simulate years of operation of a common system
while generating a series of statistical observations about the
system performance during this period.
The use of Microsoft Excel spreadsheets has a versatility
exceptional although by its nature it is an imprecise technique since
only provides statistical estimates and not exact results.
This type of simulation leads only to numerical data about behavior.
of the system, does not provide additional insight regarding the relationships
of cause and effect within the system, except for the signs that may
extract from these numbers and the analysis that is required for
build the model.
The design of these models must be well grounded in statistical theory,
Simulation runs are often needed in spreadsheets.
surprisingly long to obtain statistically significant results
significant.

24