a choice of platform: excel ® and crystal ball ®
DESCRIPTION
A Choice of Platform: Excel ® and Crystal Ball ®. Michael Schilmoeller Tuesday, February 2, 2011 SAAC. Overview. The importance of transparency and accessibility Availability of diagnostics The topography of the RPM High-performance Excel XLLs Carefully controlled calculations - PowerPoint PPT PresentationTRANSCRIPT
A Choice of Platform:Excel® and Crystal Ball ®
Michael SchilmoellerTuesday, February 2, 2011
SAAC
2
Overview• The importance of transparency and
accessibility– Availability of diagnostics
• The topography of the RPM• High-performance Excel
– XLLs– Carefully controlled calculations
• Crystal Ball and CB Turbo• Olivia• The efficient frontier
3
Observed StatesManifest Capacity (MW) 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
NEW Cohort Cost ($M Real) 0.0 0.0 0.0 0.0 0.0 133.9 136.5 139.22 Status 0 0 0 0 0 6 3 32 Cost ($M Real) 0.0 0.0 0.0 0.0 0.0 133.9 136.5 139.2
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00141.8 144.4 147.1 149.7 152.3 246.5 340.6 434.7 524.7 614.7
3 3 3 3 3 6 6 6 7 7141.8 144.4 147.1 149.7 152.3 246.5 340.6 434.7 524.7 614.7
0.00 0.00 0.00 0.00 27284.76 28452.62 30000.00 23409.07 30000.00 30000.00 29112.87704.7 794.7 884.7 974.7 1367.7 1367.7 1367.7 1367.7 1367.7 1367.7 1367.7
7 7 7 7 5 5 5 5 5 5 5704.7 794.7 884.7 974.7 974.7 974.7 974.7 974.7 974.7 974.7 974.7
30-Per Life
27427.51 30000.00 30000.00 28187.09 27192.54 30000.00 25637.19 30000.00 27456.80 0.00 0.001121.3 1027.1 933.0 843.0 753.0 663.0 573.0 483.0 393.0 0.0 0.0
5 5 5 5 5 5 5 5 5 9 9728.2 634.1 540.0 450.0 360.0 270.0 180.0 90.0 0.0 0.0 0.0
C:\Backups\Plan 6\Studies\Model Development\Construction Cost Uncertainty\FixedCst_08.xls
Transparency
4
Overview• The importance of transparency and
accessibility– Availability of diagnostics
• The topography of the RPM• High-performance Excel
– XLLs– Carefully controlled calculations
• Crystal Ball and CB Turbo• Olivia• The efficient frontier
5
Layout of the RPM
6
Logic StructureSource: Q:\MS\Plan 5\Appendix Model\AppL_060227.doc
7
Overview• The importance of transparency and
accessibility– Availability of diagnostics
• The topography of the RPM• High-performance Excel
– XLLs– Carefully controlled calculations
• Crystal Ball and CB Turbo• Olivia• The efficient frontier
8
High-Performance Excel
• XLLs – special dynamic link library (DLL) files containing both C/C++ “native” add-in functions via Excel “C” API and “flat” functions to control Excel memory
• Bypass Excel’s calculation tree– Strict enforcement of the calculation order
with Range.calculate method– Store function values for recall– Enable functions selectively
9
High-Performance Excel(An Aside)
• Windows Server 2008 r2 provides an High Performance Computing (parallel-processing) add-on, now in beta 3
• The HPC initiative is aimed at Excel users and Excel 2010 has provisions for user to configure Server 2008/HPC for enhanced workbook performance
10
Requirements
• Windows XP• Excel 2000 or better• COM Typelibs:
– NWPCC• Errdll.dll – error handling and reporting• NWPCC07Risk.xll – dispatch algorithms, etc.
– Microsoft:
11
Overview• The importance of transparency and
accessibility– Availability of diagnostics
• The topography of the RPM• High-performance Excel
– XLLs– Carefully controlled calculations
• Crystal Ball and CB Turbo• Olivia• The efficient frontier
12
Crystal Ball and CB Turbo
• It seemed like a good idea at the time….• The trials and tribulations of third-party
software• Current status
13
Overview• The importance of transparency and
accessibility– Availability of diagnostics
• The topography of the RPM• High-performance Excel
– XLLs– Carefully controlled calculations
• Crystal Ball and CB Turbo• Olivia• The efficient frontier
14
Olivia
15
Olivia
16
Olivia
17
Olivia
18
Olivia
19
Olivia
20
Olivia
21
Olivia
22
Olivia
23
Olivia
24
Overview• The importance of transparency and
accessibility– Availability of diagnostics
• The topography of the RPM• High-performance Excel
– XLLs– Carefully controlled calculations
• Crystal Ball and CB Turbo• Olivia• The efficient frontier
25
The Efficient Frontier
• A device for filtering out “bad” plans (not to be confused with “bad” NPV outcomes)
• One plan “dominates” another if it is no worse in any regard, and better in at least one regard, than the other
• The bad plans are the dominated plans
26
Evaluating Vaccines
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
27
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
AB
28
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
AB
29
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
30
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
31
The Efficient Frontier
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
123
124
125
126
127
128
129
77 78 79 80 81 82 83
Thou
sand
s
Thousands
Side Effects
Inef
fect
ive
source: \EUCI 100323 Presentation\Efficient Frontier\EUCI 100323 01.xls
32
What does the Efficient Frontier Tell Us?• The Efficient Frontier does not
tell us what to do• The Efficient Frontier tells us
what not to do• Most useful if there are a large
number of choices
33
Resources• Fifth power plan• http://www.nwcouncil.org/energy/powerplan/5/(06)%20Risk%20Sect
ion.pdf• http://www.nwcouncil.org/energy/powerplan/5/(07)%20Portfolio%20
Analysis.pdf• http://www.nwcouncil.org/energy/powerplan/5/AppendixP.pdf• http://www.nwcouncil.org/energy/powerplan/5/Appendix%20L%20(P
ortfolio%20Model).pdf• Sixth power plan• http://www.nwcouncil.org/energy/powerplan/6/final/SixthPowerPlan_
Ch9.pdf• http://www.nwcouncil.org/energy/powerplan/6/final/SixthPowerPlan_
Appendix_J.pdf
34
End
35
Observing States
Option Selection (integer)
FOM(R $M/ MW/ period)
Late Constr Costs (RL $M/ MW/ Period 2̂)
Earliest Availability (Period)
Regional Share
Retirement mothball life (periods)
Retirement evaluation cost (RL $M/MWPeriod)
Decommisioning cost (RL $M/MWPeriod)
First Period Mothball Costs(RL $M/ MW/ Period)
First Period Cancellation Costs (RL $M/ MW/ Period^2)
Generation technology Status
LT Fuel Price (Range name)
MTBR (weeks) FOR [0…1]
Nameplate (MW) - required for cost calcs of existing units only
256 0.01310136 0.002999913 100% 0.000448407 0 CCCT New 1 0.05 1
Criterion Set ID
Planning Periods
Early Construction Periods
Late Construction Periods
Development Costs (RL $M/ MW/ Period 2̂)
Mothball Costs (RL $M/ MW/ Period)
Cancellation Costs (RL $M/ MW/ Period^2)
Early Constr Costs(RL $M/ MW/ Period 2̂)
CancelThreshold (
Const Cost Escl (.01=1%/period)
ResourceLife (periods)
OptionLife (periods)
Market-driven ramp rate (MW)
Planned Development Costs (RL $M/ MW/ Period^2) Index
CCCT Criterion_004 0 4 6 0 0.000087896 0.021963468 0.00313711 -99999 0.000% 30 20 FALSE 0.0013258 0
Option Selection (integer)
FOM(R $M/ MW/ period)
Late Constr Costs (RL $M/ MW/ Period^2)
Earliest Availability (Period)
Regional Share
Retirement mothball life (periods)
Retirement evaluation cost (RL $M/MWPeriod)
256 0.01310136 0.002999913 100%
Criterion Set ID
Planning Periods
Early Construction Periods
Late Construction Periods
Development Costs (RL $M/ MW/ Period^2)
Mothball Costs (RL $M/ MW/ Period)
Cancellation Costs (RL $M/ MW/ Period^2)
CCCT Criterion_004 0 4 6 0 0.000087896 0.021963468Decommisioning cost (RL $M/MWPeriod)
First Period Mothball Costs(RL $M/ MW/ Period)
First Period Cancellation Costs (RL $M/ MW/ Period 2̂)
Generation technology Status
LT Fuel Price (Range name)
MTBR (weeks) FOR [0…1]
Nameplate (MW) - required for cost calcs of existing units only
0.000448407 0 CCCT New 1 0.05 1
Early Constr Costs(RL $M/ MW/ Period^2)
CancelThreshold (
Const Cost Escl (.01=1%/period)
ResourceLife (periods)
OptionLife (periods)
Market-driven ramp rate (MW)
Planned Development Costs (RL $M/ MW/ Period^2) Index
0.00313711 -99999 0.000% 30 20 FALSE 0.0013258 0
C:\Backups\Plan 6\Studies\Model Development\Construction Cost Uncertainty\FixedCst_08.xls
Transparency