Skip to content

Sales Invoices

Introduction

Codis' Sales Invoice Excelerator allows Sage 200 Sales Invoices and Credit Notes to be entered and amended from Excel.

Features include:

  • Entry of single or multiple sales invoices on a single sheet.
  • The download of existing sales invoices from Sage 200.
  • Browse Sage data and pick from browse to enter on the sheet.
  • Support for project accounting.
  • Optional extended validation to check for duplicate references.
  • The usual Excelerator features, as described in Introducing Excelerator.

Please see this Quick Tutorial.

Info

To create Sales Invoices using Sales Invoice Excelerator, the user must have access to the "Enter Sales Invoice" feature in Sage.

To create Sales Credit Notes using Sales Invoice Excelerator, the user must have access to the "Enter Sales Credit Note" feature in Sage.

See also: Sage 200 Configuration

Standard templates

Templates are working examples of Excel worksheets for use with Excelerator. You can use the standard templates as they are, amend them using the Template Designer, or create your own templates from scratch.

Codis provides a single template workbook: S200SLInvoiceTemplate.xlsx.

This workbook has four worksheets:

Single This worksheet allows the entry of a single invoice.

SingleGV This worksheet allows the entry of a single invoice using the Goods/VAT indicator.

Multiple This worksheet allows the entry of multiple invoices.

HeaderOnly This worksheet allows amendment of invoice transactions.

HeaderOnlyMemo This worksheet allows amendment of memos for invoice transactions.

MultipleGV This worksheet allows the entry of multiple invoices using the Goods/VAT indicator.

Clear All

This menu option will clear down the data in the Excelerator ranges.

You can choose just to clear down the detail ranges by changing the "Don't Clear Header Ranges" under Options.

See also: Clear Excel Data.

Options

Don't Clear Header Ranges

If this is ticked, when the Clear All menu option is selected, the header ranges (single cell ranges) will not be cleared.

Check for Reference Duplication (before Release 3.5.207)

If this is ticked, then Excelerator extends the usual validation to check:

  • That a reference is entered.
  • The reference for the same invoice date doesn't already exist on an invoice item in Sage.

Check for Second Reference Duplication (before Release 3.5.207)

If this is ticked, then Excelerator extends the usual validation to check:

  • That a second reference is entered.
  • The second reference for the same invoice date doesn't exist on an invoice item in Sage.

Check for Reference Duplication (from Release 3.5.207)

From Release 3.5.207, the duplicate reference checking has been enhanced. The options are now:

  • NoCheck
  • Warning
  • Disallow

If Warning or Disallow are selected, then Excelerator will check that a reference is entered, and then for duplicate invoice items in Sage. The check for duplicates is controlled by new options detailed below. If no value is entered or a duplicate is found, then the user will either be warned about saving the invoice or it will not be allowed.

Check for Second Reference Duplication (from Release 3.5.207)

From Release 3.5.207, the duplicate reference checking has been enhanced. The options are now:

  • NoCheck
  • Warning
  • Disallow

If Warning or Disallow are selected, then Excelerator will check that a second reference is entered, and then for duplicate invoice items in Sage. The check for duplicate second references is controlled by new options detailed below. If no value is entered or a duplicate is found, then the user will either be warned about saving the invoice, or it will not be allowed.

Check Reference Duplication by:

This option controls how duplicate references are detected. You can choose either:

  • Reference - just check if there is already an invoice with the same reference number.
  • ReferenceAndInvoiceDate - check if there is already an invoice with the same reference number and invoice date.

Check 2nd Reference Duplication by:

This option controls how duplicate references are detected. You can choose either:

  • Reference - just check if there is already an invoice with the same second reference number.
  • ReferenceAndInvoiceDate - check if there is already an invoice with the same second reference number and invoice date.

Tracking Saved and Valid Data Options

See Tracking Saved and Valid Data Options

Populate Nominal Code After Account Browse?

If this option is ticked, then when browsing on customers, Excelerator will populate the first line of the nominal analysis of the invoice with the default revenue code for the selected customer.

External Account Code Field

This option is related to the Using Excelerator to Import Data functionality.

Info

This option allows invoices to be created using an external account ID instead of a Sage account ID.

The option specifies which field on the customer's record the external ID is held. The External Account Reference range can be used to include an external account ID with the imported data. This ID can then be used to determine a Sage suppliers account using the field on the customer's record specified on this option.

Number of Memos

This option specifies how many memo fields you want to maintain in Excelerator.

Calculate Values

This menu option will calculate tax values for invoices and credit notes on your sheet. Only invoices with lines with values will be processed.

It will look at all invoices and credit notes and their nominal analysis, calculate the following values and populate the sheet with the new values.

  • Invoice (gross) value.
  • Tax Analysis Total.
  • Nominal Analysis Total.
  • Tax Value.
  • Exchange Rate.
  • Invoice Goods Value.
  • Discount.
  • Discount Days.
  • Tax Discount
  • Discounted Invoice Total
  • Require Authorisation

  • VAT Adjustment Document Expected?

  • For each of the nominal analysis lines:

  • Tax Value
  • Discount Value
  • Tax Code
  • Tax Short Code
  • Tax Rate
  • Tax Discount Amount

Entering Invoices and Credit Notes

Sales Invoices

You can enter sales invoices in the Standard templates supplied with Excelerator or design your own.

You can enter a single invoice or multiple invoices in a single worksheet.

You can enter a Credit Note by using the "Type" range and entering "CreditNote".

You may want to Validate Sage Data before you save.

Use the Save to Sage button to save the data.

The worksheet will be populated with calculated data after the save.

Default Data

As with all Excelerators, SL Invoice Excelerator will try to use default values where none are entered. For instance, you do not have to include a Due Date range on the sheet. Excelerator will work out a due date from the supplier's payment terms.

Credit Notes

To create a credit note:

  • Include the DocumentType range on the spreadsheet. See Designing Templates.
  • Enter "CreditNote" into that range. You can browse to pick CreditNote or Invoice.

Entering a Single Invoice

You can use the "Single" worksheet on the template or design a similar one with a single customer and invoice number cells in the header.

Entering Multiple Invoices

You can use the "Multiple" worksheet on the template or design a similar one with the customer and invoice number ranges as columns.

Validating and Saving In Batches

This feature functions in the same way as the Validating and Saving In Batches purchase invoice functionality.

Amending Invoices

Sage allows limited header information on SL and PL invoices to be amended, and Excelerator provides support for these amendments. It also allows memos to be amended.
However, invoice details cannot be amended. Since invoices cannot be created without details, two modes are available depending on whether detail ranges are included:

  • Invoice Creation Mode: This is the standard mode used when detail ranges are present on the template. It allows for the creation of new invoices with full details.
  • "Header Only" Mode: This mode is used when detail ranges are not included on the template. In this mode, Excelerator checks for a URL range to identify the invoice being amended. Columns of header information can still be included.

Example Use Cases

  • Use Invoice Creation Mode when creating new invoices with full details, such as line items and amounts.
  • Use "Header Only" Mode when you need to amend header information (e.g., invoice date or memo) without modifying the invoice details.

Transaction Enquiry

The Transaction Enquiry menu button lets you download invoice transactions onto the Excelerator worksheet.

You will be prompted to enter a customer's account reference. You can use the dropdown button to pick a customer.

  • If a customer is already entered on the sheet, the selection will default to that customer.
  • Note: Only active customers can be entered.

When you select a customer, all transactions for that customer will be listed in a browse window. (See Searching and Filtering In Browse).

Info

You can select transactions from one or multiple customers, depending on whether the sheet allows.

The following options are available:

  • Exclude Control Account Posting: Excludes control account and VAT postings when downloading transactions to the sheet.
  • Reverse Sign of Credit Note Values: Reverses the sign of credit notes. Ensure the document type range is included to identify credit notes.

Printing

Sales invoice printing will print a Sage report that lists sales ledger transactions for the customers on the sheet.

The user can highlight customers on the sheet or print transactions for all of them. The report will not include transactions entered on the sheet but not saved to Sage.

See Printing.

Info

The Sage report used is Sales Ledger Transactions Report.report

Use Cases

See Entering Project Expense And Revenue

Entering VAT

Please see Entering VAT. This describes the different ways of entering VAT for purchase invoices, but the same principles apply to sales invoices.

Entering Memos

Invoice memos can be saved and updated. You have to choose how many memo fields you want to maintain by setting the Number of Memos options. The memo ranges must be grouped with the invoice range, as shown in the HeaderOnlyMemo worksheet or in the example below for a single invoice:

image

Warning

Sage does not allow blank memos to be saved. Because of this, you cannot have blank memo entries followed by non-blank memo entries when creating an invoice. If updating an existing transaction, blank memo entries will delete existing memos.