Posts

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 simon@novemberfriday.wpengine.com.

 

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 simon@novemberfriday.wpengine.com and we can chat about how I could help improve the quality of your accounts and how they can make your agency more profitable..