Proposal summary

This proposal covers the scope of implementing custom solutions for the P&L reports for project and project clients, Project creation restriction, resource and service item assigning scripts.

Requirement 

The client would like to have a detailed P&L report that consists of different transactions that constitute to the Cost and revenue side of project records and the reports should also provide rates in USD based on a custom fx table and a dynamic FX as of the report run date and should have the ability to expose the same in Excel/CSV.

Our Solution

Sales order & Project relation

Scenario 1:- New SO sign off /deal sign off & project is Not in System

1. As soon as the deal gets finalized the sales order gets created in the system by the finance team.
2. Create a project for the deal by the project manager Admin.
3. Associate a newly created project with a sales order created in Step 1 using the standard project feld.

Scenario 2:- New SO sign off (Eg: Change Request) & project is already in the System

1. Attach project while creation of Sales order.

Revenue recognition

1. We need to show the journals as a line in the report with its corresponding amount that is posted to the Income account.

Sales order

1. The project invoices will be linked to a sales order and will not be a stand alone one.
2. All the revenue recognition will be created from sales order.
3. The Committed amount is the Sale order total without tax amount.
4. one Sales order always means one project it is related to.
5. One project can be associated with multiple sales order
6. The user will be using the standard Project field to relate and associate to a project record.
7. The primary currency of the project will be equal to the sales order currency.
8. The journals created as a part of revenue recognition journals and the invoice records that are related to the project will be considered and out of that the amounts posted to the income accounts will be summed up to the revenue amount.

Time track

1. We use the loaded labour rate from emp records. Whatever rate is there at present in the employee record will be fetched. Historical loaded rate will not be considered for the cost calculation. Only the latest rate will be used for all the calculations.
2. We will group by service item or by employee only. This will be an option which can be toggled. Only one option can be selected before generating the report.
3. There will not be any transaction date or status for these line
4. We will take time track irrespective of status of transaction

Expense Report

1. The project need to be selected in the line level of the expense report to be associated to the expense report.
   
   
2. We will consider line level date and exp lines irrespective of status.
3. We will consider the transaction amount and convert it to usd and not the base currency value from GL impact

Vendor Bill

1. The bill lines that have the project selected in the line level of the project record.
   
   
2. The tarndate will be the body date of transaction 
3. Amount will be the transaction currency amount that we consider for USD conversion.

Bill credit

1. The bill credit lines that have the project selected in the line level of the project record.

Users are able to select the different filters for the generation of P&L reports and click the submit button, script will run background and generate the updated report. Users can download it from the download provided on the suitelet page.
Also, an email will be sent to the corresponding user with the updated P&L report.
The Detailed P&L reports, will list the P&Ls like time tracking, invoice, expense reports, and vendor bills, etc, and shows them based on the subsidiary. Users can select the respective subsidiary and then the corresponding P&L lines against that subsidiary will be shown.

Saved Search 

• In one search we will get the labour cost and it will show total labour cost in USD for a project, given we enter the Employee record loaded cost (custom field) with USD labour rates. Total cost= Total Sum of (Hours tracked by each employee * Loaded labour rate in USD). One project line results per this search result. It can be grouped by employee, service item, Date not needed, Sum of total hours, no document numbers, Total Amount in USD (emp record loaded rate*hours).
• In another search, we will get the total revenue amount from Invoice-> especially the revenue recognition journal amount & credit memo amount per project in a transaction currency so we need to convert it into USD and show that up in the report. Each line of this search corresponds to one project line.
• 2 search for revenue invoice and journal.
• Fx rate custom record search.
• System Fx rate standard search.
• From another search, we can get the expense in different base currencies. We need to convert it in USD and based on the subsidiary. Expense from Bill, Expense from expense report
• Revenue recognized search & independant SO related search for project.

FX rate conversion

There will be two sets of FX rates to be considered that can be toggled for the conversion from the transaction currency to USD.
We will create a custom record to enter all the combination currency fx rate conversion to USD which will be manually maintained by the finance team/ admins. And secondly, we will use the NetSuite auto-populated fx rates to do the conversion.
All the figures will be populated in the USD for the summary reports and for the detailed report the amount will show il transaction, base, and in USD.
Better to show both of the converted amount columns based on static and dynamic fx rates.
Dynamic fx rate: The report run date will be considered as the fx date for fetching the conversion rate.

Columns for Detailed Project report

• Project – Name of the project (Client name : Project ID).
• Project Customer – Name of the project customer.
• Project Subsidiary – Subsidiary of the corresponding project.
• Date – Date of the transaction (No Date for the Time Transactions)
• Transaction type – Type of the transaction (Expense. Labour,Revenue).
• Account type – Either revenue or expense based on the transaction type.
• Qty/hours – Points the quantity for the transaction and hours for the time entries.
• Currency – Currency of the transaction.
• Committed Amount – The total amount from the sales orders or invoice if it has been added in the deferred account.
• Amount – Amount in transaction currency
• Amount in USD – Amount that converts the Transaction currency to USD using Netsuite standard exchange rate conversion.
• Amount dynamic in USD – Amount that converts to the Transaction currency to USD using custom record exchange rate conversion
• Entity/ service item – Name of the employee or service item selected.
• Document number – Internal ID of the transaction.
• PO# shows the PO number for sales orders
• Status – Status of the transaction

Time transaction
Toggle option possibility for emp/service item. For employees, we can show the rate as well and not for the service item option. Skip the status part from the report, no status for time tracking but for other transactions. 

Note: The report shows all the time tracked irrespective of Time track status (rejected/approved/open).

• Date cannot be shown. 
• Tran type given ->Labour.
• For time the Currency is in USD. 
• For the time there won’t be any amount in USD
• LOB only for time transactions.
• There is no trans status for this.

Expense transaction

Vendor expense from vendor bill, bill credit, and employee exp from expense report.
Bill body date, exp line-level date, Vendor bill, Exp report as trans type.
The line-level currency or the body based on multi-currency option need to fetch amount corresponding

Filters

• Client
• Project (Multi select option)
• Transaction date (between range)
• Project subsidiary
• Project status
• Project Manager 

Note: Need to select one filter as a mandatory one, need to decide on which one to choose. Suggested filters are client/ project subsidiary

Revenue part

We will use a standard project connection to link the SO and Project so all revenue transactions will have this project link.
The user will enter the filters and click on the Generate Report button then file creation will happen in the background and will send an email to the requested user if the file is less than 10 MB. If the file is over 10 MB then a mail will be sent to the user to login to NetSuite and download the CSV report from that request page. The files will be saved in the file cabinet storage and old files over 30 days will be auto-deleted from the file cabinet storage. 

Key Considerations

• We need to provide some filter for each report and the values will vary with each filter.All the filters follow  AND logic and not the OR logic to display the results.
• We will amend the report downloading functionality and the download file format will be in CSV.
• Invoice is always associated with the project code using the standard project field.
• The P&L report generation is based on the schedule status.
• The maximum attachment size is 10MB and the email will only be sent to the user  if the attachment size is less than 10 MB.
• File type for download is of CSV format only.
• All currency conversion happens from the transaction currency to the Billing currency to the USD 
• We will consider the gross amount for the reports
• All amounts will be in 2 round precisions.
• We will only show the recognized amounts for the revenue transaction and not the deferred amounts.