Calculating and completing a complex report in Excel

[thewinchester]

Summary:
I'm trying to create a report/worksheet within Excel that shows me exactly how much of each component good I need to have/make on a specific day, based on the quantity of finished goods items that use it.

And I'm honestly stuck on the best way of how to achieve this.


Overview:
I have a table of products (called Finished Goods, or FG, primary key is FGSKU), each of which has a number of components (in a table called Component Goods, or CG, Primary key CGSKU).

I then have another Table, where I define which CG items that are part of each FG (called FG to CG Mapping), where a FG has a 1-M relationship with CG.

For example, my Simple Pumpkin Soup Kit - makes 1.5L (FGSKU 81450) might have 7x CG items mapped to it.

Data quality is enforced through the use of lists.

Back in the FG table (where the main work each day needs to happen), I enter in the number of units on order for that FG on a given day.

The intent being that I can produce a report which calculates for me and shows how many of each CG I need to make on each day.

So sticking with the example - if I had 3x Simple Pumpkin Soup Kit - makes 1.5L (FGSKU 81450) on order; then it would tell me the total quantity of each CG item associated with the FG item was needed. This would mean the report would show 300 g of Pumpkin Butternut; 6 ea of Onion Brown (Whole); 1200 g of Potato White; and so on.

And ideally to speed production, it would be good to sort the results based on the CG Category associated with each CG line.


Data examples:

FG table

Chunky Melon Trio - 250g	Fruit Prepared	F-10
Chunky Pineapple - 220g	Fruit Prepared	F-10
Chunky Rockmelon - 250g	Fruit Prepared	F-10
Chunky Watermelon - 250g	Fruit Prepared	F-10
Family Seasonal Fruit Platter - 1.3Kg	Fruit Prepared	F-10
Fruit Salad - 250g	Fruit Prepared	F-10
Sliced Melons Trio - 1kg	Fruit Prepared	F-10

<colgroup><col style="width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:15155;width:333pt" width="444"> <col style="mso-width-source:userset;mso-width-alt:4676;width:103pt" width="137"> <col style="mso-width-source:userset;mso-width-alt:3942;width:87pt" width="116"> <col style="mso-width-source:userset;mso-width-alt:4778;width:105pt" width="140"> </colgroup><tbody>
[TD="class: xl65, width: 68"]FGSKU[/TD]
[TD="class: xl65, width: 444"]FG Name[/TD]
[TD="class: xl65, width: 137"]FG Category[/TD]
[TD="class: xl65, width: 116"]FG Pick Location[/TD]
[TD="class: xl65, width: 140"]Qty Required for current day[/TD]

[TD="align: right"]7470[/TD]

[TD="align: right"]7455[/TD]

[TD="align: right"]7465[/TD]

[TD="align: right"]7460[/TD]

[TD="align: right"]7450[/TD]

[TD="align: right"]7475[/TD]

[TD="align: right"]7500[/TD]

</tbody>

CG table

Avocado (Prep or unit type)	g	Vegetable - Whole
Baby Rocket	g	Vegetable - Leaf
Bacon (type)	g	Meat
Basil	g	Herb
Basil Pesto	g	Condiment
Bay leaf	g	Herb
Beef Lean (cut type)	g	Meat
Beef Mince Lean (g)	g	Meat
Beef Mince Low Fat	g	Meat - Beef
Beetroot (Ready to eat)	g	Vegetable - Whole

<colgroup><col style="mso-width-source:userset;mso-width-alt:10222;width:225pt" width="300"> <col style="mso-width-source:userset;mso-width-alt:4539;width:100pt" width="133"> <col style="mso-width-source:userset;mso-width-alt:3976;width:87pt" width="117"> <col style="mso-width-source:userset;mso-width-alt:4147;width:91pt" width="122"> <col style="mso-width-source:userset;mso-width-alt:5870;width:129pt" width="172"> <col style="mso-width-source:userset;mso-width-alt:3976;width:87pt" width="117"> </colgroup><tbody>
[TD="width: 300"]CG Name[/TD]
[TD="width: 133"]CGSKU[/TD]
[TD="width: 117"]CG Unit[/TD]
[TD="width: 122"]CG Unit value[/TD]
[TD="width: 172"]CG Category[/TD]
[TD="width: 117"]CG Pick Location[/TD]

[TD="align: right"]900001[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900002[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900003[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900004[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900005[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900006[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900007[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900008[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900009[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]900010[/TD]

[TD="align: right"]1[/TD]

</tbody>

FG to CG Mapping

Pumpkin Butternut

Onion Brown (Whole)

Potato White

Garlic (Clove)

Stock Vegetable (Packet type)

Cream (400ml carton)

Parsley Italian

Stock Vegetable (Packet type)

Stock Vegetable (Packet type)

<colgroup><col style="width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:9608;width:211pt" width="282"> <col style="width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:6212;width:137pt" width="182"> <col style="width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:6161;width:135pt" width="181"> </colgroup><tbody>
[TD="width: 68"]FGSKU[/TD]
[TD="class: xl65, width: 282"]FG Name[/TD]
[TD="class: xl65, width: 68"]CGSKU[/TD]
[TD="width: 182"]CG Name[/TD]
[TD="class: xl65, width: 68"]CG Unit[/TD]
[TD="width: 181"]CG Qty Required for FGSKU[/TD]

[TD="align: right"]81450[/TD]
[TD="class: xl65"]Simple Pumpkin Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900053[/TD]

[TD="class: xl65"]g[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]81450[/TD]
[TD="class: xl65"]Simple Pumpkin Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900042[/TD]

[TD="class: xl65"]ea[/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]81450[/TD]
[TD="class: xl65"]Simple Pumpkin Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900052[/TD]

[TD="class: xl65"]g[/TD]
[TD="align: right"]400[/TD]

[TD="align: right"]81450[/TD]
[TD="class: xl65"]Simple Pumpkin Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900026[/TD]

[TD="class: xl65"]g[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]81450[/TD]
[TD="class: xl65"]Simple Pumpkin Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900065[/TD]

[TD="class: xl65"]ea[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]81450[/TD]
[TD="class: xl65"]Simple Pumpkin Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900023[/TD]

[TD="class: xl65"]ea[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]81450[/TD]
[TD="class: xl65"]Simple Pumpkin Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900045[/TD]

[TD="class: xl65"]g[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]81485[/TD]
[TD="class: xl65"]Potato and Crispy Bacon Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900065[/TD]

[TD="class: xl65"]ea[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]81458[/TD]
[TD="class: xl65"]Sweet Potato & Basil Soup Kit - makes 1.5L[/TD]
[TD="class: xl65, align: right"]900065[/TD]

[TD="class: xl65"]ea[/TD]
[TD="align: right"]1[/TD]

</tbody>

Example idea for the report (to show the what i'm aiming towards)

 

[Fazza]

At first glance this seems like well structured data & a great task for MS Access. And if staying in Excel, a pivot table might be a good solution.