財務モデリングテクニックを解説する第四章の後編です。前編では、モデルの可読性を高める基本的なテクニックや分析手法を中心に学びました。後編では、よりモデルの構造化とロジック制御に焦点を当て、堅牢でメンテナンス性の高いモデルを構築するための高度なテクニックを解説します。
複数シートの効率的な集計を可能にする「串刺し計算」、モデル内のデータを体系的に整理する「データの型」、再利用可能な計算部品を作る「計算モジュール」、そして複雑な条件分岐を司る「フラグ」と「循環参照」の回避策まで、一歩進んだモデラーを目指すための必須知識を網羅します。
「串刺し計算」マスター講座：複数シート集計をスマートに実現
複数の太陽光発電所ポートフォリオの評価や、複数の事業部門を持つ企業の連結財務モデルなど、個別のエンティティのアウトプットをまとめて合算アウトプットを作成したいというニーズは、財務モデリングにおいて頻繁に発生します。このような場合に活躍するのが、Excelの「串刺し計算（3D集計）」機能です。
串刺し計算（3D集計）の概要と有効性
串刺し計算機能を使用すると、連続して並んでいる複数のワークシートの同じセル番地の値を簡単に合計したり、平均したりすることができます。
例えば、「Asset_01」「Asset_02」「Asset_03」という3つのシートがあり、それぞれが同じフォーマットで各年の売上高（Revenue）をC4セルに持っているとします。これらのシートのC4セルの値を「Asset_Total」という別シートのC4セルに合計したい場合、通常であれば =Asset_01!C4 + Asset_02!C4 + Asset_03!C4 のように一つひとつセル参照を指定する必要があります。シート数が少なければこれでも対応できますが、10シート、20シート、時には100シートを超えるような場合には、数式の構築作業が非常に煩雑になり、エラーのリスクも増大します。
串刺し計算機能を使えば、このような集計を非常に簡潔な数式で、かつ容易に構築することができます。
具体的な数式構築ステップ
串刺し計算で合計する場合の数式は、例えば以下のようになります。
=SUM(Asset_01:Asset_03!C4)
これは、「Asset_01シートからAsset_03シートまでの全てのシートのC4セルを合計する」という意味です。
（シート名に空白が含まれる場合、例えば「Asset 01」というシート名であれば、=SUM('Asset 01:Asset 03'!C4) のようにシングルクォーテーションで囲む必要があります。）
この数式を手入力する必要はなく、以下のステップで直感的に構築できます。
- 集計結果を表示したいセル（例: Asset_TotalシートのC4セル）を選択し、
=SUM(と入力します。 - 数式がアクティブなまま、最初のシート（例: Asset_01シート）のタブをクリックし、集計したいセル（例: C4セル）を選択します。この時点で数式バーは
=SUM(Asset_01!C4のようになっているはずです。 Shiftキーを押しながら、最後のシート（例: Asset_03シート）のタブをクリックします。すると、Asset_01シートからAsset_03シートまでの全てのシートが選択された状態になり、数式バーの表示が=SUM('Asset_01:Asset_03'!C4のように変わります（シート名に空白がなければシングルクォーテーションはつかないこともあります）。- 数式バーの末尾に
)を入力し、Enterキーを押します。
これで、指定した範囲のシートの同じセル番地の合計値が計算されます。
ブックマークシート（空白シート）活用の推奨
串刺し計算を使用する際には、集計対象範囲の最初と最後に、何も入力されていない空白のシート（ブックマークシート）を挿入し、これらのブックマークシートを含めて串刺し計算の範囲に指定することを強く推奨します。
例えば、「Asset_Start」と「Asset_End」という2つの空白シートを作成し、集計対象のシート（Asset_01, Asset_02, Asset_03）をこれらの間に配置します。そして、串刺し計算の数式を =SUM(Asset_Start:Asset_End!C4) のようにします。（実際には空白シートのC4セルは0なので計算結果に影響しませんが、計算対象の項目が入力されている列・行に合わせます。）
ブックマークシートを活用するメリットは以下の通りです。
- 集計対象範囲の明示化: どのシートが集計対象となっているかが視覚的に分かりやすくなります。
- 分析の柔軟性向上: 特定の資産（シート）を分析対象から除外したい場合、そのシートをブックマークシートの外側（例えばAsset_Endシートの右側）にドラッグ＆ドロップで移動するだけで、自動的に集計から除外されます。逆に、新たに追加したいシートをブックマークシートの間に移動すれば、自動的に集計対象に含まれます。これにより、資産の売却や新規買収のシミュレーションなどが非常に簡単に行えます。
串刺し計算の3つの留意点
串刺し計算は非常に便利ですが、使用にあたっては以下の点に注意が必要です。
- 参照先シートは全て同一構成を維持する必要がある:
串刺し計算は、参照先の各シートの「同じセル番地」を計算処理します。したがって、集計対象となる全てのシートにおいて、同じ項目が同じセル位置にある（つまり、シートのレイアウトが完全に同一である）必要があります。 - 行（列）の追加・削除時の自動調整なし: 通常のセル参照では、参照先のシートで行を追加・削除すると、参照している数式も自動的に調整されます。しかし、串刺し計算ではこのような自動調整は行われません。参照先シートのいずれかで行や列を追加・削除すると、串刺し計算の参照先が意図しない箇所にずれてしまう可能性があります。これを避けるためには、
- 集計結果シートにエラーチェックを設ける。
- 参照先シートで行数や列数を変更する際には、集計結果シートや他の参照先シートでも同様の修正を慎重に行う（ブックマークシートの活用は、修正範囲を特定しやすくする点でも有効です）。
- 串刺し計算は、行や列の変更が頻繁に発生しない、構造が安定した箇所での使用が望ましいです。
- シートの配置の確認:
ブックマークシートを活用する場合、最終的な意思決定やレポーティングの際には、ブックマークシート間に意図したシナリオに応じた適切なシートのみが配置されていることを必ず確認する必要があります。モデルの利用者が複数いる場合は特に、使用方法や留意点に関するガイダンスをモデル内に含めることが推奨されます。
適切な理解のもとで串刺し計算を活用することで、財務モデルを使った計数分析の幅が大いに広がるでしょう。
「データの型」徹底理解：モデリングの精度を高める4つの分類
汎用性が高く、メンテナンスしやすい財務モデルを構築するためには、モデル内で扱う各データ項目がどのような性質を持つのかを意識することが重要です。これを「データの型（Data Type）」という概念で整理することができます。プログラミングにおける厳密な型定義とは異なりますが、財務モデリングにおいても、データは以下の4つの型に分類して考えることができます。
この分類は、①データが時間を通じて一定か（定数）、時間とともに変化するか（時系列）か、また②データが1行で表現されるか、複数行にわたるか、という2つの軸で整理できます。
| 1行で表現されるデータ | 複数行にわたるデータ | |
|---|---|---|
| 時間を通じて一定 | 定数データ (Constant) | リストデータ (List) |
| 時間とともに変化 | 時系列データ (Time-series) | テーブルデータ (Table) |
1. 定数データ (Constant Data)
最もシンプルな型で、時間の経過の概念がない、あるいはモデルの計算期間を通じて変化しない数値やテキストを指します。Excel上では、通常、時系列データが並ぶ列（例: O列以降）よりも左側の特定の列（例: N列「Constant」列）にまとめて記載します。
- 例:
- 初年度の売上個数（「初年度」という時点が固定されているため）
- 一度きりの費用（例: 販売開始2年目に発生する特定のキャンペーン費用）
- 税率や利率（モデル期間中に変更が想定されない場合）
- 事業開始日、プロジェクト名
2. 時系列データ (Time-series Data)
定数データとは逆に、時間の経過に従って値が変化する数値やテキストです。財務モデルの主要な部分を構成します。Excel上では、各期間（年、月など）に対応する列に見出し（例: 2025, 2026… または Year 1, Year 2…）を設け、その下に各項目の値を時系列で配置します。
- 例:
- 財務3表（P/L, B/S, C/F）の全ての項目
- 毎年の売上高、売上個数、費用
- 毎年の設備投資額
- 税率や利率（モデル期間中に変更が想定される場合、例えば段階的に上昇する税率など）
3. リストデータ (List Data)
複数の「定数データ」がリスト形式でまとまっているものです。個々の項目は時間を通じて変化しませんが、それらが複数行にわたって一覧化されています。Excel上では、各リスト項目が行として並び、その値は定数データを格納する列（例: Constant列）に記載されます。
- 例:
- 商品単価リスト（商品A単価、商品B単価、商品C単価など、各単価は期間中一定）
- 費用項目リスト（固定費項目A、固定費項目B、固定費項目Cなど、各固定費は期間中一定）
- 耐用年数に応じた税法上の償却率リスト（各耐用年数に対する償却率は法令で固定）
リストデータの特徴は、複数の定数データを一つのグループとして扱い、モデル内で必要な時に特定の項目（例: Price Bの単価）を容易に参照できる点です。
4. テーブルデータ (Table Data)
複数の「時系列データ」が表（テーブル）形式でまとまっているものです。リストデータの各項目が、さらに時間軸に沿って変化する値を持つ場合に用います。Excel上では、各項目が行として並び、各期間の列にそれぞれの時系列データが入力されます。
- 例:
- 商品単価テーブル（商品A、B、Cの単価がそれぞれ年ごとに変動する場合）
- 固定費費用テーブル（固定費A、B、Cの内訳金額がそれぞれ年ごとに変動する場合）
- 為替レートテーブル（JPY/USD、JPY/EUR、JPY/AUDなどの各レートが年ごとに変動する場合）
テーブルデータは、複数の時系列データを一つの項目グループとして効率的に管理し、必要な項目と時点の値を柔軟に参照することを可能にします。例えば、「為替レートテーブル」から「JPY/USD」の「Year 2」のレートをピンポイントで取得する、といった使い方ができます。
リストデータやテーブルデータは、特に抽出条件が変化したり、多数の選択肢の中から特定のデータを利用したりするような複雑なモデルを構築する際に非常に有効です。
「計算モジュール」設計論：再利用性とメンテナンス性を高める構造化アプローチ
財務モデルの心臓部である計算シートは、無秩序に数式を並べるのではなく、構造化されたアプローチで構築することが求められます。その核となるのが「計算モジュール（Calculation Module）」という概念です。これは、計算を論理的な「ブロック」単位で構築し、それらをレゴブロックのように組み合わせてモデル全体を作り上げる手法です。
計算モジュールとは？
計算モジュールとは、特定の計算目的を持った数式の集まりであり、以下の厳密なルールに基づいて作成されます。
- アウトプットの項目と1対1対応: 原則として、一つの計算モジュールは、財務諸表などのアウトプットシートに表示される一つの項目（例: PLの売上高、BSの固定資産残高）を計算するために存在します。例えば、「固定資産」という大きなテーマで減価償却費や期末残高をまとめて計算するのではなく、「PL減価償却費モジュール」「BS固定資産残高モジュール」のように分離します。
- 1行に対して1項目しか入力してはならない: Excelの1行には、1つの計算要素（ラベル、単位、数式など）のみを記述します。
- 計算は左から右、上から下に行われる: データの流れを一定方向に保ち、モデルの可読性を高めます。
- 1行内では数式を途中で変更してはならない: 一つの行の時系列データ部分（例: Year 1からYear 10まで）は、全て同じ計算ロジック（数式）でなければなりません。期間によって計算方法が変わる場合は、フラグなどを用いてIF関数で分岐させるか、別の行で計算します。
これらのルールを守ることで、各モジュールが独立性を持ち、交換可能（他のモデルで再利用しやすい）、修理可能（エラー箇所を特定しやすい）、そして他のモジュールと組み合わせやすくなります。
モジュールの内部構造：サブモジュール
計算モジュールは、通常5行から20行程度の計算量で構成されます。非常にシンプルな場合は3行程度になることもあります。モジュール内の計算をさらに整理するために、「サブモジュール」というより小さなブロック単位を用いることがあります。
例えば、PLの売上高を計算する「売上モジュール」を考えます。売上が「都市部売上」と「地方部売上」から構成され、それぞれで単価と数量が異なるとします。この場合、売上モジュールは以下のような構造になります。
| 売上モジュール |
|---|
| サブモジュール1: 都市部売上計算 |
| （ラベル）都市部単価 |
| （ラベル）都市部数量 |
| （ラベル）都市部売上 ( = 都市部単価 × 都市部数量) |
| サブモジュール2: 地方部売上計算 |
| （ラベル）地方部単価 |
| （ラベル）地方部数量 |
| （ラベル）地方部売上 ( = 地方部単価 × 地方部数量) |
| サブモジュール3: 売上高合計 |
| （ラベル）都市部売上 (上記サブモジュール1の結果を参照) |
| （ラベル）地方部売上 (上記サブモジュール2の結果を参照) |
| （ラベル）売上高 ( = 都市部売上 + 地方部売上) |
このように、1つのモジュール内に3～4つ程度のサブモジュールがあり、各サブモジュールが3～4行程度の項目から構成されるのが理想的な形です。
メインモジュールとファンクションモジュール
1モジュールの計算量が20行を超えるような非常に複雑な計算が必要な場合は、モジュールを2つに分割することが推奨されます。
- メインモジュール: アウトプット項目と1対1対応する主要なモジュール。
- ファンクションモジュール: メインモジュールの計算の一部を独立させた補助的なモジュール。アウトプット項目と直接対応しないこともあります。
例えば、CFの借入金返済額を計算する「借入金返済モジュール（メインモジュール）」があるとします。この返済額計算の中で、「返済可能現預金」の算定ロジックが非常に複雑で長くなる場合、これを独立させて「返済可能現預金モジュール（ファンクションモジュール）」を作成します。
「返済可能現預金モジュール」で計算された結果を、「借入金返済モジュール」が参照して最終的な返済額を計算する、という流れになります。
ファンクションモジュールは、複数のメインモジュールで同様の計算（例: 各種フラグの計算）を行う場合や、メインモジュールの計算が過度に複雑になるのを避けるために構築されます。最も代表的なファンクションモジュールは、各種の条件判定を行うための「フラグモジュール」です。
「フラグ」制御による条件分岐：モデルロジックを明確にする基本テクニック
財務モデルにおける計算は、単純な四則演算だけでなく、様々な条件に基づいて処理を分岐させる必要があります。この「条件分岐」を制御するための最も基本的な概念が「フラグ（Flag）」です。フラグをどれだけ効果的に使いこなせるかが、モデルの品質、特にロジックの明確さとメンテナンス性を大きく左右します。
フラグとは？
フラグとは、財務モデル内において「ON/OFF」の状態、より正確には「真（TRUE）」か「偽（FALSE）」かの論理値を保持するセルのことです。このTRUE/FALSEの値を利用して、計算の実行条件を制御したり、特定の状態を識別したりします。
- 例:
- 運転開始から3年間のみ発生する費用がある場合、「運転開始から3年以内フラグ」を作成し、該当期間のみTRUEとなるようにします。
- 従業員の年齢が40歳以上の場合に特定の手当を計算するなら、「40歳以上フラグ」を作成します。
- 感度分析のためにユーザーがシナリオを切り替えるスイッチ（例: ベースケース/アップサイドケース）もフラグの一種です。
- 計算結果が正しいかどうかのチェック（エラーチェック）も、計算が合っていればTRUE（OK）、間違っていればFALSE（NG）となるフラグで表現できます。
フラグを使った計算とIF関数の連携
フラグ（TRUE/FALSEの値）は、最終的にIF関数などの条件分岐を行う関数と組み合わせて使用されます。
Excelのセルに以下のような論理式を入力してみましょう。
=10<20→ 結果はTRUEになります（10は20より小さいので正しい）。=2*5=11→ 結果はFALSEになります（2×5は10であり、11とは等しくないので正しくない）。
これらのTRUE/FALSEを返す論理式（またはTRUE/FALSEが直接入力されたセル）をIF関数の第一引数（論理式）に使用します。IF関数の基本的な使い方は、IF(論理式, 真の場合の値, 偽の場合の値) です。
=IF(TRUE, 100, 200)→ 結果は100（論理式がTRUEなので、真の場合の値が返る）=IF(FALSE, 100, 200)→ 結果は200（論理式がFALSEなので、偽の場合の値が返る）=IF(10<20, 100, 200)→10<20はTRUEなので、結果は100=IF(A1=TRUE, B1*C1, 0)→ セルA1がTRUEならB1×C1を計算し、そうでなければ0を返す。
なぜ1と0ではなくTRUE/FALSEを使うべきか？
TRUE/FALSEの代わりに、1（ON/真）と0（OFF/偽）を用いて条件分岐を行っているモデルを見かけることもあります。しかし、専門家の立場からは、フラグとして1と0を使用することはあまり推奨されません。主な理由は以下の通りです。
- 金額表記との混同:
例えば、モデルが百万円単位で計算されている場合、50万円以上100万円未満の値は四捨五入されて「1」と表示されることがあります。わずかな税金や受取利息など、金額そのものが1や0になることも少なくありません。このような場合に、その「1」や「0」が金額なのかフラグなのか判別しづらくなります。 - 可読性の低下:
セルにTRUEやFALSEと表示されていれば、それが論理値であり、条件分岐に使われているフラグであることが一目で分かります。 - 条件付き書式の設定の容易さ:
TRUE/FALSEの値に対しては、条件付き書式を設定してセルを色分けするなど、視覚的に分かりやすくする工夫が容易です。 - IF関数内でのロジックの明確化:
フラグを全く使わずに、IF関数の論理式部分に複雑な条件式を直接記述するモデルもありますが、この場合、その条件式が最終的にTRUEになるのかFALSEになるのかを読み解くのが煩雑になります。条件判定部分を独立したフラグ行として計算しておけば、ロジックの検証が容易になります。
以上の理由から、条件分岐を伴う計算では積極的にフラグ（TRUE/FALSE）を用い、モデルの可読性とメンテナンス性を向上させることをお勧めします。
「循環参照」との付き合い方：回避策と代替ソリューション
財務モデリングにおいて、特にプロジェクトファイナンスの利息計算やアップフロントフィー計算などでは、ある計算結果がその計算自身の前提条件に影響を与えるという「循環する計算ロジック」が必要となるケースが頻出します。Excelにはこのような状況で「循環参照」という警告が表示されることがあります。
循環参照とは何か？
循環参照とは、数式が入力されているセル自体が、直接的または間接的にその数式の計算要素（引数）となっており、セル参照がループしている状態を指します。Excelで循環参照が発生すると、「1つ以上の循環参照が発生しています。…」というエラーメッセージが表示されます。
なぜ循環参照（およびExcelの反復計算機能）を避けるべきか？
循環参照を解決するためにExcelの「反復計算」機能を有効にする方法がありますが、財務モデリングの専門家の間では、循環参照および反復計算機能の使用は極力避けるべきという認識が一般的です。その主な理由は以下の3点です。
- 循環参照の増幅リスクが高まる:
最初の循環参照が発生した際にはエラーメッセージで気づけますが、それを放置して作業を続けると、2つ目以降の循環参照が発生しても同じメッセージしか表示されないため、問題がどこで増えているのか特定が困難になります。複数の循環参照が絡み合うと、解決はほぼ不可能になります。 - 一度エラーが生じると修復が困難または不可能になる（反復計算利用時）:
反復計算機能を利用している場合、一度でもタイプミスなどで意図しない大きな数値が循環計算のループに入り込むと、計算結果が発散してしまい、元に戻すことが非常に困難になることがあります。モデルが不安定になり、信頼性が著しく損なわれます。 - 計算の正確性が失われる可能性がある（反復計算利用時）:
比較的単純な循環ロジックであれば反復計算でもある程度正しい結果が得られることもありますが、計算が少し複雑になると、反復計算の回数や変化の許容値の設定によっては、計算が収束しなかったり、誤った値に収束したりするリスクがあります。そして何より、正しく計算されているかどうかの検証が非常に難しいという問題があります。
循環計算の解決策：代替アプローチ
では、循環するロジックをモデルに組み込む必要がある場合、どのように対処すればよいのでしょうか。ここでは、建設期間中の借入金利息（支払利息が元本に組み入れられ、その組み入れられた元本に対しても翌期以降利息が発生するようなケース）を例に、3つの代替アプローチを紹介します。
1. 期首残高に利率を乗じる方法
最もシンプルな解決策の一つが、各期の利息を「期首の借入金残高 × 利率」で計算する方法です。
- 期末残高 = 期首残高 + 当期発生利息（期首残高ベース） + 当期新規借入 – 当期返済
この方法の留意点：
- 当期の利息計算に、当期中に発生する元本の増減（新規借入や元本への利息組入）が反映されないため、実際の発生利息額と若干の乖離が生じる可能性があります。特に、当期の元本増減が大きい場合や、月次・四半期といった短い期間でキャッシュフローを厳密に捉えたい分析では、この乖離が問題となることがあります。
- この計算は、各期のキャッシュフロー（この場合は借入増減や利息支払）が期末に発生するという前提に近くなります。NPV（正味現在価値）やIRR（内部収益率）計算で、キャッシュフローが期央に発生すると仮定する「期央主義」を採用する場合には、一部整合性が失われる点に留意が必要です。
2. コピー＆ペーストマクロを使用する方法
期首残高ではなく、より実態に近い「期中平均残高」を用いて利息を計算したいが、循環参照を避けたい場合に、簡単なVBAマクロを使用して計算結果を値として貼り付ける方法があります。
考え方としては、まず仮の利息（例えば期首残高ベース）で期末残高を計算し、それを使って期中平均残高を算出。その期中平均残高で再度利息を計算し、その結果を元の利息計算箇所に「値貼り付け」する、というプロセスをエラーが許容範囲に収まるまで繰り返すマクロです。
例：
- A列: 利息計算（期中平均残高 × 利率、初期値は仮の値）
- B列: 期末残高（期首残高 + A列の利息）
- C列: 期中平均残高（(期首残高 + B列の期末残高) / 2）
- D列: C列の期中平均残高から再計算した利息
マクロは、D列の値をA列に値貼り付けし、A列とD列の差が小さくなるまでこれを繰り返します。
この方法の留意点：
- モデルの柔軟性（Flexibility）が失われる: 前提条件（利率など）を変更した場合、自動的には再計算されません。変更の都度、マクロを実行する必要があります。
- マクロのメンテナンス: 行や列の挿入・削除があった場合に、マクロ内のセル参照範囲も修正しないとエラーの原因となります。これを避けるためには、コピー対象範囲や貼り付け範囲に「名前の定義」を適切に行う必要があります。
- マクロの使用はモデルの透明性を若干損なうため、使用は限定的にし、モデル内に詳細な説明を記述することが不可欠です。
3. 代数的解法の適用
期中平均残高を用いた利息計算は、中学レベルの簡単な代数（連立方程式）を用いることで、マクロを使わずに循環参照を回避して解くことが可能です。
以下の2つの方程式を考えます（簡単のため、当期新規借入や返済はないものとします）。
- 利息(I) = (期首残高(BoP) + 期末残高(EoP)) / 2 × 利率(r)
- 期末残高(EoP) = 期首残高(BoP) + 利息(I)
この連立方程式を利息(I)について解くと、以下のようになります。
まず、式2を式1のEoPに代入します。
I = (BoP + (BoP + I)) / 2 × r
I = (2 × BoP + I) / 2 × r
2 × I = (2 × BoP + I) × r
2 × I = 2 × BoP × r + I × r
2 × I – I × r = 2 × BoP × r
I × (2 – r) = 2 × BoP × r
I = (2 × BoP × r) / (2 – r)
この最後の式を用いれば、期首残高(BoP)と利率(r)だけで、期中平均残高を考慮した利息(I)を直接計算できます。これにより循環参照は発生しません。
この方法の留意点：
- 導出された数式（例:
I = (2 * BoP * r) / (2 - r)）が、一見して直感的に理解しにくい可能性があります。 - そのため、モデルの透明性を確保するために、計算式の隣接セルやコメント機能を用いて、その数式の導出過程や意味について詳細な説明を記述することが強く推奨されます。
循環する計算ロジックが必要な場合でも、これらの代替アプローチを検討することで、モデルの安定性と信頼性を維持しつつ、意図した計算を実現することが可能です。
コメント