こんにちは！
今回は「シミュレーションモデルの基本的な考え方・組み方」をご紹介します。
以前、分析のパターンとして、
- （単純な）比較
- 分類
- 予測
の３つをご紹介しましたが、この「予測」に当たる分析手法になります。
シミュレーションモデルの考え方を理解すると、一段深い仕事ができるようになると思いますので、ぜひ参考にしていただければ。
では、早速行きましょう！
シミュレーションとは
シミュレーションは、Excelなどを使って、将来的な結果を予測する分析手法です。
シミュレーションをする目的としては、
このままいったら、3年後はどういった数字になるだろうか？
という「結果を予測する」狙いもあれば、
理想的な結果に到達するためには、どういった要素が必要になるか？
という「必要な要素を逆算する」狙いもあります。
前者はイメージしやすいと思いますが、後者の具体例としては、
新会社設立にあたり、資本金をいくら積んでおけば、資金繰りが安定しそうか？
といったことをシミュレーションで計算するイメージです。
シミュレーションの流れ
シミュレーション作成の大きな流れとしては、
- 目的からシミュレーションの範囲・領域を考える
- 計算式を考える
- 変数を設定していく
- Excelでシミュレーションモデルを組んでいく
- 組んだシミュレーションモデルを動かしながら検討していく
という感じになります。
それぞれ解説していきます。
なお、シミュレーションのコツは、
はじめは、ざっくりと荒いシミュレーションモデルを組む！
そして、それをどんどん修正し、細かくしていく！
とすることです。
実際の実務においては、上記のシミュレーション作成のプロセスが1サイクルで終わることはありません。
「作っては直し、さらに作っては直し」と修正を繰り返すことになります。
目的からシミュレーションの範囲・領域を考える
まず、「目的からシミュレーションの範囲・領域を考える」ですが、シミュレーションの目的を踏まえ、シミュレーションの前提を整理していきましょう。
具体的には、
- シミュレーションの対象は何か
（例：PLだけでいいか、それともBS・CFまで含めた財務三表全体か） - どこまでの範囲を計算式に入れるか
（例：薄型液晶テレビ全体をやるか、それとも有機ELテレビに絞ってやるか） - どこまで予測するか
（例：1年後まで予測するか、それとも3年後まで予測するか） - 予測の粒度はどうするか
（例：「年単位でやるか、日単位でやるか」や、「一万円単位やるか、一円単位でやるか」）
といったことを考えていきます。
計算式を考える
次に、「計算式を考える」ですが、これはシミュレーションの設計図を書くステップです。
いきなりエクセルを触らず、
どういった数字を使って、どう演算していくのか？
と考え、紙に落としていきます。
ここでのポイントは、「変数は、なるべく他の変数と連動させるようにする」というものです。
例えば、お店の利益をシミュレーションするためには、当然まずは売上と費用を算出する必要がありますが、この時、
売上高の規模が増えるにつれて、従業員の数も増やさなければいけないだろう！
売上高と人件費が比例するような形で設計しよう！
といった感じで計算式を組んでいくことになります。
変数を設定していく
「変数を設定していく」のステップでは、計算式に出てくる変数にどういった数字を割り当てるかを検討していきます。
ここでは、前述の通り「他の変数と連動させる」というときもあれば、「固定値を置く」というときもあります。
例えば、毎年の「租税公課」の額を計算するときは、
租税公課 ＝ 税引前利益額 × 税率
といった感じで、「税引前利益額」と連動させることになりますが、「税率」の方は政府から公表されてますので、
税率 = 30%
みたいな感じで固定値を置いていくことになります。
また、“色々試す” という「仮定値」の変数を置いてもいいですね。
例えば、
新会社設立にあたり、資本金をいくら積んでおけば、資金繰りが安定しそうか？
といったことをシミュレーションする場合は、「資本金」の欄は仮定値に設定した上で、グリグリと数字を変えながらシミュレーション結果を何度も算出し、
資本金を1億円にしたら、半年後に資金ショートしてしまう！
一方、資本金を1.5億円にしたら、資金繰りで問題はなさそうだ！
といったことを確認することになります。
Excelでシミュレーションモデルを組んでいく
シミュレーションの計算式の「設計図」と、変数に入れ込む「数値」が揃ったら、実際にExcelでシミュレーションモデルを組んでいくことになります。
この時のポイントは、
- 計算式は極力シンプルにする
- １つの行には同じ計算式を入れる
の２つです。
それぞれ見ていきましょう。
Excelの操作については、以前記事を作成しているので、ぜひご参考にしていただければ。
計算式は極力シンプルにする
まず、セルに入力する計算式は極力シンプルにしましょう。
計算式を分割できる時は、極力セルを分割して計算するようにします。
理由は３つあります。
まず、セルに入力された計算式が複雑になると、計算ミスを引き起こしやすいからです。
また、セルに入力された計算式が複雑になると、計算ミスを修正しにくくなります。
計算ミスがあることに気づいても、セルに入力された計算式が複雑だと、
どこを修正すれば良いかが全然検討つかないな…
と悩むことになります。
さらに、シミュレーションモデルは、作成者だけが使うものではありません。
作成者の後任も使いますし、上司も使うでしょう。
作成者が休んだら、あなたが代打でシミュレーションモデルを触る役割になるかもしれません。
急遽触ることになったシミュレーションモデルが「セルに複雑な計算式が入力されたやつ」の場合、
どういう仕組みになっているか全然分からねぇ…
と吐きそうになりながら対応することになります。
では、具体的にどのようにExcelを組めば良いでしょうか。
「当月の製品販売数」のシミュレーションを使って見ていきましょう。
前提として、
- 「当月の製品販売数」は、基本的に「需要数」と一致する。
- ただし、「需要数 > 在庫数」の場合は、「在庫数」が「当月の製品販売数」の上限になる。
- なお、「需要数」の計算式は、需要数 = 客数 × 購入数
としています。
ここで、「悪い例」のように、一つのセルに、
=MIN( 客数×購入数, 在庫数 )
と、MIN関数、「客数」、「購入数」、「在庫数」といった要素を詰め込み過ぎてはいけません。
そうではなく、「良い例」のように、
- 当月の製品販売数
- 需要数
- 客数
- 購入数
- 在庫数
とセルを分割してシンプルな計算式を入れていきましょう。
こうすることで、
- 計算ミスを防ぐことができ、
- 仮に計算ミスをしてもどこを修正すれば良いか判断しやすく、
- 作業者以外の人も理解しやすい
というシミュレーションモデルを作ることができるようになります。
１つの行には同じ計算式を入れる
１つの行には同じ計算式を入れるようにしましょう。
これも、
- 計算ミスなどを防ぎ、
- 作業者以外の人がシミュレーションモデルを組み替えやすくする
を目的とした工夫です。
以下は、「当月末の在庫数」のシミュレーション例です。
「悪い例」のように、１つの行に関数・演算子や手入力を混ぜてはいけません。
作成者以外の人がこのシミュレーションモデルを修正する時、一部に手入力があると気がつかずに、
この行は、前月の「当月末の在庫数」を参照すれば良いな！
とコピペしてしまい、シミュレーションがグチャグチャになってしまうおそれがあります。
そうではなく、「良い例」のように、１つの行に同じ計算式を入れましょう。
ここでは、「１つの行に関数・演算子や手入力を混ぜない」ための工夫として、「来月」列の左に空欄列（グレーの列）を追加し、
この行は、前月の「当月末の在庫数」を参照すれば良いな！
としても大丈夫なように設計されています（手入力ではなく、赤枠部分を参照することで、自動的に「0」が入るようになっています）。
組んだシミュレーションモデルを動かしながら検討していく
シミュレーションモデルができたら、あとはそれを使って検討を深めていくだけです。
前述した通り、
新会社設立にあたり、資本金をいくら積んでおけば、資金繰りが安定しそうか？
といったことをシミュレーションする場合は、「資本金」の欄は仮定値に設定した上で、グリグリと数字を変えながらシミュレーション結果を何度も算出し、
資本金を1億円にしたら、半年後に資金ショートしてしまう！
一方、資本金を1.5億円にしたら、資金繰りで問題はなさそうだ！
といったことを確認することになります。
また、利益のシミュレーションをするときは、「ストレステスト」というものをやることが多いです。
これは、まずベースとなるシミュレーション結果を出した上で、
想定よりも良い状況で推移した場合、シェアはさらに＋3%、材料費は-5%になる。
想定よりも悪い状況で推移した場合、シェアは-6%、外注費が+8%になる。
といった感じで、良い場合と悪い場合それぞれのシナリオを考え、どこまでなら利益が出て、どこから赤字になるかを確認するものです。
せっかく作ったシミュレーションモデルなので、グリグリいじって検討しまくりましょう！
具体例を見てイメージを深めよう！
シミュレーションについて色々と文章でご紹介してきましたが、
なんとなく分かったけど、ぶっちゃけ具体的なイメージは湧かないな…
と感じている人が多いと思います。
なので以降は、シミュレーションの具体例として、
- ラーメン屋の売上予測
- 今後必要となる保育所数の予測
- 新規開店した居酒屋の資金繰り予測
の３つを取り上げながら解説をしていきたいと思います。
いずれも簡素なシミュレーションですが、イメージ醸成の参考には十分なるかと思います。
では、それぞれ見ていきましょう！
ラーメン屋の売上予測
まずは、ラーメン屋の売上予想のシミュレーションです。
設定としては、
- 上野店と池袋店の２店舗を経営
- 上野店は１号店で客がだいぶ入っているが、最近出した池袋店はまだまだ成長中
- どちらも20席くらいの大きさ
- ラーメンとしては味噌・醤油を販売
- サイドメニューとして餃子とチャーハンを販売
- その他にビールや、トッピングのチャーシューや煮卵を販売
といった感じです。
では、計算式を設計しましょう。
売上高の構成としては、
- 上野店と池袋店の売上を分けて算出する
- 各店舗の売上は、「売上 ＝ 販売数 × 単価」と分解
とした上で、さらに「販売数」を分解しましょう。
分解の候補としては、
- 商材軸：
販売数 ＝ ラーメン販売数 ＋ サイドメニュー販売数 ＋ その他メニューの販売数 - プロセス軸：
販売数 ＝ 商圏人数 × 認知率 × 来店率 × 来店数@１ヶ月 × 購入数 - 供給サイド軸：
販売数 = 席数 × 回転率@1時間 × 営業時間 × 稼働率 × 営業日数＠１ヶ月
といった感じですが、今回は「商材軸」で分解してシミュレーションを組みましょう。
実績値が簡単に取れて、各商材がどれほど成長しているかの数字も確実に取れるからです。
この考え方に沿ってExcelを組むと、以下のようになりました。
※文字が小さくて見づらいという方は、PDFを表示いただければ幸いです。
シミュレーション結果を見ていきましょう。
開店したばかりの池袋店の成長が著しいため、５ヶ月後の１ヶ月売上は、現状より100万円程度大きくなりそうですね。
なお、シミュレーション中で「上野店のラーメン販売数の成長率」が、
- 来月までは、実績どおり8%であるのに対し、
- 再来月以降は0.1%となっている
という点に注意が必要です。
これは、「店の席数＝20席」と考えると、
昼と夜の食事時に捌けるのは、6000食/月が上限だろう
と考えたため、
- 6000食/月までは、現在の成長率の実績値を用い、
- それ以降は、食事時以外の時間帯の販売数を伸ばす必要があり非常に難しいため、成長率は極端に下がるだろう
として成長率を切り替えたことが理由です。
非常に簡素なシミュレーションですが、具体的なイメージは持てたでしょうか？
次の具体例にいきましょう！
今後必要となる保育所数の予測
次に、今後必要となる保育所数の予測シミュレーションです。
設定としては、
- シミュレーション作成者は、市で保育所の設置を担当しており、今後の保育所の増設計画（＆そのための予算要求）を考えるためにシミュレーションを組み立てている
- 今後の住民数の予測値は、別途データがある
- 保育所１つ当たりの「対応可能児童数」のデータも持っている
といった感じです。
では、計算式を設計していきましょう。
まずは市内のエリアを区分けすべきでしょう。
なぜなら、市の北端に保育所を設置したとしても、南端の住民は距離が遠くて利用できないからです。
なので、保育所に物理的に通えるであろう範囲でエリアを切り、そのエリアごとに、
追加で必要な保育所数 = 現状では対応できない児童数 ÷ 対応可能な児童数@1保育所
と計算していくことにしましょう。
この考え方に沿ってExcelを組むと、以下のようになりました。
※文字が小さくて見づらいという方は、PDFを表示いただければ幸いです。
シミュレーション結果を見ていきましょう。
3年後までに、９ヶ所ほど保育所を増設する必要がありそうですね。
内訳を見ると、とにかく乙エリアの児童数の急増が見込まれ、それが保育所の需要を押し上げているようです。
甲エリアへの投下資金を抑え、乙エリアに集中して資金を投下すべきでしょう。
新規開店した居酒屋の資金繰り予測
最後に、新規開店した居酒屋の資金繰りのシミュレーションです。
設定としては、
- 居酒屋の開店を考えている店主が、運転資金をどれだけ積んでおけば分からないので、シミュレーションを組んで検討したがっている
- 資金繰りのシミュレーションなので、細かめの粒度で確認するため、週単位で予測したい
といった感じです。
簡素なシミュレーションではありますが、コロナで飲食店の倒産が増えていますし、どんな感じになっているのかをシミュレーションを通じてイメージしてみましょう。
では、計算式を設計していきましょう。
資金繰りのシミュレーションなので、現金残高を予測すればよく、基本的な計算式は、
現金残高 ＝ 前週の現金残高 + 今週の集金額 – 今週の支出額
となるでしょう。
なお、ここで、
集金額 ≠ 売上額
であることに注意です。
つまり、お客さんはクレジットカードで支払いするパターンも多く、クレジットカード会社から入金があるのは翌月以降になるので、「売上は立っているものの、売上ほど現金は入ってこない」という状況になっていきます。
次に、計算式の中で用いる変数を洗い出し、数字の設定しましょう。
今回は、
- 売上高：
一旦65万円/週としています。
つまり、１日9万円ほどの売上を見込んでいることになります。 - 現金売上：
クレジットカード利用率は70%と考え、売上高の30%が現金で支払われると想定します。 - 売掛金の回収：
クレジットカード会社からの入金のことであり、「前月の売上が、当月の２週目に振り込まれる」という前提を置いています。
また、クレジットカード会社に手数料5%を取られる想定です。 - 材料費：
売上高の35%を想定 - 消耗品費：
売上高の3%を想定 - 人件費：
給与額面35万円ほどの正社員が2名、9万円ほどのアルバイトが３名と考えました。
合計して100万円ほどを想定しています。 - 家賃：
18万円/月を想定し、毎月4週目に支払う想定です。 - 水道光熱費：
13万円/月を想定し、前月分を当月の3週目に支払う想定です。 - 広告費：
10万円/月を想定し、前月分を当月の2週目に支払う想定です。 - 借入金の返済：
2ヶ月目から発生し、毎月3週目に15万円/月を支払う想定です。 - 当初の現金保有額：
1ヶ月の支出見込み（≒255万円）の半分くらいと考え、130万円
としました。
この考え方に沿ってExcelを組むと、以下のようになりました。
※文字が小さくて見づらいという方は、PDFを表示いただければ幸いです。
シミュレーション結果を見てみましょう。
Excel上では計算されていませんが、利益を計算してみると、
- 8週目までの売上（≠集金額）の合計は、520万円
- 8週目までの支出の合計は、472万円
なので、利益としては単純計算で50万円ほど出ていることになります。
一方で、資金繰りをみると、5週目の段階で「現金残高がマイナス＝資金ショート」となっているため、5週目で店を畳むことになってしまいますね。
「利益がしっかり出ているのに、資金ショートになる」という典型的な「黒字倒産」に陥っています。
もっと多くの運転資金を持っておくべきでしょう。
今回は飲食店なので、入金サイト（＝入金までの期間）を考慮しなければいけないのはクレジットカードくらいです。
一方、製造業の場合は、「棚卸資産」の概念が入ってくるので、入金までの期間はさらに長くなり、さらに大きな運転資金が必要になります。
もし起業をする方がいましたら、
- 入金サイトは極力短く
- 支払サイトは極力長く
を念頭に置いていただければと思います。
オススメの書籍
今回の記事に関連したオススメの書籍は以下の通りです。
『道具としてのファイナンス』
『ビジネスモデリング入門』
『補給戦』
※スキル本というよりは、戦争時の「兵站」を扱った読み物であり、面白いです
おわりに
いかがでしたでしょうか？
お仕事の参考になれば幸いです！
