In our previous article, we discussed how to enter an Inventory Adjustment in NetSuite and when it should be used. While Inventory Adjustment is ideal for smaller corrections and item-level changes, there are situations where businesses need to update inventory on a much larger scale.
This is where the Inventory Worksheet comes in.
Inventory Worksheets allow you to update the quantity or value of multiple inventory items at once. They are especially useful after a physical inventory count, during year-end review, or whenever you need to reset inventory quantities to match actual stock on hand.
This article walks you through how to create an Inventory Worksheet and highlights some important considerations before using it.
Now, let’s get started.
WHAT IS AN INVENTORY WORKSHEET?
An Inventory Worksheet is used to enter changes to the quantity or value of inventory items in bulk.
Unlike Inventory Adjustment, which changes inventory by adding to or subtracting from the current quantity, Inventory Worksheet sets the item to a new total quantity as of the worksheet date.
For example:
If your item has 25 units on hand and you enter 10 in the worksheet, NetSuite resets the quantity to 10, not 35.
This makes Inventory Worksheets very useful after a stock count, because they allow you to enter the final counted quantity directly.
However, Inventory Worksheets cannot be used for:
a. Lot-numbered items
b. Serialized items
c. Inactive items
You should also avoid creating a worksheet with more than 1,000 item lines.
HOW TO CREATE AN INVENTORY WORKSHEET
1. Navigation:
- NetSuite: Transactions > Inventory > Adjust Inventory Worksheet
- NetSuite Next: Open the Explore menu (hamburger icon in the bottom left corner beside the home button). In the menu, go to Transactions > Inventory > Inventory Worksheets > Create Inventory Worksheet.
For OneWorld accounts, in the Subsidiary field, select the subsidiary you want to associate with this worksheet.
NetSuite:
NetSuite NEXT:
2. Fill out the Primary section.
Complete the following fields:
a. Reference #: If enabled, enter a unique number to identify and track the worksheet.
b. Adjustment Account: Select the account where the worksheet adjustment will post. This is usually an expense account for inventory adjustment.
c. Transaction Order: Choose when the worksheet should post:
- First in Day: Posts at the beginning of the day and does not include additional inventory transactions entered during that day.
- Last in Day: Posts at the end of the day and includes all inventory transactions entered during that day.
d. Date: The field defaults to today’s date, but you can change it as needed.
e. Posting Period: Select the posting period where the transaction should be recorded.
f. Memo: Optionally enter a short note for the worksheet.
3. Fill out the Classification section.
If your company uses classifications, you can enter values for:
a. Department
b. Class
You should also select the Location where the inventory needs to be updated.
Once you choose a location, the list of items and quantities for that location appears at the bottom of the page.
4. Review the Adjustments section.
In the Inactive field, choose how to filter items:
a. No to exclude inactive items
b. Yes to show only inactive items
c. All to show both active and inactive items
Remember, serialized and lot-numbered items do not appear in this list.
5. Enter the new quantity.
In the New Qty column, enter the new total quantity for the item.
This is not a quantity to add or subtract. It is the final quantity that NetSuite should show as of the worksheet date.
6. Update the new value if needed.
In the New Value column, you can change the auto-calculated total value for the item.
This is helpful when the worksheet needs to reflect both a quantity update and a value correction.
7. Enter bin details if applicable.
For items that use bins, do one of the following:
a. Click the Bins icon in the Bin Numbers field and enter the quantity for each bin
b. Enter bins directly using this format:
Bin101(50), Bin102(43)
If you use Advanced Bin/Numbered Inventory Management, click the Inventory Detail icon to enter bin and quantity details in the popup window.
You can only enter bins associated with the item.
8. Repeat for other items.
Continue entering the new quantity or value for each item you want to update.
Remember that you can enter up to 1,000 line items at a time.
9. Click Save.
After you save the worksheet, NetSuite updates your inventory records. You can then open the related item records to view the updated quantities and values.
To view saved worksheets later, go to:
Transactions > Inventory > Adjust Inventory Worksheet > List
HOW INVENTORY WORKSHEET CALCULATES QUANTITIES
One important concept for new users is that Inventory Worksheet is exclusive of previous stock totals.
This means the quantity entered on the worksheet becomes the official quantity as of the worksheet date.
For example:
You take a physical count on January 1 and determine that an item has 10 units. If you enter an Inventory Worksheet dated January 1 with a quantity of 10, that quantity remains 10 on that date, even if older transactions are entered later.
This helps preserve the counted quantity from a stock take.
However, additional transactions entered on the same date can affect what appears on reports depending on the worksheet’s Transaction Order setting.
The key thing to remember is that Inventory Worksheet sets the inventory quantity to a new total as of the worksheet date. Because of this, it should be used carefully and only when that behavior matches your business need.
Related article SAID99378: Creating an Inventory Worksheet
With this article and our previous guide on Inventory Adjustment, you now have a better understanding of the two main ways to correct inventory in NetSuite.
Stay tuned for more in our Inventory Transactions series! Don’t forget to follow New to NetSuite > Supply Chain Management to be updated when new articles are posted.
