FIN - Data Validation - Asset Management
Purpose: This document is to provide colleges instructions on how to perform the validation of data in the PeopleSoft environment.
Colleges are being asked to review data that was converted from the legacy environment to PeopleSoft. The goal of data validation is to ensure legacy data was accurately converted into PeopleSoft. College Subject Matter Experts (SMEs) are included in data validation as a learning experience in navigating the PeopleSoft application, selecting records to review, and reporting issues discovered.
College executives are asked to support the time and effort expended by college SMEs necessary to complete the following:
- Develop a familiarity with navigating the relevant PeopleSoft menus and pages.
- Review selected records, comparing legacy data to the records as converted into PeopleSoft.
- Report issues discovered after careful review of converted data.
- Monitor resolution of reported issues for review in the next conversion testing round.
- Develop an approach for self-managing the data validation and issue reporting process in subsequent rounds of data conversion testing.
It is expected that College Data Validation SMEs will have already read the Data Validation Overview guides before beginning their validation activities:
During the Business Process Fit Gap sessions, attendees were provided an Asset Conversion Worksheet template. You were asked to provide a complete list of your assets for converting into ctcLink. Note: For DG4 Asset Management Data will not be converted until Cycle #3.
The worksheets were transformed into ExceltoCI workbooks. The assets were then loaded into ctcLink and processes run to calculate current year depreciation and create the requisite accounting entries. There are separate accounting entries for the creation of the asset and recording the monthly depreciation amounts.
After go-live, asset additions and depreciation would flow to the General Ledger via Journal Generator, but for conversion the General Ledger already reflects the addition and accumulated depreciation. These journals will not be created for conversion, so the validation will use online pages and queries on the DIST_LN (AM accounting line) table to compare with the sample data provided.
The Validation Results and Feedback are due NO LATER THAN on the last day of the Data Validation Period. It is strongly recommended that issues are reported EARLY in the validation period and are not held until the end of the period. Validation Results and Feedback are for reporting closure of the college's validation activities for the conversion cycle. To view a list of the validation periods please refer to the timeline topic in the Data Validation Overview - Getting Started Guide.
The data for the Asset Conversion comes from the BPFG Homework assignments. Colleges were asked for a complete asset listing, as of the last update to your fixed asset accounts in FMS. Compare the ctcLink asset data to your legacy data. Also compare the ctcLink asset totals to the converted GL balances.
Several issues were noted during prior conversions that need to be considered as you do your validations. The issues, and any remediation, is noted below.
- Asset cost did not meet minimum capitalization thresholds. Depending on the Asset Class, whether the asset represented a Small & Attractive asset or not, the threshold used may have been adjusted to allow the asset to be loaded. Otherwise, assets that did not meet the thresholds did not get loaded.
- Small & Attractive assets do not create any accounting or depreciation entries. These assets can still be viewed from the Basic Add pages.
- Accumulated depreciation data exceeds the Asset cost on the supplied spreadsheet, resulting in a negative net book value (NBV). No adjustment was made for these assets, and when the depreciation calculation was processed, ctcLink created negative deprecation amounts to bring the net book value back up to $0 at the end of the asset’s useful life. (If the asset did not fall into the next group.)
- Assets were listed with In Service dates that indicated that the asset should have been fully depreciated, but the asset still showed a positive net book value. If, for example, the asset Class (as defined by OFM) should be depreciated for seven years, but the In Service date was 10 years ago, ctcLink will not generate any further depreciation for that asset. These assets will never depreciate down to a $0 NBV.
Follow the steps below to ensure the cost and accumulated depreciation agree to the provided data and verify the future depreciation calculations are correct.
Navigate to: Navigator > Reporting Tools > Query > Query Viewer
Run the query CTC_CNV_ASSET_VALIDATION and download it to Excel to slice-and-dice the asset data as needed. The query will prompt you for your Business Unit.
Make sure all the assets listed on your spreadsheet are listed. Capital and Small and Attractive assets will be listed. If an asset is not listed, check your spreadsheet for total cost the asset may have not met the required threshold.
Navigate to: Navigator > Reporting Tools > Query > Query Viewer
Run the query QFS_AM_CNV_DEPR_VALIDATION to Excel and validate the Current Period (month), YTD, and total Accumulated Depreciation amounts. This query also calculates the net book value of each asset as of June 30 of the fiscal year being converted.
Supply your Business Unit and click View Results. Only capital assets will be listed, as Small and Attractive assets do not depreciate.
You can also review and validate the future depreciation by month and distribution for individual assets. Go the Period Depreciation page to see the calculated monthly depreciation amounts. You can view any year by selecting a different Year and clicking Get Period Depr. You can get the useful life in years for an Asset Class from OFM. ctcLink calculates depreciation monthly, so you’ll have to convert years to months to do your own calculations.
Navigate to: Navigator > Asset Management > Depreciation > Review Depreciation Info > Asset Depreciation
Supply your Business Unit and click search. Only capital assets will be listed, as Small and Attractive assets do not depreciate.
Go the Period Depreciation page to see the calculated monthly depreciation amounts. You can view any year by selecting a different Year and clicking Get Period Depr. You can get the useful life in years for an Asset Class from OFM.
At the bottom of each page is a Next in List button. You can click that button to walk through the details for each asset for your Business Unit. If you get a message asking if you want to save your changes, just click No. The next asset will load, and you can navigate to any of the three pages to validate the next asset. Alternatively, click on Return to Search. The original search results will still be displayed, and you can click on a different asset ID.
In ctcLink, the Account used comes from system configuration, the remaining Chartfields are copied from the distribution provided on the spreadsheets.
A list of applicable asset-related accounts is provided here for your reference.
Navigate to: Navigator > Asset Management > Accounting Entries > Review Financial Entries
Supply your Business Unit and click Search. Select the first asset in the search results. Like the Asset Depreciation pages, there is a Next in List button that will let you walk through the assets one at a time.
Review the Account for each line of each Trans Type. ADD rows indicate the entries created by adding the asset and accumulated depreciation. DPR rows indicate the monthly depreciation entries.
Pay particular attention to the accounting for assets acquired with Proprietary Funds, as opposed to Governmental Funds. Proprietary Funds use separate Depreciation Expense accounts, as well as different accounting upon asset retirement.
Scroll the page to the right to see the Amount field. The Account field is fixed and will still display.
In the above example, row 1 shows the fixed asset account 1121070 Furnishings and Equipment is being debited for $15,223.59. This amount is offset on row 3 by account 5040010 Furnishings and Equipment expense. The system assumes the asset was purchased through AP/PO on an expense account, so this entry is to reclass the expense as a capital asset. (Don’t worry. We know these entries have already posted to the legacy GL, and we will not be posting any conversion accounting entries to the ctcLink GL.)
Row 2 is crediting 1121080 Allowance for Depr Furn and Equip, and debiting 5070010 Depreciation Expense Proprietary on row 4. Rows 5-10 represent the monthly depreciation amounts.
Because Small and Attractive (S&A) assets don’t create capital asset or depreciation entries, they cannot be validated using the steps above. You can Search for an Asset (Navigator > Asset Management > Search for an Asset) and see if they’re listed, but to see the details, you need to go to the Basic Add pages.
Navigate to: Navigator > Asset Management > Asset Transactions > Owned Assets > Basic Add
Supply your Business Unit and an S&A asset ID from your spreadsheet and click Search.
Your S&A asset General Information page displays. Here you can see the Description, the Asset Class, Acquisition Date, Tag Number and other details.
Go to the Asset Acquisition Detail page to see the Asset Cost. On a special note if you supplied Federal Asset Identification Numbers (FAINs) on your spreadsheet, the Description on this page is the only place you can see it, other than queries.
College Data Validation SMEs will communicate any conversion issues they encounter during their data validation via the Oracle Test Manager (OTM) application. The ctcLink Testing Team will provide OTM orientation materials before the data validation period for any College Data Validation SMEs who are not familiar with the tool. Please refer to the Data Validation Overview - Reporting Issues guide for more information on using OTM.
When logging a new issue in OTM related to data being validated through this guide, remember to use the following values for these specific required fields in your issue entry under the project tied to this Deployment Group and Data Validation Cycle
- Summary: (Provide a title for the issue discovered)
- Assigned To: Andy Palm
- Status: (Leave as NEW)
- College or Location : (Specify the college or district)
- Priority: (Leave blank - not used)
Also, upon completion of data validation, please ensure feedback has been logged in OTM.