excel
原価表をエクセルで作ろうとして手が止まるのは、関数の書き方が分からないからではありません。どの費用をどこに入れるか、誰がいつ入力するか、その2つが決まっていないまま表を作り始めるからです。列を増やせば増やすほど、あとから見たときに何の数字なのか分からなくなります。
先に結論を書きます。エクセルで使える原価表にするための条件は3つです。1つめは費目の分け方を先に決めて、途中で変えないこと。2つめは入力する場所と計算する場所を別のシートに分けること。3つめは見積の数字を消さずに残し、実績を横に並べて差を見られるようにすることです。この3つを守れば、表は数か月使えます。守らないと、作った本人しか読めない表になって、半年後には誰も更新しなくなります。
この記事では、原価表がエクセルで作られ続けている背景から始めて、費目の決め方、実際に作る手順、関数の使い分け、見積と実績の差を追う形、メリットとデメリット、無料テンプレートの見極め方、そして表が崩れ始めたときに何を切り分ければよいかまでを順に扱います。
原価の計算は、本来は専用の仕組みで持つほうが正確です。それでも小さな会社や数十人規模の部門では、原価表はエクセルで作られ続けています。理由は単純で、費目の分け方が会社ごと、案件ごとに違うからです。飲食なら食材と仕入れ単価、製造なら材料と加工の時間、受託の仕事なら人の時間と外注費が中心になります。既製の仕組みに合わせるより、自分たちの費目に合わせて表を作るほうが早い、という判断がそこにあります。
もう1つの理由は、原価の計算には「まだ決まっていない数字」を置く場所が必要だという点です。見積の段階では材料の単価も工数も仮です。仮の数字を入れて、売価を動かして、利益がどう変わるかを見る。この試算を何度も繰り返す作業は、表計算ソフトが最も得意にしている作業です。
メニュー作成時には商品の値段を決めなければなりませんが、その際正確な原価率の計算が必要なのはいうまでもありません。 原価率は原価÷売価ですが、エクセルを使えばいちいち電卓を叩く必要はなく、項目を入力するだけで簡単に原価率の計算ができます。さらに食材の使用量や売価といった項目の数値を入れ替えれば瞬時に再計算が行われるので、これを利用しながらシュミレーションを行えば、レシピの作成もかなり楽になります。 出典: officeut.blog.fc2.com
試算の道具として使うぶんには、この使い方で何も問題はありません。困りごとが出てくるのは、試算に使った表をそのまま実績の記録に使い始めたときです。見積のときに入れた仮の単価を、実績が出たタイミングで上書きしてしまう。すると、当初いくらで見ていたのかが分からなくなり、差が出た理由をあとから追えなくなります。現場でしばしば出るのは、この「上書きしてしまった」という話です。原価表を作るときにいちばん先に決めるべきなのは関数ではなく、見積の数字をどこに残すかです。
費目の分け方で迷ったら、材料費、労務費、外注費、その他の経費の4つから始めます。この4分類は業種を問わず当てはまり、あとから細かく割るときも枝を増やすだけで済みます。逆に最初から10以上の費目を並べると、入力する人が「これはどこに入れるのか」で毎回迷い、判断がぶれて集計が合わなくなります。
材料費は、物として消えていくものです。仕入れた食材、加工する鉄板、印刷する紙。ここで押さえておきたいのは、単価と使用量を必ず分けて持つことです。「材料費 12,000円」とだけ書いてある表は、あとから単価が上がったときに再計算できません。単価の列と数量の列を別に置き、金額はかけ算の結果として出します。
労務費は、人が動いた時間の費用です。時間あたりの単価と、かかった時間をかけて出します。ここで手が止まる会社は多く、「社員の給料を案件ごとに割るのが難しい」という理由で労務費を丸ごと落としてしまうことがあります。しかし労務費を落とした原価表は、受託の仕事ではほとんど意味を持ちません。売上の大半が人の時間で構成されているからです。厳密でなくてよいので、月の総人件費を稼働時間で割った時間単価を1つ決めて、まずそれを全員に当てるところから始めます。精度を上げるのは表が回り始めてからです。
外注費は、外に出した仕事の費用です。請求が来る前に金額が確定していることが多いので、比較的入れやすい費目です。注意点は、支払いのタイミングと原価に立てるタイミングがずれることです。3月に作業が終わって5月に請求が来る外注は、原価表の上では3月に立てます。支払日で並べると、月ごとの利益が実態からずれます。
その他の経費は、上の3つに入らないものです。交通費、送料、その案件のために買った小道具、有料の素材。ここは金額が小さいぶん入力が漏れやすく、積み上がると原価の5%から10%を占めることがあります。少額でも入れる場所を決めておくと、あとから「思っていたより儲かっていない」理由が説明できます。
4つの費目に分けたら、それぞれに「誰が入力するか」を紐づけます。材料費は購買、労務費は各自の作業記録、外注費は発注した人、経費は立て替えた人。入力する人が決まっていない費目は、必ず空欄のままになります。
作る順番は、シートを分ける、マスタを置く、入力表を作る、集計表を作る、の4段階です。この順で作ると、あとから費目が増えても壊れません。
最初にすることは、1つのファイルの中でシートを役割ごとに分けることです。マスタ、入力、集計の3枚が最低限です。マスタには単価や費目の一覧といった「変わらない値」を置き、入力には日々の記録を1行ずつ足していき、集計には計算結果だけを出します。1枚のシートに入力欄と集計欄を混ぜると、行を挿入した瞬間に集計式がずれます。表が壊れる原因のほとんどは、この混在にあります。
次にマスタを作ります。材料の名前と単価、外注先の名前、労務費の時間単価、費目のコード。ここを別シートに切っておく利点は、単価が変わったときに1か所直せば全体に反映されることです。マスタの表は、範囲に名前を付けるか、テーブルとして書式設定しておきます。テーブルにしておくと、行を足したときに参照範囲が自動で伸びるので、参照漏れが起きません。
入力表は、1行1明細の形にします。日付、案件名、費目、品目、単価、数量、金額、入力者。この8列が基本で、集計しやすいのは縦に伸びる形です。案件を横に並べて列を増やしていく形は、案件が増えるたびに式を書き足す必要が出て、必ずどこかで漏れます。案件が増えたら行が増える、という形にしておきます。
集計表では、入力表から数字を拾います。ここで使う関数はほぼ決まっていて、費目ごとの合計は SUMIFS、件数は COUNTIFS、マスタから単価を引いてくるのは XLOOKUP か VLOOKUP です。SUMIFS は条件を複数並べられるので、「この案件で、この費目の、この月の合計」を1つの式で出せます。案件ごとの原価率は、原価の合計を売価で割るだけです。ピボットテーブルを使えば式を書かずに費目別と案件別の表が出せますが、元の表を更新したあとに更新操作が必要になる点だけは覚えておきます。
最後に、入力させたくない場所を守ります。集計シートの式が入っているセルはロックして、シートの保護をかけます。入力シートでは、費目の列に入力規則を設定して、マスタにある費目しか選べないようにします。この2つをやるかやらないかで、3か月後の表の状態がまるで変わります。手で費目名を打たせると、全角と半角、スペースの有無、送り仮名の違いで同じ費目が別物として集計されます。
原価表を作る目的は、原価の金額を出すことそのものではありません。見積のときに見ていた数字と、実際にかかった数字がどれだけ違ったかを知り、次の見積に反映することです。ここが設計に入っていない原価表は、決算の資料としては使えても、次の仕事の値付けには使えません。
やり方はごく単純で、見積の列と実績の列を横に並べます。費目ごとに「見積金額」「実績金額」「差額」「差率」の4列を持ち、差額は実績から見積を引いたもの、差率は差額を見積で割ったものです。見積の列は、案件が始まった時点で確定させて、以後は触りません。上書きしたくなったときは、上書きせずに「改訂見積」の列を足します。列が増えるのは構いませんが、当初の数字が消えるのは困ります。
差を見る単位は、月ではなく案件にします。月で見ると、儲かった案件と損した案件が打ち消し合って平均に沈み、どこで見誤ったのかが分からなくなります。案件単位で並べて、差率が大きいものから順に見ていくと、見誤りの型が見えてきます。材料の単価を甘く見ていたのか、工数を短く見ていたのか、値引きの分を原価側で吸収していたのか。型が分かれば、次の見積のときに当てる係数が決まります。
差が出やすいのは、ほとんどの場合、労務費です。材料費は数量と単価で確認できますが、人がかけた時間は記録されていないと分かりません。ここで原価表の限界が出ます。表はできていて式も合っているのに、入力される時間が実態と合っていない。理由は、作業した人がエクセルを開いて行を足す手間を嫌うからです。あるチームでは、月末にまとめて思い出しながら入力する形になり、実績工数と記録の差が3割ほど開いていたという話もあります。
この問題は、表の作り方では解決しません。作業する人が普段いる場所で時間が残るようにするしかありません。進行の管理をしている板の上でタスクに時間が紐づいていれば、その数字を月末に書き出して原価表に流し込めます。工程の管理と原価の集計を1つの表に詰め込むのではなく、記録する場所と集計する場所を分けて、集計だけをエクセルに持ってくる形です。板の側でどこまで持てるかはできることのページに整理されています。
原価表ができると、次に出てくるのが売価をいくらにするかという話です。ここで使う式は2つだけで、原価率は原価を売価で割ったもの、粗利率は1から原価率を引いたものです。原価率が30%なら粗利率は70%になります。
間違いが起きやすいのは、逆の計算です。原価が決まっていて、粗利率を確保したい売価を出したいとき、原価に粗利率を足しても正しい売価にはなりません。原価6,000円で粗利率40%を確保したいなら、6,000を0.6で割って10,000円です。6,000に40%を足した8,400円では、粗利率は28.6%にしかなりません。エクセルでは、売価のセルを「原価÷（1−粗利率）」の式にしておくと、この間違いが起きません。粗利率の目標をマスタに1つ置いて、そこを参照させます。
もう1つ注意したいのは、原価表に載せていない費用があるまま売価を決めてしまうことです。事務所の家賃、通信費、管理をしている人の時間。これらは特定の案件に紐づかないので、案件ごとの原価表からは抜け落ちます。抜けた状態の原価率だけを見て売価を決めると、案件は全部黒字なのに会社は赤字という状態になります。
対処は、間接費を配賦することです。配賦というのは、案件に直接紐づかない費用を、何らかの基準で案件に割り振る作業です。基準として使いやすいのは、案件にかけた時間か、案件の売上の比率です。月の間接費の合計を、その月の総作業時間で割ると、1時間あたりの間接費が出ます。それを案件の作業時間にかけて足します。この列を1つ足すだけで、原価率の見え方が変わります。
配賦の基準は、正確さより一貫性を優先します。時間で割ると決めたら、毎月時間で割ります。月によって基準を変えると、前月との比較ができなくなり、原価表を作っている意味が薄れます。基準を変えるときは、過去の月も同じ基準で再計算して並べ直します。
エクセルで原価表を持つ利点は、大きく4つあります。
1つめは、費目を自由に決められることです。既製の仕組みは、その業界の標準的な費目に合わせて作られています。標準から外れた費目、たとえば撮影の立ち会い日当や、翻訳のチェック工数のような細かい費用を入れる場所が用意されていないことがあります。エクセルなら列を足すだけで済みます。
2つめは、試算がすぐできることです。単価を変えたら全部の行が再計算され、原価率と利益が同時に動きます。値引き交渉の場で「この条件だと利益がどうなるか」を数十秒で出せるのは、表計算ソフトの強みです。専用の仕組みでは、確定した数字を入れる前提になっていて、仮の数字で遊ぶ余地が少ないことがあります。
3つめは、追加の費用がかからないことです。すでに社内で使っているソフトなので、新しく契約する必要がありません。原価管理の仕組みを入れるとなると、導入の作業と月々の費用が発生します。まず自分たちの原価の構造を把握したいという段階なら、エクセルで1か月分作ってみるほうが早く、判断材料も多く得られます。
4つめは、渡しやすいことです。税理士や会計の担当者に数字を渡すとき、ファイルを送れば読んでもらえます。専用の仕組みに入っているデータは、書き出し方を説明するところから始めなければなりません。
必要なスキルの範囲も、思われているほど広くありません。SUM、SUMIFS、COUNTIFS、XLOOKUP、IFERROR、この5つと、テーブル機能、入力規則、シートの保護が分かれば、実務で使える原価表は作れます。マクロは要りません。マクロで自動化した表は、作った人が異動した時点で誰も直せなくなるので、原価表では避けておくほうが無難です。
利点の裏返しとして、エクセルの原価表には決まった崩れ方があります。あらかじめ知っておくと、対処の順番を決められます。
最も多いのが、同時に開けないことです。原価表は、購買、経理、進行のとりまとめ役の3者が触ります。1人が開いている間、他の人は読み取り専用で開くことになり、入力を後回しにします。後回しにした入力は、多くの場合そのまま忘れられます。クラウド上に置いて共同編集にすれば緩和できますが、式が入ったセルを複数人が同時に触る状況では、意図しない上書きが起きます。
次に多いのが、ファイルが増えることです。月ごと、案件ごとにファイルを分けていくと、半年で数十個になります。単価のマスタがそれぞれのファイルに複製されているので、単価が変わったときに全部を直す作業が発生し、直し漏れたファイルが残ります。年度をまたいだ比較をしようとして、費目の並びが違っていて比べられない、というのもよく聞く話です。
3つめは、誰がいつ何を直したのか分からないことです。数字が合わないときに、入力の間違いなのか式の壊れなのか、いつからそうなっていたのかを追う手がかりがありません。変更の履歴を残す仕組みはありますが、日常的に見に行くものではありません。
4つめは、行が増えたときの重さです。エクセルが扱える行数は1シートあたり1,048,576行あるので上限で困ることはまずありませんが、数千行の入力表に対して SUMIFS や XLOOKUP が何百個も並ぶと、開くだけで待たされる状態になります。この段階に来たら、集計をピボットテーブルに寄せるか、集計の粒度を落とすことを考えます。
5つめは、書式が壊れることです。品番や部門コードを入力すると先頭のゼロが落ちる、日付として勝手に変換される、コピーで貼ったときに全体の書式が入れ替わる。どれも集計の結果を静かに変えるので、気づくのが遅れます。コードを扱う列は、あらかじめ文字列として書式を決めておきます。
対処の順番としては、まずファイルを1つに寄せることです。案件が増えたら列ではなく行を増やす形にしておけば、ファイルを分ける必要はほとんどなくなります。次に、式を減らします。集計シートの式を減らしてピボットテーブルに寄せると、壊れる箇所が減ります。最後に、入力の経路を変えます。人に表を開かせる形をやめて、普段作業している場所から数字が集まる形にします。
原価表のテンプレートは無料で配られているものが多く、ゼロから作るより早く始められます。ただし、そのまま使えるものは少ないので、拾ってきたテンプレートを見るときの基準を持っておきます。
見るべきところは4つあります。1つめは、マスタのシートが別にあるかどうかです。単価が入力表に直接打ち込まれているテンプレートは、単価が変わった瞬間に全行の手直しが必要になります。2つめは、案件が縦に並ぶ形かどうかです。案件ごとにシートが1枚ずつ用意されている形は、見た目は分かりやすいものの、案件が20を超えたところで集計が破綻します。3つめは、見積と実績の列が両方あるかどうかです。実績だけのテンプレートは、記録には使えても次の見積には使えません。4つめは、式が読めるかどうかです。他人が作った長い式は、直せないまま放置され、いつか壊れます。読めない式が入っているなら、自分で書き直したほうが結局早くなります。
おすすめの使い方は、テンプレートを完成品として使うのではなく、費目の並びと列の設計を見る参考資料として使うことです。業種の近いテンプレートを2つか3つ開いて、共通して入っている列だけを自分の表に取り込みます。共通して入っている列は、その業種で必要だと分かっている列です。逆に1つのテンプレートにしか入っていない列は、作った人の会社固有の事情である可能性が高いので、そのまま真似しないほうがよいです。
原価計算そのものの考え方が分からないという場合は、表を作る前に基礎を押さえておくと手戻りが減ります。費目の分け方や配賦の考え方は、どの業種でも共通する部分があります。
このように原価計算が分かれば、会社の利益を生み出すポイントが見えてくるので、その知識は会社から大変重宝されるのですが、「原価計算」という言葉は一般的にはなじみが薄く、言葉を聞くだけで難しいと感じる方も少なくありません。 出典: pcci-school.com
難しく感じる理由の多くは、用語の多さにあります。実務で必要なのは、直接費と間接費の区別、配賦という考え方、そして標準原価と実際原価の差を見るという3点だけです。この3つが分かれば、テンプレートに並んでいる列の意味が読めるようになります。
テンプレートを取り込むときは、自分の表に貼り付ける前に、費目の名前を自社の呼び方に書き換えておきます。配布元の呼び方をそのまま残すと、入力する人が「これは自分の仕事のどの費用なのか」で迷い、結局その列が空欄で埋まります。列の名前は、現場で実際に使われている言葉に合わせるほうが入力が続きます。
原価表が回らなくなったとき、表の作り方を直せば済む問題と、表では解決しない問題があります。この2つを分けずに作り直しを始めると、同じことが半年後にもう一度起きます。
表の作り方で直せるのは、集計が合わない、式が壊れる、単価の直し漏れ、費目のばらつきです。これはシートの分け方と入力規則の設定で片付きます。表では解決しないのは、入力されないこと、同時に触れないこと、いつ誰が直したか分からないことです。この3つは、表計算ソフトが1つのファイルを1人で編集する道具として作られていることから来ています。
入力されない問題をもう少し分けると、原因は2つあります。入力する場所が普段いる場所から遠いこと、そして入力しても自分に返ってくるものがないことです。前者は、作業の記録が残る場所と原価を集める場所を分けて、集計だけをエクセルに持ってくる形で緩みます。後者は、入力した結果が案件の進み具合として見える形にすると変わります。数字を出すためだけの入力は続きませんが、自分の担当が終わったことを示す操作は続きます。
進行の板をどこに置くかを考え始めた段階では、費用と進行を同じ道具に載せようとしないことが判断を早めます。原価の計算はエクセルが得意で、進行の共有は板が得意です。板の側で持つのは、誰が何をどこまで進めているか、そこに何時間かかったかまでで、金額の計算は表に任せます。このとき板を選ぶ基準は、機能の多さではなく、入力する人が続けられるかどうかです。機能で絞らず、区切るのは人数とボードの数だけという料金の考え方は、入力する人を増やしたいときに効いてきます。人数を増やすたびに上位のプランへ移らなければならない形だと、記録してほしい人を板に入れられません。料金の区切り方は料金のページで確かめられます。
すでに別の道具で進行を管理している場合は、その道具のままでよいかを先に見ます。カードに時間を紐づけて記録できていて、月末に一覧で書き出せるなら、乗り換える理由はありません。困るのは、カードはあるのに作業時間が残っていないとき、そして案件の数が増えて板が探しにくくなったときです。付箋型の板からの移り方や、どこが違うのかはTrelloとの比較に整理されています。開発の課題管理から入ったチームで、案件の粒度が合わないと感じている場合の違いはBacklogとの比較にあります。他の道具との違いを横に並べて見たいときは比較の一覧から入ります。
ここで正直に書いておくべきことがあります。板の側で原価の計算はできません。費目ごとの集計や原価率の計算は、これまで通りエクセルの役目です。リポジトリの機能もありませんし、自動化と外部連携の幅で勝負する作りにもなっていません。自動で取り込めるのは付箋型の板からの移行だけで、他の道具からは手で移すことになります。画面は日本語だけです。それでも進行の記録が残るようになれば、原価表に流し込む労務費の精度は上がります。移行の手順はTrelloからの移行に、社内の数字を預けるときの扱いは安全性の考え方に書かれています。判断に迷う点があればよくある質問を先に見ると、確かめる手間が減ります。
最後に、原価表を作り始める前に決めておくとよいことを1つ挙げます。この表を毎月誰が閉めるか、です。費目の設計も式の書き方も、閉める人が読めて直せる形になっていれば長持ちします。閉める人が決まっていない原価表は、どれだけ丁寧に作っても3か月で止まります。表の設計より先に、この1人を決めるところから始めてください。
費目の分け方と、費目ごとの入力担当です。材料費、労務費、外注費、その他の経費の4つに分け、それぞれ誰が入力するかを紐づけます。入力する人が決まっていない費目は必ず空欄で残ります。関数やレイアウトは後から直せますが、費目の定義を途中で変えると過去の集計と比べられなくなるため、ここを先に固めます。
SUM、SUMIFS、COUNTIFS、XLOOKUP、IFERROR の5つで実務に足ります。費目別や案件別の合計は SUMIFS、マスタから単価を引くのは XLOOKUP、参照が外れたときの表示崩れを止めるのは IFERROR です。あわせてテーブル機能と入力規則、シートの保護を使えれば十分で、マクロは作った人以外が直せなくなるため原価表では避けるほうが無難です。
そのまま使えるものは少ないため、費目の並びと列の設計を見る参考資料として使うほうが確実です。確認する点は、単価のマスタが別シートにあるか、案件が縦に並ぶ形か、見積と実績の列が両方あるか、式が自分で読めるかの4つです。案件ごとにシートを1枚ずつ作る形は、件数が増えると集計が破綻します。
差が開く原因は多くの場合、労務費の記録です。材料費や外注費は金額が確定しますが、人がかけた時間は記録されていないと分かりません。月末に思い出しながら入力する運用だと実態と3割ほどずれることもあります。作業する人が普段いる場所で時間が残る形にして、その数字を集計だけエクセルへ持ってくる形にすると差が縮みます。