週刊/税理士ジョーの銀行融資マガジン 購読受付中

手元流動性比率(現金預金÷平均月商)の推移グラフをつくる【資金繰り・銀行融資に役立つExcel】

手元流動性比率(現金預金÷平均月商)の推移グラフをつくる【資金繰り・銀行融資に役立つExcel】

資金繰りの良し悪しをあらわし、銀行融資の受けやすさにもかかわる指標「手元流動性比率」について。常時把握できるようにExcelを使った、推移グラフのつくり方についてお話ししていきます。

目次

まずは2倍、できれば3倍。

自社の資金繰りを考えるうえで、会社が把握しておくべき「指標」はいろいろありますが。そのなかから、ひとつ。重要な指標として「手元流動性比率」を取りあげてみましょう。手元流動性比率とは、算式でいうと次のとおりです↓

手元流動性比率の算式

手元流動性比率 = 現金預金 ÷ 平均月商

上記のとおり、現金預金が平均月商(年間売上高÷12ヶ月)の何ヶ月分あるか? が「手元流動性比率」のあらわすところになります。

その「手元流動性比率」は高いほうが、つまり、現金預金が多いほうが資金繰りとしては安全です。とはいえ、その「目安」はいかほどなのか? 結論、「まずは2倍、できれば3倍」です。「1倍を下回る」ようだと危険だと言えます。

手元流動性比率が1倍を下回る、つまり、手元の現金預金が平均月商1ヶ月分を下回るようだと、多くのケースで資金繰りが忙しくなるはずです。また、ちょっと入金が遅れたりすると、たちまち資金ショート… ということもあるでしょう。

そういった危険を回避できる目安が「まずは2倍」です。そのうえで、さらに安全を考慮して、「できれば3倍」ということになります。これらの目安について、銀行もまた同じような見方をしているものです。

そのため、手元流動性比率が1倍を下回るようだと、極端に融資を受けにくくなることは覚えておきましょう。言うまでもありませんが、資金繰りが厳しい会社であり、「貸したおカネを返済してもらえないかも」と銀行は考えるからです。

そこで、会社は常に、手元流動性比率を把握しておく必要があります。把握したうえで、手元流動性比率が1倍を下回るようなことになる前に、資金調達(銀行融資)に動く。また、さらに手元流動性比率を高めるための取り組みが大切です。

これらをふまえて、まずは、手元流動性比率を常時把握できるようにしましょう。具体的には、Excelを使って手元流動性比率を計算、その推移をグラフにしておくことです。本記事では、そのグラフのつくり方をお話ししていきます。

ちなみに、できあがりのグラフはこんな感じです↓

image1

Excelを使った、手元流動性比率の推移グラフのつくり方

はじめに、各月の「現金預金」と「月商(売上高)」を入力しましょう。最新のデータがある月を、ここでは「2022年1月」とします。

このあと、「平均月商」を「直前 12ヶ月の売上高の平均」で計算をするため、ここでは「2021年1月分以降」のデータを入力している点がポイントです↓

image2

上図では、2022年12月までの手元流動性比率を管理できるように、「2022/12」までの枠を用意しています。続いて、手元流動性比率を計算するのに必要な「平均月商」を計算しましょう。

平均月商は、「直前 12ヶ月の売上高平均」としますので、計算できるのは直前 12ヶ月分のデータがある「2021/12」以降です。そこでまずは、2021年12月の平均月商を計算するために、セルD13に次のような算式を入力します↓

image3

上図の「赤枠内」のとおり、D13 には「=AVERAGE(C2:C13)」の算式を入力します。AVERAGEは、特定の範囲内にある数字の平均値を計算する関数です。上図では、C2からC13までの12ヶ月分の月商を、範囲に指定しています。

結果として、D13(青枠内)には「128」という計算結果が表示されました。

あとは、D13の算式をD25まで、下方向にコピーをしましょう。これで、2022年1月以降の月商データを入力すれば、自動的に平均月商が計算できるようになります。コピーした結果がこちらです↓

image4

上図のとおり、平均月商が 2022年12月分(赤色枠内)まで計算できるようになりました。ちなみに、2022年12月の平均月商を計算する算式は「=AVERAGE(C14:C25)」です。直前 12ヶ月分の月商で計算していることがわかります。

続いては、「最新月(最新データが入力されている月)」の判定です。このあとの作業を進めるにあたって、「最新月」がいつなのかが必要になります。セルG1を使って、次のような算式を入力してみましょう(F1は「最新月」と手入力)↓

image5

上図の赤枠内のとおり、G1には「=INDEX(A:A,COUNT(C:C)+1)」と入力します。ちょっとわかりづらい算式かもしれませんが、順番に見ていくと。まず、COUNT関数を使って、C列(C:C)に入力されている「数値の個数」を数えています。

これは、月商(C列)の最新月データが、何行目にあるのかを数えるためです。ただし、C1が「数値」ではないため、個数に含まれていないため、「+1」することで、月商の最新月データが14行目(C14)にあることを計算しています。

そのうえで、INDEX関数を使って、A列(A:A)の14行目の値である「2022/1」をG1に表示するしくみです。これにより、あたらしく月商データが入力されると、G1のセルが入力された月に変化します。

続いて、グラフを作成するための元データ一覧をつくりましょう。次のとおりです↓

image8

G3からJ3までは手入力です。左から「現金預金」「平均月商」「2倍」「3倍」と入力します。次に、F4には赤色枠内のとおり、「=DATE(YEAR(G1),MONTH(G1)-11,DAY(G1))」という算式を入力しましょう。

これは、最新月(G1)の11ヶ月前の月である「2021/02(青色枠内)」を計算するための算式です。YEAR(年)、MONTH(月)、DAY(日)に分解したうえで、月だけ「−11」をして、DATE関数でふたたびつなぎあわせる、というしくみになります。

image9

続いて、ひとつ下のセルにあたるF5には、赤色枠内のとおり、「=DATE(YEAR(F4),MONTH(F4)+1,DAY(F4))」という算式を入力しましょう。F4セルの「翌月」を計算するための算式です。「MONTH(F4)」に「+1」をしている点がポイントです。

ここまでできたら、F5からF15まで下方向に算式をコピーをしましょう。直前 12ヶ月分の年月が、自動で表示できるようになります↓

image10

続いて、現金預金の列(G列)に、各月の金額を「転記」します。G4に赤色枠内のとおり、「=VLOOKUP(F4,A:B,2)」という算式を入力しましょう↓

image11

これによって、G4には「2021/02」の現金預金の金額である「130」が自動的に転記されます(青色枠内)。

「F4」の値を、「A列・B列」のなかから探し出して、その値があるセルの「2列め(B列)」の値を拾い出すというのが、VLOOKUP関数のしくみです。ここまでできたら、F4の算式をF15まで下方向にコピーしましょう↓

image12

これで、直前 12ヶ月分の現金預金の金額を自動転記できるようになりました。

続いて、平均月商の列(H列)に、最新月の平均月商(D14)の金額を転記します。H4に赤色枠内のとおり、「=VLOOKUP(G1,A:D,4)」という算式を入力しましょう↓

image13

「G1(最新月)」の値を、「A列〜D列」のなかから探し出して、その値があるセルの「4列め(D列)」の値を拾い出すというのが、VLOOKUP関数のしくみです。

ここまでできたら、H5には「=H4」と入力して、H5の算式をH15まで下方向にコピーしましょう。最新月の平均月商が 12ヶ月分、自動的に入力されるようになります↓

image14

続いて、手元流動性比率の目安として、平均月商の2倍と3倍の値を計算します。I4に、赤色枠内のとおり、「=H4*2」の算式を入力しましょう↓

image15

上図のとおり、I4には、平均月商(H4)の2倍の値が、自動的に入力されるようになります。あとは、I4の算式をI15まで下方向にコピーしましょう。

同じ要領で、J4には「=H4*3」の算式を入力。J4の算式をJ15まで下方向にコピーをします。できあがりは、次のとおりです↓

image16

ここまでできたら、さいごにグラフを作成しましょう。まずは、F3からJ15までを選択します↓

image17

続いて、「折れ線グラフ」を挿入します↓

image18

すると、次のようなグラフがあらわれます↓

image19

あとは、じぶんの好みで書式を整えて、冒頭のようなグラフをつくりましょう↓

image1

これで、最新月の「現金預金」と「月商」のデータを入力すれば、直前 12ヶ月の平均月商を元に、上図のようなグラフを自動的に作成できます。

このグラフを見て、まずは「平均月商の1倍(赤色点線)」を危険ラインとして、そこを下回らないように。銀行融資を受けることを含めて、資金繰りを検討しましょう。

そのうえで、まずは「2倍(黄色点線)」を目指す。できれば「3倍(青色点線)」を目指しましょう。いずれにせよ、「平均月商に対する現金預金(手元流動性比率)」の動きを把握できるようにすることです。

まとめ

資金繰りの良し悪しをあらわし、銀行融資の受けやすさにもかかわる指標が「手元流動性比率」です。その手元流動性比率を常時把握できるように、Excelを使って計算、推移グラフをつくっておきましょう。

手元流動性比率(現金預金÷平均月商)の推移グラフをつくる【資金繰り・銀行融資に役立つExcel】

この記事が気に入ったら
いいね または フォローしてね!

良い記事があればシェア
  • URLをコピーしました!
目次