お役立ちコラム

お役立ちコラム

Excelで既存店前年比を計算する

売上高の既存店前年比は業態のトレンドを測るのに重要な指標です。

基本の計算式は、

(既存店の当年売上計)÷(既存店の前年売上計)

なので難しくはないのですが…

  • データを差し替えるだけで計算を自動化させたい
  • 既存店対象の確認を簡単にしたい
  • 月次だけではなく日次や週次など任意の期間で出したい
  • 同月対比、同日対比だけではなく同曜日対比で出したい

となると面倒なものです。
以下、それぞれのポイントを解説します。

既存店前年比の計算を自動化させたい

毎回当年売上の横に前年売上を貼り付けて割り算の関数を入れるのも地味に手間がかかります。
そこでPOSもしくは会計システムの数値から次のようなデータを作成し、

店舗別月別売上データ

「既存/非既存」フラグをつけます。

これは次のような内容で決めていることが多いです。
・ 開業から13ヶ月目以降、閉店前月まで
・ 改装に伴う休業期間のある月は非既存店とする

既存店フラグをつける

「日付」については「2021/5/1」と日付形式にするのをおすすめします。
「202105」と年月の6桁の数値にするやり方や、「年」と「月」の2列にしてしまうやり方などありますが、日付形式にしておいた方が後々取り回しがし易いですし、表示のさせ方は書式設定なりでどのようにでも変えられます。

このような計算式を入れて当年売上の横に前年売上を呼び出すようにします。

=SUMIFS($C:$C,$A:$A,$A,$B:$B,EDATE($B2,-12))
SUMIFS関数で前年売上を参照する


ここではSUMIFS関数を使っていますが、最新のExcelでxlookupが使えれば次のようになります。

=XLOOKUP($A&EDATE($b,-12),$A:$A&$B:$B,$C:$C,0)
SUMIFS関数ですとデータが重複していた時に問題が出ますが、そもそもデータの重複自体が問題なのでこの関数を使うことは気にしなくて良いかと思います。
何よりSUMIFS関数の方が動作が軽いです。
店舗別月別売上データ前年追加済み


これをピボットテーブルで集計します。

ピボットテーブルを追加する


縦軸に年月を入れて、 集計フィールドを使って前年比を計算させます。

ピボット集計フィールド


最後にフィルターを設定します

既存店フラグでフィルター


期間を絞り、書式設定をして、グラフを追加するとこんな感じに

ピボットグラフを追加

既存店対象店舗の確認を簡単にしたい

先程のデータでは売上と既存/非既存フラグを一体で扱いました。
このフラグの確認を簡単にするには「店舗マスタ」を別に作ります。 この「店舗マスタ」の持たせ方はいくつかやり方があります。

店舗名に日付と既存/非既存フラグを紐付けたマスタ

このようなデータを毎月追加していきます。

月別店舗別マスタ


店舗マスタが毎月増えていきますが、先程の売上データへの参照がわかりやすくExcel初心者へも引き継ぎし易いのが良いところです。

店舗名に開業日と閉店日を紐付ける、かつ休業(改装)店舗一覧を別に作る

マスタが2つに増えますがいたずらにマスタデータが増えることなくすっきりします。

店舗マスタと休業一覧


しかし月毎の既存点判定を行う関数はやや複雑になります。

まず開業13ヶ月かつ閉店前月の判定を、

=AND($B2>EDATE(開業日,12),$B2<EDATE(閉店日,-1))

として判定します。

開業日閉店日からの既存店判定


次に当年、前年とも改装対象月でないことを、

=NOT(OR($B2=[改装日],EDATE($B2,-12)=[改装日]))

として判定するのですが、ここでは次のようにこれを表現しています。

=NOT(OR(SUMIFS($L:$L,$K:$K,$A2,$L:$L,$B2),SUMIFS($L:$L,$K:$K,$A2,$L:$L,EDATE($B2,-12))))
休業(改装)一覧からの既存店判定


どちらもTRUEであれば既存店というわけです。

店舗別売上データ既存店判定


既存店前年比を日次や週次でも出したい

基本的には月次と同じですが既存店判定の方法に工夫が必要になります。
まずは店舗ごと、日付ごとの売上データを用意して、

日別売上データ


次のような関数で前年データを呼び出し、

=SUMIFS($C:$C,$A:$A,$A,$B:$B,EDATE($B2,-12))
日別売上データへの前年追加


XLOOKUP関数が使えれば次のようになります。

=XLOOKUP($A&EDATE($B2,-12),$A:$A&$B:$B,$C:$C,0)

当年売上、前年売上とも0でないことを判定します、

=AND($C>0,$E>0)
日別売上データの既存判定


同様にピボットテーブルを作り、前年比の計算を入れて、フィルタで既存対象だけ絞り込みます。

日別売上のピボットテーブル


週単位に変えたい場合は、日付のところで右クリックして”グループ化”を選択して、

日付右クリック


”日”を選択後、”日数”を7にして”OK”ボタンを押します。

グループ化ダイアログ


週単位の集計に変更できます。

週別売上前年比

データ量はどれくらいになるでしょうか?

日別売上データをExcelで扱うとして、再計算がストレスなく行えるデータ量としては5万行くらい、30店舗で4~5年分くらいが限界だと感じています。
もちろんPCスペック次第で頑張れますが、これを超えるデータ量になる場合はデータベースで管理したり、BIでアウトプットしたり工夫が必要になってきます。


既存店前年比を同曜対比で出したい

先ほどと同じように売上データを作りますが、前年データの関数が異なります。
まず単純に前年同週同曜日を参照する場合は364日前(7日✖️52週)の売上を参照します。

前年同曜売上の参照


しかし、年末年始やお盆、GWだけは同日対比にしたい、あるいは春分の日や秋分の日を合わせたいなどあると思います。
その場合は”前年あて日”データを含む日付マスタが必要になってきます。

日付マスタ


このようなデータを用意し、売上データに反映させ、

あて日を含む日別売上データ


あて日に対応する売上データを参照します。 この後、既存判定を行いピボットで表示するところは前段と同様になります。

まとめ

いかがでしたでしょううか。
既存店前年比の出し方について、月別と日別、既存判定の内容に応じて解説してきました。

冒頭でも述べましたが、飲食店にとって既存店前年比は重要な経営指標の一つだと思います。
今一度自社での算出方法を振り返ってみてはいかがでしょうか。  

管理会計構築・運用ご支援についてのご相談・お問い合わせはこちらから、まずは初回無料相談から承ります。

飲食店専門コンサルタントにお任せください

Nautical Star Strategy & Analysy は飲食店専門のコンサルタントです。
豊富な経験をもとに安価で短納期、わかりやすいコンサルティングサービスをご提供いたします。
ご支援終了時には、お手元のITを最大限活用し貴社にて運用可能な状態で引き継ぎをさせていただきます。

無料経営相談窓口

無料オンライン経営相談窓口

無料オンライン経営相談についてはこちらのフォームよりお問い合わせください。
カメラ付きパソコン、タブレット、またはスマートフォンをお持ちであれば特別な機材は必要ありません。
※当日は60分程度のお時間をいただきます。
※日時については、追って調整させていただきます。

    お名前

    メールアドレス

    御社名

    所在地

    お電話番号(ハイフンなし)

    お問合せ内容

    ご相談内容