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

Get the workbook

We send the .xlsx file to this address. We also add the address to the Asunder Scientific mailing list. You can unsubscribe at any time.