財務三表の作り方をエクセルで解説【PEファンド採用テスト対策】

プライベートエクイティファンド（以下PEファンド）を目指される方から、採用におけるモデリングテストについての対策をして欲しいといった声をよくいただきます。そこで、今回はPEファンドや投資銀行等で日常的に使用される財務三表モデルに関して実際にエクセルを用いてご説明します。

PEファンドでは財務三表モデルそのものの作成テストを行うことは非常に稀（LBOモデル自体は財務三表モデルをベースに作成されるので、基本は財務三表の理解がある前提でLBOテストを行う）で、かつ各ファンドによりテストの様式や基準も異なるものの、PEファンドでは一般的にLBOモデルが作成され、財務三表はLBOモデルの基礎になります。最低限LBOモデル作成の基礎になる財務三表を回すという点では、今回ご紹介する内容が必須レベルとなります。

【目次】

1. 1. 財務三表モデルの作り方0.エクセルの設定
   2. 財務三表モデルの作り方1.Projectionの作成
   3. 財務三表モデルの作り方2.BS項目｜運転資本・固定資産項目の作成
   4. 財務三表モデルの作り方3.借入金スケジュール作成
   5. 財務三表モデルの作り方4.株主資本（配当等）のスケジュール作成
   6. 財務三表モデルの作り方5.アウトプットの確認と財務三表が回っているかの確認

財務三表モデルの作り方0.エクセルの設定

まず、財務三表モデルを作成する際はエクセルの基本的な設定に注意しましょう。数式タブの「名前の管理」という箇所で、余分なリンクや数式がモデルに残っていないかをチェックします。
ここでは財務三表モデル上で使用するもののみを残します。下記はインプットシートで税率をtax, 必要最低現金をmincashとしてセルに名前を付けており、そこのみ残しています。

また、自動計算等に関してもオプションから下記のように設定します。データテーブル以外は自動計算にし、反復計算を行うことで不必要に循環参照エラーが出ないようにします。

また、税率等の基本的な変数は、以下のようにまとめてモデルのコントロールシートに記載することが望ましいです。

財務三表モデルの作り方1.Projectionの作成

財務三表モデル作成の際には、過去の財務数値の整理と、将来期間のプロジェクションを作成する必要があります。過去のPLをみて利益率や売上高の成長率をケース別に設定し、まずはPLのトップラインやBSの運転資本、固定資産のスケジュール等を順に作成していきます。

ここでは、PL項目に関して説明します。以下がイメージ（売上高年成長率と粗利率）でケースごとにChoose関数ないしはOffset関数を用いてケースをインプットすると数値がそれに応じて変更されるようになっています。
このケース別数値の設定を利益率（粗利率・営業利益率）の他、対象会社に応じて営業外費用（支払利息以外）等で作成します。

売上高年成長率の例

粗利率の例

上記以外にも営業利益率や研究開発費/売上高比率など、対象会社に応じてケースを作成します。プロジェクションでは、上記のようにケース別に利益率等が変化するシナリオをモデルに組み込んでいきます。

財務三表モデルの作り方2.BS項目｜運転資本・固定資産項目の作成

次に上記のPLのプロジェクションに基づいて、BSの各項目を作成します。運転資本は売上高ないしは売上原価を用いて、回転日数を乗じて将来数値を試算します。また、固定資産は減価償却費と設備投資の水準を対売上高比率で試算し、将来の売上高のプロジェクションに対応して推定計算します。
例えば売掛金の回転期間は、期末残高もしくは前期当期の売掛金平均残高を当期売上高で除して365日を乗じて回転日数を計算します。これを過去5年間計算し、将来のプロジェクションで採用する数値の参考にします。
固定資産に関する項目（減価償却費と設備投資の水準を対売上高比率等）も同様に計算し、プロジェクションにおいて採用する数値を決定します。

モデル上、プロジェクション直前期の残高に対応して増減を計算し、減価償却・設備投資・運転資本増減額は各項目に対応する箇所にキャッシュフロー計算書に記載します。

運転資本プロジェクションの結果は以下の通りです

運転資本計算のベースになる回転日数等の基礎数値は以下のようにまとめ、モデルに織り込みます。DSOは売上債権回転日数、DIOは在庫回転日数、DPOは仕入債務（買掛金）回転日数です。

固定資産に関するスケジュールは以下のようなイメージで作成し、アウトプットの財務諸表に反映させます。

財務三表モデルの作り方3.借入金スケジュール作成

借入金は、既存の借入金に対応する返済スケジュールと借入利率を仮入力して、返済スケジュールを作成します。
また、キャッシュ過不足の額についてはリボルバーローンをスケジュールに織り込むことがポイントで、キャッシュフロー計算書のFCF、期首現金の合計から、必要最低現金、既存の借入金の返済額を減算し、その結果がミニマムキャッシュの残高を超えているか、超えていない場合は不足分をリボルバーローンに組み込むようにモデルを組みます。

既存の長期借入金は、5年ないしは7年間の期間で定額弁済、利率も過去の借入金残高とPLの支払い利息を参考に推定計算しプロジェクションに織り込みます。
リボルバーローンの計算時はCF計算書で計算されるFCF（フリーキャッシュフロー）を元に求めることがポイントになります。

財務三表モデルの作り方4.株主資本（配当等）のスケジュール作成

次に、資本金や利益剰余金のスケジュールを作成します。資本金は増資がある場合に反映されるように織り込み、利益剰余金に関しては当期純利益を繰越利益剰余金に加算し、配当による減少分を加減算して期末の残高を計算できるようにします。
借入金等に比して比較的シンプルなモデルですが、配当の計算時は過去の配当性向を計算し、プロジェクションにおいて当てはめる数値をインプットします。以下のようなイメージです。
配当に関しては、会社計算規則で分配可能額の細かい規定がありますが、今回は割愛します。なお、純資産が3百万円を下回る場合は配当を行うことができない点には注意しましょう。

財務三表モデルの作り方5.アウトプットの確認と財務三表が回っているかの確認

最後に財務三表がしっかりと回っているかを確認します。具体的にはBSが過去も将来期間もバランスしているかという点です。間違いやすい点は、PLの非現金支出項目を加算し忘れる、特別利益でかつ非現金項目を減算する、借入金返済スケジュールを反映させる等です。
キャッシュフロー計算書上のキャッシュ期末残高とBSの期末残高を一致させるように財務三表モデルを組むことが重要です。

アウトプットイメージは以下のようになります。

なお、上記はケース１（マネジメントケース）の場合ですが、アップサイドケースで、よりアグレッシブなプロジェクションにした場合は下記数値になります。売上高および利益率ともに改善しているケースであることが分かるでしょうか。

BS（ベースケース）は以下のようになります

貸借一致していることが分かると思います。なお、運転資本や有形固定資産以外のその他項目は、プロジェクション作成上シンプルにするため、前期と同じ数値が継続するものとしています。これらの項目について、個別に仮定を設けてプロジェクション期間で計算する場合は、増減額をCF計算書に反映させることになります。

キャッシュフロー計算書は以下のようになります。PLとBSが上手く連携していないとCF計算も正しく作成できないため注意が必要です。

=================

＞PEファンド、投資銀行のキャリアに関する記事

LBOモデル作成ステップをエクセルで解説【PEファンド/投資銀行への転職希望者向け】
https://www.axc.ne.jp/media/careertips/lbomodel_pefund_howto

PEファンド入社1年目で身に付けておくべきスキル・経験【コンサル・投資銀行の方向け】
https://www.axc.ne.jp/media/careertips/privateequityfundfirstyear

=================

今回は、PEファンドを目指す方向けに、PEファンドや投資銀行等で日常的に使用される財務三表モデルに関して実際にエクセルを用いてご説明しました。

PEファンドのへのキャリアをお考えの方は、ぜひアクシスコンサルティングにご相談ください。