エクセル月次推移表で経営数値の年間の着地予測値を簡単に算出 【オンライン事務代行アウトソーシング・サービス 『ジム楽』ブログ】
事務や経理の代行のプロフェッショナル、オンライン事務代行アウトソーシング・サポート「ジム楽」のコッティです。
公認会計士・税理士として20年の経験から、会計税金などの数字とエクセルの組み合わせのスペシャリストを自負しています。
前回のブログでは、毎月の売上や粗利益、経費や営業利益など経営数値を前年同月と比較ができるエクセル月次推移表の作り方をお話しました。今回は、このエクセル月次推移表を使って年間の経営数値の着地予想を簡単に計算する方法についてお話します。
前回のブログについてはこちらをご参照ください。
売上や経費などの毎月の数字を前年と比較しながら一覧できるエクセル月次推移表
目次
１．経過した月の実績の累計額の列を作る
完成するとこのような表ができるというケースでお話します。
（１）合計数値などの重要な数字の列は、エクセルの一番左に入れると見やすい
前回のブログの状態では、B列から直ぐに前期と当期の毎月の数字が右から左に並んでいます。まず、この月次の数字の並びの左列の見やすいところに列の挿入行ってスペースを作ります。合計数値などを一番右に入れてしまうと、重要な情報である合計数値を見るある必要がある都度に右にスクロールする必要があり、非効率な結果になってしまいます。
まず、B列からG列を反転させたまま右クリックして列を挿入します。
次に、左上のほうに「期中参照列」「１」「２」「実勢累計」「前期」「当期」「増減」を入力します。
さらに、経過月である4月から6月までについて、期中参照列である3行目に前期は「１」を、当期は「２」を入力します。
（２）SUM関数とIF関数の組み合わせ
B7セルに、「=SUM(IF($H$3:$AT$3=1,$H7:$AT7,0))」と入力します。
IF関数の「IF($H$3:$AT$3=1,$H7:$AT7,0)」は、固定した（「$」はコピー＆ペーストをしても固定されたままという意味です）H3からAT3の間のセルが「１」ならば固定したH7からAT7のセルの値を認識する、という意味です。
これをSUM関数で囲っているので、IF関数で認識した結果を合計する、という意味になり、3行目に「１」を入力した前期について、7行目の売上高を合計する結果になります。
当期についても同様に、「=SUM(IF($H$3:$AT$3=2,$H7:$AT7,0))」と入力します。前期の「１」を「２」に変更しただけです。これで、前期と当期の実績累計値のセルの準備はできました。後ほど、一気にコピー＆ペーストして完成させます。
２．経営数値の年間の着地予想値を集計する
次に、上記のようにそれぞれのセルに「着地参照列」「着地当期」「２」を入力します。ついでに、実績累計の増減も入力しておきます。
そして、4行目の「着地参照列」に「２」を入力していきます。実績値がある4月から6月までは当期の列に、将来値である7月以降は計画列に「２」を入れていきます。また、計画値については、とりあえず簡易的に前期の数値を持ってきています（「＝前期のセル」を入力）。
当期着地の数値を出すE7セルに「=SUM(IF($H$4:$AT$4=2,$H7:$AT7,0))」と入力します。意味合いは、上記（２）と同様です。すると、4月から6月までの実績の合計に将来の7月から3月までの実績が合計され、年間の着地予想値が出ます。
３．年間の着地予想値は前年の年間数値と比べると意味が増す
年間の着地予想値だけが単独であってもあまりピンとこない可能性が高いです。一方、着地予想値と前年の年間実績値を比較すると、数字の意味がグンと感覚的につかみやすくなります。
そこで、F列に前年年間の実績欄を作り、F4セルに「１」を入力します。
そして、F4セルに「=SUM(IF($H$4:$AT$4=1,$H7:$AT7,0))」と入力すると共に、着地参照列である4行目の前期の各月の列に「１」を入れていきます。すると、前期の年間の実績値がF4セルに表示されます。
４．最後に一気にコピー＆ペーストすると効率的
B7からF7をコピーして、それより下のセルに一気に貼り付けます。するとこのように正しい数字が反映されます。
最後に、書式のコピーなどで見た目を整えて出来上がりです。
今回のエクセル・フォーマットが欲しいというかたは、お問い合わせフォームからご連絡ください。
お問い合わせフォーム
また、エクセルの書式のコピーについては下記のブログをご参照ください。
エクセルの見栄えを良くして事務作業力のレベルアップ
自社で行う振込・支払の処理や請求書発行、会計帳簿の作成などの事務作業やバックオフィス業務を効率化したい、減らしたいなどのご要望がございましたら、
初期費用ゼロにも関わらず、公認会計士事務所のノウハウで効率的で質の高いサービスをご提供するオンライン事務代行アウトソーシング・サポート『ジム楽』までお気軽にお問い合わせください。