Kitting Spreadsheet

[DocRogers]

I'm trying to create a spread sheet to show what parts go into each of the kits we assemble. I then want to create a pivot table to determine how many of each part are used in a month to figure out the correct purchasing amount for each part. I'm not sure how to lay it out in excel. An example of the information would be:
Kit# and then the listed components and how many are used in each kit, then how many kits are sold each month. Just not sure how to lay it out so it will work easily with creating a pivot table. Any help is greatly appreciated. Thank you

 

[awoohaw]

I'm a fan of as much normalization of your raw data as much as possible. So I suggest something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Kit ID	Component	Nbr Components			Sum of Nbr Components
2	RR	A2	1				A2	A3	A4	A5	A6	A7
3	RR	A3	1			FS			5	2	1
4	RR	A4	4			KK	5	5	3	1	2
5	RR	A5	4			QQ		2	1	2	5
6	RR	A7	2			RR	1	1	4	4		2
7	FS	A4	5
8	FS	A5	2
9	FS	A6	1
10	QQ	A3	2
11	QQ	A4	1
12	QQ	A5	2
13	QQ	A6	5
14	KK	A2	5
15	KK	A3	5
16	KK	A4	3
17	KK	A5	1
18	KK	A6	2
Sheet2

 

[Herakles]

Manufacturing Resource Planning (MRP) can get quite complicated but something quite useful can be set up very easily
and then enhanced later on.

Structuring the data well is crucial.

I assume that kits have a reference number and a name and likewise for components.
I refer to these references as numbers below but they could be a combination of alpha characters and numeric digits and even
characters such as hyphens.

Here is what I suggest to start with:

Worksheets:
Kits
Components
Assemblies (A list of components used in each kit.)

Kit Worksheet Columns:
Kit Number
Kit Name
Qty Sold Per Month

Component Worksheet Columns:
Component Number
Component Name
Qty needed per month (This is calculated using data in the Assemblies table)

Assemblies
Kit Number
Kit Name (Lookup on the Kit Worksheet)
Component Number
Component Name (Lookup on the Component Worksheet)
Number Of Components Required

Could it be the case that a 'Component' is made up of a number of other components.

Can you post some of your data using XL2BB.

 

[DocRogers]

I'm a fan of as much normalization of your raw data as much as possible. So I suggest something like this:

Book1
	A	B	C
1	Kit ID	Component	Nbr Components
2	RR	A2	1
3	RR	A3	1
4	RR	A4	4
5	RR	A5	4
6	RR	A7	2
7	FS	A4	5
8	FS	A5	2
9	FS	A6	1
10	QQ	A3	2
11	QQ	A4	1
12	QQ	A5	2
13	QQ	A6	5
14	KK	A2	5
15	KK	A3	5
16	KK	A4	3
17	KK	A5	1
18	KK	A6	2
Sheet2

Thank you, but my question then would be how do you associate the components that are in the kit. As you can see below in my example the first kit has 4 components of various amounts and total of 155 kits sold per month. The second kit has a couple of different parts and 1 common one with 145 sold per month. I need to organize the data in a way that i can then convert to a pivot table.

Kit	Component	Quant of part in kit	Monthly Kit usage
99001177	500002017	1	155
	500002016	2
	500002013	1
	5500011	1
9901176	515112128	2	145
	515103116	1
	500002017	1

 

[Herakles]

For the purposes of purchasing do you not need a list of the components that you need to purchase from
each supplier and the quantity required?

 

[awoohaw]

Thank you, but my question then would be how do you associate the components that are in the kit. As you can see below in my example the first kit has 4 components of various amounts and total of 155 kits sold per month. The second kit has a couple of different parts and 1 common one with 145 sold per month. I need to organize the data in a way that i can then convert to a pivot table.

Kit	Component	Quant of part in kit	Monthly Kit usage
99001177	500002017	1	155
	500002016	2
	500002013	1
	5500011	1
9901176	515112128	2	145
	515103116	1
	500002017	1

I'm not sure what the number of kits sold has to do with the "construction" of a kit, other than for your inventory needs.
But a multiplication factor sees to be an appropriate solution. As to how components are associated with a kit, I'm confused. I have them associated with the kit as column C matches to the kit name in column A.

I have shown how the data is pivot-able.

I'm not sure what you are asking for. Maybe you can mock up a desired pivot report that you need where the suggestion I made fails in your objective?

 

[awoohaw]

Thank you, but my question then would be how do you associate the components that are in the kit. As you can see below in my example the first kit has 4 components of various amounts and total of 155 kits sold per month. The second kit has a couple of different parts and 1 common one with 145 sold per month. I need to organize the data in a way that i can then convert to a pivot table.

Kit	Component	Quant of part in kit	Monthly Kit usage
99001177	500002017	1	155
	500002016	2
	500002013	1
	5500011	1
9901176	515112128	2	145
	515103116	1
	500002017	1

Your data is structured exactly as my suggestion except you do not carry the kit ID down. That is not a data structure you can build pivot tables upon.
Similarly, the quantity sold cannot be conflated with the construction of a kit. You seem to be mixing sales tracking and kit construction which are definitely related but should not be in the same flat table. But, if you did want to insist on sales being part of this data, then you should carry the Monthly kit usage down for each kit and compoenet as I did the KIT ID.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
22	Kit	Component	Quant of part in kit	Monthly Kit usage		Sum of Quant of part in kit
23	99001177	500002017	1	155			5500011	500002013	500002016	500002017	515103116	515112128
24	99001177	500002016	2			9901176	0	0	0	1	1	2
25	99001177	500002013	1			99001177	1	1	2	1	0	0
26	99001177	5500011	1
27	9901176	515112128	2	145
28	9901176	515103116	1
29	9901176	500002017	1
Sheet2

 

[DocRogers]

For the purposes of purchasing do you not need a list of the components that you need to purchase from
each supplier and the quantity required?

hello, that is what i'm trying to do with this list. The customer gave us a list of kits, the components that go in them, how many of each component and how many they will be buying each month. I'm trying to create a pivot table to see how many of each component we will need to purchase to cover all the kits. Some kits use the same components as others and some components are used in only 1 kit. Once I know the totals I can then work on getting quotes from the vendors along with min buy quantities and or price breaks.

 

[awoohaw]

@DocRogers , what version of excel are you using?

 

[awoohaw]

try this:

Mr excel questions 42.xlsm
	A	B	C	D
20	DATA!
21	Kit Components
22	Kit	Component	Quant of part in kit
23	99001177	500002017	1
24	99001177	500002016	2
25	99001177	500002013	1
26	99001177	5500011	1
27	9901176	515112128	2
28	9901176	515103116	1
29	9901176	500002017	1
30
31	DATA!
32	Order Volume
33	Kit	Orders
34	99001177	155
35	9901176	145
36
37	Report Data:
38	Kit	Component	Quant of part in kit	Components Needed
39	99001177	500002017	1	155
40	99001177	500002016	2	310
41	99001177	500002013	1	155
42	99001177	5500011	1	155
43	9901176	515112128	2	290
44	9901176	515103116	1	145
45	9901176	500002017	1	145
46
47	Pivot
48
49	Row Labels	Sum of Components Needed
50	5500011	155
51	500002013	155
52	500002016	310
53	500002017	300
54	515103116	145
55	515112128	290
DocRogers

Cell Formulas
Range	Formula
D39:D45	D39	=XLOOKUP(A39,$A$34:$A$35,$B$34:$B$35)*C39

This can be written more concisely with 365 or 2021.