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.

Year on year comparisons are too useful to be left to the year end

Accountants love to compare numbers. It’s trained into us early on in our careers. Call it analytical review and you’ll always have something to say at Board Meetings.

Don’t misunderstand me, I love comparing annual financial performances as much as the next accountant. I’m just not sure it should be a once a year treat.

Management reporting by its nature tends to focus on the monthly and cumulative annual results. If we’re sensible we will compare it to budget and get to explain why it is up or down against target.

What we don’t do enough of though is compare the year on year (YOY) performance. YOY looks at the results for the 12 months up to present and compares it to the 12 month period before that.

Looking at the monthly numbers and comparing it to budget are important but if you want to understand the long term trends in your business nothing will beat looking at the YOY performance, especially if there is any seasonality in your business.

Changes in revenue, staff costs and overheads really pop out when you look at them over a longer time period. Individual months can be affected by a single issue, project or unexpected cost. Over a year the importance of those issues get flattened out and you are left with the trend.

It is by focussing on those trends and the reason for them that management reporting can come alive by linking the numbers to the reasons. With an understanding of the reasons comes an opportunity to do something to improve future performance.

Whether it is a change in revenue from an activity or sector; additional resource cost or an increase in property costs it becomes clearer and more understandable when you compare the effect over a rolling 12 month period.

If you want to lift your management reporting out of the routine and link it to the real operational performance of the agency then start looking at the longer term trends affecting your business. Start thinking about what you need to do now to affect the figures in a years’ time.

The author, Simon Collard, is a chartered accountant with 20 years’ experience running and advising on Agency finances. If you’d like to improve your agency management reporting, talk about ways to increase the long term value of your agency please email


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


“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 .

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


Why you need more than a P&L to understand Agency profitability. Part 1

Robert Kennedy’s quote was a pretty good response to a recent think tank proposal that costed the loss to the British economy of a bank holiday. Seemed to sum things up nicely. It also made me think a little about how successful management accounts are at measuring what makes agencies profitable.

In order to understand why an Agency is profitable the P&L is the tip of the iceberg, the starting point. Revenue minus costs is the easy part. Trying to figure out and explaining why revenue and costs are what they are should be the challenge facing Finance Directors.

That’s not to say the humble P&L can’t offer you some instant insight into the fundamentals. Starting with revenue, staff compensation and operating profit numbers you can easily work out your per head metrics. This will give you a direct comparison with your competitors and will highlight areas of strength or weakness.

Even armed with information on your relative performance won’t enable you to answer the fundamental question about profitability. It will point at where you should be looking at improving performance but what drives revenue and costs?

Building long term, profitable client relationships with a happy, well-motivated team is the answer to a profitable agency but how do you measure this?

The lifeblood of an agency is the new business pipeline and the value of this pipeline over time along with the conversion rate are key drivers for any agency. In addition tracking the reasons for failure will help sharpen future proposals. What weight you put against a proposal is up for academic debate but as long as you are consistent it is the trend over time you’re interested in. It’s such a simple thing to measure and vital to your future profitability but it needs to be front and centre in reporting.

A happy, well-motivated team is vital to the intangible culture of the agency as well as saving time and money by not having to constantly replace people who leave along with the client and agency knowledge they’ve accumulated. Whilst some churn is not a terrible thing if it gets too high it will eat into your bottom line. Short of polling everyone whether they’re happy each week you can and should measure staff churn. I would also go a step further and measure churn of your star performers who have left to go to a competitor as well as total churn. Learning why people who you would want to keep have left to go to another agency has to be important to know – is it money, career prospects or culture that drove them away or attracted them?

The above are by simple to measure. As an FD making the time to sit down with the new business team or the HR manager to measure these pipeline and people metrics is as important to the long term health of the agency as the monthly accounts.

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 .

How about some really useful disclosure requirements?

Annual financial accounts are meant to inform the reader how well the company is trading and how strong the balance sheet is. They are the most visible and reliable numbers available.

A P&L or a Balance Sheet can only tell you so much and to help serious investors or casual readers get a better insight there are pages of disclosure notes giving details, amongst many others, about geographical turnover, fixed asset additions or staff numbers.

This extra detail has drawbacks though. Anyone who has tried to read the accounts of one of the groups will most likely scratch their head and wonder what’s going on. At the level of a stand alone Agency there are flaws too.

Over the years I’ve spent some time putting together and reading sets of accounts and I’ve felt for a long time that there are some better metrics that will help you figure out how well the Agency is doing. To keep accounts of readable length I’ve also got some that could be dropped without impairing their usefulness.

The two most important groups of people for any agency are their clients and their staff and they are pretty much ignored in terms of hard numbers in most accounts I’ve read. There is often a lot of well written rhetoric but little to help an analysis of how successful an Agency has been in keeping these important people happy.


Currently most disclosure is about staff costs, these are broken down between payroll and social security costs. Apart from confirming that the accountants know that Employers NI is an additional cost what good does this serve?

The second staff disclosure is to show headcount numbers split between some arbitrary heading. There is little to be gained between knowing the split between “Marketing” and  “Administration”.

Getting rid of the above would free up some time and space for something a little more useful.  I’d love to know how many freelance have been used and their cost. That way I can work out a true compensation to revenue figure as well as knowing how much the Agency relies on freelancers.

Whilst I’ve got a blank sheet of paper wouldn’t it be good to know what the staff churn rate is? We all pay lip service to people being our greatest asset but having a disclosure which highlights how your staff vote with their feet might concentrate our efforts into staff retention.


There is surprisingly little about clients in the disclosure notes. Maybe that’s an Accountancy blind spot. We like to boast about new business wins and to segment operating performance between business units as well as a geographical turnover analysis; but that’s about it.

For an independent I think it’s much more useful to know what percentage of revenue comes from your biggest client(s). Maybe it wouldn’t be comfortable telling the world how reliant you are on one or two clients but isn’t this the sort of thing someone would want to know if they were relying on the accounts? I can see the argument that you wouldn’t want your big client knowing exactly how important they were but in my experience most big clients don’t hold back from throwing their weight around.

Of less immediate commercial sensitivity but of real interest to me would be the average client tenure. It would show how successful they were at building long lasting relationships with their clients.

If any of the above seems too much like hard work in this ideal world I’d happily jettison the fixed asset note as well as tearing up the operating lease commitments between 2 and 5 years. Tedious and not that useful.

My disclosures would give a really clear picture about how efficient the Agency is, how successful they are at developing client relationships and how good they are at staff retention. That, to me at least, is what accounts and disclosure should be about.

Of course the above will never happen but these metrics are vital to measure for any growing agency to ensure growth translates into profit. They’d also make league tables more interesting. If you’d like to see how they could be applied quickly and simply to your Agency contact me on

How a kludge can help manage your agency finances.

Definition: A kludge is a workaround, a quick-and-dirty solution, a clumsy or inelegant, yet effective, solution to a problem, typically using parts that are cobbled together. This term is diversely used in fields such as computer science, aerospace engineering, internet slang, and evolutionary neuroscience.

Agency finances and financial systems can get very complicated, very quickly. The data available to analyse can grow exponentially leaving your head swimming and no better informed about the state of your finances.

Now, a good accounting system will solve this in the long term. Set up correctly to reflect how your agency operates it can give the right level of detail to the right people at the right time.

However if your budget can’t stretch to a new accounting system at the moment there are manual fixes that can give you enough information to help manage your Agency better.

An excel sales invoice register can break down your revenue and generate departmental P&Ls. Headcount numbers can give you a simple recovery analysis. Media commission can work out an approximate cost of sales accrual. If needs be a trial balance model can even generate monthly accounts from Sage50.

I’ve used all of the above (with a few others) to give some clarity and direction to Agency finances. My preference is always for a solidly based and integrated accounts and job costing system. This takes some time though and if you need some management accounts by tomorrow you’ll need a well designed kludge or two to get you through.

A word of caution though. I’ve seen spreadsheets that started with the best of intentions but then veered crazily out of control. Be especially careful if they are open to the entire agency.

Luckily for me what to focus on and what the information means and when to move to a more robust system means my role isn’t made redundant by a spreadsheet no matter how good it is.

If you’d like to know more about how I can help your Agency become more profitable with or without a kludge then contact me on