Class Notes (808,110)
Canada (493,016)
York University (33,530)
Marketing (184)
MKTG 2030 (72)
Ben Kelly (12)
Lecture 7

Lecture 7 - Intro To Simulation.pdf

16 Pages
Unlock Document

York University
MKTG 2030
Ben Kelly

Omis  2010   Lecture  7   Jessica  Gahtan   Introduction  to  Simulation  Using  Risk  Solver  Platform   -­‐ In  many  spreadsheets,  the  value  for  one  or  more  cells  representing  independent   variables  is  unknown  or  uncertain.  As  a  result  there’s  uncertainty  about  the  value  that   the  dependent  variable  will  assume.  Simulation  can  be  used  to  analyze  these  types  of   models   Random  Variables  &  Risk   Random  variable  is  any  variable  whose  value  can’t  be  predicted  or  set  with  certainty   -­‐ Many  ‘input  cells’  in  spreadsheet  models  are  actually  random  variables  (the  future  cost   of  raw  materials,  future  interest  rates,  future  number  of  employees  in  a  firm,  expected   product  demand)   -­‐ Decisions  made  on  the  basis  of  uncertain  info  often  involve  risk  (i.e.  the  potential  for   loss)   Why  Analyze  Risk?   Plugging  in  expected  values  for  uncertain  cells  tells  us  nothing  about  the  variability  of  the   performance  measure  we  base  decisions  on.   -­‐ Suppose  an  $1000  investment  is  expected  to  return  $10,000  in  two  years.  Would  you   invest  if…   -­‐ 1.  The  outcomes  could  range  from  $9,000 -­‐$11,0000?   -­‐ 2.  The  outcomes  could  range  from  $30,000 -­‐$50,0000?   -­‐ Alternatives  with  the  same  expected  value  may  involve  different  levels  of  risk   Methods  of  Risk  Analysis   Best-­‐Case/Worst-­‐Case  Analysis   -­‐ Best  case-­‐  plug  in  the  most  optimistic  values  for  each  of  the  uncertain  cells   -­‐ Worst  case-­‐  plug  in  the  more  pessimistic  values  for  each  of  the  uncertain  cells   -­‐ This  is  east  to  do  but  tells  us  nothing  about  the   distribution  of  possible  outcomes  within   the  best  and  worst-­‐case  limits   -­‐ Possible  Performance  Measure  Distributions  Within  a  Range :                             Page  1   Omis  2010   Lecture  7   Jessica  Gahtan   What-­‐if  Analysis   -­‐ Plug  in  different  values  for  the  uncertain  cells  and  see  what  happens   -­‐ This  is  easy  to  do  with  spreadsheets       Problems:   -­‐ Values  may  be  chosen  in  a  biased  way   -­‐ Hundreds  or  thousands  of  scenarios  may  be  required  to  generate  a  representa tive   distribution   -­‐ Doesn’t  supply  the  tangible  evidence  (facts  +figures)  needed  to  justify  decisions  to   management   Simulation   -­‐ Resembles  an  automated  what -­‐if  analysis   -­‐ Values  for  uncertain  cells  are  selected  in  an  unbiased  manner   -­‐ The  computer  generates  hundre ds/thousands  of  scenarios   -­‐ We  analyze  the  results  of  these  scenarios  to  better  understand  the  behaviour  of  the   performance  measure   -­‐ This  allows  us  to  make  decisions  using  solid  empirical  evidence   The  Monte  Carlo  Process   -­‐ A  large  proportion  of  the  applications  of  simulations  are  for  probabilistic  models   The  Monte  Carlo  technique  is  defined  as  a  technique  for  selecting  numbers  randomly  from  a   probability  distribution  for  use  in  a  trial  (computer  run)  of  a  simulation  model   -­‐ The  basic  principle  behind  the  process  i s  the  same  as  in  the  operation  of  gambling   devices  in  casinos  (i.e.  those  in  Monte  Carlo,  Monaco)   -­‐ Gambling  devices  produce  numbered  results  from  well -­‐defined  populations   Example  1:  Hungry  Dawg  Restaurants   i Lisa  Pon  has  just  been  hired  as  an  analyst  in  the   corporate  planning  department  of  Hungry  Dawg   Restaurants.    Her  first  assignment  is  to  determine  how  much  money  the  company  needs  to   accrue  in  the  coming  year  to  pay  for  its  employees’  health  insurance  claims.  Hungry  Dawg  is  a   large,  growing  chain  of  restaurants  that  specialize  in  traditional  southern  food.    The  company   has  become  large  enough  that  it  no  longer  buys  insurance  from  a  private  insurance  company.     The  company  is  now  self  insured,  meaning  that  it  pays  health  insurance  claims  with  its  own   money  (although  it  contracts  with  an  outside  company  to  handle  the  administrative  details  of   processing  claims  and  writing  checks).   The  money  the  company  uses  to  pay  claims  comes  from  two  sources:  employee  contributions   (or  premiums  deducted  from  employees’  payche ck),  and  company  funds  (the  company  must   pay  whatever  costs  are  not  covered  by  employee  contributions).       Hungry  Dawg  Restaurant   –  Facts      Hungry  Dawg  is  a  growing  restaurant  chain  with  a  self -­‐insured  employee  health  plan.    Covered  employees  contribute  $125   per  month  to  the  plan,  Hungry  Dawg  pays  the  rest.    The  number  of  covered  employees  changes  from  month  to  month.    The  number  of  covered  employees  was  18,533  last  month  and  this  is  expected  to   increase  by  2%  per  month.    The  average  claim  per  employee  was  $250  l ast  month  and  is  expected  to  increase  at  a   rate  of  1%  per  month.     Implementing  the  Model   See  file  Fig12-­‐2.xlsm   Page  2   Omis  2010   Lecture  7   Jessica  Gahtan     Questions  About  the  Model    Will  the  number  of  covered  employees  really  increase  by  exactly  2%  each  m  ?  Will  the  average  health  claim  per  employee  really  increase  by  exactly  1%  each  month?    How  likely  is  it  that  the  total  company  cost  will  be  exactly  $36,125,850  in  the  coming   year?    What  is  the  probability  that  the  total  company  cost  will  exceed,  say,  $38  ,000? Simulation    To  properly  assess  the  risk  inherent  in  the  model  we  need  to  use  simulation.    Simulation  is  a  4  step  process:   1)  Identify  the  uncertain  cells  in  the  model.   2)  Implement  appropriate  RNGs  for  each  uncertain  cell.   3)  Replicate  the  model  n  times,  and  record  the  value  of  the  bottom -­‐line  performance  measure.   4)  Analyze  the  sample  values  collected  on  the  performance  measure.   What  is  Risk  Solver  Platform?    Risk  Solver  Platform  (RSP)    is  a  spreadsheet  add -­‐in  that  simplifies  spreadsheet   simulation.    A  limited-­‐life  trial  version  of  RSP  is  available  with  this  book.    It  provides:   – dialogs  &  functions  for  generating  random  numbers   – commands  for  running  simulations   – graphical  &  statistical  summaries  of  simulation  data    For  more  info  see:   Random  Number  Generators  (RNGs)    A  RNG  is  a  mathematical  function  that  randomly  generates  (returns)  a  value  from  a   particular  probability  distribution.   Page  3   Omis  2010   Lecture  7   Jessica  Gahtan    We  can  implement  RNGs  for  uncertain  cells  to  allow  us  to  sample  from  the  distribution   of  values  expected  for  different  cells.   How  RNGs  Work    The  RAND(  )  function  returns  uniformly  distributed  random  numbers  between  0.0  and   0.9999999.    Suppose  we  want  to  simulate  the  act  of  tossing  a  fair  coin.    Let  1  represent  “heads”  and  2  represent  “tails”.    Consider  the  following  RNG:   =IF(RAND(  )<0.5,1,2)   Simulating  the  Roll  of  a  Die    We  want  the  values  1,  2,  3,  4,  5  &  6  to  occur  randomly  with  equal  probability  of   occurrence.    Consider  the  following  R NG:   =INT(6*RAND())+1     Simulating  Random  Values  from  a  Distribution    Values  simulated  from  a  distribution  will  have  a  similar  pattern  to  that  of  the   distribution.    The  more  values  you  simulate,  the  closer  the  results  will  approximate  the  distribution.   (Open:  “Random  Numbers”  excel  file)    Notice  that  the  random  numbers  are  regenerated  each  time  the  spreadsheet   recalculates  (e.g.  when  new  data  is  added)   Distributions  in  Excel    Excel  contains  distribution  functions  for  many  common  distribution  types:   – NORMINV(probability,  mean,  standard  deviation)   – EXPONDIST(x,lambda,cumulative)   – BINOMDIST(number_s,  trials,  probability_s,  cumulative)   – GAMMAINV(probability,  alpha,  beta)   Before  we  move  on,  let  explore  the  Excel  :”Choose”  Function    Uses  index_num  to  return  a  value  from  the  list  of  value  arguments.    For  example,  we  have  the  following  list  of  numbers:      =CHOOSE(3,A1,A2,  A3,A4,A5)  results  in  the  number  85   Page  4   Omis  2010   Lecture  7   Jessica  Gahtan   Example  2       OPEN:  Financial  Forecast  (Data)  without  Risk  Solver.xls   Click  on:  “Data”  tab   An  Example  –  Financial  Forecast    Let’s  take  a  look  at  simulating  a  simple  business  scenario...    Video:    Financial  Forecast    File:    Financial  Forecast  (Data)  without  Risk  Solver;  “ Data”  tab    Let’s  use  Excel  to  simulate1000  trials  of  this  forecast    Assumptions:   – 3  scenarios  are  equally  likely   –  Variable  cost  follows  a  triangular  distribution   –  Fixed  cost  does  not  vary   Triangular  Distribution   The  triangular  distribution  is  a  way  of  thinking  about  v ariation  that  corresponds  rather  well  to   what-­‐if  analysis  in  business.  It  is  not  surprising  that  business  analysts  are  attracted  to  the   triangular  model.  Its  finite  range  and  simple  form  are  more  understandable  than  a  normal   distribution.   It  is  more  versatile  than  a  normal,  because  it  can  be  skewed  in  either  direction.  Yet  it   has  some  of  the  nice  properties  of  a  normal,  such  as  a  distinct  mode.  The  triangular  model  is   especially  handy  for  what -­‐if  analysis  when  the  business  case  depends  on  predicting  a   stochastic  variable  (e.g.,  the  price  of  a  raw  material,  an  interest  rate,  a  sales  volume).   If  the   analyst  can  anticipate  the  range  ( a  to  c)  and  most  likely  value  (b),  it  will  be  possible  to   calculate  probabilities  of  various  outcomes.  Many  times,  such  distribut ions  will  be  skewed,  so   a  normal  wouldn’t  be  much  help.     Page  5   Omis  2010   Lecture  7   Jessica  Gahtan    First  we  need  to  use  the  RAND()  function  to  simulate  one  instance  of  each  uncertain   value   OPEN:  Financial  Forecast  (Data)  without  Risk  Solver.xls            Click  on:  “Data  with  Random  #s”  tab   Simulating  the  Scenarios    It  is  easy  to  apply  the  RAND()  function  to  simulate  the  3  equally  likely  scenarios   -­‐  place  in   cell  J4:           =3*RAND()+1    This  generates  a  random  number  from  1  to  3.9999...    Note  that  the  CHOOSE  function  truncates  the  numerical  value,  so  this  res ults  in  a  1/3   chance  of  selecting  1,  2  or  3     Si
More Less

Related notes for MKTG 2030

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.