SOLVED

Using FIFO to Determine Sale Price of Fuel

Occasional Contributor

In our business, we purchase fuel in bulk and it is then "sold" to our equipment at cost. I need to be able to attribute a price to each sale. 

 

I keep feeling like I've almost got it and then it breaks or just doesn't add up. I'd say I'm fairly competent at excel, but I've never used VBA or Macros. I'm not unwilling to learn if it solves this project for me. 

 

A few notes: 

  • I need to be able to import the data to a different system, required fields are Sale Date, Equip#, Sale Price and Quantity
  • Price needs to be determined using a first in, first out system. So The fuel purchased first is the first fuel to be sold. 
  • I need to export this periodically, as new purchases and sales are recorded so ideally I can keep adding info to these tables and it never breaks

I have two tables of data, for Purchases and Sales.

 

PURCHASES

DatePurchase QuantityPurchase Price
2/25/22226891.4023
2/25/22272781.3913
3/18/22483191.4446
3/30/22432231.6663
4/14/22458981.4318
4/29/22380141.6534
5/08/22390821.5981
5/20/22420521.4674

 

SALES

DateTimeQuantity SoldEquip#
3/08/224:26:00 PM293263
3/09/2211:16:00 AM79110
3/09/2211:22:00 AM110127
3/09/222:41:00 PM613276
3/09/224:03:00 PM348279
3/09/224:52:00 PM68110
3/09/225:11:00 PM571260
3/09/228:04:00 PM572278
3/10/224:55:00 AM405429
3/10/2211:42:00 AM598263
3/10/2211:54:00 AM71276
3/10/2212:05:00 PM138276
3/10/2212:44:00 PM328276
3/10/2212:55:00 PM428262
3/10/2212:59:00 PM155453
3/10/221:02:00 PM21606
3/10/222:34:00 PM272607
3/10/224:09:00 PM319254
3/10/227:37:00 PM219261
3/10/227:44:00 PM217261
3/10/229:44:00 PM72110
3/10/2210:29:00 PM647276
3/10/2210:45:00 PM534260
3/11/224:39:00 AM547279
3/11/222:09:00 PM241609
3/11/223:59:00 PM82134
3/11/224:05:00 PM284260
3/11/224:06:00 PM313608
3/11/224:37:00 PM413271
3/11/224:51:00 PM265276
3/11/227:25:00 PM321607
3/11/227:25:00 PM138609
3/11/227:50:00 PM386429
3/11/2210:26:00 PM557261
3/12/227:41:00 AM252279
3/12/2211:59:00 AM73127
3/12/224:12:00 PM69110
3/12/224:24:00 PM169608
3/12/225:36:00 PM594275
3/12/227:27:00 PM254607
3/13/223:54:00 AM395263
3/13/227:06:00 AM177276
3/13/227:40:00 AM537279
3/13/2210:12:00 AM52134
3/13/2211:15:00 AM665278
3/13/222:33:00 PM389607
3/13/223:35:00 PM64121
3/13/223:59:00 PM318264
3/13/224:01:00 PM443276
3/13/224:55:00 PM116110
3/13/226:36:00 PM262610
3/14/225:20:00 AM500612
3/14/225:47:00 AM243258
3/14/225:50:00 AM378279
3/14/225:51:00 AM172258
3/14/229:31:00 AM530608
3/14/2210:44:00 AM266601
3/14/2211:52:00 AM76134
3/14/221:10:00 PM111607
3/14/221:41:00 PM309276
3/14/221:51:00 PM462275
3/14/224:41:00 PM113124
3/14/224:44:00 PM51125
3/14/226:38:00 PM232273
3/14/228:08:00 PM174127
3/14/229:25:00 PM130134
3/15/225:29:00 AM156612
3/15/227:24:00 AM440261
3/15/227:47:00 AM135279
3/15/227:55:00 AM192279
3/15/2210:03:00 AM210275
3/15/2211:18:00 AM597258
3/15/2212:10:00 PM108110
3/15/221:16:00 PM470261
3/15/227:39:00 PM156124
3/16/224:47:00 AM195612
3/16/226:16:00 AM149279
3/16/228:35:00 AM35127
3/16/2212:59:00 PM520607
3/16/221:07:00 PM608275
3/16/223:47:00 PM546280
3/16/223:49:00 PM258117
3/16/223:53:00 PM217280
3/16/225:00:00 PM180273
3/16/225:16:00 PM487264
3/16/226:10:00 PM327279
3/16/226:10:00 PM71110
3/16/227:08:00 PM286610
3/16/227:47:00 PM194127
3/16/2210:17:00 PM503258
3/17/222:53:00 AM344276
3/17/223:05:00 AM360612
3/17/226:37:00 AM139125
3/17/228:43:00 AM392608
3/17/228:59:00 AM312609
3/17/2211:26:00 AM516273
3/17/2212:48:00 PM76110
3/17/222:02:00 PM101127
3/17/222:15:00 PM272610
3/17/225:14:00 PM55117
3/17/229:36:00 PM474264
3/18/224:41:00 AM391275
3/18/224:45:00 AM449279
3/18/226:34:00 AM273263
3/18/226:39:00 AM291263
3/18/228:48:00 AM450273
3/18/2210:33:00 AM114127
3/18/2210:40:00 AM175120
3/18/2210:52:00 AM236610
3/18/222:11:00 PM270278
3/18/222:13:00 PM334607
3/18/222:15:00 PM145278
3/18/225:22:00 PM87152
3/18/227:26:00 PM51110
3/18/228:13:00 PM619262
3/18/229:45:00 PM490608
3/18/2210:57:00 PM177120
3/19/221:42:00 AM285264
3/19/225:15:00 AM402275
3/19/2210:31:00 AM203258
3/19/2210:37:00 AM270258
3/19/2211:39:00 AM720260
3/19/221:35:00 PM183607
3/19/222:06:00 PM82127
3/19/223:06:00 PM368117
3/19/2210:01:00 PM727280
3/19/2211:55:00 PM337264
3/20/2210:41:00 AM302276
3/20/2212:02:00 PM303262
3/20/2212:05:00 PM371255
3/20/2212:10:00 PM188262
3/20/221:03:00 PM645274
3/20/221:14:00 PM429279
3/20/221:15:00 PM362275
3/20/221:41:00 PM247607
3/20/222:50:00 PM72110
3/20/224:29:00 PM469271
3/20/225:49:00 PM520273
3/20/227:22:00 PM304264
3/21/222:14:00 AM251280
3/21/223:24:00 AM30110
3/21/223:39:00 AM367254
3/21/224:59:00 AM405604
3/21/225:18:00 AM43116
3/21/225:25:00 AM395610
3/21/225:32:00 AM417612
3/21/2210:07:00 AM107127
3/21/2210:32:00 AM107124
3/21/2212:16:00 PM367273
3/21/221:27:00 PM465607
3/21/224:28:00 PM277125
3/21/226:25:00 PM709276
3/21/226:35:00 PM457609
3/21/227:49:00 PM686278
3/22/221:04:00 AM231604
3/22/224:53:00 AM536275
3/22/225:43:00 AM130110
3/22/2210:24:00 AM311273
3/22/2210:57:00 AM51116
3/22/222:51:00 PM102138
3/22/223:12:00 PM676258
3/22/224:49:00 PM326426
3/22/224:53:00 PM87426
3/22/226:25:00 PM258280
3/22/227:37:00 PM327262
3/22/2210:08:00 PM250609
3/23/224:48:00 AM338278
3/23/225:41:00 AM421604
3/23/227:14:00 AM148124
3/23/221:45:00 PM76606
3/23/221:56:00 PM235268
3/23/222:18:00 PM55115
3/23/223:14:00 PM395608
3/23/226:32:00 PM499280
3/23/226:37:00 PM509276
3/23/228:05:00 PM242273
3/23/2210:35:00 PM157134
3/24/222:09:00 AM292609
3/24/222:37:00 AM677263
3/24/222:39:00 AM216264
3/24/223:13:00 AM312258
3/24/225:17:00 AM63116
3/24/227:32:00 AM406610
3/24/2212:14:00 PM293273
3/24/222:06:00 PM96110
3/24/224:19:00 PM386609
3/24/227:16:00 PM421275
3/24/228:05:00 PM276607
3/24/228:15:00 PM703279
3/24/2211:29:00 PM429258
3/24/2211:45:00 PM328610
3/25/225:58:00 AM213278
3/25/227:05:00 AM260263
3/25/228:22:00 AM164127
3/25/229:55:00 AM403608
3/25/2210:33:00 AM194610
3/25/2210:38:00 AM524262
3/25/2211:32:00 AM158607
3/25/2212:44:00 PM81110
3/25/221:45:00 PM432260
3/25/222:09:00 PM331278
3/25/225:25:00 PM570279
3/25/227:12:00 PM115110
3/25/227:37:00 PM609264
3/25/2210:48:00 PM676280
3/26/221:53:00 AM230275
3/26/226:02:00 AM344608
3/26/228:54:00 AM30110
3/26/228:59:00 AM414273
3/26/221:27:00 PM210453
3/26/221:27:00 PM101127
3/26/221:30:00 PM136453
3/26/221:57:00 PM138125
3/26/223:16:00 PM343607
3/26/224:25:00 PM327279
3/26/226:25:00 PM616276
3/26/226:56:00 PM439280
3/26/228:02:00 PM721258
3/26/228:15:00 PM360610
3/27/222:20:00 AM256453
3/27/226:09:00 AM386275
3/27/226:41:00 AM370264
3/27/227:28:00 AM77110
3/27/227:47:00 AM118261
3/27/227:55:00 AM149261
3/27/228:58:00 AM146610
3/27/229:08:00 AM62116
3/27/2212:27:00 PM131127
3/27/2212:54:00 PM319263
3/27/221:51:00 PM215608
3/27/223:33:00 PM342453
3/27/223:44:00 PM61116
3/27/224:45:00 PM434276
3/27/225:25:00 PM642260
3/27/226:07:00 PM260275
3/27/226:21:00 PM661280
3/27/227:17:00 PM433255
3/27/227:19:00 PM477273
3/27/227:21:00 PM76110
3/28/228:54:00 AM328273
3/28/229:43:00 AM413607
3/28/229:45:00 AM209120
3/28/2212:38:00 PM68116
3/28/222:27:00 PM561261
3/28/225:03:00 PM147127
3/28/228:24:00 PM519610
3/28/228:56:00 PM532276
3/29/224:32:00 AM529280
3/29/225:04:00 AM238604
3/29/229:06:00 AM75116
3/29/229:10:00 AM334279
3/29/2210:04:00 AM153300
3/29/2210:42:00 AM443271
3/29/2212:22:00 PM65110
3/29/2212:41:00 PM268610
3/29/221:38:00 PM238607
3/29/221:44:00 PM457609
3/29/222:50:00 PM650260
3/29/225:29:00 PM137127
3/30/223:02:00 AM111120
3/30/225:33:00 AM342604
3/30/228:55:00 AM124134
3/30/2212:30:00 PM135607
3/30/223:34:00 PM99104
3/30/226:31:00 PM553279
3/30/227:42:00 PM317276
3/30/2210:31:00 PM481261
3/31/222:44:00 AM451275
3/31/223:42:00 AM340264
3/31/223:47:00 AM511273
3/31/223:54:00 AM96110
3/31/225:00:00 AM250268
3/31/225:03:00 AM626280
3/31/225:08:00 AM203604
3/31/225:32:00 AM87127
3/31/226:17:00 AM94134
3/31/227:54:00 AM304610
3/31/2210:29:00 AM325607
3/31/2212:01:00 PM546453
3/31/2212:10:00 PM35134
3/31/221:47:00 PM387276
3/31/222:01:00 PM122127
3/31/222:44:00 PM172610
3/31/224:06:00 PM76104
3/31/225:37:00 PM352268
3/31/225:45:00 PM454278
3/31/226:05:00 PM61110
3/31/226:10:00 PM87134
3/31/226:37:00 PM308273
3/31/229:24:00 PM109279
4/01/2212:24:00 AM297264
4/01/223:58:00 AM423260
4/01/225:15:00 AM465261
4/01/225:15:00 AM212604
4/01/225:17:00 AM93604
4/01/227:48:00 AM80104
4/01/229:06:00 AM629275
4/01/229:50:00 AM117127
4/01/229:52:00 AM192610
4/01/2210:09:00 AM116116
4/01/2212:24:00 PM232453
4/01/2212:47:00 PM44110
4/01/221:15:00 PM435279
4/01/221:26:00 PM69134
4/01/221:34:00 PM5674
4/01/223:12:00 PM320607
4/01/224:33:00 PM241273
4/01/227:54:00 PM586278
4/02/226:05:00 AM128264
4/02/227:23:00 AM84110
4/02/227:51:00 AM349609
10 Replies

@mandyturcotte 

Hello! You could use a function without VBA. XLOOKUP allows you to search for the next largest match. For this, I need an auxiliary column in which the purchased quantities are listed cumulatively. Have a look at the solution in the attached sheet to see if it meets your requirements.

It comes close, but ideally I can be more exact with the pricing. With the attached sheet, I price only 22,564 litres at $1.4023 --That's a difference of 125 litres from what I actually purchased at that price. If I'm understanding how this is working, I could be looking at up to a 850 litre (the equipment's max capacity) difference each time the price changes.

@mandyturcotte 

What should the price be when the price changes. A mixed price?

 

3/16/202212:59:00 PM5206071.4023
3/16/20221:07:00 PM6082751.3936
3/16/20223:47:00 PM5462801.3913
I think a mixed price is probably the best/easiest way to accomplish it.
best response confirmed by mandyturcotte (Occasional Contributor)
Solution

@mandyturcotte 

Now the formula with a mixed price.

@mandyturcotte 

This is a technically far more complicated solution using advanced features of Excel 365.

salesPrice
= LET(
    cumPurchases, SCAN(0,purchaseQuantity, Sumλ),
    cumSales, SCAN(0, quantitySold, Sumλ),
    MAP(cumSales-quantitySold, cumSales,
        LAMBDA(s₀,s₁,
            SUM( 
                MAP(purchasePrice, cumPurchases-purchaseQuantity, cumPurchases,
                    LAMBDA(pr,v₀,v₁,
                        MAX(MIN(v₁,s₁)- MAX(v₀,s₀),0)* pr
                    )
                )
            ) / (s₁-s₀)
        )
    )
 );

The formula accumulates both sales and purchase volumes.  Then it calculates the overlaps and the price of each tranche.  The unit price is obtained by dividing by the sales quantity. 

 

As the sales quantities are small relative to the purchase quantities, very few sales actually require an interpolated price calculation.

I'm hitting a snag with this one, Excel isn't recognizing SCAN() or MAP() functions best as I can tell. And a little research isn't giving me any answers as to why. I'm running Microsoft 365 Apps for Business as a subscription product. Version 2202 (Build 14931.20724 Click-to-Run) Semi-Annual Enterprise Channel.

@mandyturcotte 

That is a surprise to me.  It would not have been any surprise had you not had access to Excel 365, most users do not realise just how profound the changes since 2018 have been.  My belief is that even with a semi-annual license you should have had access to the Lambda helper functions for a while now.  Do you control the update cycle, or do you need to go to corporate IT?

@Sergei Baklan Can you advise?

 

Just in case you can gain access to SCAN and MAP, I have tried to repackage the solution as a Lambda function to facilitate reuse, hopefully for you, but otherwise for anyone else with FIFO based challenges.

Sorksheet formula
= CostFIFOλ(quantitySold, purchaseQuantity, purchasePrice)

CostFIFOλ
= LAMBDA(outputQuantity,inputQuantity,inputCost,
    LET(
        Addλ, LAMBDA(x, y, x + y),
        cumInput,  SCAN(0, inputQuantity,  Addλ),
        cumOutput, SCAN(0, outputQuantity, Addλ),
        MAP(cumOutput-outputQuantity, cumOutput,
            LAMBDA(u₀,u₁,
                SUM( 
                    MAP(cumInput-inputQuantity, cumInput, inputCost, 
                        LAMBDA(v₀,v₁,w,
                            MAX(MIN(v₁,u₁) - MAX(v₀,u₀), 0) * w
                        )
                    )
                ) / (u₁-u₀)
            )
        )
    )
 );
'Worksheet formula' would have read better.
Thanks! I'll keep this bookmarked and come back to it when I can figure out access. I'll have to talk to our IT Team.