エクセルで帳簿・資金繰り表を作ろう！個人事業主と法人に役立つエクセルの活用方法

企業にとって資金は「人間の血液」のような位置づけです。そのため、資金繰り表の存在意義があります。しかし、資金繰り表の作成は会計ソフトから自動作成できるとは限らないため、手間がかかる傾向にあります。そこで、手間を省くために資金繰り表をエクセルで作る方法について徹底解説します。

資金繰り表とは

事業活動に欠かせない資金繰り表のアウトラインについて説明します。

現金収支をまとめた表が資金繰り表

企業の現金や預貯金などの動きを示した現金収支をまとめた表が資金繰り表です。「物やサービスの動きを金額で示した損益計算書」や「一定時点の現金預金残高や借入金残高などの財政状態を示した貸借対照表」と異なり、企業版の家計簿ともいえる表です。

資金繰り表のひな形

資金繰り表の作成はひな形を決めるところから始まり、基本的に上段から次の順に表示します。

(1)経常収支

毎月の入金額と出金額を示します。上段に入金額の項目である売上入金、下段に出金額の項目である仕入高、外注費、家賃や給料などの諸経費、源泉所得税や社会保険料の預かり額の納付などを表示します。

(2)財務収支

銀行からの資金調達に関連する現金収支を示します。上段に現金支出の項目である借入金や借入金返済や定期預金などを、下段に臨時的な入金項目である銀行からの借入額や定期預金の解約額を表示します。

(3)現金預金残高

現金収支の動きの結果を示した現金預金残高を表示させ、現金収支のプラス・マイナスの結果を把握できるようにします。

 

また、資金繰り表はそれぞれの企業の実情に応じてカスタマイズすることができるのが特徴です。たとえば、設備投資や広告宣伝費用の投資が多額だったり頻繁に実施されたりする場合、投資に関する項目を上記の経常収支、財務収支と区分してひな形を作成することも可能です。

資金繰り表の種類

資金繰り表の種類について目的別と管理する期間別に分けて説明します。

(1)目的別

「過去の現金収支を示す実績資金繰り表」と「将来の現金収支をシミュレーションする予定資金繰り表」の2種類が存在します。

(2)管理する期間別

「月単位で現金収支を管理する月次資金繰り表」や「1日単位で現金収支を管理する日次資金繰り表」などがあります。

資金繰り表の作成方法は？

事業活動に役立つ資金繰り表は企業ごとに異なるため、会計ソフトのひな形でカバーしきれない傾向にあります。そのため、資金繰り表を自社使用にカスタマイズしたり、入出金を転記したり労力がかかります。

資金繰り表の作成方法の基本

資金繰り表のカスタマイズや入出金の転記の労力を最小限にするのにエクセルが役立ちます。そこで、エクセルによる資金繰り表の作成方法について説明します。

「実績資金繰り表→予定資金繰り表」の順に作成しよう
実績資金繰り表の作成方法

エクセルによる実績資金繰り表の作成方法はおもに2パターン存在します。

(1)エクセルのピボットテーブルで集計する方法

会計ソフトや銀行口座のデータなどをエクスポートして、ピボットテーブルで項目別に集計することができます。たとえば、売上入金を集計する場合、通帳データの預金取引をエクスポートして、入出金内容の「得意先」を行ラベルまたは列ラベルで追加すれば、売上入金の合計額を算出することができます。

 

 

 

さらに「取引日」も行ラベルまたは列ラベルで追加すれば、日単位での売上入金の把握も可能です。

 

(2)試算表から作成する

試算表から間接的に現金収支を計算することができ、慣れると上記(1)よりも資金繰り表の作成に手間がかからないのが特徴です。しかも、損益計算書や貸借対照表と比較することが可能で、利益と現金預金残高の関連性が把握でき、経営管理に便利です。

試算表から実績資金繰り表を作成する方法

経営管理に役立てるためにも試算表から実績資金繰り表を作成する方法をマスターしましょう。

現金取引の項目は損益計算書の金額をそのまま転記する

現金取引の勘定科目は特にエクセルの機能を使う必要がなく、最初に入力するのがポイントになります。具体的には損益計算書の金額と同額を資金繰り表の項目に入力します。また、手入力をする代わりに会計ソフトの試算表をエクスポートして、関数式により自動転記をすればさらに効率がよくなります。

売上入金の計算方法

そもそも入金の算式は次の通りであり、損益計算書の売上高と貸借対照表の売掛金(受取手形を含む)から間接的に求めます。
 

 

エクセルを用いる場合、前月末の売掛金残高・当月分の売上高・当月末の売掛金を入力します。たとえば、会計ソフトから前月末と当月末の売上高をエクスポートして、資金繰り表の売上入金の項目に関数を入れれば、自動計算することができます。

 

例)

貸借対照表のシートのC10に前月末の売掛金残高100万円、F10に当月末の売掛金残高120万円、損益計算書のシートのC5に当月分の売上高1,000万円が出力されている場合、資金繰り表の売上入金のセルに「貸借対照表のシートのC10＋損益計算書のシートのC5－貸借対照表のシートのF10」をプラスし、関数を入れれば、980万円が自動計算されます。なお、エクセル上に表示される算式は「貸借対照表!B25＋損益計算書!B10－貸借対照表!E25」になります。

 

 

 

 

なお、売上入金の入力方法は仕入代金や外注費の支出額の自動計算にも応用することができます。

試算表をエクスポートする際の注意点

会計ソフトの試算表をエクスポートする際、勘定科目を追加した場合には出力される行がズレるため、関数を入れなおさなければ正しい金額が計算されません。前述の売上入金の自動計算を例にすると、当座預金という勘定科目を追加した場合、出力される売掛金は貸借対照表のシートのC11とF11になってしまいます。

予定資金繰り表の作成ポイント

予定資金繰り表は手入力をすることで、体感ベースで現金収支の傾向がつかめます。それが作成するポイントです。

測数値を入力するポイント

次の順番で入力すると効率よく予定資金繰り表が作成できます。

(1)毎月の支出額や入金額が固定されている・変動が少ない項目

家賃などの経費や毎月同額の受取報酬はもちろん、事務所の水道光熱費などの毎月の変動が小さい項目も毎月同額とみなします。たとえば、電気代の場合、多少の変動があっても前期の月平均額と同額を予定資金繰り表に入力します。

(2)売上入金

損益計算書の売上高の入金予定月を予定資金繰り表の項目に入力します。

(3)仕入代金・外注費の支払い

損益計算書の仕入高や外注費の支払予定月を予定資金繰り表の項目に入力します。

資金計画を予定資金繰り表に入力する方法

投資や納税など次の項目の資金計画をする場合、予定資金繰り表の現金預金がマイナス残高(資金ショート)にならないようにするのが必須です。万が一、資金ショートしてしまう場合、銀行からの借入を検討するなどの資金調達が必要になります。

まとめ

実績資金繰り表はエクセルの機能を駆使して効率よく作成し、予定資金繰り表は手入力などにより多少手間をかけてでも現金収支の傾向を把握するのがポイントです。また、エクセルで作成する場合、会計ソフトのデータに依存しているため、正しい仕分けなどの会計処理の精度が実績資金繰り表の質はもちろん、予定資金繰り表の正確性にまで影響を及ぼします。そのため、資金繰り表の作成は税理士などの専門家に依頼することをおすすめします。

TAX（税金）ライター。会計事務所で約10年間の勤務により調査能力を身に付けた結果、企業分析の能力では高い定評を得、法人から直接調査を依頼される実績も持つ。コーチングスキルを活かした取材力で、HP・メディアでは語られない発言を引き出すのが得意。