Use row-based modeling to organize the rows in a planning sheet into a hierarchical structure that represents business drivers and outcomes. Each row can act as a driver, allowing you to define relationships between inputs and calculated results. This approach helps you analyze how changes in one row affect related rows. For example, if Cost changes, you can see how the change affects Net Profit through the defined row relationships.
By using a row model, you can:
Organize rows into meaningful driver hierarchies according to business requirements.
Build complex planning logic from simple data structures.
Perform planning, forecasting, and simulations at the row level.
Analyze the cascading impact of changes to a driver across the entire model in a single view.
Give business users greater control over planning drivers and assumptions.
Create a planning sheet by assigning the required dimensions and measures from the semantic model.
Consider the following planning sheet with the Chart of Accounts as the row dimension, Year, Quarter, and Month in the Columns field, and Actuals in the Values field.
Go to the Model ribbon and select Row Model. The Model Builder pop-up opens. Select Enable.
This action enables Model Builder. You can select Back to Home to save the report and return to this screen by selecting Row Model again.
The model builder displays each row as a configurable driver for both open and closed periods, enabling you to build row-based models.
To build your model, delete all rows except the topmost All row. Select the row selection box in the column header, clear All, and then select Delete.
To create the row hierarchy, select the top row and then select Bulk Insert.
In the side panel, enter the rows you want to add. Use Tab to add a row as a child at the next hierarchy level.
Select Add. The rows are added as shown in the following image.
Configure each row to build a simple Profit and Loss (P&L) model.
Select the pencil icon on the topmost row (All) to configure its properties:
Row Name: Enter Operating Profit.
Configure as: Select Formula.
Enter the formula in the formula box as, [Operating Income] − [Operating Expense]. As you type, suggestions pop up, and you can choose the rows from the References tab.
Select Apply.
Configure all leaf rows one by one. Select the pencil icon on Revenue or select its Type dropdown. Configure the type as Data Source.
In Choose Close Period Source Row, select the corresponding source row from the semantic model, and then select Apply. It is automatically applied for the Open Period as well.
Ensure its Desired Trend is Increase (since it is revenue), and then select Apply again.
Now, configure the Purchase row:
Configure it as Data Source.
Select the source row from the semantic model using the list.
Set its desired trend as Decrease (since it is an expense).
Select Apply.
Repeat the above step for the remaining child rows, Transportation, Adv & Promotion, and Salaries.
Configure the parent rows:
Operating Income: Configure as Aggregate to aggregate its child rows. Select Sum as the aggregation type. Set the desired trend to Increase.
Operating Expense: Configure as Aggregate to aggregate its child rows. Select Sum as the aggregation type. Set the desired trend to Decrease.
The row model is completely configured as shown in the following image.
Select Back to Home to return to the planning sheet and view the created model.
Select a row, and then select Add Child or Add Sibling to add a child row or a sibling row at the same hierarchy level.
Configure a different setting for the Open Period, if required.
Use the Aggregation dropdown to roll up the row's values across the selected time period (column dimension).
How the row model helps analyze business performance
A row model helps you analyze how changes to individual drivers affect related rows in the hierarchy. When you modify a driver, the resulting changes flow through the related calculations and parent rows.
For example, if you increase Revenue, the model calculates the corresponding impact on Operating Profit based on the relationships and formulas defined in the model.
You can also use a row model to forecast, create multiple scenarios, and perform simulations to compare different driver assumptions and evaluate their impact on business performance.
