How many units and of which models can be built with the current inventory?

[Christopher7878]

Hi

This business/inventory question comes up again now but I still don't know how to answer it. Please recommend something thanks

I don't even know how to write the question so let's start with this...

Base on the current counted inventory then how many units and of which model can be built?

Example: Either 5 units of A model, 10 units of B and 15 units of C or 4 A, 9 B and 17 C.

I thought of an excel workbook with multiple sheets and 1st sheet is the model list or dashboard. Where the "how many" and "which model" is answered.

The 2nd sheet list all the parts and qty on hand, and maybe the 3rd column lists which BOM or model that a part is in or maybe put this info on the 3rd sheet.

Then if all the parts and subassemblies needed for a model are on hand then we will know how many units of a model can be built, shown on the 1sr sheet.

But then what about models that short only a few parts that if we buy those parts then we can complete those models? We would like to know that on the 1st sheet too.

 

[vletm]

Christopher7878
How would You do it with paper and pencil?
After that ...
Write those step in Excel-sheet instead paper and pencil.

 

[Christopher7878]

OK thanks vletm

Here's the "hand drawing" in Excel...

 

[vletm]

Christopher7878
There were no connection with You give example...
How would You use ... even those informations?
... 'something' is missing!

 

[Christopher7878]

Sorry about that...

I added another page showing the relationship with these parts. The TOP level, the finished products, would be Model A 500100 and Model B 600200. They have 3 levels down. Some parts as part and some others are subassemblies with other parts under them. Some parts are common and used in both Models.

I made it so that we can only build 1 unit of Model A and none for Model B because we are short some parts.

We're short parts 10004301 and 10004201, 1 piece each. We're also short the subassemblies 100015300 and 100015200 (of which the 10004301 and 10004201 belong) but we can't count them because then we would be double count the 10004301 and 10004201, meaning we'll be short 2 pieces each, which is not true.

Then we only need to enter qty data for parts in sheet "QTY On-hand" and the "QTY-Which" sheet will be updated.

 

[vletm]

Christopher7878
hmm?
Still some challenges ...
I maybe could solve how many parts would need ... ( G.. I-columns ... press [ Do It ]-button ) not 99% sure not yet!
I've to 'break' all parts as small 'pieces' as possible (sorry my terms )...
... but next I should try to find needed parts from 'QTY on hand' ... but there are already some parts ready ... hmm?

Someway I could figure this manually ... but ... how to tell that to Excel ... gotta figure later more.

Could You figure, do that [ Do It ]-button something correct ... useful?
When no need to break some parts? ... hmm?

 

[vletm]

Christopher7878
I tried to continue ...
I could find out ... the number of items would be LEFT after build named unit.
No exact, what have You asked, but ... check it

 

[Peter Bartholomew]

I have had a go at flattening the assembly tree to produce a list of detailed parts.

 

[vletm]

Christopher7878
I added few 'features'...

with [Wanted QTYs]
> add needed number of each QTYs for every model which You would like to use and press that button
=> You'll get the result with Your given values

with [Solve MaxS]
> this tries to check model-by-model number of models could make with Your QTY-OnHand-values
=> You'll get results by model ( including missing items for next item)

>> You can use as many models as needed!

(( not fully tested! ))​

 

[Christopher7878]

Hi vletm

Thanks for your help and I've been busy that why I haven't replied.

The "Solve" button option is great!

I noticed some easily-fix errors though, such as part number 100041002. It's not in the list.






There's also this error






I changed the QtyOnHand so that we can build 2 units model A but only 1 model B and hit "Solve" but I still get 4 units total.

The "Needed QTY" are too many, such as we only need 4pcs of 10004100 for 2 units each of both models.





The 10015300 2pcs short is correct but the 10004100 should not be since we have 8pcs on hand. Also why do I have 24pcs of
10004300...

This is awesome and you're almost there...

 

[vletm]

Christopher7878
I don't know Your used terms ... but I try to explain ... someway

case 100041002 ...
as You could see ...
number 'times' Partnumber
number 'times' Partnumber

for some reason ... Your change line didn't work as it works here! ... wider column could also help.
(( also seems that even those buttons-texts would look different ... missing words ))

I changed the QtyOnHand...
The "Needed QTY" ...
That layout is not the final ...
You have mixed two different results
after [ Wanted QTYs ] results and
after [ Solve MaxS ] results
So far, You can see how and which values 'change' after You press those buttons - not only the results!
After [ Wanted QTYs ] - read ONLY those results and same way after [ Solve MaxS ]! - do not mix!
And just for clear ... any model or partnumber has 'broken' as small parts as possible.
Maybe You can have '500100' on Your table (whatever it would be), but I don't use whole '500100'.

Also why do I have 24pcs of 10004300...
I cannot know what do You have now in QTY-OnHand -sheet!
'break' all 'parts' as small pieces as possible and count after that ... how many do You have?

Those are shown in same area and I knew that part should make more clear
... after I have had feedback and I would know what?

BOMs errors ...
If I have gotten some values, then I can only use those values.
I cannot know are those 'correct' or something else.
But for logic - it don't matter.
There can add as many rows as needs as well as as many combinations as needs...

 

[Christopher7878]

Hi vletm

Oh I'm sorry I forgot to include the excel file...

Yes I see. Expand the cells... 2x 10015300, but drop down to another line would be great.






I missed it last time but what do you mean by "break" parts into smallest pieces? You cannot break any part number since it's counted as whole, for that part. "I don't use whole 500100"... well you have too since there are other part numbers with 100 in it, or 50 or 01...

I gave 10004100 0pcs on hand and ran max build and got 6 units for model A. That cannot be since we need 2pcs of 10004100 for model A so how can we make even 1 unit of A? I'm confused now...

 

[Peter Bartholomew]

I ran the small model through using worksheet formulas (including MMULT and MINVERSE). The calculation speed resulting from the use of formulas allowed the total number of models to be maximised using the evolutionary method from Data/Solver.

The calculations were originally implemented using dynamic arrays but, hopefully, the attached solution has been correctly modified for traditional CSE (non-dynamic) array formulas. Two distinct approaches are used for the array calculations. The first works down the assembly tree level by level until no more detailed parts are found. The second approach is mathematically more obscure and relies upon a matrix formula to sum the series of steps used previously.

 

[vletm]

Christopher7878
I'm still waiting for Your clear answers ...
Is below clear ... do it work always?
you have too since there are other part numbers with 100 in it, or 50 or 01...
What is difference between Part Number and BOM number? ... if check from BOMs-sheet
500100 and 10015100 ... to 'break' or not?
You can choose 'breaking level' with [ QTY LEFT ]-cell -- activate it and it would be change 'level'.
Compare both 'level's ... hmm?

 

[Christopher7878]

Hi vletm

Sorry, I've been busy but I haven't forgotten about this.

 

[vletm]

Christopher7878
You have had a ball for few days ... weeks.
You have my previous sample solution in #14 reply.
There are also questions which You could answer.
There are no limits to add any kind of combinations.
You've seen 'animated' solution - You could see how it works now.
Later, that 'animation' could hide.

 

[Peter Bartholomew]

Somewhat self-obsessed -- sorry about that -- but, since this thread has re-emerged, I downloaded my own solution to remind myself what the problem is about and what I had done. What has tempted me to post afresh is that I experimented with changed data and a different solution method in Solver.

The data change was to increase the stock levels by a factor of 10 or thereabouts. As important I switched the solver from evolutionary to Simplex LP, used to maximise the number of models produced within the constraints. The new result to report is that the changes worked and the solution was orders of magnitude faster. As for optimality, the optimisation solution looks good. There are two degrees of freedom (counts of Model A and Model B) so one would expect a solution (at least for a continuous problem) to have two active constraints, which this solution does.