原価計算といえば製造業では特に重視されていてよく耳にするかと思います。一応、会計的に正しい計算法というのもあるのですが、中小企業ではカスタマイズしたルールを用いてエクセルでざっくり計算していることが多いように思います。
今回は、本格的な原価計算とまではいかないけど、仕入をエクセルで集計するためには、どのように入力していけばいいのか少し考えてみたいと思います。
・集計単位で番号を付加する
製造番号やシリアル番号など名称に違いはあっても、恐らくどの会社でも管理用の番号を付加していると思います。受注単位、製造単位、単品ごとなど付加する単位は会社によって違うと思いますが、重要なのは原価を求めたい単位で番号を付加することです。例えば、受注はA製品×2台で受注番号を付加しているが、原価集計したいのはA製品それぞれ1台という場合、受注番号とは別に番号が必要になるということです。
一般的にはこういう場合、枝番で単品ごとの単位にしていることが多いと思います。そこでよくあるのが枝番を無視する工程の存在です。無視している工程では原価集計ができなくなるので注意が必要です。
ここではA製品×2台に対して001というNoを割り振ったとします。
・原価発生時エクセルに入力
まず原価として集計したいものは全てエクセルに入力することとします。会社によっては販管費相当でも個別の原価に入れたりしていると思います。例えば、運送費を個別の原価に入れて集計したい場合、運送費も入力するということです。
・仕入入力について
買掛単位ではなく原価単位の行数で入力します。
例えば、A製品(No001)とB製品に使う材料を購入したら1行の明細で100円となった場合。2行に分けてA用に50円、B用に50円というように入力します。会計では買掛単位として扱うことが多いと思うので、必要であればそれぞれ別管理が必要になるかもしれません。
仕入が1行でも、計算するタイミングで何かしらの基準でA製品とB製品に対して按分して計算するばいいのでは？と思われるかもしれません。もちろん専用ソフトでしっかり管理されていれば可能ですが、そうでないなら、最初から明細を原価単位で入力しておくほうが楽だと思います。
・エクセルの作り方
今回は入力、単価マスタ、集計結果を作っています。実際はそれぞれ別シートにして項目も多くなるかと思います。
ポイントとなる関数はSUMIFS、VLOOKUP、INDIRECTです。関数自体は他のサイトに分かりやすい解説があるのでそちらを見ていただくとして、どのように使っているのかというと、入力から単価マスタの参照はVLOOKUPを使い、集計結果から入力の参照はSUMIFSを使い、それぞれ最終行をINDIRECTで指定しています。
この中でも一番重要なのがSUMIFSです。単価マスタはなくても大丈夫なので、もっと簡単にしたい場合は入力と集計結果だけ作って、SUMIFSで集計しても大丈夫かと思います。