Definition:
http://en.wikipedia.org/wiki/Cash_conversion_cycle
CCC = Inventory conversion period + Receivables conversion period - Payables conversion period
- Inventory conversion period = Avg. Inventory / (COGS / 365)
- Receivables conversion period = Avg. Accounts Receivable / (Credit Sales / 365)
- Payables conversion period = Avg. Accounts Payable / (Purchases / 365)
怎麼用 SQL 取出 Inventory conversion period 呢？
-- Average Inventory
select A.activity_date, avg(B.number) as avg_inventory from
(
select max(report_date), activity_date, number from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item = '存 貨'
group by activity_date
) as A,
(
select max(report_date), activity_date, number from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item = '存 貨'
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
-- COGS
select max(report_date), activity_date, number from IncomeStmt
where
report_type = 'C'
and stock_code = '1101'
and item = '營業成本合計'
group by activity_date
-- Annual Adjusted COGS
select
max(report_date),
activity_date,
case
when strftime('%m', activity_date) = '03' then number * 4/1
when strftime('%m', activity_date) = '06' then number * 4/2
when strftime('%m', activity_date) = '09' then number * 4/3
else number
end as cogs
from IncomeStmt
where
report_type = 'C'
and stock_code = '1101'
and item = '營業成本合計'
group by activity_date
-- Inventory conversion period (days inventory outstanding)
select activity_date, dio from
(
select
C.activity_date,
C.avg_inventory / D.cogs * 365 as dio
from
(
-- Average Inventory
select A.activity_date, A.number, avg(B.number) as avg_inventory from
(
select max(report_date), activity_date, number from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item = '存 貨'
group by activity_date
) as A,
(
select max(report_date), activity_date, number from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item = '存 貨'
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
) as C,
(
--COGS
select
max(report_date),
activity_date,
case
when strftime('%m', activity_date) = '03' then number * 4/1
when strftime('%m', activity_date) = '06' then number * 4/2
when strftime('%m', activity_date) = '09' then number * 4/3
else number
end as cogs
from IncomeStmt
where
report_type = 'C'
and stock_code = '1101'
and item = '營業成本合計'
group by activity_date
) as D
where C.activity_date = D.activity_date
)
where dio is not null
order by activity_date
怎麼用 SQL 取出 Receivables conversion period 呢？
-- Receivables
select activity_date, sum(number) as receivable from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應收%'
group by activity_date, item
)
group by activity_date
-- Average Receivables
select A.activity_date, avg(B.receivable) as avg_receivable from
(
select activity_date, sum(number) as receivable from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應收%'
group by activity_date, item
)
group by activity_date
) as A,
(
select activity_date, sum(number) as receivable from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應收%'
group by activity_date, item
)
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
-- Receivables conversion period
select activity_date, dso from
(
select
C.activity_date,
C.avg_receivables / D.revenue * 365 as dso
from
(
-- Average Receivables
select A.activity_date, avg(B.receivables) as avg_receivables from
(
select activity_date, sum(number) as receivables from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應收帳款%'
group by activity_date, item
)
group by activity_date
) as A,
(
select activity_date, sum(number) as receivables from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應收帳款%'
group by activity_date, item
)
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
) as C,
(
-- Revenue
select
max(report_date),
activity_date,
case
when strftime('%m', activity_date) = '03' then number * 4/1
when strftime('%m', activity_date) = '06' then number * 4/2
when strftime('%m', activity_date) = '09' then number * 4/3
else number
end as revenue
from IncomeStmt
where
report_type = 'C'
and stock_code = '1101'
and item = '營業收入合計'
group by activity_date
) as D
where C.activity_date = D.activity_date
)
where dso is not null
order by activity_date
怎麼取出 Payables conversion period？
-- Average Payables
select A.activity_date, avg(B.receivable) as avg_receivable from
(
select activity_date, sum(number) as receivable from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應付%'
group by activity_date, item
)
group by activity_date
) as A,
(
select activity_date, sum(number) as receivable from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應付%'
group by activity_date, item
)
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
-- Payables conversion period
select activity_date, dpo from
(
select
C.activity_date,
C.avg_payables / D.cogs * 365 as dpo
from
(
-- Average Payables
select A.activity_date, avg(B.payables) as avg_payables from
(
select activity_date, sum(number) as payables from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應付帳款%'
group by activity_date, item
)
group by activity_date
) as A,
(
select activity_date, sum(number) as payables from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應付帳款%'
group by activity_date, item
)
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
) as C,
(
--COGS
select
max(report_date),
activity_date,
case
when strftime('%m', activity_date) = '03' then number * 4/1
when strftime('%m', activity_date) = '06' then number * 4/2
when strftime('%m', activity_date) = '09' then number * 4/3
else number
end as cogs
from IncomeStmt
where
report_type = 'C'
and stock_code = '1101'
and item = '營業成本合計'
group by activity_date
) as D
where C.activity_date = D.activity_date
)
where dpo is not null
order by activity_date
Hence, CCC
select A.activity_date, A.dio + B.dso - C.dpo as ccc from
(
select activity_date, dio from
(
select
C.activity_date,
C.avg_inventory / D.cogs * 365 as dio
from
(
-- Average Inventory
select A.activity_date, A.number, avg(B.number) as avg_inventory from
(
select max(report_date), activity_date, number from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item = '存 貨'
group by activity_date
) as A,
(
select max(report_date), activity_date, number from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item = '存 貨'
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
) as C,
(
--COGS
select
max(report_date),
activity_date,
case
when strftime('%m', activity_date) = '03' then number * 4/1
when strftime('%m', activity_date) = '06' then number * 4/2
when strftime('%m', activity_date) = '09' then number * 4/3
else number
end as cogs
from IncomeStmt
where
report_type = 'C'
and stock_code = '1101'
and item = '營業成本合計'
group by activity_date
) as D
where C.activity_date = D.activity_date
)
where dio is not null
order by activity_date
) as A,
(
select activity_date, dso from
(
select
C.activity_date,
C.avg_receivables / D.revenue * 365 as dso
from
(
-- Average Receivables
select A.activity_date, avg(B.receivables) as avg_receivables from
(
select activity_date, sum(number) as receivables from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應收帳款%'
group by activity_date, item
)
group by activity_date
) as A,
(
select activity_date, sum(number) as receivables from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應收帳款%'
group by activity_date, item
)
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
) as C,
(
-- Revenue
select
max(report_date),
activity_date,
case
when strftime('%m', activity_date) = '03' then number * 4/1
when strftime('%m', activity_date) = '06' then number * 4/2
when strftime('%m', activity_date) = '09' then number * 4/3
else number
end as revenue
from IncomeStmt
where
report_type = 'C'
and stock_code = '1101'
and item = '營業收入合計'
group by activity_date
) as D
where C.activity_date = D.activity_date
)
where dso is not null
order by activity_date
) as B,
(
select activity_date, dpo from
(
select
C.activity_date,
C.avg_payables / D.cogs * 365 as dpo
from
(
-- Average Payables
select A.activity_date, avg(B.payables) as avg_payables from
(
select activity_date, sum(number) as payables from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應付帳款%'
group by activity_date, item
)
group by activity_date
) as A,
(
select activity_date, sum(number) as payables from
(
select activity_date, item, number, max(report_date) from BalanceSheet
where
report_type = 'C'
and stock_code = '1101'
and item like '%應付帳款%'
group by activity_date, item
)
group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date
) as C,
(
--COGS
select
max(report_date),
activity_date,
case
when strftime('%m', activity_date) = '03' then number * 4/1
when strftime('%m', activity_date) = '06' then number * 4/2
when strftime('%m', activity_date) = '09' then number * 4/3
else number
end as cogs
from IncomeStmt
where
report_type = 'C'
and stock_code = '1101'
and item = '營業成本合計'
group by activity_date
) as D
where C.activity_date = D.activity_date
)
where dpo is not null
order by activity_date
) as C
where A.activity_date = B.activity_date
and B.activity_date = C.activity_date
and C.activity_date = A.activity_date
