「くそ…緊急事態宣言のやつめ…、せっかく今期の予算作ったのに、また改定する必要があるじゃねえか…」
この記事を読まれる方の中には、心の奥底ではこんな心境に陥っていることになった経営企画担当者は少なくないはずです笑。
皆さん、こんにちは。OWNDAYS（破天荒フェニックスという本で少ーし有名になった会社）というメガネチェーンで経営企画を担当しています、安田です。
OWNDAYSの経営企画室が担うミッションは、経営管理、取締役会・経営会議の運営、特命案件対応（M&A、社長直下プロジェクト等）など。経営管理の一つである予算作成にあたっては、世界12か国(=12通貨)、400店舗、直営・JV・フランチャイズ（商品の卸売りとロイヤリティが収益源）という3つの事業モデルを取りまとめるいう感じです。小売業は店舗の数だけPLがあるので、その積み上げが大変なのですが、下記のツイートのように現在は全てExcelを使ってゴリゴリやってる感じです笑。
過去にいくつか予算作成に関する記事を書いてきましたが（本記事の最後にリンクを用意したのでご覧ください）、今回は「Excel実践編」として、勘定奉行の「部門集計表」を管理会計フォーマットに再集計する具体的な方法をお伝えします。
予算で大切なのは実績値。Excelでの集計方法をご紹介
コロナのおかげで、経営企画をご担当されているほぼ全員の耳にタコができる程聞いた「予算」という言葉（笑）。皆さん、どのような方法で予算作成されているでしょうか？
その予算の作成で最も大事なもの。それは「過去実績の把握」だと個人的には思います。予算設定の妥当性を見るには実績が最も客観的かつ正確なデータ。翌年の成長率を過去5年間の平均成長率を加味して設定する、それに留まらず想いを乗せて利益目標をストレッチさせる、非連続的な売上成長を目指す、といったことはあって然るべきなのですが、その土台となるのは、やはり実績となります。なので、実績値の把握が予算作成においてとても重要になります。
「あのプロダクトの時、アポ100件に対して成約率は5件、つまり成約率は5%だったよね。だから、今回も5%を目指そう」または「いや、今回は10%を目指すんだ！」といったように、KPI設定や売上・利益目標の参考にはなるわけです。
なので、実績把握というのは、妥当な予算を作るうえで基本の土台となる最も大事なことの一つで、それを効率的・効果的に集計しておくことはとても重要なのですよね。
というわけで、本記事では上記のお悩みに応えるべく、Excelを使った実績値集計の一例をご紹介して参ります。実績値を集計するにあたり、元データとなるのが財務会計ソフトからの出力データになるわけですが、今回は勘定奉行の部門集計表を例にお伝えしていきたいと思います。
勘定奉行は使いづらい？
さて、その実績把握のための集計。実際どうやるのが良いのだろう…と思われた方々は、次のような悩みがあったりしませんか？
・部門集計表のPL科目が細かすぎて全体感が掴みづらい
・部門別に効率的にまとめたい
・部門別に月次集計を月ごとの推移でも追いかけたい
勘定奉行は財務会計としての利用が主軸にあるため、予算作成をする際に必ずしも適していないかもしれませｎ。部門集計表だと、管理会計目的の仕様になっていないため細かすぎて全体感が掴みづらいことがあります。
勘定奉行から出力されたファイルを初めて見たときは、「結局、人件費に類する費用って各部門ごとにいくらだっけ？」「諸々の一般管理費は、総額で毎月いくら払ってるの？」などの問いに答えられず、細かすぎて全体を捉えづらいなと感じた記憶があります。
これは勘定奉行が悪いとか使いづらいというわけではなく、単に「実績を経理目線で仕訳した結果」であり、目的が異なるためです。経営企画が文句を言えるものではありません笑。
そういう時の解決方法、つまり予算作成を目的とした実績の集計方法をここから紹介します。
部門集計表をもとに集計する
管理会計目的に実績推移を把握できる帳票を作るにあたり、２段階に分けてご説明します。初めに部門集計表をもとに集計する方法をご説明します。
まずは科目ごとにマッピング
まず、勘定奉行から月次の部門集計表を抽出します。この集計表では、予め設定された科目や部門、指定期間に応じて、以下のような集計表がダウンロードされます。
ただし、このままだと細かすぎますよね？「結局、人件費って総額いくら？」と経営陣に聞かれたときに、「えぇーーっと…」となってしまい即答できません。
そこで、いきなり一本一本の木を見るような表を使うと、森全体を見渡せなくなるため、それを合算して科目数を減らす処理をします。
そのための下準備がマッピングです。
マッピングとは、細かな科目を以下のような科目へ事前にグループ分けしておく作業のことです。これらは一例で、ご自身の事業に合わせて欲しい項目を用意いただければ問題ありません。
売上高
売上原価
売上総利益
人件費
地代家賃
減価償却費
旅費交通費
広告宣伝費
販売促進費
支払手数料
販管費合計
といっても全然難しくありません。具体的には、A列に一列追加し（追加行であることを明示するため色でハイライト）、各科目に科目グループ名をつける。たったこれだけで完成です。
Sumif関数で集計
各科目のマッピングが完成したら、次はこれを集計するだけ。エクセル初心者であっても多くの方がご存じの「SUMIF関数」を活用し、これらの科目グループ毎に集計します。
具体的には、緑ハイライトしたセルの通り、部門集計表の下へ科目グループ名を任意の順に記載し、それぞれをSUMIF関数で部門ごとに集計するのです。
はい、たったこれだけで完成です。
計算エラーチェック
ここでちょっとしたTipsを二つご紹介します。
「ちょっとした」といっても、Excelの計算の際は必ずやってほしいことをお伝えします。
SUMIFを活用するのは良いのですが、よくあるのが計算エラー。集計の参照先が誤っていたり、科目グループの記載が漏れていたり、あとで見返すと「あっ….計算ミスってる…」と気づく。みたいな経験は、経営企画の皆さんであれば必ず一度はあるんじゃないかなと思います笑。
そんなエラーを可能な限り防ぐために、私は次の工夫をしています。
二つの方法で営業利益を算出してチェック
一つは、「売上総利益と販管費合計、営業利益はSUMIFで計算しない」です。
具体的には、これらの数字については、緑で集計された数字を使って個別に算出しています。例えば、売上総利益は「売上高 – 売上原価」、販管費合計は「人件費～その他販管費のSUM」、営業利益は「売上総利益 – 販管費合計」で計算するようにしています。（これらの箇所は緑ハイライトしていません）
SUMIFで集計された数字をもとに算出することで、そこでエラーがあれば異常値を発見できることがあります。
True/Falseを使ってチェック
もう一つの工夫が、「TRUE/FALSE」です。
具体的には次の通り二つのセルを「 = 」で結び、これらの数字が同じであれば「TRUE」、異なれば「FALSE」を返してくれる機能です。
これをご覧になっていただくと、一目瞭然です。元データの営業利益（43行目）とSUMIF集計から計算した営業利益（69行目）の数字が異なるため、全てFALSEと出てしまいました。これは集計ミスです…。
どこにミスがあったか探してみると、「支払手数料」という項目の集計が出来ていなかったということが分かりました。それによって、43行目と69行目の営業利益に差異が出たのです。
そこで、販売促進費（66行目）とその他販管費（67行目）の間に「支払手数料」を追加して再集計したのがこちら（黄色ハイライトの通り）。全て営業利益が一致し、全て「TRUE」と表示されました。
これだけで全てのミスを絶対に防げるというわけではないですが、基本的な処理として必ず計算Checkを入れるようにすれば大きなミスは防げるのではと思います。
実はこれ、私はこの計算ミスをコラムのために意図したわけではなく、TRUE/FALSEを使ってみて自身のミスに気づいたわけです笑。自分で言うのもなんですが、やっぱり意味があるなぁと個人的に実感しました…。
おさらい
以上をまとめると、次の通りです。
１）木ではなく森を見れるよう、科目グループを作る
２）一列挿入して全ての科目をグループ分けする
３）そのグループ毎にSUMIFで集計する
４）計算ミス防止のため、売上総利益や営業利益は個別計算する
５）TRUE/FALSEで計算Checkする
これで各月の集計は完了です。やってることはSUMIF関数だけなので、慣れればとてもスピーディにできる方法です！
部門ごとにPL推移を作成
ここまで各月の集計方法をご紹介しましたが、これで完成ではありません。
なぜかというと「月ごとの数字は分かったけど、月次でどんな風に推移してる？売上に対して販管費増えてない？」といった経営陣からの問いにぱっと答えを出しにくいからです。
それを踏まえて、これらを月次で追いかけやすくする方法をお伝えします。
別シートに行列入替で並べ替え
結論からお伝えすると、こんな表を別シートに用意します。
１）部門ごとにPLブロックを作成し立てに並べる
２）そして、時系列で推移を見れるよう、横方向へ年月を並べます。
３）必要であれば、利益率や対売上費用率（例えば人件費率や広告宣伝費率）も追加します。
これにて、月次で推移を追っかけるための土台が整いました。
※こちらのスクリーンキャプチャーは営業Aと営業Bしか見えていませんが、経理、人事、総務、そして合計と部門集計表と同じ部門を並べます。
月別にIndex関数&Match関数で参照
土台ができれば、あとは別シートで集計した数字をこの推移表に持ってくるだけ。
そこで使用する関数は、Index関数とMatch関数です。実はこの関数たち、毎年の予算ファイル作成作業で山ほど使用しました笑。列参照も行参照もできますし、さらには（というかこれがIndexの使い道ですが）クロス参照ができます。
さて、前置きが長くなってしまいましたが、Index関数とMatch関数を使って、どのように数字を引っ張ってくるかというと、次をご覧ください。
Index関数で前述の集計箇所を参照します。
そして、PL科目を行参照、部門を列参照としてIndex関数を組むと、このように各部門ごとのPLブロックができあがりました。これを毎月集計していければ、月次部門別PLの推移表が出来上がります。
※「202004」は部門集計表を科目グループ毎に集計をしたシート名
関数そのものの詳細説明は省略しますが、「経営企画へ配属されましたー！」という方々は、SUMIFの次にこれを覚えておくだけでかなりパワーアップすると思います笑。
そして、4.3で紹介したような計算Checkもこの推移表に活用すれば、計算ミス・エラーも未然に一定程度防げるのでより安心ですね。
参考ツイート↓↓↓
まとめ
まとめです。今回のコラムは次のような課題に取り組みました。
・部門集計表のPL科目が細かすぎて全体感が掴みづらい
・部門別に効率的にまとめたい
・部門別に月次集計を月ごとの推移でも追いかけたい
そして、これらの解決方法として以下の手段をご紹介しました。
１）部門集計表上で科目ごとにマッピングする
２）SUMIF関数で集計する
３）計算ミス防止のため、計算Checkをする
４）別シートに行列入替で並べ替える（部門毎の月次推移を追いやすくする）
５）月別にIndex関数＆Match関数で参照する
これを用いれば、まずは森を見やすくなりますし、推移を追っていきやすくなります。この方法で毎月更新していければ、実績の把握が容易になりますし、それをもとに将来の画を描き易くなるのではないでしょうか？
一方で、Excelに頼らずとも、やる方法は当然あります。
例えば、例に挙げた勘定奉行は、管理会計オプション（https://www.obc.co.jp/bugyo/option/kanri）もあるとのこと、やはり各社さんともに、管理会計に使用できるモジュールなどをご用意されています。Excelを使うとどうしても作業負担や属人化の傾向が高まりますから、うまくこういうツールを活用するほうが良いとは思います！
（ここまで集計方法を紹介しておいて、なんやねんというツッコミはお控えください笑）
参考となる過去の記事
予算作成やExcel活用に関しては、過去にいくつか記事を書いてます。こちらもぜひご参考いただき、日々の業務にお役立てくださいませ！
ではでは～！！
コメント