エクセルモデルの作り方〜おれは引っ越しのコストを計算していただけなのに、そこにいたのはありし日のモデル奴隷だったんだ……おれは…おれは……！！〜
ここまでのあらすじ
色々あって引っ越しするか迷ってたおれは、「いまの家にいるのと、家賃が安い家に住むのはどっちがお得かなあ」なんてぼ〜っと考えてたんだ
暇だったからスプレッドシートで損得を計算しようとしたその瞬間
う、うわああああ！！！
おれの中のモデル奴隷が姿を現しやがった……！そうです私は調教済みのモデル奴隷だったのです(ただし奴隷としてのレベルはそれほど高くない)。
モデル奴隷こと私はスプレッドシートを触りながら「データシート機能ないの！？」「エクセルのが便利じゃない？」なんて小言を口にしつつも、それなりに作業を楽しみ、まさかまさかモデル作成のノウハウを将来の自分のために書き残すことにしたのです👶意味不明👶
そしてやほほ村に奴隷の財産が残されることとなった。呪われし大地よ、、、やほほ村の民よ、、、
この記事はエクセルモデルを作ったことある人が、自分のノウハウをまとめておくだけのものです。もちろん誰かの役に立てば嬉しいですが、こんな「やほほ村」とかいうへんぴな場所に訪れずとも「財務モデル 作り方」とかググればたくさんの情報が出てきます……！
この記事は筆者の過去及び現在のいかなる所属団体に無関係なものとして書かれています
モデルってなんすか
「モデルモデルってなんだよさっきから。うぜーんだよ！」 ――やほほ村の住人
冒頭で言ったように、私は「いまの家にいるのと、家賃が安い家に住むのはどっちがお得かなあ」と考えていたわけですが、お得になる金額を実際に計算しようとするとざっと考えるだけで下記のような事項を考慮しないといけません。
- 新しい家の家賃はどのくらいか
- 重複する賃料の金額はどのくらいか
- 敷礼金の金額はどのくらいか
- 鍵交換は自費負担か
- などなどもっと色々ある、、、うわああああああああ！！！！(発狂)
もしも、こういった事項ひとつひとつの仮定を実際に変化させながら、お得になる金額を逐一計算して確認できれば最高ハッピーではないでしょうか？
それが「モデル」です。
エクセルでもスプレッドシートでもいいので、自分が入力する値を変化させるとそれに合わせて結果を常に変化させてくれるようなファイルを作るのです。それは一つのプログラムとも言えます。それが「モデル」です。
もう少し厳格な定義を作っておきましょう🎉この記事では、表計算ソフトで作られたファイルのうち、下に書く2つのことが実現されたものを「モデル」と呼ぶことにします。
- ある前提に基づいた計算を実行してくれる
- 変数(=入力として与える値)を変化させると、結果(=出力される値)が変わる
世の中で「財務モデル」と呼ばれているものはまさにこれです。「財務モデル」の実体は大体の場合、下記2つのことができるエクセルファイルであり、それはモデルの定義を満たしています。
- 市場成長率や人件費上昇率を仮定したら、営業利益とかを計算してくれる
= ある前提に基づいた計算を実行してくれる - どっかのセルに入力された市場成長率などの数値を変えると、どっかのセルに表示されている営業利益とかの数字が変わる
= 変数(=入力として与える値)を変化させると、結果(=出力される値)が変わる
言葉じゃわからないよと苦情が殺到するかもしれないので、解像度ゲキ低のgifを載せておきます。イメージね。
水色のセルにある結果(=出力される値)が変わる
上記のgifは、「引越し費用や保証契約費用の値が色々変化したとき、引っ越した場合のコストメリットはどのくらい変わるだろうか」と見ている様子です。
こういうファイルを銀行とかファンドの人たちは夜な夜な作っていると聞いたことがあります。そのおかげで今日も街の夜景がきれいなのかな from スカイツリー😭
ちなみに上記gifになっているモデルの現物が下記リンク先にあります。冒頭で言っていた引越し費用のモデルです。実際に見てもらうと記事の理解が捗りそうです！
下記リンク先のスプレッドシートファイルに含まれるいかなる情報の正確性も、筆者は責任を負いません
どうやって作るんですか
そんなんどうやって作るの！？ってなっちゃうけど、作る流れはこんな感じ
- モデルで白黒つけたいことを明確にし、アウトプットを決める
- 計算ロジックを明確にする
- モデルの構成を固める
- モデルを作る！！！(心を無にして禅の世界へ……🐥)
- 作ったモデルを眺めて悦に入る白黒つけたかったことに決着をつける
では順番にみていくぜ
1. モデルで白黒つけたいことを明確にし、アウトプットを決める
突然ですがここで、ここまでこの記事を読み進めた人にアンケートで感想を聞きました(複数回答可)
- モデルって大変そう。作りたくない(82%)
- ブログ名にもなってるやほほ村ってなに (52%)
- ところどころにつまらない冗談が混ざっていて読みにくい(14%)
そうなのです、80%の人が「モデルって大変そう。作りたくない」と思っているのです。そしてそれは正しい感覚だと個人的には考えています。モデルは工数がかかるし、なんかようわからん計算ミスとかあると台無しです。
作るのが楽しくて脳汁がやばいですというモデルの精神的奴隷の方以外は、なぜモデルを作らないといけないのかを常に冷静に検討すべきかと考えます。
人間はモデルに支配されてしまうのか……？
一番やりたくないのは「モデル作ったけど意味なかった」というパターンです。これはもう悲しすぎる。やりきれません。だからモデル狂いの方以外は、まずモデルで白黒つけたいことを明文化したうえで、ほしいアウトプットを決めましょう。それを通じて、作らなくていいモデルは作らずに済むはずです。
今回の私の場合は、下記のようになります。
- モデルで白黒つけたいこと: 引っ越したほうがお得かどうか
- ほしいアウトプット: こんどの3月に引っ越しをして新しい家に2年間住んだ場合と、いまいる家に住み続けて2年たった場合のトータル支出の差額(家賃に加え、引っ越しに要する初期費用や引越し費用も含む)
このとき、そのアウトプットが出てこれば白黒つけたいことに決着がつくのかということに気をつけるべきです。例えば、[ここに具体例を載せるつもりだったけど、やほほ村の民は力尽きて載せることができませんでした……！残念……！]なんて話がありえるわけです。作ってみたあとに「これじゃねぇ〜〜〜〜」ってなるのは何としてでも避けましょう😭
2. 計算ロジックを明確化する
アウトプットが決まったら、計算ロジックを考えましょう。
計算ロジックとは、具体的な計算式のことをかっこよく言ってみているだけ、、、ではなく、下記2つのセットのことです。
- アウトプットを計算するための式
- 計算式中の各要素について、それが定数なのか変数なのかの情報←これが意外と大事
これだけ説明しても「何いってだこいつ」という感じになってしまうので、先ほどの例で1つずつ実際にやってみましょう。
アウトプットの計算をするための式を作る
……というわけで、アウトプットの計算式を検討した結果が下記のようになります。
なお、清書するのめんどくせぇ〜〜ってなったのでまさかの手書き！！許せ👶👶
末端から順番に、それぞれの要素を線の交点に書いてある式で組み合わせてやれば計算できます。なお、末端の「XXの家賃係数」というのは「XXを新しい家の月々の家賃で割ってあげた商」を定義としています。
ところで、この作業は得てして純粋な数学的作業の範疇を超えてきます。というのも、計算式の立て方はしばしば一通りではなく、現実的に入手できるデータやいまこの瞬間において適切な粒度のデータで計算できるような立式をしなければならないからです。
例えばちょうど今の例でいうと、引越し費用のところは引っ越し距離や荷物の重さといった要素を用いて分解できるのかもしれません。しかし、現時点でそこまで私は計算する必要がないと考えているので、その分解は適切な粒度ではないわけです。細ければいいってもんじゃないんだよ！ﾌﾟﾝﾌﾟﾝ
また、ビジネスの世界でモデルを作成する際なんかは、手に入らない情報があるならその情報が必要ないような立式、もしくはその情報のウェイトが低くなるような立式を模索すべきだと言えるでしょう。入手できない情報が計算式に入ってたら、あとで「ぴぇ〜〜ん計算できないよう🥺🥺🥺」です。
要約するなら、「色々調べて、現実的かつちょうどいい計算式をつくろう」ってことですね
計算式中の各要素が定数なのか変数なのかを決める
さて計算式ができたので、何が定数で何が変数になるかを決めちゃいましょう👶
定数と変数という言葉の意味を再確認しておくと、そもそもモデルってのは「自分が入力する値を変化させるとそれに合わせて結果を常に変化させてくれるようなファイル」だったわけで、自分が変化させられる値が変数で、変化させることがない値が定数ってことになる。
例えばさっきの例だと、更新料はもう契約で決まっていて固定だから変化させることはない。だから定数。一方で新しい家の家賃ってのは、もちろん色々変化させて考えたいから変数！
ちなみにお気づきかとは思うが、変数か定数かは末端の要素についてだけ決めればいいのじゃ👴(モデルおじさん)
というわけで実際に分類してみた結果がこちら。
こんなに変数が多いとわけわかんなくない？という指摘はやほほ村の民もわかっている通りで、ちょっと変数が多すぎる気もします。でも気にしない。宇宙の広さを思い出せ。
ちなみにどれが変数でどれが定数かという問題は、モデルが扱われる状況によって異なります。金銭的な事情で新しい家の家賃が決まっている人にとっては、新しい家の家賃は定数になります。ここもまた、数学的な問題には留まらないわけです。
さて、とりあえずこれで計算ロジック、つまり下の2つが決定できました。
- アウトプットを計算するための式
- 計算式中の各要素について、それが定数なのか変数なのかの情報
大事なのは計算ロジックの段階で、純粋な数学の範囲では解決できないことがたくさんあるということです。
だからあなたがモデルで白黒つけたいと思っていることを同じように白黒つけたいと思っている共同作業者がいるなら、その人にも意見をもらいましょう(なぜこんなことを書いているのかわかりませんが、書いてしまったので載せておきます。誰向けの記事なのかどんどんわからなくなってきています👶)。
3. モデルの構成を固める
さて、ここで急にエクセルとかスプレッドシートを開いて作業し始めてはなりません。急に作り始めてもわけわかめになって、入り組んだやばいフランケンシュタイン系モデルができあがってしまいます。ぐちゃぐちゃのモデルはたいてい、見づらいし、ミスしているし、あっているかどうかもよくわからないのです。
というわけで、スッキリモデルを作るためにファイル全体およびシート個別の2レイヤーで設計図を書いてあげましょう。
ファイル全体の設計をする
ここで決めることは、ファイル中のどのシート(タブ)に何を載せるのかです。
モデルはたいてい多くの要素を使って計算をすることになるので、何も考えずにシートを作りまくったり、逆に作らなかったりするとぐちゃぐちゃになってしまいます。ではどうやって分けるかというと……あっモデル犬だ！👴
🐶「定石としてシートを3種に分けることが多いよね」
- output: アウトプットを載せるシート
- calculation: アウトプットと前提をつなげるための計算を実行するシート
- assumption: 前提(=さっきの計算図でいう末端の要素。変数と定数ともいえる)を載せるためのシート
これはたしかにわかりやすそうです！実際にやってみましょう、ありがとうモデル犬。
これらの要素が全部1枚のシートに配置されていたらと思うと発狂です。まあ1日だけモデルが動けばいいなら構いませんが、モデルを3日後に見たらわけがわからなくなっていることでしょう。他人に共有するなんて夢のまた夢のまた夢の世界です。インセプションより深く夢に潜っちゃいます。(インセプション面白いから観てみてね)
冒頭および記事末尾のリンク先にあるスプレッドシート上のモデルでは、あえてassumptionシートに載せるはずの前提をoutputシートに載せています。本来は前提とアウトプットがシート単位で分かれている方がファイル全体の可読性が高まるのですが、下記2点の理由から今回は一緒にしてしまいました。
- 前提を細かく調整しながらアウトプットを観察したかったため、シートが分かれていると不便だったため
- 前提のセルとアウトプットのセルは後述のカラーコードにより容易に見分けがつくため
シート個別の設計をする
さて。シートの分割ができたら、いよいよシート個別の設計をしましょう！ではまたモデル犬を呼びましょう。
🐶「まず何より大事なのは、ちゃんと紙に手書きで、どのセルでどういう計算をするのか書くことでしゅ」
これは衝撃です。エクセルを作るのになんで紙に手書きなんていう前時代的行いをしなければならないのか。
ただ、これはやってみればモデル犬の言っていることがわかります。ここの設計を緻密にやっておくことで、作業時に何も考えずに済むのです。それに、作業のときに考え出すとドツボです。ぐちゃぐちゃ変更しまくる「変更沼」にハマってモデルは一生完成しません。
まずは手書きで愚直に設計。これに尽きるんじゃ……！(千鳥ノブ風)
計算式も決めちゃえ
🐶「さて、シート個別の設計をするうえで気をつけたいのは下記の2つのことダヨ」
- シート上の情報の流れを下から上に、右から左にする
- 表の基本的な形を決めたうえで、表をピチッと揃えて配置する
🐢「シート上の情報の流れについて、もちろん今回公開しているモデルの月(month)のように時間経過のような流れは人間の直感として左から右に流れていくようにすればいいけど、シート全体で計算は下から上に、右から左に進んでいくようにすべきだ。それが一番見やすい気がする*1けど、でももし嫌だったら好きにしてくれていいと思う！」
🐶「(誰お前……)」
🐢「そして、表の基本的な形を決めたうえで、表をピチッと揃えて配置するというのはもう見て感じてもらった方がはやいカメ。下に載せている図みたいな感じ。同じ種類の表なら縦の項目まで揃えたい。たとえゼロにしかならない値があってもゼロで埋めてしまえばいい。参考までに揃っていない状態も載せておいたカメ」
項目についても、値がゼロになるようなものもあえて残して揃えている
これは可読性すら著しく毀損されているレベル👾
🐶「表の形を揃えるのは、実用的な側面もあるワン。例えば、またいつか話すことになるかもしれないけど、表計算ソフト特有の絶対参照/相対参照を駆使して立式しておけば、1つの表で作った式を他の表にコピペしていくだけで全部動くように作れることがあるワン。それは表の形が揃っているからなし得る業です(突然の丁寧語)」
🐢「表の形が揃っていると、sumやindexといったセル内式の参照が真下や真右のセルに伸びているから、F2なんかで式の参照範囲を確認するときにとても視認性が高くなるカメね」
なにやらとてもマニアックな会話をしだした2人ですね。一旦休憩しましょう。
さて、いろいろな話がありましたが、シート個別の設計については、とにかくシート全てについて下図のようにできればいいと、やほほ村では考えられています(下図はもちろん完成品ですが、このような配置や計算式を手書きで事前に完成させておくといいということです)。
4. モデルを作る
さて、いざ作成開始です！
作るべきものは既に紙に書いてあります。もうこれを打ち込んでいくだけなのです。
心を無にしてください。ここからは禅の世界です……！
さよなら理性……自然と一体化せよ……🌴🐬🌋
カラーコードを適用する
このセクションはダレトクマニアックなので、狂人以外は読まないでいいです
さて、禅の世界とはいえ一つ工夫できる点があります。カラーコードです。
狂人の皆さん、モデルにはカラーコードを組み込みましょう。なんだかすごく心が浄化されます。
もともとは外銀やコンサルで活用されているテクニーーークっぽいので、日常生活で使用するモデル(日常生活でモデルを作成している時点で狂っちまってる)でここまでする必要はないかもしれませんが……👶
カラーコードカラーコードとうるさく言っていますが、要はセルのタイプによって文字やセルの色を変えようというわけです。下記が具体的かつ一般的な手法ですが、色は自分が好きなようにしてください(おまえの好きな色でおまえの人生を生きていけ！！！)。
- セルの文字色
- 青: ファイルの外部に根拠がある数字(例: 1ヶ月は30日だから入力した30という数字、現在の家賃として入力した130000円という数字)
- 緑: 他のシートのセルを参照している数字
- 黒: シート上で計算されている数字、もしくはシート上の他セルを参照している数字
- セルの背景色
- 黄: 変数
- 青: モデルのアウトプット
黄色いセルの値を変化させながら青いセルの数字を見ることで、モデルを楽しもうということになるわけです。
このようなカラーコードは実際にやってみると、モデルの構造がひと目で把握できるようになることがわかります。また、構造がひと目でわかるようになることで、間違っている箇所に気づきやすくなるメリットがあります。うれし〜〜〜〜〜！！！🎉🎉🎉🎉
なお狂人向けに、狂人中の狂人と思しきマスターたちがなんとネットで、エクセルの文字色を変えられるショートカットキーを実現するマクロを無料配布してくれたりしてます。探してみましょう🐶感謝感激！！
5. 作ったモデルを眺めて悦に入る白黒つけたかったことに決着をつける
さてさて、ようやくモデルが完成しました。
もしもいい感じにできていたら、機械仕掛けの面白いプラモデルを作ったような達成感に浸れることでしょう。ちょっと眺めたり、前提をいじったりして楽しんでください。ちなみに他人に見せても、感動を共有できることはあまりありません。
さて悦に入るだけでなく、ちゃんと白黒つけたかったことに決着をつけましょう。ここから先はもう好きにやってくれればいいです。モデルがアウトプットの設計含めて適切に作成されていれば、あなたの悩みは目の前のモデルが解決してくれるはずです！メイ・ザ・モデル・ビー・ウィズ・ユー
解決してくれなかったら、その日は諦めて早めに寝ましょう！
おやすみなさい✌
おわりに
ここで一応、冒頭でgifになっていたモデルの現物リンクを再度貼っておきます。繰り返し例に出していた引越し費用のモデルです。
さっき少しだけ説明したカラーコードもちゃっかり入れていますね🐶
下記リンク先のスプレッドシートファイルに含まれるいかなる情報の正確性も、筆者は責任を負いません
とまあ、こんな感じでモデルについて書き残しておきましたが、一番なのは自分で作ってみることです！興味があったらやってみてね〜〜！！
(この記事まじで誰向けなの？？？)
*1:心理学的に人は左上にいちばん重要な情報があると思って書類を目にする。いろいろな話があるけど、例えばこんな話がある。
ここから分かるのは、「重要なグループは左上に置いた方が良い」ということです。Webサイト（特にトップページ）で大切なのは、すぐ（5秒以内）に「概要とメリット」を伝えることです。今回の例では「キャッチフレーズ」グループがそれに当たります。ここを最初に見せるには、左上に置くのがベストということです。もし右下にあったら、あまり見てもらえないということです。
