Excel dynamically calculate cost of products based on bill of materials (BOM) and purchase price history

Question

EDIT 1 → 2024-03-30:

• Included parameters in the lambda functions so the table names and columns may be injected by the user
• Updated lambda functions with such paramteres
• Even though I found a solution, I tested the whole programming and it's really resource/memmory intensive.

There must be a way to optimize/simplify it

---

---

Objective:

Calculate products' costs for a range of dates based on a Bill of Materials and purchase price history of each material (component)

---

Process:

Get products' components and quantities up to the purchase level and then multiply them by their purchase price in each month.

Source data

TableBOM has the mix of components needed in each product (Q = qty)

Componente 1	Q1	Componente 2	Q2	Resultado
Papel 1	1	Tarjeta personal	20	Tarjeta x20
Papel 2	2	Tarjetón	40	Tarjetón x40
Caja pe	4			Empaque pequeño
Cinta pe	5			Empaque pequeño
Separador pe	6			Empaque pequeño
Tarjeta x20	1	Empaque pequeño	1	Tarjeta empaque pe
Tarjetón x40	1	Empaque grande	1	Tarjetón empaque gr
Caja gr	7			Empaque grande
Cinta gr	8			Empaque grande
Nota	1	Empaque pequeño	1	Nota empaque pe
Tarjeta x20	1	Empaque grande	1	Tarjeta empaque gr
Tarjetón empaque gr	1	Nota empaque pe	2	Tarjetón + nota
Papel 3	3			Tarjetón
Divi gr	9			Empaque grande
Sobre 4	1	Nota sola	1	Nota

TablePurchaseComponent has the historical prices of each material (component)

Fecha	Componente	Precio unitario
1/01/2023	Caja gr	100
1/01/2023	Caja pe	110
1/01/2023	Cinta gr	120
1/01/2023	Cinta pe	130
1/01/2023	Divi gr	140
1/01/2023	Nota sola	150
1/01/2023	Papel 1	10
1/01/2023	Papel 2	20
1/01/2023	Papel 3	30
1/01/2023	Separador pe	190
1/01/2023	Sobre 4	200
1/01/2023	Tarjeta personal	2
1/01/2024	Caja gr	200
1/01/2024	Caja pe	220
1/01/2024	Cinta gr	240
1/01/2024	Cinta pe	260
1/01/2024	Divi gr	280
1/01/2024	Nota sola	300
1/01/2024	Papel 1	20
1/01/2024	Papel 2	40
1/01/2024	Papel 3	60
1/01/2024	Separador pe	380
1/01/2024	Sobre 4	400
1/01/2024	Tarjeta personal	4

Tables in the sheet:

---

Lambda functions (as defined names):

fxProcessVal:

Parameters → lookup_val;component1_cols;component2_cols;result_col

Code:

=LET(
    comp_res; VSTACK(
        FILTER(component1_cols; result_col = lookup_val);
        FILTER(component2_cols; result_col = lookup_val)
    );
    comp_res_fil; FILTER(comp_res; CHOOSECOLS(comp_res; 2) <> "");
    lookup_col; IFNA(EXPAND(lookup_val; ROWS(comp_res_fil)); lookup_val);
    IFERROR(HSTACK(lookup_col; comp_res_fil); "")
)

fxProcessCompRow:

Parameters → data;lookup_row;component1_cols;component2_cols;result_col

Code:

=LET(
    sourceData; fxProcessVal(
        INDEX(data; lookup_row; 2);
        component1_cols;
        component2_cols;
        result_col
    );
    res_val; INDEX(data; 1; 1);
    res_col; IFNA(EXPAND(res_val; ROWS(sourceData)); res_val);
    sourceRes; HSTACK(res_col; DROP(sourceData; 0; 1));
    IF(
        sourceData <> "";
        FILTER(sourceRes; CHOOSECOLS(sourceRes; 2) <> "");
        CHOOSEROWS(data; lookup_row)
    )
)

fxProcessComp:

Parameters → source;component1_cols;component2_cols;result_col

Code:

=LET(
    seq; SEQUENCE(ROWS(source));
    reducer; REDUCE(
        "";
        seq;
        LAMBDA(acc; curr;
            VSTACK(
                acc;
                IFNA(
                    fxProcessCompRow(source; curr; component1_cols; component2_cols; result_col);
                    HSTACK(""; ""; "")
                )
            )
        )
    );
    temp_res; DROP(reducer; 1);
    temp_res
)

fxCompCost:

Parameters → component1_cols;component2_cols;result_col

Code:

=IFNA(
    INDEX(
        unitprice_col;
        MATCH(
            MAXIFS(purchasedate_col; purchasedate_col; "<=" & date; purchasecomponent_col; comp) &
                comp;
            purchasedate_col & purchasecomponent_col;
            0
        )
    );
    0
)

prox:

Parameters → seed;component1_cols;component2_cols;result_col

Code:

=LET(
    res; IF(
        COUNTA(seed) = 1;
        fxProcessVal(seed; component1_cols; component2_cols; result_col);
        fxProcessComp(seed; component1_cols; component2_cols; result_col)
    );
    comp; CONCAT(seed) = CONCAT(res);
    IF(comp; seed; prox(res; component1_cols; component2_cols; result_col))
)

---

Reference:

Formulas

Formula to explode the BOM list (and desaggregate each product material and quantity needed):

=UNIQUE(DROP(REDUCE("";TableBOM[Resultado];LAMBDA(acc;curr;VSTACK(acc;prox(curr;TableBOM[[Componente 1]:[Q1]];TableBOM[[Componente 2]:[Q2]];TableBOM[Resultado]))));1))

The dates to calculate the costs of each product are dynamically set in a range using this formula:

=DATE(YEAR(Q1);SEQUENCE(1;DATEDIF(Q1;Q2;"M")+1;MONTH(Q1);1);1)

STUCKED HERE:

I would like to set Q3 (dates range as a spilled range) and calculate the cost for each product in each month.

Currently the SUMPRODUCT calculates the total for all the months (dates in the range).

I've tried BYCOL and a combination of MAP and Sequence but haven't figure it out.

Any help I would appreciate it.

Link to read-only sample file (no macros)

=LET(
     data;L4#;
     date;Q3#;
     res;INDEX(data;;1);
     comp;INDEX(data;;2);
     q;INDEX(data;;3);
     lab;UNIQUE(res);
     cost;q*fxCompCost(comp;date);
     rt;MAP(lab;LAMBDA(a; SUMPRODUCT((res=a)*cost)));
     temp;HSTACK(lab;rt);
temp
)

Desired result:

Answer 1

I used a combination of REDUCE and SEQUENCE to iterate through each date column.

=LET(
     data;L4#;
     dateRow;DATE(YEAR(ParamStartDate);SEQUENCE(1;DATEDIF(ParamStartDate;ParamEndDate;"M")+1;MONTH(ParamStartDate);1);1);
     headers;HSTACK("";dateRow);
     date;dateRow;
     res;INDEX(data;;1);
     comp;INDEX(data;;2);
     q;INDEX(data;;3);
     lab;UNIQUE(res);
     cost;q*fxCompCost(comp;date);
     reducer;REDUCE("";SEQUENCE(COLUMNS(date));LAMBDA(acc;curr;HSTACK(acc;MAP(lab;LAMBDA(a; SUMPRODUCT((res=a)*CHOOSECOLS(cost;curr)))))));
     result;VSTACK(headers;HSTACK(lab;DROP(reducer;;1)));
     result
)

Names added to the worksheet:

ParamStartDate: Q2 ParamEndDate: Q3

Final:

If someone comes up with a better solution even to process the BOM please post it.

Hope this helps somebody else.