The Aged Inventory Report helps you prioritize inventory according to the time it takes to sell the products in the line. This report examines the annual sales of each item in the line, determines the number of pieces on hand, and calculates how long it will take to sell those items.
Note: If you understand the purpose of the Aged Inventory report and want to skip to the procedure click here.
For example, if you sold 365 items last year (1 per day or 30 per month) and have 700 on hand and use 3-month periods, the aging buckets are as follows:.
|
|
Number |
Time period for... |
|
A |
90 items |
0-3 months |
|
B |
90 items |
4-6 months |
|
C |
90 items |
7-9 months |
|
D |
90 items |
10-12 months |
|
E |
360 items |
over 12 months |
The Aged Inventory Report also helps you determine how your products lose value, if any, over the course of the year. If inventory does devalue over time, you can use the writedown buckets to show the percentage of devalue for the aging period.
For example, using the information in the previous example, the cost basis of Cost of Goods Sold is $1.00 per item. The writedown for each aging bucket is a cumulative 20 percent.
The writedown for the aging buckets would be as follows:
|
|
Calculations |
Cost |
For time period... |
At |
|
A |
((90 items * $1.00) * .20) |
$18 |
0-3 months |
20% |
|
B |
((90 items * $1.00) * .40) |
$36 |
4-6 months |
40% |
|
C |
((90 items * $1.00) * .60) |
$54 |
7-9 months |
60% |
|
D |
((90 items * $1.00) * .80) |
$72 |
10-12 months |
80% |
|
E |
((360 items * $1.00)* 1) |
$360 |
for over 12 months |
100% |
If no items were sold in the last year, the report devalues the inventory according to the writedown percentage associated with the No Sales aging bucket. For example, in Canada, this report can be used when claiming devalued inventory on taxes. In the United States, this report can assist in long range inventory planning and budgeting, when holding items in inventory longer than a particular period of time can become a drain on capital assets.
The Aged Inventory report lists the following:
Product ID and description.
Price line.
Year-to date sales for the item.
On-hand supply of the item.
Unit cost per item.
Total value of the item before and after the writedown percentage by the different aging periods.
Years supply of the item in inventory.
Grand totals by price line and by all price lines.
To run the Aged Inventory report:
From the Purchase >Reports menu, select Aged Inventory to display the Inventory Valuation window.
In the Branch/Territory field, enter the branch or territory for which you want to run the report. Type ALL to include all branches and territories.
In the As of Date field, enter the date from which you want the inventory aged. The system populates the field with the current date.
In the Price Line field, indicate the price lines to include on the report. Do one of the following:
Enter the price line name to run the report for a single price line.
Leave the field blank to run the report for all prices lines.
Use the Multi button to choose more than one price line. *Multiple* displays in the field.
In the Cost Basis field, enter the cost basis to use for valuing inventory. The system populates this field with Cost of Goods Sold - Cost (COGS-COST).
In the Nonstock field, indicate whether to include non-stocks in the reports:
Include - Prints non-stocks on the report.
Exclude - Does not print non-stocks on the report.
Only - Selects only non-stocks for the report.
In the Number of Recent Days to Exclude Items Created field, enter the number of days you want to limit the report to for product recently added to your product file, such as 180 to exclude new products added to your product file in the last six months. The default is 180..
In the Number of Recent Days to Exclude First Received field, enter the number of days you want to exclude inventory that has been received for the first time, such as 90 to exclude any products received in the last three months. The system will not include items that are onhand or sold if any branch meets this cutoff date.
Select the Exclude zero on-hand quantities check box, if you want to exclude products with zero on-hand quantities on the reports. The default is Yes.
In the Exclude Level area, select how the Exclude First Received in last field looks at items first received:
Branch - Reports on the first receiver per branch.
Product - Reports on the items received for the first time within the number of days you specify in the Exclude First Received in last field.
In the Writedown area, use the table to enter:
Percentages that the value of the products in the specified price lines is reduced over the specified period of months. For example, if product is on the shelf for more than 6 months, the value can decrease by 25%.
Default periods for this run of the report. For example, you can change the breakdown of months from 0 - 3 months to 0 - 6 months for the first writedown bucket.
Percentage beyond a period of months: type OVER in a From field and the corresponding month in the related To field.
Note: If writedown buckets are not used on the report, 0% is applied to the unused buckets.
See Also: