Generate Inventory Aging Table (IC61)

The Generate Inventory Aging Table (IC61) is an inventory aging report generation program that utilizes user configurable calculation values to generate detailed inventory aging reports that are viewable in various iTopia inventory aging query views.

When aging your inventory there are two key areas of configuration control that are available to you each time you generate the data for the report:

1. You enter the Inventory Aging Date which is the effective date that will be used to calculate the age of your inventory.

2. You define the Periods within the Inventory Aging Table. Each period represents the number of days used for the aging calculations.

IC61 takes the Inventory Aging Date you enter each time you execute the program and then based on the Aging Table you assign, starts working backwards through your inventory movement and assigning the inventory quantities and values to the appropriate periods you defined in the Aging Table. The system effectively creates a FIFO evaluation by taking your current on hand quantity (as of your Aging Date) and works backwards through the inventory movement records, placing them into the appropriate aging periods that you defined in your aging table.

• IC61 works with two other programs: Inventory Aging Group (ibis_icapg.im09) and Inventory Aging Period (ibis_im04).

• Inventory Aging Group (ibis_icapg.im09) allows you to create codes that represent the inventory aging analysis structure as defined by the inventory aging periods which are used to age your inventory.

• Inventory Aging Period (IM04) allows you to create and assign aging periods to the aging group codes.

• Together, these two programs enable you to set up as many inventory aging analysis structures as are required by your business.

Let's look at an example of an inventory aging analysis structure:

• Your business may require that you write off inventory that is more than three years old. In this case, you will want to know the value of your inventory that is three years old and under, and you will want to know the value that is greater than three years old. Therefore you will need to capture the results into 2 analysis periods: one for up to 1095 days and one greater than 1095 days.

• In this example, you will set up an Inventory Aging Group of '3YEAR' which is designed to identify this particular inventory analysis structure.

• In the Inventory Aging Period program, you will define a single period (Period 1) set to 1095 days. When IC61 is run, this Period 1 will hold all inventory aging details from the Inventory Aging Date (entered in IC61) back to the 1095th day.
  • The inventory that falls beyond the 1095th day needs to be captured into a second period (1096 days and older). Therefore, a 'last aging period' must be create that will always hold any inventory that falls outside the last defined period set up in Inventory Aging Period (IM04) program.

  • Returning to our example, all inventory that is greater than 1095 days will fall into the last aging period. (Please see the Inventory Aging Group topic for more information).

• Once you run IC61, you can view your results in the inventory aging query views. You will be able to see how much inventory you have that is under 1095 days old and how much is older than 1095 days. You will also see the average cost value of the inventory in both periods.

Another key concept that is critical to using IC61 effectively is understanding the role of the Revision Number:

• The Revision Number is assigned to an Inventory Aging Group in IM04. When you create the inventory aging periods for an Inventory Aging Group for the first time OR if you make changes to the periods for an existing Inventory Aging Group, you must update the revision number. IC61 requires an 'active' revision number in order to run.

• The term 'active' revision number refers to a version of the Inventory Aging Group that is ready to be used, i.e. all changes to the periods are final and no more changes are anticipated.

• Because the output from IC61 is available in iTopia views, the system will be keeping a history of your Inventory Aging reports. If you were able to modify an existing Inventory Aging Group, your historical data would render inaccurately. Therefore, the system must keep track of each version of the Inventory Aging Group(s) used. These versions are called 'revisions' in IC61.

• Once you make a change to an Inventory Aging Group in IM04, the system places the Inventory Aging Group into 'pending' status (pending the completion of the change). You cannot run that Inventory Aging Group in IC61 until the changes are finalized. You finalize these changes by updating the revision number in IM04 by clicking on Revision Update. Once this action is taken in IM04, the Revision Number is updated (considered active) and IC61 can be run.

• The iTopia views will reference the revision number used during the IC61 generate routine. Output associated with previous revision numbers indicates that the periods have been modified since that report was run.

Let's look at an example of how the FIFO evaluation works:

• We have created an Inventory Aging Group of 4MONTH. We have defined 4 Inventory Aging Periods of 30 days each in IM04 for the 4MONTH Aging Group. The last aging period description is '5+ Months'.

• As of October 31, our inventory movement is as follows:
  • 500+ received October 10
  • 300+ received August 10
  • 175 - sold August 05
  • 200+ received July 20

  • 825 is our On Hand balance as of October 31st.

• On October 31, we run IC61 for 4MONTH with an Inventory Aging date of October 31. Our aging looks like this:

  Period 1 (Oct)	Period 2 (Sep)	Period 3 (Aug)	Period 4 (Jul)	...Last Aging Period (5+ Mo)
  500	0	300	25

• Again, we run IC61 for 4MONTH but this time we use an an Inventory Aging date of September 30. Our aging now looks like this:

  Period 1 (Sep)	Period 2 (Aug)	Period 3 (Jul)	Period 4 (Jun)	...Last Aging Period (5+ Mo)
  0	300	25	0

The following list defines all of the fields in IC61:

• Inventory Aging Date - enter or select from the query the date the inventory aging is to begin. The aging routine ages the inventory from the date entered backwards i.e. back in time.

• Inventory Aging Group - enter or select from the query the Inventory Aging Group to which the inventory aging is being applied. Inventory Aging Groups are set up in Inventory Aging Group (ibis_icapg.im09) and the periods associated with the group are defined in IM04.
  • Note: If the Inventory Aging Group does not have an 'active' Revision Number assigned to it in Inventory Aging Group, the following message displays: "Aging Group (Aging Group Name) is pending. Please run the inventory aging group program and select inventory aging period. If this is a new Aging Group, add the Aging Periods and click the Revision Update button. If this is an existing Aging Group, recall the Aging Group and click the Revision Update button. OK". Click OK and update as required. For more information on Inventory Aging Period (IM04), click here; for more information on Inventory Aging Group (ibis_icapg.im09), click here.

The following table outlines the functionality of the button on IC61:

Button Label	Icon	Functionality
Start		Starts the Inventory Aging routine for the selected inventory aging date and Inventory Aging Group. Results are available in the various inventory aging query views.

^{Generate Inventory Aging Table (IC61)}