SAP Business One - Moving Average Costing Calculation

 

Moving average valuation method calculates the inventory value by the item's cost price. The item's Cost Price field is found in Inventory --> Item Master Data --> Inventory Data tab page. This field is updated dynamically by every stock receipt posting.

In the moving average method of inventory valuation, the average cost of items in inventory is recalculated each time new stock is received. This average cost is determined by taking the total cost of all items in inventory and dividing it by the total quantity of items.

Here's how the moving average method works in SAP Business One:

  1. Initial Setup: In SAP Business One, you would set up your inventory items and specify that you want to use the moving average method for valuation.

  2. Goods Receipt: When you receive new items into inventory, the system calculates the new average cost based on the cost of the incoming items and the existing items in inventory.

  3. Goods Issue: When items are sold or used, the system adjusts the inventory value and cost of goods sold based on the moving average cost.

  4. Recalculation: Each time new stock is received, the moving average cost is recalculated by considering the total value of the existing inventory and the newly received items.

  5. Reporting: SAP Business One provides reports that show the value of inventory, cost of goods sold, and other financial information based on the moving average method.

Moving Average valuation under perpetual inventory system - See below Example


   (*1) Average cost of 800 units 
           = (700x$10 + 100x$12) / (700 + 100)
           = ($7,000 + $1,200) / 800 = $8,200 / 800 = $10.25

           Cost of goods sold on May 8 = 500x$10.25 = $5,125

   (*2) Average cost of 900 units 
           = (300x$10.25 + 600x$14) / (300 + 600)
           = ($3,075 + $8,400) / 900 = $11,475 / 900 = $12.75

   (*3) Average cost of 1,100 units 
           = (900x$12.75 + 200x$15) / (900 + 200)
           = ($11,475 + $3,000) / 1,100 = $14,475 / 1,100 = $13.16

           Cost of goods sold on May 25 = 400x$13.16 = $5,264
           Cost of goods sold on May 27 = 100x$13.16 = $1,316

    Total cost of goods sold
           = 500x$10.25 + 400x$13.16 + 100x$13.16
           = $5,125 + $5,264 + $1,316 = $11,705

    Cost of ending inventory 
           = Beginning inventory + Cost of purchases - Cost of goods sold
           = $7,000 + (100x$12 + 600x$14 + 200x$15) - $11,705
           = $7,000 + $12,600 - $11,705 = $7,895

[Checking]
   Quantity of ending inventory 
          = Beginning inventory + Units purchased - Units sold
          = 700 + 900 - 1,000 = 600 units

   Cost of ending inventory
           = 600 x $13.16 (Moving Average cost per unit as of May 31)
           = $7,896
   
   $7,896 - $7,895 = $1 (rounding error) 

It's important to note that the moving average method provides a smoother valuation compared to methods like FIFO or LIFO, as it spreads the cost of purchases across all inventory items. This can be especially useful when there are frequent price fluctuations or when managing perishable goods.

Comments

Post a Comment

Popular posts from this blog

Top 3 Questions on Printing Checks for Payment in SAP Business One

Image
  SAP Business One provides some special functions for Check for Payment, ranging from the definition of a House Bank Account to printing a batch of Checks for Payment. These are the Top 3 Questions we see in SAP Business One Support about printing Checks for Payment. Question 1: How do you print Checks for Payment if the checking account is locked? SAP Business One will not allow you to print a check when another user is accessing the checking account. The Check Printing will be locked at that time. Sometimes the lock is not released, and if you try to print the Check for Payment, you will see the message ‘You cannot print checks while another user is accessing this checking account’. To release the lock, complete the following step: Under   Administration   →   Setup   →   Banking   →   House Bank Accounts , deselect   Lock Checks Printing   for the relevant bank accounts. Question 2: How can you set default print layout for your Check...
Read more

How to Import Fixed Assets Legacy Data into SAP Business One from Excel

  If you are implementing SAP Business One Fixed Assets solution and you are confused how to import the legacy data, this article will help you. A simple example will show you how to import opening balance from Excel. Business Scenario: Your company owns a car. It was acquired in September 2016 with price 600000 EUR and it is expected to be used for 10 years. This car started depreciation since September 2016 and 20000 EUR has been depreciated in year 2016. Now you would like to implement the Fixed Assets solution since year 2017 and import the value of this car into SAP Business One. What need to be done? Steps: Enable Fixed Assets solution Go to Administration -> System Initialization -> Company Details -> Basic Initialization, tick the checkbox ‘Enable Fixed Assets 2.  Define the basic parameters for Fixed Assets management Account Determination: Administration -> Setup ->Financials ->Fixed Assets -> Account Determination     ...
Read more

SAP Business One - FIFO Method Of Stock Valuation

  The FIFO method assumes that the first items received into inventory are the first ones to be sold. This method matches the cost of goods sold (COGS) with the oldest inventory costs, which can provide a more accurate representation of costs when prices are changing over time. FIFO valuation under perpetual inventory system Date Transactions Units Sold Unit Cost Inventory Units May 1 Beginning Inventory 700 $10 700 May 3 Purchase 100 $12 800 May 8 Sale (*1) (500) ?? 300 May 15 Purchase 600 $14 900 May 19 Purchase 200 $15 1,100 May 25 Sale (*2) (400) ?? 700 May 27 Sale (*3) (100) ?? 600 May 31 Ending Inventory   ??      (*1) 500 units sold             = 700 units from beginning inventory of at $10 unit cost.            Cost of goods sold = 500x$10 = $5,000    (*2) 400 units sold             = 20...
Read more