Video Transcript
Good morning everyone, my name is Steve Erickson. I am your host today, at today’s lunch and learn. We are going to focus on inventory Control Physical Inventory Processing, as it relates to Sage 300, what are your options, how can you do it.
How is Sage 300 Inventory Control set-up?
Inventory Control is used in distribution, manufacturing and a variety of industries. It is probably the most unwieldy area of your accounting system. It is a moving target. It is important that you keep track of that moving target, and you can do that through Physical Inventory Processing. So let’s take a look at – it’s one of the big questions we get.
How do we do Physical Inventory, what is available to us? We thought it was a good topic to have. When we come into Inventory Control, you have a whole section here called Physical Inventory, and in there you have a variety of icons, and we are going to look at each of these and explain what they do.
Sage 300: Generate Inventory Worksheet Setup
In the Generate Inventory worksheet we have the ability to restrict by location. Now these can be physical locations as we see here central warehouse – Portland, Newark, San Francisco. They can be virtual warehouses. They can be my Q & A area, my retail floor, my manufacturing floor. However you view locations, you can then pick and count those locations individually. You then have the ability to select by account set. And if you remember – account set is predominantly grouped inventory in like costing methods. So the idea here is that you can have many account sets and you may have had that discussion when you were setting up your inventory.
You may need to change it now that you know that it can affect physical inventory processing. But basically it is the thought that like inventory with like costing methods are probably grouped together, and therefore would likely be counted together. And so you can go ahead and select one, we’re going to do them all because we want all inventory in this location.
Sage 300: Sort in Physical Inventory
Next you can do a sort. So how do I want this to look? I can sort it by item number, category a segment of the item. I can also, if you look all the way down to the bottom. A warranty period, warranty item volume and sales are all optional fields. Optional fields can be attached to the item master record. And so you see that you could create a custom sort arrangement specifically for your physical inventory processing, counting and use that as well.
We are not going to, we are going to leave it sorted by item number, and then I can come through and I can select all items on the inventory list or only items for this location, or only items in use at this location. We are going to grab all items. I then have the option to default my quantity on hand. I am going to leave it defaulted but I’ll show you what effect that has. When I say generate its warning me that I have an existing worksheet in place and that I’m going to overwrite it.
Sage 300: Physical Inventory Worksheet
So we create the physical inventory worksheet. What does that do for us? Well, it creates a snapshot. We’ll look at it right here, in this icon called Physical Inventory Quantity. It creates a snaphsot of inventory as it is, at the moment the worksheet is built. So at the moment the worksheet is built, I have 130 units on hand. Because I checked that box on the front screen, I am assuming that my count is 130, and I will only change the count if it’s not, if it’s varianced.
What Are My Options?
Now my other option would have been to uncheck that and then this whole count column would be zero. It would be zero so that I am forced to count everything and put the number that I count in there. I can’t assume, I can’t skip, I can’t cheat, but it creates more work in counting as well. We then see our variancing which right now is zero, because we defaulted everything to the count so my variance is zero, stocking units, estimated costs, estimated unit costs, adjusted unit costs and then any variance expense, and then the status.
Sage 300: Physical Inventory: Items on Hold
Now some things will come up on hold, and they’ll come up on hold for a variety of reasons. This one in particular is coming up on hold because there is no cost associated with it. There are 71 pieces on the shelf but no cost, and so the system says, hey you might want to take a look at this before you let that one go through. So this is what we’ve generated, but what do we do with this? So we can come over and we can print an inventory worksheet, and the inventory worksheet comes through, and just gives us a printout available, and notice that I get my item number, I get my pick sequence, I get the quantity in here.
This quantity does not change relative to, to whether I check that box on the front of the creation screen. This comes through relative to the box I check when I print this screen, we’ll look at that in a minute. I can count by my multiple units of measure here, and I can come through.
Sage 300: Physical Inventory Options
So I can print this multiple times, I can send multiple people out, counting the same thing in different parts of the warehouse. And then I have to bring it back, and then I have to combine those counts together. So when those counts come back and I combine them together, then what do I do with them.
Well one option or the primary option is I can come to the inventory count screen. Now again notice, these are all defaulted to count, and that’s because we checked that box on the front. They’re all defaulted is really just reading the same data file, that this other screen was reading. But all it has here is the count, so I can come through and I have 131 of these, I’ve got 70 of these, I’ve got 130 of these, I’ve got 85, we’ll skip it. I’ve got 190 of these. I’ve got 180 of those. So we can come through and do our counts.
But these counts have to be the total count in the system. So if you’ve got 3 counters, you’ve got to bring those count sheets together, and you have to come back and combine those 3 count sheets, to make sure that the number you are putting in, is the total number, that is available or is counted for that item.
Sage 300: Physical Inventory – Close and Inventory Reconciliation
So when I close this, I can then go through and do an inventory reconciliation. So the inventory reconciliation we’re doing for location one, because that’s what we are working on.
We’re only going to show the items with variancing, I don’t need to show everything. And we’re going to go through here and run this report. And you’ll see that it comes up with the quantity on hand, what I counted and the variance that is through here. Now if these variances are reasonable, and acceptable, I can go through and hard post them. If they’re not acceptable, if they seem unusually high or low, I can go through and recount them, recheck them, go out and talk to the counters. Whatever process I need to do, to validate those numbers.
And then I can come back in to the count sheet, over here. Inventory counts, and I can update this, to yes we really do have 71 pieces on the shelf, and when I close that and re-run this report, I will see that I have cleared, that variance for the item and it has dropped off the list.
Sage 300: Physical Inventory Process
Okay, so this is the process. The process is to create the Sage 300 physical worksheet, print the count sheet, count the items, bring them back in, combine the counts, enter them in the count sheet, reconcile them here, and if I’m not happy go back to the count sheet, and then back to reconciliation, back to the count sheet, back to reconciliation until I am happy. Once I am happy with the count, then I can post it. So the post is kind of anti-climactic. I simply come in here and I say, I am posting for warehouse one.
I can set the date wherever I need it to be. I can set the reconciliation to be whatever I want it to be, and I can go ahead and post that. We have hard posted that change into the system, and when I look it up those change numbers are going to go through. But what did it really change?
So we had variances of one or two or four pieces, but what did it change. And this is an important conversation, an important thing to understand as well. That what really happened here, is we posted the net effect of what was the count, to what was in inventory. So when we generate the report, when we generate the file, I have 130 units on the shelf.
My counter comes through and counts 129 units on the shelf. Once the counter is done, I can let the warehouse go back to work, I can let receiving start to happen again, I can let shipping start to happen again, and we can deal with just the count sheets and the Sage 300 inventory worksheet file that I generated. And when we do that, we get a net adjustment of one or two in the case of the examples that we’re talking about here. When I go to post that it doesn’t matter what the inventory is for the item anymore.
Post the Net Adjustment
It might be higher, it might be lower, than what it was when I generated the Sage 300 physical inventory worksheet. What I’m going to post is the net adjustment. I’m going to post -1, or +2. I am not going to do an actual post. I am not going to take 130 and change it to 129, some systems do that, we do not. So we post the variance between the worksheet that was done and the count that happened. That allows us to release warehousing, shipping, receiving faster than if I have to hold and build out the whole file, verify it because I am doing an actual post, so it is a real advantage that I am posting the net difference between the two. Okay, with that said let’s look at some other options.
So that’s the base, we created the inventory worksheet, we looked at it here, we printed our account sheets, and then we entered our accounts, then we came through and reconciled our inventory, when we were happy we posted it. That was the process.
Sage 300: Options to Run the Process
There are options on how we run the process. First is, let’s go through and generate a new file here. And when we generate a new file, what we are able to do is come look at this physical file for location one, look at my on hand and on count sheet, and then I have export capability here. So we are going to go through, and notice it says, its a two step export.
Sage 300: Physical Inventory Export
We are going to go through and export it. I am going to call the first step one. And then I am going to go ahead and export it, and then I’m going to take the next one and call it two, and I’m getting overwrite warnings here. Okay, and we exported it. And now I can come in here, and I can edit it at a spreadsheet level. So this is what was exported, here are my counts. So we take this down to 130, down to 70, down to 129, leave 85, we’ll come up to 191, like that. Then I can go ahead and just say save that.
Conclusion: Sage 300: Physical Inventory
And I can turn around and import it. So I can say File – Import, its a two step import. I start with Step 1. And then I go to Step 2. And I come into the system and notice that the minute I do that, my counted quantity fields change, and I get my variancing here and I can scroll across, and I can look at the cost of variancing here. What is going to happen, I am going to go down by 20, down by 25, up by 10. And now I can run the same process, that I did before.
So now I can go to Inventory Reconciliation, and I can show only those with a variance, and I’ll get just those 4 items that we looked at. I can come back to this count sheet. I didn’t exclude this because I imported it because of reading the same file, so I can come back to this count sheet, and make changes if I need to make changes on a one off basis. So this gives me an option.
Now I can create an Excel spreadsheet, I can send the spreadsheet out. I can have people count into the spreadsheet, and I can bring multiple spreadsheets, and I can put them together at the spreadsheet level using the power of Excel. And then I can update the count field, in that file and import it in again.
Is it a process? Yeah. Is it easy? No. Is it something that if I am doing large quantities of items would probably be a better way to do it? Yes. The amount of work it takes to put together multiple count sheets by hand is enormous. So anything we can do to streamline that will help, and this is one of the options.
Other Options
Now, are there other options.? Yes there are. One of the big questions we get is, how do I barcode my inventory? There are a variety of ways to do that. First to understand is that barcoding is not native to the system, barcoding is not inherent, so we have to use outside applications to both print our barcodes and read and count our barcodes. One option as an example is a product called Accudart. Accudart can be installed into the system. It uses RF Technology to a handheld, and you go through and scan the items and put the count in, and it talks directly back to Physical Inventory Processing.
Sage 300 Physical Inventory: Important Questions to Ask
The first question we have to ask if we are going to bring barcoding into the mix is: how did I get barcoding there? Because in manufacturing, bulk items typically aren’t barcoded, finished good items are barcoded. Now I have to have a conversation and decide how are we going to barcode things? Are we going to barcode bins with item numbers on them? Are we going to barcode tracker id’s onto inbound product when it comes off the truck? And again, these are bulk items that aren’t barcoded. How are we going to handle that so we can count using barcodes?
So that’s a conversation we will need to have, we’re going to need to figure out, we’ll need to decide what works for you. There are a variety of ways to handle this Excel spreadsheet import, if you choose not to use this AccuDart product, we can help you with that. Anything from Access queries to combining them together, to an IMAN script to automate the process. There are a variety of things that we can do. So we encourage you to have a conversation with us, to determine what works best for you. The import capabilities really streamline the product, short of these more advanced functionalities. Once we get that in, we do the same process over again.
Sage 300 Physical Inventory: Final Reconciliation
So let’s do our final reconciliation for our items here. We are going to take a look at the changes that we’ve got in place. We can print this, send it to the Inventory Manager. We are happy with it, we are approved, so we are going to go ahead and post this.
So once we go through and post this, we get a number in here, and just go ahead and post this one down, now my reconciliations are in place. Don’t forget whether you are doing physical inventory. Whether you are doing receiving or anything else, nothing is really hard posted in the Sage 300 Inventory Control module until day-end processing is run.
You don’t have to do Day-End processing after a Physical inventory, but it does push it into Hard Post, it will pick it up in the next Day End and process it through. So that brings us to a close on the Physical Inventory. Sage 300 gives us a very powerful, very functional Physical Inventory.
If this is not enough for you, then we will look at the Import/ Export. We will look at the barcode reading capabilities. We look at barcode scanning software to create barcodes, so that we can apply them to our items, and that is a long discussion that we’ll need to put together.
More on the Sage 300 Physical Inventory Process: Contact Front Line Systems
We want you to get this process down, please contact Steve Erickson at Front Line Systems for more information on Sage 300 Physical Inventory Processing.
Back to Sage 300 Video: Sage 300 Inventory Control: Physical Inventory Processing
More Links:
Sage 300 | Barcoding Software | IMAN software
Are you interested in Sage 300 training for your business?
Contact Front Line Systems | Why Choose Front Line Systems? | Back to Home
