エクセルで毎年平均何個製品を消費しているかの関数

Question

Excelで個人商店の商品管理をしています。
今度、年に1度の問屋のセールがあり、できれば1年分数くらいを注文したいのですが、毎年何個注文するかカンで決めていて、注文し過ぎたり、足りなくなったりします。
以下のような計算をExcelで表示できたら便利だとよく考えていました。
、
2010年3月から商売を始めたとして、製品の注文履歴が下記のようなものだったとします。
10年3月1日に50個、8月1日に5個、
11年2月1日に50個、7月1日に50個、
12年7月1日に100個、
13年は注文なし、
14年7月1日に１00個、
15年10月1日に15個。
15年7月14日現在、在庫が40個ある。

11年12年13年14年に、1年平均で何個位製品を消費したのかを割り出す関数はあるのでしょうか？
それぞれの年度の平均数を以下のようなセルに表示できればより便利です。

関数　　11年　12年　13年　14年

 ？    　 82　　105　　40　　92

もし分かる方いらしたら、お教え下さい。
計算方法すら分からないのですが、それだけでも助かります。
宜しくお願い致します。

WindFaller · Accepted Answer

こんにちは。
sakuma8755様

返事を書いている内に躊躇してしまいました。私は、もう現役でもないし、私のような人間の話は、あまり役に立たないかもしれない、と思い始めたからです。PCについては、インストラクターの資格もありますが、そんなことは、あまり大したものではありません。

>小さい商店でも、エクセルだけで、在庫数、販売数、仕入数、その日時を管理するのは難しいでしょうか？

笹原清明という税理士の人が、Excelや弥生を元に、PC管理のコツを書いています。
http://www.maps-keiri.gr.jp/index.html

この著者は、おそらく多くの会社を指導してきた経験からでしょうけれども、紙に書いたものをインプットするのではなく、ダイレクトにPCに入力をすることだと書いています。

しかし、実際、現場とデスクを行き来する私などには、そういうことは出来ませんでした。

・パソコン会計の導入成功のためのポイント

なお、Excelのフォーマットが公開されていますので、それを使えばよいと思います。私がやっていた方法に最も近いものを探してみました。しばらくは、試行錯誤のはずです。

在庫管理表
http://ameblo.jp/nezi-biz/entry-11504399333.html

以下は、在庫管理表の問題点を指摘しています。
エクセル（EXCEL）による在庫管理の限界  
http://www.zaikokanri.com/column/2013/11_13_01.html
1)～5)までの問題は、Excelのままでも、ある程度解消できます。このサイトのように、基幹システムとして導入するのは、足の早い商品に限ります。

品名　前月在庫　仕入　出庫　7月度・在庫　（金額が必要な場合は、単価*在庫)
A1 
A2
A3
B1
B2
B3

このような表を月単位につけていけばよいわけです。
決算とか、締めとかを、商品管理にも是非取り入れて、年度のまとめをつくってください。だいたい、3年ぐらいのまとめを作るうちに、スタイルが固まります。

>「在庫管理がしたいから販売した又は工事で使用した分の商品を記入してくれ」
それは、お父様でなくても、嫌がりますでしょうね。むろん、大きな会社の経営者ですと、真っ先にPC管理に飛びついて、それで夢をみてしまうものですが、会社では、現場にいる人たちは、トップダウンで渋々従うだけのことです。そうではなくて、まず自分の出来るところから始めるというのが、小規模な経営の鉄則です。できれば、販売実績の明細が、自分の元に自然に入るような仕組みを作ったほうがよいでしょうね。

ところで、『パレートの法則』というのをご存知ですか？(Wikipediaより)
・ビジネスにおいて、売上の8割は全顧客の2割が生み出している。よって売上を伸ばすには顧客全員を対象としたサービスを行うよりも、2割の顧客に的を絞ったサービスを行う方が効率的である。
・商品の売上の8割は、全商品銘柄のうちの2割で生み出している。

私が仕事をしている時代には、7・3の法則と呼ばれていました。8割2割の配分が、7割3割に変わるだけです。

つまり、3000点の商品があっても、600点～900点ぐらいしか、商品的には流通していない、ということで、商品群からすれば、もっと少ないことが分かるはずです。在庫管理にしても、当面、2～3ヶ月の1度のわりでもよいので、動く商品群だけの棚卸しをすることをお勧めします。

私は、あまりPC管理を強調したりしません。現場に立つ人のことを考えると、そんなに言えないのです。

なお、初歩的な経営分析などは、日本法令の適当な一冊を買えば、十分だと思います。例えば、商品回転率などは、頭に入れておく必要があるかと思います。

さて、ここから、PCとは関係のない話です。過去の私自身の問題点を投影して述べさせていただきます。

>個人商店なので色々と一人でやらなくてはならないことはありますが、商品管理もしっかりしていきたいと感じました。

もちろん、現場に立つ人の仕事というのは、本当にいろいろあります。私のことを買いかぶっていますが、かつての私の問題点は、それは、sakuma8755様も同じかもしれないので、真摯に申し上げます。

世の中には、日々の雑務に忙殺されて、経営が疎かになる人たちは大勢います。また、そういう雑務に忙殺されていること自体に快感を覚える人もいます。

親子であれ、中小企業の社員であれ、本人がある程度仕事に熟達しますと代わりなどはいません。お互いがお互いの立場を尊重する代わりに、お互いがその仕事を続けてもらいたいと思いますから、縛りあってしまいます。失礼ながら、近い将来、お店を背負っていかなくてはならない身では、商品管理だけでなく、幅広い知識や人脈が必要ではないかと思います。

例えば、商工会や組合などの寄り合いはありませんか？同じ年代の同じ業種との人とはお話になりますか？それに、取引先など幅広い人との交流が重要ではないか、と思うのです。私は、一時期、営業部に配属されかけた時には、部長について、外回りもし期待しましたが、結局は、発注を含めた商品管理であり、会計経理に戻ってしまい、ほとんど、それらに忙殺されていたというのも過言ではありません。

こうした付き合いの中では、いろんなノウハウも聞けることがあります。だんだん、年齢が高くなるにつれて、当たり障りのない話だけで、お互いの情報交換が難しくなります。もし経営を担っていくなら、商店の中だけではなく、いろいろと外を回ってみるのが、ずっと有効だと思います。こんなことは、余計なお世話の話かもしれませんが……。
私の自戒を込めて。

WindFaller · Answer

こんにちは。

私は、以前の仕事はやめてもう15年以上も経ちますが、経験的に、少し、この話の熱が入ってしまいます。

>アイテム数が1万種類くらいあり（錠前関係の仕事で、ブランクキーだけで３０００種類くらいあります）、棚卸はしたことがなく、仕入台帳はありません。

私の取り扱っていた商品の感じとしては、大きさなど、そう大きくは違わないような気がします。ただ、他にも経年変化するものがあって、いくら大量値引きするにしても、まとめ買いができないものもあります。特に、夏場を越えるものは、商品が微妙に変化するものがあります。商品の8割は、金属で、その中で、点数的には、貴金属と卑金属では、卑金属が圧倒的に多く、私が仕事をしていた頃には、金属アレルギーに悩まされていました。

実際、私の取り扱っていたものも、大分類の4種類、中種類で800、それぞれの大きさが変わりますので、やはり小分類まで入れると常時3000ぐらいになります。一回の仕入量は、2ヶ月～3ヶ月で、点数辺りにすると、小分類で200ぐらいでしょうか。

>棚卸はしたことがなく、仕入台帳はありません。

私が仕事をし始めた時もそうでした。むろん、担当部署ではありませんでしたが。私の場合は、輸入品ですので、税務署が本社に来ると、それに応じて必ず税関員が後で来まして、それで、輸入品の仕入状態を調べられます。そこで、当時、新入社員の私を名指しで税関員の取り調べのようなことが始まり、仕入台帳などを調べられますが、それがないとなれば、いろいろと「ご指導」が始まります。しかし、役員や上司たちが、そういう話をまともに聞く気は一切ないのです。もともと、ごまかすことしか考えていなかった人たちでした。

そこで、最初に、まったく知らない私が、人に教わりながら「仕入台帳」を手書きで始めました。これは、私の小さな反乱で、少しずつ、原価の出し方など、会社がごまかしていた部分に、私の反乱は進んでいきました。その頃は、常務の下で働いても、私の自由になるものは、そんなに多くはありませんし、仕入課は、お世辞にも協力的とは言えませんでした。

やがて、バブル期に突入すると、私の取り扱う商品が、会社の粗利の20%を越えるようになり、会社も本格的になり、当初は仕入課のスタッフと一緒と言っていましたが、表面的には仕入課長や部長の許可と言いながら、表面上だけで、その数千点の商品の発注・仕入・検品・支払い予定・在庫管理・支払い、薬事法がらみ、一手を私1人で行うようになりました。ただし、商品の督促やトラブルなど、また、仕入先の接待は、すべて英語で行うというフロックはありましたが。

そこにいたって、始めてパソコン管理を、表計算を使って行うようになったわけです。
それが私のざっくばらんな経歴です。

ところで、PCやコンピュータはお得意なのでしょうか？
実は、私は、仕事では、Microsoft 製品以外では、カード型のデータベースしか、仕事で扱ったことがありません。海外向けのカスタマイズできるアプリなんてないからですが。それも、MS-DOSの時代です。

私の知人の社長が、製造業を立ち上げる時に、自分で選んだアプリケーションを使って、1人で管理していました。製造業というのは、仕掛品と、製品、また部品という区分けがきちんと棲み分けする必要があるのと、決算棚卸しで、そのやり方を間違うと、結構、税金で違いが出てきてしまいます。本人、それでも、旧世代（オフコン時代)のコンピュータの学校にいっていたらしく、本当に器用にPCを扱っていました。（アプリケーションを選択する時は、かなり慎重にしたほうがよいです。意外に、使いこなせないことも多いからです)

結局、中小企業の場合は、会計・経理の担当は別にしても、商品管理は1人で行わなくてはならず、それで、PCに精通せざるを得なくなってしまいます。

>販売した数が分からなくても、1996年4月1日から2014年3月31日までの総仕入数を、（2014-1996=）18で割った数より精度の高い、年度ごとの販売予想数が導き出される計算式は存在するのではないか思います。

仕入側は、受注・発注・在庫がメインになるはずです。

>仕入日と数は、実数を書くと非常に長くなるため、実数ではございません。
それ自体は、分かっています。以下のような考え方で作られているということだけです。

私が#1で書いた計算の方式は

仕入数　－　在庫数　＝　販売数　
という計算ですが、表には、

仕入　| 販売　| 在庫

という順番になっていて、sakuma8755様が必要なのは、本来は「販売数」、月いくつ売れたのか、ということです。販売も在庫の記録もお手元にないという状態では、どんな計算式でも、想像の範囲を越えません。また、sakuma8755様の会社（商店)が、どのような会計経理をしているか、お話で想像つきます。そういう会社も実は多いものです。税金の話を出しましたが、主に製造業の場合などに存在する問題です。

>簿記3級以前落ちたのですが、また勉強してみようかと思いました。
簿記自体は、経理をするわけではないので不要です。いわゆる「経営」や「経営分析」の初歩的な知識さえあれば十分です。

会社全体では数万点の商品群で、私の担当の取り扱い製品全体は億単位でも、個々の製品1個の売上は500～1,000円前後ですから、それほど細かな売上げデータは出てきません。会社でお願いはしていたけれども、話は頓挫してしまいました。経費が押さえられていたので、PCやコンピュータ管理が思ったようにいかなかったのです。そこで、営業データはあるものの「在庫」で販売数を想定するしかなかった、というのが現実です。

これで、一応、2ヶ月～3ヶ月サイクルの発注予定を組める段階まで成功しました。100点満点の内の40点ぐらいですが。

>実際には1995年からの、過去の仕入日と仕入数と品目が、問屋のサイトの履歴で正確に把握できます。
せいぜい調べて3年で十分だと思います。とても、それほど長い期間は、無理ではないでしょうか？

むしろ、販売実績のほうがどうなっているかです。販売はPC管理されていますか？

例えば、コンビニのPOSシステムのように、自分で集計をしなくても、その日の締めに、売上と売上数、粗利を出していくれるようなソフトを導入するとか。既存のままでは、今のお仕事に上乗せになるだけで、それに没頭しても売上には貢献しないと思います。私が仕事をしている時は、大手の開発業者さんですが、こういうのは、経費もバカになりません。例えば、「弥生」シリーズの業者さんのような所に相談してみるのも良いかとは思います。また、組合の仲間に相談してみるというのも良いと思います。

ただし、私の経験から言うと、製造業でもなければ、在庫管理や発注管理を中心ではなく、あくまでも「販売管理」から始めます。財務管理自体は、お母様がおやりになっているようですし、今の所問題がなければ、それはそのままの方がよいと思います。

>年度ごとの販売予想数が導き出される計算式は存在するのではないか思います。
もう一度申しますが、せめて在庫数の確認さえしていればわかるのですが、あまり、長いスパンを考えないで、これから先のことを考えていったほうがよいと思うのです。なるべく、仕入先の問屋の記録を頼らないで、独自の記録を作るような方向に進めていくべきではないかと提案します。

そして、今の段階では、まず、販売管理、早い話が、何かどのぐらい売れて、どのぐらいの粗利があるのか、それを把握することが先決ではないでしょうか。

WindFaller · Answer

仕入台帳と売上台帳は付け合わせなかったのでしょうか？
税務署では何かいいませんでしたでしょうか。それとも、多く税金を払っていたのでしょうか？

決算期というものがあると思います。たぶん、3月末日でしょうか？
それにしたがって、決算棚卸をし、年度の在庫数が確定すると、年間の仕入と年間の販売数などが決定されます。

>1年平均で何個位製品を消費したのかを割り出す関数はあるのでしょうか？
そのような便利な関数は残念ながらありません。
まず、売上台帳から拾い上げていくしかありません。

ただ、ざっと見たところ、年平均で、約50個ほどになるのは分かります。

おそらく、決算期に50個ほどの在庫を抱えていた時期が存在するわけですから、注文単位による値引きもありますが、一度に100個の注文というのは、多すぎることが分かります。多く買って、仕入値引で値段を安くし、在庫を抱えるのがよいか、それとも、適量で購入するのがよいのか、その按分は想像つくかと思います。基本的には、1年分の在庫の持ち越しは多すぎると感じます。

>15年10月1日に15個。
これは、記帳ミスとして、15/01/01　にしました。

添付画像をご覧になってください。ただし、これは、経理的な考え方によるものです。決算棚卸しを基準にして、棚卸しの記帳がなければ、売れていないということにさせていただきます。

ただし、返品はないものとすると、2015/03/31の決算期には、40個ほど残っていたという勘定になります。

エクセルで毎年平均何個製品を消費しているかの関数

こんにちは。

こんにちは。

仕入台帳と売上台帳は付け合わせなかったのでしょうか？

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング