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.

Accounting for Start Ups has never been easier but there’s still a vital role for a Finance Director

One of the many pleasant surprises I’ve learnt over the past 18 months or so as a freelance Finance Director is how much online accounting systems have moved on.

I’ve always counted my blessings I’ve worked more in Excel than Kalamazoo. The speed, ease and accuracy with which you can manipulate data has taken a lot of the drudgery out of the FD role.

The explosion of new, simple and exceptional value online products such as Xero, FreeAgent and KashFlow has had the same effect on accounting systems for the start-up entrepreneur. They enable anyone’s accounts to be on a robust platform from day 1.

Being able to log on wherever you are to check on the cash position or raise an invoice for work you’ve just done makes accounting easier, flexible and more relevant.

So far, so bleak for the accountant whose role could be marginalised to the annual accounts and tax computation. However there is a real opportunity for the Finance Director to become a trusted business advisor.

Naturally I’m a little biased here but no matter how simple and easy it is to process transactions or pay suppliers there is always going to be an important role for an FD who can explain not only why the numbers are what they are and ways they could be improved.

It is this added value role which can’t be replaced (yet) by software. In fact having the right software at the right time and cost should mean the time saved on the more day to day functions should mean a greater focus what the numbers mean.

Getting that advice and insight in a cost effective package is what I’m about. One of the things that can improve the chances of a small agency surviving and thriving is getting good, practical advice from an experienced, flexible and cost effective Finance Director. If this sounds interesting contact me.

5 Songs and 5 lessons for an Agency FD

Reading the stats about how people come across your blog is fascinating. You learn first-hand about the long search tail. Quotes have proved to be a popular route to my thoughts on the role of the Agency FD and in the hope of repeating this here are some lessons drawn from song titles.


Everything is free – Gillian Welch

Probably the biggest change I’ve seen in marketing has been the rise of social media. It has changed many of the basic parameters not just of marketing but also the business model of many industries, especially music. Not especially fertile ground for songwriters you’d think but this defiant paean for the creators makes it clear everything shouldn’t be free even though if it is she’ll still carry on writing and singing.

The threat and opportunity to marketers is how to harness this power and how to make it pay. Free content, paid for content and earned content should all amplify the client’s brand. The FD’s challenge is to make sure that content providers and Agencies can make it pay. Whether it is using social media to respond to customer service issues or generate links to boost your page ranking there has to be a solid commercial model behind. Everything isn’t free unless there is a very dramatic change happening.

2 + 2 = 5 – Radiohead

Not the best known Radiohead song but the only one to have such a blatant arithmetic error in the title. But yet…….

The ultimate goal for any Agency is to be greater than the sum of its parts. It can be frustrating for an FD but not every decision can be decided by a well-constructed spreadsheet. In the balance between magic and logic in an Agency it’s the FD’s job to be on the side of logic without dismissing the creativity, the passion, the magic that can make the Agency stand out.

Money Changes Everything – The Smiths

Yes I was a student in the 80s and have an unashamed nostalgic warm spot for Morrissey and Marr. Not least for some of the longest and most poetic of song titles of which the above is one of the simpler examples. I was tempted to include You Just Haven’t Earned it Yet, Baby

It is axiomatic that money does usually change everything. This is why it’s a golden rule for every Finance Director to make sure that everyone knows up front how the money works. Who owns what, who earns what and how it’s worked out. You’ll need to be transparent and explain regularly and clearly how this works. This is especially true for company bonus schemes where simpler is better and updates must be frequent. If not the bonus scheme won’t be the good news or the incentive that it should be.

Brand New Day – Van Morrison

Famously the grumpiest man in music this optimistic tune always cheers. It also serves as a reminder that what has gone has gone and what matters is what happens today, yesterday has gone.

In finance terms the link is that we should always concentrate on future costs and revenue and not historic ones. What is best to do next? Asking this question backed up by accurate and up to date historic data is key to maximising profit. Providing systems that means every manager in the Agency can answer the question is transformative.

Every Little Counts – New Order 

Firmly dating my formative musical years the lesson here is fairly straightforward. Anyone who has worked with me will have heard my favourite personal cliché that, yes, it is only x thousand/hundred pounds but it still matters. I’d still like it in the bank account.

Every little cost saving or extra billing is marginal profit. The quickest money an agency will make or lose will be through these kinds of decisions; especially at the estimate stage.

Making sure you have a sound commercial model, understanding that not every decision can be backed by a spreadsheet but when a decision has been made to be transparent about regular with updates. Recognising the emotional impact of money and instilling good disciplines into costs and revenue opportunities without risking client relationships. These are 5 valuable lessons for any Agency FD.

Over the last 20 years as an Agency Finance/Commercial Director I’ve commuted to work listening to music, some of it even post 1990, and tried to put these lessons into practice.  As a virtual or part time FD specialising in the independent sector I’m using these lessons to help Agencies grow. If you’d like an initial chat email me.

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

A Finance Director’s role in 4 quotes

Our life is frittered away by detail. Simplify, simplify.

Henry David Thoreau

It’s surprising the amount of data that even the smallest Agency can generate. Timesheets, invoices or forecasts it soon builds up and it can be daunting. The well ordered mind of a Finance Director will want to be all over it. We want to capture the data, ensure its integrity and organise it logically. Then, maybe, if we have time, we’ll share it.

The role of a FD is to make sure the rigour of good data prep goes on unnoticed in the background. The details that make it out for discussion should be simple and concise.

Systems and processes should be simple and unobtrusive. Spreadsheets should be logical and well formatted.

With all this in the background the Agency FD will be able to concentrate their effort on the real target; maximising profit.


Our business is infested with idiots who try to impress by using pretentious jargon.

David Ogilvy

Finance Directors come to agency life from a different angle with a different set of priorities, a different language. It’s not better or worse but it is important to realise it is different and we need to translate back and forth.

We need to try and understand what is important to an account director or a creative director and make sure our answers are intelligible and constructive.

It’s no good talking in jargon. Finance Directors more than ever will need to find a way to communicate, to explain, to find a common language.

To get your message across, to help people to manage job or client finances better; this is at the very heart of what makes a good Agency FD.


If one does not know to which port one is sailing, no wind is favourable.

Lucius Anaeous Seneca

It’s safe to assume that although Seneca never had to produce an annual budget he’d have produced a decent one if he set his mind to it.

All agencies should have a budget, its a direction of travel and a method to get there.  I’ve said before and will say it again; all budgets are wrong from the moment they’re written.

What is important is the plan;  how are you going to win and support new business, what is the effect of staff churn, how much should you pay your key staff, what is the effect of client attrition.

A budget alone won’t mean you reach port safely but the proper strategic thinking behind it certainly increases the odds.


Everyone has a plan until they’re punched in the face

Mike Tyson

You may have an incredibly well thought out budget with sensible assumptions but along the way you’ll be punched in face and those plans will be scrambled. A large client will leave, key people will leave, budgets will be cut and pitches will be won and lost. Whatever happens it is always about what you do next.

There are a few challenges here. One of which is about the difference between tactics and strategy. Clients and people will always change but if you have the right strategy it should pay dividends in the long term. The FD has an important role here in not only keeping calm but also in setting up management information that focuses on the success or failure of strategy over the long term; not just this month’s results.

The second issue which always crops up is about reducing headcount in the face of a business downturn. It’s a delicate balance here and whilst I always shy away from the macho brand of management that insists its best to cut early and cut hard. You’ll need to argue convincingly why this isn’t always the best approach. Cut too much and you’ll be in danger of limiting future growth, not enough and with too much emphasis on the next pitch and you’ll put the entire agency at risk. It’s a difficult job with no easy answers.

I’m a Finance Director who has specialised in Marketing Services. If you’d like a chat about how I could help improve your Agency’s profit drop me a line 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

Why aren’t Digital Agencies more profitable?

According to the latest Kingston Smith survey Digital Agencies are the least profitable of all in the marketing services sector.

They generate the lowest revenue per head (£72.9k compared to £106.6k for advertising) and turn in an all-time low operating margin of 4.7% compared to a benchmark of 15%.

This isn’t a one off either; the story was exactly the same in last year’s survey. Why should this be the case? Why are Digital agencies, literally, the poor relation? It’s not due to lack of control of employment pay rates or overhead costs so there must be market forces or operational weaknesses at work.

Here are my suggestions why this is along with some potential solutions.

Commoditised service

Without a significant voice at senior level in clients digital agencies could be left purely to deliver projects; a production house with someone else willing to undercut you on price rather than value.

Project versus Monthly Fees

Digital agencies tend to have a higher proportion of project fees to monthly fees than other sectors. Monthly fees may have a lower rate card but they safeguard against under recovery of hours.

Scope Creep

What makes the dependence on project fees so dangerous is that when fixed project fees are combined with a changing scope of work the under recovery of hours can kill the margin.

Project Risk

Digital projects can be complicated. Inter-linked processes, heritage systems, multiple platforms can make projects difficult to estimate up front. Get the initial estimate wrong and the odds on bringing in the project profitably plummet.


Maybe it’s an IT heritage thing but there seems to be a greater reliance on freelancers in Digital Agencies. Project fees + Scope creep + Freelancers = Lower margins.

Specialist versus Generalist

Trying to be all things to all clients will mean constant juggling between different skill sets unless your rates are so good you can keep enough people on payroll to cover all the different languages and specialities needed. I need a sarcasm emoticon here.

So much for the easy part but is there a solution? Can Digital agencies ever expect to bring in the same margins as Adverting or PR agencies?

It’s not going to be easy but there are some steps you can take to start improving that margin.

Proper recovery analysis. It’s vital you know if you are over servicing a client and if you are by how much and in which department. If you’re having to bring in freelancers it’s possible you’re losing money on some projects.

Risk assessment. The more complex the project, the more likely it is that something will go wrong and need additional resource to complete. Try to mitigate this up front with contingencies and clear discussions with clients about potential problems and the effect on budget. Document the risks so that you can refer back to them. Nothing a client hates more than problems popping out of the woodwork late in the day.

How much do you rely on freelancers? I’ve written before that I don’t think having freelancers is a bad thing but too many will eat into your margin. Think about how you recruit, retain and reward your staff and compare this overall cost versus the daily rate cost of a full time freelancer.

Early alarm system. You need systems to alert you early if you are over budget. No use having a wash up at the end of the project to discover the bad news; there is nothing you can do then.

Accurate forecasting – having a good idea what work is coming up will help manage what resource you need or what action you need to take.

Try to limit scope creep by having a detailed functional specification up front. At the very least make sure then any over servicing is a conscious decision based on securing/improving the client relationship.

Post launches services. Try to limit disruptive “bug fixing” to a fixed warranty period. Once the client has signed off you don’t have an obligation to offer indefinite support and maintenance for free. If it’s a commercially important site for the client offer a support contract on a use it or lose it basis.

Make sure you maximise any revenue opportunities such as Search or hosting.

Build up a roster of trusted freelancers/suppliers you can outsource fixed price elements to.

A step by step approach to find out what your revenue per head is, where you are over servicing allied to an accurate forecast are good constructive initial steps to take. Retaining your best people and giving them enough space to foresee and correct issues as they arise will improve operational efficiency.

The author specialises in improving the profitability of creative Agencies. If any of the above ring an (alarm) bell with you email him on