Jira code: REL-5
Issue: We have one report we’re looking to enhance within Netsuite that even has some trouble getting done automatically in Excel with data from NS. The summary on the Margin Report:
Objective:
To obtain a Margin report by Category, by Item (SKU)
To obtain the Margin report for a period say daily, weekly, monthly, Quarter, Year
Solution:I have created a new report by customizing the standard inventory report and by adding necessary fields. The report is grouped based on Item category. The description of report fields is as below
Type: Shows the item type.
Item: Display the item/ component name.
Qty.Sold: Displays the Total sales quantity of the item.
Revenue: Displays the Total sales revenue of the item.
% of Revenue: The percentage contribution revenue of the item by comparing to reports total revenue.
Unit Price: By dividing the Revenue by Qty.Sold
Qty. Purchased: Displays the total quantity of the item that is added to the stock by purchase transactions mainly the transactions that add up the stock of that item.
Cost: Displays the total cost of the item ordered while the item procurement.
Unit Cost: By dividing the Cost by Qty.Purchased
Margin: Revenue-Cost
Margin %: ((Revenue-Cost)/Revenue))/100
This report will show all the costs and revenue associated with the items. For Kit items this report will only show the income recorded as by standard the cost will get only attached to the member items and when we sell a kit item the cost gets attached to the KIT item rather than to its members. I have set the filters as date, The item type selected for this report is an inventory item, noninventory and kit items. This filter is available on the result page. The user can also change the item type if needed to include more item types. Then column filters can be used to change the view to week, month, and quarter based on the requirement.
By Customizing the standard in the P&L report we cannot get the cost and revenue of the item in one line, we cannot get the quantity of the calculated fields mentioned in the reporting requirement. In the P & L report also we will not get the cost associated with the KIT item. The best of what can be done in the Profit and Loss report is as shown below. That is why we have created a new report using an Item report.
In the report, we cannot remove the item lines which do not have any cost or revenue in that selected date range.