エクセルで棚卸し計算をしてみよう！
棚卸しは、会社や各部門の成績や今後の方向性を決定する上で、非常に重要なものです。
会社からお給料をもらって仕事をしている以上、会社に利益を残すことは従業員の使命でもあります。
製造・流通・小売など、仕入れと販売の差額で利益を生み出す職種には、この「棚卸し」が必要不可欠なものになります。
一人ひとりが数値に対して意識を持って仕事ができるよう、棚卸し計算はマスターしておくべき項目です。
計算が苦手な人や、いまさら人に聞くのが恥ずかしい人は、ここでこっそり習得しておきましょう。
覚えられない場合は、このページをブックマークするかエクセルで簡単にできるようにしてしまいましょう。
なんで棚卸しをしなきゃいけないの？
なんで棚卸しをしなければならないのでしょうか？
「会社」は「適正な利潤を得て労働力の再生産をする」ことが社会的責任であると言われます。
つまり、利益を出して従業員に給料を払う（雇用を創出する）という、ごく当たり前のことです。
また、その他にも国へ納める税金や株主への配当等もあり、営業収支を正確に申告しなければなりません。
これらは「財務会計」と呼ばれ、社外へ報告しなければならない内容であるため、棚卸しをするにしても細かく決まりがあり、適当に済ませられるものではありません。
決算棚卸しなどがこれに当たります。適当にやっていて、それが国税の査察等によって発覚すれば、追徴課税が発生したりします。
悪質な場合は「重加算税」というものが付加され、本来支払うべき税金の３５～４０％の重加算税が追徴課税されます。
きちんとした『棚卸し』ができなければ「重加算税」等の原因を作った張本人として個人的に会社に呼び出され、減給や降格といった処罰が下されてしまうこともあるかもしれません。
そうならないように、しっかりと「棚卸し計算」をマスターしておきましょう。
棚卸しの計算方法
「棚卸し」をする場合、まず求められている数字が何なのかを明確にしなければなりません。
仕入れと販売の差で利益を得る形態では、「荒（粗）利益率」が求められることが多いでしょう。
「荒利益率」を求める計算式（公式）は、以下のようになります（原価法）。
必要な数字は、
- 売上高（期間売上高累計）
- 仕入高（期間仕入高累計）
- 期首在庫
- 期末在庫
期首在庫は、前回期間の期末在庫が今回期間の期首在庫になるのでそれを使えばいいです。
売上高も経理等の事務部門が集計していると思うので、自分で計算する必要はないかと思います。
自分で計算しなければならないのは、「仕入高」と「期末在庫」ではないでしょうか。
仕入高は、自分で仕入伝票や領収書を整理して仕入原価を足し算しなければならないでしょう。
期末在庫は、期間中に仕入れた残りを数えて「原価」で足し算をする必要があります。
また、金額で「いくら儲かったか？」を聞かれた場合、それは「荒利益額」のことを意味しています。
「荒利益額」は、先ほどの公式の分数の上の部分（下の数式の赤い丸の部分）なので、覚えておいて損はないです。
電卓で計算しやすいように、この赤丸の部分にある括弧「（ ）」を外すと、以下のようになります。
「荒利益率」とは、荒利益額を売上で割り算したものです。
よって、最終的に電卓で荒利率を求める場合は、以下の計算式になります。
この計算式は、覚えるか、このページをブックマークするかしておいて下さい。
「あるべき在庫」の考え方
期間の営業の結果として「荒利率」が求められますが、「荒利率」を目標としたときに「あるべき在庫」という考え方があります。
荒利率を「○○％」出すには、「どのくらい在庫があればいいのか？」を計算するときに使います。
また、期間の途中で「今現在儲かっているか？・いないか？」を調べたい時にも使ったりします。
最初の公式をもじっただけですが、以下の計算で「あるべき在庫」が計算できます。
「あるべき在庫」計算に必要な数字は、
- 売上高（売上高予測）
- 仕入高（仕入高予測）
- 期首在庫
- 荒利益率（荒利益率予算・目標）
在庫をザッと計算して「あるべき在庫」に到達していれば「儲かってる」と言えるので、攻めの商売が出来ます。
ギリギリか足りない場合は「儲かっていない」ので、慎重な商売が求められます。
エクセルで「荒利益率」を計算する
計算が覚えられない時や、何度も計算するのが面倒な時は、エクセルに計算式を入れてみましょう。
一度作って保存しておけば、何度でも使うことができます。
必要な数字は、
- 売上高（期間売上高累計）
- 仕入高（期間仕入高累計）
- 期首在庫
- 期末在庫
下の画像を見てください。
１行目に数字が打ってあり、２行目に分かりやすく項目が書いてあります。
仮の数字ですが、「１＝千円」として「０００」を省いたとしたら、
- 売上高 １００００（千）
- 仕入高 ６６５０（千）
- 期首在庫 ６００（千）
- 期末在庫 ５００（千）
「Ａ１」に売上高、「Ｂ１」に仕入高、「Ｃ１」に期首在庫、「Ｄ１」に期末在庫を打ち込みます。
そして「Ｅ１」に荒利益率を求める計算式を入れます。
計算式は、
荒利益率＝（売上高－仕入高－期首在庫＋期末在庫）÷売上高×１００
を入れたいので、
セル「Ｅ１」をクリックして、上の数式バーに
＝（Ａ１－Ｂ１－Ｃ１＋Ｄ１）／Ａ１＊１００
と打ち込んで「Ｅｎｔｅｒ」キーを押します。
すると、「３３」という数字が計算されて出てきます。
これは小数点第一位が四捨五入されているので、小数点第一位まで表示させましょう。
セル「Ｅ１」をクリックしてから、上の画像の赤丸の部分「←．０」のようなものをクリックしてください。
「３３」が「３２．５」に変わったと思います。
これが求められた「荒利益率」です。
エクセルで「あるべき在庫」を計算する
次に、先ほどのシートをそのまま使って、「あるべき在庫」の計算式を入れてみましょう。
下の画像を見てください。
便宜上、項目の順番を変えたくなかったので、「Ｄ３」に計算式を入れるようにしてあります。
仮の数字として、
- 売上高 １００００（千）
- 仕入高 ６６５０（千）
- 期首在庫 ６００（千）
- 荒利益率 ３３．０（％）
「Ａ３」に売上高、「Ｂ３」に仕入高、「Ｃ３」に期首在庫、飛んで「Ｅ３」に荒利益率を打ち込みます。
次にセル「Ｄ３」に計算式を打ち込みます。
「あるべき在庫」の計算式は、
あるべき在庫＝荒利益率×売上高÷１００＋仕入高＋期首在庫－売上高
と入れたいので、セル「Ｄ３」をクリックして、上の数式バーに
＝Ｅ３＊Ａ３／１００＋Ｂ３＋Ｃ３－Ａ３
と打ち込んで「Ｅｎｔｅｒ」キーを押します。
すると「５５０」という数字が計算されて出てきます。
それが、「荒利率３３．０％を出すときに必要な期末在庫金額」ということになります。
ここまでやって「名前をつけて保存」しておけば、いつでも使うことが出来ます。
計算式が入っていない所に薄く色を塗っておけば、次回からその色塗り部分に数字を入れるだけで簡単に計算できます。
これでもう棚卸し計算に悩む必要はありません。
スポンサーリンク
