Excelで財務分析！基本的な財務モデルの作成法

財務モデルとは？ - ビジネスの未来を映す設計図

財務モデルとは、企業の過去の財務実績や将来の事業計画に関する仮説に基づき、将来の財務状況を予測するための、Excelなどで作成された分析ツールです。

それはまるで、企業の健康状態を診断し、未来の姿を描き出すための「設計図」のようなものです。

財務モデルの目的と重要性

作成されたモデルは、企業の価値評価（バリュエーション）、M&Aの検討、新規事業の採算性評価、融資の判断材料など、経営における重要な意思決定の場面で活用されます。

精度の高いモデルを構築するスキルは、金融業界だけでなく、あらゆるビジネスパーソンにとって強力な武器となります。

基本となる「3ステートメントモデル」

数ある財務モデルの中でも、最も基本的かつ重要なのが「3ステートメントモデル」です。

これは、企業の主要な財務諸表である「損益計算書（P/L）」「貸借対照表（B/S）」「キャッシュ・フロー計算書（C/F）」の三つを、Excel上で有機的に連動させるモデルです。

この「連動」こそがモデルの核心であり、例えば「売上が〇%伸びたら、最終的な現金はいくら増えるのか？」といったシミュレーションを可能にします。

【準備編】モデル作成の土台を築く

精緻なモデルを組むためには、正確なデータと、整理されたExcelシートという強固な土台が不可欠です。

必要なデータの収集：有価証券報告書の活用

モデルの基礎となる過去の財務データは、上場企業であれば金融庁のEDINETなどで公開されている有価証券報告書企業の概況、事業の状況、財務諸表など、投資家向けの重要な情報が網羅された公式な開示資料です。から入手するのが最も確実です。

最低でも過去3期分の損益計算書（P/L）と貸借対照表（B/S）のデータを準備しましょう。

Excelシートの基本設計とフォーマット

プロのモデルは、その美しさと分かりやすさにもこだわります。

• ・シートの分割

  「入力シート（過去データ）」「仮定シート（予測の前提）」「モデルシート（3ステートメント）」のように、役割ごとにシートを分割すると、管理がしやすくなります。

• ・色分けのルール

  数式ではなく直接入力するセル（仮定など）は青文字、他のセルからリンクされている数式セルは黒文字、他のシートからリンクされている場合は緑文字、といったルールを設けると、モデルの構造が直感的に理解できます。

• ・単位と表記の統一

  「単位：百万円」などの表記を明確にし、数値のフォーマット（桁区切り、マイナスの表示形式など）をシート全体で統一します。

【実践編】3ステートメントモデルの構築手順

ここからが、モデル構築の具体的なステップです。

一つひとつの計算の意味を理解しながら進めましょう。

Step 1: 損益計算書（P/L）の予測

まず、将来の利益を生み出すエンジンである損益計算書から作成します。

「売上高成長率」や「売上原価率」「販管費率」といった将来の予測値を「仮定シート」に設定します。

過去の実績を参考に、将来の事業環境を考慮して仮説を立てることが重要です。

これらの仮定値を基に、将来の売上高から始まり、営業利益、経常利益、そして最終的な当期純利益までを数式で計算していきます。

Step 2: 貸借対照表（B/S）項目の予測

次に、損益計算書の活動と連動する貸借対照表の項目を予測します。

例えば、「売上債権（売掛金）は売上高の〇日分」「棚卸資産（在庫）は売上原価の〇日分」といった形で、運転資本企業の日常的な営業活動に必要な資金のこと。主に「売上債権＋棚卸資産－仕入債務」で計算されます。に関連する項目を予測します。

また、設備投資（CAPEX）の計画や減価償却費の計算を行い、固定資産の将来価値を算出します。

Step 3: キャッシュ・フロー計算書（C/F）の作成【モデルの心臓部】

ここがモデリングで最も重要かつ難解な部分です。

キャッシュ・フロー計算書は、P/LとB/Sの情報を繋ぎ合わせ、最終的な現金の増減を計算する役割を担います。

1. 1. 営業キャッシュ・フロー(CFO)

   P/Lの当期純利益をスタート地点とし、非現金支出費用である減価償却費を足し戻し、B/Sから計算した運転資本の増減額を調整します。

2. 2. 投資キャッシュ・フロー(CFI)

   B/Sの固定資産の増減から計算した設備投資（CAPEX）の支出を計上します。

3. 3. 財務キャッシュ・フロー(CFF)

   借入金の増減や、配当金の支払いなどを反映させます。

これら3つのキャッシュ・フローを合計し、期首の現金残高に加えることで、期末の現金残高が計算されます。

Step 4: 最終調整とエラーチェック

最後に、モデルが正しく組まれているかを確認する最終工程です。

Step3で計算した期末の現金残高を、予測期間のB/Sの現預金勘定に数式でリンクさせます。

そして、B/Sの「資産合計」と「負債・純資産合計」が一致するかを確認します。

この差額をチェックするセルを設け、その値が「0」になれば、モデルの主要な連動が正しく行われている証拠です。

このバランスチェックが、モデルの信頼性を担保します。

【上級者向け】モデルの洗練と分析への応用

基本的な3ステートメントモデルが完成したら、さらに高度な分析へと発展させることができます。

感度分析とシナリオ分析

「もし売上成長率が想定より5%低かったら、利益はどうなるか？」といったシミュレーションを行うのが、感度分析特定の変数（仮定）を変化させたときに、結果（利益やキャッシュ・フローなど）がどの程度変動するかを分析する手法です。やシナリオ分析です。

Excelの「データテーブル」機能を使えば、「成長率」と「利益率」を同時に変化させた場合の結果を一覧表で示すなど、多角的な分析が可能です。

グラフ作成による視覚化

数字の羅列だけでは、傾向を掴むのは困難です。

売上高や利益の推移を棒グラフで、利益率の変化を折れ線グラフで示すなど、モデルの結果をグラフ化することで、分析結果の説得力を飛躍的に高めることができます。

まとめ

今回は、Excelを使った基本的な財務モデル、3ステートメントモデルの構築方法を解説しました。

1. 1. 準備と設計:

   正確なデータを入手し、見やすく整理されたExcelシートを準備することが、精緻なモデルの第一歩です。

2. 2. 3表の構築:

   P/L、B/S、C/Fの順に、仮定に基づいて将来の数値を予測し、数式で連動させていきます。

3. 3. キャッシュの連動と確認:

   C/Fで計算した期末現金をB/Sに反映させ、貸借がバランスすることを確認するのが最終チェックです。

財務モデリングは、一度作って終わりではなく、何度も見直し、仮説を修正しながら精度を高めていく、実践的なスキルです。

この記事を参考に、ぜひご自身で手を動かして、まずは一つのモデルを完成させてみてください。

もし、Excelの複雑な関数でつまずいてしまったり、より高度な分析を行うためのPCスペックについてのご相談があれば、いつでもお気軽に当店にご相談ください。

お客様の知的な挑戦を、ハードウェアとソフトウェアの両面からサポートさせていただきます。