### Average Aggregate Inventory Value

```Inventory Management II:
Inventory Metrics
This module covers calculating demand, simple forecasting,
average aggregate inventory, inventory turnover, bullwhip effect,
and the impact of inventory management on financial metrics.
Authors: Stu James and Robert Robicheaux
© 2013 Stu James and Management by the Numbers, Inc.
This MBTN Module introduces the fundamentals of
inventory management including:
•
•
•
•
•
•
TOPICS COVERED
Topics Covered
Calculating Demand
Simple Forecasting
Average Aggregate Inventory Value
Inventory Turnover (Inventory Turns)
Bullwhip or Whiplash Effect
Impact of Inventory Management on Financial Metrics
MBTN | Management by the Numbers
2
In Inventory Management I, we used demand in several calculations.
Now let’s describe how one would calculate demand itself and then
cover some simple forecasting techniques using only historical
demand.
available to an inventory manager.
CALCULATING DEMAND
Calculating Demand
Definitions
Where p = appropriate time period (year, month, etc.)
Question 1a: Siva wants to calculate last year’s demand for heating
coils for his business. The starting inventory on Jan 1 was 35 and
during the year there were 2 purchases of 250 coils each. Ending
inventory on Dec 31st was 70 units. What was demand for last year?
MBTN | Management by the Numbers
3
Demand
= Starting + Purchases – Ending
= 35 + 250 * 2 – 75 = 465
There are two additional considerations when calculating demand.
The first is backorders. A backorder is a customer order that has
been received, but has not yet been fulfilled. Backorders can inflate or
deflate demand depending on when they are fulfilled.
The second consideration is to not include purchases ordered but not
yet received. Let’s quickly consider an example where these factors
would need to be considered.
CALCULATING DEMAND
Calculating Demand
Question 1b: Siva’s manager, Shaila, checked the calculation and
realized that a backorder of 50 units from the previous year was
fulfilled early in the year and there is currently a backorder of 125 units.
to inventory. What is the demand when adjusted for these factors?
MBTN | Management by the Numbers
4
Demand
= Starting + Purchases - Ending
Since the 2nd order had not yet been received into inventory, purchases only
equals 250 units. Backorders are a little trickier. Strictly speaking, the
backorder for 50 fulfilled at the beginning of the year should not be included,
but we do need to include the current backorder for 125 units as that demand
fell in the current year, but could not be fulfilled due to insufficient inventory.
Demand
CALCULATING DEMAND
Calculating Demand
= Starting + Purchases – Ending
= 35 + 250 – 75 - 50 + 125 = 290
Notice that this is a significant difference from Siva’s original calculation. A
case could also be made on the backorders that they are instead special
orders and not part of normal demand, and therefore neither should be
counted. This is a managerial decision.
MBTN | Management by the Numbers
5
You may also want to convert annual demand into monthly, weekly or
daily demand depending on the circumstances. This is easily done as
shown in the definitions below.
Definitions
CALCULATING DEMAND
Calculating Demand
Weekly Demand (from Annual Demand) = Annual Demand / 52
Monthly Demand (from Annual Demand) = Annual Demand / 12
Daily Demand (from Annual Demand) = Annual Demand / 365
Daily Demand (from Annual Demand) = Annual Demand / 250
(use 365 for calendar days, 250 for business days)
Question 1c: What is the daily demand based on business days for
the heating coils if annual demand is 290 units?
Daily Demand
= Annual Demand / 250 (for Business Days)
= 290 / 250 = 1.16 units / day
MBTN | Management by the Numbers
6
Ideally, when we use demand in our calculations for things such as the
appropriate amount to order, when to reorder, etc., we are actually
more interested in future demand than historical demand. Though the
future is usually unknown, we may be asked to use a forward looking
estimate. This would be called a forecast of demand.
Let’s consider two simple methods of forecasting demand using
historical growth – one based on percentage growth and the second
based on unit growth. Note that demand could be provided in units or
currency.
SIMPLE FORECASTING
Simple Forecasting
Definitions
Forecastp+1 (based on unit growth) = Demandp + Unit Growth
Forecastp+1 (based on % growth) = Demandp * (1 + Growth%)
Where p = appropriate time period (year, month, etc.)
MBTN | Management by the Numbers
7
Question 2a: Shaila asks Siva to calculate two forecasts for the
heating coils, one based on % growth and one based on unit growth.
We know that demand was 290 units for last year. The previous year
was 210 units. Calculate the two forecasts.
SIMPLE FORECASTING
Simple Forecasting
Unit Growth
% Growth
= 290 – 210 = 80 units
= 80 / 210 = 38%
Forecast (Units)
Forecast (%)
= 290 + 80 = 370 units
= 290 * (1 + .38) = 400 units
Sometimes, especially when an item is a subassembly, the appropriate
way to forecast is to apply the forecast from the final assembly (or
assemblies if an item is used in multiple products). Let’s consider an
example of when this would be appropriate.
MBTN | Management by the Numbers
8
Question 2b: Shaila tells Siva that the heating coil is used as a
component part in two air control systems, the XF1 and the XF2. In
addition, they anticipate needing 50 units for spare parts. Marketing
has provided a forecast of 180 units for the XF1 and 100 units for the
XF2. The XF2 uses two heating coils in each system. What should
the forecast for the heating coils be based on this new information?
SIMPLE FORECASTING
Simple Forecasting
Forecast
= XF1 + 2 * XF2 + Spare Parts
= 180 + 100 * 2 + 50 = 430 units
Insight
Often marketing will have more insight into future demand patterns than
could be predicted using historical demand alone. However, using past
growth rates to forecast demand can also provide a quick check on
forecasts provided by others. If there is a large difference, it may be
worthwhile for management to check the rationale.
MBTN | Management by the Numbers
9
Because demand is rarely perfectly stable, companies forecast to
properly position inventory and other resources. Since forecasts are
often based on statistical estimates, they are rarely perfectly accurate.
As we know, due to this variability in demand and forecast error,
companies carry an inventory buffer called "safety stock".
BULLWHIP EFFECT
Bullwhip Effect
Sometimes, a missed due
date or stock-out may
affecting the supply chain.
This phenomenon is know
as the bullwhip effect.
This is graphically depicted
to the right.
MBTN | Management by the Numbers
10
Proctor and Gamble (P&G) executives coined the term after studying
the demand for disposable diapers. As expected, babies used diapers
at a fairly steady and predictable rate, and retail sales were quite
uniform. But, P&G found that each retailer based orders on a slightly
exaggerated forecast, thereby distorting the information about real
demand. Wholesalers' orders to the P&G diaper factory fluctuated
even more. And P&G’s orders to 3M and other materials suppliers
fluctuated even more.
BULLWHIP EFFECT
Bullwhip Effect
Notice how a small rise in
consumer demand launched
through the channel.
Especially look at consumer
demand relative to the
wholesaler and manufacturer
orders.
MBTN | Management by the Numbers
11
Moving up the supply chain from end-consumer to raw materials
supplier, each supply chain participant has greater observed variation
in demand and thus greater need for safety stock.
BULLWHIP EFFECT
Bullwhip Effect
• In periods of rising demand,
down-stream participants will
increase their orders.
• In periods of falling demand,
orders will fall or stop to reduce
inventory.
• Variations are amplified as one
moves upstream in the supply
chain (further from the customer).
• This effect is especially strong in
a supply chain where the
suppliers have few (or only one)
customer.
MBTN | Management by the Numbers
12
To eliminate the bullwhip effect, suppliers throughout the supply chain
can coordinate their orders based on actual end-customer orders.
This is especially effective where there is a single retailer buying from
suppliers in the chain, as it is easier to coordinate.
This approach is called Kanban, and an excellent example of its
implementation would be Wal-Mart’s distribution system.
KANBAN SYSTEM
Kanban System
• Stores transmit point-of-sale (POS) data from cash register to
corporate several times a day.
• Information used to queue shipments from:
• Wal-Mart distribution center to the store
• The supplier to the Wal-Mart distribution center.
Insight
The result is near-perfect visibility of customer demand and inventory
movement throughout the supply chain. Better information leads to
better inventory position and lower costs throughout the supply chain.
MBTN | Management by the Numbers
13
The value of all inventory held is called the aggregate inventory value.
This would also represent the value of inventory on the balance sheet.
The average value of this over the course of a year would be
considered the Average Aggregate Inventory Value.
Definitions
Avg. Agg. Inventory Value = Avg. Units ItemA * Value ItemA + Avg. Units
ItemB * Value ItemB+ … + Avg. Units ItemZ * Value ItemZ
Where Avg. Unit ItemA = Average inventory level in units for ItemA , and
Value ItemA = Value (at cost) basis for ItemA.
AVERAGE AGGREGATE INVENTORY VALUE
Average Aggregate Inventory Value
Insight
Manufacturing firms typically have about 25% of their assets tied up in
inventory, whereas retailers have approximately 75%. These are rough
estimates, and one should consider peer companies to determine best
practices.
MBTN | Management by the Numbers
14
Two measures that managers use to judge performance of inventory
management are Weeks of Supply (or potentially, days) and
Inventory Turnover. Both measures describe inventory levels relative
to sales at cost and incorporate the element of time.
Definitions
Weeks of Supply =
Inventory Turnover =
Average Aggregate Inventory
Weekly Sales (at Cost)
Annual Sales (at Cost)
Average Aggregate Inventory Value
WEEKS OF SUPPLY AND INVENTORY TURNOVER
Weeks of Supply and Inventory Turnover
Insight
As weeks of supply decreases, fewer assets are tied up in inventory.
As inventory turnover increases, fewer assets are tied up in inventory.
These two measures move together, but in opposite directions.
MBTN | Management by the Numbers
15
Question 3a: A business analyzes its inventoried items and finds the
following average inventory levels and associated unit costs. What is
the average aggregate inventory value for the company?
Item
144C
304B
99XY
67Q
667I
UFO3
677T
333Y
ROF3
664C
Average
Inventory
(Units)
2,500
1,250
5,000
2,000
150
1,000
125
11,000
250
400
Unit Cost
\$ 87.00
\$ 120.00
\$
5.00
\$ 10.00
\$ 50.00
\$
5.00
\$ 12.00
\$
0.10
\$
2.00
\$
1.25
Average Inventory Value
\$ 217,500.00
\$ 150,000.00
\$
25,000.00
\$
20,000.00
\$
7,500.00
\$
5,000.00
\$
1,500.00
\$
1,100.00
\$
500.00
\$
500.00
MBTN | Management by the Numbers
AVERAGE AGGREGATE INVENTORY VALUE
Average Aggregate Inventory Value
16
First multiply the average inventory levels for the year by the unit costs as
shown below. Then sum the average inventory value for all the parts.
Item
144C
304B
99XY
67Q
667I
UFO3
677T
333Y
ROF3
664C
Average
Inventory
(Units)
2,500
1,250
5,000
2,000
150
1,000
125
11,000
250
400
Unit Cost
\$ 87.00
\$ 120.00
\$
5.00
\$ 10.00
\$ 50.00
\$
5.00
\$ 12.00
\$
0.10
\$
2.00
\$
1.25
Average Inventory Value
\$ 217,500.00
\$ 150,000.00
\$
25,000.00
\$
20,000.00
\$
7,500.00
\$
5,000.00
\$
1,500.00
\$
1,100.00
\$
500.00
\$
500.00
AVERAGE AGGREGATE INVENTORY VALUE
Average Aggregate Inventory Value
The total of the average inventory values is \$428,600.00, which therefore is
the average aggregate inventory value.
MBTN | Management by the Numbers
17
Question 3b: Given that the average aggregate inventory value is
\$428,600, what is the weeks supply and inventory turns for the
company if the weekly sales at costs are \$55,000 and the annual
revenues are \$11.44 million? The accounting department tells us that
COGS are 25% of sales.
Weeks Supply
= AAIV / Weekly Sales (at Cost)
= \$428,600 / \$55,000 = 7.79 weeks
Annual Sales (at cost)
= \$11,440,000 * .25 = \$2,860,000 - or –
= 52 weeks * \$55,000 = \$2,860,000
Inventory Turnover
= Annual Sales / AAIV
= \$2,860,000 / \$428,600 = 6.67 Turns
WEEKS SUPPLY AND INVENTORY TURNOVER
Weeks Supply and Inventory Turnover
Insight
Quick check: Weeks supply * inventory turns must equal 52. Does it?
MBTN | Management by the Numbers
18
Let’s take a quick look at how inventory management impacts key
financial measures:
• Return on Assets – By reducing inventory levels, total assets will
decrease, thereby increasing ROA. If inventory management costs
can be reduced, ROA will also increase. However, management
must not sacrifice customer service levels that may impact
satisfaction or sales.
• Working Capital – Reducing weeks supply or increasing inventory
turns will improve working capital. Note that reducing lead times
also improves this metric.
• Cash Flow – Reducing the amount of time between when a
company purchases an item and when the company sells the endproduct or service improves cash flow as well.
MBTN | Management by the Numbers
HOW INVENTORY IMPACTS FINANCIAL MEASURES
How Inventory Impacts Financial Measures
19
Please see MBTN Inventory Management modules 1, 3
and 4 that cover other important concepts related to this
module.
MBTN | Management by the Numbers
INVENTORY MANAGEMENT– FURTHER REFERENCE
Inventory Management - Further Reference
20
```