ch 11 lecture

notes from the lecture slides/video

monte carlo simulation:

used to evaluate the impact of uncertainty on a decision

how simulation models have been successfully used in a variety of disciplines?

• financial applications include investment planning, project selection, and option pricing

• marketing applications include include new product development and the timing of market entry for a product

• management applications include project management, inventory ordering, capacity planning, and revenue management

probability distribution

represents not only the range of possible values but also the relative likelihood of various outcomes

a simulation model extends the spreadsheet modeling approach by…?

replacing the use of single values for parameters with a probability distribution of possible values

what are random/uncertain variables?

parameters that are not known with a high degree of certainty. the valuea for random variables are randomly generated from the specified probability distributions

what do simulation results do?

simulation results help us to make decision recommendations for the controllable inputs that address not only the average output but also the variability of the output

what is a best-case scenario?

its what the manager is expecting to happen

what is the worst-case scenario?

what the manager is hoping doesn’t happen

what is a risk analysis?

quantifying the likelihood and magnitude of an undesirable outcome

what are the key parameters to determine first-year profits:

• selling price per unit (p)

• first-year administrative and advertising costs (c_a)

• direct labor cost per unit (c_¡)

• parts cost per unit (c_p)

• first-year demand (d)

base-case scenario: first-years profit is computed by

profit = (p - c_¡ - c_p) x d - c_a

profit = ( selling price per unit - direct labor cost per unit - parts cost per unit) x first-year demand - first-year administrative and advertising costs

what is a what-if analysis?

involves considering alternative values for the random variables (direct labor cost, parts cost, and first-year demand) and computing the resulting value for the output (profit)

whats used to perform a what-if analysis to evaluate a worst-case scenario and a best-case scenario?

could use ranges of labor costs, parts cost, and first-year demand to perform a what-of analysis to evaluate a worst-case and best-case scenario

what are spreadsheet simulation models used for?

to conduct a more thorough evaluation of risk by obtaining insight on the potential magnitude and probability of undesirable outcomes by turning to developing a spreadsheet simulation model

do what-if analyses indicate various profit/loss values?

simple what-if analyses do not indicate the likelihood of the various profit/loss values

what is a computer generated random variable?

are randomly generated numbers from 0 up to but not including 1; this interval is denoted [0,1). placing the formula =RAND() in a cell of an excel worksheet will result in a random number between 0 and 1 being placed into that cell

to generate a value for a random variable we use the excel formula:

value of uniform random variable = lower bound + (upper bound - lowrr bound) x RAND()

to generate a value for a random variable characterized by a normal distribution with a specified mean and standard deviation:

value of a normal random variable = NORM.INV(RAND(), mean, std dev)

how to run a what-if analysis:

1. select cell range

2. click the DATA tab in the ribbon

3. click what-if analysis in the data tools group and select data table

4. when the data table dialog boc appears, leave the row input cell: box blank and enter any empty cell in the spreadsheet (e.g., D1) into the column input cell: box

5. click ok

to excel calculate the direct labor cost per unit:

=VLOOKUP(RAND(),direct labor cost table, 3,true)

to excel calculate (manually) parts cost per unit:

=lower bound + (upper bound - lower bound) * RAND()