GCPV RiskSimulator
Cargado por
Rodo Mendoza ReyesGCPV RiskSimulator
Cargado por
Rodo Mendoza ReyesAnnex 3 - Cost per bag hiAnnex 5 - Competitor prices (euros/bolsa)
Index Cost_per_bag Index Cemex Uniland Lafarge
1 11.27 13.87 11.56
1 7.33 2 11.99 12.55 14.08
2 7.60 3 15.19 13.78 12.08
3 7.08 4 12.46 13.29 13.77
4 7.46 5 12.20 14.12 11.35
5 7.50 6 13.33 12.79 12.09
6 7.30 7 12.29 14.44 13.36
7 7.49 8 14.76 11.84 14.82
8 7.54 9 13.11 12.62 11.82
9 7.18 10 12.24 13.44 11.61
10 7.32 11 13.25 12.97 12.16
11 7.38 12 11.91 11.71 14.26
12 7.43 13 13.98 10.42 12.60
13 7.63 14 12.24 13.17 11.12
14 7.38 15 11.34 10.98 11.68
15 7.90 16 12.95 15.32 13.29
16 7.55 17 11.40 11.22 13.87
17 7.70 18 11.58 11.75 13.42
18 7.63 19 14.94 13.31 13.14
19 7.70 20 11.70 14.63 11.15
20 7.26 21 13.94 10.31 12.06
21 7.50 22 14.56 11.76 12.57
22 7.36 23 12.81 12.80 13.52
23 7.66 24 14.12 12.71 11.16
24 7.46 25 12.61 12.08 11.65
25 7.71 26 13.67 12.11 12.55
26 7.40 27 14.95 13.06 14.29
27 7.53 28 11.10 12.47 10.47
28 7.73 29 13.09 12.04 13.59
29 7.41 30 12.33 13.47 12.58
30 7.38 31 13.32 10.78 12.65
31 7.34 32 12.16 13.41 12.81
32 7.46 33 13.76 11.43 14.35
33 7.61 34 15.58 11.12 12.28
34 7.51 35 12.44 13.62 11.57
35 7.87 36 14.41 11.52 13.51
36 7.65
37 7.75
38 7.61
39 7.53
40 7.63
41 7.60
42 7.31
43 7.67
44 7.62
45 7.38
46 7.67
47 7.35
48 7.51
49 7.44
50 7.44
51 7.51
52 7.18
53 7.49
54 7.48
55 7.92
56 7.35
57 7.19
58 7.57
59 7.72
60 7.33
Simulation inputs & choices
Item Value Notes
Bid option 10.50
Bid option 12.50
Bid option 14.50
Selected bi 10.50
Investment ###
Plant capac 1500000
Fixed costs ###
Fixed costs ###
Discount ra 10.00%
Transport c 0.50
Transport c 0.80
SimulationBootstrap from Annex3 (INDEX with RANDBETWEEN)
SimulationBootstrap from Annex5
Notes: Use Risk Simulator (or similar) to run Monte Carlo. See 'Instructions' sheet for steps.
Project cashflow simulation (Bilbao plant)
Year 2007 2008 2009 2010 2011 2012 2013 2014 2015
ContractQt 400000 900000 800000 700000 200000 0 0 0 0
MarketDe 800000 800000 900000 1050000 1200000 1250000 1350000 1400000 1600000
Selected_b 10.50
Sampled_v 7.33
Sampled_fi 2069969
Transport_ 0.50
Depreciati 2000000
Win_contra 1
Year ContractQtMarketDe TotalBags Price_per_Revenue VariableCoTransportCFixedCost Depreciati
2007 2007 400000 2007 10.50 21073.5 14709.3 1003.5 2069969 2000000
2008 2008 900000 2008 10.50 21084 14716.63 1004 2069969 2000000
2009 2009 800000 2009 10.50 21094.5 14723.96 1004.5 2069969 2000000
2010 2010 700000 2010 10.50 21105 14731.29 1005 2069969 2000000
2011 2011 200000 2011 10.50 21115.5 14738.62 1005.5 2069969 2000000
2012 2012 0 2012 10.50 21126 14745.95 1006 2069969 2000000
2013 2013 0 2013 10.50 21136.5 14753.28 1006.5 2069969 2000000
2014 2014 0 2014 10.50 21147 14760.61 1007 2069969 2000000
2015 2015 0 2015 10.50 21157.5 14767.94 1007.5 2069969 2000000
2016 2016 0 2016 10.50 21168 14775.26 1008 2069969 2000000
2017 2017 0 2017 10.50 21178.5 14782.59 1008.5 2069969 2000000
Discount_r 10.00%
Initial_Inv -2E+07
IRR_calc_r -2E+07 CF_after_t -2064608 -2064606 -2064603 -2064600 -2064598 -2064595 -2064592
XIRR approx Err:523
2016 2017
0 0
1700000 1900000
EBIT Tax NOPAT CF_after_tax
-4064608 0 -4064608 -2064608
-4064606 0 -4064606 -2064606
-4064603 0 -4064603 -2064603
-4064600 0 -4064600 -2064600
-4064598 0 -4064598 -2064598
-4064595 0 -4064595 -2064595
-4064592 0 -4064592 -2064592
-4064590 0 -4064590 -2064590
-4064587 0 -4064587 -2064587
-4064584 0 -4064584 -2064584
-4064582 0 -4064582 -2064582
-2064590 -2064587 -2064584 -2064582 0
Bid / Contract winning simulation
Item Formula / Value
Sampled_C 11.99
Sampled_U 11.12
Sampled_L 11.82
Our bid (Se 10.50
Lowest com 11.12
Win contra 1
Contract baSee ProjectCF sheet
How to run Monte Carlo in Risk Simulator (Palisade) or similar
- Open this workbook in Excel.
- In Risk Simulator (or similar add-in), set the number of trials (e.g., 5,000) and choose to simulate recalculation of Excel RAND
- On sheet 'BidSimulation' the sampled competitor bids use INDEX+RANDBETWEEN sampling from historical Annex5. The cell
- On sheet 'ProjectCF' sampled variables: B7 (variable cost per bag) bootstraps from Annex3; B8 (fixed costs) sampled uniform
- Monitor outputs: 'ProjectCF' -> NPV (cell next to label 'NPV') and IRR (cell next to label 'XIRR approximation'). Also monitor 'B
- Use Risk Simulator to collect statistics (mean, std, percentiles) for NPV, IRR, and Win flag.
- If you want separate scenarios for bids, change Inputs!B7 to 10.50, 12.50 or 14.50 and re-run the simulation.
- Save results and export histograms from Risk Simulator.
te recalculation of Excel RAND/RANDBETWEEN/NORMINV formulas.
om historical Annex5. The cell B10 'Win contract?' will be 1 if our bid < sampled lowest competitor, else 0.
(fixed costs) sampled uniformly between Inputs B10 and B11; Price when not winning is generated via NORMINV(RAND(),13.5,1.0).
pproximation'). Also monitor 'BidSimulation'!B10 for probability of winning.
the simulation.
MINV(RAND(),13.5,1.0).
