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

2014年12月1日

URLをコピーしました！

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

1 決算書だけでも資金繰り表だけでもダメ
2 会計データと資金繰りの関係
3 Excelで資金繰り表を作るアイデア
4 まとめ

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

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

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

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

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

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

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

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

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

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

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

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

次のような会計データ（仕訳）を準備します。
スクリーンショット 2014 12 01 12 56 44

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

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

資金繰り表の入金欄へ17万円を入れて、項目名は、「売掛金」とします。
こうすれば、売掛金の入金で資金が17万円増えたということがわかるのです。
スクリーンショット 2014 12 01 12 59 58

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

同様に、11月を計算すると、30万円の入金と12万円の支払いがあり、差し引き18万円が増えています。
10月末の17万円+18万円で、11月末の残高は35万円です。
スクリーンショット 2014 12 01 13 04 47

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

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

スクリーンショット 2014 12 01 13 10 10

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

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

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

まず、科目が資金繰りに関係あるかどうかを判定します。
入力するのは、借方（左）に［普通預金］があれば、［入金］、貸方（右）に［普通預金］があれば、［支払］と表示する数式です。
スクリーンショット 2014 12 01 13 14 04

結果、E列の［項目］欄には、こう表示されます。
スクリーンショット 2014 12 01 13 15 43

②相手科目を入れていく

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

こう表示されます。
スクリーンショット 2014 12 01 13 18 48

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

下ごしらえが終わったらピボットテーブルで集計します。
集計するのは、縦に、項目、相手科目、横に日付です。
スクリーンショット 2014 12 01 13 21 06

④VLOOKUP関数で連動

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