ClaudeでFLコストを月次で出し、飲食店の食材費と人件費の超過を探す

FLコストの月次集計は、POS売上、仕入れ明細、シフト実績の3つの表を1冊のExcelに束ねれば、Claude for Excelと一緒に組めます。月の合計だけを見ても、超過の原因は分かりません。週に割って基準を超えた週を見つけ、食材費は品目へ、人件費は時間帯へ分解すると、手を打つ場所が決まります。

この記事は、FL比率の式、3表の台帳の作り方、数字を入れた超過週の探し方、Claude for Excelへの頼み方の順に書きます。数字の例は説明用に作ったもので、実在の店舗の実績ではありません。

FLコストは食材費と人件費を売上で割った比率

FLコストは、食材費(Food)と人件費(Labor)の合計です。これを売上高で割った値がFL比率で、飲食店が毎月見る代表的な指標になります。

目標の水準は、業態や立地、客単価で店ごとに違います。この記事の例では、目標を62%と置きます。自店の目標は、過去の実績や本部の基準から決めてください。

月次で出す価値は、毎月同じ定義で並べて、前月や前年と比べられる点にあります。ただし月の合計は、週ごとの山と谷を平らにします。月のFL比率が目標内でも、特定の1週だけが大きく超えていて、残りの週が低く隠している場合があります。そこで、月次の台帳の下に週次の行を持たせます。

3つの表を1冊のブックにそろえる

入力は3表です。それぞれを別のシートに置き、集計用のシートで束ねます。売上CSVを週次のメモにまとめる流れはPOS売上CSVの週次メモの記事にあります。

週の区切りは、月曜始まりなどに固定します。月をまたぐ週は、月次の集計から外れたり二重に入ったりするので、月の境目の扱いを先に決めます。たとえば、週は月曜から日曜で固定し、月次は日付で集計し直す、と分けるとずれません。

週次シートの1行は1週間です。売上は売上シートから、人件費はシフトシートから、日付の範囲で拾います。

B2: =SUMIFS(売上!C:C, 売上!A:A, ">="&A2, 売上!A:A, "<="&A2+6)
D2: =SUMIFS(シフト!D:D, シフト!A:A, ">="&A2, シフト!A:A, "<="&A2+6)

A列は週の開始日、B列は売上、D列は人件費です。食材費のC列は、次の節の考え方で決めます。

食材費は仕入れ額ではなく使用高で出す

仕入れ明細の合計は、その期間に買った額です。使った額とは一致しません。月初に在庫が多く月末に少なければ、使った額は買った額より大きくなります。逆なら小さくなります。正しい食材費は、棚卸しを使って次の式で出します。

月次は、月末の棚卸しがあればこの式で出せます。週次は、週ごとの棚卸しをしていない店が多いはずです。その場合、週次の食材費は仕入れ額で代用することになります。仕入れ額は、まとめ買いの週に膨らみ、買わなかった週に痩せます。週次で見える食材費の動きは、使用高の動きとは別物だと理解した上で使います。月の合計は棚卸しで確定させ、週の比率は「超過のあたりをつける道具」にとどめるのが、現実的な切り分けです。

仕入れ明細は、取引先ごとに様式が違います。請求書をExcelに起こしたものや、発注システムの出力など、列名も単位もばらつきます。品目の表記をそろえる作業は、後の品目分解の精度に直結します。

基準を超えた週を見つける

週次シートが埋まったら、F比率、L比率、FL比率を式で出し、目標との差を並べます。ここでは1か月分の例を示します。

まず、週ごとの金額です。

これを比率にして、目標との差を並べます。

月計のFL比率は63.3%で、目標を1.3ポイント超えるだけに見えます。週で割ると、第3週が70.0%で8.0ポイント超過しています。売上は4週で最も低く、食材費と人件費はほとんど下がっていません。分母の売上が落ちたのに分子が追随しなかった週です。

超過の中身を、さらに食材費と人件費に分けます。第3週と、残り3週の平均を比べます。

他の3週は、売上9,200,000円、食材費2,940,000円、人件費2,700,000円の合計から出しています。第3週の売上に他の3週の比率を掛けた場合の食材費は約894,800円で、実際の1,060,000円との差は約165,000円です。人件費は同様に約821,700円との差で、約78,000円になります。超過のおよそ7割が食材費側にある計算です。

食材費の超過を品目に分解する

食材費側の約165,000円がどの品目から来たかを、仕入れ明細で調べます。考え方は、他の3週の品目別の構成比を第3週の売上に掛けた「売上に見合う仕入れ額」を作り、実際の仕入れ額との差を並べることです。

差の上位は鶏もも肉と鮮魚で、2品で135,000円です。ここまでは数字の分解で、原因までは示していません。同じ差でも、次のように読みが分かれます。

• 数量が増えた: 仕入れの数量が売上に比べて多い。売れ残りや廃棄、仕込みすぎの疑い
• 単価が上がった: 数量は同じで、キロ当たりの単価が高い。相場や取引先の変更の疑い
• 週をまたいだ: 先週の分の納品が今週に入った、まとめ買いをした

仕入れ明細に数量と単価の列があれば、差を数量要因と単価要因に分けられます。第3週と他の3週の平均単価を品目ごとに比べ、単価の差に第3週の数量を掛ければ単価要因、数量の差に他の3週の単価を掛ければ数量要因です。列が金額だけの場合は、この分解はできません。

人件費の超過を時間帯に分解する

人件費側の約78,000円は、シフト実績で時間帯と曜日に分けます。第3週の平均時給を1,100円とすると、78,000円はおよそ71時間分です。売上に見合う人時は、売上に見合う人件費821,700円を時給1,100円で割った約747時間で、実際の人件費900,000円は約818時間にあたります。これを時間帯別に分けて、差を並べます。人件費を売上・時間・時給の3要因に分ける方法は人時生産性と人件費率の記事にあります。

差の半分以上が、平日の午後(14〜17時)に出ています。仕込みや締め作業のために置いた人員なら、必要な超過です。逆に、客数が落ちた時間帯にシフトを削れなかったのなら、翌月のシフトの組み方を見直す材料になります。どちらなのかは、シフトの記録だけでは決まらず、店長の説明が要ります。

売上が小さい時間帯でも、置かなければならない最低人数があります。「売上に見合う人時」はあくまで比率からの理論値で、最低人数を別の列に持たせて大きいほうを採ると、現実に近づきます。

Claude for Excelに頼む

ここまでの台帳を、Claude for Excelと組みます。Claude for Excelは、開いているブックについて質問すると、セルを根拠に示して答えるExcelのアドインです。数式の関係を保ったまま値を変えたり、エラーの原因をたどったり、複数シートにまたがる作業をしたりできます。Pro、Max、Team、Enterpriseの各プランで使えます。

対応するのは、Excel on the web、Microsoft 365版のWindows(ビルド16.0.13127.20296以降)、Mac(バージョン16.46以降)です。Excel 2016と2019の永続ライセンス版、iPad、Androidでは動きません。

最初に、設定のInstructions欄に集計の約束を書いておくと、毎回の会話で言い直さずに済みます。この欄に書いた内容はExcelだけに効き、PowerPointやWordの指示とは別です。

食材費は「期首在庫+当期仕入れ−期末在庫」で月次に確定する。
週次の食材費は仕入れ額で代用し、シート上で「仕入れ額代用」と明記する。
比率は小数点第1位まで。目標FL比率は62%。

次に、台帳の式を点検してもらいます。

「週次」シートのB〜H列の式を点検してください。
1. 週の範囲(開始日から+6日)が、月をまたぐ週で二重計上になっていませんか。
2. 売上が0の週で、比率の式がエラーになっていませんか。
3. 月計の行が、4週の合計と一致していますか。
一致しない行は、セルの番地つきで一覧にしてください。

超過週が見つかったら、品目分解を頼みます。

第3週のFL比率が目標を超えています。「仕入れ」シートから、
第3週と他の3週で、品目ごとの仕入れ額を比べてください。
他の3週の構成比を第3週の売上に掛けた「売上に見合う額」と、
実際の仕入れ額の差が大きい順に並べてください。
元の表は変えず、新しいシートに出力してください。

「元の表は変えず」と書くのは、Claude for Excelが既存データを上書きする前に警告を出す一方、意図しない書き換えを避けるのは頼み方でもあるからです。広い範囲を編集させる前には、信頼できるコピーのブックで試すのが、公式の勧める進め方です。

外部から来たファイルを開くときの注意

仕入れ明細は、取引先から届いたExcelをそのまま取り込むことがあります。公式ページは、外部のファイルには、Claudeを操って情報を抜き出したり記録を書き換えさせたりする隠れた指示が含まれていることがあるので、信頼できるスプレッドシートだけで使うよう書いています。危険な操作を提案したときは、実行前に確認を求められます。取引先のファイルは、値だけを自店の台帳に写してから使えば、隠れた指示を持ち込まずに済みます。

Claude for Excelで任せない部分

公式ページは、監査上重要な計算を検証なしに任せることや、人のレビューなしの最終成果物を勧めていません。非対応の機能として、データテーブルとマクロ・VBAも挙げています。月次のFL比率は、経営の判断や銀行への説明に使う数字になるので、1週分を電卓で再計算して台帳と一致するか確かめてから使います。第3週なら、FL比率が70.0%になるか、手元で出せば足ります。

つまずきやすい点

売上に配達プラットフォームの手数料を含める店では、売上の定義でも比率が変わります。売上を手数料の前で数えるのか後で数えるのかを決め、月をまたいで変えないようにします。

メニュー単位の原価率は、HACCP衛生管理計画とメニュー原価計算の記事が扱っています。月次の数字の動きに理由メモを付ける方法は月次変動分析の記事で読めます。

まとめ

月のFL比率が目標内でも、週で割ると1週だけ大きく超えていることがあります。この記事の例では、月計の超過が1.3ポイントにとどまるのに、第3週は8.0ポイントでした。

食材費は品目へ、人件費は時間帯へ分けると、原因を調べる範囲が絞れます。ただし数字の分解は原因の断定ではなく、店長が現場の事情と突き合わせる材料です。台帳の式の点検や品目分解の下ごしらえをClaude for Excelに任せ、月次の確定値は電卓での再計算と棚卸しで固めます。