DesignFest Home

Expenses

An Expense Account Manager


[ Home | Problem Collection ]

Prepared by

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


Problem Domain Description

Scope

Requirements

Use Cases

Interfaces To Other Systems

References


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:

This is complicated by the following:

It is important that:

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:

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
  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:
  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.


Last updated by Torsten Layda, SWX Swiss Exchange, DesignFest® Webmaster.