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

Running the Appropriate Reports

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

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

The most efficient way to run the reports is via the Financial Statement Reports as follows:

  1. Log into the Auxiliary Reporting
  2. Within the Financial Statement Reports, enter the following parameters:
    1. Select the University Fiscal Year and University Fiscal Period Code for which the reports will be generated
    2. Enter the Chart of Accounts 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 Accounts Page (Cover Sheet)” should be selected
    7. Report Style  = Consolidated
    8. Balance Sheets         
      1. Select the boxes for both Include Balance Report and Include Balance Sheet Executive Summary Report
      2. Include Balance Columns = Current Year, Prior Year and July 1 Balance
      3. Select the box for “Sum Net Transfers”

             9. Income Statements

      1. Select box for include Income Statement Object Code
      2.  Include Current Period Data = Thru Current Period
      3. Include Columns = Current Year, Prior Year, and Budget
      4. Select box for Display Materiality
      5. Finally, select “MS Excel” for the output format, and the output destination as “Wait”. Before selecting “Run”, it would be in your best interest to save this report in your parameter sets by clicking the “Save Parameter Settings” button.  Save as “Quarterly Variance Analysis Reports”.  This will eliminate the need to recreate this report every quarter.  Next quarter, go to Financial Statement Reports then Saved Parameter Sets and the only parameters that would need to be changed are the fiscal year and period. 
      6. Select the “Run” button to generate the reports
    1.  

 

 

  1. When the report is done processing, you will get a notification. You can 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. Click OK on the prompt box to open the report

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.