Aspect Software

 

Back-office software for Restaurateurs

 
 
 

Understanding Inventory Valuation

 

(Revised 04-25-2000)


 

This document describes the methods used by Aspect Software to calculate inventory prices and ending inventory values. There are two methods that can be used. Inventory can be valued at the last price paid, or it may be valued using the standard FIFO (first-in/first-out) method. The pricing method is selected in System Settings and is set to FIFO by default.


For purposes of this explanation, let’s assume that we are talking only about one inventory item, say tomatoes. Let’s also assume that we are looking at a one-week period. Here’s what our inventory might look like for tomatoes during that week:


    Beginning Inventory + Amount Purchased - Ending Inventory = Usage / Cost
 

Quantity

10 Pounds

50 Pounds

30 Pounds

30 Pounds

 

Price Per Unit

$1.00

Variable

?

?

 

Value

$10.00

$80.00

?

?


 

(Note that the price and value of the ending inventory is missing as is the average price and cost of the item. Calculating these figures is what this document is about).


Here is a breakdown of the purchases:


  Invoice Amount Purchased Unit Price Extended Price
 

#1

20 Pounds

$2.00 / Pound

$40.00

 

#2

20 Pounds

$1.50 / Pound

$30.00

 

#1

10 Pounds

$1.00 / Pound

$10.00


 

We begin the week with 10 pounds of tomatoes. During the week, we purchase 50 pounds on three invoices. At the end of the week, we find that we have 30 pounds left over. This means that we used 30 pounds of tomatoes during the week. This is calculated as follows:


  Beginning Inventory + Purchases - Ending Inventory = Usage
 

10

50

30

30


 

It’s easy to tell how much inventory we used. But, we also need to know the value of the ending inventory and what our cost was for tomatoes. To determine these, we need to know the correct prices to use.



 

Using the last price paid to calculate inventory values


The simplest method for calculating ending inventory value and cost is to use the last price paid for the item. In the example above, the 50 pounds we purchased came from three deliveries. The last price paid was $1.00 / pound. Using this price, we fill in the ending inventory price and calculate the ending inventory value.


    Beginning Inventory + Amount Purchased - Ending Inventory = Usage / Cost
 

Quantity

10 Pounds

50 Pounds

30 Pounds

30 Pounds

 

Price Per Unit

$1.00

Variable

$1.00

?

 

Value

$10.00

$80.00

?

?


 

Once we know the ending inventory value, we can calculate the cost of the item. Calculating the cost of the item is similar to calculating the usage:


  Beginning Inventory Value + Purchases - Ending Inventory Value = Cost
 

$10.00

$80.00

$30.00

$60.00


 

Once we know the cost, we can calculate an average price of the inventory we used.


    Beginning Inventory + Amount Purchased - Ending Inventory = Usage / Cost
 

Quantity

10 Pounds

50 Pounds

30 Pounds

30 Pounds

 

Price Per Unit

$1.00

Variable

$1.00

$2.00

 

Value

$10.00

$80.00

$30.00

$60.00


 

Although this method is simple, its shortcomings quickly become apparent. One problem is that this method can cause you to incur an inventory cost even when you don’t use any inventory.


In the example below, assume that the starting price is changed to $2.00. Let’s also assume that our ending inventory is equal to what we started with plus what we bought, so our usage is zero. Because we didn’t use any inventory, we would expect our cost to be zero. However, because we are valuing the ending inventory at the last price paid of $1.00, rather than some average of the beginning and ending price, we incur a cost of $10.00 even though we didn’t use any inventory!


    Beginning Inventory + Amount Purchased - Ending Inventory = Usage / Cost
 

Quantity

10 Pounds

20 Pounds

30 Pounds

0 Pounds

 

Price Per Unit

$2.00

$1.00

$1.00

N/A

 

Value

$20.00

$20.00

$30.00

$10.00


 

This problem can become especially noticeable when the price of an item drops due to a discount given by a vendor. For example, assume that you have 15 bottles of a certain type of vodka on hand and that the total value on hand is $150.00. If you receive a 50% discount on the next bottle that you purchase, using the last price paid to calculate the ending inventory value will reduce your inventory on hand from $150.00 to $75.00. You incur a cost of $75.00 even if you don’t use any vodka. In a similar fashion, costs will be understated when the price of an item increases. In fact, it is possible to have a negative cost, even when usage of an item is positive.


It might be argued that since prices for some items will be going up while others go down, that everything will wash out. This is similar to saying that it’s okay to make mistakes on your inventory count by under-counting some items and over-counting others because everything will wash out in the end. It’s not a good way to manage your inventory


Use of the “last price paid” method may be helpful if your store suffers a flood or fire and you need to determine replacement costs for insurance purposes. However, we are not concerned about that here. Our concern is with calculating inventory costs properly so that you can manage those costs more effectively. And, that leads us to FIFO.


 

Using FIFO to calculate inventory values


The method of inventory valuation most commonly used by restaurants is the standard accounting method called FIFO. FIFO stands for first-in, first-out and it simply means that the oldest inventory gets used first. This is why you rotate the stock you receive – to make sure that the oldest tomatoes you have are used before the newer ones.


To understand FIFO, let’s continue with the example we used above. We know how much inventory we started with, what we purchased and how much we have left over. From this, we calculate how much we used. Again, what we need to figure out is the value of the ending inventory so we can calculate the cost of our item.


    Beginning Inventory + Amount Purchased - Ending Inventory = Usage / Cost
 

Quantity

10 Pounds

50 Pounds

30 Pounds

30 Pounds

 

Price Per Unit

$1.00

Variable

?

?

 

Value

$10.00

$80.00

?

?


  Invoice Amount Purchased Unit Price Extended Price
 

#1

20 Pounds

$2.00 / Pound

$40.00

 

#2

20 Pounds

$1.50 / Pound

$30.00

 

#1

10 Pounds

$1.00 / Pound

$10.00


 

The procedure is very simple. We have 30 pounds of ending inventory. Where did it come from? It came from the last two invoices we received, 10 pounds on the last invoice and 20 pounds on the invoice before that. Since we use the oldest inventory first, the 10 pounds that we started with and the 20 pounds that we received on the first invoice are already gone (this accounts for our 30 pounds of usage).


So, what we have left over is 20 pounds of tomatoes at $1.50 / pound and 10 pounds of tomatoes at $1.00 / pound. To calculate the price used to value the ending inventory, we use a weighted average. We have a total of 30 pounds of tomatoes with a total value of $40.00. Dividing $40.00 by 30 gives us a weighted price of $1.333 / pound. Note that we use more than 2 decimal points in the weighted average to avoid rounding errors.


    Quantity X Price = Amount
 

Invoice #2

20

$1.50

$30.00

 

Invoice #3

10

$1.00

$10.00

 

Total

30

$1.333

$40.00


 

Using this price, we find that the ending inventory value is $40.00.


    Beginning Inventory + Amount Purchased - Ending Inventory = Usage / Cost
 

Quantity

10 Pounds

50 Pounds

30 Pounds

30 Pounds

 

Price Per Unit

$1.00

Variable

$1.333

?

 

Value

$10.00

$80.00

$40.00

?


 

Now that we know the ending inventory value, we can calculate our cost as:


  Beginning Inventory Value + Purchases - Ending Inventory Value = Cost
 

$10.00

$80.00

$40.00

$50.00


 

When we divide the cost of $50.00 by our usage of 30 pounds, we find that the average price is $1.67


    Beginning Inventory + Amount Purchased - Ending Inventory = Usage / Cost
 

Quantity

10 Pounds

50 Pounds

30 Pounds

30 Pounds

 

Price Per Unit

$1.00

Variable

$1.333

$1.67

 

Value

$10.00

$80.00

$40.00

$50.00


 

A few words about the average price


You may have noticed that, regardless of the method used, the average price is the last figure to be calculated. It is important to understand that this price really has nothing at all to do with calculating your ending inventory value or inventory cost.


Continuing with the same example, the average price of the inventory used is $1.67.


    Beginning Inventory + Amount Purchased - Ending Inventory = Usage / Cost
 

Quantity

10 Pounds

50 Pounds

30 Pounds

30 Pounds

 

Price Per Unit

$1.00

Variable

$1.333

$1.67

 

Value

$10.00

$80.00

$40.00

$50.00


 

Given this information, a standard inventory extensions report would usually include a line like this:



  Beginning Inventory + Purchases - Ending Inventory = Usage X Price = Cost
 

10 Pounds

50 Pounds

30 Pounds

30 Pounds

$1.67

$50.00


 

Notice that the price displayed in the line above is NOT the $1.333 price used to value the ending inventory. Nor is it the price used to value the beginning inventory or any of the unit prices given on the three invoices. If you think about it, there are actually five prices involved in calculating the cost of the inventory item in our example: the beginning price, the three purchase prices and the ending price. Because it would be very difficult to show all of these prices on one line in the inventory extensions report, we use the average price to keep things simple.



 

What is LIFO?


Although LIFO is not available in Aspect as an option for valuing inventory, you may be curious about how it compares to FIFO. LIFO stands for last-in, first-out. Like FIFO, LIFO is a standard accounting method used to value inventory. Unlike FIFO, LIFO assumes that the inventory that you receive last is the first inventory to be used. It is commonly used in industries dealing with durable goods in which the last inventory received actually is the first to be shipped. In a restaurant, this would be similar to never rotating your stock. (In reality, you could rotate your stock and still use LIFO). Sometimes, tax-related issues may make LIFO an attractive option, but these issues are beyond the scope of this document.