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 
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. 
Nonpersonal 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: 221220) 

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 


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 followup 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:



Excel 97 example: =IF(I6="C",F6G6,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:



Excel 97 example: =SUM(+G5F6+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:



Excel 97 example: =IF(K6="P", (L5F6), IF(K6="V",(L5F6+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: 221220 

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 
AMOUNT: Enter a spreadsheet formula in cell "F6"
(or the appropriate cell on your spreadsheet) equivalent to the following
formula:



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:



Excel 97 example: =(G5F6) 


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.



Excel 97 example: =IF(H6="V",(I5F6),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