Introduction to Document Management

SQLWorks includes Document management capabilities to allow each user to save documents into the system for extra convenience.

Files from the user’s device can be added from the user’s device, or any file location the user’s Windows/Mac device has access to (with modify/write permissions).

To save a new file into SQLWorks, look for any window which includes a ‘Documents’ or ‘CRM’ tab. Files may be dragged into the files list (a large white space) from the Windows/Mac operating system (eg: off the desktop) or from the small file tree viewer in the bottom right of the SQLWorks window.

document management

The Document Management save window opens to confirm the details of the file – here files can be categorised using any pre-established Document Analysis types and given a description. Clicking ‘Save & Close’ will save the new document into the list, represented by a new thumbnail showing the file-type if known.

Right click on any saved document to open the original file or perform further functions with it.

document management

The new attachment is stored with a database link to the area of SQLWorks it has been saved against – files can be linked to company records, financial order or transactions (eg: sales or purchase orders), sales leads, projects and many other areas of the platform, to be more easily found by other users.

Typical document management uses include:

  • Adding documents, presentations or images to a project file.
  • Saving purchase orders from a customer against a new SQLWorks sales order.
  • Archiving product designs or specifications against stock items.

For CRM expertise and assistance, contact the SQLWorks team today.

Introduction to EC VAT Reporting

SQLWorks allows users to accurately report on EC VAT, as part of their VAT Ledger.

In order to report on EC VAT, every sales and purchase account to be included must first have a country and VAT reporting setting saved against it.

For each customer or supplier, this can be set under the ‘Bank and VAT’ tab in the Sales Ledger/Purchase Ledger.

To set up the customer/supplier for EC VAT reporting, choose the correct European country from the ‘VAT Country’ (SL) or ‘Country Name’ (PL) fields, and select the correct setting under ‘VAT Setup’. ‘INC’ is for accounts which need VAT to be included, ‘Exempt’ for accounts with reportable VAT of zero value, and ‘NRT’ for accounts with non-reportable VAT.

Whether the selected country is part of the EC VAT reporting can be set in the SQLWorks countries list (found under ‘Countries’ in Address Editor.) Double click a country from the list to edit it from ‘UK’ to ‘EU’ or ‘World’ and click save. The EU category may also be used to designate countries not formerly part of the European Union, but part of the European Economic Area (e.g.: Norway.)

System Administrators may wish to force users to include a company VAT number when dealing with EEA customers/suppliers, for tax reporting purposes – this policy can be enforced via Preferences > Accounts Prefs > ‘Finance, Analysis, Dept & Tax’* Tab, by ticking the ‘EEC VAT requires VAT No in:’ option for either Purchase Ledger (PL), Sales Ledger (SL) or both. This will restrict users from saving any new transactions against sales/purchase accounts without a known and saved company VAT number.

Accounting admins may also choose which nominal codes need to include or exclude VAT by choosing a nominal code in the Nominal Ledger, clicking edit, and ticking the ‘Add VAT’ checkbox in the top right, before saving.

SQLWorks will collate EC VAT figures in the VAT Ledger beside UK VAT, in the ‘EC VAT’ columns (1 & 2), and VAT Ledger users can click the ‘EC VAT’ buttons at the top of each EC Vat column to view the transactions included in that section.

The VAT Ledger will also produce EC VAT totals in the ‘Total’s group’ section at the bottom of the window (3). These are then used in the Form 100 totals, allowing SQLWorks accounts users to complete an accurate VAT return.

 

For additional help or SQLWorks accounting expertise – contact our team today.

*This tab may be labelled ‘VAT & Dates’ in older versions of SQLWorks.

Fact Sheet: Accounting Locks

SQLWorks includes a range of accounting locks which allow data entry for business accounting to be restricted to certain time periods.

SQLWorks operates date based-accounting rather than ‘Period-based accounting’ in the commonly understood sense – this gives companies greater granularity over how accounting is controlled.

Because of this, SQLWorks accounting can be locked down* based on a number of different periods and conditions, depending on your preference:

  • Financial Year Level Locking
  • Monthly Locking
  • VAT Period /  Quarterly Locking
  • Appropriation Locking

 

Financial Year Level Lock

Year Level Locking is available from Audit > Audit by Year in the main SQLWorks Navbar, by right-clicking on the financial year and selecting ‘Lock Year’.

year locking

Financial years that have been locked will display a ‘Yes’ in the ‘Lock’ column of the financial years list, and will prevent SQLWorks users from making any changes to financial transactions within that year. However, nominal journals may still be posted to a locked year.

This lock should only be used by your SQLWorks Accounting Admin, and acts independently of all other accounting conditions.

 

Monthly Lock (Current Financial Year Only)

This lock can be made available in your Accounts Prefs, where individual months, or months within specific ledgers, can be locked or unlocked for current financial year.

month locking

By default, users can only post in the designated active financial year – however transitionary options are available here to allow temporary posting into the final period of the previous financial year if this is still Year Level unlocked.

Because this lock can be turned on/off by a user with sufficient privileges and are always reversible, this forms a ’soft’ lock accounting managers can deploy as a highly flexible ‘Month-end’ control or similar.

 

VAT Locking (Quarterly Lock)

The SQLWorks VAT Ledger allows users to calculate the UK tax return due for each VAT Quarter.

vat locking

Once the VAT return has been complete, the date period covered by the specified VAT Quarter will be automatically locked against further accounting entries.

Reporting VAT accurately is a legal obligation, and additional transaction entries will not be permitted into the locked Quarter once VAT has been calculated for reporting to HMRC. Journaling between nominal codes is still permitted.

 

Appropriation Locking

Once you have set retained profit for a financial year (‘appropriated’ profit) the financial year will be locked, even if the financial year has not yet been Month, VAT or Year Level locked yet. No further transactions may be created in this year.

Because of this, Appropriation is usually only completed once year-end processing for that financial year has already been completed. For more information, please read: “1.1.2.4 – How to Set Retained Profit.

 

*Under certain circumstances some accounting locks can be reversed – please speak to your SQLWorks team about this in more detail.

Fact Sheet: Security Permissions

SQLWorks includes two levels of security permissions: Group and User security.

  1. User security states which parts of SQLWorks a user has access to (visible within menus) and does not have access to.
  2. 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.

Petty Cash Accounts – Lets users access any petty cash accounts.

Sundry Cash Ledger – Lets users access the sundry cash ledger for cash payments.

Sundry Cash List – Lets users access the global table listing all sundry cash payments.

 

CRM System – Security permissions for ‘SQLWorks CRM’ functions

Actions – Gives access to the Actions Table.

CRM Links – Admin Only. Allows users to link CRM items, for example: attaching a document to a lead.

CRM Prefs – Allows users access to edit system-wide CRM preferences under ‘Preferences.’

Diary Calendar – Allows users access to the diary window.

Diary Entries – Lets users access entries to the diary.

Layers – Lets users edit types of appointment, e.g.: Meeting, Visit etc.

Lead Categories – Lets users use custom checkbox categories to categorise leads.

Lead Sources – Allows users to use the editable dropdown noting where a lead originated from.

Leads – Allows users access to the Leads window.

Phone Logs – Lets users access the phone logs table.

Project Types – Admin only. Lets users access project types.

Projects – Gives users access to the Project window.

Tasks – Gives users access to the task tables.

 

ISSAccountsPrefs – Security permissions for setting SQLWorks ‘Accounts’ preferences

Accounts Prefs – Financial Management ONLY. Gives users access to system-wide accounting preferences under ‘Preferences.’

Day Book – Gives users access to the day book.

ForX – Gives users access to amend system currencies and foreign exchange rates (multiple currency) accounting.

Message Sets – Gives users access to edit various option lists – eg: ‘why a quote is cancelled’ field answers.

VAT Ledger – Gives users access to the VAT Ledger for calculating VAT submissions to HMRC.

 

Documents – Security permissions for ‘Document Management’ functions

Crystal Reports – Deprecated feature. Lets users import Crystal Reporting.

Document Analysis – Gives users access to edit the custom document type category checkboxes when uploading a new file.

Documents – Gives users access to files uploaded to SQLWorks.

Emails – Gives users access to emails linked into SQLWorks.

Email Browser – Gives users access to the email browser which synchronises to their email account.

Icons – Gives users access to amend the preview icons which load on screen when using Documents within SQLWorks.

Letters – Currently unavailable. Gives users access to SQLWorks letter writing tool.

 

IssPRList – Security permissions for the main ‘Companies’ list and associated data management tools

Address Editor – Gives users access to the full table of recorded addresses.

Client Lists – Allows users access to SQLWorks fixed and dynamic list building tools.

Client Types – Allows users to set custom checkbox categories for categorising companies.

Companies – Allows users access to the Companies List.

Contact Data – Allows users access to the contacts table (listing each company’s recorded contacts.)

 

IssPurchase Ledger – Security permissions for Purchasing

Purchase Accounts – Lets users see their Purchase Accounts with recorded suppliers.

Purchase Ledger – Grants users access to the Purchase Ledger.

Purchase Orders – Gives users access to Purchase Orders.

Purchase Transactions – Gives users access to Purchase Transactions.

Purchase Transaction List – Allows users access to the global table listing all purchase transactions.

 

IssQuotesOrderSales – Security permissions for Sales

Contracts – Lets users access contracts system.

Customer Order – Allows users access to the Sales Orders panel within the Sales Ledger.

Customer Quote – Allows users access to the Sales Quotes panel within the Sales Ledger.

Directors – Lets users access the recorded list of directors for each company.

Disbursements – Gives users control over sales disbursements (e.g.: expenses, mileage etc.)

Sales Account – Gives users access to each customer’s sales account details.

Sales Account List – Gives users access to their list of customer sales accounts in the Sales Ledger.

Sales Ledger – Allows users to access the Sales Ledger window within Accounts.

Sales Ledger Detail – Gives users access to unlocked detail lines on Sales Ledger quotes, orders and invoices.

Sales Ledger Transaction – Allows users access to the Sales Transactions panel (Invoices, payments and credit notes) within the Sales Ledger.

Sales Order List – Allows users access to the global table listing all customer sales orders.

Sales Quote List – Allows users access to the global table listing all customer sales quotes.

Sales Runs – REDUNDANT

Sales Transaction Archives – Allows users access to the global table listing all archives customer sales transactions.

Sales Transaction List – Allows users access to the global table listing all customer sales transactions.

SL Web List – (Top Secret Work in Progress. Shhhh!)

 

IssSQLWorksMain – Security permissions for managing SQLWorks itself

Audit Logs – Gives users access to the ‘audit log’ of users actions within SQLWorks.

Binary Objects – Controls template images and more. Should be enabled by default.

Database Table Viewer – For Lineal Use Only

DB Sessions – Lets users see logged-in SQLWorks sessions.

Employees – Lets users see the recorded list of company employees.

External Databases – For Lineal Use Only. Allows access to secondary databases if required for external document stores or alternate companies.

Help – Gives users help shortcut to SQLWorks online assistance. Please see www.sqlworks.co.uk/help

Label Prefs – Allows system admin to define system-wide user-defined fields. Should be locked by default.

Refs – Lets users access reference lookup lists and set start points, e.g.: first invoice number.

Server Logs – For Lineal Use Only.

SQLWorks Prefs – Management ONLY. Lets users access system-wide SQLWorks Preferences.

System Monitor – For Lineal Use Only.

User Logs – Lets users access the recorded log of user logins.

User Prefs – Lets users set the user preferences of their own SQLWorks account.

Years – REDUNDANT.

 

Nominal – Security permissions for the Nominal accounting and company finances.

Analysis – Lets users access the accounts ordered by analysis code.

Audit By Code – Lets users audit by nominal code and run nominal reporting.

Audit By Year – Lets users audit by financial year and run nominal reporting.

Budget By Year – Lets users access and compare against recorded financial budgets.

Dashboard – Lets users access the SQLWorks account dashboard.

Departments – Lets users access the accounts ordered by department.

Financial Years – Lets users set system-wide financial year start/end dates.

Nominal Journals – Gives users access to Nominal Journals.

Nominal Ledger – Lets users access the accounts ordered by Nominal Code.

Nominal Audit List – Grants users access to ‘audited’ data calculated from the nominal ledger for financial years.

Nominal Code List – Allows users access to the list of nominal codes defined within the nominal Ledger.

Nominal Journals – Allows users access to Nominal Journals within the Accounts.

Nominal Ledger – Allows users access to the Nominal Ledger (General Ledger) Window within Accounts.

Standing Journal Items – Allows users access to standing journal entry line details for each journal.

Standing Journals – Allows users access to the Standing Journals window.

Standing Journals Group – Allows users access to standing journal entries.

 

Products – Security permissions for managing Stock

Assets – Lets users access the ‘Assets’ tab of equipment linked to a company.

Bin Locations – Allows users access to stock ‘bin’ locations within each warehouse.

Dealers – Allows users access to set dealerships, defining both approved suppliers and customers for a stock item, and cross reference the two (eg: for hazardous materials.)

Order Allocation – Allows users access to SQLWorks stock ‘Order Allocation’ tab in for reserving stock for specific customer orders.

Pricing and Discounting – Allows users permission to set current and future price break rules for stock items.

Stock Allowed – Gives users access to approvals list, restricting warehouse bins to holding only specific stock items.

Stock Audit – Gives users access to Stock Audit to perform stock takes.

Stock Browser – Allows users access to the stock browser table of all stock items.

Stock Groups – Allows users access to stock groups, to which any stock item can be assigned.

Stock Ledger – Allows users access to the Stock Ledger.

Warehouse Map – Allows users access to the Warehouse Map displaying warehouses, bin locations within these, and stock items within those bins.

 

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.

 

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

Fact Sheet: Quoting

Fact Sheet Quoting

SQLWorks Accounts includes a line by line quoting tool which allows you to build custom sales quotes.

Quoting can be accessed from a company’s sales account within your SQLWorks Sales Ledger (1) in the main Navigation bar under the ‘Quotes’ Tab. Here your quotes for the selected company are listed in date order, with details of lines inside the quote previewed in the table below.

Create a new quote by clicking the ‘New Quote’ (2) button, which opens the quoting window. The details of the selected company are listed at the top of the quoting window (3), with the first line of the quote listed in the panel below. You can populate your quote with extra lines by right clicking this panel and clicking ‘Add new Line’, change the order in which lines appear or make amendments.

If using SQLWorks Stock, saved stock items can be quoted for from the dropdown list on the left of each quote line, or type in the free form text box to add custom items such a labour or other extra costs. If your stock items have default costs saved, these are automatically entered as the RRP, unless you specify different amounts and margins.

SQLWorks allows you to add multiple quote ‘options’ by right clicking the digit on the far left of each line, assigning each quoted item to a group. This is useful for putting quote options; ‘Option 1’, ‘Option 2’ etc. for a potential customer to choose between.

The command buttons at the top of each quote allow you to ‘Preview’ or ‘Print’ a quote for sending, ‘Halt’ a quote into your ‘Halted Items’ for further work later, ‘Save & Close’ the quote as it stands or ‘Cancel’ your changes.

Right clicking a quote opens up options for using that quote, including printing, emailing, duplicating or splitting a quote. By duplicating a quote you can work with multiple saved revisions, and splitting a quote allows SQLWorks to cross-check a quote against your Stock Ledger, fulfilling and order based only on what you currently have in stock.

Once your quote has been printed or emailed out to a customer, that quote is locked to everyone but your company’s SQLWorks administrator, exactly as the customer received it.

Your quotes will be made into to a formal PDF format which includes your company details and logo, and is designed by the SQLWorks team to match the formats of your existing company documentation and letterheads.

If your quote has been accepted, right-clicking and selecting ‘Send Quote to Order’ (or ‘Send Quote to Invoice’) will then move your quote on to the next stage of the sales process without the need to re-key any of the line items.

 

Contact Lineal today for more information on SQLWorks quoting: 01271 375999

Fact Sheet: Credit Control

SQLWorks Accounts allows users to control both credit limits for each company they sell to, and credit hold settings for their customers overall.

Each company’s credit limit can be found in their company information by selecting them from the list of companies with sales accounts in the Sales Ledger (1). Clicking ‘Edit’ from the top toolbar unlocks the company information for editing, and users can simply enter a chosen Credit Limit (2), before clicking ‘Save.’

Within Accounts Preferences, users can set a standard ‘Value for Credit Limit’ to apply to each new company by default. You can also change your ‘Credit Hold’ type for what happens when companies exceed their limit, chosen from one of four possible settings:

  • Manual – Where the user must check if companies have exceeded their credit limit, and choose who to place on hold or not. An utility can be run at any time to review current sales accounts.
  • Manual with Override – As above, but if a company is on hold, SQLWorks will prompt the user with notifications so that the user must choose whether to continue with the action or not.
  • Automatic – SQLWorks will place companies on hold or not, based on their credit limit and overdue invoices.
  • Automatic with Override – As above, but the user can be override this and choose to extend further credit if they choose.

If on ‘Automatic’ SQLWorks will also move companies with overdue invoices onto hold, but users can specify a number of ‘days grace’ to give customers under the ‘Sales’ Tab within ‘Accounts Prefs. Company’s automatically placed on hold will also have their orders placed on hold, unless overriden manually.

If using manual credit control, users can right click the list window in the Sales Ledger and launch the ‘Credit Control’ utility – here you can review your companies manually based on four criteria: ‘Days grace’ given for overdue invoices, ‘Put Accounts on Hold’ or ‘Take Accounts off Hold’ to add or remove holds respectively, ‘Include Outstanding Order as Part of the Credit Limit’ to include orders placed but not invoiced on a company’s credit limit.

The manual ‘Credit Control’ utility then generates a list of those companies which are on hold (but are now within agreed credit terms) or not on hold (but have exceeded their credit terms). The user must choose who to place on hold or remove from hold, based on their credit limit and ‘days grace’ for payment deadlines. You can remove companies from the list by selecting lines and clicking the ‘Filter’ Button.

If using automatic credit control, this filtering process is controlled by SQLWorks unless you choose to override a hold. Whether to use Manual or Automatic depends on your own businesses’ level of credit control.

By default, SQLWorks will always automatically hold orders for companies that have exceeded their credit limit, and (if you also use SQLWorks Stock) will not allocate stock to that customer.

For account managers, a useful tool can be to set a chosen company’s credit limit within SQLWorks to ‘0’ (always on hold) or set a highly trusted company’s credit limit to ‘-1’ (never on hold.)

In this way SQLWorks ensures you always have control over how much credit your business extends, and to which clients.

Please contact our team for more information about SQLWorks and managing sales accounts.

Understanding Ledgers

SQLWorks includes four main ledgers for customer transactions: Sales Ledger, Purchase Ledger, Sundry Cash Ledger and Petty Cash Ledger

For accounting, these transaction ledgers are collated into two analysis ledgers, your live Nominal Ledger and your Bank Ledger as described below.

 

Transaction Ledgers (Sales/Purchase/Sundry Cash/Petty Cash)

Your Sales and Purchase Ledgers control account centric transactions for selling and buying to a particular customer/supplier, typically involving an ordering process and a separated invoicing and payment process (i.e. debt and credit).

Sundry Cash Ledger is for payments to and from those whom you have no ‘account’ with, and therefore is best suited to financial transactions that have no delay in payment (for example, a simple cash sale). Because of this, your Sundry Cash Ledger should be used for direct sales & expenditure, or for moving funds into and out of your Petty Cash Ledger.

Each record in any transaction ledger will appear automatically in your nominal audit. SQLWorks follows standard double entry bookkeeping rules, in that each financial transaction has two associated nominal postings. When running a nominal audit SQLWorks uses your nominal profile in preferences and the list below to automatically generate the audit records from the records in the transaction ledgers:

Financial Transaction TypeSide 1 Posts to:Side 2 Posts to:
SL InvoiceInvoice Line Nominal CodeCreditor Account
SL Credit NoteCredit Line Nominal CodeCreditor Account
SL VATCreditors VAT Control CodeCreditor Account
SL ReceiptBank Account Nominal CodeCreditor Account
SL Currency VariationVariance Control CodeCreditor Account
SL SettlementSettlement Control CodeCreditor Account
PL InvoiceInvoice Line Nominal CodeDebtor Account
PL Credit NoteCredit Line Nominal CodeDebtor Account
PL VATDebtors VAT Control CodeDebtor Account
PL PaymentBank Account Nominal CodeDebtor Account
PL Currency VariationVariance Control CodeDebtor Account
PL SettlementSettlement Control CodeDebtor Account
Cash BookCash Record Nominal CodeBank Account Nominal Code
Cash Book Income VATCreditors VAT Control CodeBank Account Nominal Code
Cash Book Expense VATDebtors VAT Control CodeBank Account Nominal Code
Petty CashPetty Record Nominal CodePetty Account Nominal Code
Petty Cash Income VATCreditors VAT Control CodePetty Account Nominal Code
Cash Book Expense VATDebtors VAT Control CodePetty Account Nominal Code

 

Bank Ledger

Your Bank Ledger records the actual record of payments and receipts. A payment can be exist in any of your three main transaction Ledgers (Sales/Purchase/Sundry Cash). Here you can group and organise payments into deposits to exactly match your Bank statement during the bank reconciliation process.

Your Bank Ledger can include multiple bank accounts, against which to record different types of payments. Each account must have a different nominal code that is used when automatically posting the payment records from SL, PL & SCL in the nominal ledger. Note that you cannot enter a payment/receipt record in SL, PL or SCL without selecting the bank account first.
 

Nominal Ledger

Whilst your Bank Ledger records the actual movement of funds, your Nominal Ledger also considers debit and credit transactions from the invoices in your Sales/Purchase Ledgers. The Nominal Ledger gives you a constantly updated window into the profit and loss for each part of your business. By using the ‘audit by year’ you take a snapshot of your business from which you can view P&L, Balance sheet, Trial Balance and drill down into actual live data.

Your Nominal Ledger audit pulls live data from the all of the financial transactions in your Sales and Purchase Ledgers, and from all of the records in Sundry Cash Ledger and Petty Cash Ledger. The value is posted to the nominal code stored on the record and the other side of the nominal posting is decided automatically as explained above, VAT is also posted automatically to the VAT control account.

The nominal ledger also loads nominal journals, non-financial records that serve only to move figures between nominal accounts. Certain processes in SQLWorks create journals automatically, such as end of year appropriation and changes that affect stock valuation.

The Nominal Ledger can have up to three tiers: Nominal codes, Analysis codes (i.e. Sub Nominal codes), and Department codes, each of which can be crosschecked against another to breakdown spending or revenue in different segments of your business for more targeted analysis. The nominal audit creates a record of the value against each individual combination and then pools together the data as per your reporting requirements.

Your Nominal Ledger provides an understanding of your accounts, which includes amounts owed and owing as well as gained or lost, for financial analysis.

 

Understanding Ledgers