E7: ELECTRONIC ACCOUNT LEDGER
2/8/01


Fiscal Agent Handbook Index | Fiscal Agent Reporting Index

The key to keeping a department budget in balance is the development and maintenance of an effective record keeping system.  One suggested method using an Excel spreadsheet is described below.  While departments are not required to follow the suggested method, they are expected to maintain records of current available balances by line item.  Fiscal agents will be held accountable for remaining within the funds budgeted for their department by line item.  Templates for the Excel spreadsheets described below can not be downloaded using Netscape but can be downloaded using Internet Explorer, Adobe Acrobat Reader* and clicking on the links in the Downloadable Spreadsheets section of the Controller's Office Resource web site.
 

*If you don't have the Acrobat Reader on your computer, you can download it as follows: 

1.  Go to:  http://www.adobe.com
2.  Scroll down the page and click on Get Acrobat Reader.
3.  Again, scroll down the page and click on Get Acrobat Reader.

The templates are also available in the Procedures and Systems Office, E 210.  Please call extension 5148 before you bring a blank disk for your copy.

A separate department ledger sheet should be maintained for each line item. An explanation of how to maintain the department ledger balance follows.

A.

Non-personal services line items

 

 Item

Description

1

If you're using a spreadsheet template file, copy the template file to a new user file and save with a different name.

2

ACCOUNT NAME:  Enter the account name (example: Controller's Office)

3

ACCOUNT NUMBER:  Enter the account number (example:  2-21220)

4

FISCAL YEAR:  Enter the fiscal year (example:  FY2001)

5

LINE ITEM:  Enter the line item number (example:  1200)

6

BALANCE:  Enter the original budget allocation for the line item as shown in the department budget.

7

DATE:  Enter the date the document was submitted for processing.

8

PO #:  Enter the number of the transactional document, other than Requisitions and Travel Authorization Request and Encumbrance Forms, submitted for processing. The document types include:

 

Requisition (R numbers)

 

Direct Payment Voucher (D numbers)

 

Travel Authorization (T numbers)

 

Travel Advance (E numbers)

 

Printing Service Request (S numbers)

 

Purchase Order (P numbers)

 

Motor Pool (N numbers)

 

Paper and Supplies (W numbers)

 

Film Rentals (Y numbers)

 

NOTE: Departments will be billed monthly for copier (U numbers), postal (X numbers) and telephone (Z numbers) charges on vouchers.

 

9

REQ #:  Enter the number of the Requisition or the Travel Authorization and Encumbrance Form submitted for processing.

10

VENDOR:  Enter the name of the vendor.

11

DESCRIPTION:  Enter the description of the goods or services to be received through the submission of the request document. This description is useful in any follow-up required with the processing offices and in keeping a record of what types of activities were funded out of the department's budget.

12

COMMITMENT:  Enter the total cost of the goods or services being charged on all expense documents or the amount of budget allocation being increased or decreased on the Budget Transfer form. Enter budget allocation increases as a negative number; i.e., a budget transfer increase of $10.00 would be entered as (10.00).

13

ACTUAL:  Enter the actual cost of the goods or services received as shown on all expense documents and the increase or decrease of budget allocation as shown on the Budget Transfers.

14

ADJUST:  If you are using the spreadsheet template, the formula in this cell will automatically calculate any adjustment between the committed amount and the actual amount and post it to this cell.

 

If you are not using the template, enter the Excel formula in cell "H6" (or the appropriate cell on your spreadsheet) equivalent to the following formula:

 

If the O/C column equals "C" (closed)

 

+     Commitment

 

-     Actual

 

Otherwise, enter "O" (open)

 

 

Excel 97 example:  =IF(I6="C",F6-G6,0)

 

The formula should then be replicated to the other cells in column "H" as needed.

15

O/C:  Enter "O" (open) when a Purchase Order, a Travel Authorization Request and Encumbrance Form or a Budget Transfer is submitted for processing. Change the "O" to "C" (closed) when a final Accounts Payable Voucher or a Travel Voucher has been submitted for processing or a Budget Transfer copy is received from the Office of University Budgets. Enter a "C" when all other expense documents are submitted for processing and when copies of expense documents for services such as mail, copier, telephone are received. 

16

BALANCE:  If you are using the spreadsheet template, the formula in this cell will automatically calculate the account balance and post it to this cell.

 

If you are not using the template, enter the Excel formula in cell "G6" (or the appropriate cell on your spreadsheet) equivalent to the following formula:

 

+ Previous balance

 

- Commitment

 

+ Adjustment

 

 

Excel 97 example:  =SUM(+G5-F6+H6)

 

The formula should then be replicated to the other cells in column "G" as needed.

17

Use the FBMO91 report monthly, to code the REC column as follows: 

 

REC:  Enter "P" (Purchase Order) when a Purchase Order or a Travel Authorization Request and Encumbrance Form is shown on the FBMO91 monthly report as an encumbrance. Change the "P" to a "V" (voucher) when the final Accounts Payable Voucher or Travel Voucher is shown on the FBMO91 as an expenditure. Enter "V" when all other transactions are shown on the FBMO91 report.

 

The total of the FRS BALANCE column will then equal the specific line item total on the FBMO91 report.

18

BALANCE:  If you are using the spreadsheet template, the formula in this cell will automatically calculate the FRS system balance and post it to this cell. If you are not using the template, enter in cell "L6" (or the appropriate cell on your spreadsheet) a spreadsheet formula equivalent to the following formula:

 

If you are not using the template, enter the Excel formula in cell "G6" (or the appropriate cell on your spreadsheet) equivalent to the following formula:

 

If the REC column equals "P" 
+ Previous FRS balance 
- Commitment

 

If the REC column equals "V" 
+ Previous FRS balance 
- Commitment 
+ Adjustment

 

Otherwise, Previous FRS balance

 

 

Excel 97 example:  =IF(K6="P", (L5-F6), IF(K6="V",(L5-F6+H6),L5))

 

The formula should then be replicated to the other cells in column "L" as needed.

 

Thereafter, the formula will automatically calculate the FRS balance and post it in the "L" column.

  Example

 

B.

Student aide or work study line item

 

 Item

Description

1

If you're using a spreadsheet template file, copy the template file to a new user file and save with a different name.

2

ACCOUNT NAME:  Example:  Controller's Office

3

ACCOUNT NUMBER:  Example:  2-21220

4

FISCAL YEAR:  Example:  2001

5

LINE ITEM:  1126

6

BALANCE:  Enter the original budget allocation for the line item equal to the amount contained in the department budget.

7

DATE:  Enter the date the time sheet was submitted or the date of the Budget Transfer being posted.

8

TRANSFER #:  Enter the Budget Transfer number of the document being posted.

9

STUDENT:  Enter the name of the student aide or work study student as shown on the Time Sheet submitted.

10

HOURLY RATE:  Enter the hourly rate of pay being earned by the student.

11

HOURS WORKED:  Enter the number of hours worked by the student as shown on the Time Sheet submitted for processing.

12

AMOUNTEnter a spreadsheet formula in cell "F6" (or the appropriate cell on your spreadsheet) equivalent to the following formula:

 

Hourly Rate X Hours Worked

 

 

Excel 97 example:  =PRODUCT(D6*E6)

 

The formula should then be replicated to the other cells in column "F" as needed.

 

Thereafter, the formula will automatically calculate the amount being earned.

13

BALANCE:  If you are using the spreadsheet template, the formula in this cell will automatically calculate the account balance and post it to this cell.

 

If you are not using the template, enter in cell "G6" (or the appropriate cell on your spreadsheet) a spreadsheet formula equivalent to the following formula:

 

+ Previous balance

 

- Amount

 

 

Excel 97 example:  =(G5-F6)

 

The formula should then be replicated to the other cells in column "G" as needed.

 

Thereafter, the formula will automatically calculate the account BALANCE and post it in the "G" column.

14

REC:  Enter "V" when the payment or transfer is shown on the FBMO91 monthly report.

15

BALANCE:  Enter the Excel formula in cell "I6" (or the appropriate cell on your spreadsheet) equivalent to the following formula.

 

If the REC column equals "V" 
+ Previous FRS balance 
- Amount

 

Otherwise, Previous Balance

 

 

Excel 97 example: =IF(H6="V",(I5-F6),I5)

 

This formula should then be replicated to the other cells in column "I" as needed.

 

Thereafter, the formula will automatically calculate the FRS balance.

 

Use the FBMO91 report monthly, to post the codes in the REC column as described in item 14 . The total of the FRS BALANCE column will then equal the student aide or work study line item total on the FBMO91 report.

  Example:


For more information contact
Controller's Office