• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Allocation of parts& their cutting program no. to their purchase order

Gaurav Vohra

New Member
We've three tables:
Table1: Cutting datasheet, here we store data for part no., it's cutting program no. & qty. cut in that program.
Table2: Part & program allocation data, this sheet is to be dynamically updated using data entered in sheet 1 & sheet 3 such that any part is allocated qty. equal to given in po. This qty. may be fulfilled by multiple cutting programs. We need list of all programs & qty. in each program that is used to complete qty. of that part in that particular PO.
Table3: Excess stock for future consumption, this sheet is dynamically updated based on the difference of qty. of part cut & qty. required of that part in pending Pos.

Table 1 & Table 2 will keep on expanding.
 

Attachments

  • part allocation query.xlsx
    13.9 KB · Views: 15
Hello everybody.
As suggested by Vletm, I've revised the outlay of the sheet. I've shown multiple views of the same tables as data is entered progressively. Also, I've added remarks on the extreme right in the coloured box.

We've three tables:

Table1: Cutting datasheet, here we store data for part no. & it's cutting program no.(nest no.) & qty. of that part cut in that nest.

Table2: Order received for different parts, for each entry in this table we've to allocate two types of data:
a) nest no. in which that part was cut.
b) quantity of that part allocated to this po.
This is to be dynamically updated using data entered in Table1 & Table 3 such that any part is allocated qty. equal to given in po. This qty. may be fulfilled by multiple cutting programs(nests). We need a list of all nests & qty. in each nest that is used to complete qty. of that part in that particular PO.

Table3: Excess stock for future consumption, this sheet is dynamically updated based on the difference of qty. of part cut & qty. required of that part in pending Pos.

Table 1 & Table 2 will keep on getting entries.
 

Attachments

  • part allocation query.xlsx
    23 KB · Views: 4
Gaurav Vohra
Have You reread Your T&U-column 'comments'?
T3 ... okay?
T20 ... something happened which some rules or not?
If this is clear for You, then okay ... for You otherwise check again.
 
Hello Vletm, I've checked, it is okay. Kindly read the following:
1) A1, cut qty.2, required 1, allocated 1, moved to surplus 1 (2-1)
2) A2, cut qty.3, required 0, allocated 0, moved to surplus 3 (3-0)
3) B1, cut qty.2, required 3, allocated 2, moved to surplus 0 (2-2), shortfall 1 (3-2)
4) D1, cut qty.2, required 0, allocated 0, moved to surplus 2 (2-0)
 
Hello everybody!!

As suggested by Vletm, I've revised the outlay of the sheet.
I've introduced 1 more table for clarity.
I've shown multiple views of the same tables as data is entered progressively, any changes which happen to data in that step are marked.
Also, I've added remarks on the extreme right in the colored box.

We cut parts out of sheets using a machine. For the machine to cut, we create cutting programs ( referred to nests in all further discussion).
We've 4 tables/Worksheets:

Table1: Cutting datasheet, here we enter data manually for part no. & it's cutting program no.(nest no.) & qty. of that part cut in that nest.

Table2: Order received for different parts from our customers.

Table3: We've to allocate two types of data in Table 3 for each line item/entry in the table2:
a) nest no. in which that part was cut.
b) Out of the total qty. cut for that part in that nest, how much is allocated to this po.

This is to be dynamically updated using data entered in Table1 & Table 2 such that any part is allocated maximum qty. equal to given in po. This qty. may be fulfilled by multiple cutting programs(nests). We need a list of all nests & qty. in each nest that is used to complete qty. of that part in that particular PO. This is done on FIFO basis.

Table4: Excess stock for future consumption, this table is dynamically updated based on the difference of qty. of part cut & qty. required of that part in pending Pos.

Table 1 & Table 2 will keep on getting entries.
 

Attachments

  • part allocation query.xlsx
    21.8 KB · Views: 6
Gaurav Vohra
If You're trying to write about every step ... where comes C20 value?
Are those Nest no. and part no. some kind of samples? ... no. would mean something like number?
 
Value in C20 is manually entered, based on how many of a particular part is accommodated in a particular nest no. It is not a calculated value.
Nest no. & Part no. are not necessarily no.s, they can be text or (no.+text). I think I should've used the title as nest name & part name.
Also in the attached file, I've shown the sequence of events by using only one part no./name. There can be thousands of part names/no.s
 
Gaurav Vohra
As You have written ...
#1: Table1: Cutting datasheet, here we enter data manually for part no. & it's cutting program no.(nest no.) & qty. of that part cut in that nest.
Why not same as 'needed'? Means, it could be 'what ever' even smaller than 'needed'?
Your used no. seems to be code.
 
Yes you're right, I should've used code or name in place of no.
As I told you that I've prepared datasheet using only one part name( i did this to make it simpler to understand).
In reality, there are multiple parts within the same nest. How much each part is accommodated in each sheet depends on the space available in a sheet, so sometimes for the same part, we add say 5pcs or 10pcs or 1pc based on space available.
Also for some parts, we get repeat orders, so if spare space is available in a sheet we cut these parts in excess quantity & hence they move to the surplus table(Table4).
Therefore no. of a part in a nest can be smaller, equal or greater than needed.
 
Gaurav Vohra
As I told you that I've prepared datasheet using only one part name( i did this to make it simpler to understand).
Do You mean that, You don't know how do that would work?

That functionality, layout, samples should be as possible near as needed for further use.
If ... those are now something ... then those would also be ... something ... later.
That means double or more times to change change something, because it cannot work!
Any, even minor change, would mess all done work!
 
Revised the sheet. Also, read the comments in row 5,6,7,8 again(some changes).
 

Attachments

  • part allocation query.xlsx
    36.8 KB · Views: 2
My mistake. See entry in C20 was 4, in C33 also it'll be 4. The same mistake is done by me in C44 & C59.
All these viz. C20,C33,C44,C59 will be no. 4.
 
Gaurav Vohra
Do You have image
... it's challenge to try to solve something, if sample files are not correct?
... it takes time to find out 'how to get those incorrect values?
Incorrect sample result will cause incorrect real result ... if could solve challenge someway.
> Now, You ... offer ... 4 to many places ... hmm? ... everything else keeps same?
Do You really have idea that above would fix everything?
> It's much harder to get correct results than incorrect! eg 1+2 = ?
( Quickly can find out one 'correct result' as well as many 'incorrect results'. )
 
Back
Top