MonteGrid | Tutorials | Build a model and run a first simulation

Build a model and run a first simulation

Define the assumptions and forecasts of a capital budgeting model and run a simulation. The next four tutorials continue with this model.

  1. 1Open the model

    The model is a 10-year cash flow for a new plant. The inputs are on the Inputs sheet. NPV, IRR and cumulative free cash flow are on the Summary sheet. The base case NPV is $9.6M.

    Download the workbook below and open it in Excel. To install MonteGrid, see Install MonteGrid on Mac.

    capital-budgeting.xlsx

    capital-budgeting
    HomeInsertDrawPage LayoutFormulasDataReviewViewAutomateMonteGrid
    DefineAssumption DefineForecast ClearDefinition DefineCorrelation ImportDefinitions
    Run Stop RunSettings
    CreateReport ExtractData OpenMonteGrid
    Help License
    A1 Input
    ABCDEFGHIJKLM
    1 InputValueUnit
    2 Capital cost120.0$M
    3 Year 1 volume200.0thousand units
    4 Volume growth per year3.0%%
    5 Unit price, year 1450.0$
    6 Price change per year-1.0%%
    7 Variable cost per unit250.0$
    8 Fixed cost per year18.0$M
    9 Tax rate25.0%%
    10 Discount rate9.0%%
    11 Plant life10years
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    AboutInputsCash FlowSummary
    ReadyAccessibility: Good to go
    The model in Excel.
    capital-budgeting
    HomeInsertDrawPage LayoutFormulasDataReviewViewAutomateMonteGrid
    DefineAssumption DefineForecast ClearDefinition DefineCorrelation ImportDefinitions
    Run Stop RunSettings
    CreateReport ExtractData OpenMonteGrid
    A1 Input
    ABCDEF
    1 InputValueUnit
    2 Capital cost120.0$M
    3 Year 1 volume200.0thousand units
    4 Volume growth per year3.0%%
    5 Unit price, year 1450.0$
    6 Price change per year-1.0%%
    7 Variable cost per unit250.0$
    8 Fixed cost per year18.0$M
    9 Tax rate25.0%%
    10 Discount rate9.0%%
    11 Plant life10years
    12
    13
    14
    15
    16
    17
    18
    AboutInputsCash FlowSummary
    ReadyAccessibility: Good to go
  2. 2Define the unit price

    The unit price is between 420 and 470, most likely 450.

    To define the assumption:

    1. Select Inputs!B5.
    2. On the MonteGrid tab, click Define Assumption. The distribution list opens.
    3. In the Common group, click Triangular, then click Use Triangular.
    4. Enter 420 in Minimum, 450 in Most likely and 470 in Maximum.
    5. Enter Unit Price in Name.
    6. Click Save assumption.

    MonteGrid writes =MC_TRIANG(420,450,470,MC_STATIC(450,"Unit Price","")) into the cell. Outside a simulation, the cell returns its static value, 450.

    capital-budgeting
    HomeInsertDrawPage LayoutFormulasDataReviewViewAutomateMonteGrid
    DefineAssumption DefineForecast ClearDefinition DefineCorrelation ImportDefinitions
    Run Stop RunSettings
    CreateReport ExtractData OpenMonteGrid
    Help License
    B5 450
    ABCDEFGHIJKLM
    1 InputValueUnit
    2 Capital cost120.0$M
    3 Year 1 volume200.0thousand units
    4 Volume growth per year3.0%%
    5 Unit price, year 1450.0$
    6 Price change per year-1.0%%
    7 Variable cost per unit250.0$
    8 Fixed cost per year18.0$M
    9 Tax rate25.0%%
    10 Discount rate9.0%%
    11 Plant life10years
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    AboutInputsCash FlowSummary
    ReadyAccessibility: Good to go
    Define Assumption
    The distribution list.
    The distribution list.
    The distribution list.
    The distribution list. Open full size
    Unit Price, Triangular.
    Unit Price, Triangular. Open full size
  3. 3Define the other assumptions

    Define the other five inputs the same way. All five distributions are in the Common group.

    CellInputNameDistributionParameters
    B2Capital cost ($M)Capital CostBetaPERTMinimum 110, most likely 120, maximum 150
    B3Year 1 volume (thousand units)Year 1 VolumeNormalMean 200, standard deviation 25
    B4Volume growth per yearVolume GrowthTriangularMinimum 0.01, most likely 0.03, maximum 0.06
    B7Variable cost per unit ($)Unit Variable CostNormalMean 250, standard deviation 15
    B8Fixed cost per year ($M)Fixed CostUniformMinimum 15, maximum 21

    Note: Define Assumption has 42 distributions in eight groups. Under More: tails, correlation group, edit as text, you can truncate the distribution, set a correlation group, or type the parameters, for example min=110, likely=120, max=150.

    capital-budgeting
    HomeInsertDrawPage LayoutFormulasDataReviewViewAutomateMonteGrid
    DefineAssumption DefineForecast ClearDefinition DefineCorrelation ImportDefinitions
    Run Stop RunSettings
    CreateReport ExtractData OpenMonteGrid
    Help License
    B8 =MC_UNIFORM(15,21,MC_STATIC(18,"Fixed Cost",""))
    ABCDEFGHIJKLM
    1 InputValueUnit
    2 Capital cost120.0$M
    3 Year 1 volume200.0thousand units
    4 Volume growth per year3.0%%
    5 Unit price, year 1450.0$
    6 Price change per year-1.0%%
    7 Variable cost per unit250.0$
    8 Fixed cost per year18.0$M
    9 Tax rate25.0%%
    10 Discount rate9.0%%
    11 Plant life10years
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    AboutInputsCash FlowSummary
    ReadyAccessibility: Good to go
    Six assumptions.
    capital-budgeting
    HomeInsertDrawPage LayoutFormulasDataReviewViewAutomateMonteGrid
    DefineAssumption DefineForecast ClearDefinition DefineCorrelation ImportDefinitions
    Run Stop RunSettings
    CreateReport ExtractData OpenMonteGrid
    B8 =MC_UNIFORM(15,21,MC_STATIC(18,"Fixed Cost",""))
    ABCDEF
    1 InputValueUnit
    2 Capital cost120.0$M
    3 Year 1 volume200.0thousand units
    4 Volume growth per year3.0%%
    5 Unit price, year 1450.0$
    6 Price change per year-1.0%%
    7 Variable cost per unit250.0$
    8 Fixed cost per year18.0$M
    9 Tax rate25.0%%
    10 Discount rate9.0%%
    11 Plant life10years
    12
    13
    14
    15
    16
    17
    18
    AboutInputsCash FlowSummary
    ReadyAccessibility: Good to go
  4. 4Define the forecasts

    To define the NPV forecast:

    1. Select Summary!B2.
    2. On the MonteGrid tab, click Define Forecast.
    3. Enter NPV in Name.
    4. Click Save forecast.

    Define Summary!B3 as IRR and Summary!B4 as Cumulative Free Cash Flow the same way. MonteGrid wraps each formula in MC_OUTPUT, for example =MC_OUTPUT(SUM('Cash Flow'!B14:L14),"NPV").

    Note: Clear Definition removes a definition. An assumption returns to its static value, a forecast to its formula.

    capital-budgeting
    HomeInsertDrawPage LayoutFormulasDataReviewViewAutomateMonteGrid
    DefineAssumption DefineForecast ClearDefinition DefineCorrelation ImportDefinitions
    Run Stop RunSettings
    CreateReport ExtractData OpenMonteGrid
    Help License
    B2 =SUM('Cash Flow'!B14:L14)
    ABCDEFGHIJKLM
    1 MeasureValueUnit
    2 Net present value9.6$M
    3 Internal rate of return10.8%%
    4 Cumulative free cash flow83.2$M
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    AboutInputsCash FlowSummary
    ReadyAccessibility: Good to go
    Define Forecast
    Define Forecast.
    Define Forecast.
    Define Forecast.
    Define Forecast. Open full size
  5. 5Run the simulation

    To run the simulation:

    1. On the MonteGrid tab, click Open MonteGrid. The Model page lists six assumptions and three forecasts.
    2. In the sidebar, click Run.
    3. Click Run at the bottom of the Run page.

    The run uses the default settings: 5,000 trials, seed 20260802, Monte Carlo sampling. When the run ends, the Results page opens.

    capital-budgeting
    HomeInsertDrawPage LayoutFormulasDataReviewViewAutomateMonteGrid
    DefineAssumption DefineForecast ClearDefinition DefineCorrelation ImportDefinitions
    Run Stop RunSettings
    CreateReport ExtractData OpenMonteGrid
    B4 =MC_OUTPUT(SUM('Cash Flow'!B12:L12),"Cumulative Free Cash Flow")
    ABCDEF
    1 MeasureValueUnit
    2 Net present value9.6$M
    3 Internal rate of return10.8%%
    4 Cumulative free cash flow83.2$M
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    AboutInputsCash FlowSummary
    ReadyAccessibility: Good to go
    MonteGrid
    The Model page.
    The Model page.
    The Model page.
    The Model page. Open full size
    The Run page.
    The Run page. Open full size
  6. 6Review the NPV forecast

    The NPV has a P10 of -$36.9M and a P90 of $50.6M, against a base case of $9.6M. Review the results describes each panel.

    Save the workbook. The definitions are saved with it.

    The NPV forecast.
    The NPV forecast. Open full size

All tutorials