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 athe cash flow projection report showing the invoice, bills, journals, bill/ customer payments, vendor prepayment, 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 weekbuckets 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 alsoto 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 6 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 reportbased on the default filters, such as the subsidiary asAll,
As of Date astoday, 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 days. |
| 3 | WK2 | This column will be updated with the date difference of trandate/duedate and the date selected in the filter is between 8 and 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 14 and 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 21 and 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 28 and 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 35 and 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 47 and 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 will make use if 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.
- This report will retrieve data from Account balance based on chart of account/ Trail balance report for the row ‘Cash at Beginning of the week’.
- The remaining rows will be retrieve the amount from Invoices, Bills, Journals, Deposit and Checks, Bill Payments/ Customer Payments.
- To group the accounts for the report, use 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.
- Consider the Due date in Invoice and Bill transactions, as well as the date in Journal, Bill Payment/ Customer Payment, Deposit and Check transactions.
- Take 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.
- Print report as 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.