新メニューの売価を決めるときに材料費を電卓で1つずつ足していると、「やっぱりこの食材を変えよう」となっただけで最初から計算し直しになります。また、最近の値上げで仕入単価が上がったあとも、手計算のままだと「どのメニューの原価率が危険ラインを超えたのか」がすぐには分かりません。そこで、使う材料と単価を1行ずつ入れておくだけで、1食あたりの原価・原価率・粗利までが自動で出るExcel表を作りました。
こんな場面で役立ちます
新メニューの売価(価格設定)を決めるとき
使う材料と分量を書き出して1食あたりの原価を出し、売価の数字をいろいろ変えながら原価率をシミュレーションできます。あらかじめ決めた「目標原価率」との差が数字で出るので、「材料費をあと何円削るか」「売価をあと何十円上げるか」の判断がしやすくなります。食材だけでなくテイクアウト用の容器や包材も1行として入れておけば、提供までにかかる本当の原価がひとまとめに把握できます。
食材の仕入単価が上がって原価を見直すとき
入力シートの「単価」の数字を打ち替えるだけで、その食材を使っているメニューの原価率と粗利が一斉に更新されます。単価を確認した日付も横に記録しておけるので、「いつの時点の仕入れ値で計算した原価か」が後から分からなくなるのを防げます。月別の集計表を見れば、最近原価の見直しができていない時期もなんとなく掴めます。
どのメニューが一番粗利を稼いでいるか並べたいとき
メニューごとの売価と月間の販売数を入れると、商品別の「粗利総額・構成比・順位」が自動で出ます。原価率が低い(利益率がいい)メニューと、お店全体の粗利額を稼いでくれているメニューが一致しないことはよくあるので、こうしてランキング形式で並べると「どの商品を推すべきか」の優先順位が変わってきます。また、分類別の集計を見れば、食材・包材・外注費のどこにお金がかかっているかも分かります。
外注費や源泉徴収がある支払いも原価に入れたいとき
パッケージのデザイン料や外部への加工委託費などを1行として入れ、源泉区分を「対象」にすると10.21%の源泉徴収税が自動で計算されます。税込合計から源泉徴収税を引いた「実際の振込額(差引支払額)」も集計シートに出る仕組みです。仕入先ごとのインボイス登録番号を控える欄もあり、「T+13桁」の形式になっているかどうかの判定も自動でつきます。
ダウンロード
Googleスプレッドシートで使いたい方はこちらからコピーして使ってください。
シートの構成

「入力する」シートは、見出し行と200行分の入力欄だけのシンプルな作りです。薄い黄色のセルが手入力する列、薄いグレーのセルが数式の列になっています。分類・単位・税区分・源泉区分はプルダウンから選べるので、表記ゆれで集計がおかしくなる心配がありません。表が横に長いため、左端の「日付」と「商品名」の2列はスクロールしても動かないように固定してあります。

「集計を見る」シートには、全体のサマリー、分類別の構成比と順位、商品別の原価率と粗利、そして月別の推移表が縦に並んでいます。その右側には、実際の原価率と目標原価率を見比べる縦棒グラフと、月別の原価推移を追う折れ線グラフを配置しました。グラフはすべて集計表の数字と連動しているので、データを足せばグラフの形も自動で変わります。
使い方の手順
- まず「入力する」シートの2行目から、A列に単価を確認した日付、B列に商品・メニュー名、C列に分類、D列に材料名、E列に仕入先名を入れます。
- 続いてF列に1食(1個)あたりの使用量、G列に単位、H列にその単位あたりの単価を入れます。I列の小計(数量×単価)は自動で計算されます。
- J列で税区分を「10%・8%・対象外」から選び、M列で源泉区分を選択します。O列に仕入先のインボイス登録番号を入れると、P列に形式チェックの判定が出ます。
- 次に「集計を見る」シートへ移り、B6セルに対象年、B7セルにお店全体の目標原価率を入れます。月別の集計表は、このB6セルに入れた年を見て自動で切り替わります。
- 同じシートの34行目から下に、A列に商品・メニュー名、B列に税抜の売価、C列に月の販売数、I列にその商品の目標原価率を入れます。このとき、A列の商品名は「入力する」シートのB列に入れた名前と一字一句同じにしてください。
よくある質問
商品別の原価が「0」のまま計算されないとき
「集計を見る」シートのA列に入れた商品名と、「入力する」シートのB列に入れた商品名が1文字でも違っていると、Excelが別物とみなして合算してくれません。数字の全角・半角の違いや、名前の後ろにうっかり入ったスペースが原因になりやすいので、「入力する」シート側の商品名をそのままコピーして貼り付けるのが確実です。
1食あたりではなく、仕入れのロット単位(箱や袋)で入れてもいいですか
ロット単位の入力でも使えます。その場合、I列の小計がそのまま1回あたりの仕入金額になり、税込合計や差引支払額を「仕入れの支払い集計」として見ることができます。細かく原価率を出したい看板商品だけ1食あたりの分量に割り戻して別行で入れておけば、両方の使い方ができます。
源泉徴収税の端数処理を「切り捨て」に変えたいとき
初期設定では、N列の源泉徴収税はROUND関数(四捨五入)にしてあります。もし自社の経理ルールに合わせて1円未満切り捨てにしたい場合は、数式内の「ROUND」を「ROUNDDOWN」に書き換えてください。消費税の列も同じ組み方をしてあるので、合わせて直せます。
このほかのテンプレートはツール集に、生成AIへの指示文はプロンプト集にまとめています。
原価率を計算してみたものの、なかなか値上げ(売価変更)に踏み切れず迷ったとき、最終的にはどの数字を見て価格を決め直しましたか。


コメント