Excel formula/macro help

[Old and In the Way]

Hi Peeps...

I'm doing up a product costing model in Excel. One of my variables is the impact of stuck inventory on a production build consisting of x number of components. Because components are purchased in various MOQ/Reel qtys/per assy usage, the remaining stuck inventory can be quite small or large, depending on the qty to build. I can manually do this by entering a build qty of the upper level assy, say input at Cell C5. Each excess line item is totaled up and summed in a single cell(say cell R55). Here's what I would like to do, hopefully in a formula. I want to test the model for a production build of, say 100-10,000 pcs and list the calculated results of R55. The lowest value of R55 would correlate to the optmized build qty that minimizes the stuck inventory costs.

I know I can extract the MIN value in the column, but not sure how to do a macro or build a formulas that would accomplish the same result. Any suggestions?

[Dead_Parrot]

1. Could you run through a simple example?

I'm pretty sure the answer's "yes" but I'm not quite seeing what you need - Could you do an idiot proof demo?
:)

[Old and In the Way]

2. Sure!

OK, I have created a static model of a product that consists of a printed circuit board and a bunch of electronic components. The bill of material lists each part, unit price, qty used in the assy. Because each part has a different reel size qty, I always have a bunch of parts left over in odd qtys, based on these variables.

So I plug in say 3000 units to build (cell C4) and the model calculates the net value of the unused parts remaining. For example, if I need to buy 5000 resistors (1 per), I'd have 2000 left over @ .005 each = $10.00 of purchased, unused components. My next part I have to buy 1200/reel (3600 pcs total) and I'd have 600 left at, say $5.00 per part or $3000.00. And so on. I sum the total value of excess parts and divide that by the up level build qty to get a per unit burden for stuck (unusable) inventory (say calculated cell R55).

I don't want to do 9900 "what if's" to determine the best qty to build to minimize my stuck inventory.

So I need a simple macro that will automate the model to test cell C4 from a range of 100 to 10,000 (9900 iterations) - for each # in the range, print the value of R55 in the adjacent cell. I can then data sort these 2 columns to list the order of per unit burden from least to most.

Is that clearer?

[Dead_Parrot]

3. Right, got you.

Should be do-able: I'll see if I can cobble something together in the next day or two (unless anyone wants to beat me to it, of course... ;) )

[Old and In the Way]

4. Thanks!

Sic transit gloria

:-)