Expenses An Expense Account Manager

Prepared by

Henry Baragar and Gail E. Harris (Deloitte & Touche Consulting Group/DRT Systems)

Problem Domain Description

A better system (better than an Excel spread sheet) is required for an employee making a claim for reimbursement of incurred expenses. A claim is an itemization of expenses where the following information must be recorded for each item:

• the type of expense (eg. travel, meals, taxes)
• the project to which the expense is to be charged
• who paid for the expense (ie. employee or the company)
• explanation for unusual or abnormal expenses (eg. lost ticket charges or two lunches)
• identification of the supporting documentation (eg. receipts)

This is complicated by the following:

• a receipt may have several different items (eg. hotel & meals) which must be reported on separate lines
• a receipt may have items which must be reported in different reporting periods (eg. return flight could end up in a different reporting period from the outbound flight)
• under certain conditions the taxes on an item must be identified and tracked
• an item may be split between two or more projects (and therefore must appear on two or more expense reports)
• a separate expense report must be submitted for each project (which might also have different reporting periods)
• an expense report may contain items with different currencies (for those employees who travel internationally)
• an expense might include a personal portion which must be deducted (eg. extra rent on the automobile when the employee stayed away for personal time)

It is important that:

• an expense report can be audited easily (by the company, the client or the government)
• that the employee can easily verify that all expenses have been claimed (given that some items may appear on different expense reports in different reporting periods)
• the manual labor involved in producing and auditing expense claims be reduced

Finally, it would be nice (although not required) if the system "knew" which expenses were expected (based on a trip) and could alert the employee to missing expenses (eg. a missing meal) and could flag expense items that are outside of the guidelines (and require further explanation).

Scope

Although most of the requirements are oriented towards supporting the processing of an expense report by the corporation, the scope is limited to only those parts directly involving the employee. Note that although the emphasis is on travel, the system must be able to support other expenses incurred by an employee (such as cellular phone charges).

Requirements

In addition to a form for inputing expenses, the following reports are required:

• An Expense Report (by project and by accounting period)
• A Receipt list (by period and [optionally] by project)
• A Coverage Report (by period)
• [Optional] A Report of the Expense Report Status (not submitted, waiting for reimbursement, reimbursed)

These reports are described below.

The Input Form

The input form is used for inputing receipts into the system. For each item on each receipt, the following information is required:

1. the date the expense was incurred
2. the type of expense selected from a predefined set of types (eg. taxi, flight, hotel)
3. an explanation or comment for the expense (which is often a subtype of the type)
4. the amount of the expense
5. the portion that is taxes (if taxes need to be split out)
6. the project(s) to which this expense is being assigned along with the portion being assigned to each project
7. the portion of the expense that is personal
8. who paid for the expense (the employee or the corporation)
9. the currency of the expense (also need a place to input the applicable exchange rate)
10. identification of supporting documentation (preferably a label to be written on the receipt)

Note that there is often a lot of commonality between two items on a receipt (eg. project) and between two receipts (eg. currency). Also, there should be no restriction on the order in which expenses are input.

The Expense Report

The expense report details all expenses incurred by the employee for a specific project over a specific period of time. It must include:

1. the employee's name
2. the project
3. the period being covered
4. the date the expense report was prepared (or printed)
5. the list of expenses (items) in chronological order
   • must include a sequence number for the item
   • must include all the details from the input form
   • need to cross reference other expense reports for those items that are split across two or more expense reports
6. subtotal by type of expense
7. total expenses incurred
8. amount of cash advance given to employee (if there was one)
9. total expenses to be reimbursed to employee (or repaid by employee)
10. currency in which to reimburse employee (if different from currency in which expenses are being charged to the project)
11. exchange rate being used (if expenses are being incurred in two or more currencies
12. Signature spaces for employee and "approver"

Note that some extra white space should be left for use by those people processing the expense claim. The employee is reimbursed on the basis of this report. Finally, each expense report is uniquely identified by the project (item 2) and the last date of the period being covered (item 3).

Receipt List

The receipt list report lists all receipts in chronological order for a specific time period. An option should allow the list of receipts to be restricted to a specific project. Each line should contain:

1. date of the receipt
2. expense reports, along with the amount, to which expenses on receipt have been charged
3. total amount of the receipt (calculated by summing amounts charged to each project)

The purpose of this report is to enable the employee to verify that all receipts have been claimed and that the full amount has been claimed. A list of receipts to be submitted with a specific expense report can be generated by using the option to restrict this list to a specific project.

Coverage Report

The coverage list report lists all expense items (not receipts) in chronological order over a specific time period. An option should allow the list of items to be restricted to a specific type of expense. Each line should contain:

1. date of expense
2. type of expense
3. description of expense
4. amount of expense

The purpose of this report is to verify that no receipts are missing. This is accomplished by scanning this list and making sure that everything that should have been claimed was claimed (eg. by checking to make sure that three meals were claimed every day).

Report of the Expense Reports' Status

This report lists the status of all expense reports over a specific period of time. Each line of the report should contain:

1. the expense report (identified by project and last date of reporting period for the report)
2. the status, which could be one of:
   • unsubmitted
   • submitted (with date submitted or date of printing)
   • paid
3. total amount of expense report
4. reimbursement amount

This report is used to identify expense reports yet to be submitted and the total amount of money the employee is still expecting in reimbursements.

Use Cases

Two examples that have been drawn from "real life" are provided. Sample expense reports are provided for these examples. None of the other reports are available. The actual receipts will be available on request.

Example #1

The first example is a simple trip taken for Project A (PRJA):

	Henry Baragar										January 16 to 31, 1997
			Auto	Expenses	Auto,	Airline Etc.	Hotel	Sundries	Entertainment	GST	Explanation	Total
	Date	Project	Km	Amount	Cash	Charged
1	97/01/24	PRJA	22.5	$6.75							Ride to airport	$6.75
2	97/01/24	PRJA				$243.08				$31.64	Flight to Toronto (see PRJA-970115-22)	$274.72
3	97/01/24	PRJA							$6.10	$0.40	Lunch (lost receipt)	$6.50
4	97/01/25	PRJA						$3.76		$0.24	Parking	$4.00
5	97/01/27	PRJA					$197.40	$0.50	$16.09	$14.11	Hotel	$228.10
6	97/01/27	PRJA							$23.95	$1.36	Lunch	$25.31
7	97/01/27	PRJA						$6.01		$0.39	Parking (no receipt)	$6.40
8	97/01/27	PRJA				$217.64				$14.11	Car rental	$231.75
9	97/01/27	PRJA				$126.22				$16.52	Flight home (I have return portion)	$142.74
10	97/01/27	PRJA	22.5	$6.75							Ride from airport	$6.75
		Total		$13.50	$0.00	$586.94	$197.40	$10.27	$46.14	$78.77		$933.02
		Company Charged				$586.94				$62.27		$649.21
		Owed Employee		$13.50			$197.40	$10.27	$46.14	$16.50		$283.81

Note that parts of this report do not follow the requirements; they were extracted from an Excel spreadsheet using the current approach. The first problem is with the Hotel expense on line 5 which covers two nights, and should be split out on a day by day basis. Second, the phone call and meal (Entertainment) shown also on line 5 should each appear on separate lines even though they are all from one receipt. Finally, this report does not show that the airline tickets were paid for in U.S. dollars and converted to Canadian dollars. However, this example does show how some taxes (GST) must be split out from each expense item.

Example #2

The second example shows expense reports for three different projects, all covering the same time period (more or less). This is a complicated example which took considerable effort to allocate the receipts to the correct project and split the costs between projects correctly.

The first of the three expense reports in this example is also from Project A and covers the reporting period Februrary 1 to 15:

	Henry Baragar										February 1 to 15, 1997
			Auto	Expenses	Auto,	Airline Etc.	Hotel	Sundries	Entertainment	GST	Explanation	Total
	Date	Project	Km	Amount	Cash	Charged
1	97/02/03	PRJA	22.5	$6.75							Ride to airport	$6.75
2	97/02/03	PRJA				$213.00				$15.31	Flight to Toronto (see PRJA-970131-9)	$228.31
3	97/02/03	PRJA			$6.31					$0.44	Bus from airport	$6.75
4	97/02/03	PRJA							$3.01	$0.20	Lunch	$3.21
5	97/02/03	PRJA							$27.69	$1.57	Dinner	$29.26
6	97/02/10	PRJA						$19.84		$1.16	Taxi from airport	$21.00
7	97/02/10	PRJA			$16.00						Subway tokens	$16.00
8	97/02/11	PRJA							$6.96	$0.45	Lunch	$7.41
9	97/02/11	PRJA							$8.59	$0.41	Dinner	$9.00
10	97/02/12	PRJA							$5.43	$0.35	Lunch	$5.78
11	97/02/12	PRJA						$6.01		$0.39	Parking	$6.40
12	97/02/12	PRJA							$22.14	$1.24	Dinner	$23.38
13	97/02/13	PRJA					$268.47	$9.00		$18.48	Hotel 97/02/10 to 97/02/13	$295.95
14	97/02/13	PRJA							$13.26	$0.74	Lunch (1/2 of $28)	$14.00
15	97/02/13	PRJA				$124.04				$8.20	Car rental	$132.24
16	97/02/13	PRJA				$474.06				$30.46	Return flight to Ottawa	$504.52
$170.00	97/02/13	PRJA	22.5	$6.75							Ride from airport	$6.75
		Total		$13.50	$22.31	$811.10	$268.47	$34.85	$87.08	$79.40		$1,316.71
		Company Charged				$811.10				$53.97		$865.07
		Owed Employee		$13.50	$22.31		$268.47	$34.85	$87.08	$25.43		$451.64

Note how the first flight (line 2) is cross referenced back to one of the flights (line 9) on the report in Example #1 (covering Jan 16-31).

The second of the expense reports in this example is for Project B (PRJB):

	Henry Baragar										January 26 to February 8, 1997
	DATE	Project	(1) AUTO/TAXI		(2) RENTED AUTO,	PLANE,RR	(3) HOTEL/	(4) MEALS	(5) TELEPHONE &	(6) SUNDRIES	(7)ENTERTNMNT/	(8) EXPLANATION	TOTAL
			NBR OF MILES	X.315=DLR AMT	CASH	CO. CHARGD	MOTEL	(INCL TIPS)	TELEGRAMS	(EXPLAIN BELOW)	BUS. MEALS
1	97/02/03	PRJB		$14.47								Taxi to airport	$14.47
2	97/02/03	PRJB				$381.59						Flight to Philly	$381.59
3	97/02/03	PRJB						$7.74				Dinner	$7.74
4	97/02/04	PRJB						$4.45				Lunch	$4.45
5	97/02/05	PRJB					$90.72					Hotel 97/02/03 to 97/02/05	$90.72
6	97/02/05	PRJB						$4.84				Lunch	$4.84
7	97/02/05	PRJB		$4.56								Gas	$4.56
8	97/02/05	PRJB				$123.88						Car rental	$123.88
9	97/02/05	PRJB				$170.00						1/2 of flight to Providence (see PRJC-970208-1)	$$170.00
		Total		$19.03	$0.00	$675.47	$90.72	$17.03	$0.00	$0.00			$802.25
		Company Charged				$675.47							$675.47
		Owed Employee		$19.03			$90.72	$17.03					$126.78

Note how the expenses for the second flight (line 9) are shared with Project C, as shown by the cross reference to the first flight (line 1) on the next report.

The third of the expense reports in this example is for Project C (PRJC):

	Henry Baragar											February 1 to 8, 1997
	DATE	Project	(1) AUTO/TAXI		(2) RENTED AUTO,	PLANE,RR	(3) HOTEL/	(4) MEALS	(5) TELEPHONE &	(6) SUNDRIES	(7)ENTERTNMNT/	(8) EXPLANATION	TOTAL
			NBR OF MILES	X.315=DLR AMT	CASH	CO. CHARGD	MOTEL	(INCL TIPS)	TELEGRAMS	(EXPLAIN BELOW)	BUS. MEALS
1	97/02/05	PRJC				$170.00						1/2 of flight to Providence (see PRJB-970208-9)	$170.00
2	97/02/05	PRJC		$25.00								Taxi from airport	$25.00
3	97/02/06	PRJC					$99.68					Hotel	$99.68
4	97/02/06	PRJC						$2.94				Lunch	$2.94
5	97/02/06	PRJC				$302.00						Return flight	$302.00
		Total		$25.00	$0.00	$472.00	$99.68	$2.94	$0.00	$0.00			$599.62
		Company Charged				$472.00							$472.00
		Owed Employee		$25.00			$99.68	$2.94					$127.62

Note how the expenses for the first flight are shared with Project B.

Interfaces To Other Systems

None.

References

None.