Forum Discussion

mandyturcotte's avatar
mandyturcotte
Copper Contributor
Sep 20, 2022
Solved

Using FIFO to Determine Sale Price of Fuel

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
excel
Formulas and Functions
Macros and VBA
power bi
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Sep 20, 2022

    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.

    • mandyturcotte's avatar
      mandyturcotte
      Copper Contributor
      Sep 20, 2022
      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.
      • dscheikey's avatar
        dscheikey
        Bronze Contributor
        Sep 20, 2022

        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

Resources