Excelワークも怖くない！モデリングの考え方（概要編）
どうも。かすてらこんぶです。
本記事では、モデリングについて解説したいと思います。コンサルタントとして、デューディリジェンスの際のPL作成は元より、事業性評価の際の市場・売上予測や、組織戦略プロジェクトにおける人員推移計算等、Excelを使ってモデルを作る、という業務は意外と多いものです。
経験が無いと躊躇してしまいがちなものですが、動じず・ミス無く・効率的にモデリングをこなす上での基本的な考え方やTipsについて本記事では記載したいと思います。
- モデルとは何か？モデリングを行う意義
- モデルの粒度／構造設定：位置付け・重要性・実用性で決める
- パラメータシート：意味のあるものの絞込み・検証方法の担保
- 実際の数値埋め：既存データとの接続・ダブルチェックを意識
- ベースケースの時間軸比較・アップサイド／ダウンサイドとのケース間比較
- まとめ・陥りがちな罠
モデルとは何か？モデリングを行う意義
モデリングとは何か。当然単なる数値遊びではありません。定性的に分析した競争環境や市場環境は、そのままでは意味は無く、経営者はじめクライアントが気にかけるのは、自社（或いは買収先）の売上／利益にどの程度影響があるのか？という点につきます。
10P使って説明した大層な情報や示唆が、実際の数値で捉えると売上の数％しか無いような話だったり、或いは見落とした情報が実は売上に大きなインパクトを与えたり…定性的情報だけではこの辺りの感覚が自他共に持てないままです。
然るに、定性的な示唆を定量的な意味のある数値に変換する、それがモデルの意義だと思っています。また、逆に数値に落とし込む事によって生まれる示唆もあるでしょう。極論ですが、コンサルの仕事は定性的な示唆抽出だけでは片手落ちだと言うことです。
モデルを構造的に考えた場合、その中には何が含まれているべきでしょうか。
一般的にDD時に作成する様なプロジェクションモデルは以下の構成が多いと思います。
（細かい用語はいずれも後述します）
- インストラクションシート
- モデルの構造や更新履歴、数値の色等の基本説明を記載
- モデルはプロジェクト中に、バージョン100を超えること等ざらなので、履歴をきちんと記録しておくことはとても重要
- サマリーシート
- ベースケース／アップサイドケース／ダウンサイドケース別のアウトプット・KPIの比較等
- クライアントが最も欲する数値のみを後ろのシートから抽出して比較するシート
- P/Lシート
- パラメータシート
- モデルの肝。P/L上の各数値に影響する重要なパラメータのリスト（複数ケース作る場合はケース別）、予測根拠やソースが記載されているシート
- 特に複数ケース作る場合は、各ケースのパラメータを併記し、ケースを切り替えるシナリオスイッチを置く場合も
- 計算シート
- パラメータシートを元に、”売上”や”人件費”等の個別アウトプットを計算するシート
- 重要な費目は個別シート、そうでない費目（寄与度の小さい固定費等）は纏めて1シートに記載するケースが多い
- その他参考シート
既に食傷気味になりそうですね…。実際にプロジェクトでもExcelを回すモデル担当は、その他検証には回らず専任でつける事が多いです。クライアント側のモデル担当との折衝や、各モジュールのアウトプットの組込等、相当な負荷がかかる役割なので、ただのExcelワークと侮らない方が良いと思います。
以下では、作成ステップにおいて特に重要な部分を実際のモデル作成物の画像も交えながら、紹介したいと思います。
モデルの粒度／構造設定：位置付け・重要性・実用性で決める
貴方がプロジェクトアサイン初日、モデル担当にアサインされたとします。まず、何をすべきなのでしょう。先ずやるべきは、モデルの粒度・構造を検討する事です。
当然将来予測は過去のデータとの連続性を持つべきなので、私はクライアントの過去のP/Lをまず見ます（新規事業や製品の場合は過去のP/Lなどありませんが、いずれにせよ既存の市場構造との繋がりは持つべきなので、その意味では同様です）。
例を挙げると、売上／利益を構成しているのはどういった事業・製品なのか。その総数はどの程度か。現状の売上／利益への貢献度はどの程度か。過去から現在にかけてそれはどう変遷しているか、事業によって仔細データがどの程度あるか…という視点ですね。
そうすると、主体を支えてきた中核事業、過去から現在まで傍流として存在しあまり変動が無い小粒事業、新規に拡大or既存で縮小している事業等、各々の性質が見えてきます。
その上で、位置付け・重要性・実用性を勘案し、モデルの粒度・構造にアタリを付けていきます。
- 位置付け
- そもそもそのモデルはどの様に活用されるのか。新規事業の大枠の5年後のサイズが見たいのか、コストカットプロジェクトで詳細な予測をしたいのか 等
- これにより、どの程度精緻に構造設計をすべきかが規定。例えば前者の様な位置付けのモデルであれば、ある程度枝葉の事業は丸めてプロジェクションをすることで、本質的に重要な事業の検証粒度を細かく出来る
- 重要性
- 各事業の売上・利益貢献度やその変動、クライアントの成長戦略等から、重要な事業・そうでない事業を峻別
- 上記とも関連するが、クライアント・コンサル双方にとって意味のある部分に絞って深く検証を行う上で有用
- 実用性
- コンサルがアウトプットした数値をインプットとして使って終わりなのか、それともそのモデルを今後5年10年運用していくのか
- 後者の場合、より丁寧にクライアント内での運用を意識した構造にする必要
（例えばモデル上は何とか組めるものの、クライアント内で定期的に取得するのが難しいデータは回避してプロジェクションする 等）
ここで重要なのは、この大枠が設計された段階でクライアントとそれを握るという事です。モデルを後から抽象化することは比較的まだ容易ですが、粗く予測したものを細かくするのには非常に労力を要する場合が多いです。
また、検証の濃淡を事前に握ることで、クライアントとしても真に意味のある検証にコンサルタントを使役する事が出来るので、本当に優秀なクライアントはこの見極めをかなり能動的にやってきます。
以下は、例えばあるプロジェクトのP/L構造・粒度の一例です。各国毎に展開製品が異なり、国・製品によって売上・利益貢献度の重みが違う為、どこを細かく見るか・どこを粗く見るかを事前に規定しています（日本の製品Aを最も細かく、ブラジルを最も粗く見る形です）。
先程の3つの観点は以下の様に踏まえられています。
- 位置付け：ファンドの買収検討の為のモデルであり、5年後の対象会社の大枠のEBITDAを知る事が目的
- 重要性：地域別では日本・製品別では製品Aが最も重要なセグメント。他方、中国やブラジル、製品Cといったセグメントは売上・利益貢献度共に小さく、日本や製品Aに比べると枝葉
- 実用性：クライアントがM&Aを実施した後も、PMIでモデルを活用する為、日本以外の国においてはデータ粒度を無理に細かくせず、実際の運用で取得出来るレベルに留めた（売上数量まで見ずに金額ベースで分析 等）
勿論プロジェクトによって見る対象は変わりますが、そもそもそのモデルが何の為のモデルなのか、数値オナニーにならない粒度はどの程度なのか、その上でどこに絞って細かい検証を行うのか、は必ず重要になってくるので、留意頂ければと思います。
パラメータシート：意味のあるものの絞込み・検証方法の担保
無事モデル設計の大枠が決まってクライアントと合意したとしましょう。次に考えるべきはパラメータです。よくフェルミ推定なんかでやる、売上=客数✕客単価、人件費=人数✕人員単価…とほぼ同じ形でP/Lの費目を分解していきます。
この際重要なポイントは、意味のあるものの層別・検証アプローチの並立です。
- 意味のあるパラメータの層別
- 極論で言えば、意味の無いパラメータは存在しません。但し、コンサルティングのプロジェクトは時間が限られており、特にDD等ではクライアントにとってもそれは同じです。限られた時間・リソースの中で、各パラメータの価値が等価では無いことはご理解頂けると思います
- 以下の視点を持ってパラメータを一覧し、重要なものをピックアップします
- 重要性が高いものは、より細かく分解するか、検証精度を高める一方、重要性が低いものは、検証粒度を緩くします。よく取られるのは、過去の数値やトレンドを横置きする、単純な市場調査レポートの数値に合わせる 等です
- 検証アプローチの並立
- パラメータは、今後の妥当な予測値が立てれてナンボです。市場レポートやインタビュー、競合の数値からの推定等、いずれかの手段で検証するイメージまで併せて持って置かなければなりません
- さもなくば、プロジェクト佳境になって初めて、”あれ？これ将来どう予測するんだっけ？”という地獄を迎える可能性もあります
以下は、先程の例における製品AのP/Lを作る上でのパラメータ構造の大枠です。
（1枚目：全体像、2枚目：個別パラメータの予測アプローチ例）
要素分解自体は極めてオーソドックスに実施し、各パラメータの予測アプローチは手に入るデータや重要性毎に濃淡を付けています。
以下例で言うと、部品あたりA個数はあまり検証努力を割いていない一方、顧客内の自社シェアの推計を重点的に行っている事が分かるかと思います。
実際の数値埋め：既存データとの接続・ダブルチェックを意識
ここまででモデルの大枠設計やパラメータの考え方がある程度固まりました。後は、定めた枠に基づき数値を埋めていく作業です。モデリングにおいて、先に手を動かすのはNGで、この様にまず設計をしっかり担保してから作業に入るのが肝です。
数値を埋めていく際は、まず足許のデータ（過去から現状まで）をミスなく埋めていく事が重要です。会社によっては、地域毎・製品毎にデータ・フォーマットが異なる場合もあるので、横着せず共通化できる形に統合していきます。
以下、別プロジェクト（新規上市医薬品の需要予測）の計算シートの事例ですが、市場にまつわるデータ・実際の販売データ共に、先ずは確定している過去のデータをミスなく埋めていきます。（入っている数値はてきとうです）
この裏にパラメータシートがある訳ですが、重要なのは、実績数値と裏にあるパラメータが紐付いていることです。
理論的に正しく分解出来ているなら当然なのですが、過去の売上は、過去の金額市場規模と過去のシェアを掛け合わせた結果とイコールでなければなりません。将来予測の売上だけ、市場規模✕シェアの結果でアウトプットされていたとしても、過去もその整合が取れていないと、過去・将来のパラメータで大きく乖離が出てしまいます（＝過去とのデータの接続性が無い）
ここがイコールにならないとすると、パラメータ分解の式或いは入っている数値に誤りがあることになりますので、注意したい部分です。
またもう1点重要なのが、計算結果のダブルチェックです。
例えば全社売上という費目を考えると、当然ですが以下の2通りの計算結果がイコールである必要があります。
- 全地域の売上合計
- 全製品の売上合計
当たり前に同一値になるべきなのですが、参照元の違いや参照セル・計算記号のミス等で、結果がずれることがあります。そういったミスに気づく為に、P/L下部に、ダブルチェックの為の計算式を忍ばせておく方がベターです。
以下はコストサイドのダブルチェックの為の一例です。2通りの計算をした同一コストの差分が、ほぼ0で無い場合にFALSE表示を出す様にしています（IF式で=を条件にしても良いのですが、Excelは微細小数点計算の関係で誤差が出る場合もあるので、差分がほぼ0、という条件の方が良いです）。
ミスを防ぐ仕組みを担保すれば後は将来に渡ってデータを埋めていくだけです。パラメータ設定が甘いとアウトプットとしての売上やコストが異常な数値になる場合があるので、適宜過去～将来の数値変動をグラフ化する・対前年成長率等の連続性を確認する行を設ける 等、リアリティを確認する様にしましょう。
ベースケースの時間軸比較・アップサイド／ダウンサイドとのケース間比較
将来数値まで埋めて感覚的にも妥当だと思えるベースケースが出来れば、完成までは後一歩です。
よくDDで求められるのは、アップサイドケース・ダウンサイドケース作成です。アップサイドケースとは、自助努力や追い風の環境変化等で、ベースの予測より事業が上向きになるケース、ダウンサイドケースはその逆と捉えて頂ければ間違いないでしょう。
どのパラメータにアップサイド／ダウンサイドを設けるか・それぞれどの程度上振れ／下振れを見込むかは、パラメータ予測の定性分析結果やクライアントの意向に依る部分が大きい為、何とも言い難いですが、基本的には変動可能性・変動した際のインパクトが大きいものが選択されます。
感度分析という文脈で用いられますが、パラメータの数値をアナログで弄ってみて、結果にどの程度のインパクトがあるものなのかを確認する作業はその意味でも重要です。
実際にそれらが決まったら、パラメータシート上で、ベースケースのパラメータと併記して切替が出来る様にしておきます。よくケーススイッチと呼ばれるものは、Excel上のChoose関数を利用し、ケース番号（ベースケース：1、アップサイド：2、ダウンサイド、3等）に対応するパラメータを自動で採用する機構にしておくものです（下図参照。K6セル上の番号によって、将来シェアの採用数値を切替）。
結果としての数値は、Excel上で一手間かけてグラフにしておくと、パラメータをいじった際の変動が視覚的に理解しやすいのでオススメです。
以上、めでたくベース・アップサイド・ダウンサイドのP/Lが完成しました（？）
数値は数値のままでは無意味というのは冒頭申し上げた通りで、これらを比較する必要が生じます。
具体的には、以下の観点の分析をExcelやPPTに盛り込むのが常道です。
- ベースケースの時間軸比較
- 現在年とX+5年後の売上・利益はどう変わるか。変動の主な要因は何か
- 例えば全社の売上・利益推移のグラフ、一方で事業別・製品別の増減を示すウォーターフォールグラフ等が考えられますね
- ベース・アップサイド・ダウンサイドケースの比較
- 各ケースで売上・利益はどの程度変わりうるのか、その差分を有む要因は何か
- 例えば売上・利益のケース間比較グラフ、パラメータ別の売上・利益変動への貢献度を示すグラフ等が考えられますね
- パラメータ別貢献度の分解は、精緻にやる上では、共分散を用いてちと面倒な分析をやる必要があります（例えば客単価・客数共にケースで変動したとして、それぞれの利益寄与を分解して考える 等）
以下はExcel上でダウンサイドケースのベースケースとの差分を説明したものになります。
（下落要因それぞれの地域別影響度を売上・EBITDAに分解して説明。例えば中国では、人件費単価が上昇するも、事業自体が低迷=人員が削減される為、Overallの影響が単純な各要素の総和にはならない事に注意）
まとめ・陥りがちな罠
ここまで、モデリングの考え方・大枠のプロセスを説明しました。
モデリングの経験は誰しも最初は無い状態でスタートしますので、大枠を掴んでおく・おかないでは効率やクオリティが多少は変わってくるのではないかと思います。
特に、私が経験した失敗の中で、誰しも陥りがちな罠は以下です。
- 考える前に手を動かす
- Excel自体、頭を使わずとも手を動かそうと思えば動かせるツールなので、モデルの構造やパラメータ検討が甘いまま突っ走ってしまうケースがあります
- 結果、プロジェクト後半に大規模な修正を余儀なくされ徹夜するケースも…
- 将来予測に目が向きすぎて、過去との整合性が取れていない
- 将来の数値を出すのがゴールなのですが、それは過去からのリアリティのある連続性があって、という条件付きです
- 過去数値がパラメータと結びつかない・パラメータが過去の変動から比べても異常な動きをしている、等では、折角汗水流して作ったモデルの信頼性が損なわれてしまいます
- 検証の濃淡が付いていない
- 複雑なモデルがカッコイイ（笑）と思ってしまい、のべつまくなしに細かく作る
- 結果、些末なポイントを深掘りせざるを得ない一方、本当に重要な論点・パラメータの検証が甘くなり、時間の割に質が伴わないモデルに
尚、Excelの基礎的な動作やお作法的部分については、以下書籍が非常によく纏まっている為、ご参考頂ければと思います。
読了、ありがとうございました。
