Districts currently on Classic who have not migrated to Redesign Inventory yet

The following may be used if you encounter balancing issues on FTD depreciation amounts due to active items containing a straight-line method, not fully depreciated, that contain invalid beginning depreciation dates (a.k.a. "the perfect storm").   The Import log will include a warning for all items that contain invalid beginning depreciation dates "Item #: xxxxxx has a depreciation method of: Straight_Line with an invalid date".  However, for those items that are active and not fully depreciated, it may affect their 102, 104 & 305 report FTD amount balancing to Redesign.  There could be few, several or hundreds of these items where either the difference needs to be accounted for so they can move on with balancing or our strong recommendation to fix them in classic (for ease in balancing).

Listed below are the steps to find and update the invalid beginning depreciation dates in Classic prior to migration.

We know that items with invalid depreciation dates in Classic will migrate over with blank beginning depreciation dates in Inventory.  Using a test instance of the district’s data, you can (1) create a spreadsheet of active, straight-line items you will export and (2) upload into EXCEL correcting only items with an invalid beginning depreciate date, and then (3) file transfer and load into Classic's EISIX>EISIMP. 

1. In the district’s test instance, query active items with straight-line depreciation method.  Unfortunately, you can't filter just blank beg depreciation dates (so it will include both valid and invalid beg dep dates) .  

2. Export Grid Items using EXCEL format

Open

1.  

   1. Once loaded into EXCEL, sort by beginning_depreciation_date (in order for blank dates to appear first) and delete all items that contain a valid date so your spreadsheet is left with only active, straight-line items with blank beginning depreciation dates.

   2. Delete any columns you don’t need.  Our recommendation would be to remove all EXCEPT inventory_tag, status, acquisition_date and depreciation_begin_date.

      1. Open

         

   3. Highlight the acquisition_date column and select Data> Text To Columns option in EXCEL using the ‘Convert Text to Columns Wizard’ to transition it to a YYYYMM format in order to format it correctly for Classic import.

      1. On Step 1 of the wizard, select ‘Fixed Width’

      2. On Step 2, create the following break lines on the acq date

   4. On Step 3 of the wizard: format MM DD and YYYY as TEXT and do not import slashes (/) in between.  Click FINISH. If it states there is already data here, click OK to replace it.

   5. The result? Each section of the date will be its own column.  In a blank column, enter depreciation_begin_date for the header and format the new column to YYYYMM by using the following formula. (which you can then pull down to quickly formulate the date for all tags

   6. Next, include column headers for only the columns needed to import into Classic (highlighted below).  Save the file as a CSV.

Perform Steps 3-5 in a test set of their classic data/test instance first.

3. File-transfer to VMS side and run EISIX>EISIMP

4. Since these items beginning depreciation dates have been invalid, their existing LTD amounts may be incorrect as well.  In order to recalculate LTD figures from scratch, run EISDEPR - Projection option - and review the report to see if the tags from your spreadsheet are included and if their LTD amounts have changed.  Please note, the EISDEPR program will recalculate LTD for all active items.  Please contact your auditor so they can review all tags listed in the projection report as there may be a difference between the ‘old’ and ‘new’ depreciation figures, and the report may contain tags that were not included in your spreadsheet.  Your auditor will work with you to determine if the EISDEPR actual option should be run to allow the Classic EIS system to recalculate LTD from scratch for the items that were included on the projection report. 

5. Re-extract Classic files and re-import into a test instance and start running balancing reports. 

6. If all balances in their test run, you may proceed in their live Classic data, performing Steps 3-5 again 

Districts who have already migrated to Redesign Inventory

For those districts who have already migrated but may not have encountered the 'perfect storm' scenario during migration balancing,  it is recommended to review your existing depreciation data to ensure it contains valid beginning depreciation dates and correct LTD depreciation amounts. The field auditors are aware that incorrect LTD depreciation amounts carried over from Classic may exist.  This may be due to the following situations that occurred in Classic: 

• invalid beginning depreciation dates

• incorrect LTD amounts entered in ITMSCN or via EISIX spreadsheet import

• changes made to other values (i.e. useful life) involved in the calculation of depreciation of an item that could have affected the LTD depreciation figure

Items with invalid depreciation dates in Classic migrated over with blank beginning depreciation dates in Inventory.  You can create a spreadsheet of active, straight-line items that can be exported and uploaded into EXCEL correcting those items with invalid beginning depreciate dates (blank dates), and then re-import using the SYSTEM>Import option. 

1. In your instance, query active items with straight-line depreciation method.  Unfortunately, you can't filter just blank beg depreciation dates (so your query will include both valid and invalid beg dep dates) .  

2. Export Grid Items using EXCEL format

3. Once loaded into EXCEL, sort by beginning_depreciation_date (in order for blank dates to appear first) and delete all items that contain a valid beginning depreciation date so your spreadsheet is left with only active, straight-line items with blank beginning depreciation dates.

4. Copy the existing acquisition date and paste it into the depreciation begin date.

5. Delete all columns EXCEPT record_id, id, inventory_tag, and depreciation_begin_date and save in CSV format.

6. Import using SYSTEM>Import - Item import type, ensuring 'update records' is checked.  This will replace the blank beginning depreciation dates with valid dates.

7. Since these items' beginning depreciation dates have been invalid, their existing LTD amounts may be incorrect as well.  In order to recalculate LTD figures, from the items grid, filter active items that are tracking depreciation, click top checkbox to select all filtered items and run Depreciate - Projection option.  Review the report to see if the tags from your spreadsheet are included and if their LTD amounts have changed.  NOTE: this report may also include items that may not have had invalid beginning deprecation dates but incorrect LTD values due to the bulleted situations explained above.  Please contact your auditor so they can review all tags listed in the projection report.  Your auditor will work with you to determine if the Depreciate - actual option should be run to allow the system to recalculate LTD (up through the current period) for the items that were included on the projection report.