Proposal Summary
This proposal outlines the development of a streamlined process of managing inventory data in NetSuite through a user-friendly interface. The focus is on handling CSV file imports for inventory adjustments. The solution includes creating new inventory adjustment record in NetSuite.
Requirement
This project’s goal is to develop a solution within NetSuite that not only facilitates inventory adjustments but also ensures the preservation of item values during these adjustments. We aim to achieve a faster and more streamlined process, emphasizing efficiency and accuracy.
The screenshot below highlights the user interface used in QuickBooks:
A screenshot of a computer
Description automatically generated
The objective of this project is to develop an automated system for adjusting costs without altering the value of the item, specifically in response to changes in the average costing method. The current manual process, which relies on Excel and involves individual entry of costs for each item, is time-consuming and prone to errors. The proposed automated solution aims to streamline this process by implementing key features.
The automated system will include import functionality to bring in adjustment data, allowing the system to identify and select relevant items for adjustment. The cost adjustment process will be automated based on the new quantity, eliminating the need for manual data entry. Integration with the existing inventory management system will ensure real-time reflection of changes.
To enhance efficiency, the system will significantly reduce processing time compared to manual entry, minimize errors associated with data input, and enable bulk adjustments without compromising accuracy. The user interface will be intuitive, providing an option to review and confirm adjustments before implementation. Accessible controls will be implemented to manage adjustments efficiently.
In summary, the automated system aims to revolutionize the cost adjustment process by introducing efficiency, accuracy, and user-friendly features while seamlessly integrating with the existing inventory management system.
Prerequisites:
CSV file which is prepared by the Warehouse team adheres to the expected format, including the presence of the following headers.
Date
Posting Period
Reference #
Adjustment Account Subsidiary
Department
Class
Location
Memo
Transaction Order
Item (Mandatory)
New Qty (Mandatory)
New Value
Any deviation from the expected structure may result in data import errors.
Deliverables
The proposed user interface features header fields and sublist elements to optimize the CSV import process in custom HTML page.
Header Fields:
The custom page contains fields to select for the item count where the quantity will be updated in the selected locations. Based on the field’s updated values, the item count will be updated in the NetSuite. Fields in the custom page is mentioned below.
Primary
Reference # – If enabled, we can enter a unique number to identify and track the adjustments.
This number appears on the register and account detail reports.
Adjustment Account – Select an account for this adjustment.
Usually, this is an expense account for inventory adjustment.
Transaction Order – Select one of the following options for when we want to post this worksheet:
First in Day – This option posts the worksheet at the beginning of the day and does not include additional inventory transactions entered during that day.
Last in Day – This option posts the worksheet at the end of the day and includes all inventory transactions entered during that day.
Date – This field is populated with today’s date. You can enter or select another date.
Posting Period – Select the posting period on which you want this transaction to post.
Memo – (Optional) Enter a short memo for this adjustment.
Memos appear only on account registers and on the account detail report.
Classification
(Optional) To classify transactions, we can select values in the following fields: Department or Class
Location – Select the location where we need to make inventory adjustments.
When we select a location, the quantities of items in that location show in the list at the bottom of the page.
Note
If we use auto-generated numbering, a reference number can be manually entered if we allow the override of auto-generated numbers for an inventory adjustment. Otherwise, the reference number is generated and cannot be changed after the transaction is saved.
The screenshot below will display a mock up of the custom page we will be creating in the NetSuite:
CSV File Selection:
Users can effortlessly choose or browse files from their system using the provided input field for CSV file selection.
Create CSV File and Attach:
An additional option within the user interface allows users to create a new CSV file directly from the custom page. While the form structure may differ from an Excel sheet, users can input details in a manner like an Excel file. After saving the form, the system fetches the details and generates a new CSV file. This file is then attached to the file cabinet and seamlessly chosen for upload through the CSV file input field.
Fields Dropdown:
The dropdown presents users with a list of corresponding values available in inventory adjustment record in NetSuite, streamlining the selection process for this critical parameter.
Sublist Fields:
The sublist within the custom page will feature the following fields. By default, the page initializes without displaying any values in the sublist. However, once the appropriate CSV file is attached, the values will be populated from CSV files.
New Quantity – This field will be populated from CSV file and user can change the value in the user interface.
New Value – This field will be prepopulated with a value by the formula below:
Average cost from item record *New Quantity
Note: We will be referring to the average cost of an item in the body level and not the location-wise average cost.
Buttons:
The page will also include the following buttons:
Import:
This button initiates the processing of the imported file, executing the necessary actions for data integration.
Close:
An option to Close the current session.
File Storage:
Following each import process, the utilized CSV file will be systematically stored within the file cabinet, housed in a dedicated folder. This approach ensures that in subsequent instances, the files required for the process can be easily retrieved from this designated folder, streamlining accessibility, and maintaining an organized file storage system.
Assumptions:
When using the Inventory Worksheet, the item’s Average Cost would not be affected if only the new quantity was changed. The user can adjust the new quantity field to 105 and leave the new value field blank. The system will still populate the new value with system calculated value upon saving the Inventory Worksheet. This change will not, in any way, affect the average cost. As per our business requirement, user may or may not change the new value field value and requires a faster user interface with pre-populated values in cost and new value fields.
The assumption is that the CSV files utilized in the import process will consistently adhere to the expected structure with the necessary fields.
It is assumed that the print functionality will not be required to achieve from the custom page.
As per the proposal, the system is currently configured to exclusively process CSV file types. This assumption implies that other file formats are not supported in the current system setup. (can be considered if required)
In the scenario where the CSV file is generated directly from the custom page rather than being uploaded, it is assumed that the user interface for adding rows will not closely resemble the structure commonly found in Excel. The user interface is anticipated to be designed in a manner that may differ in layout and functionality from typical Excel structures. Notably, this approach implies limitations, such as the inability to provide formulas as available in Excel sheets.)
The inventory adjustment transaction changes the quantity and value of an inventory item without entering a purchase order.
For all items, the costing method will be Average.
When you use the Adjust Inventory Worksheet with LIFO or FIFO costing, the cost of any item you adjust is averaged. NetSuite ignores LIFO or FIFO, and your costing history is lost. To preserve LIFO or FIFO, use Adjust Inventory for any inventory adjustments. If you use the average costing method, you can make any changes you want on this worksheet.
Inventory transactions with the same date are calculated in a particular order to find the On Hand quantity of an item. An inventory worksheet transaction is posted at the start of the transaction date. Any additional inventory transactions on that date affect its figures.
When processing transactions, you must submit one page at a time. If you do not submit each page individually, information is not saved and can be lost when you switch between pages. To process multiple pages of information, always submit each page individually.
Bin management is not used in any warehouses.
The account you selected in Adjustment Account is the same as the asset account for one of the items you are adjusting. Please go back and change the account. Normally, the adjustment account would be an expense account.
Users may or may not divide the import into batches and limit the CSV lines not to exceed 100 lines in a single page. If the user imported a single file, the results will be paginated in the user interface.
Currently, Import is supported for Inventory Worksheets with up to 1000 lines. Poor performance results during import and in the user interface for Inventory Worksheets that are over the limit.
Est. Unit Cost column on Inventory Adjustment forms is limited to eight (8) decimal places.
Risks
Large CSV files or complex import processes may have performance implications. Monitor system performance during and after the import process to identify any degradation and optimize the solution accordingly.
The risk of data integrity issues arises if there are discrepancies between the CSV file and the existing item records.
The expected number of lines within each CSV file for the creation of inventory adjustment records is approximately 10000. If the number of lines exceeds this threshold, it is crucial to notify us so that we can promptly schedule and manage the import process effectively. Failure to address this risk may lead to performance issues, extended processing times, or potential system overload, emphasizing the importance of proactively monitoring and coordinating large-scale imports.
On the CSV file lines, if any values contain comma-separated values, there is a chance of misunderstanding the values during the import process. The presence of commas within these fields may lead to data parsing errors, potentially causing inaccuracies or incomplete representations of the account descriptions or names in the custom records.
Time
Description
Time Required
Analysis, Project Management, Development, Internal Verification, Documentation, and Bundling
50 Hours
*If there are any variations from the mentioned scope, either we will consider it in phase 2 development, or the estimated hour will change.