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

• 2021.06.05
• Excel, 経営管理向けPC Tips

Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

しかし月毎の既存点判定を行う関数はやや複雑になります。 まず開業13ヶ月かつ閉店前月の判定を、

として判定します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

まとめ

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

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