Purchase Order Variance Analysis

Purchase Order Variance Analysis

(DA0403)

Further Information

Click below to view your selection of in-depth information for this project:

Benefits Statement ... 1

Legal, Fiscal and any Standards Considerations ... 1

Overview ... 2

Examples ... 3

Purchase Receipts ... 3

Purchase Invoices ... 4

Technical Data ... 5

Implementation - Existing Sites And/Or Existing Data ... 5

Licensing ... 5

System Keys ... 5

Data Dictionary Changes ... 5

Options ... 5

Forms Changes ... 5

Suspended Fields ... 6

Conversions ... 6

Definition of Terms ... 6

Implementation - New Line 500/Sage 1000 Installations With New Data ... 7

Licensing ... 7 System Keys ... 7 Options ... 7 Suspended Fields ... 7 Definition of Terms ... 7 System Keys ... 8

Data Dictionary Changes ... 9

Inventory Control ... 9

Purchase Order Processing ... 10

Suspended Fields ... 11

Definition of Terms ... 11

Link to a

(or Captitate) walkthrough.

Benefits Statement

Variance Analysis utilises this data to provide journal creation and posting of variances generated from the data in 3 Way Matching at the Purchase Orders, Purchase Receipts and Purchase Invoice stages. This data updates a purchase variance history file that holds information, for each order line, about the planned exchange rate, actual exchange rate, standard cost, purchase price, invoice price, receipt quantity and invoice quantity. Journals generated from this data are used to post the variations to General Ledger Accounts nominated in an extension to a posting reference table or defaulted by settings in the System Manager.

Legal, Fiscal and any Standards Considerations

Overview

The Purchase Order Variance Analysis enhancements introduce new functionality for reporting on purchase price and exchange rate variances.

The Purchase Order Processing options Receipts and Purchase Invoices have been modified to update a new Purchase Variance History file. Information written to this new file can, depending on the option being run, include details about the planned exchange rate, the actual exchange rate, the standard cost, the purchase price, the invoice price, the receipt quantity and the invoice quantity for each purchase order line.

The Stock G/L Cross Reference file has been extended to hold additional account codes for the following purchase variances:

• purchase price variances (PPV)

• purchase exchange rate variances (PEV)

• purchase quantity variances (also known as purchase weight variances (PQV))

Upon confirmation of the receipt of goods, the PPV between the purchase order price and the standard cost, as well as an initial PEV between the planned exchange rate and the current exchange rate are calculated and written to the Purchase Variance History file. The Stock G/L Distribution Analysis posting file can also be updated using the PPV accrual and PEV accrual account codes defined in the Stock G/L Cross Reference file.

The total PPV between the actual invoice price and the standard cost, and the PQV, the variance between the receipt quantity and the invoice quantity at standard cost, are calculated when the Purchase Invoice is confirmed. The calculation of these variances does however, rely on the Purchase Invoice being matched against the receipt. The total PEV between the planned and the actual exchange rates is also calculated when the Purchase Invoice is confirmed.

Accruals generated via the Purchase Order Processing option G/L Reserve Accounts and posted to the General Ledger will include amounts for the PPV and the PEV on receipt from Reserve Accounts Type 3 (goods received but not invoiced) and an amount for the PQV from Reserve Accounts Type 4 (invoices not yet posted).

Additional distribution lines for the total PPV, PQV and PEV amounts are created when an Accounts Payable invoice is created from a Purchase Order Processing Invoice for posting to the General Ledger. The 'Goods received but not invoiced' accrual, 'PPV' accrual and 'PEV' accrual accounts are reversed when the Purchase Invoice is posted to Accounts Payable.

The option Stock G/L Distribution Analysis/ Post has also been modified to allow an intermediate level of detail (type I), generating one posting amount per account code and per transaction type. A Stock G/L Distribution History file has also been introduced. This file is updated with account codes and amounts when they are posted to the General Ledger to allow the history of General Ledger postings to be retained as well as allowing summary postings to the General Ledger whilst still holding the detail in stock.

Examples

Purchase Receipts

Purchase Order Processing Receipts have been modified to update a new Purchase Variance History file. (See slide 20 of the PowerPoint walkthrough for details.)

The current functionality for the receipt of a product credits the 'goods in' value at the purchase price to the Stock G/L Distribution file (Purchase Order Processing Receipts category). This has been modified to also post the difference between the purchase price and the standard cost of the item as a 'value variance'.

The value variance is assigned to the Purchase Price Variance General Ledger account defined in the Stock G/L Cross Reference file; with the stock Balance Sheet account being debited with the value of the goods at the purchase price to form the contra entry. (See slides 11 & 12 for details.)

Similarly, the value variance due to the difference in the exchange rate with the planned rate at the point of receipt is calculated and assigned to the Purchase Exchange Rate Variance General Ledger account (See slides 11, 12 & 18 for details.)

Variances on receipt of the goods are calculated as follows:

Example:

Goods are received as follows: Quantity Received 10

Purchase Price DM 28

Standard Cost GBP 5

Planned Exchange Rate 3.5 Exchange Rate on Receipt 3.3 This is calculated as:

Purchase Order Processing receipts account (GBP 84.85)

Stock Balance Sheet account GBP 50

Purchase Price Variance receipt account (GBP 30) Purchase Exchange Rate Variance receipt account GBP 4.85

Note The ‘Purchase Order Processing receipt account’ posting only occurs if the system key STNLMOVTYP includes the receipt transaction (movement type ‘N’). The system key STNLMOVTYP also governs whether the Purchase Price Variance and Purchase Exchange Rate Variance postings are performed.

Note When rounding is applied to a currency, (the number of decimal places defined within the Currency file), the formulae used to calculate the PEV (Purchase Exchange rate Variance) are as follows:

accrued PEV = { (order price/receipt rate) - ROUND (order price/plan rate) } * received qty invoiced PEV = { (invoice price/invoice rate) - ROUND (invoice price/plan rate) } * received qty

Purchase Price Variance Quantity Received Purchase Price Planned Exchange Rate

--- Standard Cost–

⎝ ⎠

⎛ ⎞

×

=

Purchase Exchange Rate Variance Qty Received Purchase Price Planned Exchange Rate

--- Purchase Price Order Exchange Rate ---– ⎝ ⎠ ⎛ ⎞ × =

Purchase Invoices

Purchase Invoices have also been modified to update a new Purchase Variance History file. (See slide 20 of the PowerPoint walkthrough for details.)

If a purchase invoice has been matched to a receipt, the following details are written to the matched Receipt Variance History record:

• Invoice number

• Invoice sequence number • Invoice quantity

• Invoice price

• Invoice exchange rate

If the purchase invoice has not been matched to a receipt, a new Variance History record is written with full details of the invoice, including the following:

• Contracted exchange rate • Purchase order exchange rate • Purchase unit

• Purchase price • Standard cost • Invoice quantity • Invoice price

• Invoice exchange rate

The value of any quantity variances, price variances or exchange rate variances are calculated according to pre-defined sets of formulae. The system key POVARCALC specifies which set of formulae is used.

Example for Formula A:

With the system key POVARCALC set to ‘A’, invoice variances are calculated as follows:

Note If the system key POVARCALC is set to method ‘B’, quantity variances, price variances and exchange rate variances are not calculated.

Note The Purchase Quantity Variance does not apply to purchase order lines that are invoiced by value rather than by quantity, or for services that do not require a purchase receipt prior to invoicing.

Purchase Price Variance Quantity Received Invoice Price Planned Exchange Rate

--- Standard Cost–

⎝ ⎠

⎛ ⎞

×

=

Purchase Exchange Rate Variance Qty Received Invoice Price Planned Exchange Rate

--- Invoice Price Invoice Exchange Rate ---– ⎝ ⎠ ⎛ ⎞ × =

Technical Data

*You must read the Implementation section before attempting to install this project*

This section details important information relating to the installation of this project. If you require any further assistance please contact Customer Support.

Implementation - Existing Sites And/Or Existing Data

Licensing

• Projects DA0403 (Purchase Order Variance Analysis) and DA0533 (Purchase Invoice Matching to Goods Receipts) must be licensed for it to become available.

System Keys

The system keys listed in the System Keys section need to be set up as described.

Data Dictionary Changes

The additional data required for this project is stored in new tables which are detailed in Data Dictionary Changes

The files can be created by running the Create New Project Files application and specifying a project code of DA0403.

Options

The following options have been added:

Forms Changes

The following forms have been added:

The following subforms have been added:

Module Option Description Command Menu po po_var_hist_clear Variance History Cleardown povhclr po_house_4 st st_nl_hist_clear G/L History Cleardown stghclr st_house_4

Application Form po po_povh_cleardown main

cleardown_tickover

Application Form Suborm

st st_nl_post print_form header_detail_new header_intermediate detail_new

The following fields have been added and are not suspended:

Suspended Fields

The fields listed in the Suspended Fields section need to be unsuspended:

Conversions

Definition of Terms

Application Form(s) Field(s)

st st_nl_maint main po_ppv_acc_msg po_ppv_acc po_pev_acc_msg po_pev_acc po_pqv_acc_msg po_pqv_acc st_nl_post print_form. detail_new tran_type

Implementation - New Line 500/Sage 1000 Installations With New Data

Licensing

• Projects DA0403 (Purchase Order Variance Analysis) and DA0533 (Purchase Invoice Matching to Goods Receipts) must be licensed for it to become available.

System Keys

The system keys listed in the System Keys section need to be set up as described.

Options

Suspended Fields

The fields listed in the Suspended Fields section need to be unsuspended:

Definition of Terms

System Keys

*The following system keys will operate in the same manner as the existing PONLACC system keys, i.e. other keys can be added with a suffix of the purchase General Ledger category, e.g. PONLPPVxxx:

• PONLPQV • PONLPPV • PONLPEV

However, if these three system keys do not contain valid values, and if a stock item with a GL reference code is processed on a PO transaction, no warning message will be given but an empty line will be posted.

Description Values Used By

PONLPQV *See below PONLPPV *See below PONLPEV *See below

PONLPPVxxx Purchase Price Variance General Ledger distribution code.

A valid General Ledger account. G/L Reserve Accounts Purchase Invoices PONLPEVxxx Purchase Exchange Rate Variance General Ledger

distribution code.

A valid General Ledger account. G/L Reserve Accounts Purchase Invoices PONLPQVxxx Purchase Quantity Variance General Ledger

distribution code.

A valid General Ledger account. Purchase Invoicing POVARCALC The method used to calculate variances. ‘A’ or ‘B’ Purchase Invoicing POVARSERV Are purchase variances for service items to be

calculated?

‘YES' or ‘NO' Purchase Invoicing STNLMOVTYP The list of stock movement types to be included in the

Stock G/L Distribution Analysis posting file.

‘A’ - ‘Z’ All amended programs

STNLPEVACC Default Purchase Exchange Rate Variance General Ledger accrual account code.

Valid General Ledger posting code.

Purchase Receipts G/L Reserve Accounts Purchase Invoices STNLPPVACC Default Purchase Price Variance General Ledger

accrual account code.

Valid General Ledger posting code.

Purchase Receipts G/L Reserve Accounts Purchase Invoices STNLPQVACC Default Purchase Quantity Variance General Ledger

accrual account code.

Valid General Ledger posting code.

Purchase Receipts G/L Reserve Accounts Purchase Invoices

Data Dictionary Changes

Inventory Control

Stock G/L Transactions (st_gltran, stgltrm)

The existing Stock G/L Distribution Analysis posting file will be replaced with the following new file that has a unique primary key, holds a transaction type identifier and has a second reference for order_number:

Stock G/L Transaction History (st_gltran_hst, stghstm)

Stock G/L WO Reference (st_nl_xref_ext, stnlrfxm)

Only the following columns are relevant to this project. Column Type Key Value Notes

account char(16) 1

transaction_type char(1) 1 A’ - ‘Z Type as defined by system key STNLMOVTYP

transaction_ref char(10) 1 sequence_number char(6) 1

order_number char(10) 2 Sales, Works or Purchase order number comments char(60)

movement_date date post_flag char(1) Y/N amount_posted double

Column Type Key Value Notes

account char(16) 1

transaction_type char(1) 1 ‘A’ - ‘Z’ Type as defined by system key STNLMOVTYP

transaction_ref char(10) 1 sequence_number char(6) 1

order_number char(10) 2 Sales, Works or Purchase order number comments char(60)

movement_date date

post_flag char(1) Y/N

amount_posted double

Column Type Key Reference Value Notes

po_ppv_acc char(16) Purchase price, rate po_pev_acc char(16) and quantity variance po_pqv_acc char(16) accounts

Purchase Order Processing

Purchase Order Variance History (pop_variance_hist, povhstm)

Column Type Key Reference Value Notes

order_no char(10) 1

order_line_no char(4) 1 sequence_no char(4) 1

history_type char(3) ORD/REC/INV

history_date date

supplier char(8)

warehouse char(2)

product char(20)

unit_code char(10) Transaction unit

dp_unit char(1)

currency char(3)

operator char(1) ‘*’ or ‘/’

dp_currency char(1)

std_cost_local double Standard cost plan_rate double Contracted rate order_price double Foreign currency

order_rate double

delivery_qty double

delivery_qty_tol double

receipt_no char(6)

receipt_seq_no char(6) Unused

receipt_qty double In transaction unit

receipt_rate double

grni_accrued double System

ppv_accrued double System

pev_accrued double System

accruals_reversed char(1) System

invoice char(10)

invoice_seq_no char(4)

invoice_qty double In transaction unit invoice_price double Foreign currency

Suspended Fields

The following fields need to be unsuspended:

Definition of Terms

auth_method char (1) DA0533

analysis_a char (10) DA0512

analysis_b char (10) DA0512

analysis_c char (10) DA0512

analysis_d char (10) DA0512

analysis_e char (10) DA0512

analysis_f char (10) DA0512

Module Application Form Subform Field

st st_nl_post print_form. detail_new movement_date

Term Definition

PEV The Purchase Exchange rate Variance. PPV The Purchase Price Variance.

PQV The Purchase Quantity Variance, i.e. the quantity variance between the receipt of goods and the invoice (also known as Purchase Weight Variance).