16 Nov 2023 | Windward Software
How to Balance Your Ledger & Stock Inventory
This Windward webinar covers balancing ledger and stock inventory within the context of Windward System Five. Specifically, it addresses the discrepancies that can arise between inventory value and ledger value. Additionally, the webinar sheds light on how overselling and manual stock adjustments impact these values.
- Scott Rockins from the Professional Services team led the webinar.
- The webinar is a part of a series, with future sessions discussing inventory counts and creating custom reports.
- The main focus was on balancing dark lines with the ledger and understanding the discrepancies between the two.
- Scott demonstrated how to use the Inventory Value report and how to find the asset for inventory on the balance sheet.
- The discrepancy between the inventory value and ledger value is normal and can be minimized through understanding and process management.
- Scott explained that the inventory value is determined by the quantity of stock on hand multiplied by the cost. The ledger value is affected by accounts payable bills or invoices from the vendor.
- Scott advised treating the accuracy of the inventory to the ledger as a key performance indicator (KPI) for the business.
- Scott demonstrated how to use the Bills and PO Reconciliation report to understand the discrepancies.
- Polls were conducted to understand how many attendees connect their vendor invoices to their purchase orders and how they perform manual stock adjustments.
- Scott noted that overselling and manual stock adjustments can cause discrepancies as they use the standard cost rather than the real cost from a purchase order.
- Scott advised that if attendees encounter a large discrepancy between their balance sheet and inventory value, they should seek help from their professional services team.
0:06
Everybody and welcome to our year end webinar series. This is the second one in the list that we have for you guys. This is how to balance your ledger and stock inventory and this is presented by Scott Rock ins from our Professional Services team. Go ahead Scott.
0:25
Thanks Gal.
0:26
And thanks for everyone for coming today.
0:29
Great to see some familiar names in the attendees list.
0:32
So we're going to talk today about how to balance your dark lines with your ledger and I'll go into more detail about exactly what I mean in a moment.
0:43
As a part of our year end webinar series, this is our second, and there'll be three more. We're going to take a break for Thanksgiving, and then we'll get into actually, how to do inventory counts.
0:53
Although the inventory count will come up a little bit today, How To Do Inventory Counts will be on November 30th, December seventh.
1:00
And at the last webinar of the series, Cracking the Code There: Step by Step Guide to Creating Custom Reports. We'll also touch on some types of reports that you can customize that are helpful today. There'll be more information on that, on December 14th.
1:16
Let's just dive right in.
1:17
So, this is a bit of a hot topic, and there are many different areas of the software that influence this topic.
1:26
So, I will go through it as quickly as I can, while still trying to cover everything.
1:31
But it's likely that we can't cover every single scenario that might happen.
1:37
But we can give you the overall idea of what you should be trying to to balance. What should be trying to achieve.
1:44
The objectives for today's conversation are first, to compare your inventory value report or your stock lines.
1:52
So, how many of something I have multiplied by the cost?
1:56
two, the ledger value for inventory, usually the 1200 ledger account.
2:01
And understand why these two values have a discrepancy.
2:06
They normally do, part of your your procedure, is understanding why they do minimizing the process like the discrepancy, And setting yourself a little bit of a goal is what we're going to encourage you to do. But, even though there's a discrepancy in most businesses, it's OK if you understand why.
2:26
And there's some good business reasons, why this occurs. And then you can look at the processes that are causing it in your business, and manage them the very best that you can.
2:41
The first thing that we're talking about here is the Inventory value. I'll show you where this report is specifically.
2:47
This report is the best inventory report to run at month end or year end.
2:53
At your end, typically you're doing this following your inventory count.
2:58
It's a report that all you can't really backdate effectively. So you gotta run it live. You need to run it at a time, you want to see it.
3:06
So right after your inventory count, right at your year end, those times, month end is a good time to grab a copy as well.
3:14
Specifically, the report I'm speaking about is here under inventory.
3:23
Inventory Reports, fourth, one down inventory value.
3:29
So if I'm looking at that in my system, although there's a few toggles.
3:35
I'll just point out, if you click this Show Detail one, it's going to go product by Product, and take a very long time to run.
3:42
Depending on how many products you have, So usually.
3:45
just hit view.
3:51
This can take a little time if you're trying to follow logs.
3:54
So you can see here's the stock quantity that I have in my system multiplied by the cost.
4:04
If I scroll down to the bottom, there's my total stock quantity.
4:12
So this is the key report that I would suggest you read at month end and at year end in the case of year end after your counting procedure.
4:26
The second thing is more, simply put is the asset.
4:30
For your inventory on your balance sheet.
4:32
Typically, it's the 1200 account. If you've stuck with the default ledger in system five, however, many people move that or rename that.
4:43
Specifically, to find that key accounting metric, you go to the general ledger, the ledger reports, and the balance sheets, good place to go.
5:02
And it's this account right here that I'm talking about.
5:07
And you can see.
5:11
My numbers don't match. And what we're going to explore today is why those numbers don't match.
5:17
And what things you can do to bring them closer or to just understand why they don't.
5:23
If you know why, they don't match, then you can do an adjustment to your ledger that's suitable, and you'll be able to explain to your accountant why and they can give you guidance on how to post your journal entry to make the two match at your end.
5:42
So, what we would encourage is to treat this as a KPI. And so, in case that's terminology or an acronym, you're not familiar with a key performance indicator for your business.
5:53
How accurate is my inventory to my ledger?
5:57
There, I think in most, all businesses there will be a discrepancy except for right after your end procedures.
6:04
But how close can you get it?
6:07
So if you take your inventory value, and you divide it by you're balancing your leger, how close can you make that percentage?
6:13
and year over year?
6:14
Or if you're counting more often, perhaps, month over month, keep an eye on that, and watch.
6:20
So that's one process you could put in place right away, is check it after the webinar.
6:26
Check it every so often, and watch for fluctuations, and then understand why Is there a fluctuation?
6:37
Now, this is the key place where misunderstanding happens. So when you're new to system five, the idea that these two things are separate values, probably seems a bit for it.
6:47
There's a good reason, several good reasons for why the system's designed that way.
6:52
But simply put, it's best if you understand which things affect, the first number, the inventory value report, and which things affect the ledger.
7:03
On the inventory side, this is the sum of the stock quantity you have on hand.
7:10
I see a question from Helen there as we go along, Helen.
7:13
You have a choice on whether or not to include your oversold.
7:17
On the Inventory Value report, it's a tick box.
7:21
The inventory value is the quantity you have on hand, multiplied by the cost.
7:29
This will increase whenever you receive a purchase order.
7:32
It will decrease whenever you sell something.
7:36
And if you do a stock adjustment, then that's not on an invoice.
7:42
I'll talk about that a little later.
7:44
Then that will decrease or increase it, depending on your selling, like you're adjusting up or down.
7:51
So those are all things that happen in, sales process happen, in receiving process, usually.
7:59
And in the ledger side.
8:05
Different things affect it slightly.
8:06
The key difference that people usually misunderstand is when an accounts payable bill or invoice is entered from the vendor, that's when the value of your ledger rises for the cost of the goods you purchased.
8:23
They also decrease when they're sold. So the selling is even across the board.
8:28
If you do any sort of manual journal entry, two, your inventory ledger, so you post freight to it for example, unless that's also put into the cost of goods, this would also create a discrepancy.
8:45
So there's a lot of reasons why these two things could become unbalanced.
8:54
If you're looking to figure out how big the imbalance truly is, here's the steps.
9:03
Run your Balance Sheet Report.
9:05
Find out your 1200 asset value.
9:09
Then you're gonna run a key report which I'm gonna show you in a moment here called the Bills to Peel reconciliation report.
9:16
This will show you the value of any POs you received into the system, which would have, if I just go back a slide, would have increased the inventory leger.
9:29
But if you have not put in the invoice, and there's sometimes a delay between when the vendor gets you an invoice.
9:38
If you're in that delay period where the bill hasn't been put in yet, there is a discrepancy there for a very good reason.
9:43
And this report, the Bills and Peel Reconciliation report, can cause that can basically outline the difference that you should add in there.
9:54
Then you add those together, and that report total, should match the Inventory Value Report that we looked at.
10:03
If it does not, you've discovered a gap in your process, or a gap in your posting, or something.
10:09
We're going to talk about where the gaps typically lie in a moment.
10:15
First, just so I'm giving you all the tools as we go along.
10:23
The report to see which POs you've received the invoice for or that you've put the invoice in for, can be found here under accounts Payable.
10:34
Accounts Payable Reports.
10:37
And it's the bills and Peale Reconciliation report.
10:43
Now this report is great, but it does require that you match up your purchase orders to your bills in your process, for putting in your invoices, and because often the person receiving the stock is back in the stock bay or receiving area.
11:01
And the person inputting the vendor invoices is at their desk.
11:06
There is a delay sometimes, between those two things happening.
11:10
And if they're not linked up properly, which I'll show you in a moment, then it can cause a discrepancy.
11:18
I'll just run this report. You can run it across all vendors, or just for one vendor.
11:26
And actually hold on here in the back step, and put the details in.
11:38
There we go, so now, on the left, there, you can see my POs from Alpine Distributors course. This is a very simple example, and on the right, you can see my bills.
11:47
So I have a number of bills that are creating a discrepancy.
11:54
Because the bill number, so for example, this bottom one here has a bill number.
12:00
There's a discrepancy, but it's about a large amount.
12:04
And these two do not have a bill number assigned.
12:06
So I haven't received the invoice yet, let's say.
12:10
You want to marry these up as tight as you can, but you're gonna take the total discrepancy. So in my case, it's quite a large discrepancy.
12:18
And compare that, as we outlined in the last slide, let's go back.
12:25
Sorry to hear.
12:27
You take the total, the value of any unbuild but received POs and add it to your balance sheet number.
12:37
Once you've done that, you should have a total to compare to your inventory value report.
12:47
There are a few other things here before we jump in there, I'm just going to backtrack for a second. Ask you guys a question.
12:54
So first of all, I'd like to know how many people connect there, vendor invoices to their purchase orders and just in case that's new to somebody, what I'm talking about is this.
13:10
When I go put in a bill, they'll, I'm going to add a bill.
13:17
I'll use Alpine, Distributors' again.
13:23
Do you go to this Purchase Orders tab right here and select which bill you're putting in the invoice form. But that's what I'm asking you.
13:30
Need to go add items and it brings them over onto your bill. So I'm curious to know.
13:38
How many of you properly do the procedure I just showed?
13:59
OK, just share that.
14:02
So as you can see, many of the people in the room do not, and you may have questions about how to do that although it's not too tricky. I went to POs.
14:11
I chose the POS, ticked off the Green Checkmarks and I press this button here.
14:17
Our regular manuals will show you how to do this process.
14:22
If you do this process, then the Bills to PO reconciliation report.
14:28
That I just showed you.
14:32
No.
14:36
So, again, that's under accounts payable, accounts payable reports.
14:42
This one here, that report's not going to work unless you connect them like that.
14:46
So then you don't have a key tool to doing this balancing, I'm talking to you about.
14:52
Whoops, sorry To hide the pole there.
15:01
So, again, what we want to make sure is that this report here under accounts Payable, AP Reports is a useful tool, the bills and Peale reconciliation.
15:15
It can help you find part of your discrepancy that is there for a very good reason. A lot of businesses I help, for example, the receiving happens during the day as the trucks show up.
15:24
And all those packing slips land on the invoicing or accounting desk, and get input the next day when the bookkeeper comes in. That's a short delay.
15:33
But if that built up, or they're away on vacation, the delay can be longer.
15:41
Now, that is a very good sort of difference, because it's a simple one to fix, And it's not too hard to find.
15:54
Now, there are some other reasons why you could have a discrepancy.
15:58
And this is where there's a lot of detail in each of these.
16:01
I'm just going to be able to touch on each one quickly, and then we'll jump into some Q and A afterwards.
16:06
So, for example, if I receive a PO and the costs, at the time I received the PO are the costs from last time I purchase from that vendor and the prices have risen.
16:18
And then I get the invoice and it's for a higher amount.
16:21
My ledger will rise more than my stock value when that happens.
16:25
So managing the stock, costs in your system, and keeping them accurate before you receive stock is the key process to tighten up there.
16:39
There's a lot of fluctuating costs right now.
16:42
Stock adjustments, so, manual stokke adjustments affect this, and they, that's because they use the standard cost for the item.
16:52
So, if you're manually adjusting stock and not doing any associated journal entries, your leger will remain the same while your stock level fluctuates causing a discrepancy.
17:03
I'll show you a quick report you can run to see which how much stock adjustments are happening in your system.
17:08
How you write off breakage theft loss, right off product or donate product.
17:14
Depending on how you process that, it can cause a discrepancy and I'll show you just a little bit on how to do that.
17:22
And, if you oversell, oversell the culprit because it's hard to sort out until you do account.
17:29
If you oversell, that's going to again use the expected or standard cost instead of a real cost from appeal.
17:36
So, it's going to cause a discrepancy. So, next question.
17:42
I'll run this one quick.
17:44
It's OK, if you oversell. There's good reasons to oversell, deal, oversell.
18:02
Interesting, OK.
18:09
So as you can see here, most people do. And that's because you need to do it to keep the sale's wheel turning.
18:14
There's good reasons why there's oversell features in our software.
18:17
But they do create a problem. I'll show why in a moment.
18:24
And I'm also curious about this. It might just guide my last few minutes presenting here. How do you perform manual stock adjustments? Do you go into the inventory item and edit it and go to the cost and stock levels and adjust it?
18:41
Do you use a transfer invoice, which is probably your best bet? Some people even load in new values over top.
18:52
Interesting.
18:58
You guys are kind of right down the middle on that.
19:03
So, I'll try and touch on the various approaches there.
19:09
OK?
19:14
As I mentioned, there's a lot a lot to talk about in this. So, I'm going to just go to our demo. And touch on a few things, When you oversell, when you do a stock adjustment?
19:26
When you, right off stock, the system, unless you tell it otherwise uses what we call the Standard Cost? So, I'm going to pull up an item here and edit an item.
19:37
We'll use something close.
19:45
So, anything we'll do, we'll use these pads.
20:00
So, this cost here, that's at standard cost.
20:03
I call it expected cost, when you oversell, when you do a stock adjustment, the system uses that cost because it's not sure which cost to use the thing about that cost.
20:14
Is it some guy or gal can just type in that box if you let them the cost is different.
20:20
You can data load into this cost.
20:24
This is the cost from last time.
20:26
So when you oversell, it uses that, is that correct? Is it incorrect? Is it the same amount on your invoice from a vendor?
20:32
It's that cost from when you initially started using our system, and we loaded it in.
20:37
Is it zero?
20:40
So, Overselling uses that cost, Stokke Adjustments done here under the cost and stock levels like this.
20:48
Use that cost and that cost is probably different than what you actually paid.
20:54
Also, stock adjustments like this one I'm doing right here, if you are commonly doing that, have no accounting effect whatsoever.
21:02
So now your leger has not changed, but your value on your value report as changed.
21:11
So you can see this is the cost I just typed in, and it brought in five at that cost.
21:19
If you need to do stock adjustments, the correct way to do it, or I'd say, a more thorough way though, a little bit more time consuming way. It sees a transfer invoice.
21:27
If this new, this is a new thing, I think for half of you, it might be, I'll show you quickly how to do it.
21:34
But there's a tiny bit of setup we won't get through today, so that's a really quick customer care call. How do I get transfer invoices?
21:41
There's the type of invoice. So I'm going to start a new sale.
21:45
I'm going to pick from my list and pick this transfer invoice, which moves product in and out of stock at cost.
21:51
transfers it.
21:54
And you need a customer that goes with this. Usually, you call it something like stock adjustments.
22:01
There's some key settings in the customer, and moving a little bit quick here, I understand.
22:10
Let's quickly edit this customer.
22:12
They need to be set up certain way in their financial tap, As a charge Account, Their taxes turned off, and their price schedule cost.
22:27
Then, on this transfer invoice, if I want to take product out of stock, I sell it.
22:33
If I want to put product into stock, I return it.
22:39
So, those are the key sort of steps to doing that correctly.
22:44
I know that's a bit rushed.
22:45
like I said, there's a lot of content in this particular topic having a good process for all of those things.
22:52
We'll help you to determine your gap and understand why it's happening in your business.
22:57
So, although there's some little tweaks in there, that it's really not a horrible idea to call customer care, to find out, Hey, where's that report?
23:05
How do I connect a invoice to my AP bill?
23:09
How do I get a transfer invoice?
23:11
That's all OK.
23:12
But if you find that you have a large discrepancy between your balance sheet and your inventory value and you're trying to sort that out by having our tech support team go through it with you, that is not really the right thing to use their services for their fixers they're going to go in there and fix things.
23:28
My team helps people with this all the time and that's probably the right place to go to for help.
23:35
Uh, so there are some free resources here.
23:41
How to design the inventory report so that you can determine where your oversold are are which we'll go over again in a later webinar whereas the manual adjustments report.
23:52
And we might just bring that up in the Q&A.
23:55
And how do I compare supplier appeals to bills to see where I've built things and where I haven't?
24:02
So I think we'll stop the presentation there.
24:06
And I hand over to you for a moment Kyle and then do a little bit of Q&A.
24:18
So, we have some free resources available here, we wouldn't do, you know, we want to know, give you the freebie stuff, and then also tell you about the paid services as well.
24:28
So, we have the Quick Reference Guide, and you can get to that group, Quick Reference guide to go into ... software dot com slash guide. And it's searchable right there. So, in this case, you would want to search for supplier POs versus bills, or Manual adjustments report, or Design Inventory Report.
24:49
There's also some Academy items here. So you would search by tag, that's usually the quickest way to find what you're looking for, is to search by tag.
24:57
And then the two tags that are relevant here would be Inventory Value and Your Engines.
25:06
Then, we have paid services.
25:07
Of course, Our professional services catalog is a great spot for you, too.
25:18
To brush up on skills, our professional services team can help you streamline your operations and help you refine your workflows, ensuring that your business operates smoothly and more profitably with system five.
25:30
Whether you're adding new features, training new people, restructuring and cross training for success, our professional services team is here to help you get the most out of Windward system five.
25:40
We also offer custom programming services to help you solve business problems within the software.
25:45
Our business analysts will seek to understand your business challenge, capture and assess your requirements, and then they'll work with the development team to propose a solution. And none of those are for you, where you don't have a very specific need, and you just want to cover a whole lot of stuff with our Professional Services team. We also sell our Our plans. So that would help you for if you have just a whole range of functionality, that you need to train on, or you just need help managing a project. You want to get something new done, right.
26:22
And if you're here for today's topic, of course, you probably would want want to consider the inventory reconciliation plan, and, uh, you would receive assistance and best practices for making proper stock adjustments, like we kind of started to get into today.
26:41
Matching vendor invoices to the PO values, and Techniques for reconciling.
26:50
Now, while we think we've offered some, some value here during this presentation, we thought we'd also sweeten the deal with a 10% off coupon. We're offering 10% off any of the Windward Professional services through the end of the year, the promo code.
27:05
You're dash and dash 23 for that.
27:09
You can go to ... software dot com slash professional services catalog to browse the catalog.
27:14
You can select a catalog item there to learn more, and if you're ready to buy, simply enter your e-mail address, and you'll be forwarded to the site where you can buy now.
27:22
Just don't forget to put it in the promo code at the checkout so that you get the 10% off.
27:28
Your account manager can also help you get the discount.
27:32
If you wanted to just do through traditional means, you can call them up, and they'll write up an invoice for you that way as well, both Options will get you that 10% discount. Just remember, the promo code is your dash dash 23.
27:47
It's gotten ready for QA. Yeah, I think I've got the questions here, Kyle, so here I can steal the screen back, if that's OK.
27:55
Yeah.
27:56
Let me just click that, but you need to stop first. Maybe you need to stop.
28:03
Here we go. It's all yours, OK. Hold on a second one.
28:15
OK, I'm probably just going to use the demonstration software here.
28:20
Um.
28:24
OK, first question that came up, I sort of touched on it as I went, so Helen asked, does the inventory report include oversold quantity?
28:36
So optionally it can.
28:39
If I go to inventory and purchases and to the Inventory report area and I go to the Inventory Value report, I think I have quite a few oversold.
28:52
I had mine ticked on to include oversold but that's an option.
28:59
Right there. So I'll just run that again. I should see some of them.
29:05
Believe I've got something here.
29:13
Looks like you're gonna have to oversell something real quick.
29:15
MSFT so I could a soar and I did, but maybe I also corrected it by adding stock.
29:23
So you can choose to include them or not I would say probably what you want to do in this process though is correct your oversold and then run the report if you're looking for a report to detect oversold.
29:36
Your best bet is the Design Inventory Report more on this topic in the last webinar of the series.
29:43
But I'll just quickly give you a preview when you design an inventory report, which is an incredibly customisable tool.
29:53
You can set it to list only oversoul, it's right there.
29:58
And then it will bring up the product that you're overselling. So, if you have to oversell it, keep the wheels turning in your business. Running this report frequently can help you stay on top of it.
30:08
That's one way you can tighten processes.
30:12
So, the next question I see here is, are you using available stock value or a financial stock value?
30:19
And if there might be some question as to what those two things are, bring up this Inventory Value Report one more time.
30:29
Now, I'm just using demonstration software, so the data's not nearly as thorough as yours will be.
30:35
I would use financial Stokke quantity here, but it really depends on how you operate. So, what's the difference between the financial and available? The difference is the product reserved for customers on work orders.
30:50
So if you are a business that has a lot of product staged for delivery or pickup, we're waiting for that next stage of construction to happen.
31:01
At the property, cena lighting showroom, for example. Then there'll be a discrepancy between the two.
31:07
But the product that you count would probably be everything that you still have, including what's on those work orders.
31:16
That's where you really have to think that through carefully depending on how you use the software.
31:25
Another good question, Is there a backdated inventory report we can use?
31:30
Yes, it has some limitations.
31:33
If I go to Inventory Reports, you'll see the Inventory Value Report, and right below it, the Backdated Inventory Value Report.
31:41
So this lets you choose a previous date and time when the inventory, to see what the inventory value was.
31:49
However, it does not take into account stokke adjustments.
31:54
So, it's never, if you do a lot of stock adjustments, it's just never gonna be the real deal.
31:59
The only way to get a true inventory out of this system is really at the time you want it.
32:04
That's why I suggest that this report, the inventory value, be something that you print as part of your month end procedures, certainly as part of your year end procedures.
32:15
But, this isn't completely wrong.
32:17
You just have to keep in mind, well, the stock adjustments aren't accounted for here.
32:24
And it doesn't let you choose a backdated date.
32:34
And I've got a question here from Don. So thank you Don so can you.
32:40
See that all, Can you do the connecting of the vendors to the POs anytime or only widths within that year or month?
32:48
You can do it anytime, done.
32:52
However, if you have your minimum book month locked down historically like the bookkeeper finishes the month locks down the bookmark, that would prevent you from editing it.
33:05
So, you would have to roll that back that minimum month.
33:13
Yes. There'll be a recording.
33:16
It's a good question along with anyway.
33:19
Sort of a comment.
33:20
So, katja saying, I'll find that I find that overselling is causing us issues because when we overselling item that's in the store but then when I update a purchase order it will ask if I want to clear the oversold, I say yes will it remove the stock that I just put in instead of clearing the oversell?
33:43
It's a sticky topic couch, it really depends on exactly all the settings in your software it's hard for me to answer.
33:48
So if you are set up a certain way when you receive product and it fills the oversold it will get rid of them.
33:55
But if you're set up the wrong way, it'll add stock to the total but still adjust it by the oversold amount.
34:02
That's it's a tough one for me to answer without looking at your software directly.
34:07
And I think that's probably all the time that we have today.
34:13
I can see that there's some more questions about how do I correct my oversold.
34:17
Watch for that in the inventory counting procedures coming up after Thanksgiving.
34:24
And I do see a question I shouldn't leave alone here.
34:28
Give me an example of non stock items and discuss how these may affect discrepancy. The non stock items.
34:35
Have a zero quantity.
34:36
They do not affect the inventory value report at all, but you've probably paid for them.
34:40
Which means your inventory, leger would be higher.
34:44
They said there's a lot of different rabbit holes in this one, so I'm sorry guys, That's all the time we have for today.
34:49
But if you're really struggling with this, of course, as Kyle mentioned, the professional services team is at your disposal, and take care. Take advantage of that promotion until the end of the year.
34:59
We'll look forward to seeing you in a couple of weeks after Thanksgiving, So enjoy your turkey, if you're having fun.
35:10
Thanks, everyone.
35:11
Thanks for coming, everybody.
Popular Webinars
Share this article on
