1 / 65

Budget and Expense Tracking

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?.

aelwen
Télécharger la présentation

Budget and Expense Tracking

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Budget and Expense Tracking Spreadsheet Training Module By: Douglas Alan Masury Sr. Financial Analyst – Mission College

  2. Welcome Budget and Expense Tracking Where is my money?

  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? Flex-day, Fall 2010

  4. 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

  5. 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

  6. Here is the tool that will help you manage your fund/grant. Flex-day, Fall 2010

  7. 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

  8. So how do I use it? EASY…. Flex-day, Fall 2010

  9. Enter in your Fund/Grant Name, followed by your name, and the appropriate Datatel account number Flex-day, Fall 2010

  10. Enter in your “TOTAL” Budget amount.. Flex-day, Fall 2010

  11. 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

  12. In the “Enter Object Code” column, enter in the object code for the Budget/Expenses you will incur… Flex-day, Fall 2010

  13. 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

  14. 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

  15. 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. Flex-day, Fall 2010

  16. 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

  17. Here are all 30 Object Code lines….. How many of you need all 30?...... Flex-day, Fall 2010

  18. 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

  19. 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… Flex-day, Fall 2010

  20. 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 Flex-day, Fall 2010

  21. 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. Flex-day, Fall 2010

  22. 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… Flex-day, Fall 2010

  23. 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. Flex-day, Fall 2010

  24. 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… Flex-day, Fall 2010

  25. 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. Flex-day, Fall 2010

  26. 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. Flex-day, Fall 2010

  27. 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… Flex-day, Fall 2010

  28. 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. Flex-day, Fall 2010

  29. 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. Flex-day, Fall 2010

  30. What is this form? And why do I need to use it? Flex-day, Fall 2010

  31. Budget Transfer form.. • You will need to use this to: • Why do I need to do that? • 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…. Flex-day, Fall 2010

  32. 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. Flex-day, Fall 2010

  33. Expense Tracking form… 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. Flex-day, Fall 2010

  34. Expense Tracking Form…. • You will need to use this to: • Why do I need to do that? • Move expenses to the proper object code… • IF it has been posted erroneously, OR; • Another fund/grant is taking your expenses per agreement…. Flex-day, Fall 2010

  35. 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

  36. 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. Flex-day, Fall 2010

  37. 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

  38. 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. Flex-day, Fall 2010

  39. 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. 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. Flex-day, Fall 2010

  40. 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. Flex-day, Fall 2010

  41. In the subsheet for object code 54210, we see the increase of expenses from the Expense Transfer that you have posted. Flex-day, Fall 2010

  42. 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

  43. The WHITE area is where we added lines. Flex-day, Fall 2010

  44. 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. Flex-day, Fall 2010

  45. 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. Flex-day, Fall 2010

  46. Notice the Balance on object code 54110… It has increased by the $375 expense from the OLink1subsheet. This means you have created the additional expense lines correctly. Flex-day, Fall 2010

  47. 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. Flex-day, Fall 2010

  48. 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. Flex-day, Fall 2010

  49. 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

  50. We are now going to do a Budget Transfer. Here is your CURRENT view of your Expense Tracking Spreadsheet. Flex-day, Fall 2010

More Related