Proposal summary
This proposal is to create a cash flow report showing the invoice, bills, journals, bill payments, deposits and checks on weekly basis.
Requirement
Yash Industries Pvt Ltd needs to have a the cash flow projection report showing the invoice, bills, journals, bill payments, deposits and checks. They need to see the report of cash inflows and outflows in the bank account wise and the report will be divided in week buckets of 7 days.
The report should be filtered based on the subsidiaries, As of Date, Department and Vendor.
They would like to have the details of all the banks and amounts that are in the base currency (i.e., USD) and also to download the report in the CSV format.
Sample Report:
Our Solution
This requirement can be achieved by creating a new custom cash flow projection report using 5 transaction saved searches, and these searches will retrieve data from all the transactions based on the filters: Subsidiary, As of Date, Department, and Vendor selected in the header of custom report.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Custom Cash Projection Report. Then upon clicking the “Custom Cash Projection Report”, the user will be redirected to a report page.
The report will first display the detailed report based on the default filters, such as the subsidiary as All, As of Date as today, Department as All, and Vendor as All.
The fields given below will be indicated on the header part of the report.
| Criteria/ Available Filter: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | As of Date | Transaction Date from Journal, Bill Payment/ Customer Payment, Deposit, Check transactions and Due Date from Invoice and Bill transactions. |
| 3 | Department | Department field from all transactions. |
| 4 | Vendor | Vendor field from transactions |
| Report Rows: | ||
| # | Field | Field Name from NS records |
| 1 | Bank account | This row shows the custom Bank Account field from Invoice and Bills transactions, standard Account field based on the filters. |
| 2 | Cash at beginning of the week | All transactions will be considered to generate the balance for each account, and this row will display the values based on the transaction date, department, and vendor filters on all records. |
| 3 | Invoices | Generate the Open Invoice and Bill search with week columns to populate the Remaining Amount values based on the due date, department, and vendor filters. The account will be considered from the custom Bank Account field. |
| 4 | Bills | |
| 5 | Journals | Generate the journal search with week columns to populate the Amount values based on the transaction date, department, and vendor filters. The bank account will be considered from the lines. |
| 6 | Deposit and Checks | Generate the Deposit and Check search with week columns to populate the Amount values based on the transaction date and department filters. The account will be considered from the standard Account field. |
| 7 | Bill Payments/ Customer Payments | Generate the Bill Payments and Customer Payments search with week columns to populate the Amount values based on the transaction date and department filters. The account will be considered from the standard Account field. |
| 8 | Subtotal | Sum of all rows from 2 to 7. |
| 9 | Closing Balance | Same as Subtotal. |
| Report Columns: | ||
| # | Field | Field Name from NS records |
| 1 | WK0 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between –6 and 0 (WK0-7 days). |
| 2 | WK1 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between 1 and 7 (As of date+6 days). |
| 3 | WK2 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between 1 and 13 (WK1+13 days). |
| 4 | WK3 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between 1 and 20 (WK1+20 days). |
| 5 | WK4 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between 1 and 27 (WK1+27 days). |
| 6 | WK5 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between 1 and 34 (WK1+34 days). |
| 7 | WK6 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between 1 and 41 (WK1+41 days). |
| 8 | WK7 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between 1 and 48 (WK1+48 days). |
Finally, on the report page, an Download button will be added to download the detailed report in CSV format.
When you click the export button, the report will be downloaded in Excel format based on the report’s size in the following cases:
- If the file size is less than 10Mb (based on predefined line count), the excel file will be downloaded directly from NetSuite. And in real-time, the excel file will be exported by split files based on the line counts that can be included in the single file.
- Otherwise, the Report Download will take place through a scheduled process where there is a delay in the process based on the availability of the NetSuite Queue. For that, we will make use of a custom record to track the requests
- If the file size is greater than 10Mb, then we will split the file into multiple files with predefined line counts in a single file due to the size of each individual attachment cannot exceed 10Mb. And the generated multiple files will be emailed to the initiator’s email if the file count is smaller than or equal to the specific count that we can attach in the email.
- If the size of files is greater than the specific count, we will generate multiple files with predefined line counts in a single file. And all generated files will be saved in NetSuite’s file cabinet and attached to the corresponding custom record entry which tracks that Report generation process.
Once all export functions have been completed in NetSuite, a single email with the custom record link will be sent to the download initiator. The initiator can export files by login to the NetSuite and by navigating to the custom record entry with the link from the email.
The Subtotal line on the CSV file will be calculated based on the result lines included in the corresponding file in the case of split file download
Note: Exported multiple files will be attached to the email is a very exceptional case. i.e. Most of the export function occurs in real-time or the files attached to the custom record.
Assumptions
- The report results will be generated based on the filters (Subsidiary, As of Date, Department, and Vendor) selected in the header of the custom report.
- This report will retrieve data from account balance based on Chart of Account/ Trial Balance report for the row ‘Cash at Beginning of the week’.
- The remaining rows will be retrieving the amounts from Invoices, Bills, Journals, , Bill Payments/ Customer Payments, Deposit and Checks.
- In order to group the accounts for the report, we will be using:
- The Custom Bank Account field from the Invoice (Bank Information Subtab) and Bill (Billing Subtab) transactions.
- The standard Account field for Journal, Deposit, and Check transactions.
- We will be considering the Due date in Invoice and Bill transactions, as well as the date in Journal, Bill Payment/ Customer Payment, Deposit and Check transactions.
- We will be taking the Remaining Amount from the Bill and Invoice transactions, as well as the Amount from the Journal, Payments, Deposit, and Check transactions, to calculate the amount on a weekly basis.
- The overdue transactions will be considered in the next week’s column.
- The amount sign varies as per the credit and debit amounts on the bank account lines in the GL impact.
- Export option will be available in CSV format.
Risks
- The line counts in the single file to progress with the scheduled process can be confirmed during the development phase.
- If the file is more than 19Mb, the user should go to NetSuite and manually download the file from the account.
- The file cabinet will be stored with many files in the future. This will have an impact on the NetSuite account’s file cabinet. So, users have to remove these files from a file cabinet in the future to free up the storage.
- The report on the UI will be displayed as normal NetSuite Table layout without any styles. It will follow the standard behavior.
- We won’t be able to find all of these rows in a single search. To overcome this, we should generate separate searches for each type of transaction. Then, using the script, we need to join all of these search results to display all columns on one page.
- Combining 5 searches based on the page index within the Suitelet. Because there will be a chance to reach the script execution limit/time limit. So the script may end up with an error without providing the result. The occurrence of this scenario can be confirmed only at the time of development.