Sharing Money With Friends: What Could Go Wrong?

Running the Finances for our Freelancers Collective with One Awesome Spreadsheet

This is partly a tutorial about how to make awesome spreadsheets, and it is partly about how we manage a flexible profit-sharing agreement for our collective of freelancers (The Hum). If you’re familiar with “microsolidarity”, this is the point where things get really spicy: sharing money between peers.

We currently have 3 partners in our collective, and we sell a combination of consulting services and digital products. We have a separate profit-share agreement for each product and each consulting job. The idea is that anything we sell through the company makes a contribution to the “common pot”, and the rest is split between the 3 of us depending on how much we contributed. The common pot pays for overheads and provides a wage buffer in case of lean times. Here’s our current rules of thumb for deciding how much income to share with the commons:

• Min 5% for everything (e.g. if we run a job through our accounts but it had no real affiliation with our brand).
• 10% for jobs where 1 or 2 of us deliver.
• 20% for jobs involving the whole team.

We’re balancing two incentives: on the one hand it is nice to contribute to the common fund, because that creates a shared resource that encourages more collaboration (“what should we invest this money in” is a great conversation for bonding). On the other hand, it still needs to feel fair: if I brought in a job on my own reputation and delivered it on my own, I don’t want to pay a big tax to the collective.

Sounds good in theory, but the technical side of things takes a bit of effort. Instead of using off-the-shelf accounting tools which are inherently limited, I’ve been teaching myself how to make spreadsheets do anything I want them to. I’ll explain my new awesome spreadsheet here, step-by-step. If you want to follow along, make a copy of this spreadsheet which has fake data in it. I’m using Google Sheets, but you should be able to follow a similar process in any other similar tool.

Import data from the bank

Once per month I sit down and run through the same process. I start by importing the transaction data from the bank account into the Bank Import sheet. Then I do a couple of things.

First, I categorise all transactions with a Category and Subcategory. So for instance in this screenshot you can see some transactions categorised as Income from Stripe (our credit card processor), and some from Invoice (people who paid by bank transfer). There are also some Staff payments: outgoing money to Nati and to Rich.

So how do we make those dropdowns in Column G & H? On the Model sheet there’s a list of all the possible categories. In the Data menu, I used the “Named Ranges” feature to give that list a name (“Categories”) — this name just makes it easy to refer to later. Then on Column G in the Bank Import sheet, I used the “Data validation” feature from the Data menu to create the dropdown, populated by that list of Categories.

The other thing I need to do to shape our bank transaction data is to convert everything to the same currency. We use multiple currencies, and I want to account for everything in Euros. To do this, I keep a table of currency exchange rates in the Model sheet, with one entry per currency per month. I used Named Ranges to refer to this table as “Currencies”.

Then on the Bank Import sheet, I convert everything into Euros in column F. Here’s that sheet again:

So if you look at cell B4: the bank tells me there was a transaction for £322.74, which is converted to €355.01 in cell F4. I use this lookup formula to find the correct exchange rate by matching the row (currency) and column (date) in the Currencies table:

Amount*VLOOKUP(Currency, Currencies, MONTH(Date))

The VLOOKUP function checks the Currency recorded for this transaction (in Column C4), matches it in the Currencies table, and then returns the exchange rate found in the row that matches the date in Column A4. Finally I multiple the exchange rate by the Amount in B4, giving the final answer in F4.

Summarise bank data

Now my bank data is categorised and normalised to a single currency, I want a summary. I use the Data menu again to create a Pivot Table, which you can see in the Bank Summary sheet.

Pivot Tables are fabulous aren’t they! It’s kinda daunting the first time you set one up, but it is an excellent way to take a big table and slice and dice it to answer questions about the data. In this case, my question is “how much monthly income and expenses do we have for each category?”

Here’s how I configured this one:

• Rows: Category and Subcategory
• Columns: Date (by default, this creates one entry per day, so I right-click on a day, and select “Create pivot date group” to switch it to this monthly view shown here)
• Values: Amount_EUR

This is good for seeing the monthly movement of money through the bank account, but we need a per-product breakdown to calculate our profit share.

Products

Each product, and each consulting job, has an entry in the Products sheet. Here I add up the total income and expenses for each row to give a final Profit (Column K) for each product.

So this is where we get to the profit share part:

For each product, we have a unique agreement about how to distribute the profit. E.g. for the Client 1 job, we put 20% of the profit into the “common pot”, and then distributed the rest equally between the three partners (Nati, Rich, Esra). The Client 2 job was a 50–50 split between Nati and Esra. For other jobs, we’ll keep track of our time internally and pay out our shares based on hours contributed.

We don’t necessarily pay these profit-shares out immediately, sometimes people want to leave some money in their own virtual account. So to keep track of each of our account balances, we have the Balances sheet:

What I’m doing here is taking the Transactions on the left and keeping an up-to-date balance for each account on the right. For example, Nati’s account moved +€1051, +€214, and -€1000 in August, so she ends August with €265 in her account. So all of our staff can see their current balance by looking at the last row on this sheet.

Budget

Finally, we have a very roughly estimated Budget, so we can see when we are due to run out of money. This sheet is very straightforward, hopefully self-explanatory:

So that’s it for the technical side: I hope it’s useful to you, and please do ask questions if anything is unclear. By the way, I’ve recently started offering my services as a spreadsheet coach, so if you want some help to make a sweet system like this, book some time with me here.

Of course, there is much more to profit-sharing than spreadsheets! The big question people always ask is “how do you make fair agreements?” Having transparent and accessible data certainly helps, but there are two fundamental ingredients that are much more important:

1. We cultivate a culture of open communication & psychological safety so everyone feels confident to speak up about their needs, desires, thoughts and fears. It’s totally normal to have hang-ups about money, so we need a safe space to talk and deliberate about our money agreements.
2. We have regular reflection times booked into our calendar every two weeks. So maybe we didn’t get the agreement perfectly right the first time, we have plenty of opportunities to adjust as we learn what works and what feels fair.

If you want to learn how to create a team culture that can support this kind of deep collaboration, check out our online course which gets into a lot more detail about decision-making, conflict, power dynamics, and all the rest.