原価計算をExcelで作る方法｜材料費・歩留まり・原価率が自動で出る表

売値を勘で決めていると、売れているのに儲かっていないことがあります。

必要なのは「材料の単価表」と「商品ごとの使用量の表」の2つだけです。使うのは掛け算と割り算です。

小さな飲食店、ハンドメイド、小売で、1個あたりいくらかかっているかを出したい人向けです。

■1. 材料の単価表を作ります

シート名を「単価表」にして、列を次のようにします。

A 材料名／B 単位（gや個）／C 仕入れ数量／D 仕入れ金額／E 1単位あたりの単価

E2に次の式を入れ、下へコピーします。

=D2/C2

例：小麦粉 1,000g を 300円で仕入れた場合、E2は 0.3（円/g）になります。卵 10個 250円なら 25（円/個）です。

■2. 商品ごとの原価表を作ります

シート名を「原価表」にして、列を次のようにします。

A 商品名／B 材料名／C 使用量／D 材料費

D2に次の式を入れ、下へコピーします。

=C2*VLOOKUP(B2,単価表!$A:$E,5,FALSE)

意味は「使用量に、単価表から引いた単価を掛ける」です。VLOOKUPは古いExcelでも動きます。

■3. 商品ごとに合計します

別のセルに商品名を入れ、隣に次の式を入れます（商品名がF2のとき）。

=SUMIFS($D:$D,$A:$A,F2)

その商品の材料費の合計、つまり原価になります。

■4. 入力例で確かめます

商品「ケーキ」を、小麦粉250g・卵2個で作る場合です。

・小麦粉：250 × 0.3 ＝ 75円

・卵：2 × 25 ＝ 50円

・原価の合計 ＝ 125円

表の合計が125になれば、式は合っています。違うときは、仕入れ数量や金額が文字として入っていないか（左寄せになっていないか）を確かめます。

■5. 歩留まり（使えない部分）を入れます

野菜の皮や筋など、捨てる分がある材料は、使える割合を「歩留まり」として単価表に1列足します。

・F列を「歩留まり」にして、使える割合を入れます（90％なら0.9）。

・E2の式を次に変えます。

=D2/(C2*F2)

例：玉ねぎ 1,000g 200円で歩留まり0.9なら、200 ÷（1,000 × 0.9）＝ 約0.222円/g です。

歩留まりは、自分の店・自分の仕入れで量って決めます。本に載っている数字をそのまま使わないでください。

■6. 原価率と売値を出します

原価率 ＝ 原価 ÷ 売値

=G2/H2

（G2が原価、H2が売値）ケーキの原価125円、売値500円なら 0.25、つまり25％です。セルの表示形式を「パーセンテージ」にします。

狙う原価率から売値を出すときは、売値 ＝ 原価 ÷ 目標原価率です。

=G2/I2

（I2が目標原価率）目標30％（0.3）なら、125 ÷ 0.3 ＝ 約417円です。

■7. 気をつけること

・原価率がいくつなら良いかは、業種や店で違います。この記事の数字は例です。自分の仕入れで入れ直してください。

・人件費・家賃・包材・手数料は入っていません。原価率だけで儲けは決まりません。

・単価表の数字を1つ変えて、原価表が変わるかを一度確かめておくと安心です。

■8. 次の一歩

・商品数が増えたら、単価表と原価表を分けたまま、商品ごとの一覧を別シートにまとめます。

・仕入れ値が変わるたびに単価表だけ直せば、すべての原価が更新されます。

ASKOのExcelテンプレートは、ココナラのプロフィールからご覧いただけます。