Inventory Balance Set | Most Important Retail Equation

JaredDolich

Introduction
The inventory balance set is quite possibly the most important supply chain equation for anyone having the responsibility for inventory planning, replenishment, finance, store operations, and executive/strategic accountability. The balance set documents and measures the value of your inventory, or more significantly, your working capital. So, you can rest assured Finance will be taking a keen interest in the inventory plans. In addition, Investment firms rely on inventory analysis for publicly held companies because it predicts future sales and margin with sharp accuracy. In this article we’ll explore the inventory balance set and build the case for why Anaplan is perfectly designed to connect all the different planning activities together.
Basic Definition: Inventory Balance Set
Accounting measures the value of their assets at the end of a designated time, using a balance sheet with debits and credits, a snapshot in time. Finance measures cash flow using sources and uses by accumulating these values over time. Balancing the inventory is a combination of both, and it’s a surprisingly simple calculation.

Inventory Balance Set

Ending Inventory = Beginning Inventory + Additions – Reductions

In a dimensional platform like Anaplan, the most important concept is to remember that the equation MUST balance. This means: to go from a beginning onhand from one period to the ending onhand of another, there must be a set of line items that explain precisely how you got there. Inventory must be preserved.
Introducing the Concept of Inventory Valuation
We need to review the concept of an inventory valuation before we can combine dimensions with the balance set. An inventory valuation is defined by the analytical perspective needed. For instance, if I’m interested in a working capital analysis, I need the inventory to valued at my cost. Or, if I’m interested in calculating the gross margin potential in my inventory, I will use a retail valuation, or what the customer would likely pay to obtain the inventory.

Each inventory valuation has its own separate inventory balance set, there’s even one for units. The good news is that we can transform an inventory valuation to any other inventory valuation by using prices, ratios, and percentages. I call this transformation: valuation jumping.

Valuation jumping refers to the line items needed to move from one inventory valuation to another.

Below is a handy graphic you can use to visualize the big picture. I will explain all these metrics in detail later, but I introduce this graphic now so you can see where we’re heading.
For example, to go from a unit valuation to a cost valuation you would use the average unit cost, or AUC.

Inventory Valuation | Unit of Measure Examples
We can also use the term Units of Measure (UOM) as synonymous with inventory valuation. For instance, if I want to report on sales, am I referring to the units, the cost which I paid, or the retail the customer paid? Every business that heavily relies on a supply chain will have a slightly different valuation set based on the nuances of their business and how their local accounting standards affect the legal definitions.

In planning, particularly inventory planning, these equations are invaluable. Most planning tools are dimensional: product, location, and time, being the most popular. Without these balance equations, it would be very difficult to make the entire dimensional cube calculate correctly.

For each UOM, I will introduce the most typical measures that add to the inventory or reduce the inventory. Below is a summary of each equation equaling the ending on hand, or EOH. I will be referring to the monetary valuation of inventory using the United States Dollar, or USD, but you can replace that with any currency. In this section we’ll look at the most common valuations: Full Price, Retail, Wholesale, Cost, and Units.

Units
Units (UNT). Refers to individual, or physically measurable components like units, weight or volume. 12, 6-pack AA batteries, 12 men’s mesh t-shirts and 12 pounds watermelon would be examples. There are quite a few nuances to this when we introduce casepacks, inner casepacks, and prepacks. But the concept is still the same if the unit we’re measuring can be reduced to a common UOM.

Cost
Cost (CST). The cost valuation measures the investment you have made in the inventory. This is by far the most challenging valuation so I’m going to spend a little extra time here. Every company has a slightly different definition of the cost basis depending on the accounting practices they have.

Most companies employ a perpetual inventory valuation, while a fraction still use periodic valuations. Perpetual just means the inventory is recalculated regularly, usually daily. Periodic is usually after a physical count. What’s important is that the cost basis includes everything that was physically deployed to procure the goods on the first receipt.

This is where things get nuanced. The purchase order cost plus the freight and duty to get the inventory to the distribution center is a typical way to calculate the cost basis. Whereas, the freight to the store and the receiving costs in the store may or may not be included, as those are transfer costs which are considered below the gross margin line. All subsequent costs like distribution may be considered outside of the cost basis and reconciled on the profitability report.

For the details of calculating the cost basis, I highly recommend Leo F. Griffin’s book entitled “Retail Auditing, A Practitioner’s Guide”, 1998 published by John Wiley & Sons, Inc. It’s old, I admit but the principles haven’t changed at all. My copy still has the original 3.25″ floppy disk.

There are significant nuances to calculating the cost basis. Retailers will typically employ one of these methods to calculate the cost in the stock ledger. The general ledger will use standard GAAP and will need to be reconciled to the stock ledger periodically.

• LIFO – Last In, First Out. The last receipts are the first to be sold.
• FIFO – First In, First Out. The first receipts are the first to be sold.
• Cost Averaging – Very popular method. Each receipt is added to the last receipt and averaged on a per unit basis, or an AUC.
• Standard Cost – a standard cost is given to a product and is reconciled at the end of a fiscal period, typically the end of the fiscal year. Sometimes retailers won’t know the cost at the time the product is set up in the merchandising systems. This simplifies the perpetual inventory calculations but introduces some issues with subsequent calculations, like gross margin.
• Retail Inventory Method – before computers this was the preferred method as it estimates the ending inventory on a retail valuation and cost valuation. The cost of goods sold is then estimated. A huge assumption is made that the receipts are grouped by margin. If the margins within the estimation vary widely, the estimate will lead to erroneous results. Great book on this is “Retail Inventory Method Made Practical” by James T. Powers of Peat, Marwick, Mitchell & Co., 1971, published by the National Retail Merchants Association (NRMA).

Wholesale
Wholesale (WHSL). This valuation measures what the wholesale customer paid for the merchandise. This cost can be taken directly from the sales order.

An important distinction must be made here. To you, a transaction to a wholesale customer is considered a sale (or shipment). To the wholesale customer, this transaction is considered a receipt. When we start calculating the gross margin, we’ll need to make the distinction of perspective: whose gross margin? I’ll discuss perspective more in the next section.

Retail
Retail (RTL). Retail measures the inventory valuation at full price, or what is on the price tag now; but, with one exception: sales. The sales are measured by what is paid by the customer, or point-of-sale, usually a cash register. Example: the price of a phone may be $400 but the customer buys it for $320 because there was a 20% off coupon.

For retailers that value their inventory at retail, there is an additional set of markdown metrics that are needed to distinguish between permanent and temporary.

A permanent markdown is one where the inventory valuation is reduced permanently to reflect changes in business conditions. The inventory is not expected to return to the original valuation. These types of markdowns are price reductions, clearance, and liquidations.

A temporary markdown is almost always some type of point-of-sale markdown like a promotion. During a promotion the value of the inventory remains at full price until it is sold. Once sold, the markdown is applied at that time. There can be many types of temporary markdowns: traditional promotions, temporary price reductions, employee discounts, and loyalty discounts to name a few.

Retailers that employ cost averaging generally don’t have such a complex set of markdown metrics. Instead they rely more heavily on discounts, the difference between the estimated full price of the inventory and the actual sales amount. In this scenario, all markdowns are temporary markdowns taken at the cash register or point-of-sale.

Full Price or Manufacturer Suggested Retail Price
Full Price (MSRP). The full price measures are the same as retail except for sales which is measured at full price without the discounts or markdowns. This set of measures is generally not shown but rather is used in the background to calculate discounts for those retailers that only employ temporary markdowns.

Perspectives and Types
Perspectives refer to location hierarchy levels, like store, distribution center, wholesale customer, or company. Types refer to the level of ownership, or commitment, the perspective has on the inventory, like what is physically in the store or what is physically in the store plus what is in-transit.

• Perspective: From whose point-of-view
• Types: Where is my inventory in the supply chain?

Conservation of Inventory
Just one comment before we jump to perspectives. This is probably the most important concept regarding the inventory balance set: ending inventory must add up to what you started with and what was added minus what was removed.

When we talk about perspectives such as a store or a distribution center, the conservation of inventory is still in effect. A transfer-out of one system may be a transfer-in to another system but no matter which perspective you take the inventory will always balance. 

Let’s look at an easy example. Let’s say I have 1000 units in the distribution center and 100 units in the store. I want to transfer 50 units from the DC to the store. Assume a zero leadtime for purposes of illustration.

Our total system has 1100 units of inventory. Now when I transfer the inventory from one perspective to another notice the total system inventory is preserved. Let’s move 50 units from the DC.

Perspectives
We’ll explore three types of perspectives as it relates to the inventory balance set:

• Supply Chain – how inventory moves from a factory to the final selling location.
• Seasons – how inventory, particularly apparel product, moves around.
• Sales Channels – Within the supply chain there are other refinements that need to be made that support the various sales channels a retailer may have.

There are many perspectives to consider for supply chain, but these three use cases will highlight the unique advantage Anaplan has over most leading supply chain planning tools. The ability to pivot the model so it meets the unique needs of the company and ties it all together with connected planning is extremely challenging for most planning solutions, but not for Anaplan. Let’s look.

Use Case 1: Supply Chain
What I’m about to describe is my point-of-view on the difference between receipts and transfers. To be honest, there’s no right answer if the inventory remains balanced. But having worked with these equations in many dimensional planning tools there are methods that make life a lot easier.

Receipts occur when inventory is owned for the first time into the business’ supply chain. All subsequent inventory movements within the supply chain are transfers. This makes balancing the inventory much easier and you can track the movement to and from transfer locations.

Equations get very confusing and challenging to interpret if you call a transfer a receipt after it’s been introduced into the supply chain. For example, let’s say a store obtains inventory from the distribution center and directly from a factory, or a drop ship.

The drop shop should be considered a receipt and the DC inventory should be a transfer. If the DC inventory was defined as a receipt then there’s no easy way to reconcile the inventory movement within the supply chain, i.e., between the DC and the store, because it’s mixed up with the drop ships.

In addition to the challenges caused by defining DC to store movements as receipts, there is the additional problem of measuring in-transit. Some retailers will assign ownership to stores immediately after a transfer is requested. Technically, this is not yet a receipt because the inventory is still in the DC. The transfer-in metric can be part of the store’s inventory balance set, accomplishing the ownership issue.

Let’s take another look at our DC and store example. Let’s add receipts this time. We’ll ship 200 units from the factory to the DC and 150 units from the factory directly to the store. We’ll continue to assume a zero leadtime.

Nice and clean. And, it allows us to track where the inventory came from and where it is going. Finally, it allows us to introduce in-transit and on-order measures and allows us to conduct further analysis on where the inventory is located, who owns it, and when it is likely to show up.

I’ll cover in-transit and on-orders in more detail when we talk about types.

Use Case 2: Seasons
Up to now we’ve only talked about inventory movements as it relates to locations: stores, distribution centers, factories, etc. Seasons are generally considered as product attributes not time attributes even though they conceptually are thought of having a begin and end. So, moving inventory from one season to another is anything but intuitive.

Seasons are fundamental to apparel merchandise planning. Typically, as part of an assortment plan they form the foundation of what the merchant would like the customer to see when they walk in the physical store or virtual store (online). There simply is no easy way to reconcile this type of assortment plan to a fiscal, merchandising plan without some type of compromise.

Remembering our guiding principle about the conservation of inventory will help us greatly here. I have managed these two ways both having their own pros/cons which I’ll discuss, but both will maintain a balanced inventory set. The first is to create another set of transfer metrics. The second is to harden the begin and end of a season and associate specific inventory deliveries to a season.

Idea 1: Extra Balance Set
Creating another set of transfer metrics allows every merchant to determine for themselves when they feel a season begins or ends, or if perhaps, they want to carryover their inventory. The challenge with this method is that the inventory movement is completely dependent on the merchant to make this indication and that every merchant uses this indicator consistently.

IT has a role to play too because the data transformations are critical to maintaining inventory balance sets. The ETL, extract-transform-load, team will need to make a debit/credit transaction every time inventory is moved from one season to another.

Also, to make matters a little easier, you might consider creating an alternative hierarchy off the product grain (usually style-color). This simplifies the reconciliation to a fiscal calendar. For IT, this will require a table with the time-product hierarchy intersection or, if performance is a problem, these will need to be de-normalized on the main tables.

Here’s how a balance set might look.

Idea 2: Hard Season Begin and End
By hardening the begin and end date of a season simplifies reporting greatly, but it also means that all merchants must agree to these time frames. This method presents some geographic issues particularly around climate and hemispheres. Sometimes we forget when it’s winter in the northern hemisphere it’s summer in the southern hemisphere.

To work around some of these challenges, some companies will associate deliveries to specific seasons. For example, receipts associated with a March 15 delivery may be defined as Summer even though the delivery name says March 15 which is in the Spring.

Methods that hard code seasons present a big challenge with how to associate the sales to a season especially if the inventory extends well beyond their intended selling period. One solution is to automatically move the inventory out of the season and into a carryover status. The carryover attribute can be a season, or just a product attribute/indicator. This provides a good analytic to distinguish new inventory from older inventory.

Use Case 3: Sales Channels
If the company is fortunate to be operating in more than one sales channel, as most are now, then you’ll need a mechanism to move inventory between them. The most important thing to remember is to consider the “perspective” each channel has. Here are some of the more typical retail sales channels to consider:

• Traditional Retail Stores (Brick & Mortar)
• Wholesale
• Store-In-A-Store (Concession)
• Consignment
• E-Commerce
• Outlet (Factory Stores)
• Licensed
• Affiliate
• Drop Ship
• Pop Ups

Moving inventory between and within some of these channels probably requires a deep understanding of your company’s transportation logistics operations. Two important nuances are worth mentioning.

Transfers to Outlet or Factory Stores. Traditionally, these transfers typically are done to move inventory from a full price store to an outlet store after a season, or particular assortment mix is past season. These transfers may reset the MSRP valuations in order to calculate more meaningful discounts and/or markdowns. Careful consideration to accounting laws should be taken. More and more, outlet channels are producing their own inventory in order to make the store look well-rounded so mixing the inventory brings all kinds of interesting challenges.

Wholesale: Your perspective or theirs? The great thing about technology like EDI is that your wholesale customers may be willing to share the sales and inventory with you in a consistently defined manner. This is particularly important to the wholesaler because it allows them to calculate the demand of their products and enables them to forecast future seasons, among other things. The challenge is to make sure your inventory balancing is done based on the “perspective” of the channel. For example, If the perspective is your company, then shipments into the wholesale channel would be considered a “retail sale at a wholesale MSRP value”. The same inventory to the wholesale account would be considered a “receipt at cost”.

TYPES
In this last section I’ll discuss the inventory status, or “where is the inventory”? There are hundreds of measurements taken by retailers but here are the most popular:

INVENTORY TYPES AND INVENTORY STATUSES

• Planned But Not Ordered. This might be part of a merchandise plan, assortment plan or ladder plan, or it might be a commitment to buy to a factory such as a blanket order. But at this stage, a purchase order has not been written.
• Ordered But Not Shipped. This type marks the beginning of the “On-Order” stage where a purchase order has been written but the factory has not yet shipped.
• Shipped / Inbound. Still considered to be on-order but the physical inventory is on its way to your initial destination, presumably a distribution center.
• DC Onhand. The physical inventory has been received and three-way matched indicating (usually) that your company is now the owner of the inventory.
• Allocated. Once received, the inventory is then allocated to a sales channel, immediately flows thru the DC, or is put away in stock and allocated later.
• In-Transit / Outbound. This is physical inventory that has left the distribution center and is currently on its way to the final destination.
• Transferred-In. The final destination has taken possession of the physical inventory.
  Sale. Inventory has been sold.
• Transferred Out / Adjustment. Excess inventory is transferred to another location or is adjusted in some way, such as for shrink or a retail/cost adjustment.

Summary
The inventory balance set is a simple equation. But as we learned, when applied to all the different planning processes that make the entire supply chain work, the calculation becomes increasingly difficult to balance. If there are multiple solution providers along the way the complexity becomes nearly impossible to reconcile. Anaplan solves the problem by pulling all planning processes into one platform and sharing the data across the entire supply chain through connected planning.

 

References

• The New Science of Retailing by Marshall Fisher and Ananth Raman, 2010
• Leo F. Griffin’s book entitled “Retail Auditing, A Practitioner’s Guide”, 1998 published by John Wiley & Sons, Inc.
• “Retail Inventory Method Made Practical” by James T. Powers of Peat, Marwick, Mitchell & Co., 1971, published by the National Retail Merchants Association (NRMA)
• “Retail Accounting and Financial Control”, Robert M. Zimmerman, Robert M. Kaufman, Gregory S. Finerty, James O. Egan, Published by Wiley, 1990