top of page
Jet Reports

Bank Balance Detailed Report

A Bank balance report is a financial report that provides information on the cash balance of a customer, a company, or an organization's bank accounts. it typically includes all details on every transaction that has occurred in a bank account over a specified period, such as deposits, withdrawals, and fees.

The report shows the total balance of all transactions that occurred during the period. The aim of creating this report is to give a clearer picture of transaction-by-transaction of the cash position of an organization’s bank accounts. This information can be used to make financial decisions about when to invest surplus funds or borrow to meet cash shortfalls. This report is also useful to reconcile any errors in bank transactions by tracking fund’s movement in and out of a bank.

Join On Point Academy to view all content

To create this Bank account analysis report, we need information from 2 different tables…

  1. Bank account

  2. Bank account ledger entries

 

The following information as shown below will be retrieved from the table.



Procedure

Step 1 Know where to pull your data from


The NL(Rows) will contain our source of data, which will be pulled from the table “Bank Account and Bank Account Ledger Entry“. The NF (Keys) will be a cell reference, which will be referencing cell $D%(Bank Account) and $J5(Bank Account Ledger Entry), and the fields will be pulled from the names as shown.



Step 2 Creating Report Options


I’m applying two filters to my report, “Date filter“ and “Company“

Click on the report options tab in the ribbon, and the pop-out, and fill in as shown.

The Company filter is applied in case you want to run the report for a particular company.

Note: When Filling in the Date range, you can separate it as “Period Start and Period End“. Each is on a separate line.

E.G. Period Start = 1/1/2022

Period End = 12/31/2022.

Or use as Date Range as shown here… I want my report to show all transactions from the first one to the selected end date, which speaks to why the formula is (=../02/28/2023)



Step 3 Filling in the formulas


Since we now know where to pull our table, keys, and fields from, I’ll show you how to do that here. Once you are done creating the functions, you can remove the names on the last row. They are just there to guide you on the name to put in the field when filling in the formulas.

  • filling the formulas for the NL(Rows)

Fill in as shown below for the NL for Bank Account, apply the filter for company… referencing to the report Option.



  • Filling the formulas for NF

fill in as shown below

Note: use the same procedure to fill in for all NF, the only difference will be the field.



  • Go to cell J5, Fill in for NL (Bank Account Ledger Entry) as shown below

Apply the date filter and the company filter here also… all referencing the report option.

Note that when applying the filter on “No.”, we refer to the No. on our report in cell E5.



  • Fill in for the NF (Bank Account Ledger Entry)

  • Note: use the same procedure to fill in for all NF, the only difference will be the field.



When you are done filling in the formulas, your report should look as shown below.



Step 4 Run your report.

When you run your report, it should be like this.



Get trained
the On Point way

From self paced online courses to private training sessions.
Become industry ready.
bottom of page