How to set up a meals per diem calculator spreadsheet

Calculating per diem seems like such a chore at work. Although I am not doing much directly related to travel, I am starting to be involved in getting several outside contractors paid. I am thinking that when they travel, I might have to figure out their per diem. It is still unclear how much we promise to pay or not pay. I went ahead with experimenting with a spreadsheet to calculate meals per diem. From this point, I am going to start referring the spreadsheet as the MPDC, i.e. the Meals Per Diem Calculator!

At my work, travel expenses are calculated based on the government per diem rate. The rate is separated by lodging and meals and incidental. The rate changes depending on the travel destination. The people who arrange travel for other employees have to refer to a bunch of tables for the lodging and daily meal rates. And then they apply those rates into a bunch of paperwork to figure out exactly how much a person can get covered on a business trip. What is even more complicated is how meals are calculated on the days of departure and arrival. Depending on the times of departure and arrival, breakfast or dinner would not be covered on those days. At my work, they follow the rule based on before/after 7am on departure day and before/after 7pm on arrival day. I learned that other organizations use different schedules so lunch could be part of the equation, too.

In comparison, lodging is so much simpler. It is all about the destination and multiply the room rate by the number of nights of stay. Meals are much more complicated so that’s where I focused my energy the last few days. The end result is the MPDC which looks like this:

mdp-calculator-3days-b-d.jpg

I don’t know how to write macro so it does not automate the process 100%. One still needs to look up the meals per diem rate first to use the MPDC but that’s pretty much the hardest part. No more thinking how many meals would be allowed on departure and arrival days and add up the cost. The above was set up in Excel but I think the formulas should be universal in other spreadsheet programs.

Before I go any further on the formulas, let’s start with setting up the data entry cells. The first table in Rows 3-5 are nothing special, just marking the cells for where the per diem rate is entered, plus a little cosmetic formatting. The cells in the second table in Rows 7-9 are where the action is. Cells B9 and D9 are set up with in-cell drop-down lists. This is mainly involved using the Validation command under the Data menu in Excel and this link explains it all. My lists of valid entries for those two cells are in Cells B16-17 and D16-17. Therefore, Cells B9 and D9 are limited to 2 choices of data each.

Now onto the formulas. Starting with the simple ones in Cells B22 and D22. Those two cells determine whether breakfast would be deducted from departure day and whether dinner would not be covered on arrival day. As shown above, B22 contains the formula =IF(B9=B16, 0, IF(B9=B17, -B5)). In plain English, it means, if departure time is before 7am, the result is zero, otherwise, departing after 7am means take away (the minus sign) breakfast dollar amount. The formula in D22 is kind of reverse: if arrival time is before 7pm, take away dinner dollar amount, otherwise, arriving after 7pm means take away nothing, zero.

The formula in Cell E9 brings everything together. Let’s look at the back end of that formula first, A5*(C9-A9+1)+B22+D22. In English, it is the total meal $ per day times the number of travel days (arrival date minus departure date plus one), “add” breakfast subtraction $ amount, “add” dinner subtraction $ amount. The front end of that formula is set up for the exception of day trips that take place in less than 12 hours. Part of the front end is =IF(AND(A9=C9, OR(B9=B17, D9=D16), 0, back end formula) and it means if departure date is equal to arrival date, AND if departure time is after 7am OR arrival time is before 7pm, the meals pay out is zero, otherwise calculate the back end formula.

The sample calculation in the picture above provides a situation like this: the travel destination allows $59 per day for meals and that total breaks down to $14 breakfast, $14 lunch and $31 dinner. The person left on Dec 3rd after 7am (i.e. no breakfast $ on departure day) and came home on Dec 5th before 7pm (i.e. no dinner $ on arrival day). 3 days of travel (3 X $59) minus one breakfast ($14) and minus one dinner ($31) equals to $132.

I tested out the IF function for the exception and the MPDC works perfectly. Here is a picture of one of the scenarios that would not cover any meals:

mpd-calculator-same-day-zero-pay2.jpg

And this picture shows only breakfast on departure day is not counted.

mdp-calculator-3days-b.jpg

As for this picture, all the meals are covered for all 3 days.

mdp-calculator-3fulldays.jpg

For the end users of the MPDC, they don’t need to see all the formulas. Hiding the bottom half of the spreadsheet with only the top two tables showing would make quite a simple looking tool, I think. Since this experiment, I set up another file for local travel within the same state which would limit the meals per diem rate to just a few variations instead of ranging from $39 to over $130 for traveling all over the US including Hawaii. That required another drop-down list for the few counties frequently traveled in the state and a per diem rate table in place of the data entry rate table at the top. Plus another long IF formula, of course.

Update (Sept 2013): In response to a recent comment, click here for the Excel file. I haven’t used this file for a long time. I don’t have to handle travel cost at work anymore. I hope the file works as advertised.

Advertisements

4 thoughts on “How to set up a meals per diem calculator spreadsheet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s