Stock Adjustments Excelerator
Introduction
The Stock Adjustments Excelerator is a powerful tool designed to simplify inventory management by enabling users to perform stock adjustments in Sage 200 directly through Excel. This tool streamlines processes such as bulk additions, write-offs, issues, returns, and transfers, ensuring efficient and accurate stock management.
Info
Stock Adjustments Excelerator does not record stock take quantities (see the Sage Stock Take Help). There is a Stock Take Excelerator.
In a stock take, if a discrepancy is found between the expected quantity (the quantity Sage shows) and the "actual quantity" found in the stock take, then the usual process is to either adjust the quantity held in Sage, or to check the stock take quantity.
Sage Stock Take Management will adjust the Sage quantity for any discrepancies when the stock take is completed. However, this adjustment will be a basic adjustment, always of the type "Stocktake".
You can use Stock Adjustments to make more accurate adjustments for stock take discrepancies.
To prevent stock take completion from repeating the adjustment, you can choose to adjust the expected quantity held on the stock take when you make your adjustment using Stock Adjustments Excelerator.
Key Features
-
Bulk Stock Adjustments: Easily perform various stock adjustments in bulk using a single Excel sheet, reducing manual entry and errors.
-
Template-Driven Process: Use standard templates or customize them to match your business requirements.
-
Validation and Error Prevention: Built-in validation ensures the accuracy of data before adjustments are submitted.
-
Supported Adjustments:
-
Stock Addition — Add new stock items to inventory.
-
Stock Write-Off — Remove unusable or damaged stock from inventory.
-
Stock Issue — Issue stock for sales, manufacturing, or other purposes.
-
Stock Return — Return stock items to inventory, e.g., from customers or production.
-
Stock Transfer — Transfer stock between different warehouses or locations.
-
Download Stock Takes: You can download the stock items and the expected and actual quantities from a stock take. You can then make more precise adjustments that the Sage stock take completion will make. Stock Adjustments Excelerator will adjust the stock take expected quantities with the adjustments, so that the stock take can still be completed.
-
Support for stock adjustments to traceable stock items.
Getting Started
Access Requirements
Info
Ensure that you have appropriate permissions in Sage 200 to perform stock adjustments you wish to make:
Permissions required:
| Adjustment Type | Sage Permission |
|---|---|
| Stock Addition | Sage.MMS.Stock.AddStockForm |
| Stock Write-Off | Sage.MMS.Stock.WriteOffStockForm |
| Stock Issue | Sage.MMS.Stock.InternalIssueForm |
| Stock Return | Sage.MMS.Stock.InternalReturnForm |
| Stock Transfer | Sage.MMS.Stock.TransferStockForm |
| Stock Take Adjustments | Sage.MMS.Stock.ManageStockTakeForm |
Templates
The provided template workbook, S200StockAdjustmentTemplate.xlsx, includes sheets tailored to specific types of adjustments:
| Sheet Name | Purpose |
|---|---|
| MultipleBins | Entry of different types of adjustments against different bins. |
| MoreRanges | Includes more of the ranges that can be included. |
| AdditionsOnly | Entry of stock additions only |
| WriteOff | Entry of stock write-offs only |
| InternalIssue&Return | Entry of internal issues and returns |
Performing Stock Adjustments
Open the Template
Use the template S200StockAdjustmentTemplate.xlsx. Select the appropriate sheet for the adjustment type or types. Remember that you can design your own templates using the Template Designer
Enter the data
Required Data for Excelerator Entry Excelerator provides great flexibility in how data is entered and which data fields are required. The following data must always be entered:
- Stock Location. This is the warehouse that the stock is stored in.
- Stock Item Code. The stock item you want to make the adjustment for.
- Bin Location (if bins are being used). This is the bin the stock is stored in.
- Adjustment Type (e.g., Write-Off, Issue). This is the type of adjustment.
- Adjustment Quantity
Validate Entries
Use the Excelerator’s validation feature to identify any data errors, such as missing item codes or invalid quantities.
Save to Sage 200
Once validated, upload the adjustments directly to Sage 200. A confirmation will indicate that the stock adjustments have been updated.
Entering Ad-Hoc Adjustments
In addition to adjusting for Sage stock-takes, you can make adjustments for stock-takes not made in Sage, or make ad-hoc adjustments.
Depending on your Sage settings and the type of adjustment you are making, and whether the stock item is traceable, you may need to include other ranges on the sheet.
Entering Adjustments For a Sage Stock-Take
Info
You should include the StockTakeName range on the sheet if you want to make adjustments for a Sage stock-take.
Stock Adjustments Excelerator integrates seamlessly with Sage Stock-Take Management. The process is as follows:
-
Download the stock-take report from Sage. This report will include the expected stock levels for each location as well as the recorded stock levels.
-
Enter the adjustments you wish to make to align the expected stock levels with the recorded stock levels.
-
Save the adjustments, selecting the option to update the expected stock levels on the stock-take. This will allow you to complete the stock-take without necessitating further adjustments in Sage Stock-Take Management.
Download
The Excelerator provides a versatile search feature in a wizard format that allows you to find products using various criteria. This option is accessible from the main menu and offers the following capabilities:
- Product Group: You can choose whether to select a product group to narrow down your search to specific categories of products.
- Search Categories: You can choose whether to select one or more search categories to refine your search. Categories can include attributes such as product type, supplier, or any custom category defined in your system. Each search category has possible category values. Enter the values for the selected categories to filter the products further. You can add multiple category values using the red plus button. If multiple categories are entered, you have the option to download details that match any of the entered categories.
- Category Values: Enter the values for the selected categories to filter the products further. You can add multiple category values using the red plus button. If multiple categories are entered, you have the option to download details that match any of the entered categories.
The wizard has four pages:
- The first page allows you to select the product group, whether to use search criteria, and whether to download inactive stock.
- The second page allows you to select search categories and values. If you chose a product group, then only the search categories for that product group will be available for selection.
- The third page displays the products selected based on the product group and search criteria.
You must then select a product group, and one or more search categories, and the category values for those categories.
Enter more stock categories using the red plus button. If you enter more than one category, you have the option to download details that have either category.
Details of stock items held at the entered location, for the entered product group and the selected category values will be downloaded. Stock levels for the entered location will be retrieved.
Options and Settings
-
Ignore Zero Quantity Entries: Skip entries with zero quantities to avoid unnecessary adjustments.
-
Audit Duplicate Adjustments: Enable warnings for duplicate entries to maintain data integrity.