How to: Calculate daily settlements and reconciliation reports for PowerBoard

Welcome to the PowerBoard Dashboard tutorials. In the previous video we demonstrated how to create and download a reconciliation CSV report. Today, well show you how to use your downloaded reconciliation CSV report to calculate your daily settlement amount. To begin, open the downloaded spreadsheet. Select the first row of data cells and in the home bar tab head to Sort & Filter, then apply the filter options to the cells. The filter arrows should now appear in the cells in the first row. Click on the arrow to filter the Customer payment source card scheme column and filter the data to exclude the AMEX charges in the dropdown. Click OK. Next, filter the daily processed transactions by clicking on the transactions processed at column and navigate down to the Text Filters field and select the Custom Filter option. The Customer Autofilter window will appear, with the caption Show rows where.

For this example, we’re going to filter transactions for the settlement date 16th January 2025. In the first field select the is greater than option from the dropdown. In the second field, to the right, type in the date, followed by the local time zone. The date format should reflect: the year, dash, the month, dash the day. It’s important to remember the date entered must be the day prior to the settlement report date. In the same field, type a capital T, to represent time zone, and enter the settlement time in digital 24-hour time along with the time zone. The time of 5:30pm represents the time of settlement from the previous day. The plus 10 indicates Australian Eastern Standard Time. If it’s Australian Eastern Daylight Time change the plus 10 to plus 11.

Now select the And option … go to the third field and select the is less than or equal to option from the dropdown. In the final field of this window, enter the current date, time of settlement and time zone using the same format as above … or copy and paste the same text from the second field and change to the current date. Then click the OK button to display only the transactions which have been completed in the last 24 hours since 5:30PM the previous day. The next step is to calculate the total settlement amount. There are 3 stages to calculating the settlement amount. Firstly, Direct Sales, secondly Captured Pre-Auth Funds and thirdly Refunds Processed. To calculate the direct sales amount, click on the arrow to filter the Authorization column and select False. Ensure to deselect any other options. Click OK. Then click on the arrow to filter the Transaction type column, select sale... then Click OK. Calculate the total sum amount by highlighting the data in the transaction amount column. You’ll see the total sum amount in the bottom right of the screen. Make a note of this amount in an unused cell and name it sale.

To calculate the captured Pre-Auth amount, click on the arrow to filter the Authorization column then select True … then OK. Next, click on the arrow to filter the transaction type column, select Capture then Ok. Calculate the total sum amount by highlighting the data in the transaction amount column. Once again, you’ll see the total sum amount in the bottom right of the screen. Make a note of this amount underneath sale and name it capture. For the refunds process, start by clearing the filters under Sort & Filter then click on the arrow to filter the Transaction type column… then, select Refund and click OK. Make a note of this amount underneath capture naming it refunds. To calculate the total settled amount, add the sale and captured amounts and minus the refunded amount. This value should match the settlement amount in your bank account for any transactions processed through PowerBoard that day.

In this example the direct sale amount is $118, the captured pre-auth amount is $148 and the refunded amount is $48. So, the total daily settlement amount will be 118 plus 148, minus 48, equals $218. It’s important to note this daily settlement amount will exclude any APM transactions like Afterpay or Pay Pal and will have different settlement times and so require a separate Reconciliation CSV report. This daily settlement amount may exclude any AMEX transactions which can take up to 3-5 business days to settle. Once settled you can filter these by following the instructions in this video, ensuring to filter these card schemes in the customer payment source card scheme column. Discover more new features on the PowerBoard Developer Hub.