Create a model using Model Builder
In this article, you learn how to build a model for your data using the Model Builder.
Consider the brewery data sample shown below in the planning sheet.
The first column contains a list of General Ledger (GL) account items for various quarters of the year 2026. In this module, you learn how to create a sample Profit and Loss (P&L) model using this example.
Note
Data can include both actuals and forecasts, and models can be configured to handle both closed and open periods.
To learn more, refer to Hybrid row configuration.
Objectives and approach
You learn how to group related line items into a hierarchical structure.
You create formula or aggregate rows to calculate key financial metrics such as Gross Revenue, Net Revenue, Total Cost of Goods Sold (COGS), Gross Profit, Operating Income (EBIT), Income Tax Expense, and Net Profit.
Finally, you build a reusable and scalable P&L model for your sample data, step by step.
Create model
Go to the Model Builder by selecting Model > Driver Model and then selecting Enable in the pop-up.
The Driver Model view opens, where you build the model.
Delete all rows except the topmost row (All) to start building your model from scratch. To delete rows, select the required rows and then select Delete.
Tip
Select the Select All checkbox in the column header to select all nodes, then clear the selection for the top node.
The model will look like this, with one row:
Start with the top-level parent row in your model. Since the objective is to calculate Net Profit, rename the top row to Net Profit by double-clicking the All row and updating its name.
Add Child row
The net profit or the net income for this model can be calculated using the formula:
This means the Net Profit row needs two child rows: Income Before Tax and Income Tax Expense.
To add a child node, select the + icon next to the node. You can also select the node to which you want to add child rows, then select Add Child and choose the type of row you want to add.
Add another child row for Net Profit and rename both rows.
Row type configuration
The Net Profit row is a formula row. In Type, select Formula. Alternatively, you can select Aggregate.
Select Configuration. Choose Subtract from the side panel that opens automatically. Select Apply.
Note
Using Aggregate automatically aggregates the child rows, while Formula uses explicit row name references to perform the calculation.
If row names change, make sure to update the formula accordingly.
Similarly, configure the child rows' type as per your requirements. Select Data Source to retrieve values from the source data, or choose Data Input to enter values manually.
For a Data Source row, select the corresponding row from the data source in the side panel to retrieve the values. If you choose the Data Input type instead, enter the values manually in the side panel.
By following the above steps, you have created a calculated row at the top level.
Now, repeat the same process to add additional child nodes and continue configuring their row type and configuration settings.
Add Formula type row
For some rows, selecting the Formula type is more suitable than using the Aggregate type.
For Income Before Tax (EBT),
Create four child rows.
Configure the Type as Data Source for the child rows if you have data in the source.
For each row, select the corresponding row from the data source.
For the parent row, Income Before Tax (EBT), configure it as Formula type.
Enter the formula in the formula box by selecting and referencing the required rows. As you type, suggestions appear automatically, or use Ctrl + Space to view them. You can switch between the References and Functions tabs to select row references or functions.
Complete other configurations for the row and select Apply.
Build further rows
Use the steps described above to create and configure additional row structures.
Tip
Alternatively, use the Bulk Insert feature to instantly build the model.
Add Sibling row
To add a new row at the same level as an existing one, select the row, and then select Add Sibling. This creates a sibling row for the selected row.
In the example above, two line items, Distributor Allowances & Rebates and Returns & Breakage, are to be added at the same level as Federal & State Excise Taxes.
You can add two of them as sibling rows to the first line item.
Repeat the same steps to build the Total COGS section and complete the model. You can also use the Bulk Insert and Bulk Edit features to speed up the process.
Add multiple rows using Bulk Insert
Use Bulk Insert to add multiple rows at once or build the entire model in one step. This is useful when you already have the model structure planned or prepared.
Select the row under which you want to add new rows.
Select Bulk Insert.
Enter the row names, with each row on a new line.
Use the Tab key to create child rows and define the hierarchy.
Choose whether to insert the rows as Child or Sibling rows to the selected row.
Select Add to apply the changes.
The rows are added under Total COGS based on the defined structure. Update the type and configuration for each row as needed.
Aggregation
The Aggregation property is typically set to Sum to roll up column values across the period from January to December. For rows that represent rates or percentages, set it to Average (Children).
Edit multiple rows using Bulk Edit
If multiple rows share common settings, use Bulk Edit to format them all at once.
Select the rows that you want to format.
Select Bulk Edit.
In the side panel, enter the required settings, such as scale, decimal points, prefix, suffix, desired trend, simulation settings, and description.
Select Apply to apply these settings to all selected rows at once.
Finish model
The completed model looks like this:
The model you build applies to the Open Period by default, unless you modify specific rows by toggling to the Open Period.
Build model in one step
If you have the complete model structure handy in a notepad, you can build the entire model in one step by copy-pasting it through Bulk Insert.
You can then configure the type, configuration, formatting, and aggregation individually or through Bulk Edit.
You can reuse the model across different datasets that follow the same business logic. Maintain and scale the model by adding or removing rows and/or adjusting row configurations as needed to reflect changing business needs.
This approach reduces the need to recreate formulas from scratch and ensures consistency across datasets that follow the same logic. Features such as Bulk Insert and Templates help streamline this process and enable efficient model building at scale.
Next step
Visualize the model using the tree layout
Related content
Create a driver-based model
Last updated
Was this helpful?