Tracking Split Costs
Today’s Author: Joe Chirilov, a program manager on the who has done a lot of work on both the Excel and Excel Services teams. Joe is going to discuss a spreadsheet he recently built using Excel 2007.
Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. We had a blast, but that’s a conversation for another day. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. This gave rise to an interesting problem, one that I’m sure you all hit once in a while: how do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time?
As you might guess, I built a spreadsheet to figure it all out for me. This isn’t the first time I’ve built such a spreadsheet, but it occurred to me that others might find such a spreadsheet useful so I decided to share it with you. As spreadsheets go, there’s nothing really complicated here, though it’s always nice to have that ready-made template when you need it. You can download the spreadsheet here: http://officeblogs.net/excel/Split_Costs.zip. You’ll notice the spreadsheet has three versions in it – more on this below.
Using It
So how do you use this thing? As I previously mentioned, it’s made for situations where multiple people in a group temporarily cover the costs of things for others in the group. You setup the spreadsheet by inputting:
-
The names of all people in the group, and
-
The various costs (e.g. plane tickets, hotel, dinner)
Then for each cost you enter the following:
Using my file as an example, it has sample data in it (also shown in the image below), Katherine paid for the train tickets, the tickets cost $1000, and the tickets were for Joe, Beth, Rob, and Katherine, so each owes 25% of the total cost.
This design allows for some flexibility in how the costs are split. Notice in the example above that James and Nancy did not get a train ticket so they aren’t assigned any percentage of the cost. The spreadsheet doesn’t enforce that everyone pays the same percentage. All it cares about is that the percentages add up to 100% (more on this later). In the image below, James booked two hotel rooms for five people, one room for two and one for three. The room for two costs more because it is split up among fewer people.
Once all the data has been filled in, what you typically see is that some people owe money, even if they paid for some costs, and some people owe a negative amount, which means they need to get paid back! It may look something like this:
At this point the easiest thing to do is to assign someone as the money collector. Once that person collects all the money, they pay back those that are owed money. This way, money exchanges hands the least amount of times as possible and the process is pretty clean. That’s all there is to it.
As you can see from the image above, the spreadsheet also provides a column for entering any amounts you have collected so far, so you can track how much you have and how much is left to collect.
On Spreadsheet Design
As I was cleaning up my spreadsheet to share with the world, I got to thinking about how others certainly have built similar spreadsheets before, and this got me wondering about other ways in which people may have approached the problem. So I built three different versions of the spreadsheet – they all do exactly the same thing but the way the data is laid out is different in each version.
So along with sharing this spreadsheet out, I’d like to take an informal poll, intended to feed my curiosity more than anything else: which of the three versions do you prefer most and why? I have my guesses but I’d like to hear from you first. I think it could be an interesting experiment, though I’m not sure yet what it will teach us. :)
How It Works
I’d like to talk a bit about a couple of the design points in the spreadsheet that hopefully you can apply to spreadsheets that you build.
Tables
Style1 uses the table feature a lot. We’ve talked about tables in the past, and if you haven’t used them, they are a great tool for working with tabular data. Read our articles on tables to learn more. In this spreadsheet, the structured references used in the formulas makes them much easier to read. Just look at how I compute “Remaining” in tables (=[Total Owes]-[Paid]) versus normal cells (=D3-E3).
VLOOKUP
Here’s a trick to using VLOOKUP with tables. VLOOKUP isn’t table-aware, so the third argument, col_index_num, is still a number and not, say, a column name when used with Tables. Next time you use VLOOKUP on a table, use the following formula for the third argument instead of an index number:
MATCH( TableName[[#Headers], [ColumnName]], TableName[#Headers], 0)
Substitute ‘TableName’ with the name of the table you are looking up, and ‘ColumnName’ for the name of the table column you want to index into. This, in effect, gives you a tight pointer to the column that does not break if the column name changes or if the column is re-arranged in the table, thereby providing a more robust alternative to using a numerical index.
You can see this technique in use in the spreadsheet in cells B23:F28 and B33:B38 on the Style1 sheet.
Conditional Formatting
I typically take a paranoid approach to my spreadsheet building, and I sprinkle lots of checks into the spreadsheet to make sure things are working properly. Conditional formatting is a great way to highlight error conditions that pop up in your spreadsheet. Here are a couple examples of how I used checks and conditional formatting to keep my spreadsheet in tip-top shape:
Percentages: make sure the distribution of cost always equals 100%. Create a formula that adds up all the percentages and apply a conditional formatting rule that highlights the cell if it equals anything other than 100%, as in the image below:
Payer: make sure that for any given cost there is only one person paying upfront. Create a formula that counts the number of values in the “Who Paid” column and apply a conditional formatting rule that highlights the cell if the count ever goes above 1, as in the image below:
If you play around with the spreadsheet you will find a few other examples like this. To learn more about creating conditional formats, check out this Office Online help article.
Good luck tracking costs for your next group event/outing. And don’t forget to let me know which of the three spreadsheet versions you like most.
