Transcript www.missioncollege.org

Budget and Expense Tracking
Spreadsheet Training
Module
By: Douglas Alan Masury
Sr. Financial Analyst – Mission College
Welcome
Budget and Expense Tracking
Where is my money?
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?
Flex-day, Fall 2010
3
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.
Flex-day, Fall 2010
4
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?
Flex-day, Fall 2010
5
Here is the tool that will help you manage your fund/grant.
Enter Fund name
Fund Name:
Enter Datatel account string
Fund Amount [enter in
gross budget amount]
Object Code
48651 48899
Amt Received [detail from
revenue page]
2011
Fiscal Grant Year
Enter Budget Administrator
Fund/Project Manager:
Fund Balance to be
received
Total Fund Amount
Your revenue account number may be different. Do not concern
yourself with that]
Total Revenue
Enter Object Object Code Description [ this fills in automatically by Object Code
Code
entering in the object code]
Link
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
Budget [figures comes
Expenditure [comes
Budget Entry
from Budget Entry
from object code detail
Link
Link page]
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 :
$
$
-
$
$
$
-
Flex-day, Fall 2010
-
$
-
$
$
-
$
-
$
-
$
$
$
-
-
$
-
6
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.
Flex-day, Fall 2010
7
So how do I use it?
•EASY….
Flex-day, Fall 2010
8
Enter in your Fund/Grant Name, followed by your name, and the
appropriate Datatel account number
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Enter Object
Object Code Description [ this fills in
Code
automatically by entering in the object code]
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
Amt Received [detail
from revenue page]
2011
Fund Balance to be
received
Total Fund Amount
$
-
$
-
$
- $
- $
-
$
-
$
-
$
- $
- $
-
Budget [figures
Expenditure [comes
Object Code
Budget
comes from Budget
from object code
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
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 :
59990 Indirect Costs
Total Expenses :
$
$
-
$
$
$
-
Flex-day, Fall 2010
-
$
-
$
$
-
$
-
$
-
$
$
$
-
-
$
-
9
Enter in your “TOTAL” Budget amount..
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
2011
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
- $
$
-
-
$
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
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 :
$
$
-
$
$
$
-
Flex-day, Fall 2010
-
$
-
$
$
-
$
-
$
-
$
$
$
-
-
$
-
10
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.
Flex-day, Fall 2010
11
In the “Enter Object Code” column, enter in the object code for
the Budget/Expenses you will incur…
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
2011
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
- $
$
-
-
$
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
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
$
-
$
-
$
-
$
-
Subtotal :
59990 Indirect Costs
Total Expenses :
$
$
-
$
$
$
-
Flex-day, Fall 2010
-
$
-
$
$
-
$
-
$
-
$
$
$
-
-
$
-
12
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]….
Flex-day, Fall 2010
13
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.
Flex-day, Fall 2010
14
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.
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 Table
51110
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
Flex-day, Fall 2010
15
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.
Flex-day, Fall 2010
16
Here are all 30 Object Code lines…..
How many of you need all 30?......
My Fund 100
Fund Name:
Me
Fund/Project Manager:
Object Code
48651 48899
100-xxxxxx-xxxx-xxxxx-000
Fund Amount [enter in gross budget amount]
2011
Fiscal Grant Year
Fund Balance to be received
Total Fund Amount
Your revenue account number may be different. Do not concern yourself with that]
Total Revenue
Enter Object Code
Amt Received [detail from revenue page]
$
25,000.00
$
25,000.00
Budget [figures comes from Budget Entry Link page]
$
$
$
25,000.00
$
25,000.00
$
-
$
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
$
-
$
-
$
-
$
59990 Indirect Costs
Total Expenses :
$
$
-
-
$
-
$
-
$
Flex-day, Fall 2010
-
Encumbrance [comes from object code detail page]
-
-
54110
-
Actual Balance [calculaated automatically]
-
$
Object Code Link
$
Expenditure [comes from object code detail page]
$
Object Code Description [ this fills in automatically by entering in the object code]
Subtotal :
Budget Entry Link
-
-
$
-
$
-
$
Balance Available [calculated automatically]
$
-
$
-
-
$
-
$
-
$
-
17
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…]
Flex-day, Fall 2010
18
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
Summary
Grant Name
My Fund 100
Datatel#
100-xxxxxx-xxxx-xxxxx-000
Object Code
54110
BTRN Ref#
[must
Hyperlink]
Budget Transfer
Amount
Beginning Budget Amount $
-
$
$
$
$
$
$
$
-
$
-
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Total Transfers
Posted
Net
Budget
Flex-day, Fall 2010
Budget Available
$
$
$
$
$
$
$
$
$
$
$
-
$
-
19
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. 00
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
Beginning Budget Amount $
5,000.00
$
$
$
$
$
$
$
-
$
-
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Total Transfers
Posted
Net
Budget
Flex-day, Fall 2010
Budget Available
$
$
$
$
$
$
$
$
$
$
$
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
5,000.00
$
5,000.00
20
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
Datatel#
100-xxxxxx-xxxx-xxxxx-000
Object Code
54110
BTRN Ref#
[must
Hyperlink]
Budget Transfer
Amount
Beginning Budget Amount $
5,000.00
$
$
$
$
$
$
$
-
$
-
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Transfer
Total Transfers
Posted
Net
Budget
Flex-day, Fall 2010
Budget Available
$
$
$
$
$
$
$
$
$
$
$
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
5,000.00
$
5,000.00
21
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…
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
2011
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
- $
$
-
-
$
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
54110
Instr Supplies
OLink1
$
5,000.00
BLink1
$
-
$
5,000.00
$
-
$
5,000.00
54210
Non Instr Supplies
OLink2
$
-
BLink2
$
-
$
-
$
-
$
-
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
-
$
-
$
-
55200
Travel Conference
OLink4
$
-
BLink4
$
-
$
-
$
-
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
$
-
$
-
$
-
Subtotal :
59990 Indirect Costs
Total Expenses :
$
$
5,000.00
-
$
$
$
5,000.00
Flex-day, Fall 2010
-
$
-
$
$
5,000.00
$
-
$
5,000.00
$
$
$
-
5,000.00
-
$
5,000.00
22
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
Actual Balance
Flex-day, Fall 2010
5,000.00
5,000.00
23
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
Date
Name
REQ/PO#
Allocated
Posted
Expense
Available Balance Summary
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
Actual Balance
Flex-day, Fall 2010
4,500.00
5,000.00
24
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.
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
Instr Supplies
OLink1
$
5,000.00
BLink1
$
-
$
54210
Non Instr Supplies
OLink2
$
-
BLink2
$
-
$
-
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
$
5,000.00
-
$
$
$
5,000.00
Flex-day, Fall 2010
$
-
$
-
-
$
-
500.00
$
4,500.00
$
-
$
-
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
-
- $
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
54110
Subtotal :
2011
5,000.00 $
$
$
5,000.00
$
500.00
$
5,000.00
$
$
$
500.00
4,500.00
-
$
4,500.00
25
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
Date
Name
REQ/PO#
Allocated
Posted
Expense
Available Balance Summary
5,000.00
Office Depot
113961
$500.00
4,500.00
Mr/s Budget Administrator
Reimbursement for Kinko's
copies
$27.55
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
Actual Balance
Flex-day, Fall 2010
4,472.45
4,972.45
26
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…
My Fund 100
Fund Name:
Me
Fund/Project Manager:
Fund Amount [enter in
gross budget amount]
Object Code
48651 48899
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
Amt Received [detail
from revenue page]
2011
Fund Balance to be
received
Total Fund Amount
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
$
25,000.00
$
25,000.00
$
$
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
Instr Supplies
OLink1
$
5,000.00
BLink1
$
27.55
$
54210
Non Instr Supplies
OLink2
$
-
BLink2
$
-
$
-
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
$
5,000.00
-
$
$
$
5,000.00
Flex-day, Fall 2010
$
27.55
-
$
-
500.00
$
4,472.45
$
-
$
-
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
27.55
-
$
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
54110
Subtotal :
- $
4,972.45 $
$
$
4,972.45
$
500.00
$
4,972.45
$
$
$
500.00
4,472.45
-
$
4,472.45
27
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
Date
Name
REQ/PO#
Allocated
Posted
Expense
Available Balance Summary
5,000.00
Office Depot
113961
$500.00
4,500.00
Mr/s Budget Administrator
$27.55
Reimbursement for Kinko's
copies
Office Depot
4,472.45
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
Flex-day, Fall 2010
$153.22
Actual Balance
4,472.45
4,846.78
28
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.
My Fund 100
Fund Name:
Me
Fund/Project Manager:
Fund Amount [enter in
gross budget amount]
Object Code
48651 48899
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
Amt Received [detail
from revenue page]
2011
Fund Balance to be
received
Total Fund Amount
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
$
25,000.00
$
25,000.00
$
$
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
Instr Supplies
OLink1
$
5,000.00
BLink1
$
153.22
$
54210
Non Instr Supplies
OLink2
$
-
BLink2
$
-
$
-
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
$
5,000.00
-
$
$
$
5,000.00
Flex-day, Fall 2010
$
153.22
-
$
-
374.33
$
4,472.45
$
-
$
-
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
153.22
-
$
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
54110
Subtotal :
- $
4,846.78 $
$
$
4,846.78
$
374.33
$
4,846.78
$
$
$
374.33
4,472.45
-
$
4,472.45
29
What is this form?
And why do I need to use it?
West Valley-Mission Community College District
FINANCE OFFICE
W.E.D. Tracking #
BTRN 11-xxx
BUDGET CHANGE/TRANSFER
Account Key
Object Description
Current Budget
Revised 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 #
DEBIT
CREDIT
TRANSFER
APPROVED
BUDGET
TRANSFER #
KEY
OBJECT
Budget Technician
Flex-day, Fall 2010
Date
30
Budget Transfer form..
• You will need to use this
to:
• Make changes to your
budget…
• Why do I need to do
that?
• You do not have enough
money in an object
code to make that next
purchase, OR;
• You have overspent an
object code….
Flex-day, Fall 2010
31
What does this form do?
• You will need to
• But Why do I need to
designate FROM which
do this?
object code to take
• If you have overspent
money FROM to put
an object code, you will
into the object code you
need to put it in
are needing to increase
balance, OR;
the budget $$$ amount. • You need to make a
From this form, we will
purchase from an
know how much $$$ to
object code that does
properly transfer for
not have enough money
you.
in it.
Flex-day, Fall 2010
32
Expense Tracking form…
West Valley-Mission Community College District
FINANCE OFFICE
Expense
Tracking #
ETRN#11-xxx
EXPENSE TRANSFER
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.
Fund-Cost Center-Tops-ObjectGrant 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
REASON:
$0.00
Fund/Grant/Department
Why am I doing this Transfer?
Date of Transfer
The budget administrators who have signature authority for the account(s) must sign below.
Approv
ed
Date
Ext.
Date
Ext.
Budget Administrator (From)
Approv
ed
Budget Administrator (To)
FOR
FINA
NCE
USE
ONLY
JOURNAL ENTRY#
Reviewed by:
BY:
Signature
Original
To:
Finance
Copies
To:
Retained by "TO" Budget administrator & by "FROM" administrator
Flex-day, Fall 2010
Date
33
Expense Tracking Form….
• You will need to use this
to:
• Move expenses to the
proper object code…
• Why do I need to do
that?
• IF it has been posted
erroneously, OR;
• Another fund/grant is
taking your expenses
per agreement….
Flex-day, Fall 2010
34
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.
Flex-day, Fall 2010
35
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.
• Then you need to enter it
into your Expense
Tracking Spreadsheet.
• Why?
• Why?
• It cannot be posted
otherwise.
• So you can adjust the
accounts needed by this
transfer. A transfer is
money spent or received.
Flex-day, Fall 2010
36
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.
Flex-day, Fall 2010
37
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.
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
OLink1
$
5,000.00
BLink1
$
9.20
$
4,990.80 $
54210
Non Instr Supplies
OLink2
$
5,000.00
BLink2
$
45.78
$
4,954.22
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
10,000.00
-
$
$
$
10,000.00
Flex-day, Fall 2010
$
54.98
$
-
374.33
$
4,616.47
$
-
$
4,954.22
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
54.98
-
-
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
Instr Supplies
$
- $
$
54110
Subtotal :
2011
$
$
9,945.02
$
374.33
$
9,945.02
$
$
$
374.33
9,570.69
-
$
9,570.69
38
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.
Expense Tracking
#
West Valley-Mission Community College District
FINANCE OFFICE
ETRN#11-005
EXPENSE TRANSFER
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.
Fund-Cost Center-Tops-Object-Grant Code
Cost Center/ Object Description
Amount
$0.00
FROM:
100-xxxxxx-xxxx-54110-000
$45.27
Totals
$45.27
100-xxxxxx-xxxx-54210-000
$45.27
Totals
$45.27
$0.00
$0.00
TO:
$0.00
Sum to zero
REASON:
$0.00
My Fund
To post to correct object code
today's date
The budget administrators who have signature authority for the account(s) must sign below.
Approved
Date
Ext.
Date
Ext.
Budget Administrator (From)
Approved
Budget Administrator (To)
FOR
FINANC
E USE
ONLY
JOURNAL ENTRY#
Reviewed by:
BY:
Signature
Date
Original To: Finance
Flex-day, Fall 2010
39
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.
54110
Date
Name
REQ/PO#
Allocated
Posted
Expense
Available Balance Summary
5,000.00
Office Depot
113961
$500.00
4,500.00
Mr/s Budget Administrator
Reimbursement for Kinko's
copies
Office Depot
$27.55
4,472.45
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
Actual Balance
Flex-day, Fall 2010
4,518.23
4,892.56
40
In the subsheet for object code 54210, we see the increase of expenses
from the Expense Transfer that you have posted.
54210
Date
Name
PO#
Allocated
Posted
Expense
Available Balance
Summary
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
Actual Balance
Flex-day, Fall 2010
5,000.00
4,954.22
41
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.
Flex-day, Fall 2010
42
The WHITE area is where we added lines.
54110
Date
Name
REQ/PO#
Allocated
Posted
Expense
Available Balance
Summary
5,000.00
Office Depot
113961
$500.00
4,500.00
Mr/s Budget Administrator
Reimbursement for Kinko's
copies
Office Depot
$27.55
4,472.45
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
Flex-day, Fall 2010
$107.44
4,518.23
Actual Balance
4,892.56
43
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
Date
Name
REQ/PO#
Allocated
Posted
Expense
Available Balance
Summary
5,000.00
Office Depot
113961
$500.00
4,500.00
Mr/s Budget Administrator
Reimbursement for Kinko's
copies
Office Depot
$27.55
4,472.45
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
4,518.23
4,518.23
4,518.23
4,518.23
$374.33
Flex-day, Fall 2010
$107.44
4,518.23
Actual Balance
4,892.56
44
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
Date
Name
REQ/PO#
Allocated
Posted
Expense
Available Balance
Summary
5,000.00
Office Depot
113961
$500.00
4,500.00
Mr/s Budget Administrator
Reimbursement for Kinko's
copies
Office Depot
$27.55
4,472.45
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
$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
Flex-day, Fall 2010
$482.63
4,143.04
Actual Balance
4,517.37
45
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.
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
OLink1
$
5,000.00
BLink1
$
482.63
$
4,517.37 $
54210
Non Instr Supplies
OLink2
$
5,000.00
BLink2
$
45.78
$
4,954.22
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
10,000.00
-
$
$
$
10,000.00
Flex-day, Fall 2010
$
528.41
$
-
374.33
$
4,143.04
$
-
$
4,954.22
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
528.41
-
-
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
Instr Supplies
$
- $
$
54110
Subtotal :
2011
$
$
9,471.59
$
374.33
$
9,471.59
$
$
$
374.33
9,097.26
-
$
9,097.26
46
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.
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
OLink1
$
5,000.00
BLink1
$
9.20
$
4,990.80 $
54210
Non Instr Supplies
OLink2
$
5,000.00
BLink2
$
45.78
$
4,954.22
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
10,000.00
-
$
$
$
10,000.00
Flex-day, Fall 2010
$
54.98
$
-
374.33
$
4,616.47
$
-
$
4,954.22
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
54.98
-
-
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
Instr Supplies
$
- $
$
54110
Subtotal :
2011
$
$
9,945.02
$
374.33
$
9,945.02
$
$
$
374.33
9,570.69
-
$
9,570.69
47
Budget Transfer guidelines
• When will it post?
•
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.
• Why does it have to be
Board approved?
•
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.
Flex-day, Fall 2010
48
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.
Flex-day, Fall 2010
49
We are now going to do a Budget Transfer. Here is your
CURRENT view of your Expense Tracking Spreadsheet.
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
OLink1
$
5,000.00
BLink1
$
9.20
$
4,990.80 $
54210
Non Instr Supplies
OLink2
$
5,000.00
BLink2
$
45.78
$
4,954.22
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
10,000.00
-
$
$
$
10,000.00
Flex-day, Fall 2010
$
54.98
$
-
374.33
$
4,616.47
$
-
$
4,954.22
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
54.98
-
-
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
Instr Supplies
$
- $
$
54110
Subtotal :
2011
$
$
9,945.02
$
374.33
$
9,945.02
$
$
$
374.33
9,570.69
-
$
9,570.69
50
Here is our completed Budget Transfer form,
moving $500 from 54110 to 54210. In this case,
it will post within one day…
West Valley-Mission Community College District
W.E.D. Tracking #
FINANCE OFFICE
BTRN 11-xxx
BUDGET CHANGE/TRANSFER
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.
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
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
Flex-day, Fall 2010
Date
51
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.
My Fund 100
Fund Name:
Fund/Project Manager:
Me
Object Code
Fund Amount [enter in
gross budget amount]
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
OLink1
$
5,000.00
BLink1
$
9.20
$
4,990.80 $
54210
Non Instr Supplies
OLink2
$
5,000.00
BLink2
$
45.78
$
4,954.22
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
10,000.00
-
$
$
$
10,000.00
Flex-day, Fall 2010
$
54.98
$
-
374.33
$
4,616.47
$
-
$
4,954.22
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
54.98
-
-
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
Instr Supplies
$
- $
$
54110
Subtotal :
2011
$
$
9,945.02
$
374.33
$
9,945.02
$
$
$
374.33
9,570.69
-
$
9,570.69
52
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.
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
$
(500.00)
$
4,500.00
Transfer
$
4,500.00
Transfer
$
4,500.00
Transfer
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)
Object Code
Net Budget
$
4,500.00
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
Flex-day, Fall 2010
$
500.00
Net Budget
$
5,500.00
53
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.
My Fund 100
Fund Name:
Me
Fund/Project Manager:
Fund Amount [enter in
gross budget amount]
Object Code
48651 48899
Your revenue account number may be different. Do not
concern yourself with that]
Total
Revenue
Fiscal Grant
Year
100-xxxxxx-xxxx-xxxxx-000
$
25,000.00
$
25,000.00
Amt Received [detail
from revenue page]
$
$
Fund Balance to be
received
Total Fund Amount
-
$
25,000.00 $
-
$
25,000.00
Budget [figures
Expenditure [comes
Enter Object
Object Code Description [ this fills in
Object Code
Budget
comes from Budget
from object code
Code
automatically by entering in the object code]
Link
Entry Link
Entry Link page]
detail page]
Actual Balance
[calculaated
automatically]
OLink1
$
4,500.00
BLink1
$
107.44
$
4,392.56 $
54210
Non Instr Supplies
OLink2
$
5,500.00
BLink2
$
45.78
$
5,454.22
54240
Duplicating
OLink3
$
-
BLink3
$
-
$
55200
Travel Conference
OLink4
$
-
BLink4
$
-
55910
Dues, Memberships, Fees
OLink5
$
-
BLink5
$
-
59990 Indirect Costs
Total Expenses :
$
10,000.00
-
$
$
$
10,000.00
Flex-day, Fall 2010
$
153.22
$
-
374.33
$
4,018.23
$
-
$
5,454.22
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
153.22
-
-
-
Encumbrance
Balance Available
[comes from object
[calculated
code detail page]
automatically]
Instr Supplies
$
- $
$
54110
Subtotal :
2011
$
$
9,846.78
$
374.33
$
9,846.78
$
$
$
374.33
9,472.45
-
$
9,472.45
54
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.
Flex-day, Fall 2010
55
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.
Flex-day, Fall 2010
56
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.
Flex-day, Fall 2010
57
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.
Flex-day, Fall 2010
58
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?
Flex-day, Fall 2010
59
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.
Flex-day, Fall 2010
60
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.
Flex-day, Fall 2010
61
This is an example of the GLBS Detailed Datatel Report
West Valley Mission Community College District
Detail Budget Status Report
For Pe riod 07/01/2010 Th ru 06/30/2011
8/12/2010
Fiscal Year: 2011
GL Account
Date Sc Ref.No
-------------------------591-260000-6820-54210-000
Page: 1
COSTCENTER: 2600 00 - Com. Serv.
Allocated
Budget
--------------
Description
------------------------
Revenue/
Expenses
--------------
Unexpended
Balance
--------------
Encumbrances
--------------
Unencumbered
Balance/Pcnt
--------------
Com. Serv. : Non-Instr. Supplies/Materia
Opening Balances -->
08/09 PJ V0327358 Office Depot, Inc
$
5,900.00
$
84.26
$
5,900.00 $
84.26 $
$
4,000.00
$
150.00
$
(84.26)
$
4,065.74
$
5,815.74 4,065.74
$
5,900.00 $
84.26 $
$
5,815.74 4,065.74
07/07 EP B0019321 Office Depot, Inc
08/02 EP B0019616 Mission College Bookstore
08/09 EP B0019321 Office Depot, Inc
$
Current Period Totals -->
To Date Totals -->
84.26
$
$
1,750.00
29.66%
Future Totals -->
Fiscal Totals -->
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
Flex-day, Fall 2010
62
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.
Flex-day, Fall 2010
63
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…..
Flex-day, Fall 2010
65