CASTR Spreadsheet Example Project

Open the example

Option 1: the example workbook

1. Download CASTR Example Project 75.7.0.xlsm and save it to a local folder (not a network or cloud-synced location the first time you open it).
2. Unblock the file. Windows marks files downloaded from the internet and Excel will silently disable their macros. Right-click the .xlsm file → Properties → check Unblock → OK.
3. Open in Excel, click Enable Content, and accept the Disclaimer sheet.
4. Read the results. The simulation has already been run, so the Summary, Histogram, Sensitivity, Tail Drivers, Joint Confidence and What-If sheets show the Results below. To run it yourself, click Run Simulation; the window that opens shows 10,000 iterations, so click Run. The fixed seed gives figures within sampling noise of these, not the very same ones: a workbook that has already been run draws in a slightly different order (see Results).

Option 2: import the inputs into your own copy

1. Save the inputs file (CASTR Example Project 75.7.0.pri) in the same folder as the spreadsheet. The import’s file dialog opens in that folder.
2. Open a fresh copy of CASTR Spreadsheet 75.7.0. Importing replaces every input in the workbook, so don’t use one that holds a real project. CASTR first saves a backup copy whose name contains BeforeImport.
3. Import: on the CASTR ribbon, click Import Inputs, confirm, and pick the file. The import also synchronizes Risk ID with the Risk Register, so Register numbers 1–7 match the Risk ID numbers. The minor risks H, I and J (Risk ID 8–10) reach the Register as three minor groups, numbered 11–13: after the highest Risk ID issued.
4. Run: click Run Simulation. The window that opens shows the file’s 10,000 iterations; click Run. The file also sets seed 123457.
5. Compare your Summary with the Results below.

At a glance

What the example contains

The estimate is $20M PE, $40M RW and $100M CN, including traditional contingency of $0.75M, $1M and $5M. Without that contingency, and with $5M of prior cost added, the base cost is $158.25M CY. Cost and duration each vary ±10%, and each phase has a chance of a better and of a worse market: PE 10% and 10% for a market 5% better or 10% worse, RW 33% and 33% for 5% better or 20% worse, CN 33% and 33% for 5% better or 30% worse. On top of that the file sets:

The YOE sheet shows CN starting 30 Aug 2028, not 1 Sep: the 2-month lag is counted as 2 × 30.4 days.

Risk register

All impacts are Triangular (min / most likely / max). Risks A–G have Include? = Yes; H, I and J have Include? = Minor. # is the Risk ID number, which for A–G is also the Register number. Costs are in $M CY; schedule in months. A dependent risk’s Probability is its chance given its parent; the Probability including Dependency column shows the overall chance.

Correlation sheet: one pair, D (Cost) ↔ E (Cost), r = 0.7. Both depend on what is found below ground, so when both happen their costs tend to be high or low together.

Minor risks

H, I and J are small: each has a mean cost under $1M and a mean delay under a month, within the Minor thresholds on Risk ID, so they are marked Minor there. CASTR doesn’t simulate minor risks one by one. The MinorRisks sheet groups them by phase-segment, and the sync puts each group on the Risk Register as one risk that occurs in every run (probability 100%, Independent):

• Expected cost is the sum of the expected costs (probability × mean) of the phase-segment’s minor risks; expected delay is the largest of their expected delays. With one minor risk per phase-segment the two rules give the same here; give a phase-segment a second minor risk to see the difference.
• The Register risk is a Triangular from 20% below to 20% above the expected value (the % Offsets above the MinorRisks table). A group with a negative expected value would be an opportunity.
• Only Phase and Segment are typed on the MinorRisks sheet; everything else in the table is a formula. Each phase-segment gets its row when the phase-segment is added. A Minor risk whose phase-segment has no row there is left off the Register.

The groups add about $1.7M to the mean cost. Their effect on the P70 shows in What-If.

Results

With the fixed seed, CASTR Spreadsheet 75.7.0 reproduces these figures exactly on the first run in a new copy of the release workbook loaded with Import Inputs. That is how the example workbook was made, so they are also the results it holds as downloaded. Every later run, in that copy or in the downloaded example, draws in a slightly different order, so the same seed gives slightly different figures, within sampling noise: for example a P70 of $249.9M CY instead of $250.2M, and a P70 finish of 27 instead of 30 August 2031. Each later run then repeats those figures. If yours differ by more, check SimSettings: Use Random Seed? = No, Seed = 123457, Number of Iterations = 10,000. A workbook whose inputs were typed in by hand can also differ a little: Excel may calculate the random draws in another order, so the same seed gives each cell a different draw. The difference is within sampling noise.

How to read each output

Summary

On the percentile-of-sum method the P70 is $250.2M CY: the budget the project stays within 70% of the time. The $91.9M contingency above the $158.25M base breaks down as about $9.2M of base uncertainty, $47.7M of risk events (including $1.7M from the three minor groups), $4.0M of CN indirect delay cost and $31.0M of spread between the mean ($219.2M) and the P70. The Summary gives contingency as a share of the $153.25M phase base cost, which leaves out the $5M prior cost: 60.0%. That is at the very top of the 25–60% national range for a moderately complex project at 10–25% design (59.99% before rounding), so the CY row has no notice. The YOE contingency, which includes escalation, is 63.4%: above the range, so the Notices column flags it. Switch the Summary to sum of phase-segment percentiles and the P70 becomes $255.2M (63.2%, also above the range); the Notices column then shows about 74%, the confidence that $255.2M actually gives on the total.

Histogram

The Histogram shows Total Project Cost, YOE, and its distribution has two humps: about 59% of the runs sit in a lower hump around $199M and 41% in an upper hump around $283M, with a valley near $247M. The split is the bridge. Every iteration gets either E, a $3–10M rehabilitation (60% of them), or F, a $65–90M replacement (40%). The mean, $237.7M, falls between the humps, where few runs land. The P70, $272.3M, sits on the upper hump: with F in 40% of the runs, a 70% budget has to cover some of them (about a quarter of the runs with F stay within it). The P95 is $312.5M and the costliest iteration $369.8M. Sum of phase-segment percentiles adds each phase’s own P70, as if every phase hit its P70 at once, which is why it gives the higher figure. The Summary labels that figure “Sum of Phase-Segment P70s”; its notice gives the share of the iterations at or below it: 73.8%, in CY and YOE alike. With one segment, sum of segment percentiles gives the same answer as percentile of sum.

Sensitivity (Tornado)

The Sensitivity sheet lists, for each result, the 15 inputs with the strongest rank correlation. The chart shows the two results that matter most here.

• F and E lead on cost, from opposite sides (+0.83 and −0.74). They are the either/or pair: F happens in every iteration where E doesn’t, so each run gets a $3–10M rehabilitation or a $65–90M replacement. E occurring means F does not, so E correlates negatively with cost and finish although it is a threat. Each risk appears twice on the sheet, once for its cost (c) and once for its schedule (s), with similar values because both depend on whether it occurs; the chart shows the larger. G appears once: it has no cost.
• CN market conditions come next on cost. The sheet lists them by cell: BaseUncertainty!J46 is the chance of a worse CN market (+0.31) and I46 a better one (−0.29).
• G has no cost of its own but still shows on cost (+0.14). Its 12–36 months of delay cost $0.5M a month through the CN indirect delay cost, and the later finish adds escalation.
• G leads the finish date (+0.62), then F (+0.50), B (+0.45), E (−0.43) and C (+0.36). B and C add (Series = Yes), and the Direct link passes RW delay straight into CN. A shows +0.16 because its impact is negative: when A occurs, the finish moves earlier.
• D barely moves the finish (+0.03). CN delays run in parallel, so D’s 1–4 months count only when it is the longest CN delay, and F (6–12) or G (12–36) is often longer.
• The three minor groups barely register (0.02 or less on cost and on finish, so they are not charted). They occur in every run, so only their ±20% size varies, and that is small next to the other risks.

Tail Drivers

Tail Drivers show what the worst outcomes have in common. In the costliest 10% of iterations (Total Project Cost YOE above $301.6M), F occurs every time against 40% overall and D 77% against 60%; E never does. The delays behind that tail are F, G and E: G occurs in 26% of those runs against 16% overall. In the latest 10% of finishes (after 7 Sep 2032), G occurs in 99.9% against 16% overall. The minor groups occur in every run, so they never set a tail apart. The Tornado answers “what moves the result most?”; Tail Drivers answer “what does a bad outcome look like?”

The Tail Driver Explainer button next to each output’s name gives a plain-language account of that output, ending with a shape test of the whole distribution. For Total Project Cost, YOE it reports Two humps found: about 59% of the runs form a lower hump around $198.6M and 41% an upper hump around $282.9M, with the valley near $247.1M, and the average, $237.7M, falls between the two peaks. It then names the risk that sorts the runs into the two humps: F occurs in 97% of the runs above the valley and almost none below it. Total Project Duration and Total Project Completion Date report One hump, and no risk is named. To see the test change its answer, set F to Dormant (Try it).

Joint Confidence

The dashed crosshairs sit at the P70 cost ($272.3M YOE) and the P70 date (30 Aug 2031). A label in each corner of the chart gives the share and the number of all 10,000 iterations in that quadrant, and the four add up to 100%:

Each dashed line on its own splits the runs 70/30, but the two 70% shares overlap on only 56.9%, because a run can be on budget but late, or on time but over budget. Each of those two corners holds the 70% less the overlap: 13.1%. Move the crosshairs and the labels update. Only 1 in 4 iterations is drawn as a dot so the chart stays readable; the labels and the joint coverage count every iteration.

Does it assume cost and schedule are independent? No. The 56.9% is a straight count of the iterations that meet both targets. Each iteration’s cost and finish come from the same simulated run, so whatever links them in the model is already in the count. The two limits show where it sits:

• If cost and schedule were independent, it would be 70% × 70% = 49%.
• If they moved perfectly together, it would be 70%.
• This run gives 56.9%; the health check below the chart reports a rank correlation of 0.62 between cost and finish.

In this model, cost and schedule are linked through shared risk events (B, C, D, E and F each add cost and delay in the same iteration; F adds $65–90M and 6–12 months together), the $0.5M a month CN indirect delay cost (which turns G’s lawsuit delay into cost), and inflation (a later finish means more escalation in the YOE cost). They are not linked where the model leaves them apart: base cost and base duration variability are drawn separately, market conditions affect cost only, and a risk’s cost size and delay size are drawn separately unless you pair them on the Correlation sheet. If on a real project a long construction phase also tends to cost more, model that link (pair a risk’s cost and schedule, or raise the indirect delay cost) and the joint coverage moves toward 70%.

The purple curve shows every date and cost pair that gives 70% joint confidence. Being 70% confident of both means moving out to that curve, giving up some date, some cost, or both.

What-If

Each Register risk has one box, ten here (A–G and the three minor groups); tick it to switch the risk off. With the metric on Total Project Cost YOE, the panel removes the risk’s cost, its delay and the cost that delay adds (the CN indirect delay cost, and the escalation of work pushed later), and the risks that depend on it follow it. It shows the P70 without the risk (from $272.3M): F $212.0M, D $264.7M, B $266.5M, G $271.0M, C $271.4M, A $274.0M and E $297.7M; Minor PE-Seg 1 $271.3M, Minor RW-Seg 1 $271.7M and Minor CN-Seg 1 $271.7M.

• G has no cost of its own, yet switching it off lowers the P70 by about $1.3M. Its 12–36 months of delay cost $0.5M a month in CN and push the work later, where it escalates more.
• B takes C with it. C can only happen with B, so switching B off removes both (−$5.8M); C alone moves the P70 by about $0.9M.
• Switching E off raises the P70 by about $25M. F happens whenever E doesn’t, so without E every run gets the $65–90M replacement, and the cost distribution has one hump.
• Removing an opportunity raises the cost: without A the P70 is $1.7M higher.
• Minor PE-Seg 1 moves the P70 most of the three groups (−$1.0M, against −$0.6M for RW and for CN), more than its $0.64M expected cost. Its 0.5–0.7 month delay passes through the Direct links into RW and CN and pushes that work later, where it escalates more.

What-If doesn’t re-run the simulation: it reworks the example’s own runs. Setting a risk’s Risk Status to Dormant and re-running draws fresh numbers, so its figures differ slightly.

Try it

Change one thing, re-run, then compare with the results above. Make Series? and Dependency changes on Risk ID and synchronize: a sync rebuilds the Risk Register and keeps only its Risk Status, which you set on the Register itself. Put each change back before the next one. The F, Delay Dependent, C and indirect-cost rows are worked out from the example’s own iterations. The Delay Dependent and indirect-cost changes switch no risk on or off, so a re-run should give the same figures. The F and C rows are estimates: setting F to Dormant changes the random draws, so expect figures close to these rather than equal (ticking F on the What-If sheet shows its P70 cost and finish without a re-run), and the C row only says roughly how far the finish moves. The others say which way the results move.

Notes

• Why correlation barely moves the P70. CASTR correlates the size of two impacts; whether each risk occurs is drawn separately. D and E occur together in about 36% of the runs, and their size swings ($5–20M and $3–10M) are small next to F’s $65–90M on/off swing. Correlation matters when the paired risks are both likely and have wide ranges. To make risks happen together, use Mutually Inclusive dependency. To see correlation working, check the Risk Corr Outputs sheet (hidden: right-click any sheet tab and choose Unhide): this run achieves 0.69.
• These numbers are for CASTR Spreadsheet 75.7.0. A later version may change them slightly; this page is updated with each release.

Get the spreadsheet

The CASTR Spreadsheet is free. Download it, see the requirements and read about every input and results sheet.