Excel Instructions
|
Excel Instructions |
|
Excel
Accounting Programs – Tips Important!!!! Do not change the names of any of the sheets (tabs at the bottom) within the files. However, you can rename the excel file from Purchase Requisition to whatever name you wish. To create a new folder
Adding New Account Codes If you need to add new
expenditure or revenue codes, you need to add them to the BudCodes sheet. This
sheet should match your Lawson budget codes. Make sure the accounts are
only listed once on the list on the “BudCodes” sheet. Otherwise, your totals
will not be correct. Transaction Sheet Budget Code When you need to manually enter a transaction, you can go to the blank cell at the bottom of the last account number and use the drop down arrow to select your account number. This will bring up a drop down list so you can choose the account number off the list instead of keying it in. This list reads the account numbers on your BudCodes sheet. You can also copy the number from above or just manually key it in. If you like typing in numbers and dashes, you can just type in the account number. However, this method allows you to mistype and possibly make account mistakes. If you use the Requisition sheet to post your transactions, it will automatically enter the budget code.
Important, there must not be any blank account numbers in your account number column. If you ever blank an account number, the cell will turn RED letting you know that there is a problem. When you use the Requisition sheet to post a requisition, the program goes to cell A3 and then goes down until it encounters a blank line. That is where it posts the next transaction. If you have to delete a transaction for some reason to make a correction, then I suggest that you leave the account number and just zero out the money on the transaction. Otherwise, it may write over some existing data and cause problems.
NOTE: Do not delete or insert any rows or columns on any of the sheets.
Dept If you choose to do department
accounting, the next column is for Department numbers. The Department number
can be numbers or alpha letters or a combination of both. Notes This is a transaction description field and can be whatever information you wish. Req# This field is for your
Requisition number. You can manually enter it or if you use the Requisition
sheet to post your transactions, it will automatically enter the Requisition
number. Vendor Enter the Vendor name.
This field is for the Purchase
Order number. Enter the number in this field. You can enter it as PO-1234 or just
1234 or however you wish to track it. RQ44 screen in Lawson will assist in
tracking purchase orders. Invoice# You can enter the invoice number when entering a payment or a petty cash reimbursement.
Check# You can enter the check number when entering your petty cash reimbursements.
Receipt# You can enter the receipt number for tracking any reimbursements to your budget codes or for posting revenue in your activity fund excel sheet.
Date Enter the transaction date. If you use the Requisition sheet to post your transactions, it will automatically enter today’s date. If you type in the date, you can enter the month, a dash or slash, and then the day. It will fill in the year for you. (Month-Day)
Budget / Revenue This column contains your beginning Lawson budget for your campus allocations. Use this column to post any Budget Amendments. Regular plus numbers increase your budget and minus red numbers reduce your budget.
On your Activity Fund accounting sheet, the Revenue column is used to post the amount of your deposits. Activity and Agency funds do not have budgets. However, you can use the department numbers to track the balances of your clubs or other areas of concern.
Encumbered This field is for your Requisition/Purchase Order encumbrance.
Payments This field is for your payment amount. If you enter a payment and there is a Purchase Order number in the PO# field on the Transaction sheet in that row, then it will also enter a minus amount equal to the payment amount in the Encumbered column. It will not liquidate more than the outstanding amount of the purchase order.
NOTE: If you are paying an amount lower than the amount of the Purchase Order and you need to close out the purchase order, you will need to manually enter the liquidation amount as a minus in the Encumbered column. This will keep your books balance with your Lawson reports. As another method of paying purchase orders, you could also go back to the original transaction for the purchase order and enter your payment on that line. If you do so and need to close out the purchase order, then enter a zero in the Encumbered column so that it will close out the PO. I do not recommend this method.
Balance This balance is calculated for you by subtracting the encumbrances and payments from the budget column.
On row 1, above the Budget, Encumbered, Payments, and Balance, the program will maintain running totals for each of the columns. Thus, the Balance amount on row 1 is the total remaining balance.
Dept Budget This is new for Department Accounting. To set up your department budgets, go to the Transaction sheet. Enter zero for the budget code, enter your department number, and then enter the department budget in the new column titled “Dept Budget” only.
Dept Balance This is new and is used only if you do Department Accounting. The column for Dept Balance will be calculated for you when you filter by department on the Transaction sheet. Further instructions on this will be coming later on.
Missing Accounts on BudCodes If you enter an account number on your Transaction sheet that is not on your BudCodes sheet, then it will display #N/A in this column. This is a reminder that you have an account error or just need to add the new account to the BudCodes sheet.
BudCodes Sheet
You should not have to do anything to this sheet except add new account numbers and re-sort the data. To resort the data, select cell A3 and click on the Sort Ascending button.
PO#s Sheet
You can enter blanket or regular purchase order numbers and it will give you the current balances and payments.
All Purchase Orders
This sheet is a pivot table. Do not type or change anything on this sheet. It will automatically track all of your purchase orders. It will automatically refresh the balances when you initially open the excel program. To update the balances after entering transactions, you will need to right click on the table and then left click on Refresh Data.
Vendor Sheet If you list vendor names here, it will look them up on the “Transaction” sheet and display the Purchase Order balance and amount paid to that vendor. You must be very careful with the spelling. The vendor field on the Requisition sheet will also use this list of vendors in the drop down window to make it easier to input the vendor name. You can enter the vendor name only or you can enter a multi-line name and address. Type the first line and press the enter key when finished. To enter a multiple lines, enter the first line and then hold the ALT key and then press enter key. You can then enter the second line. Hold the ALT key and press enter to go to the next line. When you are finished, press the enter key.
Dept# Sheet If you wish to do department accounting, this sheet will keep up to date department balances. Department numbers can be alpha or numeric and be from 1 to 3 digits. If you choose to use department numbers, you can list them here and the current balances will be displayed. If you want to do department accounting, then you will need to set up your department numbers and names on this sheet. Do not enter any money on this sheet. To set up your department budgets, go to the Transaction sheet. Enter zero for the budget code, enter your department number, and then enter the department budget in the new column titled “Dept Budget” only. The following column for Dept Balance will be calculated for you when you filter by department on the Transaction sheet. Further instructions on this will be coming later on.
Prog Sheet This sheet will display the balances for the various program codes.
Function Sheet This sheet will display the balances for the various function codes. This sheet will give you a good idea when you are getting close to overspending a function and need to do a budget amendment.
Version Sheet At the beginning of the school year, I sent most of the campuses and departments their excel accounting program in the most current version of the program. This sheet basically recaps the various changes and updates to the program.
|
Send mail to mailto:financewebmaster@pasadenaisd.org with questions or comments about this web site.
|
