エクセルで資金繰り表（６ヶ月から1年先程度の長期予測）を作る
資金繰り表は、作成する期間により、作成方法が大きく変わります。
今回は、６ヶ月以降先程度の資金繰り表を作成するアイデアを紹介しようと思います。
６ヶ月から１年先といった程度の「遠い将来」の資金繰り表を作成する場合。一番問題となるのは「正確な基礎データ」が取りにくい、ということです。
資金繰り表作成に必要な「基礎データ」
半年から１年先程度の資金繰り表を作成したいという場合、基礎データとして、１年先程度までの下記のような金額などの情報が必要となります。
- 売上高の金額と入金タイミング
- 仕入高の金額と支払いタイミング
- 外注費の金額と支払いタイミング
- 家賃、給料の支払額
- その他の経費の支払額
- 税金の支払額
- 借入金の返済額
これらの情報が「完璧」につかめれば、完璧な資金繰り表が作れます。
でも、当たり前ですが、実際には完璧に掴むことはできません。
ですから、資金繰り表を作るときには、これらの金額をどうやって合理的に見積もるか？ということが最重要ポイントとなります。
よくある合理的な見積もりの仕方
ここでは、一番よくある見積もりの仕方を紹介します。
損益計算書の見積もり
まずは「損益計算書」関連項目の見積もりをしていきます。
- 売上高
-
社長や営業担当者が、営業の状況を見て見積もります。
根拠があればいいのですが、根拠がなければテキトーに（例:現状維持、毎月１０％アップ、毎月１０％ダウンなど）数字を作りましょう。
- 仕入高、外注費その他の変動費
-
売上高と各変動費の「比率」は、事業の状況が大幅に変わらない限り、大きくブレないケースが多いです。
そのため、売上高に対する「比率」の実績を使って推定することが多いです。
- 人件費
-
採用計画や残業計画に基づいて決めましょう。
なお、売上高が大幅に変わる場合には、人件費も増やさざるを得ないケースが多いので、売上高も考慮して違和感のない数字になるように気をつけましょう。
- 家賃などの固定費
-
毎月一定額で見積もるのが基本です。
当然、大きく増減するような個別要因があれば、見積もりに加味してください。
- 税金・社会保険料の支払い
-
源泉所得税、法人税・消費税の中間納付・期末納付、社会保険料などを見積もります。
見積もれないようであれば、税理士や社会保険労務士に手伝ってもらってください。
入出金の見積もり
先ほどの損益計算書の情報と、それ以外の情報から、入出金を見積もっていきます。
- 損益計算書の各項目から入出金を見積もる
-
売上、仕入、各種経費などは、損益計算書に計上すると同時に入出金があるわけではありません。
例えば、売上の場合「月末締め翌月末入金」であれば、損益計算書計上から１ヶ月遅れで入金されてきます。
このような「ずれ」を調整していきます。
取引先の構成割合が変わらなければ、過去の入金タイミングと同様に入金されるはずですので、過去の売上計上と入金のずれ具合から、将来の入金タイミングを見積もります。
支払側も同様に、過去の経費計上と出金タイミングのずれ具合から、将来の支払いタイミングを見積もります。
- 借入金の返済、設備投資など
-
借入金の返済、設備投資など、損益計算書に直接反映しない入出金についても、見積もりに織り込みます。
結局、何を見積もっているの？
このようなやり方だと、結局、本気で見積もるのは売上高だけ。
その他の数値の多くは、過去実績の数字をそのまま持ってくることになります。
資金繰り計算エクセルシートの例
さて、上記のような考え方に基づき、実際にエクセルシートを作成してみました。
シート上は、翌月～１年後までの資金繰りを計算できるようになっています。
でも、近い将来の資金繰り表は、売上・仕入実績などを反映してより精度の高い資金繰り表を作成すべきです。
そのため、今回のエクセルシートは、「確定データに基づいた資金繰り見込みが立てられない」期間（多くの会社の場合３ヶ月後から１年後）の資金繰り計算に使用してください。
このエクセルシートは次のような構成になっています。
全体的な情報
資金繰り表作成の基準となる月、その月末時点の現預金残高を入力します。
多くの場合、このエクセルシートに入力する「前月」の年月を入れます。
変動費の対売上比率
仕入、外注費、その他の変動費について、売上に対する割合を入力します。
売上高、固定費、税金、借入金、設備投資
売上高や人件費、家賃などの固定費の発生額を入力します。
また、税金の支払額や、借入金の元本返済額、設備投資支出額も入力していきます。
※出金はマイナスの数値で入力してください。
ここまで入力すると、将来月度の予測PLが作成されます。
入金・支払いタイミングの入力
売上高、仕入高や、各種経費について、入金・支払いをいつ行うか入力します。
具体的には「損益計算書への計上」に対して「入金・支払いが何ヶ月ずれるか」を合計が１００％になるように「パーセンテージで」入力していきます。
例えば、上の図だと、売上高は、次のような割合で入金されてくる前提で計算することになります。
- ５０％・・・翌月入金
- ３０％・・・２ヵ月後入金
- ２０％・・・３ヵ月後入金
資金繰り表の作成完了
これで資金繰り表が完成します。
ダウンロード
下記リンクよりダウンロードをお願いいたします。
→資金繰り長期予測シート ダウンロードページ
注意事項
計算結果が正しいかどうかは保障できませんので、あくまで自己責任でお使いください。