Excel Worksheet for Financial Reporting - Excel

Document Sample
Excel Worksheet for Financial Reporting - Excel Powered By Docstoc
					                                                                                                  State of Wisconsin
                                                                                    Integrated Business Information System (IBIS)
                                                                                  Subsystem Survey - Asset Management Sub-Systems



                                                                                                                                                                          WiSMART Interface
                                                                                                                                 Homegrown                                (Included Interfaces
                                                                                               Off the     Software Name          (included                               to FMS or DOT
Agency System Name                   Brief Description of Function                              Shelf       If Purchased         contracted) Programming Language         systems)
                                     WiSMART transactions are downloaded and
                                     manually reviewed. Then they are entered into an
    507   Excel Worksheet            Excel worksheet for tracking.
    435   Access Database            Does not interface with WiSMART or FMS                                                           Yes
    575   N/A                        Agency does not use sub systems
505/FRS   N/A                        Agency does not use sub systems

                                     Equipment purchases keyed into system manually.
                                     Information based on reports from Purchase Plus of
                                     Master lease schedules. Use VMS to verify item                                                         uses SQL server Access
    505 Captial Inventory System     descriptions and disposal dates                             NO                                   Yes   data base                     None
                                     Machinery and equipment, land and land
                                     improvements, and building purchases are
                                     manually keyed into the Asset system. FMS
                                     transactions are downloaded and manually
        Major Fixed Assets (MFA)     reviewed. Substantially complete Capital
    410 Program                      Accounting projects are also manually reviewed.             No                N/A                Yes   Access Based                  Manual Interface
                                     Equipment purchases are keyed into the WiseTrack
Courts    Wise Track                 system                                                      Yes           WiseTrack              No                               None Required
          Equipment Tracking         Equipment inventory for items over $1000 and                                                           Oracle Database, SQL,
          System (ETS)               boats, atvs, firearms and federal excess personal                                                      Designer-Web PL/SQL,
                                     property equipment at any value. Also used for                                                         PL/SQL, Developer Reports, No Interface to
    370                              GAAP Reporting.                                             No                                   Yes   JavaScript, HTML           WiSMART
          Land Records System        Inventory of DNR acquisitions, including fee,                                                          Oracle Database, SQL,
                                     easement, lease, sales, gifts, etc. System is                                                          Designer-Web PL/SQL,
                                     responsible for Payment in lieu of taxes, and the                                                      PL/SQL, Developer Reports,
                                     Public Hunting Ground leases. Allows search on any                                                     JavaScript, HTML
                                     property to identify the acquisition funding and                                                                                  No Interface to
    370                              type. Also used for GAAP Reporting.                         No                                   Yes                              WiSMART
          Vehicle (Fleet) System     Vehicle usage chargebacks. Vehicle inventory and                                                       Oracle Database
                                     maintenance. Also used for GAAP Reporting.                                                                                        No Interface to
    370                                                                                          No                                   Yes                              WiSMART
          Resource Management       Queries of Resource Management System data
          System Query (RMS)        extracted from DOA's Service Center System (for
                                    the purpose of Break/Fix Problem Management and                       Peregrine Systems,
                                    tracking PC Inventory Data). Also used for GAAP             Yes -      Inc - client server                                            No Interface to
    370                             Reporting.                                                  DET's         application             No                                  WiSMART
          Project Financial         Queries/reports with inception-to-date information                                                      Oracle Database, SQL,
          Information System (PFIS) on all development, acquisition, gift & donations                                                       PL/SQL, Unix, Crontab, SQL
                                    projects is available on The DNR Project Financial                                                      Loader, ControlM, JCL, SAS,
                                    Information System (PFIS) web page. PFIS                                                                DBMS_JOB
                                    provides "construction in progress" for GAAP                                                                                          No Interface to
    370                             reporting.                                                   No                                   Yes                                 WiSMART
          Facilities and Buildings  Inventory of DNR owned buildings used to track                                                          Oracle Database
          Inventory                 maintenance and repairs. Also used for GAAP                                                                                           No Interface to
    370                             Reporting.                                                   No                                   Yes                                 WiSMART
          GAAP Accountant           Files used to supplement the Land Records System                                                        MS Access, Excel
          Spreadsheets              and the Facilities & Buildings Inventory as needed                                                                                    No Interface to
    370                             for GAAP Reporting.                                          No                                   Yes                                 WiSMART
                                    Equipment purchases are keyed into Asset
                                    Inventory. WiSMART transactions are downloaded
    485   Asset Inventory           and manually reviewed                                        No                                   Yes   Microsoft Visual Basic        None, Manually reconciled and entered
    143   N/A                       Agency does not use sub systems

    445 Tivoli                       This is our Asset tracking system and IT Workorder system Yes       Tivoli Service Desk     No         Proprietary Tivoli Script     Interface to WISARD




           761da32c-2d55-4657-acc4-26e8e8ab5b70.xls                                                                                                                                                               1 of 5
                                                                                              State of Wisconsin
                                                                                Integrated Business Information System (IBIS)
                                                                              Subsystem Survey - Asset Management Sub-Systems



                                                                                                                                                               WiSMART Interface
                                                                                                                            Homegrown                          (Included Interfaces
                                                                                           Off the    Software Name          (included                         to FMS or DOT
Agency System Name                 Brief Description of Function                            Shelf      If Purchased         contracted) Programming Language   systems)
                                   Buildings are recorded based on completed project
                                   info provided by DOA-Capital Accounting and
                                   WisDOT Facilities Mgmt. Worksheet calculates
   395 Buildings - Excel Worksht   straight-line depreciation.                               No                                Yes     Microsoft Excel         None                   Carol Fraedrich, OPBF
                                                                                                                                                                                      Mary
                                                                                                                                                                                      Johnson,
                                                                                                                                                                                      DBM-
                                                                                                                                                                                      BMS;
                                   Moveable non-IT assets are recorded using PO,                                                                                                      Tom
                                   invoice and voucher information. Straight-line                                                                                                     Anderse
   395 Blding Contents - Access    depreciation calculated at year-end.                      No                                Yes     Microsoft Access                               n,
                                                                                                                                                               Gets Information from TIPSBITS
                                   Equipment at State Patrol communication tower
                                   sites recorded using PO, invoice and voucher
   395 DSP Tower Sites - Access    information.                                              No                                Yes     Microsoft Access        None                    Jeff Onhstad, DSP, BOC
                                                                                                                                                                                       Dave
                                   IT equipment record created from download of                      DOA System, RMS is                                                                Miller,
                                   purchasing data (TIPS). Manual entry to adjust &                   based on Service                                                                 BITS;
                                   complete record. (SAS query produces yr-end                       Center, a product of                                                              Glenn
       IT - Resource Mgmt          downloads to Excel worksheets for calculation of                  Peregrine Systems,                                                                Thompso
   395 System (RMS)                depreciation and CAFR reporting.) DOA supported           No              Inc.                                                                      n, (TIPS)
                                                                                                                                                               Download from purchasing BITS
                                                                                                                                                                                       Mona
                                                                                                                                                                                       Yee,
                                                                                                                                                                                       OPBF;
                                                                                                                                                                                       Rick
                                                                                                                                                                                       Mohling,
                                   Vehicle and heavy equipment purchases are keyed                                                                                                     DBM,
                                   into system. Depreciation is calculated. DOA                                                                                                        BMS,
   395 Fleet - Fleet Anywhere      supported                                                            DOA System                                                                     Fleet
                                                                                                                                                               File to DOT Project Accounting System for rate charges
                                   Land & land improvements recorded based on info
       Land (Admin Facilities) -   provided by DOA-Capital Accting and WisDOT
   395 Excel Worksheet             Facilities Mgmt.                                          No                                Yes     Microsoft Excel         None                    Carol Fraedrich, OPBF
   395
                                   Series of Excel worksheets created from data
                                   warehouse and various sources - none interface to
   395 Infrastructure              WiSMART                                                   No                                Yes     MS Access and Excel     None
                                                                                                                                                                                       Rebecca
                                                                                                                                                                                       Krugman
                                                                                                                                                                                       , DTID,
                                                                                                                                                                                       Hwy
                                   Records value, legal description, acreage, etc., for                                                                                                Real
   395 Land - READS                right of way.                                             No                                Yes     MS Access               None                    Estate
                                                                                                                                                                                       Thomas
                                                                                                                                                                                       VanBeek
                                                                                                                                                                                       , DTID-
                                                                                                                                                                                       Hwy
                                   Records value, condition, etc., for scales, waysides,                                                                                               Operatio
   395 Roadside Facilities         and rest areas located along highways.                    No                                Yes     Excel                   None                    ns

                                                                                                                                                                                       William T
                                                                                                                                                                                       McDaniel
                                                                                                                                                                                       , DTSD-
                                   Records deck area, location, condition, etc., for                                                                                                   Structur
   395 Bridges/Structures          bridges and interchanges on highways.                     No                                Yes     JAVA/Oracle             None                    es Mgmt




         761da32c-2d55-4657-acc4-26e8e8ab5b70.xls                                                                                                                                                    2 of 5
                                                                                              State of Wisconsin
                                                                                Integrated Business Information System (IBIS)
                                                                              Subsystem Survey - Asset Management Sub-Systems



                                                                                                                                                             WiSMART Interface
                                                                                                                          Homegrown                          (Included Interfaces
                                                                                         Off the    Software Name          (included                         to FMS or DOT
Agency System Name                 Brief Description of Function                          Shelf      If Purchased         contracted) Programming Language   systems)
                                                                                                                                                                                    Scott
                                                                                                                                                                                    Erdman,
                                                                                                                                                                                    DTIM-
                                                                                                                                                                                    Data
                                                                                                                                                                                    Mgmt-
                                   Records miles and statiscal information on                                                                                                       Hwy
   395 Pavement                    highways.                                               No                                Yes     COBOL/VSAM              None                   Data

                                                                                                                                                                                    Mary
                                                                                                                                                                                    Kunkel,
                                                                                                                                                                                    DTIM-
                                   (Financial Integrated Improvement Programming                                                                                                    Prog.
                                   System) Records project details and descriptions                                                                                                 Fin.-Sys.
   395 FIIPS                       for highway-related construction projects.              No                                Yes     JAVA/COBOL/DB2          None for assets        Support
                                   Records costs and funding information for all
   395 Project Accounting          WisDOT projects.                                        No                                Yes     COBOL/IMS               None for assets        Rich Kneedler, OPBF
                                                                                                                                                                                    Vicki
                                                                                                                                                                                    Schaefer
                                                                                                                                                                                    , DTID -
                                   Records cost, legal description, etc., for land and                                                                                              Rails &
   395 Rail Property               track owned by WisDOT.                                  No                                Yes     Excel                   None                   Harbors
                                                                                                                                                                                    John
                                                                                                                                                                                    Jackson,
                                                                                                                                                                                    DTSD-
                                   Records cost, description, etc., for WisDOT wetland                                                                                              BEES-
       Wetland Mitigation Bank     sites - used for required reporting to DNR and                                                                                                   Environ.
   395 Accting System (WMBAS)      federal agencies, and for internal monitoring           No                                Yes     MS Access and Excel     None                   Srvcs
   395
   395 Supplies                    None interface to WiSMART or WisDOT systems

                                   Records quantity and cost for office supplies,
   395 Central Stores              uniforms, and other supplies used by WisDOT staff       No                                Yes     MS Access               None                   Rhonda Noll, DBM-BMS
                                                                                                                                                                                    Cathy
                                                                                                                                                                                    Meinholz
                                                                                                                                                                                    , DTID-
                                                                                                                                                                                    Hwy
                                   Records quantity and cost for electrical supplies -                                                                                              Operatio
   395 Electrical                  lights, sign boards, etc., used along highways.         No                                Yes     Delphi                  None                   ns
                                                                                                                                                                                    Mary
                                                                                                                                                                                    Rondou,
                                                                                                                                                                                    DMV-
                                                                                                                                                                                    Vehicle
                                                                                                                                                                                    Srvcs-
                                   Records quantity and cost for various types of                                                                                                   Special
   395 License Plates              vehicle license plates issued by WisDOT.                No                                Yes     VB and MS Access        None                   Handling
                                                                                                                                                                                    Matt
                                                                                                                                                                                    Rauch,
                                                                                                                                                                                    DTID-
                                                                                                                                                                                    Hwy
       Signs (Currently called     Records quantity and cost for roadway signs and                 (Planning to move to                                                             Operatio
   395 SIMS)                       some raw materials used to make signs. Shop Inv.        No          Cartegraph)           Yes     Delphi                  None                   ns
                                                                                                                                                                                    Matt
                                                                                                                                                                                    Rauch,
                                                                                                                                                                                    DTID-
                                                                                                                                                                                    Hwy
                                                                                                                                                                                    Operatio
   395 Sign View                   Field Sign Inventory                                   Yes          Cartegraph             NO                             None                   ns


         761da32c-2d55-4657-acc4-26e8e8ab5b70.xls                                                                                                                                                3 of 5
                                                                                             State of Wisconsin
                                                                               Integrated Business Information System (IBIS)
                                                                             Subsystem Survey - Asset Management Sub-Systems



                                                                                                                                                  WiSMART Interface
                                                                                                               Homegrown                          (Included Interfaces
                                                                                     Off the   Software Name    (included                         to FMS or DOT
Agency System Name                Brief Description of Function                       Shelf     If Purchased   contracted) Programming Language   systems)
                                                                                                                                                                         Matt
                                                                                                                                                                         Rauch,
                                                                                                                                                                         DTID-
                                                                                                                                                                         Hwy
                                                                                                                                                                         Operatio
   395 Marking View               Field Marking Inventory                              Yes       Cartegraph        NO                             None                   ns
                                                                                                                                                                         Matt
                                                                                                                                                                         Rauch,
                                                                                                                                                                         DTID-
                                                                                                                                                                         Hwy
                                                                                                                                                                         Operatio
   395 Signal View                Field Signal Inventory                               Yes       Cartegraph        NO                             None                   ns
                                                                                                                                                                         Michael
                                                                                                                                                                         Sproul,
                                                                                                                                                                         DTID-
                                                                                                                                                                         Hwy
                                                                                                                                                                         Operatio
   395 Salt                       Records quantity and cost for road salt.             No                         Yes     MS Access               None                   ns
                                                                                                                                                                         James
                                                                                                                                                                         Emmons
                                                                                                                                                                         /Matt
                                                                                                                                                                         Rauch,
                                                                                                                                                                         DTID-
                                  Records quantity and cost for various equipment                                                                                        Hwy
   395 Pavement Marking           parts, paint, etc., needed for highway marking.      No                         Yes     Microsoft Excel         None                   Op's
                                                                                                                                                                         Mary
                                                                                                                                                                         Rondou,
                                                                                                                                                                         DMV-
                                                                                                                                                                         Vehicle
                                                                                                                                                                         Srvcs-
                                                                                                                                                                         Special
   395 Postage                    Records inventory of stamps and metered postage.     No                         Yes     Microsoft Excel         None                   Handling




        761da32c-2d55-4657-acc4-26e8e8ab5b70.xls                                                                                                                                    4 of 5
                                                              State of Wisconsin
                                                Integrated Business Information System (IBIS)
                                                Subsystem Survey - Macros and Upload Utilities
                                                                                                                  Homegrown
                                                                                                                   (included
Agency   System or File Name                Brief Description of Function               Software Application      contracted)     Transfer Method
   245                                                                                Excel and Access
                                  DET Asset information is entered in an excel
                                  worksheet. Use FIRST to identify payment
                                  transactions that were for capitalized
   505 Excel Worksheet            equipment/software.                                 Microsoft Excel                  yes      none

                                  Air Services Airplane. Based on FIRST transactions
                                  and reprots form Air Services of flight hours when
                                  flight hours are the depreciable units. Note:
                                  Inhouse engine overhoauls are not an original asset
                                  object code. Costs associated with the overhaul is
   505 Excel Worksheet            reallocated to capitalized equipment cost.          Microsoft Excel                  yes      none

                                                                                                                                Manual decentralized
                                                                                                                                entry; yr-end manual
                                  Moveable non-IT assets are recorded using PO,                                                 submission & merger
                                  invoice and voucher information. Straight-line                                                into one database for
   395 Blding Contents - Access   depreciation calculated at year-end.                Microsoft Access                 Yes      CAFR reporting.
                                                                                                                                Manual entry;
                                                                                                                                automated reformat
                                  Equipment at State Patrol communication tower                                                 for merger/upload to
                                  sites recorded using PO, invoice and voucher                                                  Blding Contents
   395 DSP Tower Sites - Access   information.                                        Microsoft Access                 Yes      database at yr-end.
                                  IT equipment record created from download of
                                  purchasing data (TIPS). Manual entry to adjust &                                             Download from TIPS
                                  complete record. (SAS query produces yr-end                                                  (WisDOT purchasing
       IT - Resource Mgmt         downloads to Excel worksheets for calculation of                                             system) initiates new
   395 System (RMS)               depreciation and CAFR reporting.)                                                  Yes       asset record.
                                                                                      DOA System, RMS is based on Service Center, a product of Peregrine S




     761da32c-2d55-4657-acc4-26e8e8ab5b70.xls                                                                                            5 of 5

				
DOCUMENT INFO
Description: Excel Worksheet for Financial Reporting document sample