Calculation of landed cost and gross margin (GM) in sales transaction items
This is an explanation of which data that affects landed cost and gross margin in sales transaction item, and how.
Abbreviations that are used:
Abbreviations | Description |
|---|---|
ART | Product |
SAR | Stock assignment register |
CRQ | Sales quote request |
CQU | Sales quote |
CPO | Sales order request |
COA | Sales order |
CSA | Shipping advice |
CIN | Sales invoice |
CCN | Sales credit note |
PWO | Production work order |
SPO | Purchase order |
SOA | Purchase order response |
SSA | Goods reception |
SIN | Supplier invoice |
CDV | Customer deviation |
LCFREIGHT | Landed cost freight This is the freight part of landed cost. BLC is including LCFREIGHT. This is calculated when fright is added to a SPO, SOA, SSA item which sales transaction item has assignments from. |
BCUR | Purchase currency This is by default set equal to currency from ARTs preferred purchase price when a sales transaction item is created. It will then normally be the currency of the PWO, SPO, SOA, SSA the item has assignments from. BCUR can anyway be overruled by user for each item. |
BRATE | Purchase rate BRATE is used as purchase rate for CRQ, CQU, CPO, COA, CSA items. This is the rate between BCUR and sales currency. |
INVRATE | Purchase rate INVRATE is used as purchase rate for CSA, CIN and CCN items. This is the rate between BCUR and sales currency at the date the CSA was shipped. |
RATE | Sales transaction rate This is the rate between sales currency and local currency at the sales transactions date (DATE). |
NETPRICE | Net price This is a sales transaction items net price in sales currency |
CNETPRICE | Converted net price Thus is a sales transaction items net price converted to local currency (NETPRICE * RATE) |
NETLCLAMT | Net Landed Cost This is the total landed cost amount for a sales transaction item in sales currency (QTY * BLC * Purchase rate) In this formula is Purchase rate either BRATE or INVRATE, depending on type of sales transaction type. |
CNETLCAMT | Converted net Landed Cost This is the total landed cost amount for a sales transaction item in local currency (QTY * BLC * Purchase rate * RATE) In this formula is Purchase rate either BRATE or INVRATE, depending on type of sales transaction type. |
BLC | Landed Cost This is the total cost for a product arriving at the warehouse. It is the key for calculation of GM. |
GM | Gross Margin This is a percentage that tells how much of a sales amount that is profit. |
How is BLC calculated?
BLC for a sales transaction item is calculated based on real cost from its SAR assignments from PWO, SPO, SOA and SSA. Or it is based on expected cost price in ART if the sales transaction item has no assignments.
BLC is the landed cost for one unit. It’s a value that is calculated in an items BCUR.
If BCUR in sales transaction item is different from the company's local currency, BLC is calculated by first calculating the cost from the ART/SAR assignments back to local currency. A company’s GM model decides which rate that is used for this:
GM model = “H” - Historic rate (RATE from SSA)
GM model = “C” - Current rate (rate at today's date)
GM model = “I” - Invoice rate (rate from SIN)
After the cost from SAR/ART is calculated back to local currency, it is converted to BCUR using exchange rate from the date of the document.
There are anyhow some sales transaction items which don’t have landed cost calculated based on assignments from SAR :
CQU, COA, CSO, CSA and CIN items for non-stock products (ART not using assignments). Landed cost for these are stored directly in the item. It is copied from ART as default, but may be manually overruled by user.
CIN items of type “Advance invoice settlement” or which have been created by forward from another CIN/CCN. These items do not have any landed cost.
CIN items created from a CDV. Landed cost for these are calculated based on the content in “LCCalc” in the CDV.
For all other CIN items Landed cost is copied from the shipping advice (CSA) or service order (CSO) item is has been forwarded from.
How is GM calculated?
Gross Margin is the difference between sales transactions items net price and landed cost, in percentage of net price. It is calculated like this: (NetPrice – (( BLC * Purchase rate ) * 100)) / NetPrice.)
Purchase rate is either BRATE or INVRATE, depending on sales transaction type.
Examples of calculation of BLC and GM for each GM model:
Local currency : NOK
Todays rate: 11.7
If GM model is “H” (Historic rate)
Calculation of BLC:
BLC = ((( SSA.NetPrice + SSA.FreightPrice ) * SSA.Rate ) / Rate between local CUR and BCUR ) / COA.Qty
BLC = ((( 100 + 10 ) * 11.5 ) / 11.4 ) / 10
BLC = ((( 110 ) * 11.5 ) / 11.4 ) / 10
BLC = (( 1265 ) / 11.4 ) / 10
BLC = ( 110.965 ) / 10
BLC = 11.0965 EUR
Calculation of GM in COA item:
GM = ((COA.NetPrice - ( BLC * COA.Purchase rate )) * 100 ) / COA.NetPrice
GM = (( 150 - ( 11.0965 * 11.3 ) * 100 ) / 150
GM = ( 150 - ( 125.39045 ) * 100) / 150
GM = (( 24.61 ) * 100 ) / 150
GM = ( 2461 ) / 150
GM = 16.41 %
Calculation of GM in CIN item:
GM = ((CIN.NetPrice - ( BLC * CIN.Purchase rate )) * 100 ) / CIN.NetPrice
GM = (( 150 - ( 11.0965 * 11.5 ) * 100 ) / 150
GM = (( 150 - ( 127.61 ) * 100) / 150
GM = (( 22.39 ) * 100 ) / 150
GM = ( 2239 ) / 150
GM = 14.93 %
If GM model is “I” (Invoice rate)
Calculation of BLC:
BLC = ((( SSA.NetPrice + SSA.FreightPrice ) * SSA.Rate ) / Rate between local CUR and BCUR ) / COA.Qty
BLC = ((( 100 + 10 ) * 11.6 ) / 11.4 ) / 10
BLC = ((( 110 ) * 11.6 ) / 11.4 ) / 10
BLC = (( 1276 ) / 11.4 ) / 10
BLC = ( 111.93 ) / 10
BLC = 11.193
Calculation of GM in COA item:
GM = ((COA.NetPrice - ( BLC * COA.Purchase rate )) * 100 ) / COA.NetPrice
GM = (( 150 - ( 11.193 * 11.3 )) * 100 ) / 150
GM = (( 150 - ( 126,4809 )) * 100 ) / 150
GM = (( 23.5191 ) * 100 ) / 150
GM = ( 2351.91 ) / 150
GM = 15.68 %
Calculation of GM in CIN item:
GM = ((CIN.NetPrice - ( BLC * CIN.Purchase rate )) * 100 ) / CIN.NetPrice
GM = (( 150 - ( 11.193 * 11.5 ) * 100 ) / 150
GM = ( 150 - ( 128.7195 ) * 100) / 150
GM = (( 21.2805 ) * 100) / 150
GM = ( 2125.05 ) / 150
GM = 14.19 %
If GM model is “C” (Current rate)
Calculation of BLC:
BLC = ((( SSA.NetPrice + SSA.FreightPrice ) * Todays rate ) / Rate between local CUR and BCUR) / COA.Qty
BLC = ((( 100 + 10 ) * 11.7 ) / 11.4 ) / 10
BLC = ((( 110 ) * 11.7 ) / 11.4 ) / 10
BLC = (( 1287 ) / 11.4 ) / 10
BLC = ( 112.895 ) / 10
BLC = 11.2895
Calculation of GM in COA item:
GM = ((COA.NetPrice - ( BLC * Todays rate )) * 100 ) / COA.NetPrice
GM = (( 150 - ( 11.2895 * 11.7 )) * 100 ) / 150
GM = (( 150 - ( 132.0872 )) * 100 ) / 150
GM = (( 17.9128 ) * 100 ) / 150
GM = ( 1791.28 ) / 150
GM = 11.94 %
Calculation of GM in CIN item:
GM = ((CIN.NetPrice - ( BLC * CIN.Purchase rate )) * 100 ) / CIN.NetPrice
GM = (( 150 - ( 11.2895 * 11.5 ) * 100 ) / 150
GM = ( 150 - ( 129.8293 ) * 100) / 150
GM = (( 20.1707 ) * 100) / 150
GM = ( 2017.07 ) / 150
GM = 13.45%