エクセルで資金繰り表を作る方法
前回の記事で、
エクセルで分析するための弥生会計への仕訳の入れ方
というのを書きました。
今回は、その実例の1つとして、
帳簿から過去の資金収支を表にまとめた「資金繰り表」を
自動作成する方法を見ていきたいと思います。
資金繰り表を作成する方法
作成法そのものは非常に単純です。
- 補助科目を適切に分けて、
「この勘定科目+補助科目ならば、資金繰り表のここに表示する」
という対応関係が明確になるようにする - 仕訳を入れるときには、1:1仕訳の形で入れる
- 現金・預金の元帳をエクセルにエクスポートする
- エクスポートしたデータを使いやすい形に加工する
- (面倒であれば、省略可)勘定科目・補助科目から、
vlookup関数で対応する資金繰り表の項目を導出 - ピボットテーブルで集計する
これで、資金繰り表ができます。
具体的な操作は、次のような感じです。
例えば、
弥生会計で次のようなデータが入っていた場合の操作を見てみます。
※弥生会計でなくても、
総勘定元帳のデータさえ入手できれば、
どんな会計ソフトでも、同じ考え方で処理できます。
- 1.現金・預金の元帳をエクセルにエクスポートする
-
総勘定元帳の画面(科目はなんでもいい)から、
現金・預金関連科目をエクスポートします。
(形式は何でもいいのですが、
とりあえず、汎用形式(タブ区切り)でエクスポートします)できあがったエクセルファイルを開くと、次のような感じになります。
- 2.エクスポートしたデータを使いやすい形に加工する
-
まず、
A列が「明細行」となっている行以外は使わないので、
他の行を全て削除します。
※エクセルで空白行を削除する方法のように、
オートフィルタを使って、[明細行]以外の行だけを選択し、
まとめて削除するのが簡単だと思います。また、不要な列も削除しましょう。
通常は、
日付、相手勘定科目、相手補助科目、借方金額、貸方金額、摘要
程度を残していれば、十分だろうと思います。今回は、考え方の解説が趣旨ですので、
相手補助科目・摘要も削除して、
シンプルな表にしてしまいます。 - 3.勘定科目・補助科目から対応する資金繰り表の項目を導出
- 4.ピボットテーブルで集計する
-
最後に、ピボットテーブルで集計します。
- 値:差引
- 行:資金繰表科目
- 列:日付(※年・月でグルーピングする)
を持ってくると、
レイアウトが資金繰り表に近くなります。
勘定科目・補助科目と、資金繰り表の対照表を作って、
vlookup関数で、資金繰り表の科目を導出します。
※資金繰り表科目の並べ替え用に、
最初2桁に科目番号のようなものを振っておくと便利です
また、借方金額-貸方金額で「差引」を計算しておきます。
ここまでできれば、
あとは、見栄えを整えたり、
残高の情報を入れ込んだりすれば、資金繰り表は完成です。
元仕訳が、きちんとしていれば簡単だが、、、
このように、
元仕訳がきちんとしていれば、
資金繰り表の集計に、手間はほとんどかかりません。
ただし、
「元仕訳をきちんとする」
というのが、慣れないと非常に大変です。
資金繰り表作成に、手間をかけたくない、という場合は、
入力する仕訳のルールをどうするか、
徹底的に考えておくことをおすすめします。