Sample LIFO Projections Report
The image above represents a sample LIFO Projection Report that is created by the LIFO-PRO software. It provides LIFO expense (income) amounts that would result from the range of year end inventory current year cost (FIFO) inventory balances shown in the leftmost column and the range of inflation indexes shown on the sixth row for the subsequent reporting period.
LIFO Projection Calculation Steps
The amount of the annual LIFO expense (increase in LIFO reserve compared to the prior year LIFO reserve balance) is often significant, so many companies attempt to estimate this expense in order to book a LIFO expense amount during the year.
For companies using retail LIFO, changes in cost complements from one year to the next also affects the amount of the change in the LIFO reserve.
The amount of the current year LIFO expense or income is determined by two separate components, the LIFO inflation effect and the LIFO layer erosion effect. The LIFO inflation effect can always be calculated by multiplying the prior year FIFO balance times the current year LIFO inflation percentage. There will not be a LIFO layer erosion effect if there is no LIFO layer erosion. LIFO layer erosions are avoided when there is a LIFO increment instead of a LIFO decrement. A LIFO increment occurs if the current year inventory at base exceeds the prior year inventory at base and a decrement occurs if the converse is true.
Calculating Projected Minimum FIFO Balances Required to Avoid LIFO Layer Erosions
There is a simple way to determine (without making a calculation of the current year inventory at base balance) whether a LIFO decrement is avoided: a LIFO increment occurs (and a decrement is avoided) if the current year end FIFO balance exceeds the prior year end FIFO balance by at least the amount of the current year LIFO inflation effect (prior year FIFO times current year inflation). If there is a LIFO decrement, the amount of the LIFO layer erosion effect is determined by the amount of the decrement and the amount difference between the current year LIFO index and the average of the indexes for the LIFO layers eroded.
A simple example of the calculation of the LIFO inflation effect follows:
|Base year (2012) FIFO balance||100,000||Row 1|
|2013 FIFO balance||104,000||Row 2|
|2013 inflation rate||3%||Row 3|
|2013 inflation effect||3,000||row 4=1 x 2|
|2013 FIFO balance required to avoid layer erosion||103,000||row 5=rows 1 + 4|
There is no LIFO layer erosion effect for 2013 because there is no LIFO decrement and there is no LIFO decrement because the $4,000 increase in FIFO balance from the prior year exceeded the $3,000 LIFO inflation effect.
|2014 FIFO balance||108,000||Row 6|
|2014 inflation rate||3%||Row 7|
|2014 inflation effect||3,120||Row 8=2 x 7|
|2014 FIFO balance required to avoid layer erosion||107,120||Row 9=2 + 8|
There is no LIFO layer erosion effect for 2014 because there is no LIFO decrement and there is no LIFO decrement because the $4,000 increase in FIFO balance from the prior year exceeded the $3,120 LIFO inflation effect.
|2015 FIFO balance||110,000||row 10|
|2015 inflation rate||3%||row 11|
|2015 inflation effect||3,240||row 12=6 x 11|
|2015 FIFO balance required to avoid layer erosion||111,240||row 13=6 + 12|
|2015 layer erosion effect||(46)||row 14|
|2015 net LIFO expense||3,194||row 15=row 12 + 14|
There is a 2015 LIFO layer erosion effect because there is a LIFO decrement. There is a LIFO decrement because the $2,000 increase in FIFO balance from the prior year is less than the $3,240 LIFO inflation effect. The amount of the LIFO layer erosion effect is only $46 because the size of the decrement is small and the index of the 2013 and 2014 layers eroded is not much less than the 2015 index. The 2015 net LIFO expense is $3,194 which the sum of the $3,240 LIFO inflation effect minus the $46 layer erosion effect.
Calculating Projected LIFO Expense & Increments
For any year in which there is a LIFO increment, the LIFO expense will be the amount of the LIFO inflation effect regardless of how much the current year end FIFO balance has increased compared to the prior year end. Because of this, the effect the amount of an increase in FIFO balances compared to the prior year has on the current year LIFO expense is only that of preventing a LIFO decrement (if the balance increases by at least the amount of the current year LIFO inflation effect). Any further effect of the LIFO reserve by a higher FIFO balance will only occur in the following year.
Knowing this, unless there is a large LIFO decrement expected for the year (usually caused by a significant decrease in the FIFO inventory balance), the amount of current year LIFO inflation is the primary factor influencing the amount of the current year LIFO expense.
Inventory balances are relevant in making midyear or year end LIFO projections but unless the year end inventory balances will be a lot less than at the prior year end, using the actual prior year end breakdown by CPI or PPI category may produce reasonably accurate LIFO projections.
Companies that use the IPIC method can use the amount of CPI or PPI inflation since the prior year end to make midyear projections of their annual LIFO expense. They can do this using the inventory balances as of midyear month end periods but unless the inventory mix has changed a lot since the prior year end and unless the inventory balance is expected to be a lot less than the prior year end and the decrease in inventory is expected to continue through year end, spending time to break down the inventory by the appropriate CPI or PPI categories probably is not a good investment of time. It is far simpler to use the inventory mix as of the prior year end especially since there is no guarantee the midyear inventory mix will be the same as for the next year end.
Instructions for running LIFO Projections in the LIFO-PRO Software
Companies using the LIFO-PRO software can make IPIC method projections very quickly if they use the prior year end Excel file showing the FIFO inventory balances by CPI or PPI category. The CPI or PPI inflation a company would normally use for a projection is either that reflected in the most recent 12 months’ CPI or PPI indexes or the CPI or PPI inflation since the appropriate month used for year end calculation. The following steps should be followed for running LIFO PROJECTIONs for a company with these assumptions: December year end, PPI indexes, a November appropriate month and the most recent PPI indexes published are July 2009.
- Select the proper data folder from the LIFO-PRO Company Selection menu.
- Click on the IPIC Data Input… command button on lower right of the Main Menu.
- From the IPIC Menu, click on the IPIC Method Settings command button and make sure the current year and prior year appropriate index month settings are both November and that the year end month specified is December and click on either the Exit and Save Settings or Exit and Don’t Save Settings command buttons to return to the IPIC Menu.
- From the IPIC Menu, click on the third command button in the top row of four command buttons that is labeled Use 12/31/2008 year end Report 23 for projection. This file (Excel file named IPICRpts122008.xlp) will load. This file shows the prior year end inventory balances by PPI codes. The cell A2 entry for the December 2009 year end (next year to be closed) should already be present but you should change this if necessary. To run the projection, put your mouse cursor on the cell that shows the grand total inventory balance and then click on the command button on the left labeled Start IPIC index calculation. To run the projection using an Excel file, click on either of the first two command buttons on the top row of command buttons.If the indexes have not yet been published for the current year appropriate month selected, within a few seconds the program will display a message with a title bar reading “Indexes not found….” and the text in the Buttons will provide the user with two different inflation measurement period options. The message will read as follows:
When the calculations are complete, the current year indexes will appear in the column so labeled in the Report 23S that is displayed. Make sure the row under the Totals/ avg. row reads either “Inflation rates are Jul 2010 PPI indexes divided by Nov 2009” if the first button is selected or “Inflation rates are Jul 2010 PPI indexes divided by Jul 2009” if the second button is selected. If the third button “Cancel” is selected you will be returned to the IPIC menu screen.