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

FGSKU	FG Name	FG Category	FG Pick Location	Qty Required for current day
7470	Chunky Melon Trio - 250g	Fruit Prepared	F-10
7455	Chunky Pineapple - 220g	Fruit Prepared	F-10
7465	Chunky Rockmelon - 250g	Fruit Prepared	F-10
7460	Chunky Watermelon - 250g	Fruit Prepared	F-10
7450	Family Seasonal Fruit Platter - 1.3Kg	Fruit Prepared	F-10
7475	Fruit Salad - 250g	Fruit Prepared	F-10
7500	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>
</tbody>

CG table

CG Name	CGSKU	CG Unit	CG Unit value	CG Category	CG Pick Location
Avocado (Prep or unit type)	900001	g	1	Vegetable - Whole
Baby Rocket	900002	g	1	Vegetable - Leaf
Bacon (type)	900003	g	1	Meat
Basil	900004	g	1	Herb
Basil Pesto	900005	g	1	Condiment
Bay leaf	900006	g	1	Herb
Beef Lean (cut type)	900007	g	1	Meat
Beef Mince Lean (g)	900008	g	1	Meat
Beef Mince Low Fat	900009	g	1	Meat - Beef
Beetroot (Ready to eat)	900010	g	1	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>
</tbody>

FG to CG Mapping

FGSKU	FG Name	CGSKU	CG Name	CG Unit	CG Qty Required for FGSKU
81450	Simple Pumpkin Soup Kit - makes 1.5L	900053	Pumpkin Butternut	g	100
81450	Simple Pumpkin Soup Kit - makes 1.5L	900042	Onion Brown (Whole)	ea	2
81450	Simple Pumpkin Soup Kit - makes 1.5L	900052	Potato White	g	400
81450	Simple Pumpkin Soup Kit - makes 1.5L	900026	Garlic (Clove)	g	1
81450	Simple Pumpkin Soup Kit - makes 1.5L	900065	Stock Vegetable (Packet type)	ea	1
81450	Simple Pumpkin Soup Kit - makes 1.5L	900023	Cream (400ml carton)	ea	1
81450	Simple Pumpkin Soup Kit - makes 1.5L	900045	Parsley Italian	g	1
81485	Potato and Crispy Bacon Soup Kit - makes 1.5L	900065	Stock Vegetable (Packet type)	ea	1
81458	Sweet Potato & Basil Soup Kit - makes 1.5L	900065	Stock Vegetable (Packet type)	ea	1

<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>
</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.