MonteGrid | Tutorials | Correlate assumptions

Correlate assumptions

Correlate the demand inputs and the cost inputs, and compare the NPV with the uncorrelated run.

  1. 1Correlation groups

    Year 1 Volume and Volume Growth both depend on demand. Unit Variable Cost and Fixed Cost both depend on input prices. The first run sampled them independently.

    A correlation group applies one rank correlation coefficient to every pair of its assumptions. A group with no coefficient uses Default group correlation on the Run page, 0.6 by default. MonteGrid imposes rank correlations with the Iman–Conover method.

    Continue with the workbook from Build a model and run a first simulation, or download it below.

    capital-budgeting.xlsx

  2. 2Create the Demand group

    To put Volume Growth in a new group, Demand:

    1. Select Inputs!B4.
    2. On the MonteGrid tab, click Define Assumption. The dialog opens with the saved distribution.
    3. Click More: tails, correlation group, edit as text.
    4. Enter Demand in Correlation group.
    5. Click Save assumption.

    The group name is the third argument of MC_STATIC: MC_STATIC(0.03,"Volume Growth","Demand").

    capital-budgeting
    HomeInsertDrawPage LayoutFormulasDataReviewViewAutomateMonteGrid
    DefineAssumption DefineForecast ClearDefinition DefineCorrelation ImportDefinitions
    Run Stop RunSettings
    CreateReport ExtractData OpenMonteGrid
    Help License
    B4 =MC_TRIANG(0.01,0.03,0.06,MC_STATIC(0.03,"Volume Growth",""))
    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 Demand group.
    The Demand group.
    The Demand group.
    The Demand group. Open full size
  3. 3Complete the groups on the Model page

    To add the other assumptions:

    1. On the MonteGrid tab, click Open MonteGrid. The Correlation groups panel shows Demand with Volume Growth.
    2. Under Demand, select Year 1 Volume in Add assumption….
    3. Enter Costs in New group and click Add.
    4. Under Costs, select Unit Variable Cost, then Fixed Cost, in Add assumption….

    Leave the coefficient boxes empty to use 0.6. Save the workbook.

    Correlation groups: Demand.
    Correlation groups: Demand. Open full size
    Correlation groups: Demand and Costs.
    Correlation groups: Demand and Costs. Open full size
  4. 4Run and compare

    On the MonteGrid tab, click Run. The settings are unchanged, so only the correlation differs from the first run.

    NPV ($M)IndependentCorrelated
    P10-36.88-42.76
    P505.155.11
    P9050.6159.30
    SD34.6740.47

    In the rank correlations, Volume Growth moves from fifth to second.

    The NPV forecast with correlation.
    The NPV forecast with correlation. Open full size
  5. 5Correlation matrices

    A correlation matrix sets a coefficient for each pair of assumptions. An assumption can be in a group or in a matrix, not both. The model in these tutorials does not use a matrix.

    To define a matrix:

    1. Select an empty cell for the top left corner of the matrix.
    2. On the MonteGrid tab, click Define Correlation.
    3. Enter a name and select the assumptions.
    4. Enter the coefficients in the upper triangle.
    5. Click Write matrix.

    MonteGrid writes the matrix to the worksheet as a block: the name in the corner, the assumptions across and down, and the coefficients between them. If the coefficients cannot all hold, the dialog offers Adjust to nearest.

All tutorials