FIFO Lambda in Excel

Inventory library

/*  FUNCTION NAME:  Calculateλ
    DESCRIPTION:*//**Builds tables of output costs from input and output tables*/
/*  REVISIONS:      Date            Developer           Description
                    01 Mar 2024     Peter Bartholomew   Original Component
                    17 Jul 2024     Peter Bartholomew   Major revision to tracking entries in combined table
*/
Calculateλ
=LAMBDA(
//  Parameter Declarations
    [outFlow], [inFlow], [unitCost], [option],
//  Main Procedure
    LET(Help,   TRIM(TEXTSPLIT(
                    "DESCRIPTION:   →Builds tables of output costs from input and output tables.¶" &
                    "VERSION:       →17 Jul 2024¶" &
                    "PARAMETERS:    →¶" &
                    "  outFlow,       →(Required) A column array of output quantities¶" &
                    "  inFlow,        →(Required) A column array of  input quantities¶" &
                    "  unitCost,      →(Required) A column array of  input costs¶" &
                    "  option,        →(Optional) A switch that selects output options¶"  &
                    "    0:         →Normalised table showing tranche quantity and cost against input/output¶" &                    
                    "    1:         →List showing costs grouped by output¶" &
                    "    2:         →Crosstab showing tranche quantities against input/output¶" &
                    "    3:         →Crosstab showing tranche quantities and cost against input/output",
                    "→","¶" )
                ),
    //  Check inputs
        outFlow,  IF(OR(ISOMITTED(outFlow), outFlow=""),   #Value!, outFlow),
        inFlow,   IF(OR(ISOMITTED(inFlow),  inFlow=""),    #Value!, inFlow),
        unitCost, IF(OR(ISOMITTED(unitCost), unitCost=""), #Value!, unitCost),
    //  Procedure
        //Use cumulative flows to identify change of input/output events
        cumulativeOutflow, SCAN(0, outFlow, SUM),
        cumulativeInflow,  SCAN(0, inFlow, SUM),
        stackedflow, VSTACK(cumulativeInflow, cumulativeOutflow),
        eventLevels, SORT(stackedflow),
        priorLevels, VSTACK(0, DROP(eventLevels, -1)),
        tranchVolume, FILTER(eventLevels-priorLevels, eventLevels>priorLevels),
        //Use change event flag to accumulate input and output batch numbers
        isInflow, SORTBY(VSTACK(IF(inFlow, 1), IF(outFlow, 0)), stackedflow),
        isOutflow, 1 - isInflow,
        inBatchNum, FILTER(SCAN(1, isInflow, SUM) - isInflow, eventLevels>priorLevels),
        outBatchNum, FILTER(SCAN(1, isOutflow, SUM) - isOutflow, eventLevels>priorLevels),
        //Lookup unit cost by input batch number
        tranchCost, tranchVolume * INDEX(unitCost, inBatchNum),
        result, SWITCH(option, 
            //  Presents the results as a list, crosstab or detailed crosstab as required
            1, VSTACK({"Output idx", "Volume", "Cost"},   
                GROUPBY(outBatchNum, HSTACK(tranchVolume, tranchCost), SUM)),
            2, PIVOTBY(outBatchNum, inBatchNum, tranchCost, SUM,,0),
            3, PIVOTBY(outBatchNum, inBatchNum, HSTACK(tranchVolume, tranchCost), SUM,,0,,0),
            VSTACK({"Input idx","Output idx","Volume","Cost"},
                HSTACK(inBatchNum, outBatchNum, tranchVolume, tranchCost)
        )
    ),
    //  Handle Error
        Error,  MAX(ISERROR(Result)+1),
    //  Return Result
        CHOOSE(Error, Result, Help)
    )
);