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.
To create this Bank account analysis report, we need information from 2 different tables…
Bank account
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.