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.)
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.
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.
SQLWorks includes two levels of security permissions: Group and User security.
User security states which parts of SQLWorks a user has access to (visible within menus) and does not have access to.
Group Security sets permissions for groups of users, including every user in that group and overrides an individual’s security settings.
To review your SQLWorks users, click the ‘System Administration’ button on the main navbar. If you wish to review your users, click ‘Users.’ This displays a table with every valid SQLWorks user at your company, and administrators can double click to view or edit an individual’s user settings.
To review your SQLWorks groups, click ‘Groups.’ This displays a table with your saved Groups of users. You can also add a new group, copy or edit any existing group from this table.
To change your security settings, click ‘Security’ (1). This loads your list of groups in the left hand list, with the users contained within any selected group in the right hand list. By clicking the ‘Add’ or ‘Remove’ buttons you can add or remove individual users to the correct group.
By using the ‘Group Windows Security’ Tab along the top, you can also set the permissions for that group (and therefore the users contained within it.) Grant access by clicking the checkboxes next to each area of the system, and give ‘Basic’ (find, add new or edit) or ‘All’ permissions (find, add new, edit, edit multiple or delete.) (2)
There are no limits on the size or number of groups users can be assigned to, so your permissions structure can be as simple or complex as your internal company policies require. Please ask the SQLWorks team if you need help or advice on setting up security permissions settings, or on IT security best practice.
By default, SQLWorks has one ‘Admin’ group which has administration permissions for the Sales/Purchase Ledgers, Stock Ledger, Finance and CRM sections. This can be changed to different groups in larger organisations – for example to designate a different administrator for sales/purchasing.
Once you have your users and groups configured correctly, simply close the System Administration window, and your SQLWorks security permissions will be in place when each user next logs on.
SQLWorks Security Permissions
Bank Ledger – Security permissions for managing Bank accounts and cash accounting
Bank Accounts – Lets users see Bank accounts within the Bank Ledger.
Bank Transactions – Lets users access options for bank transactions within the Bank Ledger.
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.
‘What I own now’
Actual Stock is the quantity of an item you currently own within your warehouse(s.)
‘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.
‘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.
‘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.
‘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.
‘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.
‘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.
‘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.
‘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.
If you are using SQLWorks MRP, the Potential stock is the quantity that will be compared to your re-order level for each stock item (within your chosen time horizon) to inform whether additional stock needs to be purchased or manufactured.
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 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
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
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:
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.
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.
Standard Cost – Your custom valuation, not derived from any financial transactions in the system, and used to give a stock item an arbitrary value.
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.
Sometimes you just need to be able to search all of SQLWorks at once – so SQLWorks’ Global Search function has been built to allow users to easily search anywhere (or everywhere) for that elusive file, record or entry.
Pressing the keyboard shortcut ‘Ctrl G’ (or ‘Cmd G’ on Apple devices) opens the Global Search window (1), from which users can select sections of SQLWorks they want to search through using the checkboxes in the top left (2) (e.g.: ’CRM’, ‘Stock’ etc, or simply ‘All’).
Type the intended search term in the text field, and specify the terms of the search: ‘Begins With’ the search term or ‘Contains’ the search term, using the radio buttons, and click the magnifying glass or press return to begin the global search (3).
Global Search will list any and all results in the List Menu below, including where those entries were located. As always, right clicking a list option and click ‘Get Info’ to see more information about that entry.
Double clicking a list item opens that entry from the relevant part of SQLWorks, wherever it has been found.
For more information, contact our team today: 01271 375999
SQLWorks version 6 has been confirmed as both Microsoft Windows 10 and Apple Mac OS X El Capitan compliant.
First developed in 1988, Lineal’s cross platform flagship business management software for Accounting, CRM, Stock Control (and more) has been updated many times over the last three decades to add a wealth of new features and stay at the cutting edge of business requirements.
In order to be declared compliant for both Microsoft and Apple’s rival operating systems, the software must match the functionality of both, and has undergone three months of live testing at Lineal on both systems.
“Here at Lineal we’re delighted that SQLWorks has been declared to be Windows 10 and OS X El Capitan compliant.” Software Development Manager, Will Adkin explained: “SQLWorks undergoes constant development to match our clients’ needs, and we will be expanding to new platforms in the near future.”
“We’re currently developing the free upgrade to the next version for all customers, which will now be 64-bit compliant for stronger performance, and introduce new features.”
Explore SQLWorks today: call 01271 375999 or send us a message for a free demonstration.
SQLWorks Stock Ledger tools have been designed to give you complete control over your warehouse(s) and the constant movement of goods necessary for your business to profit.
Stock Ledger can be entered from the Navigation Bar on the left of SQLWorks, under ‘Products’ (1), in one of two versions: ‘Simple’ or ‘Complex’. These different levels of functionality fit your needs and complexity of your business, with complex stock including advanced features such as internal movements, stock locations and batch control.
Opening the Stock Ledger displays a list of every stock item known to your business (2) and allows you to search or filter the list to find the stock item you need. Selecting an item from the list loads its information in the main window for viewing or editing, with movements, orders and other stock functions all found in the lower half of the Main Window.
Keeping count your stock can depend on workflow, so separate figures for ‘Actual’ (available for sale), ‘Pending’ (not yet for sale), ‘Allocated’ (reserved for order), ‘Free’ (warehoused) and ‘Available’ Stock are all recorded (3), to ensure maximum accuracy.
At the top half of the Stock Ledger are tabs governing the attributes of the selected stock item (4) – including a summary of activity, purchase and sale pricing, ordering defaults, analysis categories and other notes.
In the lower half of the Stock Ledger are tabs governing activity on the selected stock item (5) – including a summary dashboard, full stock movement history, linked transactions, deliveries, stock status, any manufacturing/bills of material data, works orders, stock history & linked CRM entries.
‘Stock Audit’ allows SQLWorks to take a snapshot of your stock, producing printed figures for stock checking, calculating a variance factor based on counts from your warehouse team, and permits you to correct your SQLWorks’ Stock Ledger based on this data.
Storing your stock in each warehouse, and its stock bins, can be mapped in any way that you choose: SQLWorks understands where stock is being kept, and keeps track of crates, packs and individual units to ensure that exact quantities are never confused. Buy in crates of a thousand, store as individual units and sell in packs of five – safe in the knowledge that SQLWorks understands the difference. From the ‘Info’ Tab on the toolbar a set of ‘Allowed’ permissions even blocks items from being sold in the Sales ledger in error, and a monthly ‘lock down’ feature can be used to ensure historic data remains an accurate record.
Materials or component parts can be logged as such to avoid miss-selling, and product ‘bills of material’ from those parts saved for accounting of finished products. Any product in SQLWorks can become a kit, built from an unlimited number of sub components in a known construction time, costed as you see fit.
Valuation can vary widely across companies: that’s why SQLWorks understands different types of stock prices: including setting a ‘Default Cost’ for basic use, ‘Average Cost’ across units or materials or ‘Batch Cost’ for varying costs between batches, or a ‘Standard Cost’ for your own asset valuation as needed, and recording sale prices in up to 3 sales currencies (set by the user) to allow for international stock movements.
Batch management gives you the ability to record the item price differently between batches, track shifting margins and buy, store or re-sell the same item deploying different quantities and valuations depending on the batch. All pricing data links directly to your SQLWorks Accounting tools, to ensure that each area of your business software operates as one.
Adjustments to your stock are easily made from the ‘Movements’ button on the toolbar: for example stock movements can be entered in bulk for rapid updating of stock figures, or imported/exported from an external file. For those that need it, Stock Ledger includes an optional serial number system – using unique item numbers SQLWorks can be set to prompt, or even block, users against moving stock without evidencing its serial number. Speak to our team about how the Stock Ledger can best be configured for your businesses workflow.
SQLWorks logs not just your current stock, but your stock history – allowing you to trace movements or individual items long after the event, or monitor stock levels and finances over time. This information is then relayed using graphs for ease of use, and ensures that you always have accurate stock data at your fingertips.
For more information, contact our team today: http://www.sqlworks.co.uk/contact/