Introduction to Stock Audit and Stock Taking

Stock Audit allows businesses to perform a comprehensive stock take, record variances, verify the results, report on valuation, and automatically adjust stock levels for accuracy.

Stock takes can be initiated, or continued, by clicking on ’Stock Audit’ under ‘Products’ in the Main SQLWorks Navigation Bar (1). To begin a new stock take simply click ‘Create New’ or load an unfinished stocktake via ‘Load Existing’. Each stock take is warehouse specific, and can be given a reference ID.

Users must choose whether the final results will ’Save Quantity’ (adjust the actual quantity to a fixed number established from the stock check) or ’Save Variance’ (adjust the actual quantity according to a variance from the theoretical quantity, incorporating quantity changes which may have taken place during the stock take.)

stock audit type

Clicking ‘Print Sheets’ generates a series of stock take forms, listing the stock items and locations under review, which can be printed and assigned to stock counters for counting. Printed stock take sheets include a notes field for stock counters to record additional information, but do not list the theoretical quantity, so as not to prejudice the accuracy of the stock take.

stock audit sheets

Once stock counters have returned their stock take sheets, stock controllers can enter counted values in the ‘Count’ column next to each item by entering the name of the counter in the ‘Counted By’ dropdown (2) at the top of the window. The ‘Variance’ Column automatically calculates the variance, and a note of the counter’s name is recorded in the ‘Count By’ column (3)

If required, stock controllers can print “check sheets” in order to have questionable variances verified by a second counter, and enter a revised quantity in the same field by using the ‘Checked By’ dropdown at the top of the window (2). The ‘Variance’ Column automatically calculates the variance, and a note of the checker’s name is recorded in the ‘Check By’ column (3). When printing check sheets you may enter a single product code or a csv of codes, each product including its current values for all bin locations are printed on their own page.

To review the records, clicking the ‘Print Variance’ button at the top of the window produces a report on the results of the stock take, grouping the variances on each item across all monitored locations, and calculating a monetary value for the overall positive or negative variance.

By clicking ‘Finish’, the results of either the ’Save Quantity’ or ’Save Variance’ stock take are applied to the SQLWorks Stock Ledger to bring the stock levels back into alignment with the stock take.*

 

For additional information on stock control, contact our team today.

 

 

*If the ‘Free Qty’ tickbox is ticked at the top of the theory column, the theoretical values for the stock audit are shown excluding any allocated quantity. SQLWorks will not accept a variance which adjusts the quantity of an item below the quantity of that item which is already Allocated to Sales Orders, and will instead adjust the Actual quantity as close to the recorded variance as allowed numerically.

stock free

Fact Sheet: Stock Quantities

SQLWorks calculates a number of different stock quantities for measuring how much stock you have.

These allow you to measure how much of each stock item are at different stages of your stock control process, and can be found displayed in the Stock Ledger for each stock item as follows. Scroll down for more information about each.

Stock Quantities


Actual

‘What I own now’

  • Actual Stock is the quantity of an item you currently own within your warehouse(s.)

 

Actual 


Pending

‘What I own which is temporarily unavailable’

  • Pending Stock is the quantity of an item you currently own which is not to hand – for example Stock you own which is in ‘Transit’ internally, or stock currently waiting in ‘Make’ Bins for manufacturing.*

*The exact nature of Pending stock may vary between individual SQLWorks companies, and may represent stock dispatched but not processed, if this setting is applied – please contact your account manager to clarify.

Pending


Allocated

‘What’s been sold to a specific customer’

  • Allocated Stock is the quantity of an item you currently own which has been reserved to fulfil a specific customer’s order.

Allocated


Free

‘What’s really in hand’

  • Free Stock is your Actual Stock minus any stock currently Pending or Allocated. This count typically represents the amount of stock you own that’s on hand, and does not include anything currently in transit, or already allocated to fulfil specific sales orders.

Available


SOP

‘What’s been ordered’

  • SOP stock is the quantity of a stock item which is wanted by your customers (currently outstanding on your sales orders.) This includes any Allocated stock already reserved to fulfil specific sales orders.

SQLWorks can be configured to exclude Forward Orders from this number.

SOP


Available

‘What would I have left’

Available stock is your Actual Stock minus your SOP. This count represents the maximum quantity of a stock item you own which could still be sold if all your sales orders were fulfilled.

Free


WoP

‘What’s needed for parts / to be made from parts’

  • WoP Stock is the quantity of an item currently listed on outstanding works orders – to be made from other stock items, or used to make other stock items.

WOP


POP

‘What’s on order’

  • POP stock is the quantity of an item currently listed on outstanding purchase orders. This is stock you are expecting to be delivered by suppliers.

SQLWorks can be configured to exclude Forward Orders from this number.

POP 


Potential

‘What I could have’

  • Potential Stock is all your potential stock added together. This count represents the maximum amount of stock you would have if you fulfilled all outstanding customer orders, received all outstanding purchase orders and build all outstanding works orders.

Potential


Stock Quantities Overall:

 

Stock Quantities

 

For help and advice on SQLWorks’ Stock Control capabilities, please contact our team today.

 

Team SQLWorks at EurOmnis 2016

EurOmnis

Our SQLWorks team recently exhibited at the 2016 EuroOmnis developers conference in Tuscany, Italy – joining Omnis developers conference from more than a dozen countries around the globe.

EurOmnis is an annual educational event designed to bring Omnis programmers together to share knowledge and to showcase the very latest in software development.

“Lineal have been part of, and latterly helped organise, this international conference of Omnis developers since 1999” explained Managing Director Mike Matthews. “As a member of the Omnis developers group (ODEV), our SQLWorks Business Management Software undergoes an ongoing process of learning and development – and we’re always looking at what features future versions of SQLWorks will need to stay at the forefront.”

“The best businesses are always looking to the latest technology for an extra edge, so both our team and SQLWorks software need to be just as agile.”

EurOmnis

Attendees enjoyed classes, discussions and more surrounding the latest advances in Omnis software development, including javascript web tools, responsive graphs and visualisation aids, and Omnis development for portable devices such as smartphones and tablets.

Representatives from Omnis were also present, to host seminars informing and updating attendees on the platform’s latest news, as well as explore likely developments in the near future and gather feedback.

EurOmnis

The conference also gives developers a chance to showcase what they have been working on to other developers, drawing on software projects undertaken across countless industries and sectors.

To learn more about SQLWorks: contact us today.

Importing your data to SQLWorks

importing –

If you’re new to SQLWorks, importing your existing data to SQLWorks can seem daunting. Fear not! We’ve prepared this handy guide to make this process easier.

Decisions about your data are yours – but at any stage, you can ask the SQLWorks Team for help.

 

About Your Data

Data imported into SQLWorks is categorised in two types: Static and Transactional.

Static data is fixed lists of ‘things’ – including companies, contacts, address, your stock list, warehouses and more. Transactional data includes list of transactions, stock movements and financial ledger entries like orders, invoices, credit notes and more. Static data must be imported first, followed by transactional data.

importing

 

Finding Your Data

Both your static and transactional data comes from whichever system(s) you use currently – this could mean importing from a number of sources, including:

  • An old software program (e.g.: Sage)
  • A patchwork of spreadsheets (e.g.: Microsoft Excel)
  • A legacy database program or file (odbc compatible)
  • Nowhere (because you’re a new or paper-based company)
  • Some combination of the above

It’s up to you what data you place in SQLWorks, however whilst some data is almost always needed SQLWorks (even if entered new), other data is optional. As a rule, names, codes, accounting and VAT entries will need to be imported, but the optional parts of how your business model works (e.g.: records of quotes, or past stock movements) are optional.

 

How To Import:

All data for importing into SQLWorks needs to be given to the SQLWorks team in one of two formats:

  • An agreed file format exported from another software (e.g.: Sage export file)
  • A comma or tab delimited spreadsheet, .CSV or .TXT file. (e.g.: If using Excel, it is helpful to save the files as a .CSV in the ‘save as’ menu)

If you provide data to the SQLWorks team in spreadsheets (or .CSV/.TXT files) these will need column headings grouping certain types of the data together. For example, in a stock list, all your stock codes need to be in the same column, under an identifiable heading such as ‘Stock Code.’ The SQLWorks team can help you with this stage if you get stuck.

Depending on what SQLWorks modules you will be using, you will need to import files for the following data (see table below). Compulsory data within these are marked – for example: every Company imported must have a name.

 

 

SQLWorks Core

CRM

ACCOUNTS

STOCK

StaticCompanies

  • Name
  • Company Code

 

Contacts

  • Name

 

Addresses

  • Line 1
 Sales Accounts

  • Name
  • Company Code

Purchase Accounts

  • Name
  • Company Code

Nominal Codes

  • Name
  • Nominal Code
 
Transactional   

Outstanding Sales Orders

  • Company code

 

Outstanding Purchase Orders

  • Company code

 

Outstanding Sales Invoices

  • Company code
  • Date
  • Amount
  • VAT

 

Outstanding Purchase Invoices

  • Company
  • Date
  • Amount
  • VAT

 

Bank Rec

 

1 Bank Account

  • Name, Acc & Sort Codes

 

1 Petty Cash Account

  • Link to Bank Account
 
Optional Static 

 

 

 

Sales Leads

 

Projects

  • Project Code

 

 

Nominal Departments

  • Name
  • Department Code

 

Nominal Analysis Codes

  • Name
  • Analysis Code

 

Nominal Subheadings

  • Name

 

Budgets

  • Amount
Warehouses

  • Name
  • Number

 

Stock List

  • Stock Item Name
  • Stock Code
  • Sale Price
  • Purchase Cost
  • Current Stock Quantity

 

Warehouse Bins

  • Number
Optional TransactionalTasks

 

Phone Logs

 

Actions

 

Emails

 

Historic Sales Quotes

  • Company
  • Date

 

Historic Purchase Quotes

  • Company
  • Date

 

Historic Sales Orders

  • Company
  • Date

 

Historic Purchase Orders

  • Company
  • Date

 

Historic Sales Invoices / Receipts / Credit Notes

  • Company
  • Date
  • Amount
  • VAT

 

Historic Purchase Invoices / Payments / Credit Notes

  • Company
  • Date
  • Amount
  • VAT

 

Purchase Invoices (Historic)

Stock Movements

  • Stock Code
  • Date

 

 

 

Fact Sheet: Consignments & Consignment Stock

Consignments –

If you sell consignment stock through the premises of another company, SQLWorks can help you keep track of your consignments.

Stock locations can be managed in a number of ways, but the easiest way to hold your stock at another location is to create a new warehouse to represent this, named after the customer who holds this stock as a consignment.

To add a consignments warehouse, open ‘Products’ from the main nabvbar (1), open your Warehouse Map (2) and click the ‘New’ button on the top left to add a new warehouse to your list of warehouses. Name this warehouse after the consignment location, or the name of the consignment customer.

When creating the new warehouse, remember to check the correct radio button on the right hand side before saving, tagging the new consignment warehouse as ‘consignment wh’ or ‘retail store.’

You can treat this warehouse like any other – moving stock to or from the premises of your seller, raising customer orders and invoices against that company, and performing stock valuations.

If your consignment is large, you can also divide it into multiple ‘Bin’ locations, as you might for one of your own warehouses, and assign stock to the correct bins accordingly.

consignments

You can choose to change a customers’ default order type to ‘IWT’ (Inter-warehouse transfer) or CONS (Consignment) under the ‘Print and Orders’ Tab in a customers’ Sales Ledger account.

This function allows you to specify your (actually their) new consignment stock warehouse under “Warehouse to” for stock, to be moved into by default. In the case of IWT and Consignment stock, this order will then be removed to prevent invoicing a consignment stock re-seller or similar for the consignment before sale.

At all times SQLWorks treats consignment stock exactly as what it is: your stock, temporarily stored with someone else.

 

For help with stock control and warehousing: contact the SQLWorks team today.

Manufacture and Kitting

manufacture

SQLWorks includes a manufacture and kitting tool able to budget for and build manufactured products using a selection of saved kits.

Manufacturing is accessible to users of the SQLWorks Advanced Stock, and can be found within the Stock Ledger screen under the ‘Products’ module in the main Navbar (1).

Clicking the ‘Kit Details’ Tab opens the kitting information for the selected stock item (2), and users should click the ‘Setup’ button if using these tools for a given stock item for the first time. By default, SQLWorks saves up to 3 alternate builds for each manufactured item (although more are available) with saved descriptions for each build (3).

Each stock item in your SQLWorks stock ledger can be both a ‘parent’ (made from its stock item ‘children’ – its components) or a ‘child’ of another stock item ‘parent’. Right-clicking opens options to ‘add child’ (component part) including values for both the components and associated labour costs.

Saved builds can include many components, sub components, and more levels as needed.

On the right hand side of the panel (4) are fields displaying the ‘Base Component Cost’ (the total value of the component parts as worked out by your saved SQLWorks stock valuation model) the ‘Marked Up Component Cost’ (the total markup value once percentage markups such as labour or assembly costs have been applied to each component for this build) and the ‘Current Kit Cost’ with your assigned sale cost for the finished product.

The kit price will be re-calculated automatically as component parts change, or if you have disabled this feature, by pressing the ‘Re-calculate’ button. Users can update the cost details for a build, allowing for any recent changes to stock ledger components, their value or assembly markup costs. You can also use saved shortcuts in the quick select menu of the Stock Ledger to view ‘Parent Items’ and ‘Child Items’ for easy searching.

SQLWorks manufacturing gives you a toolkit to organize the manufacture of kits from countless components, and to keep track of costs at every stage of the production line.

 

For specialist manufacture and kitting tools – speak to us about SQLWorks Stock Control today.

Fact Sheet: Order Allocation

For the most professional warehousing operations, SQLWorks includes a powerful automated order allocation system.

‘Order Allocation’ can be accessed by users who have the SQLWorks Advanced Stock module under ‘Products’ in the main Navbar .

The top of this window gives you a series of filters for every stock order recorded in SQLWorks , with a series of configurable order allocation stages that your warehouse stock must move through to be dispatched in the panel below.

Typically stock will be progressing through one of six stages:

  • ‘Unallocated’ – Stock that has not yet been processed.
  • ‘Allocated’ – Stock from a specific warehouse reserved for a specific order.
  • ‘Released’ – Stock in a specific bin location or locations, approved for picking.
  • ‘In Pick’ –  Stock that has been picked and due to be dispatched to the customer.
  • In Transit’ – Stock that is part of internal stock movements between warehouses

By default all lines that meet your search criteria are displayed on the relevant tabs on the bottom of the window. These display is automatically ‘locked’ to editing, however using the radio buttons users can make the list ‘Selectable’ to turn on or off individual (or groups of) order lines, or ‘Editable’ to change individual allocation qty within a line. Right clicking a selectable or editable line opens helpful options for highlighting mass, order group or inverse line selections.

In the unallocated tab clicking the ‘Auto Set Values’ button on the right will allocate anything SQLWorks can, when you save it will move order lines to the ‘Allocated’ Tab. Since not every allocated stock item within an order is always available for dispatch, SQLWorks releases the order allocation based on the dispatch rules set in the order:

  • ‘Allow Back Orders’ – When picked, any outstanding stock is cancelled unless SQLWorks is told to hold as outstanding items for back ordering.
  • ‘Allow Part Order’ – SQLWorks will allocate order lines as they become available, unless told to wait until the full order can be fulfilled.
  • ‘Allow Split Line’ – Send partial quantities from lines whenever they are available.

You can specify saved defaults for your company’s SQLWorks order allocation, which can be overridden with a rule for specific customer’s sales account, and are then applied to each specific order for the account.

Once released, SQLWorks can auto-generate intelligent picking notes – itemising stock to be picked using optimal warehouse walking route based on the known locations of your warehouse bins. When a pick is complete, warehouse operatives can re-enter stock ‘Fail Quantity’ figures into your order allocation history, along with reporting reasons for why the stock in question could not be picked. The remaining quantity is then automatically moved to invoice, allowing you to dispatch large numbers of orders with ease and efficiency.

An inventory Audit Log also allows you to look back through a complete history of every order line, or you can refer to the ‘Order Processing’ Tab within the Stock Ledger for a graphical summary and past failed order data.

For a more professional stock control solution – contact us about SQLWorks today: 01271 375999

Did you know? Stock by Account

Stock by account

It’s often useful to be able to see what a company has been quoted for, ordered, or has been invoiced for, over a longer period of time.

SQLWorks provides a useful summary of this information under each company’s ‘Stock by Account’ table.

Opening a company’s Sales Ledger Account in SQLWorks and clicking the ‘Stock’ Tab in the main window will display a table that breaks down a company’s stock data by month. Users can choose the financial year to observe, filter by Product, Stock Group or more, and choose to count the number of quotes, orders or invoices.

This is a useful feature for repeat customers, providing a quick and easy summary of activity on a customer’s sales account over the course of 12 months. For a more detailed list of stock or custom items quoted, ordered or invoiced, click the ‘Detail’ tab and specify the date range with which to search that company’s sales account.

Either table can also be exported to Microsoft Excel if needed, so that SQLWorks can always report your sales account activity in the way that is most convenient for you.

 

Contact our SQLWorks team for more information: 01271 375999 

Did you know? Stock Costs

Stock comes in many different forms, so SQLWorks stock ledger can be set to value stock, per item, in four different ways – known as stock costs:

  1. Default Purchase Cost – specify a purchase cost against any stock item in any currency, and when you buy in that currency, SQLWorks will match the costs using the appropriate exchange rate.
  1. Average Cost – this is an average taken across all purchase invoices over the total quantity of stock. Accurate to up to 4 decimal places, this can be recalculated with a right click or set to automatically update via Preferences > Accounts Prefs > Stock. If no stock is available average cost will estimate an average from recent sold stock using your invoices.
  1. Standard Cost – Your custom valuation, not derived from any financial transactions in the system, and used to give a stock item an arbitrary value.
  1. Batch Cost – Used for advanced warehousing, batch cost records the cost of each item from a specific purchased batch, and can vary between batches, allowing for more accurate manufacturing, re-sale and accounting.

On costs/landed costs, accounting for extra stock costs obtained with freight charges, duties and import taxes, can also be recorded specifically or as averages, and users can specify whether to include or exclude on-costs from their stock valuations.

When generating reports in SQLWorks, users can specify the default valuation for your stock from among the stock cost methods, choosing the one most appropriate for your business. By setting a default cost type, this also affects your Sales Ledger, directly affecting profit and associate reports.

 

Learn more about SQLWorks stock today: http://www.sqlworks.co.uk/stock/

Lineal launches new SQLWorks website

Lineal Software Solutions have launched of our new sqlworks website for our SQLWorks Business Management Software (www.sqlworks.co.uk).

Managing Director of Lineal, Mike Matthews, explained: “This is a big year for SQLWorks, as we’re due to release Version 8 in conjunction with the release of Omnis 8, and we wanted to overhaul our SQLWorks website too.”

“We always aim for our software to work how you work: we’ll now be offering 3 different delivery models to suit different businesses’ needs, with SQLWorks available in on-premise, hosted and cloud versions.”

SQLWorks, Lineal’s leading software for Accounting, CRM & Stock Control was first developed for manufacturing in 1983, and has evolved substantially over 33 years.

Keeping up with the times, the new website has been designed to be fully responsive, for use on mobile and tablet devices. More and more people will use SQLWorks on the move in the future, from a variety of devices, so the SQLWorks website should mirror this.

Existing SQLWorks users will also receive additional support, accessing learning materials via the SQLWorks News page. Extra features, including a live SQLWorks demo and our SQLWorks help guide, will be available soon – watch this space!

 

Discover SQLWorks today: visit www.sqlworks.co.uk or call 01271 375999