[SQL] CCC研究

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