This template enables users to record expense claims and automatically calculates expense claim totals by client, employee and expense account. The template can be used by individual employees for recording the expenses that they need to claim or by businesses for the recording & allocation of all employee expense claims. The template also contains an expense claim remittance which can be printed, attached to supporting documents, signed off by employees and authorized for payment by managers.

The template includes the following sheets:
Setup - enter your business name and sales tax percentage on this sheet. A list of error codes is also listed on this sheet for information purposes.
Claims - this sheet can be used to record all expense claims. Individual users can enter all the required information pertaining to their expense claims and businesses can copy the data from the versions that are used by individual employees in order to calculate the expense claim totals for the entire business.
Clients - add client codes and client names for all your clients on this sheet. The expense claim totals are automatically calculated for the user defined date range that is specified in the filter section at the top of the sheet. These calculations can be used to determine the expense claim amounts that need to be charged to clients.
Employees - add the names of all employees on this sheet. The expense claim totals per employee are automatically calculated for the user defined date range that is specified in the filter section at the top of the sheet. These calculations can be used to analyse the expense claims that are submitted by each employee.
Accounts - add expense account codes, account descriptions and general ledger codes on this sheet. The expense claim totals per expense account are automatically calculated for the user defined date range that is specified in the filter section at the top of the sheet. These calculations can be used to calculate expense claim totals per expense account and to record the expense claim entries in your primary accounting system.
Remit - this sheet contains a remittance for expense claims. Only the claim number needs to be entered or copied into cell G4 and the rest of the sheet is populated automatically based on the entries that have been added to the Claims sheet for the specified claim number.

Setup

The business name and sales tax percentage can be entered on the Setup sheet. The business name is used as a heading on all the other sheets and the sales tax percentage is used to calculate the inclusive, exclusive and sales tax amounts.

The default error codes are also listed on this sheet for information purposes. These error codes relate to the user input on the Claims sheet and indicate an error with the user input in the appropriate column and row. Refer to the Error Codes section of these instructions for more guidance on user input errors.

Recording Expense Claims

All expense claims need to be recorded on the Claims sheet. A separate line item needs to be recorded for each amount that is claimed and these line items are then grouped into individual claims by entering the same claim number for all the appropriate line items.

The template can be used for individual employees and for entire businesses. A separate version of the template can be provided to each employee who will then record all of their expense claims by using these separate versions of the template. The data from each employee's version can then be copied into a consolidated version for the business and used to analyse all the expense claims that are paid to employees.

Note: The contents on the Claims sheet have been included in an Excel table. All the columns on the Claims sheet with yellow column headings require user input. The columns with light blue column headings contain formulas that are automatically copied for all new entries that are added to the table. New expense claim line items can be added to the table by simply entering a claim date in the first blank cell in column A - the Excel table will automatically extend to include the new entry.

The Claims sheet contains the following user input columns (columns with yellow column headings):
Claim Date - enter the claim date in this column. The claim date should be the date on which the claim is submitted which will not necessarily be the same date as the supplier invoice date. All line items that form part of the same expense claim should have the same claim date.
Employee - select the employee name from the list box in this column. All employees need to be added to the Employees sheet before being available for selection.
Claim Number - the claim number that needs to be entered in this column should be the same for all line items that form part of the same expense claim. Claim numbers can be in any format but we suggest using a combination of numbers & letters. Each expense claim (which may consist of multiple line items) should have a unique claim number in order for the claim totals to be calculated correctly.
Document Date - enter the supporting document date in this column. Supporting documents can be invoices, cash slips, receipts or any other valid proof that an employee has incurred an expense on behalf of the business.
Document Number - enter the supporting document number in this column (if available).
Supplier - enter the name of the supplier that was paid by the employee in this column.
Description of expense - enter a detailed description of the expense for which the claim is being submitted.
Client Code - select a client code from the list box in this column. Client codes need to be created on the Clients sheet before being available for selection.
Expense Code - select an expense account code from the list box in this column. Expense account codes need to be added to the Accounts sheet before being available for selection.
Sales Tax Code - select a sales tax code from the list box in this column. If the sales tax on an expense can be claimed back from a Revenue authority, select the "A" code. This code only applies if the business is registered for sales tax purposes. If sales tax cannot be claimed, select the "E" code.
Quantity - enter the quantity that is being claimed. For most invoices, a quantity of 1 can be used and the total invoice amount can be entered in the Rate column. Other expense line items may require a quantity to be entered for example where mileage is being claimed which is based on a specific rate per km / mile. The mileage would then be entered in the quantity column and the rate in the next column.
Rate - enter the rate inclusive of sales tax in this column. For most invoices, the total tax inclusive amount needs to be entered in this column. Where the claim amount needs to be based on a quantity which has been entered in the Quantity column, the tax inclusive rate needs to be entered in this column.
Payment Date - enter the payment date of the claim in this column. The same payment date should be entered for all line items that form part of the same claim. The payment date is only used to keep track of which expense claims have been paid. Outstanding claims can therefore be listed by filtering this column for blank values.

The Claims sheet contains the following calculated columns (columns with light blue column headings):
Inclusive Claim Amount - this amount is calculated by multiplying the quantity and rate.
Sales Tax - this amount is calculated based on the sales tax code that has been selected in column J. Sales tax is calculated based on the sales tax percentage which has been specified on the Setup sheet for all "A" tax codes. No sales tax is calculated for all "E" codes. If the business is not registered for sales tax purposes, you can either enter a 0% on the Setup sheet or allocate all expense claim line items to the "E" tax code.
Exclusive Claim Amount - this amount is calculated as the difference between the inclusive and sales tax amounts that are calculated in the previous 2 columns.
Error Code - this column will contain an error code if there is a problem with the input in any of the user input columns. Refer to the Error Codes section of the instructions for more info on the error codes that may be encountered. All error codes must be resolved in order to ensure that the template calculations remain accurate.

Note: If the sales tax percentage that has been entered on the Setup sheet needs to be amended, all the values that have been calculated in the Sales Tax column on or before the date on which the change takes effect need to be copied as values before changing the percentage. These values will then not be affected by the change in sales tax percentage and all new sales tax calculations will be based on the new percentage.

All the column headings on the Claims sheet contain filter selection arrows which indicate that the Filter feature has been activated on this sheet. This feature can be used to filter the data on the sheet based on requirements. Note that the totals above the column headings will also only be based on the filtered records once a filter has been applied to the sheet.

Note: The Filter feature can be used to display the claim line items for any of the individual or combined totals on the Clients, Employees or Accounts sheet by simply applying the same filter criteria as has been applied to the appropriate sheet to the columns on the Claims sheet. This is especially useful if you want to supply a client with the detailed claim entries which make up the total that is charged to the client for any particular period.

Note: The data on the Remit sheet is dependent on the sequence of the first three columns on the Claims sheet. You should therefore not add columns within the first 3 columns or delete any of these columns otherwise the Remit sheet will not reflect accurate results.

Client Charges

The Clients sheet can be used to calculate the expense claim charges that need to be invoiced to each client based on any user defined date range. Simply create a new client code for all client accounts and enter a client name for each client in columns A and B. The columns with light blue column headings contain formulas which need to be copied for all new client codes that are created.

Note: All the client codes that are created on the Clients sheet need to be entered in a continuous cell range otherwise some entries may not be included in the template calculations. You should therefore not insert any blank rows between the rows that contain data.

Client codes can be in any format but we suggest using a combination of letters and numbers for this purpose. We also recommend using an abbreviation of the client name in the client code which will make it easier to select the correct client codes on the Claims sheet. All the client codes that are added on the Clients sheet are automatically included in the Client Code column on the Claims sheet.

Note: A default client code also needs to be created for all expense claim charges that cannot be recharged to your clients (for example "XXX01"). All entries on the Claims sheet need to be assigned to a client code and if you create a specific code for claims that cannot be recovered from clients, the total amount of this exposure will automatically be reflected on the Clients sheet.

Note: The "From" and "To" dates in the filter section at the top of the sheet enables users to compile a report of total expense claims by client for any user defined date range. The dates on which these filters are applied are the claim dates that have been entered in column A on the Claims sheet.

Employees

The Employees sheet can be used to calculate the total expense claim charges that each employee has claimed based on any user defined date range. Simply enter or copy the employee names into column A and the totals in the columns with light blue column headings will be calculated automatically based on the date range that is specified in the filter section at the top of the sheet.

Note: All employee names that are added to the Employees sheet need to be entered in a continuous cell range otherwise some entries may not be included in the template calculations. You should therefore not insert any blank rows between the rows that contain data.

Note: All the employee names that are added to the Employees sheet are automatically included in the list boxes in the Employee column on the Claims sheet.

Note: The "From" and "To" dates in the filter section at the top of the sheet enables users to compile a report of total expense claims by employee for any user defined date range. The dates on which these filters are applied are the claim dates that have been entered in column A on the Claims sheet.

Expense Account Allocation

The Accounts sheet can be used to calculate the total expense claim charges that need to be allocated to each individual expense account based on any user defined date range. Simply enter the expense account code, account description and general ledger code and the totals in the columns with light blue column headings will be calculated automatically based on the date range that is specified in the filter section at the top of the sheet.

Note: All expense accounts that are added to the Accounts sheet need to be entered in a continuous cell range otherwise some entries may not be included in the template calculations. You should therefore not insert any blank rows between the rows that contain data.

Note: All the expense account codes that are added in column A on the Accounts sheet are automatically included in the list boxes in the Expense Code column on the Claims sheet.

Note: The "From" and "To" dates in the filter section at the top of the sheet enables users to compile a report of total expense claims per expense account for any user defined date range. The dates on which these filters are applied are the claim dates that have been entered in column A on the Claims sheet.

The report totals can also be used to record expense claim entries in your primary accounting system by using the general ledger codes which have been specified in column C. We recommend using a single control account as the contra account for these entries.

• The individual expense accounts should be debited with the tax exclusive amounts and the sales tax control account should be debited with the total sales tax amount. The total tax inclusive amount should be credited to the expense claim control account.
• The total payments to employees needs to be debited to the expense claim control account and credited to either a payroll control account (if payments are made through the payroll system) or against the bank account from which the payments are made.
• After processing these entries, the expense claim control account should have a nil balance.
• Note that if expense claims are recharged to clients, the client accounts need to be debited with the total tax inclusive amount for each client, the appropriate expense accounts need to be credited with the tax exclusive amounts and the sales tax control account need to be credited with the sales tax amount.
• If you have created a client code for expense claim amounts which cannot be recharged to clients, this client account needs to be excluded from the client recharge entries. The filter section at the top of the Accounts sheet therefore includes a cell (G2) where this client code can be specified in order to exclude this client account from the calculated totals. If you do not want to exclude any client accounts, cell G2 needs to be left blank.

Expense Claim Remittance

The Remit sheet can be used to compile a printed copy of each expense claim which can be signed by the employee and authorized by a manager. The only user input which is required on this sheet is entering a claim number in cell G3. All the other information on this sheet is populated automatically based on the entries that have been added to the Claims sheet for the specified claim number.

Note: If any of the information on the Remit sheet does not populate correctly, please refer to the Claims sheet and ensure that all the user input columns have been completed correctly as specified in these instructions. Also ensure that no columns have been added within the first 3 columns on the Claims sheet and that no columns have been deleted from the Claims sheet.

Expense claim remittances should be printed and supporting documents should be attached to the printed copies before the claims are signed off by the employee and approved for payment by a manager.

Note: The Remit sheet provides for 20 expense claim line items in the default design. If you require more than 20 line items per expense claim, you can insert the required number of rows above the total row (row 28) and copy the formulas from the last row that contains the default formulas (row 27).

Error Codes

The following error codes may result from inaccurate input on the Claims sheet and will be displayed in the Error Code column. The heading of the affected input column will also be highlighted in orange:

• E1 - this error code means that the employee name that has been selected in column B is invalid. All the employee names that have been added to the Claims sheet will be included in the list boxes in column B and the error can therefore be rectified by simply selecting a valid employee name from the list box. New employee names must be added to the Employees sheet before being available for selection.
• E2 - this error code means that the client code that has been selected in column H is invalid. All the client codes that have been added to the Claims sheet will be included in the list boxes in column H and the error can therefore be rectified by simply selecting a valid client code from the list box. New client codes must be added to the Clients sheet before being available for selection.
• E3 - this error code means that the expense account code that has been selected in column I is invalid. All the expense account codes that have been added to the Accounts sheet will be included in the list boxes in column I and the error can therefore be rectified by simply selecting a valid expense account code from the list box. New expense account codes must be added to the Accounts sheet before being available for selection.

Note: Input errors may result in inaccurate template calculations and it is therefore imperative that all errors are resolved before reviewing the template calculations.