Creating Monthly Reconciliation Statements Based on Delivery Recepits

Many companies operate on a "monthly statement" basis for transactions. Instead of settling payments for each individual shipment, invoices are usually accumulated throughout the month and settled at the end of the period. This system is particularly useful when involving frequent orders, as it reduces the administrative procedures of processing payments multiple times, which saves time for both parties.

Other than existing documents such as purchase orders, delivery notes, and sales invoices, a "Monthly Reconciliation Statement" (or "Monthly Accounts Receivable/ Payable" Statement) is usually produced as well. Having a system that can automatically consolidate and calculate relevant data such as customer information, accounts receivable, accounts payable, and billing month from individual records into their corresponding monthly statement can create a more efficient workflow than manually compiling each customer's monthly statement. This way, you would only need to review the final reconciliation statement.

In this article, we use a typical Delivery Note (shipping receipt) as an example to demonstrate several methods of designing an automated Monthly Reconciliation Statement on Ragic, based on delivery (shipping) details.

Note: In situations where customers frequently place orders, you can:

(2) Issue a single order per month and generate multiple shipping invoices from it, since shipments are made in multiple batches. You may also adopt other methods that suit your needs better. With Ragic, you can flexibly implement different workflows and designs based on your business requirements or situation, rather than adhering to a single, fixed process.

Suppose your delivery note looks like this:

Basic shipping and sales information are placed in Ragic's regular fields, while sales and delivery details, which usually consists of multiple items, are placed in subtables.

And you would like to generate a Monthly Reconciliation Statement like this:

Or like this:

In other words, a reconciliation statement is created every month for each customer, which includes fields such as the billing month and basic customer information. At the end of the month or statement date, the statement automatically lists the sales details and amounts for that month, then calculates the total sales amount for the month, which is the amount due for settlement.

You can achieve this using the following methods:

Method 1: Creating a Reconciliation Statement via Grouping Report (Easier Approach)

This method uses Ragic's "Grouping Report" feature to show sales details and grouping them as "one record per customer, per month." Since grouped reports can automatically add or sum numerical values within each group, you can use the generated report as a reconciliation statement and refer to it whenever needed.

Here are the steps to do this:

1. The reconciliation statement should list product delivery details, which come from the subtable of the Delivery Note. However, since Ragic currently does not support generating reports directly from subtable data, we first ned to use the create new sheet from subtable function to convert the delivery item subtable into an independent record. This will generate an independent Delivery Details sheet like below:

Some fields from the original sales invoice, such as "Customer" and "Date" are not part of the subtable, but still need to be included in the new Delivery Details sheet. To include these fields, simply use the "Link Field from Parent Sheets" function to bring them into the new sheet.

2. Next, use the Report generation feature to create a Grouping Report from the Delivery Details sheet. For a simple scenario wherein the billing month corresponds to the shipment date's month, you can select "Date" as the "Group By" field and choose to group by month. Save and name your report as "Reconciliation Statement." When saving your report, select "Customer" as the "Filter" to generate reports that only show sales items for specific customers within the billing month.

Feel free to take the opposite approach and set Customer as the "Group by" field and Billing Month as your "Filter." Additionally, you can choose a specific field for sorting (eg. Date). Remember to tick the "Show group total" box to show each section's total amount.

If you don't see the field you want in the Group by selection menu, select the "Click here to select fields to display in the report" and move the desired field to the "Selected fields" area.

If you don't want to manually filter customers every time you're accessing the report, or if you would like to add additional notes for the billing month, you can create the "Billing Month" and "Billing Month - Customer" fields directly on your Sales or Shipping Invoices. The "Billing Month" field can be automatically populated using formulas, while the "Billing Month - Customer" field can be generated using a string formula such as C1+"-"+A3, where C1 is the Billing Month field and A3 is the Customer field.

Once created, you can then add these fields into your Sales Item sheet using the "Link Field from Parent Sheets" function mentioned earlier.

Method 2: Creating a Reconciliation Statement Sheet by Linking to Delivery Details

If you need to make further calculations with the calculated fields from the reconciliation statement (eg. current month reconciliation amount/ current month receivable amount), you'll need to create a reconciliation statement sheet in Ragic, rather than using the Report feature. An example would be if you would like to track the paid and unpaid balances.

Using the same Delivery Note sheet, follow these steps:

1. Create a Reconciliation Statement Sheet, where each record represents one customer, for one month. Create a "Monthly Reconciliation Statement ID" field as your primary key field as the unique identifier. Some examples are: "202501 ABC Corporation", "202502 ABC Corporation", "202503 ABC Corporation", "202504 ABC Corporation", and so on.

2. Create a "Monthly Reconciliation Statement ID" Field to the Delivery Note Subtable and create a link to the "Monthly Reconciliation Statement ID" in the Reconciliation Statement Sheet.

Using this method, users must select the corresponding "Monthly Reconciliation Statement ID" whenever filling out Delivery Note details. If the reconciliation statement for the month has already been created and follows the "Month- Customer Name or ID" format, you can automate the selection by applying a string formula into this field. By doing so, the "Reconciliation Statement ID" will automatically be filled in (or selected) when users enter the Customer Name and Month.

3. Similar to earlier, use the New Sheet from Subtable function to convert the delivery items into an independent Delivery Details sheet. Ensure that the new sheet includes the "Monthly Reconciliation Statement ID" field.

4. Return to the Monthly Reconciliation Statement Sheet. Since the Delivery Details Sheet's "Monthly Reconciliation Statement ID" field is linked to the Monthly Reconciliation Sheet, you can use the show references from existing sheets function to retrieve the Delivery Details for that customer, for the month. Finally, add an "Accounts Receivable" field. Apply a formula to SUM all of the amounts from the linked Delivery Details, automatically calculating the total receivable amount for the month.

Advanced Tip 1: Generating Monthly Reconciliation Statements from Invoices or Customer Purchase Orders

If you need to generate the monthly reconciliation statement based on other documents such as sales invoices, customer purchase order numbers, shipping notes, and the like, you can simply replace the "Delivery Note" in this guide with your preferred document type.

If you don't need to see each individual delivery item detail in the reconciliation statement and wish to only consolidate the Delivery Note numbers, you do not need to use the Subtable to Sheet function to generate a "Delivery Details" sheet. You can directly use the "Show References from Existing Sheets" tool to insert the Delivery Note data as a reference subtable in your reconciliation statement.

If you are from procurement or purchasing and need to create monthly reconciliation statements for suppliers or to track monthly accounts payable details, the same logic applies: Replace the "Delivery Note" in this guide with your "Purchase Invoice" or "Purchase Order" sheet, and replace "Customer" with "Supplier" to manage payables instead of receivables.

Advanced Tip 2: Automatically Generate Billing Month with Formulas

Previously, we have discussed about generating Monthly Reconciliation Statements using Grouping Reports and using Reference Subtables. Regardless of which method you use, a "Billing Month" field is required. Other than manually entering the values for this field, you can use a formula to automatically generate the billing month.

The simplest method is when billing month is equal to the delivery date's month. For instance, if the delivery occurred on 2025/08/25, then the billing month should be 2025/08. Assuming that Delivery Date is on field E1, you can use the formula: LEFT(E1,7) on your Billing Date field to automatically extract the year and month from the date.

In practice, however, many companies do not use the Delivery Month for billing. Instead, they may have a cutoff date within each month, where transactions before this certain date belong to the current billing month, while transactions after the date will roll over into the next month. For example, if the cutoff date is on the 25th of each month, deliveries made on or before the 25th are billed in the current month, while deliveries made after the 25th are billed in the next month.

If your company follows this method of billing, you can adjust the formula accordingly:

How to Setup?

(This example uses the Delivery Note sheet, but you may apply this logic to other sheets as needed)

1. Create a Numeric field for the Billing Cutoff Date, as shown in the screenshot below. The field in the example is on K4.

2. Add another Numeric field (K5 in this example), and use the formula DAY(I5), where I5 is the Delivery Date. The extracted day will be used in further calculations.

3. Create a new Date field. In this example, we place it on K6. We set the format as yyyy/MM to show year and month. This field is used to generate the next month's billing period based on the Delivery Date (I5) information. Use the formula: DATE(YEAR(I5),MONTH(I5)+1,1) , which extracts the year and month from the Delivery Date I5 and shifts it to the first day of the following month. Since the format is in "yyyy/MM," only the year and month will be displayed.

4. Add a new date Date field (K7 in the sample) for the Billing Month, then use the IF() conditional formula for the system to determine which Billing Month the Delivery Note belongs to.

If K5 (Delivery Date Day) is equal to K4 (Billing Cutoff Day)+1, then return I5 (Delivery Day). Otherwise, return K6 (next month's billing period).

This way, if your Delivery Note date is greater than the cutoff date, the Billing Month will show the next month.

If you find that these calculation fields clutter the page, you can Hide them through Field Settings and keep your sheet looking clean!