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.

The paradox of choice

Walking to work the other day I noticed a shop sign advertising that they stocked over 30 types of Dutch salt liquorice. This sparked several thoughts. Firstly I didn’t know salt liquorice existed nor did I know that the Dutch specialised in it to such an extent that they produced over 30 varieties at least.

It also made me think how choice can be confusing. The paradox of choice is a well know theory. Intuitively it makes sense to me; faced with too much choice it becomes more difficult to make a choice. Which type of Dutch salt liquorice is the best one? Too many alternatives can make decision making more difficult and increases the chances of not making any decision at all.

Now, to stretch the point a little, the same can be true of agency finances too. There are so many numbers, metrics and ratios to look at that it’s tempting to be put off and either settle for what you have now or to look at everything equally. There is so much you could look at that a large part of what makes a good FD is knowing what to focus on.

Beyond the staple of the Profit & loss report (although there are always good and bad formats but that’s a different story) there are several numbers that absolutely must form part of the management reporting. They link the financial performance to the operational performance.

Firstly keeping track of headcount will enable you to look at revenue per head. This vital number drives profitability encompassing as it does information about the volume and nature of paid client activity. It’s also a really good, quick insight into how you compare to your peers.

Secondly staff compensation to revenue is the commercial ball game. It’s about how efficiently you can turn client revenue into profit. If you were a hotelier you would obsess about occupancy. If you were a dairy farmer you would focus on yield. If you run an agency you have to care about the ratio of staff compensation to revenue.

Whilst there are many, many varieties of Dutch salt liquorice there is a comfortingly small number of fundamental numbers that you have to focus on. There are others that are really useful but if you get thrown by the paradox of choice then focus on managing the two measures above and you’ll not go drastically wrong.

The author, Simon Collard, is founder of November Fridays which advises Marketing Services Agencies on ways to improve profitability. He is also Head of Operational Performance at Ciesco Group. If you would like to talk to him about helping increase value of your Agency by improving operational performance please email him on


What should management accounts do?


I’ve seen a lot of management accounts in my time. Bad sets of accounts can be too short or too long, too simple or too complicated. They can focus on the wrong metrics and they can lack context. They can be produced so late that everyone has already moved on. Sometimes they can focus entirely on the profit and loss and ignore the balance sheet entirely. Most importantly they can entirely lack any insight into why the numbers are what they are..

To be useful accounts need to meet the following criteria;

i)     They need to be produced quickly within a maximum of  7/8 working days, ideally fewer.

ii)    They need to show the overall financial position including current and future trading, balance sheet strength and cash flow.

iii)   There should be enough detail to inform but not enough to confuse.

iv)  They must include words of explanation and insightful analysis not just numbers.

v) They should summarise with more detailed analysis available if needed.

Sounds simple doesn’t it? Not surprisingly I think good management accounts and sensible advice from your FD is a vital part of growing your agency profitably. To have accounts quickly makes them more relevant and speeds up decision making. Monthly accounts should always focus on the balance sheet and cash flow as much as the P&L as careful management of cash is integral to growing your agency.

Striking the balance between detail and summary is difficult. There is always so much detail that could be reported. One way around this is to deliver at both levels so there is a summary P&L which just has the headlines with detail, if required, on the inside pages.

It’s also important to recognise that whilst accountants are number focussed not everyone shares this fascination so a narrative which includes an explanation of the events of the month is a good idea. What client activity has driven revenue up or down? How have leavers or joiners affected staff compensation? Have any one-off overhead costs affected the profit? Not least what light can they shed about the immediate future and how can they inform the conversation about what to do next.

At a conservative guess I’ve prepared just over 200 sets of management accounts as a Finance Director of Marketing Services Agencies and more importantly have had to explain them at least twice as often to everyone from Managing Directors to Interns. Anyone who would listen basically. Take a look at your next set of accounts to see if they meet these criteria. If they do, congratulations. If not why not drop me an email on and we can chat about how I could help improve the quality of your accounts and how they can make your agency more profitable..



“It measures everything, in short, except that which makes life worthwhile”

Robert Kennedy on GDP

Why you need more than a P&L to understand Agency profitability – Part 2

Part 1 made the point about how you need a broader set of metrics than just a P&L to order to understand why an Agency is profitable.

Beneath the comforting familiarity of the revenue and costs of a typical Agency P&L there are forces at work which will influence long term success. A Finance Director needs to measure these factors in order to help manage long term Agency profitability.

I covered the importance of the pipeline and conversion for new business success and longer term revenue generation. Keeping tabs on the reasons for staff churn can help retain client and agency knowledge and keep recruitment costs down.

Beyond these metrics there are some other really useful measures that will help you understand the shape and direction of your Agency.

Something as simple as an Excel Invoice register will enable you to analyse your revenue in a number of really interesting ways. It’s easy to get carried away but here are a few simple but powerful measures that will add depth and insight to your monthly reporting.

i) Building long term, profitable client relationships is vital but if you don’t measure it how can you manage it? Average client tenure is a simple way to track if your clients are still happy enough to continue paying you.

ii) What percentage of the total revenue do your 5 biggest clients contribute? It’s always easier to grow off the back of a single, strong client but the trade-off is over dependence on one relationship. Having a wider spread of clients is always safer. A sensible target is having about 50% – 60% of revenue from your 5 biggest clients and no one client accounting for more than 20% of total revenue.

iii) The split between retainers and projects is easy to track too and tells you a lot about your agency. This is a nuanced area and will vary from sector to sector but generally speaking it is helpful to have a solid base of monthly fees topped up by additional project activity. What the optimum split should be is something that needs a little thought – I’ve seen 80:20 both ways work OK.

iv) Measuring growth in revenue between new business and net organic growth is also pretty straightforward. Winning new business and then growing that business through performance is the goal. Having both contributing equally to growth is ideal – a year on year 50:50 split is a good target.

The above are by simple to measure. Simple organisation of revenue in a click and filter spreadsheet and a few Sum if formula can make a powerful difference to the insight you can bring to bear on the monthly reporting.

I’m an experienced FD of marketing services agencies. If you’d like an initial chat about how I could help your agency be more profitable then please contact me on .

Not all revenue is equal – how marginal revenue can improve your profitability.

It hardly needs saying that revenue drives an Agency. Everything from resource levels, pay rises, premises and, of course, profitability are ultimately determined by your income.

When a new opportunity comes up should you treat all revenue as the same? Should every project to every client be priced using the same ratecard? Does the type of project, the nature of the relationship affect how you price the opportunity?

Obviously this would be a short article if I thought the answer to these two questions was yes. In my opinion pricing of projects is as subjective as it is mathematical and needs to factor in the concepts of risk, reward and the vital importance of marginal revenue and costs.

It’s stating the obvious again but project income is generally riskier than a fixed monthly resource based fee. The income stream is more volatile and can be switched off completely at short notice. It can put a strain on resources and it is more difficult to plan around. This is why it’s perfectly reasonable to ask for a project rate card higher than your fee rate card. You may not get it but it is reasonable to ask.

Resource based fees (RBFs) should guarantee a level of work but this guarantee comes with the expectation of a lower price tag. Lower risk, lower reward. The danger with RBFs comes in managing the scope of work. There is always the danger of being asked to squeeze in another piece of work into a fixed fee. You will need to measure the potential income against being too inflexible and damaging the client relationship. Always tricky, you need your Client lead to be sensitive to rumblings of unhappiness.

Another choice comes when pricing up a project for an existing client. It may be in a slightly different area, another brand but it does come from a new budget area. It’s not handed to you on plate so it is a competitive situation. Should you stick with the existing rate card or should you discount?

There are two schools of thought here. Pricing at the existing rate card preserves the integrity of the current commercial agreement. It also maximises the margin. The other perspective is that, if you have the capacity, this is marginal revenue which has no added cost and should drop straight through to the bottom line – 100% margin in reality whatever it might say on the WIP report.

There is no right or wrong answer here. My preference is always to do the deal, to get more revenue through the door. If we can do this without bringing in additional headcount then the argument is that much stronger. If it gets you into another budget area then I’ll need a lot of convincing not to use our current team and billing to make us very competitive.

I always strip back proposals to understand what this means to marginal revenues and marginal costs to deliver the project. Rather than the profitability of the job I think about the effect on the bottom line overall. You can have an unprofitable job but a more profitable agency – this is the importance of marginal revenue.

Looking at projects in this way is a powerful way of linking the opportunity to agency profitability. It may be that you want to preserve the integrity of the existing arrangements but, now more than ever, it’s worth considering if there is a deal to be done. I may try to keep the rate card the same but discount the overall project – that way you can still go back to the higher rate for future work.

The author, Simon Collard, is an experienced, commercial Finance director with 18 years experience of worrying about proposals and costings. If you would like to have a chat about how using his experience could improve your profitability you can contact him on