by Dr Will @ Iconic

Calculating Inventory Turnover (with PostBooks Example)

Posted by Amos Hunt on Sep 19, 2013 3:20:44 PM

Mathematicians developed Little's Law (which we discussed in last week's post) to solve a limited problem of measuring customer flow. The idea was to prove that you could calculate the average time spent by a customer at a location by dividing an average head count by the average arrival rate. But it turns out that this handy formula can be used to calculate the flow rate of any kind of inventory at all.

From an accounting perspective, inventory is an abstraction, using a costing scheme to sum up the overall value of all the inventory on the books, so it can be a little tricky to measure. You can't literally watch a dollar turn into a few minutes of labor or a fraction of a purchased item and finally walk out the door with the satisfied customer. The average flow time of a dollar through inventory seems even more elusive than the flow time of a customer. But the other two parts of the formula are already in your books just waiting to be used: average inventory is just beginning and ending inventory for a period, divided by two; and the flow rate is your Cost of Goods Sold.

Here's an example of how you might create a financial report in PostBooks to provide all the information you need to calculate your inventory turns. (If you're using other business software or don't have an ERP system yet, you'll have to adapt these steps according to how your software works.)

First, you have to define the report. This is the tedious and frustrating part if you don't already know what you're doing. So, please, as the fellow says: hold onto your butts.

In xTuple Postbooks, navigate to Accounting → Financial Reports → List Financial Reports...

Why not "New Financial Report...", you ask? Making a new financial report from scratch sounds fun to you? You are clearly a strange and powerful being from another dimension. The rest of us are going to copy an existing report so that we don't have to enter the column layouts ourselves. For our purposes, we can select one of the system reports, "Basic Balance Sheet," and click "Copy." You can enter whatever you want for the "Target Report."

Next, open the new report for editing, and delete all the existing rows. You don't need them. The fastest way is to delete the top level groups ("ASSETS" and "LIABILITIES AND OWNERS EQUITY").

Click "Add Top Level Group" to replace the groups you deleted. Enter "INVENTORY" (or similar) for the name, and tick the checkbox for "Show Subtotal" to ensure that the report displays totals for all inventory accounts. None of the other options matter for this report. Add a second top level group with the name "COGS." Again, the exact name doesn't matter.

This report won't do anything yet, though, because the top level groups are just labels. You still need to add the accounts. Now, if you're the charming fellow who insisted on entering the column layouts from scratch, you might want to put these accounts in one by one. You won't be interested to know that you can enter all your inventory accounts at once using a Sub Type.

Select the "INVENTORY" group and click "New Account" to get started. After ticking "Select Multiple Accounts by Segment," you can open the Sub Type drop-down menu and select "IN-Inventory" and save. Select COGS and follow the same steps to add all the "Cost of Goods Sold" Sub Type accounts to the report.

Now you can run the report by navigating to Accounting → Financial Statements → View Financial Report... and selecting the report you just made.

For the column layout, choose "Current, Year Ago." (If you don't see this option, you didn't import the column layouts from an existing report. Tsk, tsk.) In the "Periods" table, select exactly one period from the list and click Query. Assuming your COGS accounts are zeroed out once a year, select a period at the end of your financial year.

Now all of the information you need is in the resulting table. You'll just need to pull it out and do some calculations. It's easy to copy and paste the table into a spreadsheet. Just right click, and in the context menu that appears, select Copy to Clipboard → All. Now paste into your favorite spreadsheet software.

The value in the first column for COGS subtotal gives you the cumulative COGS for the year. To get the average inventory, add together the starting and ending inventory (the Inventory subtotal values in the first and second columns), and divide by two. Below, you'll see a formula you can use to calculate the number of times your inventory turns over in a year.

To find out how many days it takes to complete an inventory turn, divide 365 by the inventory turns per year.

So that's how you do that!

Topics: flow unit, Inventory, Technical, operations managament, ERP, Process, postbooks

Iconic BMS is dedicated to helping you make your brewery Iconic ... by providing the techniques and technology to improve your operational excellence.

Your brewery is focused, as it should be, on your beer and your customers.  The "business of the brewery" is that set of activities common to all businesses that make your organization effective and efficient.  Improvements in the "business of the brewery" help you improve by freeing resources to focus on what is most important - the Customer ... and the beer!

This blog will cover more than just techniques and technology, though.  Included will be all manner of information that takes a brewery from fantastic to Iconic.

Subscribe to Email Updates

Recent Posts

Posts by Topic

see all