Tracking reimbursements for employees

September 25, 2014 8:10 AM

I'm in charge of a tuition reimbursement process in my company-- people give me their approved applications along with their grades and receipts and I calculate the reimbursement and send it along to accounting. Part of this process is keeping tabs on what's being spent on what. Right now, I'm using a massive Excel sheet to do this and it is messy.

I'm looking for programs or systems for tracking. I need is to track information about the employee (name, department, title), how much they are receiving each time they apply for reimbursement, information about their program, and also keep a running total for how much is spent all together. I am not a finance person, so making beautiful, elaborate spreadsheets is beyond me. What I would really like is a database with a profile for each person, where I can see their information and the details for reimbursement, rather than a mega spreadsheet. My inclination/knowledge is toward FileMaker, as I've used that before and it allows customization. I don't have the experience to build a FileMaker database from scratch, though, and it sounds like my company doesn't want to drop the money on a consultant. We have Tableau and PeopleSoft (neither of which I've used) and there is Access 2007, but I'm wondering where to look after Excel.

posted by thefang to Technology (3 answers total)

Access will allow you to import the Excel files you have already created. Other databases will allow you to do that, too, but Access should make it pretty easy. Tables will be similar to various tabs on a single spreadsheet.

One suggestion I have for you is that you don't want one table for each employee, for example. What you want is one table that holds the employee info for all employees. It would hold their employeeID number, name, start date and any other pertinent information that applies to the employee and does not change (or rarely changes, like names). EmployeeID numbers should be unique, but if your company does not use them you can assign a unique number when you add anyone to the database.

All other tables will now use that employee number instead of a name. Dealing with names when trying find someone is very annoying when you have names that are often shortened (Michael/Mike, Angela/Angie, etc.). If you want to see all info for just one employee, then you would write a query that joins the employee table with other tables that hold your info. It will join on the EmployeeID column and as you learn about Access queries, it will all make sense.

From your description, you would want another table that has EmployeeID, semester or term, amount requested, amount paid, term start date, and term end date. You would then use a summary query to total up this table by date, employee or total program reimbursement.

When you are first learning Access, just focus on tables and queries. There are other things in Access, but they almost all rely on tables and queries, so you need a good foundation for them.
posted by soelo at 8:29 AM on September 25, 2014 [1 favorite]

Honestly, for something simple, I'd do Access, which looks a lot like other Microsoft programs, and will be much easier to import from.

You'll probably want tables like:

Employee -- includes employee number, name, address, DOB, department
Payment -- amount, date, employee number

The program/reimbursement information depends a bit -- does each person do one program, or do people do many different programs that need reimbursement? Are there lots and lots of programs you can do, or just a handful of them?

I created an Access database for work, in part because it plays so easily with other office software, but it also turns out that people who are reasonably comfortable with Excel and Word are fine using Access and making changes because they understand how it works.
posted by jeather at 9:14 AM on September 25, 2014

If you're stuck on Excel for the moment and don't have the time to create an Access database, it sounds like a pivot table in Excel might give you the quick and dirty solution until you can take the time to create the database.
posted by skittlekicks at 11:23 AM on September 25, 2014