ある製品の部品在庫金額を最小にする求め方について

質問

ボールペンなどいくつかの部品を組み立てて一つの製品になるものについて、
「部品の在庫金額を最小にするにはあと何個つくればいいか」を求めるには
どうすればよいでしょうか。

例えば、4つの部品を1つずつ使用して組み立てて作る製品があった場合、それぞれの
部品には現在の在庫数、1個当たりの単価、新たに発注する場合の最小ロットサイズが
あると思います。

部品A：在庫52　単価15円　最小ロットサイズ100個
部品B：在庫35　単価5円　最小ロットサイズ150個
部品C：在庫126　単価3円　最小ロットサイズ500個
部品D：在庫21　単価1円　最小ロットサイズ200個

部品Dを全て使うと21個の製品ができ、残った在庫金額を計算すると850円となります。
部品Bを全て使うと35個の製品ができますが、部品Dは足りなくなるので200個発注する
ので、それを加味しても残った在庫金額を計算すると714円と上よりも安くなります。

在庫数、単価、ロットサイズを組み合わせて、最終的に在庫として余る金額が最小と
なるには、何個の製品を作ればいいのかをエクセルで算出するにはどうやれば
いいでしょうか。
また、最小金額の他に、製品10個、100個単位などで作った場合の在庫金額の推移を
段階的に見たい場合もエクセル上で計算(シミュレーション)可能でしょうか。

関数、VB問いませんので、分かる方はぜひ教えて頂けますと幸いです。
よろしくお願いいたします。

回答No.1

Proof4 · ベストアンサー

わたしがVBAできない人なので、マクロを使わないで何とかしました。
文章だけで説明するのだと冗長になりわかりづらいかと思い、試験的に作成したものを閲覧可能な状態にしておきました。参考にしてください。（文末URL参照）

大雑把に説明します。
[1]
在庫が不足の場合、（作る製品の数 - 在庫）を最小ロットで割ったときの値の小数点以下を繰り上げ、それに最小ロットを掛けることで、追加で必要になる数を求めます。（添付画像で数式表示されている箇所）
[2]
最後に残った在庫状況を足し算と引き算で計算
[3]
SUMPRODUCTS関数で、それらの合計金額を算出
これが、おおまかなつくりです。（参考ブックのSheet1）
このつくりをそのまま使用して、作る製品の数によって表を作成します。つづけてグラフを生成すると、視覚的にどこいら辺が最小になるかがわかります。（参考ブックのSheet2）

ズバリこの数の時！とはいきませんが...お役に立てれば。

https://drive.google.com/file/d/0BxB-fpxIqYrEN09HekFYS1N6Wlk/view?usp=sharing
※上記のファイルは予告なく削除されることがあります。

お礼

詳しい解説とサンプルファイルを付けて頂き、ありがとうございます。
初めて使う関数がありましたので、内容の理解に時間がかかってしまいまして、お礼の返信が遅れて申し訳ありません。

Proof4さんに作って頂いたサンプルファイルを元に、実務で使うデータを入れ込んで試してみました。
その結果、私が希望していたデータに限りなく近いですし、特にグラフで視覚的に在庫金額が下がるポイントが複数分かりますので、とてもいいです。

最低ロットサイズの他に積上げ数というものがありましたので、数式を多少アレンジさせて頂きました。
VBを使わなくても、品目数が増えたら行と列を追加していくだけで対応できるので、これからも活用させて頂きます。
行列のMMULT関数はとても便利ですね。

この度は丁寧に解説して頂き、本当にありがとうございました。
サンプルファイルであれこれ試すことが出来たので、理解が深まりやすかったです。

ps：
お礼コメントを入力したのに先にベストアンサー設定をしたら、コメントが消えてしまいましたので、再入力しました。
ベストアンサーはチェックボックス式にしてほしいな・・