最初に
本記事はBIツールをつかって損益計算書（PL表）を表現したい方に対して書いた記事です。
記事内ではDr.SumやMotionBoardという専門ツールをつかっていますが、内容はどのBIツールでも共通の課題であると思っていますので、ぜひ記事を読み進めていってください。
損益計算書（PL表）って？
社会人生活をしていると、どこかでこのような帳票を見る機会はございませんか？
この表は損益計算書、またの名をProfit & Lossという名前の表で、PL表と呼ばれるものです。
企業や部門が
・どれくらい売上を上げたか
・売上に対してどれだけ原価を払ったか
・売るためにどれだけ広告を出したり、人が動いたか、人が動くための準備が必要だったか
・税金はいくらかかったか
が一覧で可視化され、それぞれの利益いくらであったかをチェックし、会社の業績を把握するものとなります。
上の図は勘定科目をまとめたもののみにしていますが、例えば[販売費および一般管理費]、つまり販管費の内訳は下の図となります。
企業によって勘定科目の内容は多少異なりますが、大きな分類の項目は同じことでしょう。
こちらの表をBIツールで表現したいというニーズをよく伺います。
率直にいうと、難しい。でもやれなくはない、というのがぼくの見解です。
具体的なやり方の前に、難しいポイントについて解説をしましょう。
明細である仕訳データを集計する難しさ
会計システムの明細データは仕訳データです。
この仕訳データは売上データなどとは異なり、集計するのが非常に厄介です。
非常に厄介な細かい理由については、別記事を参照してください。
データの集計対象が仕訳データの場合は、BIで集計できる形までデータベースで加工した方が良いです。
アプリケーション層であるBIツールで無理矢理つくっても、メンテナンス性が悪くなってしまいます。
例えば勘定科目がひとつ追加されるだけで、大幅な改修が必要となるでしょう。
PLデータは出力できる
ぼくは案件で様々な会計システムを見ています。そのどれもで言える共通点は、
だいたいの会計システムでPL表の元データはCSV出力が可能です。
PL表のBIツールでの可視化はそのPL表のデータをつかいましょう。
ただし整形されている、データ分析しやすい表現になっているかはパッケージによってまちまちです。
以下では理想となるデータの形を前提に解説します。
可視化の準備をする
さて、データが手に入ったところで、いよいよ可視化の準備です。
PL表のデータは綺麗に整形されている状態ですと、このような形になります。
会計システムによってはシナリオが入っている場合があります。
実績値だったり一次予算、修正予算、見込みなどが入っていて、期中の業績に応じて細かく金額の修正が入ってきます。
後述しますが、これらは実績との差分を可視化すると、ユーザーにとても喜ばれます。
ここでポイントがひとつあります。
会計システムによっては黄色網掛けのデータが入っているかいないかがわかれる時があります。
この黄色網掛けの項目はいわば、勘定科目の中分類と大分類になります。
BIツールで表現する場合、上位となる勘定科目はあった方がよいのです。
えっ？ BIなんだから、もし上位科目がなければ集計すればいいじゃんって思いますよね。
ここがPL表の厄介なところで、ここでもただ修正すればいいってもんじゃないのです。
PL表の表現はやや特殊で、売上というプラスの科目も、販管費というマイナスの科目も数値はすべてプラスで表現します。
つまり
売上 100
販管費 100
とPL表で表現されていても、利益を出す計算では 売上 ー 販管費 と引き算をしないとならないのです。
どの科目は足して、どの科目は引いて、という制御をしなければならないのが、BIツールの集計では表現し難いところなのですね。
ではどのようにすれば良いかを解説します。
もし計算済みのデータを持っていらっしゃる場合は下記の[可視化をする]まで飛ばしてください
縦の計算をする
ここでの縦の計算とは、先ほど解説した勘定科目の大分類と中分類の計算です。
これを事前に計算しておきます。
下の図をご覧ください。
こちらの図では[売上]、[売上原価]、[売上総利益]が計算の対象項目です。
図の右側にあるように計算式にはマイナスを含みます。
大半のBIツールではこの計算を行いながら表をつくることができません。
例外としてMotionBoardであれば[データ補完]と[キーマップ事後計算]をつかって、
データとして存在しない科目をつくり、特定の科目に縦の四則演算をするという高度な機能がございます。
しかし、MotionBoardでできなくもないけど、集計表のひとつひとつの式が複雑になり、保守性が下がってしまいます。
ここはやはりデータベース側で解決することが望ましいです。
プログラムを書く
いきなり[プログラムを書く]とありますが、難しい話ではありません。
やりたいことは
・データとして存在しない上位の科目をつくる
・その科目に特定の計算をさせる
とこれだけです。
前提としてSQLを発行しても良いデータベースを用意する必要があります。
ここではDr.Sumにデータを取り込み、そこでSQLを発行する前提で記述をします。
SQLのサンプルはこちらです。
INSERT INTO DM_PL(
SELECT
年度,
月度,
シナリオコード,
シナリオ名,
組織コード,
組織名,
'150' AS 勘定科目コード,
'売上総利益' AS 勘定科目名,
SUM(DECODE(勘定科目名,'売上',金額,0)
-DECODE(勘定科目名,'売上原価',金額,0)
) AS 金額
FROM DM_PL
GROUP BY
年度,
月度,
シナリオコード,
シナリオ名,
組織コード,
組織名,
'150',
'売上総利益'
)
;
実際はデータベースの項目名に沿って記載しますが、今回は項目の構成を
であると定義して、解説を進めます。
このSQLで、本来のデータとして存在しない
| 項目名 | 値 |
|---|---|
| 勘定科目コード | 150 |
| 勘定科目名 | 売上総利益 |
という項目を自分のテーブルにつくります。
DM_PLというテーブルを参照して、新しい項目をDM_PLに書き込んでいます。
こちらは１つのSQLで年月・シナリオ・組織ごとにつくられます。
ポイントとなるのは下記の部分です。
SUM(DECODE(勘定科目名,'売上',金額,0)
-DECODE(勘定科目名,'売上原価',金額,0)
) AS 金額
DECODEという関数はDr.Sumで死王できるもので、[勘定科目名が'売上'のレコードは金額の項目を持ってきて、なければ0を入れる]という意味です。
ここで勘定科目[売上総利益]は勘定科目[売上]の金額の合計を勘定科目[売上原価]の金額の合計で引き算せよ、としています。
このSQLを下図の黄色編みかけの数だけ用意してあげる必要があります。
さらに、さきほどの[売上総利益]のように濃い黄色編みかけの計算式は薄い黄色編みかけの項目を使用します。
[売上]、[売上原価]のSQLを流す前に[売上総利益]のSQLを流すと空振りに終わってしまいます。
そのため、SQLを流す順番は
１.薄い黄色編みかけ群のSQL
２.濃い黄色編みかけ群のSQL
としましょう。
このSQLをデータ更新のたびに流してあげればOKです。
実案件での経験を書きます。 実際のSQLはこちらになります。 先程の説明のときに使用したSQLとは異なり、項目名が英語だったり、合計の指定も勘定科目コードでおこなっています。【さらに細かく👉】実際の本番案件で書いたSQLはこちら
ぼくの担当したお客様のPL表はもう少し複雑なもので、SQLが合計37本になりました。
INSERT INTO DM_PL(
SELECT
FY_Label,
FY_Name,
SCENARIO_Label,
SCENARIO_Name,
PERIOD_Label,
PERIOD_Name,
D001_Label,
D001_Name,
'153' AS ACCOUNT_Label,
'給料等' AS ACCOUNT_Name,
SUM(DECODE(ACCOUNT_Label,'801000',Value,0)
+DECODE(ACCOUNT_Label,'802000',Value,0)
+DECODE(ACCOUNT_Label,'803000',Value,0)
+DECODE(ACCOUNT_Label,'804000',Value,0)
+DECODE(ACCOUNT_Label,'808010',Value,0)
+DECODE(ACCOUNT_Label,'808020',Value,0)
+DECODE(ACCOUNT_Label,'809010',Value,0)
+DECODE(ACCOUNT_Label,'809020',Value,0)
) AS Value
FROM DM_PL
GROUP BY
FY_Label,
FY_Name,
SCENARIO_Label,
SCENARIO_Name,
PERIOD_Label,
PERIOD_Name,
D001_Label,
D001_Name,
'153',
'給料等'
)
;
バッチやETLツールをつかって、このSQL37本を順番に動かします。
可視化をする
ここまででデータが準備できたら、あとは可視化をするだけとなります。
ここまで下準備ができていたら、どのツールでも基本的にはできるんじゃないかなと思います。
しかし、ユーザーの痒い所に手が届く場合は、ツールが限定されます。
ここではぼくの一押しツールである、MotionBoardで実行してみます。
ダッシュボードとしての完成度の高さはMotionBoardが格別です。
ということで、Motionboardで完成した画面がこちらです。
画面に大きく出ているPL表を様々な軸で切り替え、フィルタリングをする機能を設けております。
こちらのダッシュボードの見方について、解説させていただきます。
画面いっぱいの集計表
MotionBoardは集計表の行単位でフォントや背景色を変更することが可能なので、上記で説明した科目大分類や中分類は青や水色にして、目立たせます。
集計表の一番下に[合計]や[総合計]をつけてはいけません。
列の合計はOKですが、PL表の場合の行の合計はNGとなります。
この集計表をメインとして、下記の機能で様々に切り替えてデータを分析することになります。
時系列軸、組織軸を切り替える
MotionBoardはボタンをつかって、集計表の軸を変更することができます。
下図のように集計表の列項目を切り替えて使用できます。
また細かな制御ですが、[組織別]のときだけ、画面左の検索メニューの中に[月度]フィルターが登場します。
組織ごとに情報を見る場合は、単月・年単位などの時系列で切り替えることが可能です。
画面右上の黄色いボタンは、集計軸の切り替えです。
会計システムのPL表には[シナリオ]という概念があり、実績値のデータなのか予算のデータなのかを切り替えることができます。
シナリオや年度で切り替えて実績と比較をする
シナリオは[一次予算]、[二次予算]、[修正予算]、[見込み]のように、期が進むにつれて、確度の高い予算が入ります。
これらの予算たちは実績を比較する必要があります。
画面右上の黄色いボタンたちが集計表を切り替えるボタンです。
左から
①[実績]
②[シナリオ変動]
③[対シナリオ]
④[対年度]
としています。
③の対シナリオは①-②の値が入るので、実績との予算比較になります。
④の対年度はシナリオ[実績]の年度比較です。
それぞれ左のメニューのコンボボックスで比較したいデータを選択します。
そして②[シナリオ変動]は[一次予算]、[二次予算]、[修正予算]、[見込み]などを切り替えて表示されます。
黄色いボタンの左から2番目をご注目ください。
[一次予算]だったボタンが[修正予算]に切り替わる様子が下図で分かります。
つまり、本ダッシュボードは実績や予算を自在に切り替え、年度や組織の軸で分析ができる構成となっております。
会計の帳票をここまで柔軟に切り替えられるのはMotionBoardの強みだと思います。
最後に
BIツールはデータを集計することを得意とします。
しかし、会計システムの場合は単純なデータの集計だけで、見たい情報にたどり着かない場合が多いです。
そこを解決するために、データベースをつかって下準備をしたり、高度な表現ができるBIツールで可視化を行うことになります。
PL表の表現で悩む方々に、解決のヒントとなれば幸いです。