budget and expense tracking

Budget and Expense Tracking

Spreadsheet Training Module

By: Douglas Alan MasurySr. Financial Analyst – Mission College

Budget and Expense Tracking

Where is my money?

Welcome

Flex-day, Fall 2010 3

Do you have a personal checkbook?

• The Budget and Expense spreadsheet is exactly like having an electronic checkbook.

• You will be able to easily see where your money was spent and your available balances.

• Can I really make that next purchase?


Why do I need to use this?

• Without it, you will not know how much money you have left in your fund account to

make that next purchase.

• With it, you will know how you have spent your money, how much is left, AND whether

you will have enough to make that NEXT purchase.


Doesn’t Datatel tell me that?

• When you incur an expense, you create the necessary paperwork to get it paid [or

reimbursed];• After the appropriate signatures, it is sent

forward to Finance for payment;• It can take up to two weeks for this to show in

Datatel; • How can you effectively know your balance?


Fund Name: Enter Fund name

Fund/Project Manager: Enter Budget AdministratorEnter Datatel account string

Fiscal Grant Year 2011

Object Code Fund Amount [enter

in gross budget amount]

Amt Received [detail from

revenue page]Fund Balance to be

received Total Fund Amount

48651 48899

Your revenue account number may be different. Do not concern yourself with that]

$

- $

- $

- $

- $ -

Total Revenue

$ -

$ -

$ -

$ -

$ -

Enter Object Code

Object Code Description [ this fills in automatically by entering in the object

code]Object

Code Link

Budget [figures comes from

Budget Entry Link page]

Budget Entry Link

Expenditure [comes from object code detail page]

Actual Balance [calculaated

automatically]

Encumbrance [comes from object code detail page]

Balance Available

[calculated automatically]

1 #N/A OLink1 $

- BLink1 $

- $

- $

- $

-

2 #N/A OLink2 $

- BLink2 $

- $

- $

- $

-

3 #N/A OLink3 $

- BLink3 $

- $

- $

- $

-

4 #N/A OLink4 $

- BLink4 $

- $

- $

- $

-

5 #N/A OLink5 $

- BLink5 $

- $

- $

- $

-

Subtotal : $ - $ - $ - $ - $ -

59990 Indirect Costs $ -

$ -

$ -

$ -

Total Expenses : $ - $ - $ - $ - $ -

Here is the tool that will help you manage your fund/grant.


This looks Complicated!!!

• It is not. Everything in this spreadsheet is formula driven, appropriately hyperlinked, and gives you your spending results immediately after your data [budget or expense] entry.

• You will know your budget, expenses, and encumbrance balances all on one summary sheet.


So how do I use it?

•EASY….


Fund Name: My Fund 100

Fund/Project Manager: Me 100-xxxxxx-xxxx-xxxxx-000


Object Code

Fund Amount [enter in gross

budget amount]


revenue page]Fund Balance to

be received Total Fund Amount

48651 48899

Your revenue account number may be different. Do not concern yourself with

that] $

- $

- $

- $

- $ -

Total Revenue

$ -

$ -

$ -

$ -

$ -

Enter Object Code

Object Code Description [ this fills in automatically by entering in the

object code]Object

Code Link

Budget [figures comes from Budget Entry Link

page]

Budget Entry Link



automatically]


Balance Available


54110 Instr Supplies OLink1 $

- BLink1 $

- $

- $

- $

-

54210 Non Instr Supplies OLink2 $

- BLink2 $

- $

- $

- $

-

54240 Duplicating OLink3 $

- BLink3 $

- $

- $

- $

-

55200 Travel Conference OLink4 $

- BLink4 $

- $

- $

- $

-

55910 Dues, Memberships, Fees OLink5 $

- BLink5 $

- $

- $

- $

-

Subtotal : $ - $ - $ - $ - $ -


$ -

$ -

$ -

Total Expenses : $ - $ - $ - $ - $ -

Enter in your Fund/Grant Name, followed by your name, and the appropriate Datatel account number


Enter in your “TOTAL” Budget amount..Fund Name: My Fund 100



Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures

comes from Budget Entry

Link page]

Budget Entry Link



automatically]


Balance Available


1 #N/A OLink1 $

- BLink1 $

- $

- $

- $

-

2 #N/A OLink2 $

- BLink2 $

- $

- $

- $

-

3 #N/A OLink3 $

- BLink3 $

- $

- $

- $

-

4 #N/A OLink4 $

- BLink4 $

- $

- $

- $

-

5 #N/A OLink5 $

- BLink5 $

- $

- $

- $

-

Subtotal : $ - $ - $ - $ - $ -


$ -

$ -

$ -

Total Expenses : $ - $ - $ - $ - $ -


Now you need to enter your line item object codes…..

• Why do I need to do that?

• The line item object codes are the buckets where you will be spending your money from.

• They determine HOW you will be spending your money.





Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available


54110 Instr Supplies OLink1 $

- BLink1 $

- $

- $

- $

-


- BLink2 $

- $

- $

- $

-

3 #N/A OLink3 $

- BLink3 $

- $

- $

- $

-

4 #N/A OLink4 $

- BLink4 $

- $

- $

- $

-

5 #N/A OLink5 $

- BLink5 $

- $

- $

- $

-

Subtotal : $ - $ - $ - $ - $ -


$ -

$ -

$ -

Total Expenses : $ - $ - $ - $ - $ -

In the “Enter Object Code” column, enter in the object code for the Budget/Expenses you will incur…


How does the sheet know what to populate the Object code Description with? AND where does it get it from?

• On the Summary Page, located with a start point of cell A150, you will find the list of object codes and their respective descriptions. The formula reads from this list [via a V/H Lookup Table]….


What do I do if the object code I want is not on this list?

• At the bottom of this list are a few blank cells. Enter in the new object code [in column A] and the corresponding description [column B].

• Then highlight the entire list and the descriptions and sort by column A. This will put the new object code in the correct order with the others.

• The V&H Lookup Table formula will now work with this new object code.


Do not adjust this table, you may add object codes at end of table but

do not sort once data entry has begun.

Object Code Table51110 FT Instruct Teach

51130 FT Fac Counselors

51210 Admn/Deans Salaries

51232 FT Academic Non Teach

51310 PT Faculty,Teach

51410 PT Fac Counselors

51410 PT Fac Counselors

51412 PT Fac Non Teach Other

51450 PT Fac Non Teach Stipends

52110 Class FT Sal

52120 FT Mgmt Salaries

52310 Hrly Class Sal

52320 Hrly Class Sal OT

52325 Hrly Class Replacements

52350 Hrly Class Stipends

52410 Hrly Class Instr Aide Sal

52420 Hrly Class Instr Aide Sal OT

52450 Hrly Class Instr Aide Stipend

53100 Mandatory Fringe

53300 Health Benefits

54110 Instr Supplies

54210 Instr Books, Mags, Period

54210 Non Instr Supplies

This is what the list looks like. The

descriptions are brief and give the basics of the object code and what it is used for.

For the most part, there is usually no need to be adding

new object codes as this list comprises almost ALL of the MOST common

object codes used.


A note or two about the Summary page

• The Summary page currently shows room for 5 object codes. This is for this training as in actuality, the sheet allows for 30 object codes.

• How do I activate them? Highlight the lines to the left [between the last object code and the Subtotal line, right click, and press “Unhide”. All 30 will show for usage.

• The spreadsheet allows for 30 individual object codes in total. At the bottom of the Summary Spreadsheet [not shown here] you will see 30 Olinks… each one is hyperlinked to a separate object code line on the Summary Page. Same with the Blinks.

• For those you are not going to use, highlight them, right click on your mouse, and HIDE them. The print setup will only print those that are shown on the screen.



Fund/Project Manager: Me 100-xxxxxx-xxxx-xxxxx-000 Fiscal Grant Year 2011

Object Code Fund Amount [enter in gross budget amount] Amt Received [detail from revenue

page] Fund Balance to be received Total Fund Amount

48651 48899 Your revenue account number may be different. Do not concern yourself with that]

$ 25,000.00 $ - $ 25,000.00 $ - $ -

Total Revenue $ 25,000.00 $ - $ 25,000.00 $ - $ -

Enter Object Code Object Code Description [ this fills in automatically by entering in the object code] Object Code Link Budget [figures comes from Budget Entry Link page] Budget Entry Link Expenditure [comes from object code

detail page] Actual Balance [calculaated automatically] Encumbrance [comes from object code detail page]

Balance Available [calculated automatically]

54110 Instr Supplies OLink1 $ - BLink1 $ - $ - $ - $ -

54210 Non Instr Supplies OLink2 $ - BLink2 $ - $ - $ - $ -

3 #N/A OLink3 $ - BLink3 $ - $ - $ - $ -

4 #N/A OLink4 $ - BLink4 $ - $ - $ - $ -

5 #N/A OLink5 $ - BLink5 $ - $ - $ - $ -

6 #N/A OLink6 $ - BLink6 $ - $ - $ - $ -

7 #N/A OLink7 $ - BLink7 $ - $ - $ - $ -

8 #N/A OLink8 $ - BLink8 $ - $ - $ - $ -

9 #N/A OLink9 $ - BLink9 $ - $ - $ - $ -

10 #N/A OLink10 $ - BLink10 $ - $ - $ - $ -

11 #N/A OLink11 $ - BLink11 $ - $ - $ - $ -

12 #N/A OLink12 $ - BLink12 $ - $ - $ - $ -

13 #N/A OLink13 $ - BLink13 $ - $ - $ - $ -

14 #N/A OLink14 $ - BLink14 $ - $ - $ - $ -

15 #N/A OLink15 $ - BLink15 $ - $ - $ - $ -

16 #N/A OLink16 $ - BLink16 $ - $ - $ - $ -

17 #N/A OLink17 $ - BLink17 $ - $ - $ - $ -

18 #N/A OLink18 $ - BLink18 $ - $ - $ - $ -

19 #N/A OLink19 $ - BLink19 $ - $ - $ - $ -

20 #N/A OLink20 $ - BLink20 $ - $ - $ - $ -

21 #N/A OLink21 $ - BLink21 $ - $ - $ - $ -

22 #N/A OLink22 $ - BLink22 $ - $ - $ - $ -

23 #N/A OLink23 $ - BLink23 $ - $ - $ - $ -

24 #N/A OLink24 $ - BLink24 $ - $ - $ - $ -

25 #N/A OLink25 $ - BLink25 $ - $ - $ - $ -

26 #N/A OLink26 $ - BLink26 $ - $ - $ - $ -

27 #N/A OLink27 $ - BLink27 $ - $ - $ - $ -

28 #N/A OLink28 $ - BLink28 $ - $ - $ - $ -

29 #N/A OLink29 $ - BLink29 $ - $ - $ - $ -

30 #N/A OLink30 $ - BLink30 $ - $ - $ - $ -

Subtotal : $ - $ - $ - $ - $ -

59990 Indirect Costs $ - $ - $ - $ -

Total Expenses : $ - $ - $ - $ - $ -

Here are all 30 Object Code lines….. How many of you need all 30?......


How do I enter my “Line Item Budgets”?

By now, you will have entered in your specific object codes.

For each object code, click on the corresponding “BLink#” and this hyperlink will take you to the Budget Entry Link page.

[and there are 30 BLink#’s too…]


This is where the hyper link takes you….. To the location of the object code selected… Am I in the right place for the object code I want? Yes… as you will

notice at the top : Object Code and then you will see your object code… Budget Transfer Tracking Sheet Summar

y Grant Name My Fund 100

Datatel# 100-xxxxxx-xxxx-xxxxx-000 Object Code 54110

BTRN Ref# [must

Hyperlink]

Budget Transfer Amount

Posted Budget Available

Beginning Budget Amount $ -

$ -

Transfer $ -

Transfer $ -

Transfer $ -

Transfer $ -

$ -

Transfer $ -

$ -

Transfer $ -

$ -

Transfer $ -

$ -

Transfer $ -

$ -

Transfer $ -

$ -

Transfer $ -

$ -

Total Transfers $

- Net

Budget $

-


What am I seeing? This is the subsheet where budget entries are made. By having clicked on the Blink# for your object code, the hyperlink will take you to the appropriate budget section for that object code. Enter in your Beginning Budget

Amount in the GREEN Cell. In this case, $5000. 00Budget Transfer Tracking Sheet Summar

y Grant Name My Fund 100


BTRN Ref# [must

Hyperlink]



Beginning Budget Amount $ 5,000.00

$ 5,000.00

Transfer $ 5,000.00

Transfer $ 5,000.00

Transfer $ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Total Transfers $

- Net

Budget $ 5,000.00


You will also notice that the “Budget Available” field is now populated as the “Net Budget” field is also populated with the current “BUDGET” available.

To the right of the spreadsheet, is a BLUE “ Summary” button. Click on this and you will be taken to the Summary page to see your updated entries.

Budget Transfer Tracking Sheet Summary

Grant Name My Fund 100


BTRN Ref# [must

Hyperlink]



Beginning Budget Amount $ 5,000.00

$ 5,000.00

Transfer $ 5,000.00

Transfer $ 5,000.00

Transfer $ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Transfer $ -

$ 5,000.00

Total Transfers $

- Net

Budget $ 5,000.00


Now you will see your $5000 entry you made on the Budget Entry Sheet appearing on the Summary Sheet. Every entry you make on ANY of the subsheets will appear on the Summary Sheet and will automatically

calculate the balance for you. Now we are going to add an EXPENSE and a PURCHASE REQUISITION for the Object Code 54110. You begin by clicking on the OLink1 hyperlink for the Object Code 54110…




Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available


54110 Instr Supplies OLink1 $ 5,000.00 BLink1

$ -

$ 5,000.00

$ -

$ 5,000.00


- BLink2 $

- $

- $

- $

-


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

5,000.00 $ - $ 5,000.00 $ - $

5,000.00


$ -

$ -

$ -

Total Expenses : $

5,000.00 $ - $ 5,000.00 $ - $

5,000.00


As you can see, your BUDGET amount has populated this subsheet as well. On this subsheet for Object Code 54110 [as you see at the top of the sheet], all your entries MUST be made on the yellow lines for the formulas to work. Anything on the white lines will not affect the

formula and that data will not adjust any balances, either expense or budget.54110

Date Name REQ/PO# Allocated Posted Expense Available Balance Summary

5,000.00 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00

$0.00 $0.00 5,000.00

Actual Balance 5,000.00


I have entered a Purchase Requisition for Office Depot in the amount of $500. At this point, no purchases have been made against the Requisition, nor has the Requisition been cleared thru

Purchasing. However, your intended expenditure IS recorded under the “Allocated” column as you are intending to spend this money for supplies. After the entry is complete, notice the “Available Balance”

has been updated to reflect this intended expenditure. Also, at the bottom of the “Allocated” column is the balance of the outstanding Encumbrances. Click on the Summary to return you to the Summary

Page…

54110


5,000.00

Office Depot 113961 $500.00

4,500.00

4,500.00

4,500.00

4,500.00

4,500.00

4,500.00

4,500.00

4,500.00

4,500.00 $500.00 $0.00 4,500.00



What do you notice?Your Purchase Requisition is now showing on the Summary page and has affected the Balance Available for the

Object Code 54110. [As always, do not forget to Save your work..] Now we will enter in an expense that you have used a District Check Request to pay. Click on the OLink1 Hyperlinked button to take us to the expense sheet for

54110.




Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ -

$ 5,000.00

$ 500.00

$ 4,500.00


- BLink2 $

- $

- $

- $

-


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

5,000.00 $ - $ 5,000.00 $ 500.00 $

4,500.00


$ -

$ -

$ -

Total Expenses : $

5,000.00 $ - $ 5,000.00 $ 500.00 $

4,500.00


You will notice I entered in an expense reimbursement for a Kinko’s purchase on the yellow line. Under “Name”, I entered the expense name, meaning: who is the Payee. Under the expense column on the very same line, I have

entered the amount of the expense reimbursement. Notice the “Available Balance” has been affected by this reimbursement. Also, look at the bottom of the “Expense” column and you will see the total of your expenses to

date. Click on the Summary button to return to the Summary Page.

54110


5,000.00 Office Depot 113961 $500.00 4,500.00 Mr/s Budget Administrator $27.55

Reimbursement for Kinko's copies 4,472.45

4,472.45 4,472.45 4,472.45 4,472.45 4,472.45 4,472.45 4,472.45

$500.00 $27.55 4,472.45



You will now see the Expenditure Column has your entry in it and the Budget Available balances have been updated with the new balances.

Let us continue with the entry of an Office Depot invoice against the Purchase Requisition [now Purchase Order] and see how this works. Click on OLink1…




Object Code


budget amount]




48651 48899


that]

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ 27.55

$ 4,972.45

$ 500.00

$ 4,472.45


- BLink2 $

- $

- $

- $

-


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

5,000.00 $ 27.55 $ 4,972.45 $ 500.00 $

4,472.45


$ -

$ -

$ -

Total Expenses : $

5,000.00 $ 27.55 $ 4,972.45 $ 500.00 $

4,472.45


You received an invoice for items purchased at Office Depot in the amount of $125.67. We need to record the invoice as an expense [see Expense column] AND as a reduction to the Purchase order in the “Allocated” column. This is a double

sided entry as the purchase, in this case, also changes the balance of the Purchase Order. Review the bottom of each column to see your new balances. ALSO, look at the “Available Balance” column. It remains the same. WHY? Because we are adjusting the Purchase Order downward and increasing the Expense upward. They both cancel each other out. We

are making a purchase against an Allocation/Encumbrance. Return to the Summary page to see your results.

54110


5,000.00


4,500.00

Mr/s Budget Administrator $27.55


Office Depot B19056 -$125.67 $125.67

4,472.45

4,472.45

4,472.45

4,472.45

4,472.45

4,472.45

4,472.45 $374.33 $153.22 4,472.45



You will now see your Expenditure total AND your Encumbrance totals have changed. In addition, your Actual Balance AND your Budget Available has also been updated

with the new entry.




Object Code


budget amount]




48651 48899


that]

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ 153.22

$ 4,846.78

$ 374.33

$ 4,472.45


- BLink2 $

- $

- $

- $

-


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

5,000.00 $ 153.22 $ 4,846.78 $ 374.33 $

4,472.45


$ -

$ -

$ -

Total Expenses : $

5,000.00 $ 153.22 $ 4,846.78 $ 374.33 $

4,472.45


West Valley-Mission Community College District W.E.D. Tracking # BTRN 11-xxx

FINANCE OFFICE BUDGET CHANGE/TRANSFER

Account Key Object Description Current

BudgetRevised Budget

Difference = Zero

$0.00

$0.00

$0.00

$0.00

$0.00

$0.00 Totals: $0.00 $0.00 $0.00

Date The budget administrators who have signature authority for the account(s) must sign below. Approved__________________________________ Date ____________________ Ext._________

Budget Administrator Approved__________________________________ Date ____________________ Ext._________

Budget Administrator Approved__________________________________ Date ____________________ Ext._________

College President/Director of Business Services

FOR FINANCE USE ONLY

JOURNAL ENTRY #

TRANSFER APPROVED

BUDGET

TRANSFER # DEBIT CREDIT KEY OBJECT

Budget Technician Date

What is this form? And why do I need to use it?


Budget Transfer form..

• You will need to use this to:


• Make changes to your budget…

• You do not have enough money in an object code to make that next purchase, OR;

• You have overspent an object code….


What does this form do?

• You will need to designate FROM which object code to take money FROM to put into the object code you are needing to increase the budget $$$ amount. From this form, we will know how much $$$ to properly transfer for you.

• But Why do I need to do this?

• If you have overspent an object code, you will need to put it in balance, OR;

• You need to make a purchase from an object code that does not have enough money in it.


West Valley-Mission Community College District

Expense Tracking # ETRN#11-xxx

FINANCE OFFICE EXPENSE TRANSFER

Fund-Cost Center-Tops-Object-

Grant Code Cost Center/ Object Description Amount

$0.00 FROM: $0.00 $0.00

Totals $0.00 $0.00

TO: $0.00 $0.00 Totals $0.00 Sum to zero $0.00

REASON: Fund/Grant/DepartmentWhy am I doing this Transfer?

Date of Transfer

The budget administrators who have signature authority for the account(s) must sign below.

Approved Date Ext. Budget Administrator (From) Approved Date Ext.

Budget Administrator (To)


JOURNAL ENTRY# Reviewed by: BY: Signature DateOriginal To: Finance Copies To: Retained by "TO" Budget administrator & by "FROM" administrator

This form is used to correct actual expenses only, not budget transfers or encumbrances. Corrections should indicate date and reference of the original transaction. The "from" is the account where the charges currently are shown in the general ledger; the "to" is where they should be charged.

Expense Tracking form…


Expense Tracking Form….

• You will need to use this to:


• Move expenses to the proper object code…

• IF it has been posted erroneously, OR;

• Another fund/grant is taking your expenses per agreement….


What does this form do?

• You will need to designate FROM which account to take the expense FROM to put into the account the expense needs to be moved to. From this form, we will know how much $$$ to properly transfer for you.

• But Why do I need to do this?

• Maybe you placed the wrong object code on the expense payment/ reimbursement, OR;

• You need to transfer the expense to the proper fund/grant.


What do I do with this form AFTER I have created it?

• After the appropriate signatures, it needs to be forwarded to Finance for posting.

• Why?

• It cannot be posted otherwise.

• Then you need to enter it into your Expense Tracking Spreadsheet.

• Why?

• So you can adjust the accounts needed by this transfer. A transfer is money spent or received.


How do I enter this in my spreadsheet?

• First, locate the object code[s] in your spreadsheet that this transfer will be posted to.

• IF you are taking an expense from a different fund, then it will be a one sided entry on your spreadsheet. In this case, it will be a +[plus], added to your expenses.

• IF someone else is taking the expense, it will also be a one sided entry on your spreadsheet. In this case, it will be a –[minus], subtracted from your expenses.





Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ 9.20

$ 4,990.80

$ 374.33

$ 4,616.47

54210 Non Instr Supplies OLink2 $ 5,000.00 BLink2

$ 45.78

$ 4,954.22

$ -

$ 4,954.22


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

10,000.00 $ 54.98 $ 9,945.02 $ 374.33 $

9,570.69


$ -

$ -

$ -

Total Expenses : $

10,000.00 $ 54.98 $ 9,945.02 $ 374.33 $

9,570.69

This is the Summary Sheet AFTER the Expense Transfer has been created. The next slide will show you how this came to

be, starting with the Expense Transfer form.


West Valley-Mission Community College District Expense Tracking # ETRN#11-005

FINANCE OFFICE EXPENSE TRANSFER

Fund-Cost Center-Tops-Object-Grant Code Cost Center/ Object Description Amount

$0.00

FROM: 100-xxxxxx-xxxx-54110-000 $45.27

$0.00

Totals $45.27 $0.00

TO: 100-xxxxxx-xxxx-54210-000 $45.27

$0.00

Totals $45.27

Sum to zero $0.00 REASON: My Fund

To post to correct object codetoday's date


Approved Date Ext. Budget Administrator (From)

Approved Date Ext.

Budget Administrator (To)


JOURNAL ENTRY# Reviewed by: BY: Signature Date

Original To: Finance

This form is used to correct actual expenses only, not budget transfers or encumbrances. Corrections should indicate date and reference of the original

transaction. The "from" is the account where the charges currently are shown in the general ledger; the "to" is where they should be charged.

This is the completed Expense Transfer form. This will need to be entered on to your Expense Spreadsheet to update your expenses and see the results before it is posted to

Datatel.


54110


5,000.00 Office Depot 113961 $500.00 4,500.00 Mr/s Budget Administrator $27.55


Office Depot B19056 -$125.67 $125.67 4,472.45 ETRN 11-005 ($45.78) Transferred to 54210 4,518.23 4,518.23 4,518.23 4,518.23 4,518.23 4,518.23

$374.33 $107.44 4,518.23


You will see the Expense Transfer has been entered. In this case, we are crediting [reducing] the expense because we are moving the $45.78 to object code 54210 from 54110 . The next

slide will show the debit [increase] from this transfer.


54210

Date Name PO# Allocated Posted Expense Available Balance Summar

y

5,000.00 ETRN 11-005 $45.78 Transferred to 54210 4,954.22 4,954.22 4,954.22 4,954.22 4,954.22 4,954.22 4,954.22 4,954.22 4,954.22

$0.00 $45.78 5,000.00


In the subsheet for object code 54210, we see the increase of expenses from the Expense Transfer that you have posted.


What do I do if I have used all the lines on the spreadsheet expense page?

• First you will need to add more lines. How do I do this? • Excel is a relational database type program. IF you add lines outside of the

formula definitions, the additional lines will not be included in the formulas, thus the totals will not properly transfer to the Summary Page.

• So, highlight an area of lines on the OLink# page that have no expenses. Then, right click on your mouse and select INSERT. This will insert the number of lines you have highlighted. After that, you MUST remember to cut and paste the formulas appropriately AND to make sure every other line is yellow and every other line is white… All of this MUST be done before the last line of the formula cell definition. [=sum(A6..A23] You would enter in the lines after A6 but before A23 to insure those new lines being added to the formula, thus being carried forth to the Summary Page properly.


The WHITE area is where we added lines.54110


5,000.00


4,500.00



Office Depot B19056 -$125.67 $125.67

4,472.45

ETRN 11-005 ($45.78)

Transferred to 54210 4,518.23

4,518.23

4,518.23

4,518.23

4,518.23

4,518.23 $374.33 $107.44 4,518.23



By Cutting and Pasting, change the WHITE lines to White and Yellow. Then Cut and Past the FORMULA to the White lines to fill in.

54110


5,000.00


4,500.00



Office Depot B19056 -$125.67 $125.67

4,472.45

ETRN 11-005 ($45.78)


4,518.23

4,518.23

4,518.23

4,518.23

4,518.23

4,518.23

4,518.23

4,518.23

4,518.23 $374.33 $107.44 4,518.23



Now when you add an Expense [as an example], the formula will properly self adjust. This will also carry forward to the Summary

sheet as intended.54110


5,000.00


4,500.00



Office Depot B19056 -$125.67 $125.67

4,472.45

ETRN 11-005 ($45.78)


4,518.23

4,518.23

$375.19

4,143.04

4,143.04

4,143.04

4,143.04

4,143.04

4,143.04

4,143.04 $374.33 $482.63 4,143.04



Notice the Balance on object code 54110… It has increased by the $375 expense from the OLink1 subsheet. This means you have

created the additional expense lines correctly. Fund Name: My Fund 100



Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ 482.63

$ 4,517.37

$ 374.33

$ 4,143.04


$ 45.78

$ 4,954.22

$ -

$ 4,954.22


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

10,000.00 $ 528.41 $ 9,471.59 $ 374.33 $

9,097.26


$ -

$ -

$ -

Total Expenses : $

10,000.00 $ 528.41 $ 9,471.59 $ 374.33 $

9,097.26





Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ 9.20

$ 4,990.80

$ 374.33

$ 4,616.47


$ 45.78

$ 4,954.22

$ -

$ 4,954.22


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

10,000.00 $ 54.98 $ 9,945.02 $ 374.33 $

9,570.69


$ -

$ -

$ -

Total Expenses : $

10,000.00 $ 54.98 $ 9,945.02 $ 374.33 $

9,570.69

Now we see the result of the Expense Transfer Entry. IF you were to transfer the expense to someone else’s account, then

you would only have a credit that would be posted to this tracking sheet. Next we will do a Budget Transfer.


Budget Transfer guidelines

• When will it post?

• Why does it have to be Board approved?

• IF you are moving funds from the same object code rollup number [1000 to 1000; 4000 to 4000, etc], then after signatures, it can be posted to Datatel in one day.

• IF you are moving funds from one object code rollup to another [1000 to 2000, 2000 to 4000, etc], then it MUST be Board approved before it can be posted to Datatel. Once approved, then in a few days it can be posted to Datatel.


Budget Transfer guidelines

• What do I do if it cannot be posted right away?

• The purpose of the Expense Tracking spreadsheet is to allow you the foresight for planning so these type of transfers can happen in a more timely manner thus allowing you to make the purchases you are needing in also, a timely manner.

• With the tools presented here today, you can and should be able to plan your spending strategies effectively.

• IF it cannot be posted right away, see the Sr. Financial Analyst who works with your account for assistance.





Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ 9.20

$ 4,990.80

$ 374.33

$ 4,616.47


$ 45.78

$ 4,954.22

$ -

$ 4,954.22


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

10,000.00 $ 54.98 $ 9,945.02 $ 374.33 $

9,570.69


$ -

$ -

$ -

Total Expenses : $

10,000.00 $ 54.98 $ 9,945.02 $ 374.33 $

9,570.69

We are now going to do a Budget Transfer. Here is your CURRENT view of your Expense Tracking Spreadsheet.


West Valley-Mission Community College District W.E.D. Tracking # BTRN 11-xxx

FINANCE OFFICE BUDGET CHANGE/TRANSFER

Account Key Object Description Current Budget Revised Budget Difference = Zero

100-xxxxxx-xxxx-54110-000 $5,000.00 $4,500.00 ($500.00)

100-xxxxxx-xxxx-54210-000 $5,000.00 $5,500.00 $500.00

$0.00

$0.00

$0.00

$0.00

Totals: $10,000.00 $10,000.00 $0.00

My Fund

To move funds to allow for purchase

Today's Date


Approved__________________________________ Date ____________________ Ext._________Budget Administrator

Approved__________________________________ Date ____________________ Ext._________Budget Administrator

Approved__________________________________ Date ____________________ Ext._________College President/Director of Business Services


JOURNAL ENTRY # TRANSFER APPROVED

BUDGET TRANSFER #

DEBIT CREDIT KEY OBJECT

Budget Technician Date

This form is used to revise the current year budget for the General Fund, categorical programs and restricted funds. For transfers among General Fund accounts, the increases must equal the decreases (i.e. the net change in expenditure budgets is zero). For catgorical programs and restricted funds, the balance between revenue and expense accounts must be maintained. This form is also used to

reappropriate funds left unspent at the previous year-end closing. Any modifications to full-time accounts must be accompanied by the Board action corresponding to a reclassification, change in time base or turnover.

Here is our completed Budget Transfer form, moving $500 from 54110 to 54210. In this case,

it will post within one day…





Object Code


budget amount]




48651 48899


that] $

25,000.00 $

- $

25,000.00 $

- $ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ 9.20

$ 4,990.80

$ 374.33

$ 4,616.47


$ 45.78

$ 4,954.22

$ -

$ 4,954.22


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

10,000.00 $ 54.98 $ 9,945.02 $ 374.33 $

9,570.69


$ -

$ -

$ -

Total Expenses : $

10,000.00 $ 54.98 $ 9,945.02 $ 374.33 $

9,570.69

Now we need to enter the Budget Transfer. Since we are starting with 54110, click on the “BLink1” hyperlink to take

you to the Budget Entry Sheet.


Budget Transfer Tracking Sheet Summary

Grant Name My Fund 100

Datatel# 100-xxxxxx-xxxx-xxxxx-000

Object Code 54110

BTRN Ref# [must Hyperlink]

Budget Transfer Amount Posted Budget Available

Beginning Budget Amount $ 5,000.00 $ 5,000.00

Transfer $ (500.00) $ 4,500.00

Transfer $ 4,500.00

Transfer $ 4,500.00

Transfer $ - $ 4,500.00

Transfer $ - $ 4,500.00

Transfer $ - $ 4,500.00

Transfer $ - $ 4,500.00

Transfer $ - $ 4,500.00

Transfer $ - $ 4,500.00

Transfer $ - $ 4,500.00

Total Transfers $ (500.00) Net Budget $ 4,500.00

Object Code 54210

BTRN Ref# [must Hyperlink]

Budget Transfer Amount Posted Budget Available

Beginning Budget Amount $ 5,000.00 $ 5,000.00

Transfer $ 500.00 $ 5,500.00

Transfer $ - $ 5,500.00

Transfer $ - $ 5,500.00

Transfer $ - $ 5,500.00

Transfer $ - $ 5,500.00

Transfer $ - $ 5,500.00

Transfer $ - $ 5,500.00

Transfer $ - $ 5,500.00

Transfer $ - $ 5,500.00

Transfer $ - $ 5,500.00

Total Transfers $ 500.00 Net Budget $ 5,500.00

We are now where we need to be to enter in our Budget Transfer. Since we are reducing 54110’s budget by $500, in the cell under the GREEN Beginning Budget Entry, we enter in -500, and hit the ENTER key. This will reduce our Budget Available.Next, we scroll down to 54210 and following the instructions above, we enter in the +500 to increase the Budget Available.

Every BUDGET Entry MUST be balanced AND can only be transferred within the same fund. No transfers are allowed outside of your fund, meaning: from one fund to another.

Click on the Summary hyperlink to see what your entries have done to your Summary page.





Object Code


budget amount]




48651 48899


that]

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Total Revenue

$ 25,000.00

$ -

$ 25,000.00

$ -

$ -

Enter Object Code


object code]Object

Code Link

Budget [figures


Link page]

Budget Entry Link



automatically]


Balance Available



$ 107.44

$ 4,392.56

$ 374.33

$ 4,018.23


$ 45.78

$ 5,454.22

$ -

$ 5,454.22


- BLink3 $

- $

- $

- $

-


- BLink4 $

- $

- $

- $

-


- BLink5 $

- $

- $

- $

-

Subtotal : $

10,000.00 $ 153.22 $ 9,846.78 $ 374.33 $

9,472.45


$ -

$ -

$ -

Total Expenses : $

10,000.00 $ 153.22 $ 9,846.78 $ 374.33 $

9,472.45

So what has happened? Notice your budget for 54110 and 54210. Object Code 54110 has been decreased, while 54210 has been

increased. Also, your Actual Balance and Balance Available cells for each object have been adjusted by the Budget Transfer and you

now have new balances.


I want to print out my Summary Page. What do I do?

• The Summary page is already set for printing. You do not have to do anything as it will come to you this way whether you use 1 or 30 object codes.

• Just select the PRINT Icon in Excel and the Summary Page will print.


Other things to note about the Expense Tracking Spreadsheet

• For the purposes of this presentation, I have only shown you 5 lines for which to enter in Object Codes.

• There are 30 contained within this sheet. They are hidden.

• To view them, highlight the lines from 19 to and including 71 and right click on your mouse and select Unhide and you will see all 30 line items.


Other things to note about the Expense Tracking Spreadsheet

• You do not have to use all of them. If you have 12 object codes, use only 12. Hide the rest by highlighting the lines you do not wish to use or see, and right click on your mouse and select HIDE.

• Seeing all 30 will not affect the usage of this sheet. However, why print what you do not use? Hiding what you do not need will not show up in your printed Summary page.


An ending note:

• Utilizing the spreadsheet will assist you greatly in knowing at any moment [before Datatel] where you stand financially with your fund/grant.

• With this knowledge, you can effectively strategize and plan your next fiscal move so your department can get those needed purchases in a more timely manner.


An ending note:

• Usage of the spreadsheet will take a little time to get comfortable with. Practice. Save the sheet to a separate file name and play with it. When you are comfortable with its operations, then save a real copy and get started using it.

• And a FINAL Question: – How does this relate to Datatel?


AND here is your answer…

• It does.

• When you view the Summary page, the columns of Budget, Expense, Actual Balance, Encumbrances [Allocations], and Balance Available completely mirror GLBS [the Detail report] in Datatel.


Another part of the answer…

• When you view the Link subsheets for each object code, using the report GLBS [in Datatel], you will see all your Expenses AND Allocations along with the reductions to your specific Purchase Orders and subsequent increases to your expense line items for each PO purchase.


West Valley Mission Community College District8/12/2010 Detail Budget Status Report Page: 1

For Pe riod 07/01/2010 Th ru 06/30/2011

Fiscal Year: 2011 COSTCENTER: 2600 00 - Com. Serv.

GL Account Allocated Revenue/ Unexpended UnencumberedDate Sc Ref.No Description Budget Expenses Balance Encumbrances Balance/Pcnt

-------------------------- ------------------------ -------------- -------------- -------------- -------------- --------------

591-260000-6820-54210-000 Com. Serv. : Non-Instr. Supplies/Materia

Opening Balances --> $ 5,900.00 08/09 PJ V0327358 Office Depot, Inc $ 84.26

07/07 EP B0019321 Office Depot, Inc $ 4,000.00

08/02 EP B0019616 Mission College Bookstore $ 150.00

08/09 EP B0019321 Office Depot, Inc $ (84.26)

Current Period Totals --> $ 84.26 $ 4,065.74

To Date Totals --> $ 5,900.00 $ 84.26 $ 5,815.74 $ 4,065.74 $ 1,750.00 29.66%

Future Totals -->

Fiscal Totals --> $ 5,900.00 $ 84.26 $ 5,815.74 $ 4,065.74 $ 1,750.00 29.66%

================================================

==================

==============

================

=================

===============

Totals for COSTCENTER: 2 60000 - Com. Serv.

To Date Totals --> $ 5,900.00 $ 84.26 $ 5,815.74 $ 4,065.74 $ 1,750.00

This is an example of the GLBS Detailed Datatel Report


Another note to be aware of…• This spreadsheet should be used for only one [1]

fund/costcenter. IF you have multiple funds/costcenters, use multiple copies of the spreadsheet as co-mingling of funds is not allowed.

• Beyond the legality of comingling of funds, it would make it more difficult for you to properly track your monies as to how much $$$ you actually have in the fund[s] you are attempting to track.

• Thus, use a different spreadsheet for each Fund and CostCenter. Name each one by your costcenter and save appropriately.

So how easy is that?

When can I start using it?


IMMEDIATELY…..

• It really is that easy. • And!!!!!!– If you have any questions about using it; see• Doug Masury [Funds 120, 121, 300, 591, 595]• Queenie Chan [Funds 100, 017, 120, 712, 732]

– And we can help you in getting your finances in order.

– Thank you…..

budget and expense tracking

Documents

expense tracking budget

data budget

expense entry

fund account

gross budget amountamt

total revenue

revenue pagefund balance

revenue account number