Skip to content

Purchase Invoices

Introduction

Purchase Invoice Excelerator

The Purchase Invoice Excelerator developed by Codis enables the easy entry and modification of Sage 200 Purchase Invoices and Credit Notes to be entered and amended from Excel.

This tool offers various features, such as:

Please check out this Quick Tutorial.

Info

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

To create Purchase Credit Notes using Purchase Invoice Excelerator, the user must have access to the "Enter Purchase 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: S200PLInvoiceTemplate.xlsx.

This workbook has two worksheets:

Single This worksheet allows the entry of a single invoice.

HeaderOnly This worksheet is for amending invoice transactions.

HeaderOnlyMemo This worksheet allows amendment of memos for invoice transactions.

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

Multiple This worksheet allows the entry of multiple invoices.

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

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 supplier and reference number.
  • ReferenceAndInvoiceDate - check if there is already an invoice with the same supplier, 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 supplier and second reference number.
  • ReferenceAndInvoiceDate - check if there is already an invoice with the same supplier, 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 suppliers, Excelerator will populate the first line of the nominal analysis of the invoice with the default revenue code for the selected supplier.

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 supplier'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 supplier's record specified on this option.

Number of Memos

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

Entering Purchase Invoices and Credit Notes

Entering Purchase Invoices

You can enter purchase 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 may want to Validate Sage Data before you save. Use the Save to Sage button to save the data.

It is populated with calculated data after the save.

Default Data

As with all Excelerators, PL 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.

Entering Credit Notes

You can enter a Credit Note using the "Type" range and enter "CreditNote" in this range. Enter positive values for credit notes. An invoice will be generated if the "Type" range is not included or left blank.

Entering a Single Invoice

The standard template supplied with Excelerator, S200PLInvoiceTemplate.xlsx, has a worksheet named "Single" that can be used to enter a single invoice with multiple nominal analysis lines. You can see that this worksheet has single Excel cells for invoice header ranges, account, name, Reference, etc. You can use this worksheet, or you can design your own.

If you browse and then select a customer, the first line of the nominal analysis breakdown will be populated with the default purchase account for the supplier.

An example of using this style of sheet might be that you could receive from a telecoms supplier, in CSV form, a list of phone numbers and values to bill. You could map the phone numbers to nominal accounts using a lookup. You could paste the CSV list into a worksheet with a formula to lookup from Excelerator ranges in place, then, with one click, post the invoice to Sage 200.

Entering Multiple Invoices

The standard template supplied with Excelerator, S200PLInvoiceTemplate.xlsx, has a worksheet named "Multiple" that can be used to enter multiple invoices with single or multiple nominal analysis lines.

With this format, header information is entered into ranges with multiple cells in a column.

You can enter multiple nominal analysis lines for an invoice either by repeating the invoice header information for each distribution line or leaving the invoice data blank. In the latter case, Excelerator will assume that any further distribution lines are for the same invoice. Imported data is more likely to be in the first format, whilst entering data in the second format can be quicker.

Multiple Purchase Invoices

How to enter a value once for all invoices or invoice detail lines

Most invoice detail ranges can be promoted into the header as a single cell and have a single value entered into that cell.

That value will be applied to all detail lines on all invoices. Please see the example below, which uses a single project code for all invoices.

Invoice Detail Lines

Entering Project Accounting Purchase Transactions

Project analysis for invoices can be entered into detail ranges in Excelerator. You can combine the entry of the project analysis with the nominal analysis or just enter the project analysis, in which case the default expense code for the project item will be used.

The spreadsheet below shows this:

Project Account Purchase Transations

Amending Invoices

Sage allows limited header information on purchase 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.

You can amend the following information on an existing purchase invoice transaction in Sage:

  • The Due Date
  • The Reference
  • The Second Reference
  • Memo information

Clear All

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

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

See also: Clear Excel Data.

Validating and Saving in Batches

See Tracking the Valid and Saved Status of Data on a Sheet

Ranges

If this range is included, it will be populated with the result of saves or validation.

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. Entered data must be valid for this to work.

  • 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 VAT

There are two distinct ways of entering the VAT for purchase invoices:

Enter the VAT Against Goods Lines

This method is convenient if the invoice specifies the VAT amount against the individual goods lines.

If entering VAT in this way, you should include the tax amount range but not the goods/VAT indicator range.

You must specify the corresponding VAT code for that goods line to ensure the correct VAT rate is applied.

You can then enter a VAT amount or use the Calculate Values option to calculate the VAT amount based on the VAT code and the entered goods value.

Enter the VAT as Separate Lines

This method can be helpful if the invoice specifies the VAT on separate lines from the goods and services. This can be particularly useful for importing from other data sources, which can sometimes provide data in this format

To enter VAT in this way, you must include the Goods/VAT indicator range and not the VAT Amount range. The VAT amount should be entered into the "Amount" ("Goods Value") range.

Using the Calculate Values utility when entering VAT may add lines to the spreadsheet. You should allow for this when you design your template.

Transaction Enquiry

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

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

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

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

Info

You can select transactions from one or multiple suppliers, 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

Purchase invoice printing will print a Sage report that lists purchase ledger transactions for the suppliers on the sheet.

The user can highlight suppliers 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 Purchase Ledger Transactions Report.report

Use Cases

Entering Project Expense And Revenue

This example allows the entry of expenses and revenue for a single project.

It elevates the project code to the header in a single cell in a PL Invoice and an SL Invoice worksheet. It also has a single, second reference entered in the header, which is used for the employee name.

Credit Card Statements

Electronic credit card statement content is pasted into a pre-defined template in this use case.

A code showing the type of expenditure is mapped to a nominal expense code. using a VLOOKUP Excel formula:

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.