How the “SUMIF” formula can make you more profitable
I’ll come clean. I do love a well-constructed spreadsheet. Done properly they are simple, logical and informative.
That said, use the wrong formula, and you’ll fall out of love with them soon. Too simple and they will be too much manual interference. Too complicated and you’ll end up managing the spreadsheet rather than the agency.
This is perfectly illustrated for anyone using Xero or FreeAgent. I’m a big fan of these online accounting systems. They’re really easy to use. Intuitive. They’re also really good value. What they aren’t so good at is organising your accounts in a way that is insightful into your business.
Faced with a P&L which has every account from the vital (Revenue, Salaries) to the less important (Postage, Books and Journals etc) it’s understandable to skip to the bottom line to see if you’ve made a profit and loss and move swiftly on.
This would be a mistake. With a little effort up front it’s possible to turn this long list into a useful P&L which would give you the key metrics for your business and, therefore, a vital clue how to manage your business better.
“SUMIF” is the magical little formula that can make this happen. If you know and use it already my apologies for preaching to the choir. If not here’s how you can make it do most of the work for you.
To produce an insightful summary P&L it’s my opinion that you need only 6 numbers. These are
1) Sales
2) Cost of Sales
3) Salaries
4) Freelancers
5) Overheads
6) Headcount
With these numbers you can look at revenue, staff costs and profit per head to see how effective your commercial model is and a big pointer to what you need to improve.
In order to get that insight you’ll need to do a little initial setting up work in Excel but believe me it’ll be worth it.
Here are the 4 steps needed to transform your P&L.
1) Export your system generated P&L into a CSV file
2) For each account assign a category from the 5 above (excluding headcount) and insert into a column next to the value
3) Set up a simple P&L format like the one below
Sales £X
Minus Cost of Sales £ £Y
Equals Revenue £X – £Y
Salaries £XX
Freelancers £YY
Overheads £ZZ
Profit = £X – £Y – £XX – £YY – £ZZ
4) For each of the categories above use a “SUMIF” formula to add up all the amounts in your original P&L that fit into that category.
Now for a science bit. The format of the “SUMIF” formula works like this; =SUMIF(range of categories, category name, range of values of categories).
This may sound difficult but that’s more down to my inability to translate formula into English. Basically you are asking Excel to add up all the numbers that have “Sales” or “Overheads” in the category column.
You can extend this logic to both your balance sheet and an analysis of your overheads. For both the balance sheet and P&L you need an extra category to split your trial balance (a list of all of your accounts and their balances) between whether its a P&L or a Balance Sheet account. For overheads you’ll need another category field to break them down into useful sub totals. My personal preference is
a) Property
b) Legal & Professional
c) Marketing
d) Other Staff
e) Office & General
That should cover most eventualities as well as giving enough information to investigate if there is any unusual spending.
Once you’ve set up the columns you should be able to cut and paste in your new numbers monthly and you should have a really useful, simple and quick to produce P&L and Balance Sheet.
Why is this worth it? It’s probably the most lucrative hour or so you could spend. Knowing what you’re charging per employee and how the cost of your staff compares to your revenue is the first step to improving your margins. All from a SUMIF formula. Now you know why I love it so.