Scope Variance Analysis Template by ynh65887

VIEWS: 360 PAGES: 62

More Info
									Financial Reporting Training



                  Office of the Controller
Agenda
1. Accounting Review

2. What is a Budget and General Budget Concepts

3. nVision Financial Reports:
    • Define nVision
    • Variance Analysis
    • Available Budget

4. Budget Inquiries & Budget Status Report

5. Overview of some useful Queries

6. Drill down

7. Running Queries & nVision in the
     Reporting Database

                                             Office of the Controller
       What is Accounting?

Accounting is a systematic way to identify, measure and communicate
  the economic impact of events, such as activities that generate
  inflows and outflows.

Accounting is a self-balancing process which measures:
  1.   Financial Activity: REVENUES and EXPENSES for a period
          Income = Revenues – Expenses
                                       AND
  2.   Financial Position: BALANCES on a cumulative basis at a certain date
         Positive balances which will provide future benefits are ASSETS
         Negative balances which represent future obligations are LIABILITIES




                                                        Office of the Controller
     What is Accounting?


Example of Economic Event:

FIU provides educational services to students.

1.   The University has the right to collect fees from students (Revenues) but
     must also pay faculty to render the services (Expenses).
                                         AND
2. The University collects the cash from the student and pays cash to the
   professor, resulting in a net positive cash (Asset) balance at the end of the
   period




                                                          Office of the Controller
    Why “fund” accounting

           Activities are segregated by funding source
Each fund :
   is Restricted in its uses by statute
   is Allocated a Budget that cannot be overspent

Currently, within each fund, activities are further segregated by
  Organizations and Departments that identify which unit/college is
  responsible for performing the activity and the specific nature of the
  activity (e.g. College of Business puts on the FIU Online Conference).


      http://finance.fiu.edu/controller/Docs/FundRulesMatrix.xls



                                                         Office of the Controller
Which funds does FIU have?

   E&G – Educational and General
   AUX – Auxiliary Enterprises
   C&G – Contracts and Grants
   Agencies
   FA /FinAid – Financial Aid/ Scholarships
   Student-Related Activities
   Concessions




                                               Office of the Controller
 Which Entities does FIU have ?

 Entities affiliated to the University:
         FIU01 – Florida International University
         FIU02 - FIU Foundation: administers donations for the purpose
          of the advancement of FIU. Considered a Non-for-profit entity,
          not a governmental entity.
         FIU03 – Wolfsonian Inc.
         FIU04 - FIU Research Foundation
         FIU05 – FIU Athletic Finance Corp
         FIU06 – FIU Health Care Network
         FIU07 – Foundation Real Estate Investments




                                                     Office of the Controller
     Revenues = Money coming in

Operating Revenues are generated from providing goods or services in
   the normal course of operations to customers.
Non-Operating Revenues are for incidental activities, such as investment
   earnings.
In fund accounting, revenues are recognized when measurable and
   available to spend, the earlier of:
   When collected in cash using cash basis (e.g. conference registration fees collected
    now for an event to occur in the future)
                                            OR
   When earned using accrual method (e.g. student registers for classes now but tuition
    payment is not due to University until later)
NOTE: Revenues which have not been collected are classified as receivables until
  they are collected in cash (e.g. the University has a receivable from a student
  until his/her student account is paid to the University).


                                                              Office of the Controller
     Expenditures = Money being spent

Costs incurred in efforts to generate revenues, classified as:
    Expenses if the payment provides immediate benefits (e.g. salaries, travel)
    Capital if the payment provides long-term benefits (e.g. tangible assets
    with longer lives such as equipment).
NOTE: In fund accounting, all expenditures for goods and services are recorded in
  the period that they occur using accrual method (e.g. salaries are accrued for
  the days between the last pay period date and month-end).
At the operating fund level, expenditures are treated as expenses and
  those subject to capitalization are accounted for in another fund.
Expenses which have not been paid are classified as payable until they
  are paid in cash (e.g. invoices from vendors with certain number of
  days to pay).



                                                        Office of the Controller
    Sources of Revenues and Expenses
Revenues:
     Student Financials (tuition, other student fees)
     Cashiers (cash/credit card deposits)
Expenses
     Payroll (salaries and benefits actual and accruals)
     Voucher (operating and capital expenses)
     Commodity Card (operating expenses)
     Interdepartmental charges with no approval required (phone, printing,
     overhead)
     Interdepartmental charges with approval required (facilities
     maintenance)
In both cases, re-classes of revenues and expenses from one
department to another or from one account to another should be
requested via an Inter-Departmental Transfer Form.


                                                         Office of the Controller
 Income and Financial Position

Remember the results of accounting?
1. Income measures net operations earned during the period
              Income = Revenues –Expenses
2. Fund Balance is the net financial position at the end of the
   period. It represents what resources you have left to spend
   in the short-term:
         Cash + Receivables –Payables = Fund Balance
              Beginning Fund Balance
              + Income
              + Cash Coming In
               – Cash Going Out
              = Ending Fund Balance
                                            Office of the Controller
          Example
College creates a new program to begin 7/13/xxxx

                                                                       Assets                 -   Liabilities =      Net Assets
                                                                Cash       + Receivable       -    Payable       =   Fund Balance
                    Balance at 6/1/xxxx                     $         -       $           -       $          -       $            -
Start-up funding from another department                    $    10,000                                              $       10,000 Cash In
26 Students register for classes $2000 each due 7/1                           $    52,000                            $       52,000 Revenue
New Program Manager is paid for period 6/1 - 6/14           $    (2,520)                                             $      (2,520) Expense
Purchase of laptop for new PM                               $    (1,200)                                             $      (1,200) Expense
PM is paid for period 6/15 - 6/28                           $    (2,520)                                             $      (2,520) Expense
Payroll accrues 2 days in June 6/29 - 6/30                                                        $     (360)        $        (360) Expense
                   Balance at 6/30/xxxx                     $      3,760      $     52,000        $     (360)        $       55,400
Student pays for classes                                    $    52,000       $   (52,000)                           $            -
PM is paid for period 6/29- 7/12                            $    (2,520)                                             $      (2,520) Expense
Reverse accrual for 2 days in June, liability paid                                                $       360        $          360 Expense
Buy food for students for July, payment not due until 8/5                                         $   (4,000)        $      (4,000) Expense
PM paid for period 7/13 - 7/27                              $    (2,520)                                             $      (2,520) Expense
Faculty paid for period 7/13 - 7/27                         $    (3,880)                                             $      (3,880) Expense
PM Payroll accrues 4 days in July 7/28 - 7/31                                                     $     (720)        $        (720) Expense
Faculty Payroll accrues 4 days in July 7/28 - 7/31                                                $   (1,109)        $      (1,109) Expense
Pay back start-up funding                                   $   (10,000)                                             $     (10,000) Cash Out
                    Balance at 7/1/xxxx                     $     36,840      $           -       $   (5,829)        $       31,011




                                                                                                  Office of the Controller
Example (Continued)                                                             $ 2520
                                             Tuition           $2520            $ 2520
                                            26* $2000          $2520            $ 720
                                                               $ 360            $ 1109
                               June         July               $5400            $ 3880
Beginning Fund Balance     $       -    $   55,400                              $ (360)
Revenues                   $   52,000                                           $10389
Expenses
   Salaries and Benefits   $    5,400   $   10,389
                                                                         Food $4000
   Operating Expenses      $        -   $    4,000
   Other Capital Outlay    $    1,200   $        -
Total Operating Expenses   $    6,600   $   14,389   Laptop $1200

  Income (Loss)            $   45,400   $   41,011      Departmental
                                                            Loan
Transfer In                $   10,000
Transfer Out                            $ (10,000)                    Loan Payback
Ending Fund Balance        $   55,400   $ 31,011




                                                         Office of the Controller
Agenda
1. Accounting Review

2. What is a Budget and General Budget Concepts

3. nVision Financial Reports:
    • Define nVision
    • Variance Analysis
    • Available Budget

4. Budget Inquiries & Budget Status Report

5. Overview of some useful Queries

6. Drill down

7. Running Queries & nVision in the
     Reporting Database

                                             Office of the Controller
What is a Budget ?


 Management plan for meeting probable
  revenues and expenses for a given period of
  time
 Serves as a control mechanism to match
  anticipated and actual revenues and
  expenditures




                                 Office of the Controller
    Budget Transfers
Budget Transfers – This is used to transfer Budget Dollars from one
   Department or Project ID to another. Departments can initiate
   a transfer between accounts as needed, while Projects are
   requested through OSRA. Because the PeopleSoft System
   “Tracks” Budgets, transfers between accounts are not
   necessary, however if a transaction becomes stuck in the
   “Budget Check” process within the system, this will correct
   the problem.




                                               Office of the Controller
Transfers
         Transfers Out are not Expenses
                        AND
          Transfers In are not Revenues
                      Department “A”            Department “B”
Cash Transfer              CASH                      CASH

                  Departmental Expense “A” Departmental Expense “B”
Budget Transfer           BUDGET                  EXPENSE
      OR              Department “A”            Department “B”
Budget Transfer           BUDGET                  EXPENSE

                      Departmental “A”         Departmental “B”
ID Transfer              EXPENSE                  EXPENSE



                                                 Office of the Controller
Budgetary Accounts Categories used at FIU
 Category                   Examples
                             •   Sales of Goods or Services
 Revenue                     •   Rental Income
                             •   Sponsorship Revenues
 Permanent Salaries and      •   Faculty, Staff, Administrative Salaries
 Benefits                    •   Benefits
                             •   Temporary Employees
 Other Personal Services
                             •   Student and Graduate Assistants
 (OPS)
                             •   Adjunct Faculty
                             •   Travel Expenses
 Other Operating Expenses    •   Office Supplies
                             •   Repair and Maintenance
                             •   Personal Computers
 Operating Capital Outlay
                             •   Furniture
 (Assets ≥ $5000)
                             •   Repair and Maintenance
                             •   Transfers to Construction
 Cash Transfers In & Out
                             •   Start-up Costs for other departments


                                                            Office of the Controller
Budget Cycle for FIU

                             Prior Year

   Preliminary     Implementation          Review                 Budget
      Phase             Phase               Phase                 Posted
 July – February   February – April       April – May           End of June



                           Current Year

                   Monitoring Phase
                     July –June




                                                  Office of the Controller
Agenda Update
1. Accounting Review

2. What is a Budget and General Budget Concepts

3. nVision Financial Reports:
    • Define nVision
    • Variance Analysis
    • Available Budget

4. Budget Inquiries & Budget Status Report

5. Overview of some useful Queries

6. Drill down

7. Running Queries & nVision in the Reporting Database


                                             Office of the Controller
  The nVision reporting tool
   Provides real-time financial reporting capability.
   Extracts requested financial data from PeopleSoft.
   Places it into an Excel spreadsheet where it can easily
    be reviewed and manipulated.

                                    •Keyed to Business Unit
                   Report           •Includes saved parameters
Template or
 style sheet
                  Request
                                        Rules for generating reports
                                           for slices of the data


         Layout             Scope

                                               Office of the Controller
            Reporting nVision – Elements
Microsoft Excel
 Microsoft Excel is the backbone of nVision reporting. nVision facilitates
  the retrieval of information from PeopleSoft databases into a Microsoft
  Excel spreadsheet.
The Layout of the Report
 The Layout of the Report is a Matrix or Tabular Excel spreadsheet.
The Ledger Tables
 Layouts retrieve data from a ledger. This could be Actual, Budget or
  Forecast Ledgers. The ledger tables have five major data components:
   Business Unit
   Ledger
   Chartfields (Account, Department and Project ID, Program, Fund, Site or Class.
    Additionally for Projects PC Business Unit and Activity)
   Period and Year
   Net Posted Amount
 Actual transactions and Budget Transactions are stored on two separate
  tables.
   Actuals data is on the LEDGER table
   Budget data is on the LEDGER_KK table                     Office of the Controller
         Reporting nVision – Elements
                  Continued
Time Spans
 Time Spans control the number of data periods extracted from the
  ledger table and summarized.
Trees
 Trees are used to create hierarchical structures that visually
  represent a set of summarization rules for a particular field. The
  summarization rules depicted in a tree apply to the detail values
  of a particular field – departments, accounts, funds, vendors,
  customers or other values the University defines.




                                               Office of the Controller
            Reporting nVision – Variables
Report Variables
 Provide information about
  he current nVision report
  such as the Business Unit,
  Report ID/name, Title,
  Layout Name, Operator ID,
  etc.
Date and Time Variables
 Provide information about
  the Date of the Report, the
  Tree, the Fiscal Year
  Accounting Period, etc.
Scope Variables
 Provide information related
  to the scope definition used
  in the nVision report such as
  the Scope Field Value or
  description (i.e.,
  department id or name)
                                  Office of the Controller
         Reporting nVision – Time Spans

Time Spans
 The Time Span controls the number of data periods extracted
  from the ledger tables and summarized.
 Typical Time spans provided with PeopleSoft General Ledger
  include:
   PER – Period – Retrieves only activity for the period selected (1=July, 2=Aug, etc.)
   YTD – Year to Date – Includes year-to-date amount for items selected.
   BAL – Balance – Retrieves life-to-date amounts (balance forward amounts) for balance
    sheet accounts.
 Time spans are often expressed relative to current period, so that
  they automatically adapt the content of a report to the “As of
  Reporting Date” specified when the report is executed.




                                                              Office of the Controller
               Reporting nVision – Scope
Scope
 The Scope feature allows you to create multiple instances of a
  report using a single report request.

                           PeopleSoft
                            Database




   Single PS                   PS
    nVision                  nVision
    report                   Report                                   Multiple
   Instance                  Layout                                  PS nVision
                                                                       report
                                                                     Instances




                                               Office of the Controller
            Reporting nVision – Scope
                   Continued
Scope
 A Scope can be on one Chartfield and one value as well as many
  Chartfields and any values
 A Scope can be built on single values or tree nodes.
 This is a single value scope:




                                              Office of the Controller
Scope      Reporting nVision – Scope
 This Scope includes Department and Fund Trees:




• This Scope can be copied to another
  and change the fund node to:
  OTHER_UNRESTRIC_FUND
                                             Office of the Controller
Agenda Update
1. Accounting Review

2. What is a Budget and General Budget Concepts

3. nVision Financial Reports:
    • Define nVision
    • Variance Analysis
    • Available Budget

4. Budget Inquiries & Budget Status Report

5. Overview of some useful Queries

6. Drill down

7. Running Queries & nVision in the Reporting Database


                                             Office of the Controller
                               Aggregate
Users                                                             Report
                                 level

                                                              VA
                                               Fund           Report
CFO / BOT

                                                                 E&G and
                                         Fund
                                                                 Non E&G:
                                    Exec Area
OFP                                                              AS report*
                                                                   Non Athletics:
                                    Fund
                                                                   AS report
                             College/Unit
                                                                   Athletics:
EABM                                                               AI Report

                    DeptID                                              E&G : DI report
ABM                                                                     Non E&G: XA report
        *A modified version of the AS showing subtotals per EA and College is being tested




                                                                          Office of the Controller
          Reports Inventory: Variance Analysis
                     The VA Report
                   The Columns come from Ledgers and use Time Spans



The Rows come
from Chartfields




                                                   Office of the Controller
          Reports Inventory: Variance Analysis
                     The AS report
                       Variance Categories:
Perm. Salaries   OPS   OCO      OOE     Total Expenses        Revenue




                                                   Office of the Controller
Reports Inventory: Variance Analysis
           The XA report




                             Office of the Controller
Reports Inventory: Variance Analysis
           The DI report




                             Office of the Controller
                        nVision Financial Reports
                     Variance (Budget vs. Actual) Reports
Type of Report          Description of the Report                                      Before       Now

Detail Transaction      Detail transaction data for committed (enc & pre)/ paid
Reports                 expenses as well as budget by Dept ID
                                                                                         DT         DT



                        Detail variance by account with a subtotal by account
                        rollups ( S&B, OCO,OPS, OOE) for a single Dept ID
                                                                                          DI
                                                                                                    DI
                        Detail variance by account with a subtotal by account
                        rollups ( S&B, OCO,OPS, OOE) for a single E&G Dept ID
                                                                                         DO

Summary Reports         Detail Inflow (revenue, fund balance & transfers in) and
                        Outflow (Expenses and transfers out) variance with               XA         XA
                        subtotals account and account rollups

                        Detail Inflow (revenue, fund balance & transfers in) and
                        Outflow (Expenses and transfers out) variance with                AI        AI
                        subtotals account and account rollups for an Athletics dept




                                                                         Office of the Controller
                         nVision Financial Reports
                  Variance (Budget vs. Actual) Reports
Type of Report           Description of the Report                                      Before       Now
                         Actual and budget information for Salaries & Benefits for a
                         range of Dept ID with a subtotal by Dept ID and funding          ES
                         source
                         Actual and budget information for Other Capital Outlay for
                         a range of Dept ID with a subtotal by Dept ID and funding        EC
                         source
                         Actual and budget information for Other Personnel
                         Services for a range of Dept ID with a subtotal by Dept ID       EO
                         and funding source
Executive Summary                                                                                    AS*
                         Actual and budget information for Other Operating
                         Expenses for a range of Dept ID with a subtotal by Dept ID       EE
                         and funding source

                         Actual and budget information for all expenses for a range
                         of Dept ID with a subtotal by Dept ID and funding source
                                                                                          DS

                         Actual and budget information for revenues for a range of
                         Dept ID with a subtotal by Dept ID and funding source
                                                                                          AS

*Note: The revenue section is collapsed



                                                                          Office of the Controller
Agenda Update
1. Accounting Review

2. What is a Budget and General Budget Concepts

3. nVision Financial Reports:
    • Define nVision
    • Variance Analysis
    • Available Budget

4. Budget Inquiries & Budget Status Report

5. Overview of some useful Queries

6. Drill down

7. Running Queries & nVision in the Reporting Database


                                             Office of the Controller
       Reports Inventory: Available Balance
Audience         Aggregate              Report
                   level

                                      DS
                               Fund   Report
CFO

                                       E&G and
                          Fund
                                       Non E&G:
                     Exec Area
OFP                                    DS Report

                       Fund              E&G and
                College/Unit             Non E&G:
                                         DS Report
EABM

            DeptID                       Budget Overview
ABM                                      Budget Status Report



                                         Office of the Controller
Reports Inventory: Available Balance
             DS Report




                             Office of the Controller
Reports Inventory: Available Balance
  DS Report Budget Reconciliation




                             Office of the Controller
Reports Inventory: Available Balance
             ES Report




                             Office of the Controller
Reports Inventory: The DT report
                       nVision Financial Reports
                                 Available Budget
                                                                                            Budget
Detail Budget status Report Available Balance detail by Budget account DI
                                                                                            Status*

Salaries Executive Summary     Salaries & Benefits Available Balance                 ES       ES

OCO Executive Summary          Other Capital Outlay Available Balance                EC       EC

OPS Executive summary          Other Personnel Services Available Balance            EO       EO

OOE Executive Summary          Other Operating Expenses Available Balance            EE       EE


Executive Summary              Exec Sum by Dept                                      DS       DS

*Note: The Budget status report is NOT an nVision report. A tutorial is available online (UPK)
on how to run it at http://panthersoft.fiu.edu/financials/onlinetraining.htm



                                                                 Office of the Controller
Agenda Update
1. Accounting Review

2. What is a Budget and General Budget Concepts

3. nVision Financial Reports:
    • Define nVision
    • Variance Analysis
    • Available Budget

4. Budget Inquiries & Budget Status Report

5. Overview of some useful Queries

6. Drill down

7. Running Queries & nVision in the Reporting Database


                                             Office of the Controller
           Reports Inventory: Available Balance
                  Budget Status Report
Ledger Group              Description
                          Expense budgets, expenses,
CC_DEP (Department Child) encumbrances & pre-encumbrances for
                          departments by detail Budget Account
                          Expense budgets, expenses,
CC_PRD (Department
                          encumbrances & pre-encumbrances for
Parent)
                          departments by Parent Department
                          Budgeted, collected and recognized
CC_REV
                          revenues
                          Expense budgets, expenses,
CC_PG (Project Child)     encumbrances & pre-encumbrances for
                          project by detail Budget Account
                          Expense budgets, expenses,
CC_PRP (Project Parent)   encumbrances & pre-encumbrances for
                          projects by Parent Project ID



                                              Office of the Controller
     On-Line Inquiries - Reports Inventory:
Available Balance Budget Overview Detail View




                                Office of the Controller
     On-Line Inquiries - Reports Inventory:
Available Balance Budget Overview Parent View




                                Office of the Controller
      On-Line Inquiries - Reports Inventory:
Reconciliation Budget Overview: Drill to Activity Log




                                        Office of the Controller
  Reports Inventory: Reconciliation
Budget Overview: Drill to Activity Log




                          Office of the Controller
        SQR Reports Inventory:
Available Balance Budget Status Report




                            Office of the Controller
        SQR Reports Inventory:
Available Balance Budget Status Report




                            Office of the Controller
Agenda Update
1. Accounting Review

2. What is a Budget and General Budget Concepts

3. nVision Financial Reports:
    • Define nVision
    • Variance Analysis
    • Available Budget

4. Budget Inquiries & Budget Status Report

5. Overview of some useful Queries

6. Drill down

7. Running Queries & nVision in the Reporting Database


                                             Office of the Controller
                     Useful Queries


        Query Name                             Description
                               List of employees involved in approving a
REQUISITION_WORKFLOW_ROUTING
                               requisition by Requisition ID
                               Lookup Expense Managers 1 & 2 (Approvers) and
FIU_DEPT_APPROVERS
                               Organizational department by Dept ID
                               Detail transaction data for committed (enc & pre)/
FIU_GL_TRANDATA_BYDATE
                               paid expenses as well as budget by Dept ID

                               Lookup Fund / PCS/ Class associated with a Dept
FIU_GL_DEPT_PROJ_LOOKUP        ID or Org dept/Fund/Class/PCS/PCBU/Activity for
                               a project




                                                    Office of the Controller
Query Data




             Office of the Controller
Tables to Know for Queries




                    Office of the Controller
Agenda Update
1. Accounting Review

2. What is a Budget and General Budget Concepts

3. nVision Financial Reports:
    • Define nVision
    • Variance Analysis
    • Available Budget

4. Budget Inquiries & Budget Status Report

5. Overview of some useful Queries

6. Drill down

7. Running Queries & nVision in the Reporting Database


                                             Office of the Controller
                  Drill Down
 STEP 1 (One time): Follow instructions found at
http://panthersoft.fiu.edu/faculty_downloads.html




                                    Office of the Controller
                    Drill Down
Step 2: Open the nVision report and select the cell you
         would like to get transaction details on




                                       Office of the Controller
          Drill Down
Step 3: Call the drill down Add-in




                             Office of the Controller
         Drill Down
Step 4: Select type of drill down




 1




              2




                            Office of the Controller
     Drill Down
Step 5: Pickup the report




                       Office of the Controller
Any Questions?




                 Office of the Controller

								
To top