JIRA: INKAL-1
PROPOSAL SUMMARY
This proposal outlines a comprehensive approach to streamline the demand planning process by combining multiple reports into a limited set of reports or a single, cohesive report. This initiative aims to reduce manual efforts, enhance accuracy, and improve decision-making within the organization’s demand planning operations.
REQUIREMENTS
I have a report that I build manually each week utilizing several different reports in NetSuite to help us determine how much inventory we want to reorder. I was wondering if you would be able to help me simplify my process by combining all of them into one report or possibly dashboard that I can export weekly.
Here are the reports I am currently utilizing and a spreadsheet sharing the data source of each column. Please Note: I build the report in the “Item Details Report with image BB” export and pull in data from a pivot table I have built referencing the “Custom Sales by Customer Summary BB”
NetSuite Reports:
- Item Details Report with image BB (This is the base of my report)
- All Items Sold + Committed (Pulling this to reference “Sold (1/1/23- today’s date)” and I VLOOKUP into the item details report
- Custom Sales by Customer Summary BB (I am limited on the number that can fit in this report so typically pull data for the last 3 months). The reports listed below are all exported so I can VLOOKUP various columns into the Custom Sales by Customer Summary BB report before pivoting the data to see what we’ve sold by week by channel over the last 8weeks
- Open Order Detail (I pull this for the last two months; I add all open order SKU detail to my custom sales by customer summary bb doc to make sure open order details are included in our demand planning)
- Customer Account Detail (Pulling this to reference “Customer Category”)
- Document # + Date Vlookup (Pulling this to reference the “Document Date”, “Reference Number”, and “Location Where Order Was Placed”)
DELIVERABLES
- To integrate the ‘Item Details Report with Image BB’ and the ‘All Items Sold + Committed’ (with year-to-date sold quantity), we will develop a new saved search report. This report will incorporate the year-to-date sold quantity using specific formulas and conditions. This report will contains following columns:
- To include the values from the ‘Open Order Detail,’ ‘Customer Account Detail,’ and ‘Document # + Date Vlookup’ columns into the ‘Custom Sales by Customer Summary BB’ report, a new saved search report will be generated. This report will feature the following columns:
- We will generate an additional saved search report to automatically display the quantity sold in the last 8 weeks, inclusive of invoices, cash sale quantities, and open orders. This report will dynamically present the quantities for each item of the past 8 weeks, this will eliminate the need for manual report creation by the user. See the attached image for reference.
- These three reports will be incorporated into the dashboard to facilitate rapid access.
ASSUMPTION
- If any of the items have no transactions within the selected year-to-date (YTD) date range, these items will be omitted from both the combined ‘Item Details Report with Image BB’ and the ‘All Items Sold + Committed’ report.
- We will exclude all non-inventory items and items for which the collection field is empty from the report.
- We will pull all the upcoming quantities exclusively to the I+A warehouse.( for On Purchase order column)
- We assume that the year-to-date quantity sold will encompass all the following records, including cash sales, invoices, credit memos, and cash refunds.
- We did not excluding zero sales amounts from the report. However, if there are items with zero quantities, we will exclude them from the report.
- We will exclude ‘other’ Category customers from the reports. Additionally, we will also exclude customers who do not have a category assigned.
- Sold Qty/Open Qty column stands for- Quantity sold/invoiced for the invoices and cash sale. For open orders, we should consider only the remaining fulfill /billed quantity if the order status is partially fulfilled/billed.
- We will excluder the JE, CM, Cash Refund from the ‘Custom Sales by Customer Summary BB’ report.