This article explains how Sales Order / Invoice profitability is calculated in Sage 200.

NOTE: As there is no link for Order Returns or Sales Credits back to the Order or Invoice they originated from to get the cost price then these cannot be included in any reporting.

Relevant database tables

There are several tables in the Sage 200 Database linked with profitability and the various stages of the Sales Order process.

• SOPOrderLineProfitAnalysis, which links to SOPOrderProfitAnalysis [Updated on Saving Order]
• SOPDespatchLineProfitAnalysis [Updated on Confirm Despatch]
• InvoiceLineProfitAnalysis, which then links to InvoiceProfitAnalysis [Updated on SOP Post Invoice]

Values held in the tables:

There are 2 main sets of figures in each table, Estimated and Realised.

• Estimated values are updated as soon as the Order is Saved/Despatched/Invoice Posted. The cost price used for Estimated values will be the Average Cost Price.
• Realised values are dependent on several factors (i.e. SOP and POP Settings) and because of these settings can be updated by different processes, but they can effectively be reduced to 4 main areas:
  1. SOP Confirm Despatch – SOP Setting is Update Stock On Despatch + you already have some stock where Cost Price is known (or "Confirmed").
  2. SOP Post Invoice – SOP Setting is Update On Invoice Post + you already have some stock where Cost Price is known (or “Confirmed”).
  3. Record POP Invoice – POP Setting Update Price on Goods Received is Not selected + SOP Orders have processed, possibly creating a Shortfall and there are outstanding POP Orders relating to stock on these SOP Orders, meaning there is “UnConfirmed” stock.
  4. POP Confirm Goods Received - POP Setting Update Price on Goods Received is selected + SOP Orders have processed, possibly creating a Shortfall and there are outstanding POP Orders relating to stock on these SOP Orders

Profit Percentage

Within Stock Settings, there is an option for “Profit Calculation”, where Percentage Profit can be calculated as a percentage of either Revenue or Cost. To prevent the Profitability tables from having to be recalculated if this setting changes, the Profit percentage is automatically calculated for both methods and stored in the following fields:

• EstProfitPercentOnRev
• EstProfitPercentOnCost
• RealisedProfitPercentOnRev
• RealisedProfitPercentOnCost

Calculation

The profitability of the line is calculated by:

• Cost Value = Cost Rate * Line Quantity
• Issue Value = Issue Rate * Line Quantity
• Profit Value = Issue Value - Cost Value

  Profit Percentage is either:

• 100 * ((Issue Value - Cost Value) / (Cost Value)) or,
• 100 * ((Issue Value - Cost Value) / (Issue Value))

NOTE: The 'Issue Rate' used in determining the profitability of a line is the tax exclusive discounted selling price. The ‘Cost Rate’ used in the calculation will either be Estimated or Realised.