Excelで会計ソフトのデータから資金繰り表を作る方法［入門編］

資金繰り表をExcelで作る方法として、基本的な考え方をまとめてみました。

 

決算書だけでも資金繰り表だけでもダメ

決算書、というよりも日々の業績の確認は欠かせません。
10月の成績はどうだったか、11月の成績がどうだったかをきちんと把握し、次に活かさなければいけないからです。
（私の11月分は今日、この後にまとめます）

決算書、日々の数字は、取引が発生したとき・確定したときに計上します。

たとえば、10月に、その月の売上が30万円あり、その入金が11月にある場合は、

10月　売掛金/売上高　　30万円（30万円の売上が発生した）
11月　普通預金/売掛金　30万円（30万円の売上代金が振り込まれた）
という処理です。

これを、
11月　普通預金/売上高　30万円
としてしまうと、一見楽ですが　月々の売上を正しく把握できません。

前者の場合、10月の売上高は30万円、後者の場合は、10月の売上高が0円となってしまうのです。

前者の方法で集計するのが、決算書。
税金（法人税など）は、決算書を元に計算するため、こちらを優先して作るケースの方が多いのですが、本当に大事なのは、資金つまりお金がいくら残っているか、いくら増えたか、いくら減ったかです。

決算書の利益がマイナスでも、会社はつぶれませんが、お金がなくなったらつぶれます。

決算書を作る一方で、資金繰り表も作っておかなければいけません。
しかし、この資金繰り表は作るのが大変です。
会計ソフトやシステムで出せる場合もありますが、まともに作ろうとすると時間がかかりすぎます。

私は、会計データを利用して、資金繰り表を作っていますので、その方法をご紹介します。
複雑な部分もありますので、ご紹介するのは、ごく基本的な方法です。

 

 

会計データと資金繰りの関係

次のような会計データ（仕訳）を準備します。

最終的な資金繰り表はこのようなものです。

この2つのつながりをまずとらえておきましょう。
1つ目の売掛金/売上　30万円というデータは、資金繰りには関係ありません。お金が動いていないからです。
2つ目の普通預金/売掛金　17万円というデータは預金が17万円増えているので資金繰りに関係あります。

資金繰り表の入金欄へ17万円を入れて、項目名は、「売掛金」とします。
こうすれば、売掛金の入金で資金が17万円増えたということがわかるのです。

次の仕入高/買掛金12万円は、資金繰りに関係ありません。
結果、10月は、0からスタートしたと仮定すると、17万円増え、0円減って、残高が17万円となるのです。

同様に、11月を計算すると、30万円の入金と12万円の支払いがあり、差し引き18万円が増えています。
10月末の17万円+18万円で、11月末の残高は35万円です。

11月の数字（試算表）を見ると、売上高が50万円、仕入高が20万円、差し引きで30万円の利益となっています。
資金繰り表は、差し引き18万円の増加です。
30万円の利益だけど、18万円の資金増。この事実をつかんでおかなければいけません。

会計データすべてを集計したのが、決算書・試算表、会計データのうち、資金の増減に関係あるものを集計したのが資金繰り表なのです。

 

 

Excelで資金繰り表を作るアイデア

このしくみをふまえて、Excelで加工していきます。

①資金繰り表に関係があるかどうかを判定する

まず、科目が資金繰りに関係あるかどうかを判定します。
入力するのは、借方（左）に［普通預金］があれば、［入金］、貸方（右）に［普通預金］があれば、［支払］と表示する数式です。

結果、E列の［項目］欄には、こう表示されます。

②相手科目を入れていく

集計したときに、「どんな理由で資金が増減したか」をみるため、相手科目を入れていきます。
入れるのは、「もし、E列が［入金］だったら、右側（貸方）の科目を、［支払］だったら、左側（借方）の科目を表示するというものです。

こう表示されます。

③ピボットテーブルで集計

下ごしらえが終わったらピボットテーブルで集計します。
集計するのは、縦に、項目、相手科目、横に日付です。

④VLOOKUP関数で連動

見栄えをよくするため、月初・月末の残高を計算するために、VLOOKUP関数を使ってフォーマットへ連動させます。
（SUMIFS関数を使う方法もありますが、私はこの方法でやっています。）
事例では、説明のため同じシートで連動させていますが、実際には、シートを分けた方がいいです。

まとめ

こういったフォーマットを作っておくと、会計データを貼り付け、ピボットテーブルを更新するだけで資金繰り表ができます。
実際に使うには、
・投資、売却、借入、返済といった項目を作る
・入金、支払という項目ごとにピボットテーブルを作る（ミス防止のため）
といったこともやっています。

まずは今回の事例で基本的な流れをつかんでいたければと思います。

 

 

---

---

---

---

【編集後記】
昨日は、また別のチームメート（3月にデビュー）とトライアスロンバイクの下見へ。
喜んでいる姿を見て、私も新車を買おうかと傾きつつあります。
資金繰り計画中です。。

【昨日の1日1新】
※詳細は→「1日1新」

新チームメイトとスイム、バイク下見
トライアスロンショップ近くの中華屋
エゴマ油