Skip to main content

How to use cumulative totals to create beginning and ending balance reports?

Updated over a week ago

Overview

Beginning inventory and ending inventory represent the quantities of a product at the start and end of a specific period.

  • Beginning inventory: Quantity of a product at the start of a given time period.

  • Ending inventory: Quantity of the same product at the end of that time period.

Creating balance reports by cumulative totals, you can better calculate turnover rates, inventory costs, and other performance metrics, track the stock movements, and make informed decisions.


How to Design It?

Note:

The method described here calculates beginning and ending balances only for products with stock movements during the period. Items with no inbound or outbound records will not appear in the report.

1. Select the Data Sources via Data Stream in Data Factory.

2. Aggregate data by the desired period, e.g., Year–Month.

3. Use formulas to determine the transaction amount (inbound – outbound) for each period.

4. Add cumulative fields for cumulative inbound and cumulative outbound quantities.

5. Calculate Ending Balance: Cumulative Inbound – Cumulative Outbound

6. Calculate Beginning Balance: Ending Balance + Outbound – Inbound

7. Visualize the calculation results in a Dashboard.


What Does It Look Like?


How to Configure It?

Step 1 Adding Data Stream

Go to App Management, click Data Factory > + New Data Stream.

Step 2 Selecting Input Data Source

Rename the data stream and add three Input nodes. Since Material Inventory, Material Requisition, and Return of Items forms contain all the outbound and inbound records of the items, select them as the input data source.

Step 3 Setting Data Filter

Add a Data Filter node after each Input node to filter out the data that passes the approval node in the workflow. Only these data should participate in the calculation of the inbound and outbound inventory.

Step 4 Aggregating Filtered Data

Aggregate the filtered data from the three forms into a single table. Specifically, aggregate the data related to outbound inventory into one column, and the same for inbound inventory:

Combine into a single dataset with two main columns: Inbound Inventory and Outbound Inventory. Map the Quantity Claimed as outbound inventory. Map all returns and receipts as inbound.

Remove unnecessary fields (e.g., approval comments) if not needed.

Step 5 Grouping and Summarizing

Perform Group by on aggregated data. If you need to calculate the beginning and end inventory for each month, then you can group the data on a monthly basis:

This gives you a monthly outbound or inbound inventory for each product.

Step 6 Calculating Beginning and Ending Balances

Add a Field Settings node to calculate the beginning and ending inventory.

Calculate the Period Total

Add the formula:

Period Total = Inbound Inventory - Outbound Inventory

The data preview is like:

Calculating Ending Balance

Similarly, add Running Total field and set the Data of Application of Period Total as ascended. Then you can get the ending balance. Configure it as follows:

Calculate Beginning Balance

Use the formula:

Beginning Balance = Ending Balance – Period Activity

After setting all the calculations, you can preview the data:

Step 7 Publishing Data Stream to Dashboard

Publish the accumulated and aggregated data through dashboard. Members can preview the inbound inventory and outbound inventory of the beginning and end period.


I have finished reading. 🏆

👉I can't wait to Try for myself.

👉I need more HELP in Discord Forum.

Did this answer your question?