誰かのための資金繰り予定表のたたき台
お陰様でフォロワー数が900を超え、1000が見えてきました
— uk_ak@情報管理部門立ち上げ (@ukak01026431) December 22, 2019
1000を超えたらなんかフォロワーの皆さんの益になる御礼的な事ができないかなと思案中
いつも役に立つtwとかしてないのでね
お陰様でフォロワー数が1,000アカウントを超えました。ありがとうございます。
1,000アカウント記念に何をさせてもらおうかと考えた末に、企業にとって大事な資金繰りについて初noteを書くことにしました。といっても教えるというようなものではなく、私がどうやって資金繰り予定表を作成しているのかをただ晒すだけです。ですから、このやり方が正しい、というものではありません。
まったくもって洗練した作り方はしていないので、まずは資金繰り予定表作成未経験の方の踏み台やたたき台に。また、ベテランバックオフィスの皆さんの反面教師、酒の肴、業務改善の手がかりにでもなれば幸いです。そしてもっといい作成方法があったら教えて下さい。
なお、作成例に使用した数値はすべてダミーであり、整合性はありません。
1. 資金繰り予定表作成の前提
＜作成の目的＞
ネットで検索すれば資金繰り予定表のフォーマットはいろいろ出てくると思いますが、私が作っている資金繰り予定表はそれらよりも収支項目がかなり多いと思います。何故なら、私の資金繰り予定表の作成目的が、ただキャッシュの動きを見る・報告するだけでなく、具体的にキャッシュの出入り額をコントロールできるようにする為だからです。
私は以前、毎月現預金残高が一番底のときには数十万円しかないという状況が続く会社に勤めていました。実際に数カ月給与遅配したこともありました。そんな状況では、何を支払い、何の支払を止めたりすると底を乗り切れるか計画する必要がありました。これをするには収支項目の細かい資金繰り予定表が不可欠だったのです。この頃の資金ショート・フォビアがあるため、面倒でも粒度の細かい資金繰り予定表作成が今でもやめられないのです。
なので、特に資金に困っておらず、単にキャッシュの動きだけ見られればいいというのであれば、私の資金繰り予定表のように多い収支項目は不要だと思います。
＜エクセルの難易度＞
なるべく難しくない関数や機能を使って作成するようにしています。この業務が私の手から離れた場合、エクセルがあまり得意ではない人でも使用できるようにするためです。
私自身、エクセルが大して使いこなせているわけではないですが、現在の勤め先では私以上のエクセルの使い手がほとんどいないのが実情です。
では以下に、実際の私の作成手順から会社独自の処理手順を省き、少し汎用性を持たせた作成方法を記述します。
2. エクセルシートの構成
資金繰り予定表はエクセルで作成しています。まず最低限必要なシートは以下の４つです。
①現預金マスタ ②収支科目マスタ ③収支データ ④資金繰り予定表
①と②は③の作成を助けるマスタを記述したシート、③は集計の元となるデータ格納シートで、④は③を資金繰り予定表のフォーマットで集計したシートになります。
また、予算を資金繰り予定表に反映させ、未来の資金繰りを予想するために利用するシートは以下の２つです。
⑤予算（月次推移） ⑥予算比較
あと、④資金繰り予定表以外にも、参考に作成している集計用シートが以下の３つです。
⑦月度別残高 ⑧月度別口座別残高 ⑨口座別月間日次推移
では、それぞれのシートの内容を簡単に説明します。
3. 現預金マスタ
現預金の口座をリストにしたものです。
会計ソフトのデータと連動する可能性を想定し、Ｂ列～Ｅ列は会計ソフト（私の使用会計ソフトはPCA）の勘定科目マスタと補助科目マスタを引用しています。もし会計ソフトと連動する必要がなければＡ列とＦ列のデータだけでいいと思います。なお、入力規則として使用するため、Ｆ２：Ｆ８を「現預金リスト」として名前登録しています。
余談ですが、実際の私の資金繰り予定表はもっと口座数が多いです。金額は少ないのですが、いろんな金融機関との付き合いで借入用の口座がいくつもあったりします。
4. 収支科目マスタ
収支の項目をリストにしたものです。前述したとおりネット検索して見つかる資金繰り予定表の作成例より項目が多めです。これでも今回のnote用に項目を減らしました。実際に私が作っているものはさらに収支項目が多いです。
100番台は営業活動ＣＦの収入です。私は「受取手数料収入」「家賃収入」などの事業別にしていますが、会社によっては「現金売上」「売掛金回収」という入金種別にしたり、「当月期日」「前月期日」などの回収サイト種別にしているところもあるかと思います。
200番台は営業活動ＣＦの支出、301～312は投資活動ＣＦ、321～332は財務活動ＣＦの項目です。
900番台は現預金口座間の振替の際に使用しています。
資金に困っておらず大まかにお金の流れが掴めればいいのであれば、営業活動ＣＦの支出項目は「原価支払」「人件費支払」「税金支払」「特別支出」「その他支払」程度の項目数で十分かもしれません。
なお、入力規則として使用するため、Ｃ２：Ｃ４１を「収支科目リスト」として名前登録しています。
5. 収支データ
収入・支出の情報を格納するシートです。
A列は月度、B列は収入・支出のあった日付を入力します。
Ｃ列は「現預金リスト」と名前定義したものを利用し、リストとしての入力規則を設定しています。Ｃ列を入力するとindex&match関数でD列～Ｈ列に現預金リスト_シートからデータが引用されるようになっています。
Ｉ列も同様に「収支科目リスト」として名前定義したものをリストとして入力規則が設定されています。こちらも入力するとindex&match関数でＪ列～Ｋ列に収支科目リスト_シートからデータが引用されます。
Ｌ列（内容）は収入・支出の内容、Ｍ列（予算科目名）はその収入・支出が会計ソフトでどの科目として扱われるのかを入力します。
入金の時にはＮ列（収入）に、支出の時はＯ列（支出）に金額を入力します。もしお使いの会計ソフトが貸借（左右）の別なく金額を入力するタイプであれば、M列とN列は１列にまとめてよいと思います。
＜過去の実績データの入力＞
過去の実績の収支データをこのシートに反映するには、一行一行手入力も可能ですが、面倒なので、会計ソフトの総勘定元帳もしくは補助元帳をCSV形式で書き出したものを貼り付けています。このシートに会計ソフトの勘定科目コードなどを入れる列（Ｅ列～Ｈ列）があるのはそのためです。Ｅ列～Ｈ列をフィルタで絞り込むなどして現預金リスト列など（Ｃ列～Ｄ列）を入力することができます。
＜未来の予算数値の入力＞
資金繰り”予定”表ですので、まだ実績のない未来の収支データについても入力が必要です。まずは毎月定期的にある入金・支出のひと月分の収支データを、月度（Ａ列）と日付（Ｂ列）を変えて年度末の月分までコピーします。
次に、年払い、半年払い、四半期払いなどの入金・支出のデータを入力します。
しかし、これだけではただの毎月の実績数値＋年払い等の収支しか反映されません。予算は業績成長などを見込んで売上・経費ともに変化（主に増加）していきますので、その増加分を収支データに追加しなければなりません。
そこで、月次推移での予算数値が記載されたシート（予算（月次推移）_シート）と、予算と収支データ_シートの予算科目名（Ｍ列）別に集計した数値とを比較するためのシート（予算比較_シート）を作成しています。
上図は、予算（月次推移）_シートです。
下図は、収支データと予算数値を比較し、大きな乖離がないかをチェックするためのシート（予算比較_シート）です。
Ｃ列には、予算（月次推移）_シートから乖離をチェックしたい月度の予算数値をコピーしています。Ｄ列ではそれを税込金額に計算式で置き換えます。実際には税込の金額が現預金口座に出入りするからです。
Ｅ列は、Ｅ２の月度、Ａ列の勘定科目名を集計条件として、SUMIFS関数で収支データ_シートの収入列および支出列を集計した数値が入ります。
Ｆ列（差列）では、Ｄ列（予算(税込）列）とＥ列（収支データ列）の差額が計算されます。この差額が大きいものについて、収支データ_シートにデータ行を追加し、差額を入力します。
例えば、１月度の支払手数料における予算数値が収支データの集計値より1,000,000円多かった場合、収支データ_シートには
月度（2020年1月度）／日付（2020/1/31）／現預金リスト（3 AB銀行_CD支店）／現預金マスタID_（3）／勘定科目コード（1310）／勘定科目名（普通預金）／補助コード（2001）／補助科目名（AB銀行_CD支店）／収支科目リスト（271 その他諸経費支払）／収支科目コード（271）／収支科目名（その他諸経費支払）／内容（予算差額）／予算科目名（支払手数料）／収入（空白）／支出（1,000,000）
などと入力しています。
なお、売上に関しては予算数値をそのまま反映させていますが、費用の予算数値が収支データより少なかった場合は基本的に収支データ_シートへの反映はしていません。
6. 資金繰り予定表
収支データ_シートを集計した資金繰り予定表です。
１行目の月度と、C列の収支科目コードを集計条件として、SUMIFS関数で収支データ_シートを集計しています。Ｅ２の前月繰越は手入力です。
これでひと通り完成ですが、資金繰りの数値を他の観点から簡易に捉えるために作成している集計用シートが他に３つあります。
7. その他の集計用シート
＜月度別残高＞
月度別に収支データ_シートを集計したものです。
月度期間の情報としてＢ列（月初列）とＣ列（月末列）を作ったのは、Ａ列～Ｃ列を月度マスタとして利用できるようにするためです。また、入力規則として使用するため、Ａ２：Ａ１３を「月度リスト」として名前登録しています。
Ａ列（月度列）を集計条件として、SUMIFS関数で収支データ_シートからＥ列（収入列）とＦ列（支出列）を集計しています。
Ｈ列（試算表列）は、月次の実績数値を入力し、収支データ_シートに実績と相違（Ｉ列：差列）したデータが入力されていないかをチェックするためにあります。
＜月度別口座別残高＞
月度別＆口座別に集計したシートです。あらかじめ１年分の集計表を作成しています。
Ａ列（月度列）とＢ列（現預金マスタID列）を集計条件として、SUMIFS関数で収支データ_シートからＥ列（収入列）とＦ列（支出列）を集計しています。
こちらもＨ列（試算表列）に月次の実績数値を入力し、収支データ_シートに実績と相違（Ｉ列：差列）したデータが入力されていないかをチェックしています。
＜口座別月間日次推移＞
指定した月度＆口座の一月分の日次推移を確認する集計シートです。具体的に何日にどの口座が資金が足りなくなり、別の口座から預金振替をしなければいけないのかをチェックしています。
Ｂ１は、「月度リスト」と名前定義したリストを入力規則として設定しています。Ｂ２を入力するとindex&match関数でＡ３（上図では2020/1/1）が口座別残高_シートから月初列のデータが引用されます。また、Ａ４～Ａ３３はＡ２から１日ずつ日付が増えるように計算式を入れています。
Ｅ１もまた「現預金リスト」と名前定義したものを利用し、リストとしての入力規則を設定しています。Ｅ１を入力するとＨ１に現預金マスタ_シートの現預金マスタIDにあたる数値が計算式によって入ります。
Ｂ３の前日残高は、Ｂ１の月度とＨ１の現預金マスタIDを集計条件として、SUMIFS関数を利用して月度別口座別残高_シートから引用しています。
Ｈ１の現預金マスタIDとＡ列（日付列）を集計条件として、SUMIFS関数で収支データ_シートかＣ列（収入列）とＤ列（支出列）を集計しています。
最後に
言うまでもないことでが、資金繰りの状況を把握することは、企業が継続的に存在するためにとても重要です。しかし、私も今回のnoteを書く前に参考にできるものはないかとネットで検索してみましたが、実際の作成方法についてはあまり詳細な記載をしているものが見つかりませんでした。
たとえ一人でもいいので、お粗末ながらもこのnoteが誰かの資金繰り予定表を作成するきっかけになったり、業務改善のヒントになったりしてくれると嬉しいです。