2

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

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

CG to FG mapping table

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

Example report idea Example idea for the report (to help convey the idea of what i'm trying to achieve

  • Probably you need to use data model in pivot table (support.office.com/en-us/article/…) However not having your data in editable format I can't suggest more. – Máté Juhász 2 days ago
  • @MátéJuhász: What would be the best way to share an editable format that you could look at to potentially provide more insight - would a link to the document in a Onedrive instance suffice? – thewinchester 2 days ago
  • You can format your data as table here and include the output into your question – Máté Juhász 2 days ago
  • I've given that a crack @MátéJuhász, not sure why the markdown formatting looks awful however. – thewinchester 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.