Auxiliary Variance Analysis Instructions

Analysis Timeline

The quarterly variance analysis is completed by each reporting auxiliary unit in the month following the end of each fiscal quarter, after auxiliary/accrual documents have been submitted and approved: October, January, April, and July. A reminder is sent at the end of each quarter with pertinent instructions and deadlines guiding analysis submission and response timelines.

The associated ASOP 17.0 is available here

Running the Appropriate Reports

Before beginning the analysis, the following three reports need to be generated via IUIE:

  1. Income Statement - Object Code
  2. Balance Sheet 
  3. Balance Sheet - Executive Summary

The most efficient way to run the reports is via the PDQ Generator as follows:

  1. Log into the IUIE via One.IU
  2. Within the Master Catalog, select Kuali Financial/Auxiliary/Financial Reports/PDQ Generator
  3. Enter the parameters necessary to run the reports for your organization
    1. Select the fiscal year and (ending) fiscal period for which the reports will be generated
    2. Enter the chart code and organization code (account number if applicable to how you have run the PDQ Generator in the past) for your organization
    3. Select the box next to “Include Organization Hierarchy” if there are sub-organizations whose accounts should also be included for reporting purposes. If you are uncertain whether this box should be checked, discuss it with your auxiliary accounting consultant.
    4. “Include Closed Accounts” should be selected
    5. “Highlight Variances” should be selected
    6. “Include Cover Sheet” should be selected
    7. Report Style should have “Consolidated” chosen
    8. Balance Sheets         
      1. Select “Yes” for both Balance Sheet and Balance Sheet Executive Summary Report
      2. Select “Current Year, Prior Year, and July 1 Balance” for Include Balance Columns
      3. Select the box for “Sum Net Transfers”
    9. Income Statements
      1. Select “Yes” for Income Statement Object Code
      2. Select “No” for Income Statement Object Level
      3. Select “No” for Include Income Statement Executive Summary
      4. Select “Thru Current Period” for Include current period data
      5. Select “Current Year, Prior Year, and Budget” for Include Columns
      6. Choose “Yes” for Display Materiality
      7. Finally, select “MS Excel” for the output format, and the output destination as “Send to Completed Reports.” Before selecting “Run”, it would be in your best interest to save this report in your catalog by clicking the “Save Settings” button.  Save as “Quarterly Variance Analysis PDQ Generator”.  This will eliminate the need to recreate this report every quarter.  Next quarter, go to “My Catalog/New Shortcuts” folder and the only parameters that would need to be changed are the fiscal year and period. 
      8. Select the “Run” button to generate the reports

VA instructions image - report parameters.png

 

  1. When the report is done running, you will get an e-mail notification. You can now access the output in two ways:
    1. Click on the link provided in the e-mail, then select either of the two options provided
    2. In IUIE, select My Catalog/Completed Reports (This is where reports come to if any IUIE output destination is selected as ““Send to Completed Reports”). Then select  either of the options provided.

Understanding Reporting Levels

It is important to understand the three reporting levels in the University financial system, consisting of object code, level, and consolidation. An object code is assigned to each financial transaction as it occurs. This is the lowest reporting level. Each object code rolls up or reports to a specific object level. Each object level then reports to a specific consolidation code, which is the highest report level.  An example follows:

VA instructions image - consolidation hierarchy.png

 

For the variance analysis, the object level is used generally to identify variances. 

How to Use the Reports and Complete Variance Analysis

The reports automatically identify the variances for you in different colors, relative to each statement returned.  The variance identification rules used are as follows:

Balance Sheet (Excluding Fund Balance) – Balances that have not changed since the prior year (except Capital Assets and Revolving Fund), or are negative (except Allowances for Bad Debt, Inventory, and Accumulated Depreciation).

Balance Sheet Executive Summary (Excluding Fund Balance) – Variances to prior year greater than 30% AND $10,000.

Income Statement – Object Level variances to budget and/or prior year greater than or equal to the calculated materiality for the organization will require detailed explanations. The BIRT Income Statement calculates materiality for you by taking actual year-to-date Total Revenue and multiplying it by 10%. The minimum materiality threshold is $25,000 and the maximum is $500,000.

Balance Sheet

Red highlight = Negative object code balance

Blue highlight = Unchanged object code balance

Green highlight = Material variance in object level

Income Statement

Green highlight = Material variance in object level

Note – Materiality for the Income Statement has been automatically calculated for you at the end of the statement

  1. Please explain any variances identified in the three statements:
    1. Select the first blank cell to the right of the highlighted cell
    2. Add your explanation(s) in this cell
    3. Please resize the column width and row height so that your whole explanation is visible in this cell
  2. At the beginning of the statements, in the large blank cell to the right of the report parameters, please also include information on any activity out of the ordinary related to Total Revenue, Net Income, Total Assets, and Total Liabilities. Examples of such activity could be – an accrual entry was missed, income was not deferred as planned, expenses that were not included in the budget etc.  More examples could be changes in business strategy, significant new clients / developments expected in the future, macro-economic effects on your business etc.  A consultant may pose follow-up questions in this regard.
  3. When your spreadsheet is completed, save it in .XLS or .XLSX format (use the ‘Save As’ option instead of ‘Save’) and e-mail it to the Auxiliary Accounting mailbox - auxacct@iu.edu

If you are uncertain who your consultant is or have questions about the process, please refer to the Auxiliary Accounting consultants list.

NOTE: Please make sure the explanations given on your variance analysis are complete (the more detail you supply, the better).  Quantify the analysis whenever possible and explain not only what occurred, but why. If your consultant contacts you for additional information, it is in your best interest to provide them with the information they need as quickly as possible.

Appropriate Variance Explanations

The explanation of variances should be as detailed as possible. Suitable explanations will provide details of WHY the variance occurred. 

  • If the variance is mainly related to a transaction(s), then please indicate the relevant amount(s), and/or other details specific to the transaction
  • If the variance is related to a change in business conditions/operations, then please note the change and quantify the effect, if possible
  • If the variance was due to an accounting error, please provide detail for the related transactions and KFS documents

If explanations are not sufficient, you will receive follow-up questions from the consultant.

 

Examples:

Accounts Receivable

Actual Prior Year Variance
$400,000 $325,000 $75,000

Explanation - In the fiscal year, we began doing business with ABC Company. On 06/30, this company had a $63,500 invoice outstanding.

Accounts Payable

Actual Prior Year Variance
$250,000 $500,000 ($250,000)

 Explanation – We purchased a $200,000 machine in May FYXX, the invoice for which was not paid until FYXX.

Sales and Services

Actual Prior Year Variance
$50,000 $40,000 $10,000

 ExplanationX%increase in occupancy across campus in room & board.  Increase in meal plan rates of x%. 

Supplies and Expense

Actual Prior Year Variance
$2,500,000 $3,000,000 ($500,000)

 Explanation – Received unexpected discounts of 15% on supplies purchased for X program.