Management Report Prep

Each month, the year-to-date data used in the management report is posted here to assist Financial Managers with preparation of their reports.

Visit the OFA training page for a recorded session on management report tips.

When you open the Management Report file in Excel, the dashboard automatically opens (providing you clicked “Enable Content” or answered any security questions). At any time, if you close the dashboard and want to return to it, simply click the button on the “FM Pivot” sheet of the Excel workbook.

The following is a step-by-step process for entering basic projections:

  1. If this is the first time the file has been used since downloading from the email, save the file to the location of your choice using the button
  2. Select the Financial Manager from the drop-down menu in the top frame of the dashboard
    Note: Each dlevel has only one designated financial manager, but there may be more than one person responsible for providing projections within a unit. It is highly recommended (but not required) to have a separate file for each person to avoid any confusion. One file is shareable with multiple people, but only one person can use it (and save to it) at any given time.
  3. Select aDLEVEL from the drop-down menu
    1. If the dlevel is already filled in, this is the only dlevel for which the financial manager is responsible
    2. If the dlevel area is blank, there are multiple dlevels for the selected financial manager and you must select a dlevel to enter projections
  4. Select a Fund Type from the drop-down menu
    1. If it is already populated, there is only one fund type for this dlevel
    2. If the fund type area is blank, there are multiple fund types for the selected financial manager and you must select a fund type to enter projections
  5. Select an Appropriation from the drop-down menu
    1. If it is already populated, there is only one appropriation for this dlevel
    2. If the appropriation area is blank, there are multiple appropriations for the selected financial manager and you must select an appropriation to enter projections.
  6. Fund # may remain blank for all fund types other than F3, F7, FE and FL
  7. Unless Fund Type F3, F7,FE or FL is selected, you can enter projections in the Projection column (see the Auxiliary, Recharge, Enterprise & Leasing Funds section for more information on projecting F3, F7,FE & FL fund types).
    1. Any SBS Code with a current year Budget, YTD or Encum activity requires you to enter a projection (even if it is “0”)
      Note: Only the Fund Type must have projections at each SBS Code with activity (except for Auxiliary, Recharge, Enterprise & Leasing funds). However, if desired, you may select each fund from the Fund # drop-down menu and enter projections for each particular fund. See the Fund-Level Projections section for more information. For each dlevel, you must enter projections either at the Fund Type level or the Fund # level.
    2. When entering projections, you may tab through the Projection column or press the "Enter," down or up keys on your keyboard
    3. As soon as a value is entered or changed, the "Projections Saved" button will change to "Write Projections to File"
    4. Totals will automatically re-calculate as you leave each projection box
  8. Click the "Write Projections to File" button after you finish entering projections for that Financial Manager, DLEVEL, Fund Type, Allocation & Fund #
    Note: If any line shows activity in the Budget, YTD or Encum fields but does not have a projection entered, a "Missing Projection" pop-up message box will tell you which lines are missing projection entries. You are not able to save the projections until you enter a projection for each line (may enter a “0”).
    1. If the "Missing Projection" pop-up message does not display, the "Write Projections to File" button changes to "Projections Saved" indicating that projections were successfully written to a separate sheet and the entire file has been saved
    2. If the file is closed or another level is selected, the projections entered for that Financial Manager, DLEVEL, Fund Type, Allocation & Fund # combination will remain in place until they are changed and saved again
    3. In most instances, Total YE Proj should equal Budget for SBS Codes ‘10030 Gen Fund - Match’ and ‘10040 Gen Fund - State’ (the (Favorable) Unfavorable field should show “0”)
      If these revenue codes are not balanced to “0”, you will receive a notification requiring you to either correct your projection(s) or enter a comment to explain the variance (see the Additional Controls section for help with this control)
  9. Continue through each dlevel, Fund Type and Allocation found in the drop-down lists until all projections are entered
  10. Checkthe Projected Year-End totals at the bottom of the dashboard after completing each screen to ensure they accurately reflect youranticipated fiscal year-end status
    1. Total Projected Year-End Budget Surplus / (Deficit) is calculated by taking Favorable (Unfavorable) Expenditures, less (Favorable) Unfavorable Revenue)
    2. Total Projected Year-End UFB / (Deficit) is calculated by taking the Total YE Proj Revenue, less Total YE Proj Expenditures
    3. In most instances the Total Projected Yearend Budget Surplus / (Deficit) are the same as the Total Projected Year-End UFB/(Deficit); if they are not the same, it is because your budget is out of balance
      Note: The projection checklist will show you a list of all Dlevel/Fund Type/Fund# combinations requiring entry of projections. You may click the "Projection Checklist"  button at any time to view your progress (see the Additional Controls section for more information).
  11. When all items are crossed off the projection checklist, the file is complete and you can email it to the Budget & Cost Records team as an attachment

Changes were made in FY14 to prevent double-entries at the fund level and fund type level. For instance, the Financial Manager used to be able to enter projections for a particular restricted fund and then move up a level to the fund type and enter more projections for F2 as a whole. This created major problems and had to be addressed.

  • The user now has the ability to project either at the fund type OR the fund # level, but not both
  • Once a projection is entered at either level, the other is not available for entry
    • This only affects the particular dlevel with which you are currently working
    • You can project each dlevel at a different level (fund or fund type) than another dlevel
  • Projections entered at the fund type level are not seen if you drill-down in to the fund # level
  • It is impossible to assign an overall Fund Type projection to specific fund numbers, so keep this in mind when deciding which level will work best for that dlevel

If you decide to project at the fund number level, you do not need to enter projections in each and every fund. If a restricted fund is no longer active and just needs closing, simply disregard that fund and move on to another one that is actively in use.

Take care to review each and every fund to ensure that projections are entered for all active funds.

These types of funds (F3, F7, FE and FL) require projections at the fund # level. In addition, the DLEVEL field will automatically clear or gray-out when selecting any of these fund types. This was intentionally put in place so the Fund Balances would reflect the most accurate figures, as many units have these types of funds spread out among multiple dlevels.

Projections follow the same process as other fund types except for the fact that each auxiliary, recharge, enterprise or leasing fund number must contain projections (unlike restricted funds). If you do not have projections for a fund number for whatever reason, include that information as a comment.


Depreciation Entries

A mechanism is built into the dashboard to allow entry of both sides of a depreciation projection at once, starting with the recharge fund.

Once a projection is entered in 78000 - Miscellaneous on any recharge fund and the "Write Projections to File" button is clicked, the user is asked if the projection includes depreciation.

If yes, the Depreciation - Reserve Entry window will open where you will select the reserve fund number from the drop-down menu and enter the depreciation amount to complete the reserve fund side of the depreciation entry. Remember, you must use a negative value for the reserve depreciation entry.

  • The projection amount entered in 78000 - Miscellaneous on any recharge fund should equal the total of the projected depreciation plus any other projected 78000 - Miscellaneous expenditures.
  • If the appropriate reserve fund is not shown in the Depreciation - Reserve Entry drop-down menu, email the Budget & Cost Records team to have this information added to the file prior to completing and submitting your report.
  • If the reserve fund does have existing activity and needs additional projections as well as depreciation, complete the reserve fund before the recharge.
    • Leave out the depreciation on the reserve and complete any other entries before moving on to the recharge.
    • This is where you will enter the depreciation for the reserve.

Note: Currently the reserve fund drop-down returns ALL reserve funds associated with the selected financial manager without regard for fund type (F7, FE, FL). Take care when projecting depreciation to select a reserve fund of the same fund type as the recharge/enterprise fund related to the depreciation.


Equipment

Currently, there is not a mechanism in place to handle equipment projections to an F1 that does not yet have activity.

If you need to project the expenditure and revenue for recharge activity equipment and the appropriate F1 is not included in the drop-down menu, email the Budget & Cost Records team to have this information added to the file prior to completing and submitting your report.

SBS Codes Reference List

You can use the SBS Codes Reference List to locate appropriate SBS codes for use in entering projections and allows multiple search options.

  • SBS Search by Acct presents a list of expenditure and revenue account codes; scroll through the list and select the account code you want to project to and the appropriate SBS code is shown.
  • You may also select the SBS code in question from the drop-down list in either the Expenditures or Revenue tab to view all of the account codes that are included in that SBS code’s category.

Save/Save As

Clicking "Save" or "Save As" will save your file to its current location or to a new one. It WILL NOT save any changes to projections that you currently have on the screen.

The only way to save projections (and write them to the file) is to click the "View Projections to File" button. If the "Projections Saved" button is visible, all projections at the currently selected level are written to the file and saved.


Print

Selecting the "Print" button will display everything on the current screen in a spreadsheet format. The print dialog box will automatically open and you can select the printer, print to a pdf or cancel the action altogether.

When you are done, the Dashboard is displayed again with the same information as you left it.


Close

Clicking the "Close" button will close both the Dashboard and the workbook in the background. It will prompt you to save if any changes were made.

Click the red "X" on the top-right of the Dashboard to just close the Dashboard window.


See Your Data

If you want to see exactly what the Dashboard is accessing, selecting the "See Your Data" button will automatically filter the table on the “FM Pivot” tab to the Financial Manager that is selected in the Dashboard.

You can manipulate this table in any way you like since the Dashboard does not depend on this tab. The FM Pivot is there for your use, so feel free to play around with it.

Click the "Back to Dashboard" button to bring the Dashboard back in to view.


Projection Checklist

This tool allows you to keep track of which dlevels and fund types are projected and essentially crosses those fund types off the list.

If you have auxiliary, recharge, enterprise or leasing funds, those fund types are displayed under the dlevels with the actual fund number listed and crossed off (if projections are complete). If a fund # still needs projections, the fund type of that fund will remain in white.

For reference purposes, the boxes next to each fund type will contain an “X” to indicate that comments are entered.


Add/Edit Comments

At any time you can enter projections, you can also enter comments. The comments window will display the current levels selected, and existing comments will populate in the text box.

Clicking "Save" in the Comments window will write the comments to a spreadsheet but will not save the file. If you close the file without saving (either using the save buttons on the main Dashboard or saving projections), the comments are lost.

You can view comments written to the file by selecting the “Comments” tab. Please do not alter this worksheet in any way. You can print this sheet for reference purposes, but it is mainly there to quickly see important information regarding that particular file.

Keep in mind that the "Print" button will also display any comments you have made at that particular level.


Known Issues

  • A fund or dlevel that does not have any current activity will not populate in the dashboard. If projections are necessary, email the Budget & Cost Records team to have this information added to the file.
  • When entering depreciation projections, the reserve fund drop-down list will display ALL reserve funds associated with your unit without regard to fund type. Take care to select the reserve fund that is the same fund type as the recharge/enterprise fund related to the depreciation.
  • The file will not work on Apple products. Without a Windows emulator in place, a Mac cannot access the controls that exist within the Dashboard. Currently, there is no plan for a Mac-compatible version.
  • Different displays may cut off some of the information in the Dashboard. Clicking the scroll-bar and dragging down should allow you to see all of the sections of the window.

 

Downloadable PDF: Management Report Dashboard

University of Alaska Accounting and Administrative Manual

  • A-20 – Management Reporting
  • A-15 – Calculation of Lapse of State Appropriation
  • A-16 – Carryforward Accounting

Verify budgetary reporting requirements are met

  • Total budgeted expenditures must equal total budgeted revenues for all current funds (unrestricted and restricted) at the allocation level / dlevel
  • Total actual expenditures may not exceed total actual revenues (including unreserved fund balance) at the allocation level / dlevel
  • Total actual expenditures may not exceed total budgeted expenditures at the allocation level / dlevel
  • Total personal service expenditures may not exceed total personal services budget at the allocation level / dlevel
  • Actual revenue by individual revenue source may not exceed budgeted revenue by individual revenue source at the allocation level / dlevel

Downloadable PowerPoint: Management Reporting Training

The expanded data files summarize the management report data at the org/acct code levels and also include grant codes which are not included in the management report file. These files are provided as a reference tool to facilitate management report preparation at the dlevel/SBS code levels as provided in the management report file each month. 

Note: All links download an Excel file.

 

The Budget & Cost Records team is happy to answer questions and help navigate management report preparation including understanding the provided data files.