Building a Custom P&L Report in NetSuite
One of our UK-based clients had a custom requirement to build a Project Profit and Loss (P and L) report on NetSuite.
The client required us to build a Project custom P and L report constituting the cost and revenue side of project records and reports as well as provide rates in USD. They specifically required the currency to be in USD and in case of multi-currency transaction, it should be converted to project currency (USD).
Although, building a P and L report is easy in NetSuite. Our client had specific requirements on report grouping and summary, how the income and cost should be calculated, and how the report should be emailed in CSV.
NetSuite originally allows employee costs to be calculated based on the employee record but here the client required these to be calculated in USD since the labor rate is confidential and not visible to all users.
Our developers initially created a Suitelet page – ‘P and L report based on entity’ in NetSuite aligned with the needs of the client. Through the Suitelet page, the user can filter the report for a specific time period and choose the desired client. Refer the image 1 below.
Upon clicking on the ‘Send real-time request button’ the user is able to download a default P and L based on the selected client. Further, our developers also built the option to drill down on each client and choose the required project under each client to create a custom report for the user. Here the user can select the client and click on the ‘Show projects’ button to look into the projects. We also added custom filters like Project Manager, Project Subsidiary and Project Status through which the user can pinpoint the desired result.
Upon clicking the ‘Send Real-Time Request’ button, an email notification is sent to the user along with the downloadable CSV report attachment and the P and L CSV can be downloaded on click of the ‘Download’ button on the page or from the email notification. Here the user can see his latest requested/ generated report. Refer the below image for a better understanding.
We added the following entities in the P and L CSV:
• Sales order
• Revenue recognition
• Time track
• Expense Report
• Vendor Bill
• Bill credit
• Credit memo
• FX conversion (currency exchange rate)
(Note: These entities may be customized according to the client or Project requirement.)
The project custom P and L served the purpose of the client successfully. Notably, this is not a standard NetSuite P and L as the computational methods were different and were tailored according to the needs and business logic of the client.