2962133 - Variance Calculation and Variance Report in Production Order

Symptom

You have closed a Production Order (PO) and need to explain the cost of the product, or the production variance (Total Variance).

Image/data in this SAP Note is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Cause

Functionality description

Frequently Asked Questions (FAQs)

The following FAQs are explained:

1. How to check the variance in the Variance Report?
2. How is the Variance calculated in a Production Order when you close it?

         2.1. Find DocEntry of the production order.

         2.2. Find total transaction values of the product item.

         2.3. Find total transaction values of inventory component items.

         2.4. Find total transaction values of resources and non-inventory components.

         2.5. Find total transaction values of by-product items.         

         2.6. The revaluated value when closing the production order

         2.7. The Actual Product Cost displayed in production order

1. 1. 1. Scenario 1: Parent item received with a single batch
      2. Scenario 2: Parent item received with multiple batches

         2.8. The Variance after closing the production order

1. How to check the variance in the Variance Report?

The variance breakdown is available in the Variance Report (as of SAP Business One 9.3 PL02).

 Scenario:

      1. Create a product item P1, and component items C1 and C2.
      2. Go to Production → Bill of Materials, choose Product No. P1, components C1 and C2, Issue Method Manual.
      3. Create Inventory Revaluation for component item C1 (New Cost 10) and C2 (New Cost 20).
      4. Create Goods Receipt for component item C1 and C2, quantity 2.
      5. Go to Production → Production Order, choose P1 where Planned Quantity is 1, Base Ratio for component C1 and C2 are both 1, and add it.
      6. Open the Production Order from step 5, and update its status to Released.
      7. Right-click the PO, select Issue Components and add Issue for Production with quantity 2 for component item C1 and quantity 1 for component item C2.
      8. Right-click the PO, select Report Completion and add Receipt from Production for the Production Order from step 5.
      9. Open the Production Order from step 5, click the yellow arrow next to Total Variance on the Summary tab.
      10. The consumed quantity 2 of component C1 in production is greater than the planned quantity 1, which causes a negative variance.

      11. Close the Production Order from step 5 and the following calculations are made: As there is a difference between the two summed amounts, the difference is referred to as Total Variance and shown on the Summary tab of the Production Order.

• The system sums the values of the received product P1.
• The system sums the values of the issued components from Issue for Production.

 2. How is the Variance calculated in a Production Order when you close it?

When calculating the component cost and product cost, we have to calculate it as a sum of all issues and receipts.

To check the Actual Item Component Cost and Actual Product Cost, you can click the orange arrow to open Inventory Posting List from the Summary tab of the Production Order. Go document by document in the report and check the value of every document.
Having that we can calculate all of the documents as a sum of transaction values (TransValue) of inventory tables as follows:

2.1. Find DocEntry of the production order.

select * from OWOR where "DocNum" = XX

--Replace XX with the Production Order NO.
--Note the DocEntry value.

2.2. Find total transaction values of the product item.

select abs(sum("TransValue")) from IVL1 where "TransSeq" in
(select "TransSeq" from OIVL where "MessageID" in (select "MessageID" from OILM where "TransType" in (59, 60)
 and "BaseAbsEnt"=XX and "BaseType"=202 and "ActionType" in (1,2) and "AppObjType"='P'));
--Replace XX with the OWOR.DocEntry of the production order.

2.3. Find total transaction values of inventory component items.

select abs(sum("TransValue")) from IVL1 where "TransSeq" in
(select  "TransSeq"  from OIVL  where "MessageID" in (select  "MessageID"  from OILM where "TransType" in (59, 60)
 and "BaseAbsEnt"=XX and "BaseType"=202 and "ActionType" in (1,2) and "AppObjType"='C'));

--Replace XX with the OWOR.DocEntry of the production order.

2.4. Find total transaction values of resources and non-inventory components.

select sum("TotalLC") from ILM3 where "BaseAbsEnt" in (select "DocEntry" from IGE1 where "BaseEntry"=XX and "BaseType"=202);
--Replace XX with the OWOR.DocEntry of the production order.

--The total transaction values of resources can be seen in ORTL table if the production order row does not contain non-inventory components.

select sum("LineTotal") from ORTL where "BaseEntry"=XX and "BaseType"=202;
--Replace XX with the OWOR.DocEntry of the production order.

If you need to check the transaction values of resources and non-inventory components for each itemcode, use the following query:

SELECT sum(i."TotalLC"),i."ItemCode" FROM OILM o
left join ILM3 i on o."MessageID" =i."MessageID"
join IGN1 i2 on o."TransType" =59 and o."DocEntry" =i2."DocEntry" and o."DocLineNum" = i2."LineNum"
WHERE i2."BaseEntry" = XX and i2."BaseType" = 202 and o."AccumType" = 1
group by i."ItemCode";
--Replace XX with the OWOR.DocEntry of the production order.

2.5. Find total transaction values of by-product items.

When the production order row contains by-product items, use the following detection queries:

select abs(sum("TransValue")) from IVL1 where "TransSeq" in
(select  "TransSeq"  from OIVL  where "MessageID" in (select  "MessageID"  from OILM where "TransType" in (59, 60)
 and "BaseAbsEnt"=XX and "BaseType"=202  and "ActionType" in (1,2)  and "AppObjType"='B'));

--Replace XX with the OWOR.DocEntry of the production order.

When there is a difference between product value and all components (step 2 minus step 3 minus step 4 <> 0), this results in a product variance, referred to as Total Variances in the application and shown on the Summary tab of the Production Order. In such case the system needs to revaluate the product value when closing the production order.

2.6. The revaluated value when closing the production order can be found by the following query:

select I.* from IVL1 I, OIVL O where I."TransSeq" = O."TransSeq" AND O."TransType" = 202 AND O."CreatedBy" = XX;
--Replace XX with the OWOR.DocEntry of the production order.

--Note the RevalTotal value.

--The RevalTotal value corresponds to the proportion of product quantity still in stock, in case the onhand quantity decreased before closing production order.

2.7. The Actual Product Cost displayed in production order = Product Item Value + RevalTotal

2.7.a. The Actual Product Cost (for Standard/Moving Average/FIFO valuated parent item) displayed in production order = Product Item Value + RevalTotal

For easier understanding, see the following example:

1. Having a production order with one component item of the Manual issue type, you report completion for quantity 1.
2. SAP Business One will take the cost of the component item and with this cost the parent is taken into stock, however the component item is not issued.
3. So we can say that you make stock value of the parent from nothing. To be accurate, we have to re-valuate the stock value of the parent item if needed.
4. If you close the production order, this step is taken automatically and the parent item will be revaluated to the value of zero, because that should be the real value.
5. If you open Inventory Audit Report, you will see the new type of transaction - PW - the revaluation for production order closing.

2.7.b. The Actual Product Cost (for Serial/Batch valuated parent item) displayed in production order = Product Item Value + Production Order Close Value (Production Order Close Value <> RevalTotal)

Below are given 2 scenarios covered in the section 2.7.a. and 2.7.b. for 'RevalTotal Value' and 'Variance' calculation for batch managed items with Serial/Batch valuation method.

• Scenario 1: Parent item received with a single batch
• Scenario 2: Parent item received with multiple batches

Scenario 1: Parent item received with a single batch

1. Create a production order with:
   1. Parent item (P1 test) that is batch managed and has Serial/Batch valuation method.
   2. Only one component item that has Standard valuation method to simplify the calculation. Make sure that child item has enough quantity in stock.
   3. The standard cost of the component should be set to 100.
2. Change status of Production Order from Planned to Released.
3. Right click the PO, select Issue Components option and issue the 100 quantity of child item. Actual Item Component Cost is 100 * 100 = 10000.
4. Right click the PO, select Report Completion option to open the Receipt from Production window in Add mode.
   1. Manually change the quantity (Completed) in row level from 100 to 125.
   2. Assign the single batch no. 'B1' to this 125 quantity.
   3. Add the document.
5. Now check the Summary tab of Production Order and Actual Product Cost is showing 125 * 100 = 12500.
6. In this situation, Variance would be (Actual Item Component Cost – Actual Product Cost) = 10000 - 12500 = - 2500.
7. Add a Goods Issue document for parent item to issue 25 quantity of batch 'B1'. So now the quantity in stock for parent item is 100, and the on batch qty for 'B1' is also 100.
8. Add a Goods Receipt document for parent item to receive the 10 quantity but for different batch like 'another batch'.
9. In Item Master Data and Inventory Audit Report, the quantity in stock should be 110 for parent item and in Batches and Serials Inventory Audit Report, the quantity for batch 'B1' is 100 and for batch 'another batch' is 10.                                                                                                                                                   
   

   Attachment : image.png

                                                  

10. Change status of Production Order from Released to Closed and update the document. Now in the Summary tab, Total Variance is showing 500.                                                                                     
    

    Attachment : image.png

                                                                                                                                                                

As there is a difference between the parent value and the sum of all components' value, this results in the product variance, referred to as Total Variance in the application and shown on the Summary tab of the production order.                       

In such case the system needs to revaluate the parent value when closing the production order. Here we will introduce the following 3 values:

• • Variance before closing the Production Order
  • Production Order Close Value
  • RevalTotal Value (The RevalTotal value is also explained in SAP Note 2962133.) 
    • A.    Variance before Closing the Production Order

        It is explained in the above step 3, 4, 5 and 6. 

• • • B.    Production Order Close Value

After closing the production order, how is the variance 500 calculated? The system calculates the variance by batch qty (quantity of batch 'B1' is 100 and quantity of batch 'another batch' is 10) as the item has Serial/Batch valuation method and is managed by batch. See the Batches and Serials Inventory Audit Report above.                                                                                                                                 

At the time of closing production order, only 100 of the 125 quantity (125 is completed quantity) for the batch 'B1' is in stock.

Production Order Close Value (for parent item of batch 'B1') is calculated with below given calculation logic:

= (Component Value - Product Value) * (on Batch Quantity / Completed Quantity of this Production Order)

= (10000 - 12500) * (100 / 125)

= (- 2500) * (0.80)

= - 2000

So, the total Production Order Close Value = - 2000

Variance (after closing the Production Order):

= Product Value + By-Products Value - Component Value - Labors & Resources Value + Production Order Close Value 

= 12500 + 0 - 10000 - 0 + (- 2000)

= 12500 - 12000

= 500

• • • C.    RevalTotal value
    • The RevalTotal is calculated with below given calculation logic:

= (Actual Item Component Cost - Actual Product Cost) * (In Stock Quantity / Completed Quantity)

= (10000 - 12500) * (110 / 125)

= - 2500 * 0.88

= - 2200

Scenario 2: Parent item received with multiple batches:

1. Create a production order with:
   1. Parent item (P1 test) that is batch managed and has Serial/Batch valuation method.
   2. Only one component item that has Standard valuation method to simplify the calculation. Make sure that the child item has enough quantity in stock.
   3. The standard cost of the component should be set to 100.
2. Change the status of the production order (PO) from Planned to Released.
3. Right click the PO, select the Issue Components option and issue the 100 quantity of child item. Now Actual Item Component Cost is 100 * 100 = 10000.
4. Right click the PO, select the Report Completion option to open the Receipt from Production window in Add mode.                                                                                                   
   1. Manually change the quantity (Completed) in row level from 100 to 125.
   2. Assign the batch 'B1' with 100 quantity, batch 'B2' with 20 quantity, batch 'B3' with 5 quantity.
   3. Add the document.
5. Now check the Summary tab of the production order and Actual Product Cost is showing 125 * 100 = 12500.
6. In this situation, Variance would be (Actual Item Component Cost – Actual Product Cost) = 10000 - 12500 = - 2500
7. Add a Goods Issue document for parent item to issue only 25 quantity (where 10 quantity of batch 'B1', 10 quantity of batch 'B2' and 5 quantity of batch 'B3'). Now the quantity in stock for batch 'B1' is 90, quantity in stock for batch 'B2' is 10, quantity in stock for batch 'B3' is 0.                                                               
   

   Attachment : image.png

                                                       

8. Add a Goods Receipt document for the parent item to receive the 10 quantity but for different batch like 'B4'.
9. In Item Master Data and Inventory Audit Report, the quantity in stock should be 110 for the parent item. In Batches and Serials Inventory Audit Report, the quantity for batch 'B1' is 90, for batch 'B2' is 10, for batch 'B3' is 0 and for batch 'B4' is 10.
10. Change status of the production order from Released to Closed and update the document. Now in the Summary tab, Total Variance is showing 500.                                                                                 
    

    Attachment : image.png

As there is a difference between the parent value and the sum of all components value, this results in a product variance, referred to as Total Variance in the application and shown on the Summary tab of the production order.

In such case the system needs to revaluate the parent value when closing the production order.

Select I.* from IVL1 I, OIVL O where I."TransSeq" = O."TransSeq" AND O."TransType" = 202 AND O."CreatedBy" =XX;

--Replace XX with the OWOR.DocEntry of the production order.

A. Variance before Closing the Production Order  
    It is explained in the above step 3, 4, 5 and 6.

B. Production Order Close Value                                                                                                                                                                                                 
After closing the production order, how is the variance 500 calculated? System calculates the variance by 'Batch Quantity in Stock' (on batch quantity of 'B1' is 90, on batch quantity of 'B2' is 10, on batch quantity of 'B3' is 0) as the item has Serial/Batch valuation method and is managed by batch. See the Batches and Serials Inventory Audit Report above. At the time of closing the production order, only 90 of the 125 quantity (125 is completed quantity) for the batch 'B1' is in stock. The calculation logic is the same as in Scenario 1 but applied per batch.
= (Component Value - Product Value) * (on Batch Quantity / Completed Quantity of this Production Order)

Production Order Close Value (for parent item and batch 'B1'):
= (10000 - 12500) * (90 / 125)
= (- 2500) * (0.72)
= - 1800
Production Order Close Value (for parent item and batch 'B2'):
= (10000 - 12500) * (10 / 125)
= (- 2500) * (0.08)
= - 200
Production Order Close Value (for parent item and batch 'B3'):
= (10000 - 12500) * (0 / 125)
= (- 2500) * (0)
= 0
The total Production Order Close Value = (- 1800) + (- 200) + (0) = - 2000
Variance (after closing the production order):
= Product Value + By-product Value – Component Value – Labors & Resources Value + Production Order Close Value 
= 12500 + 0 - 10000 - 0 + (- 2000)
= 500.               

1. RevalTotal 

In Scenario 1, the quantity in stock 110 was used to calculate the RevalTotal when receiving the parent item with the same batch.

In Scenario 2, the parent item is received with several batches so the quantity in stock is not considered, and system uses the Real Received Quantity of the parent item for each batch at the time of adding the Receipt from Production document.

The RevalTotal is calculated with below given calculation logic:

 = (Actual Item Component Cost - Actual Product Cost) * (In Stock Batch Quantity / Completed Quantity)

The RevalTotal for batch 'B1' = (10000 - 12500) * (100 / 125) = - 2500 * 0.80 = - 2000.

The RevalTotal for batch 'B2' = (10000 - 12500) * (20 / 125) = - 2500 * 0.16 = - 400.

The RevalTotal for batch 'B3' = (10000 - 12500) * (5 / 125) = - 2500 * 0.04 =  - 100.

2.8. The Variance after closing the production order

2.8.a. The Variance for Standard/Moving Average/FIFO valuated parent item after closing the production order

   = Product Value + By-Product Value - Inventory Component Value - Resources and Non-Inventory Value + RevalTotal

2.8.b. The Variance for Serial/Batch valuated parent item after closing the production order

   = Product Value + By-Product Value - Inventory Component Value - Resources and Non-Inventory Value + Production Order Close Value