エクセルで資金繰り表を作る方法

前回の記事で、
エクセルで分析するための弥生会計への仕訳の入れ方
というのを書きました。

今回は、その実例の1つとして、
帳簿から過去の資金収支を表にまとめた「資金繰り表」を
自動作成する方法を見ていきたいと思います。

資金繰り表を作成する方法

作成法そのものは非常に単純です。

  1. 補助科目を適切に分けて、
    「この勘定科目+補助科目ならば、資金繰り表のここに表示する」
    という対応関係が明確になるようにする
  2. 仕訳を入れるときには、1:1仕訳の形で入れる
  3. 現金・預金の元帳をエクセルにエクスポートする
  4. エクスポートしたデータを使いやすい形に加工する
  5. (面倒であれば、省略可)勘定科目・補助科目から、
    vlookup関数で対応する資金繰り表の項目を導出
  6. ピボットテーブルで集計する

これで、資金繰り表ができます。

具体的な操作は、次のような感じです。

例えば、
弥生会計で次のようなデータが入っていた場合の操作を見てみます。

shikinguri_0_1

※弥生会計でなくても、
総勘定元帳のデータさえ入手できれば、
どんな会計ソフトでも、同じ考え方で処理できます。

1.現金・預金の元帳をエクセルにエクスポートする

総勘定元帳の画面(科目はなんでもいい)から、
現金・預金関連科目をエクスポートします。
(形式は何でもいいのですが、
 とりあえず、汎用形式(タブ区切り)でエクスポートします)

できあがったエクセルファイルを開くと、次のような感じになります。

shikinguri_1_1
2.エクスポートしたデータを使いやすい形に加工する

まず、
A列が「明細行」となっている行以外は使わないので、
他の行を全て削除します。
エクセルで空白行を削除する方法のように、
 オートフィルタを使って、[明細行]以外の行だけを選択し、
 まとめて削除するのが簡単だと思います。

また、不要な列も削除しましょう。

通常は、
日付、相手勘定科目、相手補助科目、借方金額、貸方金額、摘要
程度を残していれば、十分だろうと思います。

今回は、考え方の解説が趣旨ですので、
相手補助科目・摘要も削除して、
シンプルな表にしてしまいます。

shikinguri_1_2
3.勘定科目・補助科目から対応する資金繰り表の項目を導出

勘定科目・補助科目と、資金繰り表の対照表を作って、
vlookup関数で、資金繰り表の科目を導出します。
※資金繰り表科目の並べ替え用に、
 最初2桁に科目番号のようなものを振っておくと便利です

また、借方金額-貸方金額で「差引」を計算しておきます。

shikinguri_1_3
4.ピボットテーブルで集計する

最後に、ピボットテーブルで集計します。

  • 値:差引
  • 行:資金繰表科目
  • 列:日付(※年・月でグルーピングする)

を持ってくると、
レイアウトが資金繰り表に近くなります。

shikinguri_1_4

ここまでできれば、
あとは、見栄えを整えたり、
残高の情報を入れ込んだりすれば、資金繰り表は完成です。

元仕訳が、きちんとしていれば簡単だが、、、

このように、
元仕訳がきちんとしていれば、
資金繰り表の集計に、手間はほとんどかかりません。

ただし、
「元仕訳をきちんとする」
というのが、慣れないと非常に大変です。

資金繰り表作成に、手間をかけたくない、という場合は、
入力する仕訳のルールをどうするか、
徹底的に考えておくことをおすすめします。

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。