エクセル財務モデル（財務三表シミュレーションモデル）の作成方法：実践的ガイド

私は、国内大手PE（プライベートエクイティ）ファンド出資会社の財務経理責任者として、約5年間、PEファンドの他、金融機関や監査法人等とコミュニケーションをとってきました。そのコミュニケーションのもとになったのが、財務三表が連動した正確な財務モデルになります。
企業の財務状況を正確に把握し、将来の経営戦略を策定するためには、財務三表（損益計算書、貸借対照表、キャッシュフロー計算書）の理解とシミュレーションが不可欠です。また、その財務モデルは様々なシミュレーションに対応できるようにするため、エクセルで作成するのが一般的です。
本記事では、エクセルを活用して財務三表のシミュレーションモデルを作成する方法を、分かりやすく解説します。

財務三表とは？その基本構造と重要性

財務三表は企業の経営状況を把握するための基本的な財務資料です。

エクセルを用いた財務三表モデル作成の基本ステップ

エクセルで財務三表を作成する際の基本的な流れは以下の通りです。

財務モデル サンプル

上記各項目について、以下説明をしますが、実際に作成した財務モデルにもとづいて作成した方がわかりやすいと思いますので、説明用にシンプルな財務モデルを作成しましたので、提供いたします。
こちらのURLからダウンロードできますので、是非、ダウンロードしてください。こちらのモデルを操作・確認しながら読んでいただければより理解が深まると思います。

ダウンロードボタンをクリックすると、Googleスプレッドシートが表示されますので、左上の「ファイル」タブをクリックし、Excel形式でダウンロードください。

作成にあたっての留意点

作成に入る前に、基本的な留意点についてお伝えします。
財務モデルは将来数値を予測するためのものになります。シミュレーションは、最初一度きりのものでなく、定期に、または状況が変わったタイミングで何度も行います。
また、当初財務モデルを作成した時点から、途中で前提が変わってくることもあり、その場合は、新しい前提を財務モデルに組み込む必要があります。
したがって、財務モデル作成にあたり、作成後のメンテナンスも考慮して作成する必要があります。
つまり、将来的に、基本情報の追加、変動要素の追加、前提の変更、科目の追加等があるものとして、それらがあった場合にメンテナンスしやすい作りにしておくことが非常に重要です。

モデル構成（設計図）の決定

最初にモデルの構成を決めます。
基本的には、基本情報を設定する「マスタシート」、変動要素を設定する「パラメータシート」、アウトプットとしての「財務三表」シートで構成されます。
ただし、今回作成した財務モデルは、将来的なメンテナンスを考慮して、主要な勘定科目（「売掛金」「棚卸資産」「買掛金」「借入金」）は別シートにしました。
次の様な構成にしています。

基本情報の設定

今回のモデルでは、基本情報を設定するシート（マスタシート）として次のシート構成にしました。

商品マスタ

当社は卸売販売を行う会社とします。
扱う商品をA商品～D商品とし、各商品の「仕入単価」「売単価」「原価率」をこのシートで設定します。
それぞれ次の金額で設定しています。

基本情報を設定するマスタシートは、基本的にテーブルにした方が計算式の設定、メンテナンスにおいて望ましいです。表をテーブルにするのは、表を範囲指定したうえで、「Ctrl」キーと「T」キーを同時に押し、ダイヤログボックスの「OK」をクリックすると、その表がテーブルになります。

得意先マスタ

次に販売先（得意先）の入金サイトを設定します。
得意先はX社・Y社・Z社とし、各入金サイト（月）を次のとおりに設定しています。

パラメータ（変動要素）の設定

今回のモデルでは、パラメータ（変動要素）は次のシート構成にしました。
パラメータシートに入力する数値によって、財務三表が連動して変化するようにします。そのため、何の要素をパラメータとして設定するかは、シミュレーションの目的によって決まります。
今回は「販売数量」および「仕入数量」をパラメータとして設定しています。

月別予想販売数量

商品別に各月の販売数量を予測した表になります。
仮に上記数値の場合、財務三表はどの様な数字になるのかシミュレーションします。

月別仕入数量

商品別の仕入数量を入力する表になります。
在庫数量（棚卸数量）および販売数量を考慮して数値を入力します。

以上で、マスタ＆パラメーターシートが完成です。

損益計算書（P/L）モデルの作成

財務三表の中で、一番最初に作成するのが損益計算書になります。
損益計算書では、売上の増減、原価の変動、営業費用の増減が利益に与える影響をシミュレーションします。
販売価格、原価率、販売数量が変化したときに、売上高および利益・利益率の変動や、コスト削減が企業の利益にどのように影響するかを分析できます。

売上高、売上原価、売上総利益

「商品マスタ」「月別予想販売数量」のデータから、月別の売上高、売上原価、売上総利益を算出します。
売上高は、XLOOKUP関数を使い、各商品の売単価（「商品マスタ」）と各商品の月別販売数量（「月別予想販売数量」）を掛け合わせて算出します。
例えば、A商品の2025年1月（E5セル）の売上高は次の計算式で算出します。

財務モデリングでは、各シートから条件に合致するデータを引っ張ってくるケースが多くあり、その場合には、XLOOKUP関数を使用します。
XLOOKUP関数については、こちらの記事で詳しく説明していますので、もし理解が浅い場合はご覧ください。
経理に必要なExcel関数とその実務での活用例（XLOOKUP関数）

売上原価も、XLOOKUP関数を使って算出しています。
例えば、A商品の2025年1月の売上原価（E10セル）は次の計算式で算出します。

売上総利益は、各商品の売上高から売上原価を差し引くことで算出します。

販管費、営業利益

財務モデリングにあたっては、コスト試算についてなるべく実際のコストに近くなるように精緻に計算式を設定します。ただ、今回は基本的な構造の理解に主眼を置いているため、今回のモデルでは、「人件費（販売員）」以外の販管費は固定費としシンプルにしています。
「人件費（販売員）」は、売上高×インセンティブ率とし、インセンティブ率は20%で設定しています。
営業利益は、売上総利益から販管費計を差し引くことで算出できます。

経常利益、当期純利益

営業外費用は「支払利息」のみとしています。
「支払利息」は、借入金の残高と各借入金の利率で計算されます。
借入金は、各借入金の条件（返済回数、利率等）、残高推移をわかりやすく明確にするために、別シートを設けて管理するのが一般的です。
今回のモデルでも、「借入金」シートを作り、各借入金の情報を一覧にしています。「支払利息」は「借入金」シートで計算した金額をもってきています。

今回作成した「借入金」シートは次のような形式にしています。
短期借入金と長期借入金とに区分し、最後に借入金計の情報を算出しています。
「支払利息」は借入金計の利息の行（57行目）から金額をもってきています。

経常利益以下は次の算式です。
今回のモデルでは、特別利益、特別損失、法人税等はゼロにしています。

経常利益=営業利益－支払利息
税引前当期利益＝経常利益＋特別利益－特別損失
当期純利益＝税引前当期利益－法人税等

以上で損益計算書（P/L）モデルが完成です。
次に貸借対照表（B/Sモデル）の作成を行います。

貸借対照表（B/S）モデルの作成

貸借対照表は企業の財務安定性を分析するための指標です。
損益計算書（P/L）モデルの作成は大体イメージできる人が多いですが、貸借対照表（B/S）モデルの作成はP/Lよりも少し難易度が高く、会計の考え方の理解がより必要になります。
B/Sというのは、バランスシートとよばれますが、それは貸方と借方がバランスしている、つまり貸借の金額が一致していることを意味します。どこかにミスがあると、貸方と借方の合計額が一致しなくなりますので、必ず、貸方と借方の合計額が一致しているか確認することが必要です。

今回のモデルでは、上述のとおり、B/Sモデルの作成にあたって、主要な科目である「売掛金」「棚卸資産」「買掛金」「借入金」を別シートにしました。「借入金」シートについては既に説明しましたので、残りの3科目のシートについて、以下説明します。

「売掛金」シート

売掛金発生の金額は、P/Lからもってきています。
売掛金は税込金額にする必要がありますので、P/Lの売上高×1.1となります。
例えば、P4セルの計算式は次のとおりです。

売掛金回収の金額は、売掛金発生額および各得意先の入金サイトによって決まってきます。
各得意先の入金サイトは「得意先マスタ」で設定していますので、そのデータを参照します。
今回のモデルでは、その入金サイトが変化した場合に、「得意先マスタ」の設定を変えることで、売掛金の回収時期Sも変化するようにしています。
その様な仕組みにする場合、OFFSET関数を使用します。
例えば、P11セルの計算式は次のとおりです。

OFFSET関数についてはこちらの記事で詳しく説明していますので、理解が浅い場合はこちらをご確認ください。
経理に必要なExcel関数と実務での活用例（OFFSET関数）

以上で、売掛金の発生金額と回収金額が算定され、当月の売掛金の増減金額（17行目の金額）が計算されます。この売掛金の増減金額を「BS」シートに連携させます。

「棚卸資産」シート

棚卸資産の増加は、「月別仕入数量」シートの情報と「商品マスタ」シートの情報から算出しています。「月別仕入数量」シートからは月別・商品別の仕入数量、「商品マスタ」シートからは商品毎の仕入単価の金額を参照し、それぞれ掛け合わし、消費税込みとするために1.1を掛けることで棚卸資産の増加額が算出されます。
例えば、P4セルの計算式は次のとおりです。

次に、棚卸資産の減少ですが、棚卸資産が減少するのは商品が販売されたときになりますので、「月別予想販売数量」シートと「商品マスタ」シートの情報から算出しています。「月別予想販売数量」シートからは月別・商品別の販売数量（棚卸資産の減少数）、「商品マスタ」シートからは商品毎の仕入単価の金額を参照し、それぞれ掛け合わし、かつ消費税込みとするために1.1を掛けることで棚卸資産の減少額が算出されます。
例えば、P11セルの計算式は次のとおりです。

以上で、棚卸資産の増加額と減少額が算定され、当月の棚卸資産の増減金額（17行目の金額）が計算されます。この棚卸資産の増減金額を「BS」シートに連携させます。

「買掛金」シート

買掛金の発生は、「月別仕入数量」シートの情報と「商品マスタ」シートの情報から算出しています。「月別仕入数量」シートからは月別・商品別の仕入数量、「商品マスタ」シートからは商品毎の仕入単価の金額を参照し、それぞれ掛け合わし、消費税込みとするために1.1を掛けることで買掛金の増加額が算出されます。
例えば、P6セルの計算式は次のとおりです。

次に、買掛金の減少ですが、買掛金が減少するのは支払ったときになりますので、発生した買掛金の金額と、当社の支払サイトによって算出されます。当社の支払サイトはB2セルで設定しており、今回は「1ヶ月」としていますが、B2セルの値を変えることで、買掛金の支払額も変わるように計算式を設定しています。
例えば、K13セルの計算式は次のとおりです。

以上で、買掛金の発生額と支払額が算定され、当月の買掛金の増減金額（19行目の金額）が計算されます。この買掛金の増減金額を「BS」シートに連携させます。

「BS」シート

貸借対照表の各金額は当月残高になるため、前月の残高からその科目の増減額を足すことで算出されます。
・「売掛金」「棚卸資産」「買掛金」の増減額は「売掛金」シート「棚卸資産」シート「買掛金」シートの増減の金額をもってきています。
・「短期借入金」「長期借入金」の金額は「借入金」シートからもってきています。
・「現預金」の金額は「CF」シートからもってきていますので、後述します。
・「利益剰余金」の金額は前月残高に「PL」シートの当期純利益の金額を足すことで算出しています。

少しややこしいのが「仮払消費税」と「仮受消費税」になります。
仮払消費税の計算式を組むためには、消費税が課せられる科目と課せられるタイミングを理解しておく必要があります。
今回のモデルでは、次の科目・タイミングで消費税が課せられます。

以上を踏まえて、仮払消費税の計算式は、例えば、N8セルの計算式は次のとおりです。

次に、仮受消費税は売上の金額に対して10%が課せられます。
したがって、仮受消費税の計算式は、例えば、N19セルの計算式は次のとおりです。

ちなみに、消費税の端数処理は、全て円未満切り捨てとしており、「ROUNDDWN関数」で端数処理しています。

その他、固定資産の各科目、資本金の額は今回は固定としています。
以上で、全ての科目の金額が算出され、最後に貸借がバランス（一致）しているか確認します。
33行目で、貸借が一致しているか確認する計算式を入れており、「OK」となっていれば、一致しています。

以上で、貸借対照表モデルは完成です。

キャッシュフロー（CF）モデルの作成

キャッシュフロー（CF）では、企業の資金流動性および安全性を分析します。
キャッシュフローは次の3区分で構成されます。

キャッシュフロー（CF）は、損益計算書（P/L）と貸借対照表（B/S）から数値をもってきて完成させます。
各科目は次のシートから数値をもってくるようになっています。
（投資CFの各科目は今回ゼロとしています。）

月末現預金残高は次の計算式です。

このCFで算出された「月末現預金残高」の金額とBSシートの「現預金」の金額を一致させます。
以上で、財務三表が完成です。

テスト（検証）

財務三表が完成すれば、各シートがきちんと連動し、正確な数値が算出できているか確認します。
このテストは主に次の様な方法でおこないます。

ダッシュボードの作成

テスト（検証）で正しくワークしていることが確認できれば財務三表は完成です。
ただ、財務モデルの中でも、特に必要なデータをピックアップし、さらにそれらを視える化した「ダッシュボード」を作成するのが一般的です。

今回サンプルとして、この様なダッシュボードを作成しました。

財務シミュレーションモデルを活用した経営戦略の策定

財務シミュレーションモデルを活用すると、以下のような戦略を立てることが可能になります。

動画による解説

損益計算書シミュレーションモデル、および財務三表シミュレーションモデルについてYoutube動画で解説しています。
よろしければこちらもご覧ください。

おわりに

財務三表のシミュレーションモデルをエクセルで作成することで、企業の財務状況を多角的に分析し、戦略的な意思決定をサポートすることが可能となります。本記事で紹介した手法を活用し、実務に役立てていただければ幸いです。
※関連記事
PEファンドが出資会社の財務経理責任者に求めるスキルとは？財務（LBO）モデルの構築手法と成功のポイント