Most Profit(Loss) reader especially which are not referring to the Cash Flow report as comparison benchmark are unaware with the increased of current assets account. Inventory is one of the accounts that easily camouflage those profit numbers.
To simply illustrate that, say there's a retail company that operates only in cash both for purchase and sales, don't have any debt and loan. This company start with 1 billion IDR cash capital, buy merchandise for 1 billion IDR, and sell it for 2 billion IDR. Then the company gain 1 billion IDR profit. Now the company use all the money buying the inventory for 2 billion IDR amount. By the end of the period, this company will book 1 billion IDR of starting Capital, 1 billion IDR of profit which reflect to its asset; 2 billion IDR of inventory.
The question then arise, what happened if that 2 billion IDR of inventory turns out can not be sold because it is expired? They have to sell the merchandise as scrap, and suddenly that 2 billion IDR inventory is not worth that amount of money anymore. If the company sell it all as scrap at 200million IDR , by the end of period, the company suddenly have loss position at 800million IDR.
In various scales and sometimes out of periodic control, this thing happened in the companies. Dead stock, unusable stock, expired stock, bad stock which booked in the accounting with its purchase value, are illusions of profit.. Since it is an illusion, it can also be used as fraud account especially for a company that have a lot of inventory traffic. What happened with merchandise stock that don't have expiry date? Keeping stock has its own cost. i.e warehouse cost and interest cost for the money value. As long as that is not a merchandise that have increasing value overtime like wine or smoked meat, the keeping cost can not be compensated with increased revenue of merchandise sales.
Some company as dictate by accounting standard operating procedure, trying to minimize the loss by doing periodic stock taking. But the problem with stock taking is mostly the activity only focus and check whether the stock is there with the correct quantity or not. Hence it solve only book accuracy problem, the illusion problem of inventory value is still there.
Inventory Aging
Since most stock is degrading its value overtime either caused by expiry time or the keeping cost, we can conclude that by monitoring the age of stock can also helping company to prevent its degrading value.
The idea of inventory aging is just as simple as classify and existing inventory based on the time of procurement, and calculate the financial value for each class. However at the floor, the effort and the method is not just as simple as it sound. Especially if the warehouse management strategy is not efficient enough to make sure that the flow of stock is FIFO (First In First Out). But this following idea, will at least giving the big picture of stock condition. The real condition could be worse, but cannot be better than Inventory Aging calculation.
Calculation Technic
Inventory aging calculation, algorithm is as simple as
1. Calculating the current stock value of inventory.
2. Query the last purchased particular stock in total amount that match current stock value.
3. Classify query result based on purchased date.
Step #1 : Calculating current stock value of Inventory
Code: Select all
declare MyAccount cursor local static read_only forward_only for
select b.gl_subcode,sum(b.gl_value)
from Accounting.[journal.header] a
inner join accounting.[journal.detail] b on a.hdr_id = b.hdr_id
where a.gl_date <= @Date and b.acc_no = @AccNo and b.cc_id = @CCID
group by gl_subcode
Step #2 : Get all GL transaction data from last "increment transaction of inventory" based on transaction date, but only the ones that the summary value match the current stock value
Code: Select all
open MyAccount
fetch next from MyAccount into @SubCode,@TargetValue
while @@Fetch_Status=0
begin
if @Sign=sign(@TargetValue)
begin
if @Sign<0 --- for Credit account
insert into @Result select gl_id,case when balance>0 then gl_value+balance else gl_value end
from
(select b.gl_id, b.gl_value,@TargetValue-sum(b.gl_value) over (order by a.gl_date desc,b.gl_value) as balance
from accounting.[journal.header] a
inner join accounting.[journal.detail] b on a.hdr_id = b.hdr_id
where a.gl_date <= @Date and b.gl_subcode = @Subcode and b.acc_no = @AccNo and b.cc_id = @CCID and b.gl_value<0
) a where balance+gl_value<0
else -- for Debit account
insert into @Result select gl_id,case when balance<0 then gl_value+balance else gl_value end
from
(select b.gl_id, b.gl_value,@TargetValue-sum(b.gl_value) over (order by a.gl_date desc,b.gl_value desc) as balance
from accounting.[journal.header] a
inner join accounting.[journal.detail] b on a.hdr_id = b.hdr_id
where a.gl_date <= @Date and b.gl_subcode = @Subcode and b.acc_no = @AccNo and b.cc_id = @CCID and b.gl_value>0
) a where balance+gl_value>0
end
fetch next from MyAccount into @SubCode,@TargetValue
end
close MyAccount
deallocate MyAccount
Step #3 : Query the GL Id from @Result, pivot based on the age
Code: Select all
select
group_id,
group_name
,isnull([1],0) as Period1
,isnull([2],0) as Period2
,isnull([3],0) as Period3
,isnull([4],0) as Period4
,isnull([1],0)+isnull([2],0)+isnull([3],0) +isnull([4],0) as Total
from (
select * from (
select
d.group_id, e.group_name, a.gl_value,
case when datediff(day, c.gl_date, @docdate)>60*3 then 4
--when
else CEILING(CONVERT(DECIMAL,datediff(day, c.gl_date, @docdate))/60) end as dt
from @Result a
inner join accounting.[journal.detail] b on a.gl_id = b.gl_id
inner join accounting.[journal.header] c on b.hdr_id = c.hdr_id
inner join inventory.[item.master] d on b.gl_subcode = d.item_no
inner join inventory.[item.group] e on d.group_id = e.group_id
) a pivot (
sum(a.gl_value) for
dt in ([0],[1],[2],[3],[4])
) as pv
) a
NB:
More comprehensive coding can be found in the UDF : [Accounting].[Outstanding.Local.General.Alternate]
Faster performance driven query can be found in the UDF : [Accounting].[Outstanding.Local.General], using cursor and fetch is faster than sub-query.
