Calculating and completing a complex report in Excel

thewinchester

New Member
Joined
Jan 7, 2010
Messages
3
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

FGSKUFG NameFG CategoryFG Pick LocationQty Required for current day
7470Chunky Melon Trio - 250gFruit PreparedF-10
7455Chunky Pineapple - 220gFruit PreparedF-10
7465Chunky Rockmelon - 250gFruit PreparedF-10
7460Chunky Watermelon - 250gFruit PreparedF-10
7450Family Seasonal Fruit Platter - 1.3KgFruit PreparedF-10
7475Fruit Salad - 250gFruit PreparedF-10
7500Sliced Melons Trio - 1kgFruit PreparedF-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 NameCGSKUCG UnitCG Unit valueCG CategoryCG Pick Location
Avocado (Prep or unit type)900001g1Vegetable - Whole
Baby Rocket900002g1Vegetable - Leaf
Bacon (type)900003g1Meat
Basil900004g1Herb
Basil Pesto900005g1Condiment
Bay leaf900006g1Herb
Beef Lean (cut type)900007g1Meat
Beef Mince Lean (g)900008g1Meat
Beef Mince Low Fat900009g1Meat - Beef
Beetroot (Ready to eat)900010g1Vegetable - 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

FGSKUFG NameCGSKUCG NameCG UnitCG Qty Required for FGSKU
81450Simple Pumpkin Soup Kit - makes 1.5L900053Pumpkin Butternutg100
81450Simple Pumpkin Soup Kit - makes 1.5L900042Onion Brown (Whole)ea2
81450Simple Pumpkin Soup Kit - makes 1.5L900052Potato Whiteg400
81450Simple Pumpkin Soup Kit - makes 1.5L900026Garlic (Clove)g1
81450Simple Pumpkin Soup Kit - makes 1.5L900065Stock Vegetable (Packet type)ea1
81450Simple Pumpkin Soup Kit - makes 1.5L900023Cream (400ml carton)ea1
81450Simple Pumpkin Soup Kit - makes 1.5L900045Parsley Italiang1
81485Potato and Crispy Bacon Soup Kit - makes 1.5L900065Stock Vegetable (Packet type)ea1
81458Sweet Potato & Basil Soup Kit - makes 1.5L900065Stock Vegetable (Packet type)ea1

<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)

FWjlrLg
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,384
Messages
5,635,982
Members
416,891
Latest member
Okomomo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top