Solve the RASON Simulation Model and Review Results

Business Forecast Simulation: Excel vs. Power BI Data Binding

These two RASON models run the same Monte Carlo simulation — forecasting profit under an uncertain unit cost and an uncertain market condition, across 1,000 trials. The simulation logic is identical; what differs is where the input data comes from, how much of it is imported versus defined statically, and how bound data is indexed in formulas. This page walks through both, explaining what each section does and calling out where the two examples diverge.

The Problem Being Solved

A business is forecasting next period’s profit under two sources of uncertainty: the unit cost of production, and which of three market conditions will occur. Each market condition (1, 2, or 3) has its own sales price and sales volume. Over 1,000 trials, the model samples a fresh unit cost and market condition each time, calculates revenue, production cost, and resulting profit, then collects statistics — the average profit, the profit from every individual trial, and the probability of clearing a $100,000 profit target.

Model Flow

Simulation Settings

Setting modelType to simulation tells RASON to run a Monte Carlo simulation rather than solve a single deterministic result. modelSettings.numTrials sets how many times the model is recalculated — 1,000 in both examples. engineSettings.randomGenerator and samplingMethod control how the random draws behind each trial are generated; both models use the same settings (Lecuyer generator, Latin hypercube sampling), so this part of the model is identical between the two examples.

Importing Data

Both models import the triangular-distribution parameters used for unit cost. Where they diverge is the market data: the Excel model also imports sales price and sales volume by market condition, while the Power BI model defines that same information as static values directly in the model instead of importing it.

From Excel

"datasources": {
  "excel_data": {
    "type": "excel",
    "connection": "C:\RASONDesktopExcelExamples\ForecastData.xlsx",
    "selection": "Forecast with Uncertainty!H16:J17",
    "direction": "import",
    "header": true
  },
  "excel_data_1": {
    "type": "excel",
    "connection": "C:\RASONDesktopExcelExamples\ForecastData.xlsx",
    "selection": "Forecast with Uncertainty!H20:J20",
    "direction": "import",
    "header": true
  }
}

header marks the first row of the selected range as column labels rather than data. excel_data pulls in a 2x3 range covering both market rows (volume and price) across three market conditions. excel_data_1 pulls in the three triangular-distribution parameters for unit cost.

From Power BI

"datasources": {
  "mktDemandCost_Min": {
    "type": "PowerBI",
    "connection": "pbix=ForecastData",
    "selection": "EVALUATE SELECTCOLUMNS('mktDemandCost', 'mktDemandCost'[Minimum])",
    "colIndex": "mktDemandCost_index",
    "direction": "import"
  },
  "mktDemandCost_MostLikely": {
    "type": "PowerBI",
    "connection": "pbix=ForecastData",
    "selection": "EVALUATE SELECTCOLUMNS('mktDemandCost', 'mktDemandCost'[MostLikely])",
    "colIndex": "mktDemandCost_index",
    "direction": "import"
  },
  "mktDemandCost_Max": {
    "type": "PowerBI",
    "connection": "pbix=ForecastData",
    "selection": "EVALUATE SELECTCOLUMNS('mktDemandCost', 'mktDemandCost'[Maximum])",
    "colIndex": "mktDemandCost_index",
    "direction": "import"
  }
}

Instead of one datasource covering all three triangular parameters, the Power BI example splits them into three separate single-column imports — one per parameter — each pulled from the same mktDemandCost table via a DAX SELECTCOLUMNS query. colIndex ties the three back together row-for-row, so the first value in Min, MostLikely, and Max all describe the same record, even though each is fetched independently.

Binding Data to RASON Names

// Excel
"data": {
  "marketData": { "binding": "excel_data" },
  "productCost": { "binding": "excel_data_1" },
  "fixedCosts": { "value": 120000 }
}

// Power BI
"data": {
  "productCostMin": { "binding": "mktDemandCost_Min" },
  "productCostMostLikely": { "binding": "mktDemandCost_MostLikely" },
  "productCostMax": { "binding": "mktDemandCost_Max" },
  "productPrice": { "dimensions": [3], "value": [11, 10, 8] },
  "demand": { "dimensions": [3], "value": [50000, 75000, 100000] },
  "fixedCosts": { "value": 120000 }
}

This is the biggest structural difference between the two models. The Excel version binds two full imported tables — marketData (price and volume together) and productCost (the triangular parameters) — plus a static fixedCosts value. The Power BI version binds three separate imported columns for cost, but defines productPrice and demand as static arrays written directly into the model, even though the Excel example pulls that same price-and-volume information from the workbook. These differences aren't a recommended pattern to follow — they're intentional, showing how flexible RASON is at accepting the same kind of data through different import methods and data structures.

Uncertain Variables

// Excel
"uncertainVariables": {
  "unitCost": {
    "formula": "PsiTriangular(productCost[1,1], productCost[1,2], productCost[1,3])"
  },
  "marketType": {
    "formula": "PsiIntUniform(1,3)"
  }
}

// Power BI
"uncertainVariables": {
  "unitCost": {
    "formula": "PsiTriangular(productCostMin, productCostMostLikely, productCostMax)"
  },
  "marketType": {
    "formula": "PsiIntUniform(1,3)"
  }
}

PsiTriangular samples a fresh unit cost each trial from a triangular distribution defined by a minimum, most-likely, and maximum value. The Excel example reads all three parameters out of one row of the imported productCost table using 2D bracket notation — [1,1], [1,2], [1,3]. The Power BI example passes productCostMin, productCostMostLikely, and productCostMax directly, with no bracket notation at all, since each of those names is already a single imported value rather than a row in a shared table. marketType is identical in both models — an integer from 1 to 3, sampled fresh each trial.

Formulas

// Excel
"formulas": {
  "salesPrice": {
    "formula": "IF(marketType=1,marketData[2,1],IF(marketType=2, marketData[2,2], IF(marketType = 3,marketData[2,3])))"
  },
  "salesVolume": {
    "formula": "IF(marketType=1, marketData[1,1],IF(marketType=2, marketData[1,2], IF(marketType = 3,marketData[1,3] )))"
  },
  "totalRevenue": { "formula": "salesVolume*salesPrice" },
  "productionCosts": { "formula": "salesVolume*unitCost" }
}

// Power BI
"formulas": {
  "salesPrice": {
    "formula": "IF(marketType=1,productPrice[1],IF(marketType=2, productPrice[2], IF(marketType = 3,productPrice[3])))"
  },
  "salesVolume": {
    "formula": "IF(marketType=1, demand[1],IF(marketType=2, demand[2], IF(marketType = 3,demand[3] )))"
  },
  "totalRevenue": { "formula": "salesVolume*salesPrice" },
  "productionCosts": { "formula": "salesVolume*unitCost" }
}

Both formulas sections look up the price and volume for whichever market condition was sampled that trial, then calculate revenue and production cost. The indexing again reflects how each model stores its market data: Excel reads row/column pairs out of one combined table — marketData[2,1] for price, marketData[1,1] for volume — while Power BI reads a single position out of two separate 1D arrays — productPrice[1], demand[1]. totalRevenue and productionCosts are identical in both models.

Uncertain Functions: Collecting Results

"uncertainFunctions": {
  "newProfit": {
    "formula": "totalRevenue-productionCosts-fixedCosts",
    "mean": [],
    "trials": [],
    "target(100000)": []
  }
}

This section is identical in both models. Unlike formulas, which are recalculated fresh every trial, uncertainFunctions are tracked and analyzed across all 1,000 trials. mean returns the average profit across every trial, trials returns the full list of individual trial results, and target(100000) returns the probability that profit reaches or exceeds $100,000 in a given trial.