Reconciling Inventory

When you run a fabrication business, your inventory is going to drift out of sync from time to time. Slabs are going to break. You're going to forget to add a slab to a job. You're going to forget to complete an activity. Once in a while, you might even have to deal with slab theft.

Your inventory system is only useful if you have confidence in it - 90% right doesn't help much. But people make mistakes (and no system is perfect), so it's important to fix discrepancies in your inventory system on a regular basis. Getting your inventory system to match the actual inventory you have on hand is called  reconciliation.

The process of reconciling is fundamentally the same, whether you use a spreadsheet or  JobTracker Inventory Edition - and even if you supplement JobTracker Inventory Edition with a barcode scanning solution from one of our partners

Here's the overview:

  • Compare what you have in your system to what you have in real life
  • When something is missing in real life or in your system, figure out why and update the system accordingly (and improve your process if possible to prevent the mistake from happening again)

If you're not using serial numbers to uniquely identify each slab, then the process is even simpler - just count the number of slabs you have on hand and update the count in your system. That approach works fine for less expensive items (some people do this for sinks, for example), but for something as expensive as slabs, you really should track each one individually with serial numbers. If you don't, it's very difficult to identify issues like slab theft.

Reconciling a Spreadsheet

Many of our customers track their inventory in a spreadsheet. In fact, this is what we recommend if you're not currently tracking inventory. We even offer a  free inventory toolkit for this purpose.

To reconcile your spreadsheet, first make sure it's as up-to-date as possible with newly received material. Also make sure you hide or delete rows that have already been consumed.

Next, make sure your spreadsheet is ordered in a way that makes it easy to find the slabs you're looking for (typically that means ordering by serial number), then print it out.

Move through your physical inventory one slab at a time. Start at one end of the yard and work your way to the other, going to each slab in succession.

For each slab that you come to in real life, find it on your printout and check it off. If you find a slab that's not on your printout, make a note of the details on the back of your printout. 

When you're done going through all the slabs, then it's time to investigate the differences (slabs that weren't checked off and slabs that weren't already on the spreadsheet).

Manually Reconciling in JobTracker Inventory Edition

The following example assumes you're using serial numbers for your slabs. If you're not, you probably should be. Call us to discuss.

Reconciling with JobTracker Inventory Edition is very similar to reconciling a spreadsheet. Ultimately, you print out an ordered list of serial numbers, check them off, make note of slabs not found in real life or in the system, and investigate the differences.

Step by Step

First, make sure that you've  completed all activities that are complete in real life, especially the ones with material. This is important, because if you have a serial number assigned to a job activity, but the activity is not complete, then it will still show up in inventory.

Next, look for unserialized product variants with allocated material or serial numbers with negative amounts. That's a sign of mismatches in your allocations.  Contact us for help fixing those - if you don't fix them, your inventory will never be correct.

Once you've taken care of these preliminary "clean up" tasks, create an inventory detail view with the columns that are most useful to see when you reconcile. Start with these:

  • Serial Number
  • Product Variant
  • Account/Job
  • Location
  • Consider adding Measurements, Is Remnant, Batch Number, and others that are interesting or useful to you.

You probably want to show only On Hand inventory:

You might add a filter to break down reconciliation into manageable chunks. Locations are really useful for this. In fact, you might consider organizing your Location names based on how much you can reconcile in one "session."

Let's say, for example, that you can reconcile 60-80 slabs in a 1-hour session (the more organized your inventory is in, the faster reconciliation will be). To facilitate reconciliation, you could name your locations in similar-sized chunks. So if you have 800 slabs in your inventory, you'd probably want 10-15 named locations.Then you can filter your inventory detail to show one location / one chunk of work at a time:

Sort the view by serial number. You're going to be looking for a specific serial number on your list, so they need to be in order.

Print out the view using your browser. This isn't a fancy printout, but it doesn't need to be. You're just going to be checking off rows on the printout. If you really want to make it prettier, then copy and paste into Excel and format as desired.

Now walk out to the slabs and begin comparing. Start from one end of the location you're reconciling. Look at the serial number on the first slab - most likely, it was scribbled on the side of the slab when it was received, but you might print labels instead. Either way, find the serial number on your printout and check it off. Go to the next slab. Check it off. Keep doing that until you get to the other end of the location.

If you find a slab that's not on your printout, write it down on on the back of the printout (include the serial number, product variant, account/job if any, and location). You're going to research that when you get back to your computer (or hand it off to someone else to research).

Optional: Some fabricators write the job name or number on a slab when a customer selects it - or put some sort of a sticker on the slab to represent that it's on hold (so employees and other customers can see it's no longer available). If your company does that, then you should also compare that information on your slab to the account/job on your printout while you're reconciling and note any mismatches.

When you're done going through all the slabs in a location, you'll have a printout with most or all of the slabs checked off. If every serial number is checked off and no new ones are noted, you're done - success! This is what you're always shooting for, and after your processes improve, this should be the norm when you reconcile. Until it becomes normal, it's a signal that you need to improve your human processes for handling inventory ... in a nutshell, make sure that every time a slab is ordered, received, moved, or consumed in real life, the same action gets recorded in JobTracker Inventory Edition as well.

If a serial number on your sheet isn't checked off - meaning it's in the system but not at that location in real life, then you have to investigate why:

  • Search for the serial number. Is it assigned to a job that's been finished in real life? Should it have been?
  • View current inventory for the product variant. Were neighboring slabs used on other jobs? Ask production if the missing slab was used for one of those jobs.
  • Look through the other physical locations for that particular slab. If you find it, update the location for the serial number and try to figure out how the mistake happened. Start by looking at the change log for the serial number to see if someone (maybe you!) just marked the location wrong when it was received. If it appears to have been moved, ask your team when it got moved and why - and coach them on the importance of updating JobTracker the next time a slab is moved.
  • Was the slab broken? If so, make new remnants for any remaining usable pieces and make an adjustment to get rid of the rest (the resulting balance should be 0 when you're done). NOTE: this is different from the steps for creating a remnant from a slab that's allocated to a job.
  • Was the slab stolen? This is the worst case, but it's part of the reason you reconcile ... reconciling can't eliminate theft completely, but it helps you identify it as quickly as possible - and knowing that all slabs are accounted for can itself be a deterrent. 

After you go through all the unchecked serial numbers on your printout, then you have to look at the ones that you noted on the back - meaning they're at the location in real life but not recorded in the system at that location. Again, you have to put on your investigator's hat and ask why:

  • Search for the serial number. If found, does it simply have the wrong location? If so, fix it (but also try to figure out how to avoid the mistake in the future)
  • Look at the current inventory for the product variant - was one slab just mis-numbered?
  • Ultimately, you're trying to figure out where the slab came from. If you found one that was never entered, then go to current inventory for the product variant, make a new serial number, and write the correct serial number on the slab (and then the hard part - try to figure out why it was never entered in the first place)

When you finish one location, then repeat the process for the next one. The first time you do a reconciliation, you should probably try to get them all done in a day or two. After that, you could decide to do an all-encompassing reconciliation once/month or so ... or instead, you might assign someone to reconcile just one location each day (a "rolling" reconciliation). Once you finally get everything up-to-date, it's easier to keep it up-to-date moving forward.

That's pretty much all there is to reconciliation: comparing the slabs in your system to your slabs in real life and figuring out the differences. It's intimidating to get started, but it's not so bad once you get going.

Adding a Barcode Scanner

If you have so many slabs that it's hard to do even a rolling reconciliation in a reasonable time frame, you might start looking for a way to speed things up. A barcode scanning solution can help, but it won't perform miracles. A barcode scanning solution uses the same basic approach as discussed here. It just makes the comparison a bit faster - and you still have to put the same energy into investigating discrepancies.

JobTracker Inventory Edition does not include barcode scanning. It lets you print labels with barcodes on them, but it doesn't have a built-in way to scan those barcodes. For that, you'll need to speak with one of our partners who offer such a solution. These solutions help you compare slabs in real life to slabs in JobTracker Inventory Edition. But instead of looking at a piece of paper and checking off a slab, you point a wand at the barcode label you previously printed out and stuck on the slab.

Some of these solutions do more than just reconcile. For example, MIRS from Data Bridge can also set the location of slabs when you scan them. We recommend that you talk with these vendors and their customers if you're interested in a barcode scanning solution.

However, if you haven't done so yet, we also recommend that you start with the manual reconciliation process described above - it might be enough! That's the same reason we recommend most of our customers start tracking inventory in a spreadsheet before upgrading to JobTracker Inventory Edition ... many times, the simple approach is enough. Once you reach a large enough number of slabs that it's difficult to reconcile your entire inventory by hand in a day or two, then you'll probably be well served with an barcode scanning solution from one of our partners.

Still need help? Contact Us Contact Us