MonteGrid | Examples
Examples
Example models for MonteGrid. Each workbook has an About sheet that describes the model. The models and their values are fictional.
Corporate finance
-
Capital budgeting: new plant
Ten-year cash flow for a new manufacturing plant. Volume, price, variable cost and fixed cost drive EBITDA. Straight-line depreciation and a 25% tax rate give free cash flow, discounted at 9% to NPV.
- Distributions
- BetaPERT, Normal, Triangular, Uniform
- Correlation groups
- Demand, Costs
- Forecasts
- NPV, IRR, Cumulative Free Cash Flow
- Model size
-
- 6 assumptions
- 3 forecasts
- 3 worksheets
- 133 formula cells
-
Pharmaceutical asset NPV
Fifteen-year cash flow for a drug candidate entering Phase III: uptake after launch, revenue decline at loss of exclusivity, discounting to NPV. Risk-Adjusted NPV is the unrisked NPV times the probability of approval, 0.62. Realised NPV draws the approval outcome: the full NPV on approval, otherwise the loss of the remaining development spend.
- Distributions
- Bernoulli (yes/no), BetaPERT, Discrete, Lognormal, Normal, Triangular, Uniform
- Correlation groups
- Market
- Forecasts
- IRR, Peak Annual Revenue, Risk-Adjusted NPV, Realised NPV
- Model size
-
- 12 assumptions
- 4 forecasts
- 3 worksheets
- 250 formula cells
Project cost and schedule
-
Defense hardware program cost estimate
Bottom-up cost estimate for a hardware program. Labor cost is burdened rates times learning-curve hours. With material, it is escalated to then-year dollars and rolled up by work breakdown structure (WBS) with a management reserve.
- Distributions
- BetaPERT, Lognormal, Normal, Triangular
- Correlation groups
- Rates, Engineering, Hardware, Material, Software
- Forecasts
- Total Program Cost, Unit Recurring Flyaway, Labor Share of Total
- Model size
-
- 19 assumptions
- 3 forecasts
- 4 worksheets
- 135 formula cells
-
Construction schedule
Schedule risk for a construction project: 15 tasks with three-point durations on five network paths. Paths A, B and C run in parallel, and paths D and E follow. A weather allowance extends the schedule. Liquidated damages apply after the contract completion date, capped at 10% of the contract value.
- Distributions
- BetaPERT, Discrete, Triangular
- Correlation groups
- Structural, Procurement, Fit-out
- Forecasts
- Project Duration, Forecast Completion Date, Calendar Days Late, Liquidated Damages Exposure
- Model size
-
- 17 assumptions
- 4 forecasts
- 3 worksheets
- 68 formula cells
-
Risk register contingency
A capital project with a $60M base estimate and a register of 10 risks. Each risk has a probability of occurrence and a three-point cost impact. A Triangular estimate factor of 0.97 to 1.08 applies to the base estimate.
- Distributions
- Bernoulli (yes/no), BetaPERT, Triangular
- Correlation groups
- None
- Forecasts
- Risk Cost, Total Project Cost
- Model size
-
- 21 assumptions
- 2 forecasts
- 2 worksheets
- 48 formula cells
Insurance
-
Insurance claims with excess-of-loss reinsurance
Annual large-claim losses for an insurer. The claim count is Poisson with a mean of 4. Each claim size is Lognormal with a mean of $2M and a standard deviation of $3M. A reinsurance layer of $10M in excess of $5M applies to each claim, for a premium of $1.2M.
- Distributions
- Lognormal, Poisson
- Correlation groups
- None
- Forecasts
- Gross Loss, Ceded Loss, Net Cost
- Model size
-
- 16 assumptions
- 3 forecasts
- 3 worksheets
- 82 formula cells
Engineering and quality
-
Tolerance stack-up
An assembly gap: the housing inner length minus four part lengths. Each dimension is Normal, with a standard deviation of one third of its tolerance. The gap specification is 0.10 mm to 0.60 mm.
- Distributions
- Normal
- Correlation groups
- None
- Forecasts
- Assembly Gap, Out of Specification
- Model size
-
- 5 assumptions
- 2 forecasts
- 2 worksheets
- 15 formula cells