So, you want to learn
Bookkeeping!
Merchandise Inventory
by Bean Counter's Dave Marshall

Lesson 3
Estimating Inventories


Introduction Lesson 1 Lesson 2 Lesson 3 Lesson 4 Lesson 5 Lesson 6
Bean Counter
Most businesses because of the time and expenses involved in taking a Physical Inventory (counting and assigning costs), only take an inventory once a year at the end of their accounting period.

A weakness of using the Periodic Inventory Method is that since no continuous detail records are maintained, the cost of the goods that are sold are not known until a Physical Inventory is taken. Why is this a weakness ? I don't know about you, but I'd like to have a pretty good idea of whether I'm making money during the year and not have to wait until year end to take an inventory to find out !

Without monthly or at least quarterly Profit and Loss Statements as the old saying goes "the horses may all have already gotten out of the barn". You need to have readily available information in order to adjust plans and take any remedial actions needed to insure the profitability of your company. You are in the business to make a reasonable profit aren't you ? Also, no early information is available for tax planning. At the end of your year it's too late to make any decisions and take any actions that might save you some taxes.

So, if I want to prepare monthly or quarterly financial statements (interim statements), what other options are available that don't require a physical inventory ? You can estimate your Ending Inventory. Can you think of any other reason that might require you to estimate your ending inventory ? God forbid, but what if your inventory was destroyed by a fire. It's very difficult to count ashes and determine what goods were destroyed..

Two common methods used for estimating inventories are:

  • Gross Profit Method
  • Retail Method

The Gross Profit Method uses an estimated Cost Of Goods Sold and/or Gross Profit Rate. Let's first define Gross Profit. Gross Profit is the difference obtained by subtracting Cost Of Goods Sold from Sales.

Gross Profit = Sales - Cost Of Goods Sold
Note: Gross Profit is also often referred to as Gross Margin. The two terms mean exactly the same thing.

The Gross Profit Percentage Rate is calculated by dividing the Gross Profit Margin Dollars by Sales Dollars and multiplying by a 100.

  • Gross Profit Percentage Rate = Gross Profit Margin Dollars / Sales Dollars x 100

The actual calculations convert the percentage amounts to their decimal amounts by dividing the stated percentage amount by 100.

  • Gross Profit Decimal Equivalent Rate = Gross Profit Percentage Rate / 100

The Cost of Goods Sold Percentage Rate is calculated by dividing the Cost Of Goods Sold Dollars by Sales Dollars and multiplying by a 100.

  • Cost Percentage Rate = Cost Of Goods Sold Dollars / Sales Dollars x 100

The actual calculations used convert the percentage amounts to their decimal amounts by dividing the stated percentage amount by 100.

  • Cost Decimal Equivalent Rate = Cost Percentage Rate / 100

Nothing earth shattering here, we learned about decimals and percentages in elementary school.

The calculations are illustrated in the following table.

  Dollars Percentage Calculation Decimal
Equivalent
Sales 1,000 100 % (1000/1000) x 100 1.00
Less:Cost Of Goods Sold 750 75 % (750/1000) x 100 .75
Gross Profit 250 25 % (250/1000) x 100 .25

Bean Counter's Warning:
I've run across many small business owners that use a desired Gross Profit (Margin) Percentage to mark up cost in order to calculate the Selling Prices of their products; however, many don't calculate the selling price correctly and end up leaving money on the table.

Quick Lesson in Correctly Calculating Selling Price
Assumptions:
Desired Gross Profit (Margin) Percentage- 20%
Desired Gross Profit Decimal Equivalent- .20
Product Cost- $1.00

Erroneous Calculation Often Made
Markup Calculated = Cost X Desired Gross Profit Decimal Equivalent
Markup Calculated = $1.00 x .20
Markup Calculated = .20
Calculated Selling Price = Product Cost + Markup Calculated
Calculated Selling Price = $1.20

Correct Calculation
Calculated Selling Price = Product Cost / (1 - Desired Gross Profit Decimal Equivalent)
Calculated Selling Price = $1.00 / (1 - .2)
Calculated Selling Price = $1.00 / .8
Calculated Selling Price = $1.25

Let's calculate our actual gross profits (margins) and see why the first method of calculating is wrong.

Description Correct Method Wrong Method
Selling Price $1.25 $1.20
Cost $1.00 $1.00
Gross Profit $.25 $.20
Gross Profit % 20.00 % 16.66 %
  (.25 / $1.25 x 100) (.20 / $1.20 x 100)

If my goal is to obtain a 20 % Gross Profit (Margin) Percentage I should sell my Product for $1.25 not a $1.20. I'm sure none of you have been calculating your prices incorrectly have you ?

Correct Calculation Steps

  • Determine Desired Gross Profit Percentage
  • Convert the Desired Gross Profit Percentage to a Decimal Gross Profit by dividing by 100
  • Calculate The Markup Factor by subtracting the Desired Decimal Gross Profit from 1.
  • Calculate Desired Selling Price by dividing Product Cost by the Calculated Markup Factor

Let's do one more sample calculation assuming a product cost of $1.00.
Desired Gross Profit Percentage - 40 %
Decimal Gross Profit = 40/100 =.4
Markup Factor = 1 - .4
Markup Factor = .6
Calculated Desired Selling Price = $1.00 / .6
Calculated Desired Selling Price Needed to Generate a 40% Gross Profit= $1.666 (rounded to $1.67)

Just like a TV show interrupts for a commercial, you've had your word from your sponsor, me, now back to Estimating Inventories and our first topic, the Gross Profit Method .

Gross Profit Method

We use the Gross Profit Method to estimate our ending inventory in the following situations:

  • A physical count is not feasible.
  • As a check of the reasonableness of our inventory amount.
  • To prepare interim (monthly, quarterly) financial statements when using the Periodic Inventory Method.

The method is not acceptable for tax (IRS) or in meeting annual financial reporting requirements. In other words, the method is used to prepare reports that are used internally.

The underlying rationale for the Gross Profit Inventory Method is based on the premise that Cost of Goods Sold can be estimated from Sales based on calculations obtained from prior analysis of the normal relationship between selling price and cost of goods sold.

Amounts needed in order to estimate our inventory are:

  • Cost Of Goods Available For Sale
    Cost Of Beginning Inventory
    Cost Of Purchases
  • Sales
  • Estimated Gross Profit Percentage or Cost of Goods Sold Percentage
    Estimated Gross Profit Percentage is obtained from an analysis of the previous year's product sales and cost data and arriving at an average gross profit rate (sales minus cost of goods sold divided by sales multiplied by 100).

Assume we obtained the following information from our business records:

Beginning Inventory $50,550
Net Purchases $325,450
Sales $475,650
Estimated Gross Profit 30%
Estimated Gross Profit
Decimal Equivalent
.30

Estimate Of Ending Inventory Calculations

  • Calculation Of Goods Available Sale
Beginning Inventory $50,550
Net Purchases 325,450
Goods Available For Sale $376,000
  • Calculation Of Estimated Cost Of Goods Sold
Option 1     Option 2  
Sales $475,650   Sales $475,650
Less:
Estimated Gross Profit
($475,650 x .30)
$142,695      
Estimated Cost Of Goods Sold $332,955  
Estimated Cost Of Goods Sold
($475,650 x .70)
$332,955
  • Calculation Of Ending Inventory
Goods Available For Sale $376,000
Less:
Estimated Cost Of Goods Sold

$332,955
Estimated Ending Inventory $43,045

Calculations Presented As One Schedule

Beginning Inventory $50,550
Net Purchases 325,450
Goods Available For Sale $475,650
Less:
Estimated Cost Of Goods Sold

$332,955
Estimated Ending Inventory $43,045

Considerations when performing the calculations:

  • Adjust the rate for changes in the relationship between gross profit, cost, and sales such as adding new product lines.
  • Consider calculating and using different gross profit rates for each type of inventory or product groups with varying gross margins.
  • Consider using an average gross profit rate calculated based on several prior periods instead of just the prior year's information.

Retail Inventory Method

The retail inventory method is an aggregate method used to estimate the cost assigned to our ending inventory. The method requires maintaining some detailed records. Records are maintained for purchases and inventories at both cost and retail prices. In other words, the cost of the beginning inventories and purchases are maintained as well as the retail value (sales prices) of the beginning inventories and purchases.

In addition a physical inventory is taken and priced at retail.

Let's prepare a simple table to illustrate the cost and retail data maintained.

We obtained the following information from our detailed records.

Category Cost Selling Price
(Retail)
Beginning Inventory 10,000 15,0000
Purchases 100,000 150,0000
Sales   120,0000

Why don't we have a cost amount in our table for Sales ? Easy answer. We don't know ! That amount along with our Ending Inventory Cost is what we're going to estimate using the Retail Method.

Step (1)
The Goods Available for Sale at Retail is calculated by adding the Beginning Inventory Retail Value and the Purchases Retail Value.

  • Goods Available For Sale @ Retail = Beginning Inventory @ Retail + Purchases @ Retail

Calculation of Goods Available For Sale at Retail

Beginning Inventory-Retail 15,0000
Purchases-Retail 150,0000
Goods Available For Sale-Retail 165,0000

Step (2)
The Ending Inventory amount stated at Retail Dollars is determined by subtracting the Sales for the period from the Goods Available for Sale at Retail.

  • Inventory @ Retail Dollars = Goods Available For Sale @ Retail - Sales

In our illustration, the calculations would be:

Sales obtained from our General Ledger for the period were $120,000.

Beginning Inventory-Retail 15,0000
Purchases-Retail 150,0000
Goods Available For Sale-Retail 165,0000
Less:
Sales
120,000
Ending Inventory-Retail 45,0000

Step (3)
The Cost Of The Goods Available For Sale at Cost is calculated by adding The Beginning Inventory at Cost and the Purchases at Cost.

  • Cost Of Goods Available For Sale @ Cost = Beginning Inventory @ Cost + Purchases @ Cost

Calculation of Goods Available For Sale at Cost

Beginning Inventory-Cost 10,0000
Purchases-Cost 100,0000
Goods Available For Sale-Cost 110,0000

Step (4)
The cost percentage (cost-to-retail-ratio)is obtained by dividing the Goods Available for Sale at Cost by the Goods Available for Sale at Retail Prices.

Cost Percentage (Cost-To-Retail-Ratio) Calculation:

Cost Percentage (Cost-To-Retail-Ratio) = Goods Available For Sale @ Cost / Goods Available For Sale @ Retail x 100
Cost Percentage (Cost-To-Retail-Ratio) = 110,000 / 165000 x 100
Cost Percentage (Cost-To-Retail-Ratio) = 66.67 %
Cost Decimal Equivalent = .6667

Final Step
Our final step is simply to multiply our Cost Percentage/Cost-To-Retail-Ratio (Decimal Equivalent) by our Ending Inventory- Retail Value
Estimated Cost Of Ending Inventory = $45,000 x .6667

Estimated Cost Of Ending Inventory = $30,002

What about our Cost Of Goods Sold amount ?
Now that we have an estimate for the cost of our Ending Inventory the calculation of our Cost Of Goods is simple arithmetic.

Beginning Inventory- Cost 10,000
Purchases- Cost 100,000
Goods Available For Sale- Cost 110,000
Less:
Ending Inventory- Cost

30,002
Cost Of Goods Sold 79,998

So we had Sales of $120,000 with an estimated Cost of $79,998.

Just testing ya. What's our Gross Profit (Margin) and what estimated percentage Gross Profit did we make ?

  • Gross Profit is $40,002 ($120,000 - 79,998)
  • Estimated Gross Profit Percentage = 33.33 % ($40,002 / $120,000 x 100)

The steps used in calculating the estimated cost of the ending inventory using the Retail Inventory Method are normally combined into one schedule.

Category Cost Retail
Beginning Inventory 10,0000 15,0000
Purchases 100,0000 150,0000
Goods Available For Sale 110,0000 165,0000
     
Percentage Of Cost To Retail 110,0000 / 165,000 x 100 = 66.67% (.6667)
     
Less:Sales   120,0000
Ending Inventory at Retail   45,0000
Estimated Ending Inventory at Cost 30,002 (45,000 x .6667)
Estimated Cost Of Goods Sold 79,998  

What major benefit did we receive from using the Retail Inventory Method ? If you recall, in our prior Lesson about Costing Methods, we had to determine the unit costs either from invoices, our perpetual records, or our detailed purchases records in order to assign cost to our ending inventory and cost of goods sold.

Using the Retail Inventory Method eliminated the need for determining unit costs and maintaining detailed product cost records. Before you start jumping up and down too much though, we have to maintain detailed sales and price (retail) records.

The reasoning behind the calculation is quite simple and is based on the following simple equation and the relationship between cost and retail values expressed by our Cost To Retail Ratio (percentage calculation).

The calculation assumes that the cost-to-retail-ratio computed from the goods available for sale is a representative average of the goods contained in the ending inventory. In reality, it's very unlikely that all the products in the ending inventory would have the same cost percentage. Actual goods in the ending inventory might have 70%, 65%, 75, etc. cost percentage. In other words, we're assuming that the mix of products contained in the ending inventory is the same as the mix calculated and represented by our cost-to-retail-ratio calculated from our goods available or sale.

(1) Retail Value of Beginning Inventory + (2) Retail Value of Purchases
= (Equals)
(3) Retail Value of Goods Sold (Sales) + (4) Retail Value of Ending Inventory

In other words, the total of the goods we had On Hand during the year are either Sold or in our Ending Inventory.

Simple algebra allows us to calculate any of the values if we know three of the values.

Since our records provide us with the values for (1)Retail Value of Beginning Inventory, (2)Retail Value Of Purchases, and the (3)Retail Value Of Goods Sold (Sales), we can calculate our (4)Retail Value Of Our Ending Inventory by rearranging our equation as follows:

Retail Value Of Ending Inventory = Retail Value Of Beginning Inventory + Retail Value Of Purchases - Retail Value Of Goods Sold (Sales)

Since we use Cost to value our Ending Inventory all we need to do to convert the Ending Inventory Amount at Retail to Cost is to calculate our Cost-To-Retail Ratio and multiply it by the Ending Inventory Retail Value.

Ending Inventory At Cost = Ending Inventory At Retail x Cost-To-Retail Ratio

Since I like to keep things simple, the basics that are involved in using the Retail Inventory Method are summarized in the following three steps:

  • Step 1 Calculate the Value Of The Ending Inventory At Retail
  • Step 2 Calculate Cost-To-Retail Ratio (Percentage)
  • Convert Ending Inventory At Retail To Cost by multiplying by the Cost-To-Retail Ratio (Percentage).

The main difference between the Gross Profit Method and the Retail Inventory Method is the data that is used to calculate the cost percentage used to convert sales at selling prices to sales at cost. The retail inventory method uses a cost percentage, called the cost-to-retail ratio , which is based on a current relationship between cost and selling price. The gross profit method relies on past data to estimate the current cost-to-retail-ratio (percentage).

What about those Cost Flow Assumptions we discussed in Lesson 2 ,Average, FIFO, and LIFO ? Whether you were aware or not, we used the Average Cost Flow Assumption with the Retail Inventory Method in our prior example calculations.

The key to using the Retail Method is the calculation of the Cost-To-Sales-Ratio. The calculation is slightly different based on the cost flow assumption that is used with the Retail Inventory Method.

The FIFO Cost Flow assumes that the ending inventory is made up of the latest purchases. Due to this fact, our calculation of our Cost-To-Sales-Ratio normally excludes our Beginning Inventory Cost and Retail Amounts. Our calculation becomes Net Purchase Cost divided by Net Purchase Sales Value.

The LIFO Cost Flow assumes that the ending inventory is made up of the oldest purchases. We normally would calculate two (2) Cost-To-Sales-Ratios. One for the Beginning Inventory Amounts and the other for the Current Purchase Amounts.

I've made the examples up to now very basic in order to illustrate the basic calculations used with the Retail Inventory Method. The real life applications are just slightly more complex. In order to understand, the following examples we need to become familiar with some new terminology.

  • Original or Normal Selling Price - price at which goods are normally sold.
  • Markups are increases in price which raise our price above our original selling price.
  • Markup Cancellations are decreases in price downward from our current selling price which is currently greater than our normal selling price back toward our original normal selling price but not below it.
  • Net Markups are simply the difference between Markups and Markup Cancellations (Markups - Markup Cancellations).
  • Markdowns are decreases in price which lower our current selling price below our original selling price.
  • Markdown Cancellations are increases in price upward from our current selling price which is currently below our normal selling price back up toward our original selling price but not above it.
  • Net Markdowns are simply the difference between Markdowns and Markdown Cancellations (Markdowns - Markdown Calculations).

Examples of usage of the terms:

  • Original Selling Price of our Super Widgets is $15.00, normal price our customers pay.
  • Increased Selling Price to $17.00 which represents a $2.00 markup ($17.00 - $15.00).
  • Decreased Selling Price from $17.00 to $16.00 represents a $1.00 markup cancellation ($17.00 -$16.00).
  • Decreased Selling Price from $16.00 to $14.50 represents a $1.00 markup cancellation and a $.50 markdown of our original $15.00 selling price.

Markup cancellation is $1.00 ($16.00 - $15.00).
The amount of the markup cancellation does not go below our original selling price of $15.00.

Price decreases below our original selling price are mark downs.
Markdown is .$50 ($15.00 - $14.50). We decreased our current selling price below our normal $15.00 selling price.

  • Increased Selling Price from $14.50 to $14.75 represents a markdown cancellation of $.25 ($14.75 - $14.50). Markdown cancellations do not go above our normal selling price.

Observation:
Note that you can't have a markup cancellation without first having a markup and that you can't have a markdown cancellation without first having a markdown.

Detailed Example Of Retail Method Calculations assuming FIFO, LIFO, and Average Cost Flows.

We obtained the following information from our detailed Sales and Accounting Records:

Description Retail Cost
Sales (Less Discounts) $8,000  
Sales Discounts 400
Sales Returns 200
Breakage 100
Markups 1,000
Markup Cancellations 100
Markdowns 2,000
Markdown Cancellations 700
Beginning Inventory 1,000 700
Purchases 10,000 7,500
Purchase Returns 500 400

Step (1)
Our first step is to calculate our Goods Available For Sale at Retail. This is done the same way regardless of the Cost Flow Assumption.

Step (2) Calculate our Ending Inventory At Retail by subtracting our Total Retail Value Of Our Goods Sold, Spoiled, or Damaged from our Goods Available For Sale At Retail.

Description Retail Subtotals Retail Totals
Beginning Inventory   $1,000
Purchases 10,000  
Less:
Purchase Returns

500
 
Net Purchases   9,500
Markups 1000  
Less:
Markup Cancellations

100
 
Net Markups   900
Markdowns 2,000  
Markdown Cancellations 700  
Net Markdowns   1,300
Step (1) Total Retail Value
of Goods Available for Sale
 
10,100
Sales (less discounts) 8,000  
Sales Returns 200  
Sales Discounts 400  
Spoilage/Breakage 100  
Step (2) Total Retail Value of
Goods Sold, Spoiled, or Broken
 
8,700
Ending Inventory   $1,400

Step (3)
Calculate the cost-to-retail ratio to summarize the relationship between cost and retail value. (This ratio is calculated differently for different retail methods.)

Step (4)
Convert the ending inventory stated at retail to ending inventory stated at cost by multiplying by the cost-to-retail ratio.

Three versions assuming Average, FIFO, and LIFO of the retail method are illustrated below.

Description Retail
Subtotals
Retail
Totals
Cost Average
Retail
Average
Cost
FIFO
Retail
FIFO
Cost
LIFO
Retail
LIFO
Cost
Beginning Inventory   $1,000 700 $1,000 700        
Beginning Inventory-LIFO Layer               1,000 700
Purchases 10,000   7,500            
Less:
Purchase Returns

500
 
400
           
Net Purchases   9,500 7,100 9,500 7,100 9,500 7,100 9,500 7,100
Markups 1000                
Less:
Markup Cancellations

100
               
Net Markups   900   900   900   900  
Markdowns 2,000                
Markdown Cancellations 700                
Net Markdowns   1,300   1,300   1,300   1,300  
Step (1) Total Retail Value
of Goods Available for Sale
 
10,100
             
Total Cost Value of
Goods Available For Sale
 
 
7,800            
Total Retail and Cost Values
Used In Calculation
 
 
  10,100 7,800 9,100 7,100    
Total Retail and Cost Values
Used In Calculation
LIFO-Second Layer
 
 
         
9,100

7,100
Sales (less discounts) 8,000                
Sales Returns 200                
Sales Discounts 400                
Spoilage/Breakage 100                
Step (2) Less:
Total Retail Value of
Goods Sold, Spoiled, or Broken
 
8,700
             
Ending Inventory
At Retail
  $1,400              

Step (3)

Method Cost Retail Ratio Decimal
Equivalent
Average 7,800 10,100 7,800 / 10,100 .7722
FIFO 7,100 9,100 7,100 / 9,100 .7822
LIFO
LIFO-First Layer 700 1,000 700 / 1,000 .70
LIFO-Second Layer 7,100 9,100 7,100 / 9,100 .7822

Step (4)

Method Retail Ending
Inventory
Decimal
Equivalent
Assigned Cost
Ending Inventory
Value
Average $1,400 .7722 $1081.08
FIFO $1,400 .7822 $1,095.08
LIFO
LIFO-First Layer $1,000 .70 $700.00
LIFO-Second Layer 400 .7822 $312.88
   Total LIFO     $1,012.88

Average Flow Computations
Retail Value includes the retail value of Beginning Inventory, Net Purchases , Net Markups, and Net Markdowns. Cost Includes the cost of Beginning Inventories and Net Purchases.

The average method pools the beginning inventory and purchases and assumes that ending inventory is representative of this "pool" to arrive at an Average Cost-To-Retail Ratio which considers both the "mix" of the beginning inventory and purchases.

FIFO Flow Computations
Retail Value includes the retail value of Net Purchases , Net Markups, and Net Markdowns. Cost Includes the cost of Net Purchases.

Note:Beginning Inventory Retail Values and Cost are omitted from our calculation. Can you think of a good reason for doing this ? The definition of the FIFO Cost Flow provides an excellent clue. Since FIFO assumes that the oldest or first goods purchased are sold first, the ending inventory is made up of the last or latest goods purchased. Since we assume that are beginning inventory has been sold, our Cost-To-Retail Ratio calculation for converting our ending inventory at retail to cost only uses the Cost and Retail Value of our Current Purchases. In addition, all the Net Markups and Net Markdowns are assumed to relate to our current purchases.

Observation:If in our example our sales had been less than the Dollar Retail Value Of our Beginning Inventory amount of $1,000, we would have had two Layers for our FIFO example and two Cost-To-Retail calculations.

LIFO Flow Computations
Since LIFO assumes that the latest purchases are sold first and ending inventory contains the oldest goods purchased, more than likely our Ending Inventory will be made up of some items from our Beginning Inventory and some items from our Current Purchases. In our example, our Ending Inventory At Retail is $1,400 which represents $1,000 of Retail Value from our Beginning Inventory and $400 of Retail Value from our Current Purchases. In other words our year end inventory is composed of two cost "layers." In any period where net purchases exceed the cost of the items sold a new layer would be added to our Ending Inventory.

Observation:Actually, the IRS treasury regulations govern the use of the LIFO Costing Method with the Retail Inventory Method. This method is often referred to as the LIFO Retail Method. The example presented was simplified and did not include the use of price indexes which are actually required when using this method.

Dollar Value LIFO
Another method that you may run across but wasn't discussed is called Dollar Value LIFO. This method also uses price indexes to value the "layers" of the ending inventory.

You should know the routine by now. Yep ! Just a little quiz to check you out.
Quiz-Estimating Inventory
Are you still keeping up with me ? I sure don't want that snail to beat ya ! We've covered quite a bit of ground even if some of us take a little longer to get there. Patience and perseverance are traits to be admired so keep plugging along.
<--BACK        NEXT-->
Introduction Lesson 1 Lesson 2 Lesson 3 Lesson 4 Lesson 5 Lesson 6
Bean Counter