pscm 7217.1.1 data analytics · origin the modern version of the monte carlo method was invented in...

* Reference - Business Analytics: Methods, Models, and Decisions (1st edition, James R. Evans, Pearson)

PSCM_7217.1.1 Data Analytics

Excel Functions and Simulation

Sang Jo Kim

July 4, 2015

Excel PivotTable and PivotChart

Excel vlookup()

Monte Carlo Simulation

Contents

Excel Menu: Insert - Tables - PivotTable

Then, follow wizard steps.

PivotTables allow:

Quick creation of cross tabulations

Numerous custom-made summary tables and charts

Exploring Data Using PivotTables

3-4

* PivotTable and vlookup.xlsx- The data table must have column titles.

PivotTable Field List

Select the fields for:

Report Filter Column Labels Row Labels Σ Values

Or, before choosing PivotTable, you can select a cell in the data and let Excel prepare a default PivotTable.

Exploring Data Using PivotTables

First Mission using

PivotTable

7

2nd Mission using

PivotTable

3rd Mission using

PivotTable

PivotChart Click the PivotTable you

created Choose “Option – PivotChart” from the menu

8

0

1000

2000

3000

4000

5000

6000

East North South West

Book

DVD

vlookup() ≈ Database

Search

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) lookup_value: The value you want to lookup

table_array: The range of cells that VLOOKUP will search for the Lookup_value and the return value. The first column in the cell range must contain the Lookup_value.

col_index_num: The column number (starting with 1 for the left-most column of table_array) that contains the return value.

range_lookup (optional)

• FALSE: searches for the exact value in the first column.

• TRUE: assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one.

9

(range_lookup= FALSE)

vlookup() ≈ Database Search

10

(range_lookup = TRUE)

* Returns the closest number smallerthan the value* The first column should be sorted!

Simulation and Risk Analysis

Spreadsheet Models with Random Variables

Monte Carlo Simulation Using Risk Solver

Newsvendor Model


Origin The modern version of the Monte Carlo method was invented in the late 1940s by

Stanislaw Ulam, while he was working on nuclear weapons projects at the Los Alamos National Laboratory. It was named by Nicholas Metropolis, after the Monte Carlo Casino (in Monaco), where Ulam's uncle often gambled. Immediately after Ulam's breakthrough, John von Neumann understood its importance and programmed the ENIAC computer to carry out Monte Carlo calculations.

Typical procedure Define a domain of possible inputs.

Generate inputs randomly from a probability distribution over the domain.

Perform a deterministic computation on the inputs.

Aggregate the results.


* Source: Wikipedia

http://en.wikipedia.org/wiki/Probability_distribution

http://en.wikipedia.org/wiki/Deterministic_algorithm

Outsourcing Decision Model Base model: What if the “Production volume” (Demand)

= 1,500?

Spreadsheet Models with Random Variables* Monte Carlo Simulation.xlsx

Outsourcing Decision Model Suppose production volume is uncertain ~ N(1000,1002) Replace cell B12 (Prod. volume) with

=ROUND(NORM.INV(RAND(),1000,100),0)


* Press F9to recalculate

=ROUND(NORM.INV(RAND(),1000,100,true),0)

* Monte Carlo Simulation.xlsx

Outsourcing Decision Model: Simulation solely using standard Excel functions


* Limitation: How many trials would be sufficient? Law of Large Numbers!

Law of Large Numbers

The Law: The average of the results obtained from a large number of trials should be close to the expected value, and will tend to become closer as more trials are performed.

(ex) Die casting• SJ: Bets on {1,2}• SP: Bets on {3,4,5,6}

Who will win from a die casting?

(ex) Newsvendor problem We are maximizing the “expected” profit

The expected profit for a given ordering quantity can be closely achieved only after a “Large Number” of trials!

Steps for Simulating with the Risk Solver Platform

1. Develop a spreadsheet model. (Deterministic)

2. Determine probability distributions for uncertain input variables.

3. Identify output variables you want to predict.

4. Choose the number of trials and replications.

5. Run the simulation.

6. Interpret the results.


Using Risk Solver Platform Probability Distribution Functions

For the Outsourcing Decision Model, assume that two inputs are uncertain – demand and unit cost.

Demand (production volume) is normally distributed with a mean of 1000 and standard deviation of 100 units.

Unit cost has a triangular distribution with a minimum of $160, most likely value of $175, and a maximum of $200.


Using Risk Solver Platform Probability Distribution Functions

=PsiNormal(1000,100,PsiTruncate(0, 1E+30))

=PsiTriangular(160,175, 200)XX


Using the Distributions Button in Risk Solver PlatformSelect cell B12.Risk SolverDistributionsCommonNormal

Mean=1000Stdev=100

Select cell B10and enter unitcost distribution.


Using the Distributions Button in Risk Solver Platform

Normal Distribution dialog for Demand in cell B12


“Truncated” Normal Distribution

X 0

Using the Distributions Button in Risk Solver Platform

Triangular Distribution dialog for Unit Cost in cell B10.


Defining Uncertain Cells in Risk Solver Define worksheet cells for the output variables

you want to predict using the Results button in the Simulation Model group.

Risk Solver calls these uncertain cells. Uncertain cells must be numeric. The values of these cells will be computed using

the randomly generated input values. There will be one value of each uncertain cell

generated on each trial of the simulation.


Using the Results Button in Risk Solver Platform

Select cell B19.Risk SolverResultsOutputIn Cell

Risk Solver then modifies cell B19 (you can do this manually as well).

=B16-B17+PsiOutput()xx


Running a Simulation Options, All Options Simulation Tab Trials per Simulation

Use at least 5000 trials. Simulations to Run

Use more than 1 run ifyou want to examine variation between runs.

Simulation Random SeedChoose a nonzero numberif you want to reproduce theexact same results.

Sampling MethodUse Monte Carlo for more randomized sampling.


Run and View Simulation Results in Risk Solver Choose Simulate, Run Once

Frequency tab displays a histogram and summary statistics for the output variable.

Chart Statistics support risk analysis via changes to upper/lower cutoffs.

Click the down arrow next to Statistics to change the results displayed.

Double click on any uncertain output cell to view

its results.



Analyzing Simulation Results for the Outsourcing Decision Model

A small candy store sells Valentine’s Day gift boxes that cost $12 and sell for $18.

In the past, at least 40 boxes have sold by Valentine’s Day but the actual amount is unknown.

After the holiday, boxes are discounted 50%.

Determine net profit on the gift boxes.

C = 12, R = 18, S = 9

Net profit = R(min{Q,D}) + S(max{0,Q−D}) − CQ

=18(min{Q,D}) + 9(max{0,Q−D}) − 12Q

Newsvendor Model

Newsvendor Model

Suppose the store owner kept records for the past 20 years on number of boxes sold.

Original Newsvendor ModelHistorical data on boxes sold

* Monte Carlo Simulation - Newsvendor.xlsx

Newsvendor Model

Simulating the Newsvendor Model Using Resampling

Newsvendor Model

Generate candy sales by resampling from the 20 historical values.

Set demand in B11 as a random variable.

“Distributions” –“Distribution Wizard”

Set profit in B17 as the uncertain output.

Simulating the Newsvendor Model Using Resampling

Newsvendor Model

Using a Fitted Distribution for Monte Carlo Simulation

Newsvendor Model

Generate candy sales by fitting a probability distribution to the 20 historical sales values.

Use the function structure: Concavity or convexity, etc.

Systematically choose the decision variable values and observe the objective function values

Choose the decision variable values closer to the optimal zone you guess

Optimization Using Simulation

pscm 7217.1.1 data analytics · origin the modern version of the monte carlo method was invented in...

Documents