Set up inventory in Sage quickly and gain stock control.
On the face of things, setting up individual product records Sage can look really onerous, especially if you have hundreds of unique products. However, this can be acheived significantly quicker via Sage's csv import routine. All you need to know is the layout of the file and the configuration of the data. This can be found in Sage by selecting File - Import and then choosing the help button from the File Import dialogue box.
Even if you have a large inventory you'll more than likely be able to export this data in a csv file format from Selling Manager Pro, Turbo Lister or even your website inventory. This then simply needs to be configured, re-arranged and checked to see if it is in the correct format.
Sage Product Record Overview
Before any stock control can take place, Sage Product Records need to be created in Sage Accounts, for each unique stock item. Creating stock records in Sage allows the user to record important details about the individual product items and also implement many of the useful features within Sage that can help them run their business, such as:
• Inventory of product records
• Stock levels
• Re-order levels (Sage 50 Plus & Pro only)
• Re-order quantities (Sage 50 Plus & Pro only)
• Product Tax codes
• Product location
• Product weight
• Cost price
• Sales price
• Product Supplier
• Sales category (nominal code)
This level of detail allows the user to generate reports through Sage to understand: what they have left in stock, when to order, how much to order and from which supplier, as well as generating analysis on product profit, VAT liability and performance of sales.
Product Records can be created manually, one at a time, or imported via the Sage csv import routine from a pre-existing list. Whilst there are many fields of information within the Product Record, the following need to be considered when setting up your inventory:
Product Code: Often known as a SKU (Stock Keeping Unit) or Stock Code, the Product Code is a unique identifier which forms the basis of inventory. Each unique product should have its own unique code, which easily identifies the product. Up to 30 characters can be used for the product code in Sage. No spaces are allowed.
Description: Product name of item or description. Up to 60 characters can be used.
Tax Code: Usually T1 (standard VAT) or T9 (non vatable). Allocating a Tax code against a product allows the correct VAT liability to be raised on the invoice for the product sale. The percentage for each tax code is referenced from the Tax Code section of the Configuration Editor (Settings - Configuration - Tax Codes).
Item Type: Each product record can be a Stock Item, Non-Stock Item or Service Item. Stock Items default to enforce stock level integrity. This means sales cannot be processed where there is insufficient stock available. Non-Stock items still allow stock levels to be tracked but enable the user to process sales where there is insufficient stock.
Sales Nominal Code: Allows the income from the sale of the product to be automatically allocated against a sales nominal code. This allows the user to implement a structure which categorises their product sales, i.e.
4000: Computer Hardware
4001: Computer Software
4003: Computer Accessories.
The nominal structure needs to be set up in advance through the company section in Sage.
Supplier A/C: Allows the user to allocate a supplier against a particular product. This becomes particularly useful to generate supplier lists for products that are running low in stock. The Supplier A/C is a look up field, requiring the supplier to already be set up in Sage.
Re-order Level: This is the threshold the user can establish for items in stock when they need to reorder more products. Product Records that fall below this level are displayed in red to alert the user. This facility is only available in Sage 50 Accounts Plus and Professional.
Re-order Qty: The re-order quantity allows the user to establish quantities to be ordered from the supplier when placing an order. Reports of products requiring re-ordering can be generated within Sage, by supplier, with these re-order quantities. This facility is only available in Sage 50 Accounts Plus and Professional.
Creating Product Records via the Sage csv import
Sage enables users to import data, via a csv file import routine, and automatically create product records. Sage uses a fixed file format for the import. This means that it expects specific columns to contain specific data. Even if you don't want to enter the sales price, for example, the column for that would contain that data would still need to exist, only with no data in it.
Sage 50 allows 19 fields of information to be imported that can be used in the creation of the Product Record. Imagine viewing this list in an Excel spreadsheet. The following data would be represented in the following columns:
Column A: Product Code (max 30 characters, no spaces)
Column B: Description (max 60 characters)
Column C: Tax Code (T0 - T99)
Column D: Sales Price (net prices)
Column E: Cost Price (net prices)
Column F: Unit of Sale (each, per box etc)
Column G: Location
Column H: Nominal Code (Lookup - needs to be set up in Sage first)
Column I: Stock Category
Column J: Department
Column K: Supplier Account Ref (Lookup - needs to be set up in Sage first)
Column L: Part Number
Column M: Re-order Level (Sage 50 Plus or Pro)
Column N: Item Type (0=stock, 1=non-stock, 2=service)
Column O: Commodity Code
Column P: Weight.
Column Q: Stock Take Date (dd/mm/yyyy)
Column R: Re-order Quantity (Sage 50 Plus or Pro)
Column S: Stock Take Quantity
Only the items in red are essential to create the Product record, although other details can be very usefulk. Files for import need to be created and saved in a csv format and should NOT have any column headers. All fields should be included in the file, in the sequence shown, even if they hold no data. If the file is to contain Nominal Codes or the Supplier Account reference, these MUST already exist in Sage 50 Accounts.
To import your completed csv file and automatically create records in Sage 50, simply select File - Import, and then select ‘Stock Records’ from the on screen prompt. Sage will cycle through the file twice and report any errors, specifying the column and line of the file.
As with all data entry, take a back up first so if anything does go wrong you can revert back to the original data.
Before you start creating the csv file for import consider:
- The nominal structure. Do you want to see reports on sales simply by sales channel (eBay, direct, website) or by product type (computer hardware, software etc). These need to be created in advance.
- Suppliers. Only 1 supplier can be allocated against a single product record. These need to be created in advance.