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 set up sales tax percentages 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 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 specified in the filter section at the top of the sheet. These calculations can be used to determine the expense claim amounts which 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 specified in the filter section at the top of the sheet. These calculations can be used to analyse the expense claims 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 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 which have been added to the Claims sheet for the specified claim number.
Setup
The business name and sales tax percentages can be entered on the Setup sheet. The business name is used as a heading on all the other sheets and the sales tax percentages are used to calculate the inclusive, exclusive and sales tax amounts.
The sales tax codes in the sales tax section on the Setup sheet are automatically included in the tax code list boxes on the Claims sheet. There are two sales tax code types - one for national or federal sales tax and one for state sales tax. The codes created on the Setup sheet are included in both sales tax code list boxes and the sales tax percentages are automatically applied based on the sales tax codes selected for each claim entry.
Note: You can add an unlimited number of additional sales tax codes by inserting a new row above the end of list entry, entering the new sales tax code and specifying the appropriate sales tax percentage. If sales tax percentages change, you can therefore simply create a new sales tax code with the new sales tax percentage and use the new code for all transactions after the effective date of the rate change.
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 which 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 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 which are automatically copied for all new entries 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 entered in this column should be the same for all line items which 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 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.
Tax 1 Code - select a tax code from the list box. All the sales tax codes which have been created on the Setup sheet will be available for selection and sales tax percentages are calculated based on the selected tax code. If your business is not registered for sales tax purposes, all transactions should be recorded by using the E tax code. The first sales tax code is for national or federal sales tax and should therefore be applicable in most countries.
Tax 2 Code - select a tax code from the list box. All the sales tax codes which have been created on the Setup sheet will be available for selection and sales tax percentages are calculated based on the selected tax code. If your business is not registered for sales tax purposes, all transactions should be recorded by using the E tax code. The second sales tax code is for state sales tax - if you do not need two sales tax codes, this column can be deleted.
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 1 Amount - the sales tax amounts in this column are calculated based on the tax 1 code selected in column J and the sales tax percentages specified on the Setup sheet. The tax 1 calculations are applicable for national or federal sales tax and should therefore apply to most countries. If no sales tax should be calculated, the E tax code can be selected in column J.
Sales Tax 2 Amount - the sales tax amounts in this column are calculated based on the tax 2 code selected in column K and the sales tax percentages specified on the Setup sheet. The tax 2 calculations are applicable for state sales tax and may therefore not apply in all countries. If you do not need state sales tax calculations, you can delete this column.
Exclusive Claim Amount - this amount is calculated as the difference between the inclusive and sales tax amounts 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.
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.
Removing Second Sales Tax Calculations
We have included two sales tax types in the template to accommodate calculating sales tax on a national / federal basis and a state basis. If you do not need both sales tax types, you can delete the second sales tax type. You should not however delete both as this may result in template calculation errors.
Note: If sales tax is not applicable to your business, we recommend using the "E" sales tax code when recording all entries. No sales tax will then be calculated as the sales tax percentage for this code is zero.
The second sales tax type can be removed by deleting the Tax 2 Code column (column K) and the Sales Tax 2 Amount (column Q) on the Claims sheet. You can also delete the second sales tax amount calculations from the other sheets but be careful not to also delete the filter criteria at the top of the sheet (you need to select the cells in the sales tax 2 amount section and delete the cells by using the shift cells left option).
Note: No new columns should be added between the exclusive amount and sales tax amount columns on the Claims sheet otherwise it may affect some of the sales tax calculations in this template. Also, if you delete the columns for the second sales tax type, the Sales Tax 1 Amount column may display validation warnings but these can safely be ignored as it has no effect on the template calculations. If you do not want to see these validation warnings, just keep both sales tax types and just don't select any tax codes in the second sales tax code column.
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 created.
Note: All the client codes 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 not insert any blank rows between rows containing 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 which 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 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 enable 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 entered in column A on the Claims sheet.
Employees
The Employees sheet can be used to calculate the total expense claim charges which 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 specified in the filter section at the top of the sheet.
Note: All employee names which 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 which 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 enable 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 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 specified in the filter section at the top of the sheet.
Note: All expense accounts which 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 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 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 expense accounts should be debited with the exclusive amounts and the sales tax control accounts should be debited with the total sales tax amounts. 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 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 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 selected in column B is invalid. All the employee names which 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 selected in column H is invalid. All the client codes which 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 selected in column I is invalid. All the expense account codes which 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.
