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

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

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

決算書、というよりも日々の業績の確認は欠かせません。
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

まとめ

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

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





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

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

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




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』
###