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.

For organization purposes, I suggest that you create a separate folder for your excel accounting files.  You may want to name the folder “2007-2008” or something like that so that you can keep your accounting years from getting confused.  If you use a server at your school, I suggest that you keep the files on the server and not the “C” Drive on the PC in your office.  That way your files are backed up every night and not lost in case of a computer crash.  If you choose to keep the files on your “C” Drive, then I suggest that you back them up daily. 

To create a new folder

  1. Open Windows Explorer. (To open Windows Explorer, click Start, point to Programs, point to Accessories, and then click Windows Explorer.  If you are running Windows 98, click Start, point to Programs, , and then click Windows Explorer)
  2. Click the drive or folder in which you want to create a new folder.
  3. On the File menu, point to New, and then click Folder.
  4. Type a name for the new folder, and then press ENTER.

 

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.

PO#

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.

 

excel_1

  

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.