EIS FYE Closing

1. EIS FYE Closing • Finish all current year processing. Items received prior to June 30th (2009) should be added to EIS for FY2009, Any items received after June 30th can be added to the EIS pending file but should not be added to EIS until FY2010. • NOTE: If the depreciation data has been changed so that it is necessary to completely recalculate the life-to-date (LTD) depreciation from the beginning for a number of items, you may need to run the program EISDEPR. However, if you have been tracking depreciation on some of these tags for some time, then this program might cause problems. Let me explain in the following slide:

2. Using EISDEPR • Example of why it should be used with caution… • original cost of item is $650 being depreciated over 10 years • However, in the 9th year, an acquisition of $200 was posted and increased the original cost to $850. Until then, the item was being depreciated $65/year x 8 years for a book value of $520 thus far. • The increase of $200 in the 9th year changed the original cost to $850 so instead of $130 left to depreciate on this item, it’s now $330 for the remaining 2 years. The annual rate of depreciation will now be $165/year. • Once this item is fully depreciated, the history of depreciation will be an annual rate or $65 for the first 8 years, and then at an annual rate of $165 for the remaining 2 years to reflect the true depreciation over the 10 years. • However, if you run EISDEPR in the 9th year, the LTD depreciation would be re-calculated on an original cost of $850 for the past 10 years. This history of depreciation will now be $85/year over 10 years. (so it will no longer reflect the true depreciation over the past 10 years.)

3. EIS FYE Closing • Districts with a Life Limit specified for the capitalization criteria may choose to run an EIS304 Brief Asset Listing to verify all items meeting the dollar capitalization criteria also have a valid Life Limit and are on file as capitalized assets. • In EIS304, select non-capitalized items and choose to select only items with an original cost equal or higher than the capitalization threshold. Items not meeting both of the capitalization limits will not be capitalized; therefore, this will produce a listing of any items on file that currently are not capitalized that most likely should be. Enter capitalization option: (C,N,B,?) <B> _ N Include or Exclude Lot Items by Quantity? (I,E,N,?): <N> _ Enter selection option (1-9,*,?): <*> _ Do you wish to select items by original cost? (Y,N,?) <N> _ Y Enter original cost: < .00> ________.__ 1000

4. EIS FYE Closing • When all items have been entered, run the recommended list of reports (this includes all necessary GAAP schedules). • All recommended FYE reports are explained in detail in the upcoming slides.

5. GAAP Reports for FYE • EIS101 – Schedule of Fixed Assets by Source…..provides a summary of the original cost of capitalized items by their source or the fund the items were originally charged to when purchased. • Output file: EIS101.TXT • EIS102 – Schedule of Fixed Assets by Function and Class…..produces a schedule of fixed assets by function and class. Can be generated by function and class, class or a summary by function and class. Creates a summary and detail report • Output files: EIS102S.TXT and EIS102D.TXT

6. Made up of items entered on the system without an acquisition record EIS101 Summary of the original cost of capitalized items by their “source”. Each fund type is printed on a separate page and additional page is printed for “unknown fund” types Source means “the fund the items were originally charged to when purchased”. EIS101 uses the PO information from the acquisition record (ACQTRN) to identify the source fund used in purchasing the items. Output file: EIS101.TXT Item record is missing fund code or fund code does not have a valid fund type specified in EISMNT/FNDSCN

7. EIS102 Prints the original cost and/or book value of capitalized assets by their function and/or class. Can be generated by function and class, class or a summary by function and class. The Book Value on the report is the Original Cost minus the Total Depreciation Output Files: EIS102S.TXT EIS102D.TXT

8. EIS103 – Schedule of Changes in Fixed Assets • Contains “changes” in capital asset balances during the current fiscal year • The EIS103 creates three reports as listed below: • The EIS103S is a summary report listing the beginning balance, acquisitions, dispositions, transfers in, transfers out, adjustments, and the ending balance in separate columns horizontally across the report. The following equation is used in determining the ending balance is: Beg. Balance + Acquisitions - Dispositions + Transfers In - Transfers Out +/- Adjustments = End. Balance • EIS103D is a detailed schedule of changes. This report lists any individual tag numbers that may have incurred acquisitions, dispositions, transfers, or adjustments. • EIS103E is an error report. • The summary and detail reports will page break on each fund type: fiduciary, governmental, proprietary, and undefined.

9. EIS103 – Schedule of Changes in Fixed Assets • The GAAP flag must be set to “Y” to be able to generate this report. • For FYE, it is recommend to generate the report 3 different ways (AC, FC & FD). • The report excludes: • items which do not meet the capitalization criteria • items which have a status of "disposed of", "inactive", or "old tag number" • items coded as operating leases • Items that have an undefined function, fund, or asset class code will be placed on a line titled "unclassified". • Specific entities can be included or excluded (i.e. NOGAAP)

10. Sample EIS103 Summary Report Total original cost amount at the beginning of the fiscal year. Should equal the ending balance from the prior fiscal year.

11. Sample EIS103 Summary Report Any newly capitalized items that have been added during the fiscal year.

12. Sample EIS103 Summary Report Amounts of items that were part of the beginning balance but were disposed of during the fiscal year.

13. Sample EIS103 Summary Report Total amount of Transfer into this fund.

14. Sample EIS103 Summary Report Total Amount of transfers out of this fund.

15. Sample EIS103 Summary Report Includes amount for any transaction which have the error adjustment flag set to “Y”. Also includes amounts for items that may have been capitalized at the beginning of the year but are no longer or vice versa (i.e. EISCAP)

16. Sample EIS103 Summary Report + - + - + =

17. EIS GAAP Reports • Totals by Fund Type on the EIS101, EIS102, and EIS103 should match when balancing out for the fiscal year-end. • Any errors reported on the EIS103E report should be resolved prior to closing the fiscal year.

18. EIS104 – Schedule of Changes in Depreciation • Contains changes in depreciation of fixed assets during the fiscal year • The EIS104 creates two reports as listed below: • EIS104S is a summary report listing the beginning depreciation, continuing items, acquisitions, dispositions, transfers, adjustments and ending depreciation balance in separate columns horizontally across the report. The following equation is used in determining the ending depreciation Beg. Dep. + Dep. Continuing Items + Dep. Acq. - Dep. Disp. - Dep. Transfers Out + Dep. Transfers In +/- Dep. Adjustments • EIS104D is a detailed schedule of changes. This report lists any individual tags numbers that may have incurred acquisitions, dispositions, transfers or adjustments • Reports will page break on each fund type: fiduciary, governmental, proprietary and undefined.

19. EIS104 – Schedule of Changes in Depreciation • The GAAP flag must be set to “Y” in order to generate a valid report. • For FYE, it is recommended to generate the report 3 different ways (AC, FC & FD) • The report excludes: • items which do not meet the capitalization criteria • items which have a status of “inactive” or "old tag number" • Items that have an undefined function, fund, or asset class code will be placed on a line titled "unclassified". • May be used as a depreciation balancing tool with the EIS305.

20. EIS104 Summary Report Life-to-Date depreciation as of June 30 of the last fiscal year closed for all items that were capitalized at the beginning of the current fiscal year.

21. EIS104 Summary Report Fiscal-to-Date depreciation for all items that were capitalized at the beginning of the year.

22. EIS104 Summary Report Fiscal-to-Date depreciation for any capitalized items added during the current fiscal year.

23. EIS104 Summary Report Total depreciation (Life to date + Fiscal to date) for any capitalized items disposed of during the current fiscal year

24. EIS104 Summary Report Life-to-Date depreciation for any capitalized items transferred during the current fiscal year.

25. EIS104 Summary Report Total Depreciation (LTD + FTD) for any items that had a transaction posted with the error adjustment flag set to “Y”. Or, items that may have changed capitalization during the year.

26. EIS104 Summary Report - + + + + - - =

27. EIS GAAP Reports • Depreciation totals on the EIS104 should balance with totals on the EIS305 run for capitalized assets. • Beginning depreciation on 104 should compare to the LTD depreciation on the 305 (Active Items + Items disposed of during current FY) • Ending depreciation on 104 should compare to the Total depreciation on the 305 (for active items)

28. Depreciation for the current fiscal year Original Cost – LTD --------------------------- Remaining Life Original Cost – Total Depreciation = Book Value Depreciation as of June 30 of the last fiscal year. As displayed in ITMSCN. Reported as Beginning Depreciation on EIS104. LTD + FTD = Total. Reported as Ending Depreciation on EIS104.

29. Recommended FYE Non-GAAP Reports • EIS203 – Asset Listing by Grant/Source Report ….. is a listing of acquisition transactions by source account code and/or by a grant identifier; This is an optional report and contains much of the information found on the acquisition transaction record plus the location of the asset. • EIS303 – Inventory Master Listing…..includes all data on file for items; VERY LARGE report – it can produce a complete listing of all items or of specified subsets (i.e. generate a report of just depreciation information for all items); don’t recommend printing out a hard copy of the report. • EIS304 – Brief Asset Listing….. a one line per item listing of all items on file or subsets; The following EIS304 reports are recommended…they include capitalized items only and Status Codes of “A, N, EH, and EN” (with the exception of the disposition report). • EIS304ACQ Acquisitions for current fiscal year • EIS304CL Brief Asset Listing by asset class • EIS304DSP Dispositions for current fiscal year • EIS304FC Brief Asset Listing by function • EIS304FD Brief Asset Listing by fund

30. Other Recommended FYE Non-GAAP Reports • EIS305 – Book Value (Depreciation) Report…..shows depreciation information listing original cost, salvage value, book value, % of depreciation, and last year of useful life. • The date 06/xxxx (where xxxx is FY being closed) is used for the reporting date. The following EIS3045 reports are recommended…they include capitalized items only and Status Codes of “A, N, EH, and EN” (with the exception of the disposition report). • EIS305CL Book Value Report by class • EIS305DSPCL Depreciation for current FY dispositions by class • EIS305DSPFC Depreciation for current FY dispositions by function • EIS305FC Book Value Report by function • EIS401 – Insurance Values Report…..lists insurable values and replacement cost information for items; insurable values listed reflect the insurance classifications assigned to the item categories in EISMNT/CATSCN; Recommended if the district maintains current replacement cost and/or insurable values on the inventory item records.

31. Other Recommended FYE Non-GAAP Reports • EIS801 – Audit Report…..tracks changes made to the EIS files • For FYE, select the Official Option and keep on file for the auditors used for an official audit trail; • EISCLS Reports: The following reports are generated from the EISCLS program and recommended to be kept on file • EISCLS.TXT – Ending Balances Report by Fund • EISDEP.TXT – Depreciation Posting Report

32. EIS FYE Closing • When all reports are complete for FYE, make a copy of the EIS files and other appropriate files. • NWOCA has a copy .COM procedure we have districts run. It includes the following files and copies the files to an extension using the current FY (i.e. *.EIS_F09) • All EIS*.IDX files • EISDAT.REL • EISAUD.SEQ • ACCT.IDX • INAMT.IDX • POAMT.IDX • POHIST.IDX • TABL.IDX • VENDOR.IDX

33. EIS FYE Closing Run EISCLS • Creates an EISCLS.TXT as well as an EISDEP.TXT report. • EISCLS.TXT shows the ending balances by Fund, Function and Asset Class. (These are the beginning balances for the next year) • EISDEP.TXT is a summary report of the current year’s depreciation posted by fund. • Advances EIS last FY closed flag in EISMNT/DATSCN by one year • Adds one year’s worth of depreciation to LTD depreciation field • If on GAAP, updates beginning balance fields for new FY • If NOT on GAAP, ITC needs to assist in helping the district start up on GAAP by running EISGAAP. • EISGAAP: run only once and at the beginning of the GAAP startup year. • Sets GAAP flag in EISMNT/DATSCN to “Y” • Creates beginning information fields for each asset in ITMSCN (EISITM.IDX) • Generates a beginning balance report for the GAAP startup year

34. EISCLS.TXT Date: 04/21/09 Sampleville Schools Page: 1 Time: 10:36 am Ending Balances by Fund - Actual (EISCLS) Fund Description Ending Balance Undefined 349,160.00 001 GENERAL FUND 116,421,626.78 006 LUNCHROOM FUND 181,510.09 300 ATHLETIC FUND 297,657.31 572 CHAPTER 1 PROJECT FUND 9,851.00 Total: 117,284,805.18 Date: 04/21/09 Sampleville Schools Page: 2 Time: 10:36 am Ending Balances by Function - Actual (EISCLS) Function Description Ending Balance Undefined 0.00 1110 ELEM. INSTRUCTION 102,364,161.95 1120 MIDDLE SCHOOL INSTRUCTION 2,154,098.56 1130 HIGH SCHOOL INSTRUCTION 3,114,896.97 1200 SPECIAL INSTRUCTION 1,127,754.47

35. EISDEP.TXT Date: 04/21/09 Sampleville Schools Page: 1 Time: 10:36 am Depreciation Posting Report – Actual (EISCLS) EIS GAAP Flag is ON Depreciation Book Value Depreciation Book Value Posted for After Posting Posted for After Posting Fund Description All Items All Items Capitalized Items Capitalized Item Undefined 0.00 349,160.00 0.00 349,160.00 001 GENERAL FUND 16,815,164.43 7,530,978.92 16,834,503.15 7,403,303.94 006 LUNCHROOM FUND 4,112.64 12,872.06 3,487.76 10,732.28 300 ATHLETIC FUND 16,524.95 150,226.68 15,926.28 147,213.75 572 CHAPTER 1 PROJECT 118.81 408.62 34.96 0.00 Total: 16,794,364.68 8,068,837.27 16,815,054.15 7,935,409.97

36. Beginning Info Data • When running EISCLS and on GAAP, it updates beginning info fields for capitalized assets for new FY • When running EISCLS and not on GAAP, ITC needs to run EISGAAP for the district. It will create the beginning information for capitalized assets • Each item has “hidden” fields which are referred to as the GAAP fields (a.k.a. Beginning Info Data). These fields are not visible through any of the EIS prorgrams but can be looked at through DATATRIEVE’s ITEMS • These “GAAP fields” are used in calculating beginning and ending balances by the EIS103 program. • Access SAAS21 library and ready the ITEMS domain. Enter SHOW FIELDS and you will see a section at the end of the list titled “BEGINNING_INFO”

37. Beginning_Info Section BEGINNING_INFO BEGINNING_BALANCE <Number> = ORIGINAL COST BEGINNING_FUND <Character string> = ASSET’s FUND NUMBER LOOKUP_BEGINNING_FUND <Computed value> DISPLAY_BEGINNING_FUND <Computed value> BEGINNING_FUNCTION <Character string> = ASSET’S FUNCTION NUMBER LOOKUP_BEGINNING_FUNCTION <Computed value> DISPLAY_BEGINNING_FUNCTION <Computed value> BEGINNING_CLASS <Character string> = ASSET’S ASSET CLASS NUMBER LOOKUP_BEGINNING_CLASS <Computed value> DISPLAY_BEGINNING_CLASS <Computed value> CAPITALIZED_BEGINNING_OF_YEAR <Character string> = Y or N • Will be “Y” if capitalized asset was added in a prior year • Will be “Y” if EISCAP ran and items once considered non-capitalized is now capitalized • Will be ’N” if capitalized item was added in the current fiscal year GA A P F I E L DS

38. Capitalized item added during current year (prior to running EISCLS) B E F O R E Same item after EISCLS has been run. GAAP fields have been set and item will now be included in the beginning balances. A F T E R

39. On the EIS103, the beginning balance column amounts comes from the stored “beginning balance” field of all items that had the capitalized beginning of year field set to “Y”.

40. EIS103 errors • Ending Balance Amount Not Equal to Item’s Original Cost….thefollowing formula is being used for each active item included on the EIS103: Sum of Current Year Acq. + Beginning Bal. = Orig. Cost • This means the total of all the acquisition transaction amounts for this item for the current FY are added to the beginning balance amount (stored in a hidden field on the item record) and is compared to the item's original cost found on ITMSCN screen 2. If it doesn’t agree, then the error message is issued and the item is NOT included in the EIS103 report.. As seen in ITMSCN Total of all acquisitions posted for an active capitalized item during the current fiscal year From the stored “hidden” beginning_balance field on ITEM record in Datatrieve

41. EISSCN/ITMSCN look at the item’s status (ACTIVE), original cost ($1896.14) and the acquisition date (09/21/2007) EISSCN/ACQTRN….look for any current FY acquisitions with the update original cost flag set to “Y”). District is currently in FY08.

42. DATATRIEVE BEGINNING_INFO SECTION BEGINNING_BALANCE : 1,896.14 BEGINNING_FUND : 001 LOOKUP_BEGINNING_FUND : FD001 DISPLAY_BEGINNING_FUND : GENERAL FUND BEGINNING_FUNCTION : 2800 LOOKUP_BEGINNING_FUNCTION : FC2800 DISPLAY_BEGINNING_FUNCTION : SUPPORT SERVICES- BEGINNING_CLASS : 0300 LOOKUP_BEGINNING_CLASS : A 0300 DISPLAY_BEGINNING_CLASS : MACHINERY - EQUIPMENT CAPITALIZED_BEGINNING_OF_YEAR : Y Sum of Current Year Acq. + Beginning Bal. = Orig. Cost 1896.14 + 1896.14 = 1896.14??? Since this item was added during FY2008, the BEGINNING_INFO section SHOULD NOT contain data… the beginning balance information shouldn’t be set until EISCLS runs for FY2008. Using Datatrieve, remove all of the BEGINNING_INFO section data. By doing so, the formula EIS103 uses will be correct: Sum of Current Year Acq. + Beginning Bal. = Orig. Cost $1896.14 + $0 = $1896.14

43. EIS103 errors • Ending Balance Amount Not Equal to Zero… ….thefollowing formula is being used for each disposed of item included on the EIS103: Sum of Current Year Acq. + Beginning Bal. - Orig. Cost = 0 As seen in ITMSCN Total of all acquisitions posted for the capitalized item during the current fiscal year From the stored “hidden” beginning_balance field on ITEM record in Datatrieve • This means the total of all the acquisition transaction amounts for this item for the current FY are added to the beginning balance amount (stored in a hidden field on the item record). The items original cost is subtracted off and the result is compared to 0. If the result is not 0, then the error message is issued and the item is NOT included in the EIS103 report.

44. EISSCN/ITMSCN look at the item’s status .. This error usually occurs with an item that has been disposed of (status of "D“). Check for a DSPTRN record to see if it might be in error; original cost ($2790) EISSCN/ACQTRN….look for any possible acq. transactions posted in the current FY EISSCN/DSPTRN….look for a disposition transaction and verify the date is in the current FY

45. DATATRIEVE BEGINNING_INFO SECTION BEGINNING_BALANCE : 0.00 BEGINNING_FUND : 001 LOOKUP_BEGINNING_FUND : FD001 DISPLAY_BEGINNING_FUND : GENERAL FUND BEGINNING_FUNCTION : 1130 LOOKUP_BEGINNING_FUNCTION : FC1130 DISPLAY_BEGINNING_FUNCTION : HIGH SCHOOL BEGINNING_CLASS : 0300 LOOKUP_BEGINNING_CLASS : A 0300 DISPLAY_BEGINNING_CLASS : MACHINERY - EQUIPMENT - FIXTURES CAPITALIZED_BEGINNING_OF_YEAR: Y Sum of Current Year Acq. + Beginning Bal. - Orig. Cost = 0 $0 + $0 - $2790 = 0??? Since this item was disposed of during FY2008, the BEGINNING_BALANCE field should still contain the original cost. At the beginning of FY2008, this item was still active, therefore when they closed FY2007, the beginning _balance field should have been updated with the original cost of the item. Disposing of the item in FY2008 does not affect the BEGINNING_BALANCE. To correct this, using Datatrieve, enter the original cost to the “BEGINNING_BALANCE”field By doing so, the formula EIS103 uses will be correct: Sum of Current Year Acq. + Beginning Bal. - Orig. Cost = $0 $0 + $2790 - $2790 = $0

46. EIS103 errors • Transaction Fund/Function/Class Not Equal to Previous Transaction…This type of error occurs when there has been an improper change of Fund/Function/ Asset Class code. • EISSCN/ITMSCN….look at the current fund/function/asset class, and status of the item • EISSCN/ACQTRN….look for any current year acquisitions. If any, note the date, fund/function/asset class (does it match the fund/function/asset class in ITMSCN?) • EISSCN/TRNTRN…. If the fund/function/asset class code on the ACQTRN record is different from ITMSCN, then there has been a change in the coding (TRNTRN). You should look for a corresponding TRNTRN record and examine it carefully. • One source of the error is when a TRNTRN record has been posted with a date prior to the acquisition date. Compare the date on this record with the date(s) on any ACQTRN records. If the TRNTRN record date preceedes the date on the ACQTRN record, you have the source of the error. You may need to delete and re-add the TRNTRN record.

47. GAAP checklist Before running EISGAAP • The following is a checklist of things that need to be completed before running EISGAAP for a new district or district starting over with a new inventory: • Capitalization thresholds are set in EISMNT/DATSCN • All locally defined codes are complete in EISMNT • Current fund, function and asset class codes are entered on each asset • Original cost is entered on all assets • Review capitalized leases • Depreciation information is set for all capitalized items • All acquisitions and dispositions are complete for the current EIS FY

48. GAAP Restrictions Once EISGAAP is run, you are now bound by certain constraints on the system: • Must add an acquisition record for each new item entered. • Must add a disposition transaction record for each item to be disposed of. • Must enter fund, function, asset class codes on ITMSCN - screen 1 for all new items entered. • Must add a transfer transaction record in order to change the fund, function, or asset class codes of an item on the inventory. • The original cost of an item cannot be modified without adding an acquisition transaction record and setting the “update original cost” flag to “Y”.

49. GAAP Restrictions • Cannot change the capitalization thresholds directly on EISMNT/DATSCN. Must run the EISCAP program. Note: If EISCAP is run, the ending capital asset balances for the prior fiscal year will no longer equal the beginning balances for the current fiscal year. • Cannot delete an item or transaction if it affected GAAP statements from the prior year. However, they can be deleted if added in the same fiscal year. • Transaction dates must fall within the current EIS fiscal year. However, “historical acquisition transactions” can be dated for a prior year. (“Update original cost” field is set to “N”)

50. GAAP checklist First year on GAAP • The following is a checklist of things that can be completed before the end of their first year on GAAP (before they run GAAP reports (EIS101 – EIS104) • Enter historical acquisition transactions for (at least) their larger assets in order to make the GAAP schedule by source (EIS101) more accurate. • Acquisition method should be verified and updated if necessary. Donated or lease assets may need acquisition method updated so that are correctly reflected on EIS101. • Any entities that need to be excluded from GAAP schedules or need to be reported separately should have the “entity ID” field in ITMSCN filled in for all items associated with that entity. GASB34 stated items being stored as lots be excluded from the GAAP schedules for it’s suggested that these items be coded with an entity ID such as ‘NO GAAP’. The GAAP schedules have the option to exclude/include entity IDs.