高度な財務モデリングのベストプラクティス：インテリジェントでエラーのないモデリングのハック

エグゼクティブサマリー

はじめに：財務モデル

財務モデルは、すべての企業の財務ツールキットに不可欠な部分です。 これらは、特定のビジネスの過去の財務データを詳述し、その将来の財務実績を予測し、そのリスクとリターンのプロファイルを評価するスプレッドシートです。 財務モデルは通常、会計の3つの財務諸表、つまり、収益計算書、貸借対照表、およびキャッシュフロー計算書を中心に構成されています。 ほとんどの企業の経営陣は、少なくとも部分的には、財務モデルの詳細、仮定、および成果に依存しています。これらはすべて、当該企業の戦略的および資本的意思決定プロセスにとって重要です。

この記事は、財務モデルを構築する際に専門家のベストプラクティスに従うことを検討している初心者および中級金融の専門家向けのステップバイステップガイドとして役立ちます。 高度な財務モデラーのために、この記事では、時間、出力、およびモデリングの有効性を最適化するための専門家レベルのヒントとハックの選択も紹介します。 さぁ、始めよう。

モデルの計画

複雑なすべてのものと同様に、財務モデル（「モデル」）を構築するための最初のステップは、青写真を注意深くレイアウトすることです。 モデリング演習の途中での計画外の予期しない構造変更は、特にモデルのアダプターが作成者と同じでない場合、時間がかかり、混乱を招き、エラーが発生しやすくなります。 このような課題は、演習の開始時に少しの献身的な計画時間で簡単に覆されます。 計画段階は次のようにすることをお勧めします。

1.モデルの最終目標を定義します。

モデルの目的を明確に定義することは、モデルの最適なレイアウト、構造、および最終出力を決定するための鍵です。 このプロセスの一環として、構築を開始する前に、モデルの主要な利害関係者が設計図とプロセス設計を承認するように時間をかけてください。 これにより、最終的な好みや意図を表明する機会が与えられ、「スコープクリープ」（業界用語）や将来の苦痛なリダイレクトを回避できます。

2.モデルの構築とその耐用年数の両方のタイムラインを理解します。

モデルの最終目標の二次的なものですが、モデルを構築するためのタイムラインとモデルが使用される期間を理解することも、モデリング演習へのアプローチを決定するための重要な入力です。 長期間および長期間（耐用年数）のモデルは、通常、ゼロからカスタムビルドされ、膨大な量の操作の詳細、柔軟性、および感度機能を備えています。 より迅速で短期間の運用モデルまたは資本プロジェクトモデルの場合、モデラーは多くの場合、プレハブテンプレートを使用して、エラーを最小限に抑えながら建設の速度を最大化します。 さらに、モデルテンプレートはより馴染みがあり、組織内のさまざまな利害関係者が使用/操作しやすい傾向があります。

3.「詳細」と「再利用性」の間の最適なトレードオフを決定します。

必要な詳細レベルとモデルの再利用性の間の最適なトレードオフを決定する場合（つまり、モデルが複数のトランザクションタイプ/目的のために再加工されることを意図しているか、代わりにこの1回限りの演習のために設計されているか）、私がこれまでのキャリアのほとんどを踏襲してきた、モデルの選択/アプローチを決定するための有用なフレームワークは次のとおりです。

ブループリント/計画フェーズが完了し、重要な決定が確定したので、モデリングの次のフェーズに進むことができます。

モデルの構造化

この時点で、Excelを開いて、構造化について考え始める準備ができています。 可能な限り最高のレベルでは、すべてのモデルを3つのセクションに分割できます/分割する必要があります：（a）入力/ドライバー、（b）計算（予測財務諸表）、および（c）出力。 これらのセクションを分離するのが優れているほど、エラーを最小限に抑え、時間どおりに最適化しながら、モデルの監査と修正が容易になります。

私は、作成したほぼすべてのモデルに対して同じ構造的アプローチに従いました。 私のそれぞれの利害関係者と私が常に実用的で消化しやすく、最終的には有用であると感じているアプローチ。 そのセクションは次のとおりです。

1. 表紙（タブ）：プロジェクトコード名、モデルの意図の説明、作成者の連絡先情報、および該当する免責事項。
2. [ドライバー]タブ：入力と仮定。
3. [モデル]タブ：計算（つまり、3つの財務諸表の予測と計算）。
4. [出力]タブ：モデルの最も重要なハイライトの簡潔でわかりやすい要約。
5. [感度]タブ：意思決定プロセスに移行する際に管理者が依存するシナリオ、感度、およびデータの結果の範囲。

これらの各セクションを一度に1つずつ説明します。 次のように：

表紙ページ

表紙はあなたの作品との最初の接点です。 構築するのが最も簡単ですが、うまくいくと、素晴らしい第一印象を残し、これから何が起こるかを明確に説明します。 シンプルで説明的な表紙が一般的に最良のアプローチであり、通常は次のセクションが含まれます。

1. モデルの名前：自明。
2. モデルの目的：その使用目的を説明する段落
3. モデルインデックス：各タブの説明と目的を詳しく説明した簡単な表。 このセクションは、金融以外のオペレーターにとって特に役立ちます。入力に使用する必要のあるタブ、意思決定時に焦点を当てる出力、および必要な複雑な計算タブを強調表示することで、モデルの構造とフローを「ダイジェスト」するのに役立ちます。手つかずのままにしておきます。
4. モデルのバージョン履歴：日付ごとに数秒かけてモデルに加えられた主要な変更を入力することで、特に変更を遡って元に戻したり変更したりする必要がある場合は、常に時間を節約できます。 これは、複雑なモデルや、将来テンプレートとして使用する可能性のあるモデルに特に当てはまります。
5. 著者の連絡先情報：自明
6. 該当する免責事項（法律顧問から提供された場合） ：自明

注意：表紙は、作成者以外の、変更を加える明示的な権限を持たないすべての人に常にロックすることをお勧めします。

ドライバーのタブ：入力と仮定

モデルのカバーページの直後に、ドライバー（入力）タブが表示される必要があります。 このタブは、金融以外のオペレーターが最も頻繁に操作する可能性が高いタブであるため、このタブが明確で、簡潔で、理解しやすいものであることを確認する必要があります。 通常、[入力]タブ内に2つの入力セクションを実装することをお勧めします。1つは静的入力用で、もう1つは動的入力用です。 静的入力とは、仮想の「発電所のサイズ」や「企業の開始時の債務残高」など、時間の経過とともに変化しない入力を意味します。 動的入力とは、「インフレ」の仮定、「債務のコスト」、「収益の伸び」の仮定など、時間の経過とともに変化する入力（たとえば、月ごと、または年ごと）を意味します。

サンプルのドライバーと前提条件タブの例

上記の静的入力セクションと動的入力セクションの両方で、データを2種類に明確に分離することをお勧めします。（1）仮定のシナリオに関係なく変化しないハードコードされた数値と（b）感作パラメーターさまざまな仮定シナリオを推進し、最終的には感度テーブルを推進します。 ただし、どのパラメーターが感度パラメーターを構成するのか、プロジェクトの最終段階までどのパラメーターを構成するのかを完全に把握することはできないことに注意してください。 感度モデリングの詳細については、次の記事を参照してください。

[モデル]タブ：詳細な計算と運用の蓄積

このタブはモデルの中心を表しており、すべての入力、仮定、およびシナリオが連携して、企業の財務実績をその外部の年に予測します。 また、このタブから、さまざまな仮定主導のシナリオが実行され、最終的な戦略的決定の前に実行される演習の評価部分も実行されます。

サンプルモデルタブの例

[シナリオと感度]タブ

認定されたサードパーティのモデルオペレーターは、事前にプログラムされたシナリオの選択肢を選択する場合でも、[シナリオと感度]タブをかなり頻繁に使用します。 このため、シナリオを直感的に作成し、実際のシナリオを外部の編集から保護し、十分に多様な感度を作成して、事前にプログラムされた少数のシナリオで、感度テーブル（以下のサンプル）で起こりうる結果の広い視野を得ることができるようにする必要があります。も構築されています。

ご参考までに、私のキャリアを通じて信頼してきたシナリオのフォーマット構造は、ほんの一例として次のとおりです。

上の画像に関するいくつかの注意事項：

1. モデルユーザーは、シナリオ番号を選択する場所であるため、これのみを編集できる必要があります。 番号は、スプレッドシートの右側に表示されているシナリオの1つを示しています。 次に、ユーザーは最初の列に選択したシナリオ（この場合はNo. 6）を表示します。 これは、モデルで参照されるシナリオと感度のスプレッドシートの唯一の列です。
2. 選択したシナリオが表すものを効果的に要約する説明フィールドをここにいくつか追加します。
3. 特に他の誰かがモデルを使用する場合は、入力の各単位を指定する列を追加することが常に非常に役立つと思います。
4. この列は、選択したシナリオ（この場合はNo. 6）のレバレッジ統計/フィールドを取得します。これらはすべて右側（青色）に表示されます。 それを駆動するために必要な式は、オフセット関数です。つまり、「= OFFSET（上の赤で強調表示されている最初のシナリオのすぐ左に空のセルを挿入します。シナリオが選択/強調表示されているセル）」。 2つのセルの間に空きスペースがあるため、2つのコンマ（、、）はタイプミスではないことに注意してください。
5. 仮定をマクロカテゴリとサブカテゴリにグループ化します。 これは、あなた（モデラー）とユーザーの両方が、モデルが選択したシナリオを明確に理解するのに役立ちます。

サンプル感度テーブルの例

[出力]タブ

出力タブは、モデルのオペレーターが最も頻繁に使用するタブです。 何年にもわたって、中程度から複雑なモデルでは、少なくとも3つの出力タブに傾倒していることに気づきました。

1. [財務出力]タブ：これは、[モデル]タブに詳細が示されている財務の要約です。 それらは通常、年次ベースで提示されます（モデルは四半期ごとである場合もあります）。 この出力は50〜150行で、計算タブのすべての主要な項目を表示する必要があります。 ユーザーがこのタブとさまざまな[計算]タブを切り替えないように、十分な詳細を提示してください。 また、ベストプラクティスとして、出力タブで計算を再実行することはできません。また、この情報には、の直接リンクのみを含める必要があります。
2. エグゼクティブサマリータブ：このタブはかなり標準的であり、通常、グラフ、チャート、表を組み合わせて表示し、エグゼクティブと取締役会メンバーがナビゲートするために必要なさまざまな傾向、分析、および主要な要約統計をできるだけ簡単かつ簡単に示します。重要な決定。
3. 特定の出力タブ：このタブには、通常、投資メモのテンプレート、投資委員会のプレゼンテーション、または決定ポイントに到達するために必要な経営幹部や取締役会メンバーによる要求によって指示される特定の出力が含まれます。

表、チャート、グラフを含む[モデル出力]タブの例

この時点で、モデルの構築フェーズは正式に完了しています。 記事の冒頭で言及した専門家レベルのモデリングのベストプラクティスのいくつかに注意を向けることができます。 フォーマットから始めましょう。

モデルのフォーマット

まず、各企業/グループが独自の好みや社内慣行を持っている可能性があることに注意することが重要です。 そのため、構築中は、それぞれの会社が規定する形式に最初にチェックインし、それを順守することが重要です。 ただし、企業固有の慣行がない場合、以下のコンテンツでは、モデルをフォーマットするためのウォール街の普遍的な言語について詳しく説明します。

財務モデリングの最初で最も簡単なフォーマット方法は、一貫性のある識別可能な配色を使用して、さまざまなタイプのセルとデータを示すことです。 次のように：

青=入力、または履歴値、仮定、ドライバーなどのハードコードされたデータ。

黒=同じシートから派生した数式、計算、または参照。

緑=数式、計算、および他のシートへの参照（ただし、一部のモデルはこの手順を完全にスキップし、これらのセルに黒を使用していることに注意してください）。

紫=他のExcelファイルへのリンク、入力、数式、参照、または計算（ここでも、一部のモデルはこの手順を完全にスキップし、これらのセルにも黒を使用することに注意してください）。

赤=修正するエラー。

適切にフォーマットされた（色分けされた）財務サマリーの例

上記のユニバーサルカラーコーディング標準に従ってExcelスプレッドシートをカラーコーディングするための組み込みの自動化機能がないことに注意してください。 代わりに、これらの結果を達成するために独自のマクロを設計し、その後、ショートカットの組み合わせを作成して、作業を自動的に色分けすることができます。

最近、同僚（今日は感謝しています）から次のマクロ（詳細な手順を含む）を受け取ったので、数時間の手作業を節約できました。 よろしければ共有したいと思います。

マクロ作成手順（Mac版とPC版の両方のExcel用）：

1. Alt + W + M + Rを同時に押して、マクロに名前を付けて記録を開始します。
2. F5（「セルにジャンプ」）を押してから、Alt + Sを同時に押して、「特別に移動」メニューを表示します。
3. 「O」を押して定数を選択し、「X」を押してテキストのチェックを外します。
   
4. 次に、Alt + H + FC（またはCtrl + 1）を同時に押して、これらの定数の青いフォントの色を選択します。
5. Escを押します。
6. F5から始めて同じことを行いますが、定数の代わりに数式（F）を選択し、「X」を押してテキストのチェックを外します。
7. 次に、Alt + H + FC（またはCtrl + 1）を押して、これらの定数に黒のフォントの色を選択します。
8. Alt + W + M+RまたはAlt+T + M+Rでマクロの記録を停止します。

他のワークブックへのリンク

他のワークブックやワークシートへのリンクを見つけるのは難しいので、これを正しく機能させるにはVBAを使用する必要があります。 基本的な考え方は次のとおりです。記号「！」の存在を検索します。 ブック全体の数式を含む各セルで、フォントの色を緑に変更します。 これをVBAエディターで変更し、「！」のすべてのインスタンスを介してループfor eachに作成する必要があります。 見つけたら、これらのそれぞれのフォントの色を変更します。

一部の数式は他のワークシートのセルに直接リンクせずにセルを参照するため、このショートカットは100％の時間は機能しないことに注意してください。 幸い、緑色のセルは黒色または青色のセルよりもまれであるため、上記の方法はほとんどのモデルでかなりうまく機能します（他のワークシートへの残りのリンクは、表示されたとき、またはそれらに遭遇したときに手動で有機的にフォーマットできます）。

モデルを監査するためのベストプラクティス

モデリングするときは、常にこの1つの質問に頭を悩ませることをお勧めします。「このモデルを簡単に監査できるようにしていますか？」 実行され、式が作成され、リンクが構築されるすべてのタスクに対して、その仕事を行うためのより高速な「より汚い」（業界用語では）方法が常に存在するためです。 そのようなハックやトリックは、その時点で、特に時間間隔の後に巧妙に見えるかもしれませんが、常に忘れられ、追跡が困難なエラーにつながります。 第三者のレビュー担当者を念頭に置いておくと、プロセスをガイドし、重要な時点で正しい決定を下すのに役立ちます。

以下は、監査人の考え方で構築する方法に関する一連のベストプラクティスです。 次のように：

1. 1行、1つの数式

行ごとに1つの数式のみを使用する必要があります。つまり、任意の行の最初のセルで使用される数式は、行全体に均一に適用される同じ数式である必要があります。 ユーザーは、モデルを垂直に下に進むときに、各行の最初のセルを見て、モデルの構造を理解する必要があります。

これは原則として単純ですが、さらに強調するのに十分な頻度で違反されます。 一般的な例は、スプレッドシートが「過去の財務」列のグループと「年外の予測」に分割されている場合によく発生します（上記の「適切にフォーマットされた（色分けされた）財務概要の例」というタイトルの画像を参照してください）。 ）。

これらのインスタンスに対処する簡単な方法の1つは、スプレッドシートの上部に配置され、モデルの本体を通じてIFステートメントを使用して参照されるフラグ（たとえば、1/0、 TRUE / FALSE ）を使用することです。 作業中のこれの簡単な図は次のとおりです。

Excelモデリングでの「フラグの使用」の例

2.数式内にハードコードされた数値は埋め込まれていません

数式に埋め込まれたハードコードされた数値は、ユーザーがモデルに慣れていない場合に見つけるのが非常に難しいため、絶対に使用しないでください。 代わりに、入力/ハードコードを数式から明確に強調表示して分離します。 さらに良いことに、すべての入力/ハードコードを（必要に応じて）収集し、それらを同じタブに集約します。 続いて、必要なセルと適切なタブから、必要に応じて数式をプル/参照します。

3.シンプルは常に優れています

複雑な数式は避けたほうがよいでしょう。 代わりに、数式を簡単に消化できるステップに分割します。 一見きれいに見える1つの行の代わりに、このアプローチでは多くの場合、より多くの行が作成され、スプレッドシートが大きくなります。 ただし、サードパーティによる追跡と監査がはるかに簡単なものです。

4.サインコンベンションに一貫して準拠する

符号の規約/キーをゼロにするタイミングを決定する必要があります。 例として、モデルの設計段階で、「コスト、費用、控除、減価償却、設備投資などは負の数または正の数として表示されますか？」と自問してください。 私の個人的な好みは、2つの理由から、常にコストを負の数で表すことです。（a）合計は常に直線の合計になり、ユーザーエラーを最小限に抑えることができます。また、（b）記号だけを使用して間違いを見つけやすくなります。

5.セルに名前を付けるのではなく、Excelのグリッドロジックに依存します

可能であれば、セルに名前を付けることは避けてください。名前の付いたセルのソース入力（「インフレーション」など）を見つけるのが難しくなります。 代わりに、数式内でExcelのグリッド規則に依存することをお勧めします（たとえば、参照が別のタブまたはブックにある場合は、セルC4または場所[Tab Name]l'!G21にリンクするだけです）。

6.複数の場所で同じ入力をしないでください

入力をシンプルかつ透過的に整理します。 すべての入力をいくつかのドライバータブに統合し、スプレッドシート全体でそれらの特異点からそれらを参照することをお勧めします。

7.ファイルのリンクを避ける

他のファイルへのリンクは避けてください。 別のファイルから必要な関連データをハードコードされた入力として入力し、必要に応じて手動で更新することをお勧めします。 クロスリンクは、より大きなExcelモデルをクラッシュさせたり、一貫性のない更新を行ったりすることが知られており、それによって追跡が困難なエラーが発生します。

8.シートまたは行を非表示にしないでください

より長いスプレッドシート内では、行/列を「非表示」にするのではなく「グループ化」します。

9.少数の大きなタブは、複数の小さなタブよりも優れています

この実践は100％経験に基づいています。 複数のタブや、さらに悪いことに、クロスリンクされた複数のスプレッドシートにまたがるよりも、1つの大きな連続したスプレッドシートにまたがるデータの連続配列を追跡および監査する方が簡単です。

10. 1つのタブにある「集約されたエラーチェック」を使用して、モデル全体に​​チェックを作成します

チェックは、モデルの整合性をすばやく確認するための最も簡単な方法です。 「チェック」には、タイにする必要のある合計が実際に行われることを確認することから、バランスシートが実際にバランスをとることを確認することまで、すべてが含まれます。 私は通常、各スプレッドシートの上部または下部にいくつかのチェックを作成し、それらを個別の「チェックタブ」に統合します。 これにより、モデル内のエラーを簡単に見つけて、そのエラーの発生場所を追跡することが容易になります。

貸借対照表「チェック」のサンプル

チェックは通常非常に高レベルであるため、モデルの整合性を検証するためにチェックのみに依存することは決して良い考えではないことに注意してください。 しかし、それは良い出発点です。

切り分け–上級ユーザー向け：Excelのヒント

このセクションでは、上級ユーザー向けの非常に効果的なExcelのベストプラクティスをいくつか取り上げます。 これらは少し調整が必要な場合がありますが、後で数時間の作業を節約し、実装が比較的簡単になるはずです。 それらは次のとおりです。要するに、簡潔で、的を射た箇条書きです。

1. できるだけ多くのキーボードショートカットを使用してください。 インターネットには、さまざまなニーズに合うExcelキーボードショートカット上のファイルがいくつかあります。 ここでいくつか参照します：
   • キーチートシートのモデリング
   • 包括的なExcelのヒントリスト
2. F5（「特別に移動」）を使用して、ハードコードされたすべての数値または数式をすばやく見つけます。
3. モデルを監査するには、TracePrecedentsとTraceDependentsを使用します。
4. XNPVとXIRRを使用して、収益分析に向かう途中で、 custom__日付をキャッシュフローに適用できるようにします。 これは、計算に等距離の時間間隔を暗黙的に想定するExcelのNPVおよびIRR関数とは対照的です。
5. 大きなスプレッドシート全体で情報を検索するには、 VLOOKUP関数ではなくINDEX MATCH関数を使用します。
6. VLOOKUPは、ほとんどの場合、 IFステートメントよりも優れています。 それに慣れてください。
7. 数式の構文にIFERRORを含める習慣を身に付けてください。
8. 日付関数、 EOMONTH 、およびIFステートメントを組み合わせて使用​​して、日付を動的にします。
9. 財務モデルを提示または共有するときにグリッド線を削除します。 よりクリーンで洗練された出力ドキュメントになります。

好きか嫌いか…

好きでも嫌いでも、Excelは、企業の財務、分析、およびデータ主導の意思決定に関して、全知であり、遍在し、全能です。 信じられないかもしれませんが、初心者や初心者であっても、威圧的または苦痛である必要はありません。 人生のほとんどのことと同じように、練習、一貫性、細部への注意（Excelの場合はショートカット）で、ほとんどの方法でそこにたどり着くことができます。

アプリケーションに慣れると、それが強力な生産性と数値のストーリーテリングツールであることがわかります。これは、個人的な生活の中でも、機能しなくてもまばらに機能することができます。 Excelの流暢さのさまざまな段階を進むにつれ、この記事を頻繁に参照する実用的なガイドとして維持することをお勧めします。