excel
原価管理をエクセルでやるときの落とし穴｜工数との繋ぎ方
原価管理をエクセルで始めようとして詰まるのは、表の作り方ではありません。表は作れます。詰まるのは、その表に入れる実績の数字が集まらないところです。材料の金額は請求書から拾えますが、誰が何時間その案件に関わったかは、誰かが記録していなければどこにも存在しません。
先に結論を書きます。エクセルでの原価管理が続くかどうかは、関数の使い方ではなく、工数を記録する仕組みを別に持っているかどうかで決まります。表の設計に時間をかけても、労務費の元となる時間が集まらなければ、埋まらない列が残ります。逆に、記録する仕組みがあれば、集計はエクセルで十分に足ります。
この記事では、原価管理で何を突き合わせるのか、シートの組み方、使える関数、工数が集まらない落とし穴、無料のひな形の選び方、そして限界の合図と次の手までを順に扱います。
エクセルで原価管理を始める会社が多い理由
原価管理を始めようという話が出るのは、たいてい利益率が下がったときです。売上は増えているのに手元に残らない。どの案件で削られているのかが分からない。この状態を解消するために、案件ごとの原価を出す必要が出てきます。
そして最初に選ばれるのがエクセルです。理由は5つあります。
第1に、追加の費用がかかりません。すでに社内で使っている道具なので、新たな契約も稟議も不要です。始めるまでの時間が最も短い選択になります。
第2に、自由度が高いことです。自社の原価の構造がそのまま表にできます。専用のシステムでは決まった項目に合わせる必要がありますが、エクセルなら項目を自社の実態に合わせられます。工事の進み方や、案件の単位が業種によって違うことを考えると、この自由度は実際に効きます。
第3に、扱える人が既に社内にいることです。新しい道具の操作を覚える期間が不要で、引き継ぎもしやすくなります。表計算のスキルは職種をまたいで共通しているので、担当が代わっても止まりません。
第4に、ひな形が無料で手に入ることです。業種ごとの原価管理表が公開されており、自社の形に近いものを選んで直せます。ゼロから設計する必要がありません。
第5に、小規模な範囲なら精度が十分に出ることです。案件の数が月に数十件までであれば、エクセルで計算した原価と、専用のシステムで計算した原価に実質的な差は出ません。
エクセルは、表計算ソフトのなかでも特に多機能で柔軟性が高く、原価管理に対応できるツールです。実際に、多くの建設会社がエクセルを使用して、原価管理を行っています。 出典: andpad.jp
つまり、エクセルを選ぶことは妥当な判断です。問題は、選んだあとに何で詰まるかを知らずに始めることです。
原価管理で突き合わせる2つの数字
表を作る前に、何と何を比べるのかをはっきりさせます。ここが曖昧なまま表を作ると、数字は並ぶのに判断に使えません。
原価管理の中心は、見積もった原価と、実際にかかった原価を突き合わせることです。見積もりの段階で「この案件は材料に40万円、人件費に60万円」と想定します。終わったあとに、実際にいくらかかったかを出します。この差が、次の見積もりを直す材料になります。
原価の中身は3つに分かれます。材料の費用、人の時間にかかる費用、そしてどの案件に紐付くか直接には決められない費用です。3つめは、家賃や共通で使う道具の費用で、何らかの基準で案件に割り振ります。
このうち、集めるのが難しいのは2つめです。材料の費用は請求書と発注の記録から拾えます。3つめは月次の合計を按分すれば出ます。しかし人の時間は、記録していなければ存在しません。ここが原価管理の実務で最も大きな壁になります。
差が出たときに見る観点も決めておきます。数量が想定と違ったのか、単価が想定と違ったのか。材料が余分に必要だったのか、仕入れの値段が上がったのか。この区別ができる形で記録しておかないと、差の原因が特定できず、次の見積もりに反映できません。
そして、比べる相手がない状態で原価だけを出しても、判断材料にはなりません。3件ほど過去の案件を遡って集計して、比較の基準を作ってから始めるほうが、最初の1件から使える表になります。
シートの組み方は3層で足りる
エクセルで原価管理の表を作るとき、シートの分け方が後の手間を決めます。効く形は決まっていて、3層に分けます。
第1層は、変わらない情報のシートです。案件の一覧、材料の単価、人ごとの時間あたりの単価、費用の分類。ここは月に数回しか直しません。
第2層は、実績を入力するシートです。1行に1件の記録を書きます。日付、案件、費目、数量、単価、金額、記録した人。この形を守ることが最も重要です。
第3層は、集計のシートです。第2層のデータから、案件ごと、月ごとの数字を出します。ここには手で数字を入力しません。すべて第2層からの計算にします。
この3層で最も破られやすいのが、第2層の1行1件です。見やすさを求めて、案件ごとに列を横に増やしたり、月ごとにシートを分けたりしたくなります。しかしこれをやると、集計が一気に難しくなります。案件が追加されるたびに集計の数式を直すことになり、直し漏れが数字の誤りになります。
縦に伸びる形は、見た目は不便です。1,000行を超えると目で追えません。しかし集計はピボットテーブルで行うので、目で追う必要はありません。入力するシートは見やすさを捨てて、見るのは集計のシートだけにします。
もう1つの原則は、計算と入力を同じセルに混ぜないことです。金額の列を数量×単価の計算式にしておくと、実際の請求額が計算と違ったときに上書きされ、以降その行だけ計算が効きません。計算した金額と、実際の金額を別の列にして、差が出た行が分かる形にします。
シートを保護する設定も入れておきます。入力してよいセルだけを開けて、数式が入っているセルは触れないようにします。数式が壊れる事故は、悪意ではなく、貼り付けの操作で起きます。
使う関数と機能は4つで足りる
原価管理の表に必要な機能は限られています。難しい関数を覚える必要はありません。
第1はSUMIFSです。条件を付けた合計で、案件ごと、費目ごと、月ごとの集計はこれで出ます。原価管理の集計の大半はこの関数で足ります。
第2はピボットテーブルです。1行1件で入力したデータを、案件別や費目別に集計して並べ替えられます。集計の軸を変えるたびに数式を書き直す必要がなくなるので、第3層の集計はピボットテーブルで作るのが実用的です。
第3は入力規則です。案件名や費目を自由に入力させると、表記の違いで集計が分かれます。同じ案件が「A社倉庫工事」と「A社 倉庫工事」で2つに分かれると、原価が半分ずつ計上されます。一覧から選ぶ形にして、手入力を止めます。原価管理の数字が合わない原因で最も多いのがこれです。
第4はテーブル機能です。範囲をテーブルにしておくと、行を追加したときに数式と書式が自動で広がります。範囲を固定した数式は、行を足したときに新しい行が集計から漏れます。この漏れは気づきにくく、月末に合計が合わないという形で現れます。
関数のスキルとしては、この4つを使えれば原価管理の表は作れます。それ以上の作り込みは、担当者以外が触れない表を生みます。配列を使った複雑な数式で作られた表は、作った人が異動した時点で誰も直せなくなります。読める範囲で作るほうが、長く使えます。
なお、表計算の上限まで使うことはまずありません。1枚のシートに入る行は1,048,576行、列は16,384列です。この上限に当たる前に、計算の重さで実用の限界が来ます。数式が数万行に広がると、開くだけで時間がかかるようになります。
落とし穴の1つめ、工数が集まらない
ここが本題です。エクセルで原価管理を始めた会社が半年で止まる理由の大半は、労務費の元となる時間が集まらないことにあります。
材料の費用は、請求書と発注の記録があるので、あとから拾えます。しかし時間は違います。先週の火曜に誰がどの案件に何時間関わったかは、その日に記録していなければ再現できません。1週間経つと、本人も正確には思い出せません。
よく取られる方法が、月末に自己申告で工数を集める形です。しかしこの数字は、勤務時間の合計に合わせて逆算されたものになります。本人は正確に書こうとしますが、記憶が残っていないので、だいたいの割合で埋めます。この数字で計算した原価は、案件ごとの差を見るには精度が足りません。
日報で集める形も取られます。これは精度が上がりますが、日報を書く手間が増えます。書く手間が増えると、まず正確さが落ち、次に提出が遅れ、最後に止まります。1か月続いた日報が3か月目に止まるのは、よくある展開です。
精度を出すために必要なのは、記録の手間を作業の流れの中に埋めることです。案件ごとの作業を管理している場所があり、そこで作業を進めるついでに時間が残る形であれば、追加の手間がほぼ発生しません。逆に、原価管理のためだけに記録を求めると、それは純粋な追加の作業になり、続きません。
割り切る判断もあります。人ごとの時間あたりの単価を細かく分けず、部門の平均で計算する。時間の記録を15分単位ではなく30分単位にする。精度を落としても、記録が続くほうが、精度の高い記録が3か月で止まるより役に立ちます。始めるときに精度を上げすぎないことが、続けるための条件になります。
落とし穴の2つめ、ファイルが分かれていく
エクセルの原価管理でもう1つ起きるのが、ファイルが増えることです。
原因は、同時に触れないことにあります。1人が開いている間、他の人は編集できません。編集したい人は、自分の手元に複製を作って作業します。そして戻すのを忘れます。1週間後に、どちらが最新なのか分からない2つのファイルが存在します。
共有の仕組みで同時編集ができる環境なら、この問題は軽くなります。ただし軽くなるだけで、消えません。同じセルを同時に直したときの扱いや、数式が壊れたときにどちらの版が正しいかの判断は残ります。
派生して起きるのが、月ごとにファイルを分ける運用です。ファイルが重くなるので分けたくなりますが、分けると年間の集計ができなくなります。年間で見るために、集計だけを別のファイルに写す作業が発生し、写し間違いが入ります。
対策は3つあります。1つめは、入力するファイルを1つに固定して、複製を作らないことです。2つめは、入力の担当を1人か2人に絞ることです。全員が入力する形にするほど、分裂の確率が上がります。3つめは、変更の履歴が残る場所に保存することです。誰がいつ何を変えたかが追えれば、壊れたときに戻せます。
どの対策も、エクセルの機能の問題ではなく運用の決め方の問題です。表の作り方をどれだけ工夫しても、この部分は解決しません。
落とし穴の3つめ、分かるのが終わってから
3つめは、集計の時期の問題です。月次で集計する形にすると、原価が想定を超えていることに気づくのは、月が終わってからになります。
工事や制作の案件では、月が終わった時点で作業の大半が終わっています。超えていることが分かっても、打てる手がありません。原価管理の目的が「次の見積もりを直すこと」だけであれば、これでも役に立ちます。しかし「いま進んでいる案件の赤字を防ぐこと」であれば、月次では遅すぎます。
途中で気づくためには、進行中の消化率が見える必要があります。見積もりの人件費に対して、いま何割の時間を使ったか。作業がまだ半分なのに時間を7割使っているなら、その時点で打てる手があります。人を追加するか、範囲を調整するか、追加の費用を相談するか。
この見え方を作るのに必要なのは、集計の速さではなく、記録の速さです。作業の時間がその日のうちに残っていれば、途中の消化率はいつでも出せます。月末に集めている限り、途中では分かりません。
もう1つの対策として、案件を小さく区切る方法があります。3か月の案件を1つの単位で見るのではなく、工程ごとに区切って、工程が終わるたびに突き合わせます。区切りが細かいほど、気づくのが早くなります。エクセルの表の側では、案件の列に工程の列を足すだけで対応できます。
費目をどこまで細かく分けるか
表を作る段階で必ず迷うのが、費目の細かさです。細かく分けるほど分析はできますが、入力の手間が増えます。
判断の基準は1つです。その費目を分けたことで、次の行動が変わるかどうか。材料の費用を「鋼材」と「木材」に分けて、仕入れ先を変える判断ができるなら分ける価値があります。分けても同じ対応しかできないなら、合計のままにします。
実務で有効な粒度は、費目を8つ前後に収める形です。材料、外注、人件費、運搬、機械の使用料、消耗品、間接費、その他。この程度であれば入力の選択に迷わず、集計の表も1画面で読めます。20を超えると、入力する人がどれを選ぶか迷い始め、同じ内容が別の費目に入ります。
細かさが必要になったときは、費目を増やすのではなく列を足します。材料の費目を1つに保ったまま、材料の種類の列を別に持つ形です。こうすると、通常は費目で集計し、必要なときだけ種類で分けられます。費目を増やすと過去の記録と比べられなくなるので、この方法のほうが後戻りできます。
もう1つ、案件の単位についても同じ判断が必要です。1つの受注をそのまま1案件にするか、工程ごとに分けるか。分けるほど途中で気づけますが、入力のときにどの工程かを選ぶ手間が増えます。3か月を超える案件だけ工程で分ける、という線引きが実務では収まりやすくなります。
始める段階では粗く作ります。細かくしたくなったときに足せる構造にしておけば、最初から細かく作る必要がありません。細かく作った表は、入力が続かないという理由で使われなくなります。
間接費の割り振りは1つの基準に絞る
案件に直接紐付かない費用の扱いは、原価管理でいちばん議論が長引くところです。家賃、共通で使う道具、管理部門の人件費。これらを案件にどう割り振るかで意見が分かれます。
割り振りの基準として使われるのは、主に3つです。案件ごとの売上の割合、案件に投入した時間の割合、そして案件の件数で等分する方法です。どれが正しいということはありません。自社の費用の発生の仕組みに近いものを選びます。
選ぶときの注意は、基準を1つに絞ることです。費目ごとに違う基準を使うと、集計の数式が複雑になり、数字を検算できなくなります。検算できない数字は、会議で疑われた時点で使えなくなります。1つの基準で全部を割り振り、明らかに合わない費目だけを例外として直接計上します。
割り振った金額の精度を追いすぎないことも重要です。間接費の割り振りは、どの基準を選んでも近似です。近似の精度を上げる作業に時間をかけるより、直接かかる費用の記録の精度を上げるほうが、判断に効きます。案件ごとの差が出るのは、ほとんどの場合で直接の費用の側です。
実務的な進め方としては、最初の3か月は間接費を割り振らず、直接の費用だけで集計します。案件ごとの差が見えるようになってから、間接費を足します。間接費を最初から入れると、どの数字の違いで差が出たのかが分からなくなります。
なお、割り振りの基準を年度の途中で変えると、前後の比較ができなくなります。変えるなら年度の区切りに合わせます。変えた事実と、変えた理由を表の中に書き残しておくと、翌年に見た人が混乱しません。
出た差を次にどう使うか
原価管理の表を作る目的は、数字を並べることではなく、次の見積もりを直すことです。この循環が回り始めて初めて、作った労力が回収されます。
回し方は決まっています。案件が終わったら、見積もりと実績の差が大きかった費目を2つだけ選びます。全項目を検討すると時間がかかって続きません。2つに絞って、なぜ差が出たのかを1行で書きます。数量が想定より多かったのか、単価が想定と違ったのか。この区別だけでよいです。
次に、同じ種類の案件の見積もりの前に、その1行を読みます。読む仕組みがなければ、記録した内容は使われません。見積もりを作る手順の中に、過去の差を読む工程を入れます。この工程があるかどうかで、原価管理の価値がまったく変わります。
5件ほど蓄積すると、差の出方に癖が見えてきます。特定の種類の作業で常に時間が超過している、特定の仕入れ先で単価の変動が大きい。癖が見えたら、見積もりの計算に反映します。個別の案件の反省ではなく、計算の前提を直す作業になります。
もう1つの使い方が、受けるかどうかの判断です。過去の実績から、種類ごとの実際の利益率が出ます。利益率が低い種類の案件は、価格を上げるか、受ける件数を絞る判断ができます。この判断は経営の側の話になりますが、材料になる数字は原価管理の表から出ます。
うまく回っている状態の見分け方は、見積もりの精度が上がっているかどうかです。見積もりと実績の差が案件を重ねるごとに縮んでいれば、表は役目を果たしています。差が縮まないなら、記録の精度か、読む工程のどちらかが欠けています。
無料のひな形をどう選ぶか
原価管理表のひな形は、無料で公開されているものが多くあります。使う価値はありますが、選び方があります。
第1に、自社の案件の単位と合っているかを見ます。工事の単位で原価を出す表と、製品の単位で出す表では、構造が違います。単位が違うひな形を直すより、単位が合っているひな形を探すほうが早く終わります。
第2に、1行1件の形になっているかを見ます。案件ごとに列が横に伸びる形のひな形は、見やすく作られていますが、案件が増えると集計が壊れます。縦に伸びる形のものを選びます。
第3に、数式が読める範囲で作られているかを見ます。開いて数式を1つ見て、意味が分からなければ、自社では直せません。配布されている状態で使える場合はよいのですが、必ず直したい箇所が出ます。
第4に、入力規則が設定されているかを見ます。設定されていなければ自分で足します。ここが無い表は、表記の違いで数字が分かれます。
おすすめの使い方としては、ひな形をそのまま使わず、構造だけを真似て自社で作る形です。項目を自社で埋めながら作ると、どの数字が集まらないのかが作る過程で分かります。これは完成したひな形を受け取ると気づけません。
なお、ひな形を探す時間は長くかけないことです。2時間探して合うものがなければ、3層の構造で自分で作るほうが早く終わります。表の作成自体は半日の作業です。時間がかかるのは、そのあとの記録の仕組みのほうです。
入力を続けさせるための工夫
原価管理の表が止まる直接の原因は、入力が途切れることです。入力が続く形にする工夫を4つ挙げます。
1つめは、入力する場所を1か所に固定することです。案件ごとにファイルが分かれていると、どこに書くかを考える手間が入ります。考える手間は小さく見えて、続くかどうかを分けます。
2つめは、入力にかかる時間を1件あたり30秒以内に収めることです。選ぶだけで済む項目を増やし、自由に書く欄を減らします。金額と数量以外は、すべて一覧からの選択にできます。
3つめは、入力の締めを週単位にすることです。月末にまとめて入力する運用は、記憶が残っていないため精度が落ちます。毎日を求めると負担が重く、週1回であれば思い出せる範囲に収まります。金曜の終わりに15分と決めておくと定着します。
4つめは、入力されていないことが分かる形にすることです。案件ごとに、最後に入力された日付を集計のシートに出します。2週間入力がない案件が並んでいれば、声をかける対象がすぐ分かります。入力されていないことに気づけない表は、気づいたときには数か月分が空いています。
これらはすべて、表計算の機能ではなく運用の設計です。原価管理の表で失敗した話を聞くと、表の作りが悪かったという説明になることが多いのですが、実際に止まっているのは入力の側です。表を作り直す前に、入力の手間と締めの周期を見直すほうが効きます。
限界の合図と、工数との繋ぎ方
エクセルで続けるか、別の手段に移るかの判断が必要になる時期が来ます。合図は決まっています。
エクセルでの原価管理は、小規模な運用では非常に効果的です。しかし、製品数や工程数、管理項目が増えてくると、次第に限界を感じることが出てきます。具体的には、以下のような兆候が見られた場合、別の手段を検討するタイミングです。 出典: smartf-nexta.com
実務で現れる合図は4つです。1つめは、集計に毎月半日以上かかっている状態です。2つめは、数字が合わない原因を探す作業が発生している状態です。3つめは、最新のファイルがどれか分からなくなったことがある状態です。4つめは、工数の数字が自己申告の逆算になっている状態です。
このうち1つめから3つめは、エクセルの中で対処できます。ピボットテーブルに寄せる、入力規則を足す、保存先を1つに固定する。表の作り方と運用の決め方の問題です。
4つめだけは、エクセルの中では解決しません。工数の記録は、作業をしている場所で発生させる必要があります。原価管理の表に入力を求める形では、記録が続きません。
進行を管理する板を使っている場合、この記録は作業の流れの中に埋められます。案件ごとの作業が板の上にあり、進め方を変えるついでに時間が残る形であれば、追加の作業がほぼ発生しません。板から出た時間の記録を、エクセルの第2層のシートに写して集計する。この分担にすると、表の側は集計に専念できます。
板の側で必要なのは、案件と作業の単位が原価の単位と揃うことと、期間で並べ替えられることです。カンバンとガントとカレンダーを同じ板で切り替えられるかはできることで確かめられます。表計算で工程表を引いている場合、線を引き直す作業がそのまま消える部分でもあります。
道具を選ぶ段階では、進行の管理と課題の管理のどちらを軸にしているかで、記録の残り方が変わります。課題の管理を軸にしたものとの違いはBacklogとの比較、板の見やすさを軸にしたものとの違いはTrelloとの比較、表の作り込みで管理する考え方との違いはNotionとの比較に項目ごとに並べています。
費用については、原価を管理する目的で機能の上位の枠を選ぶ必要はありません。機能で絞らず、区切るのは人数とボードの数だけという区切り方であれば、人数と板の数だけを見て決められます。条件は料金に出ています。既に使っている板から移す手順はTrelloからの移行、始め方で詰まりやすい点はよくある質問に集めてあります。
最後に1つ。原価管理をエクセルでやること自体は、小さくない規模でも成り立ちます。止まるのは表の限界ではなく、工数が集まらないことです。表の作り込みに時間をかける前に、時間をどこで記録するかを決めるほうが、結果として早く形になります。
Q1. 原価管理をエクセルで行うのは何件くらいまで可能ですか？
案件が月に数十件までであれば、専用のシステムで計算した原価と実質的な差は出ません。判断の基準は件数ではなく、集計に毎月半日以上かかっているか、数字が合わない原因を探す作業が発生しているかです。どちらかが起きたら運用の見直しが必要です。
Q2. 工数はどうやって集めるのが正確ですか？
作業をしている場所で記録が残る形にしてください。月末の自己申告は、勤務時間の合計に合わせた逆算になるため、案件ごとの差を見る精度が出ません。精度を落としてでも、記録の手間を作業の流れに埋めるほうが続きます。30分単位でも十分に使えます。
Q3. 原価管理表のシートはどう分ければよいですか？
変わらない情報、実績の入力、集計の3層に分けてください。実績の入力は1行1件を守り、案件ごとに列を横に増やさないことが最も重要です。集計のシートには手で数字を入力せず、すべて入力シートからの計算にします。集計はピボットテーブルで作ります。
Q4. 無料のひな形はそのまま使ってよいですか？
構造だけを真似て自社で作るほうが結果的に早く終わります。自社の案件の単位と合っているか、1行1件の形になっているか、数式が読めるか、入力規則が設定されているかを確かめてください。探すのに2時間かけても合うものがなければ、3層の構造で作るほうが早いです。