How do retailers calculate inventory yield?

Factual Questions
1

Background:
I operate a small online retail operation.
I order product from my wholesale suppliers. Once it arrives, I list it for sale on a couple of websites.
As product sells out, I re-order from my suppliers.
Due to the nature of my product, items are frequently out of stock or understocked at my suppliers’ warehouses. In some cases, I can only place one order for an item with no possibility of refill orders.
I have a 7-day lead time on orders.
Every week I analyze sales, then project sales on a per-item basis.
If an item has less than a 21-day supply in my warehouse, I order enough units to get back to a 21-day supply.
Given the lag time between order and arrival, I wind up bouncing between a 7 and 14 day supply of product. Sometimes the market changes, and I wind up with turkeys or out of stock, but that’s the game.
All of this works for me.
What vexes me is the fact that I can’t figure out how to calculate the time-based yield of my inventory.
If I buy a share of stock at $10 and sell it a year later at $15, that’s a 50% ROI. All kinds of calculators on the web will let me do calculations with one purchase and then one sale a year later.
When it comes to the APR I’m getting from my inventory, I can’t find an appropriate calculation or calculator. Has no proprietor before me ever wanted to know if his stack of widgets is earning him a 50% ROI or a 500% ROI?

I’ve got an item that costs me $17 while I make a $8 profit per unit. I sell 3 units per day. I can calculate that my profit margin is 53%.
My money for the first 3 units is tied up for 8 days (7 days to get here, about 1 to sell). If I could avoid going out of stock, that means my $17 would turn over just under 45 days per year. In effect, my $17 turns into $360 in profit in addition to me getting my money back. I’m showing that as a 2117% annual ROI.

However, I’m not going to just order 1 unit. I’m going to order 63 units (a 21-day supply). I could plug in the sales and purchases into a calculator on a per-transaction basis, using a calculator like this:
http://www.fatpitchfinancials.com/wp-content/uploads/2006/09/Annualized%20rate%20of%20return.xls

Which I got from this page:

My problem is that over the course of the year, that would mean making entries for 1095 retail sales and 52 wholesale buys.

How in the heck would I calculate ROI on my inventory without doing all that work?

By way of background, what I find myself wanting to do is buy more pieces (days) of higher-yielding product while I buy less days of lower-yielding product.
Normally, carrying more than 7-14 days fill would be irrational, but in quite a few cases I will find myself unable to re-order a given item next week.

I have cobbled together a calculation that gives me a number that reflects yield vs turn time, but I know it’s not right.

It goes:
((PROFIT) / (COST)) / ((CURRENT STOCK LEVEL)/(SALES PER DAY)+7) * 365 * 100

The problem here is that it seems to neglect internal rate of return, as well as only giving the product credit for its profit at what amounts to the 21-day point.
What am I supposed to do in order to get this calculation?
Does no one else think the way I do on this?

2

There are bigger supply chain gurus here than I but I will give this part of the question a stab.

Math guys, If I misname or use a rough less accurate version of the proper math please be gentle, its been a few years.

The concepts you may be looking here for is safety stock and or safety time. Especially with a hot selling product the last thing you want is for a customer to have to seek another vendor. This creates a situation where it is not irrational to build in extra stock to cover orders where a vendor may be spotty in their ability to fill orders.

You also may want to do a little more number crunching on your averages. For example, do you average 3 a day over a 30 day period but do they tend to sell as singles? 5-10 at a time?

If we look at 7 days of sales and they sell 1,5,9,2,1,1,2.

You have an average of 3 per day, but a range of 8

Now we apply a standard deviation to get an idea of how much you need to have on hand to cover typical statistical deviations in your sales.

To cover about 70% of all possible known sales in a given day you need to have 5 on hand (the average plus the square root of the range)

bump to 7 and you cover about 95%

bump to 9 and you are at 99.7%

So you are probably going to want to make sure that any calculations you make leave you at 7-9 pcs of that sku on hand not 3 if replenishment is assured the next day. Remember 95% is a poss out of stock situation 1 day in 20, not so great.

I assume you are using excel or the like to do the heavy lifting, if so you may wish to adjust your purchasing calculations to reflect the minimum you want to have on hand for a day. If probably will only adjust your inventory by a few days stock but it will greatly reduce stockouts.

The same calculations will give you better ideas of the variation in slow sellers as well and may be able to help get a better picture of your flow of a slow moving SKU as well.

One other fun thing to play with, especially if warehouse space is impacted, is divide your operations costs into the volume of your warehouse. So to make it simple, you have 100,000 cubic feet of rack space and $100,000 a month in costs, it costs you $1 per month for each cubic foot of material you store. if you add that into your calculations you can see where a volume of slow selling product may be losing you money by not moving fast enough.

3

and you should probably look at a week as the sales increment, not a day, since your fullfillment time is 1 week. If you have 2 weeks on hand at 3 standard deviations, you should be golden since that gives you enough stock to have 2 hot weeks back to back, with no replensihment, and no stockouts.

4

Thanks for the input. I’d never thought of charting out standard deviations, but it does make sense.
Anyone else?
I know this math is Doper-Solvable. I’m surprised there isn’t a name for the equation I’m trying to do, complete with a wiki page somewhere.
Almost makes me wish I’d done business instead of sociology in college…

5

Okay.
If I only have one guy responding to me, I am going to assume that the dope probably doesn’t have interest in my question.

If I was going to pay for an answer, what kind of professional or academic should I consult?
Would a student going through a business degree program do this? Mathematician? Accounting student?

6

This is a task for an accountant. Anyone with an MBA should have some coursework on this–I certainly took a couple of courses involving these issues–but someone who concentrated on management or finance is not going to be fresh on the subject.

What you are doing is calculating the Cost of Goods Sold. The issue is, of course, the items in your inventory did not all cost the same. How to resolve that? There are a few different ways, which give different results (in the short term. Over the long term it would all equalize). You can use Last In First Out, First In First Out, or Average Cost for the cost of an item in inventory.

Also, I don’t think people typically think of this as Return On Investment so much as Profit Margin. Inventory is not really considered an Investment, as far as I know–it is an expense.

There is a lot of qualifications there, because I did concentrate on management, but that might point you in the right direction.

7

This with the caveat that is it a profit margin as **Nevada **said.

A metric better suited to compare with a stock return ROE would be Net Income / Average Inventory.

Inventory is a balance sheet item, a snapshot in time. You take the average of two time periods, divide it into the Net Income over you’re time period, and you have an approximate ROE, assuming you have no debt.

I would guess your basic accounting software is capable of generating a balance sheet based off of your journal entries, at which point this becomes a simple exercise.

If you don’t have accounting software, you’re probably losing out on a ton of basic and useful information by not having it and I would prioritize that above doing any ad hoc ROE analysis.

8

Might not be a matter of interest, sometimes folks don’t see the post.

Might be something to look for in a consultant. I did a couple of the tests toward CPIM but never finished due to a change in jobs.

9

I assumed that the 190+ views at the time I posted that meant there was interest.
I think the underlying problem is that my thinking runs contrary to accepted wisdom.
Most retailers carry product lines because they are trying to offer full storefronts… if you don’t carry bananas, people won’t come to your grocery store.
My business model is purely centered around quick turn and high yield. My customers are price and product-centric, not site centric.

10

Use the DuPont method:

this is exactly what it’s made for.

11

I like the DuPont method, but I don’t see how I can apply it on a per-item basis, or how it lets me account for re-investing the revenue from product sales back into product.
I’m trying to project forward, not backwards.

12

Well, if you are NOT making an entry in your books for every purchase and every sale, you are not keeping your books at all, which might be the start of your problem. Wal*Mart, whose business model, believe it or not, is to minimize inventory and maximize turnover, does enter all purchases and sales in their books.

As **FasterThanMeerkats **notes, once you have entered all sales and purchases into an accounting package you can find all sorts of different ways to slice and dice your numbers.

13

Within a wholesale environment, the preferred metric we used was GMROI.
Fairly standard KPI in retail too.

The key difference is that it uses average inventory for the period, rather than current or closing inventory.

14

The spreadsheet I’m working on isn’t for accounting existing sales.
It is for optimizing purchases made today and sold in the future.
It’s very good to know that I sold 200 pieces of “Dave’s Savage Hot Sauce” [1] last month that cost $10/unit and sold at $14/unit.
However, my desire is to be able to compare the predicted sales of “Dave’s Really, Really Mean Chipotle Butter” to the former product, and compare how good of a use of my funds buying a 21-day stock of either will be.
I can’t use my accounting package to run the ROE of a hypothetical product. Thus the existing [flawed] calculations of future sales and their yield.

For the record, I do account for sales in the past and maintain a ledger of inventory as it comes and goes. However, my prices and product demand are volatile, and I adopt new product all the time.

[1] Not a real product, I’m afraid.

15

I know next to nothing about the science of inventory management, but I have a feeling that you’re interested in numbers that people aren’t normally interested in. While it does make sense that you want to get some idea on what your yield is on the money you have tied up in inventory, someone analyzing a business cares much more about the yield on the total investment. That is, if you sometimes have the cash from a sale sitting around unused, that’s money isn’t working for you in the same way that inventory sitting on store shelves isn’t.

If you order new product once a week to get you to a 21-day projected supply, then you will probably always have capital equal to the cost of 21 days worth of stock tied up somehow. It’s possible that you put your income from sales into the overnight money-market and thus make a small return off your sales early in the week, but at the profit margins and turnover rates you’re citing that return probably isn’t relevant. So long as you order once a week to the same level, you will always have that level worth of capital invested in that product, regardless of how the sales of that product are spread throughout the week. Storage costs could complicate this slightly, but again, what does it matter the exact day an item is sold if you only make decisions on reordering once a week regardless? Thus the expected weekly yield on inventory is 100% * (expected profit per item * expected number sold per week)/(expected cost per item * expected number sold in 21 days). Since the 21-day supply is 3 times the number sold per week, this will simplify to (profit per item)/(3 * cost of item). This won’t be what your past return was, but what you expect to make in the future, which sounds like what you actually are interested in.

It sounds somewhat simplistic, but is based on ordering up to a 3 week supply once a week and not using your capital for anything else in the meantime; the only period that matters is the week. It’s possible I’m forgetting something important, but from what I see the only thing I’m not taking into account in intraweekly reinvestment which it sounds like is not going on. If you’re actually reinvesting the income throughout the week as sales happen then it complicates matters far beyond what anyone here is likely willing to do. Such a calculation would likely need to take into account the weekly cyclical purchase patterns to determine how much you would be reinvesting on average.

16

Thanks!
I’ll chew that number over.

17

OK, sorry for the misunderstanding earlier.

This is more a problem for the Finance department, and I am in the middle of that class right now. Essentially, I am being taught that the Finance department would go ahead and estimate sales of the new product, and set up the hypothetical end-of-year statements with and without the new product. With those statements it is pretty obvious which is the better use of funds. Remember to account for the people who would have bought the Savage Sauce if the Chipotle Butter had been available, but would buy the Butter if it is.

The magic of the process lies in producing those estimates of sales. That is the responsibility of the Marketing department, and those guys have no clue.

I have no idea how you would set up hypotheticals in your accounting package, whatever it is, of course, but it is not terribly difficult to do it in Excel.

18

Thanks for the response, Reno.

I’ve got a speadsheet I call ‘Sales Volume Analysis’.
It’s got thousands of different products from my supplier catalogs in it, and I want to have the ‘Return On Equity’ for each item displayed side by side.
Right now I’m using the formula that I stated above:
((PROFIT) / (COST)) / ((CURRENT STOCK LEVEL)/(SALES PER DAY)+7) * 365 * 100

I believe that is effectively my ROI per day, then annualized. The +7 accounts for the 7 days to get the item up for sale. It doesn’t account for the 3-5 day float to get the money to the bank from the customer’s credit card. It also, of course, doesn’t account for reinvestment.
I have this formula up for interest’s sake, since it doesn’t tell me what TO buy, it just (kind of) tells me what my current money is doing for me.
I can use it for modeling future yields by manually walking up the stock level further than I really have, but if I were to, for instance, plug in 180 days I would have the problem that the calculation doesn’t show the effects my being able to re-invest half of my working capital in 90 days.
Normally I wouldn’t tie up money for 180 days, but like I said, I get in on some last time buys and some closeout deals.
It seems that there has to be some way to assume I reinvest my funds in stock. If I was dealing in stocks with less frequent sales, the XIRR function seems to do it.
My other goal is to have a column that would tell me the largest quantity of stock to carry without having the result from the above formula dip below, for instance, 800%.

19

If by XIRR you mean MIRR, the Modified Internal Rate of Return, that calculation is based on assuming that you can re-invest the funds at your…Weighted Average Cost of Capital, I believe. If you do mean XIRR I may fail the test that I am taking this Thursday.

It seems to me that you are trying to do two different things simultaneously. The first is to calculate your inventory policy, which is based on the average number of sales per day, the lead time for delivery, and the risk of stock-outs that you are willing to accept. That will give you the optimal number and size of the orders that you will make per year. I learned the formula for doing this but I don’t remember it off-hand.

That will also give you the average inventory cost for the period–calculate the number of purchases times the cost of each and divide by the length of the period (365, if you want to do this on an annual basis).

Then you can calculate the return on working capital that you get with that inventory policy.

But really, you need to calculate the optimal inventory policy FIRST and see what the return would be–there is no point in having a desired return and then trying to tinker with your inventory policy to get that return.

20

I do know a lot about inventory management. The subject is too involved to put in a board post. What you should be concerned about is return on working capital. Money that’s tied up in inventory is reducing your working capital. Proper inventory management will increase your working capital and that’s how you grow your business. Read up on ROWC, in particular the impact of inventory management. This may force you to understand the different ways in which inventory accounting is done and how you can choose a way that would contribute to the growth of your business.