Excel reagent inventory spreadsheet for managing supplies.

Excel reagent inventory spreadsheet for managing supplies.

We have a chemistry instrument at a small lab that doesn’t run a lot of tests.  Therefore it’s important to manage, even micro-manage, keeping stock of reagents and supplies.  Unaccustomed to the lower rate of consumption, on a couple of occasions we’ve over-estimated the amount of reagent that needed to be ordered.  Now we have enough of some reagents to get us through 2019 if it didn’t expire first.  Yet for some other reagents we’ve run out and had to borrow.

So I set out to create a spreadsheet that would track reagent usage and give us an idea of how much to buy and when.  The project sort of mushroomed as I kept thinking of neat things to do with it.  I plan to explain the macro in more detail later but for now, here is how it works.

Daily to semi-daily:

  • Record the levels, expiration dates, and stability of each reagent on the instrument in the ‘On Machine’ sheet.
  • When done be sure to put in the current date for the data you just recorded at the bottom.
  • Run the ‘Copy to History’ macro.  If the sheet has not been created it is and column headers created in order.
  • Do the same for reagents sitting on the shelf.

This could be quite a task for a large lab but like I said this is micro managing.  This is all we need to get an idea of how much reagent is getting used over time.  To really snazz this up though we need some nice charts and a complex macro that churns through the data and gives us some actionable info.  For this we have the ‘CreateCharts’ macro.  Starting on the ‘Machine History’ sheet you run the CreateCharts macro and it does exactly that.  Do the same for the shelf inventory.  The CreateCharts macro does many things:

  • Creates a usage chart for each reagent.
  • Plots a trend line.
  • Calculates ‘burn rate’, the number of tests or reagent containers used each day.
  • Using average burn rate, tries to predict when the reagent will run out. (‘0 date’).
  • Also checks stability and expiration.
  • Alert the user about reagents that are about to be used up/expiring/stability ending soon.

Here is what the end result looks like:

One problem I’ve run into is that we use so few reagents off the shelf that it’s taking a long time to get good burn rate numbers for shelf inventory.  It shouldn’t be too hard to use the much more precise numbers from the machine history instead.  I’m not sure when I will get around to adding that though.

Here is the excel spreadsheet with macro.  Enjoy!


Leave a Reply

Your email address will not be published. Required fields are marked *