Skip to content

Pro Forma

Introduction

A pro forma is a preliminary sales document issued to a customer ahead of a confirmed sale — for example, to request advance payment, to support a customer's purchasing process, or to confirm prices and terms before a sales order is raised. In Sage 200 these are recorded as pro forma invoices.

Pro Forma Excelerator is a tool that enables users to create and modify Sage 200 pro formas using Excel. It comes with several useful features, including:

  • The ability to create and modify pro formas in bulk or individually on a single sheet.
  • Existing pro formas can be downloaded and amended.
  • Some pro forma header details can be amended without their details.
  • Pro formas can be raised against prospect accounts as well as standard customer accounts.
  • Browse Sage data and pick from browse to enter on the sheet.
  • Ability to print existing Sage pro formas directly from Excel.

For more information about other Excelerator features, please check out the "Introducing Excelerator" guide.

Info

To use Pro Forma Excelerator, the user must have access to the following features in Sage:

  • Enter New Pro Forma
  • Amend Pro Forma
  • Sales Orders List

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 S200ProformaInvoiceTemplate.xlsx.

The S200ProformaInvoiceTemplate.xlsx includes sheets which allow:

  • Single — Entry of a single pro forma
  • Multiple — Entry of multiple pro formas

Options

Don't Clear Header Ranges?

This option controls whether single-cell ranges (those generally at the head of the spreadsheet) will be cleared when the Clear button is clicked.

This can be useful if you want to keep header information when, for instance, entering a series of single pro formas for the same customer.

Delete existing lines when amending proforma?

This option controls the behaviour of Excelerator when saving an existing pro forma and when there is a line number missing from the pro forma you are saving that is present on the existing pro forma in Sage.

If this option is "Yes", Excelerator will delete that line. If this option is "No", Excelerator will leave this line unchanged.

Ignore completed proforma?

This option affects which pro formas are shown in the Download Pro Forma option.

If it is ticked, completed pro formas will not be shown. Otherwise, all pro formas will be shown.

Ignore proforma lines with zero quantity?

If this option is not ticked, then any pro forma lines where no or zero quantity is entered are considered as having incomplete data by Excelerator, resulting in an error when validating or saving.

If ticked, then these lines will be ignored. This can be useful, for instance, if a sheet has a standard pro forma set up with items already entered, and the user must enter quantities. In these circumstances, zero is a valid quantity, meaning this item is not being included on the pro forma.

Ignore proforma having zero quantity in all lines?

Used with the "Ignore proforma lines with zero quantity?" option. If all detail lines for a pro forma have zero quantity, then this pro forma will not be created, but the data is considered valid, and other pro formas on the sheet will be processed.

Entering and Amending Pro formas

Entering Pro formas

To enter pro forma items, populate the line ranges in the same way as for sales orders. See Entering Orders for the full walkthrough — the same principles apply for standard items, additional charges, free text items and comment lines.

Amending Pro formas

Download the pro forma first using Download Pro Forma, make your changes, and save back to Sage.

To amend an existing line on a downloaded record, identify it using the Line Number range — the original line number on the record in Sage.

You don't need to use Line Number when creating new records.

A second range, Line ID, can also be used to identify a line. Line ID is a system-generated unique value Sage assigns to each line; it only appears after you download an existing record, and is most commonly used by interfaces and other integrations that already have the IDs to hand. For hand-edited sheets, Line Number is usually the simpler choice.

Either range on its own is enough to identify the line being amended — you only need to enter the field(s) you are changing. The other identifying details (stock code, line type, warehouse, quantity) will be picked up from the existing Sage line.

If both ranges are entered, Line ID takes precedence, and the two values must refer to the same line in Sage.

If a line is missing from the worksheet when you save, Excelerator may delete that line depending on the "Delete existing lines when amending proforma?" setting under Options.

Re-sequencing Lines

A separate range, Line Sequence Number, lets you reorder lines on an existing record. Set it to the position you want the line to end up at (1 for the first line, 2 for the second, and so on) on each row you want to move; on save, Excelerator instructs Sage to re-sequence the lines to match.

Line Sequence Number is not used to identify a line — that's what Line Number and Line ID are for. You only need to enter Line Sequence Number on rows whose position you want to change.

Prospect Accounts

Pro formas can be raised against prospect accounts as well as standard customer accounts. To do this, enter Prospect in the Account Type range on the pro forma header. Leave Account Type blank, or enter Customer, to raise the pro forma against a normal customer account. You can also pick Customer or Prospect from the Account Type browse.

Info

Prospect pro formas need the Sage SOP setting "Allow quotations and pro formas to be entered for prospects" to be switched on.

Enter the prospect's invoice address in the Prospect Address ranges (Prospect Postal Name, Prospect Address 1 to 4, Prospect City, Prospect County and Prospect Post Code). These ranges only apply when Account Type is Prospect.

Tax Codes on Pro forma Lines

Each pro forma line can specify its tax code in two ways:

  • Tax Code — the numeric short code (for example, 1).
  • Tax Code Description — the tax code name (for example, Std Rate).

You can populate either one, both, or neither:

  • If you supply neither, Excelerator will apply the default tax code for the customer or item when saving.
  • If you supply only the description, Excelerator will resolve the matching short code automatically.
  • If you supply only the short code, Excelerator will resolve the matching description automatically.
  • If you supply both, they must refer to the same tax code in Sage.

Info

For convenience, you can place the short code into the Tax Code Description range and Excelerator will still resolve it correctly.

Warning

If the Tax Code and Tax Code Description on a line refer to different tax codes, the pro forma will fail validation with the error Detail Tax code {code} doesn't match with tax name "{name}". Correct one of the values so they refer to the same tax code, then save again.

Calculate Values

The Calculate Values option will populate prices and values on your pro formas. Values already entered will not be overwritten.

As prices can depend on the customer and standard item quantities being ordered, these should be entered before clicking the Calculate Values button.

Browses

Like other Excelerators, Pro Forma Excelerator allows you to browse and download Sage data.

You can browse on Sage products, customers, tax codes, delivery addresses and other data. After selecting a customer from the customer browse, Excelerator will populate the name and address fields. After selecting a product from the product browse, Excelerator will populate product-related data but not the price. To populate the price, use the Calculate Values option.

Changing the Delivery Address

When you select a customer using the browse or fast entry, the pro forma's delivery address will be populated with the default address. You can then pick from other delivery addresses for the customer in the same way as for sales orders — see Changing the Delivery Address.

Download Pro Forma

This option functions in a similar way to the pro forma browse. It will display a browse, and the selected pro forma or pro formas will be downloaded onto the worksheet.

An option can be used to choose not to include complete pro formas (see Options).

Unlike the browse, you have the following options:

Clear before download

Any pro formas already entered on the sheet will be cleared.

Append

The pro formas you are downloading will be added to the sheet after any pro formas already entered.

Append from current cell

The pro formas you are downloading will be added to the sheet from the current cell.

Info

If the pro forma feature is disabled in Sage, the Download Pro Forma option will report that downloading is not available. Contact your Sage administrator to enable the feature.

Delete Pro Forma Item(s)

Excelerator offers two ways to delete pro forma lines.

  • The context menu (right mouse button) Delete item(s) option
  • The ribbon "Delete Pro Forma Item(s)" button

Context Menu Delete item(s) option

To delete items, select the item(s) by clicking in the Line Number range (or any detail range) and clicking on the right button on your mouse, then selecting "Delete item" (or "Delete items" when you have selected more than one line).

Info

You should only use the context delete button after downloading or saving to Sage. The pro forma on the worksheet must appear as it does in Sage.

This option will only appear on the context menu when the Line Number range is present, and a line number is entered.

This feature works best when amending a single pro forma.

Info

If amending a single pro forma, you can select multiple lines to delete.

Ribbon "Delete Pro Forma Item(s)" button

This menu option will allow you to delete lines from the pro forma.

You should download a single pro forma. Click on the delete option, and you will be given a list of lines to choose which to delete.

Printing

You can print pro formas from Excelerator by clicking the Print button.

  • The pro formas must already exist in Sage. You cannot print new pro formas on your worksheet until you have successfully saved them to Sage.
  • The print option will print all the pro forma document numbers on the sheet or the ones you select.

See Printing.

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?" option under Options.

See also: Clear Excel Data.