Posts

How the “SUMIF” formula can make you more profitable

/in , /by

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.

Good and early beats perfect but late

/in /by

I’m sure, in life, there are plenty of occasions when perfection has a role to play. In management reports less so.

What managers of any Agency want to know, as quickly as possible, is how is my business doing?

So, what is more useful to an Agency? A good set of management reports delivered quickly after month end or a marginally more accurate set delivered a week later?

It’s a rhetorical question obviously. More important is how do the CEO and FD work together deliver the right information at the right time? Here are a few ideas;

  1. Make sure there is a robust revenue forecast. If the business doesn’t take it seriously problems will end up with the Finance department to sort out which will take far longer. If it is a priority for the CEO it’ll be far more accurate far earlier.
  2. Most of the tricky assumptions are about revenue. By contrast most cost assumptions in an agency should be a little more straightforward and can be made quickly. Salaries are usually paid before the month end so will be known; freelancer days can be counted and costed; overhead categories should be relatively stable with sensible judgements about any one-off costs.
  3. Finance Directors by nature don’t like to guess but a report based purely on best estimates gives them licence to talk earlier about likely outcomes. With an accurate forecast they should be able to provide a “Flash” P&L far earlier than the normal one. 80% of the benefit for 20% of the effort.

All CEOs should know that their monthly P&L is not 100% accurate – it is the end result of many assumptions but with the right preparation it is possible to get a pretty good picture of how your

Business is doing. Then you can get onto the interesting stuff – the hows and the whys.

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

/in /by

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?

/in /by

 

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

 

 

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

/in /by

 

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

Magazine formats and miniature charts – the future of management reporting?

/in /by

Most management reports look dull. More importantly they fail in their main task which is to communicate useful information in order to help make better decisions about how to run a company.

Finance Directors have to hold their hands up and accept the blame here. We love numbers and have a tendency to think that the monthly reports would be a that little bit better with just a little more numerical analysis.

I’m never going to argue that you don’t need numbers. I’d have gone into a different job if that was the case. However numbers should always be a means to an end. The objective of every report that the finance department produces must be to inform.

Finance Directors have a vital role in executing Agency strategy. They need to report on the successes and failures and have an opinion, backed up by a facts, about what is best to do next. They must not exile themselves to a back office number crunching role. They need to improve the quality of their reports to improve their standing as a credible strategic voice around the Boardroom.

Most management reports will be produced in Excel. This isn’t a problem as Excel is a very powerful business information tool but it is one that can do far, far more than crunch numbers.

One way to improve management reports is to improve the readability through more user friendly formats. Rather than plough on with the same tired format why not take inspiration from an industry which lives or dies by its readability? With a little preparation and a little imagination it can publish information in magazine format packed with visual information that can cover an amazing amount of information without swamping the reader with numbers.

Recognising that monthly accounts are never going to look like Hello magazine how can Finance Directors make their reports a little more interesting?

Data preparation, formatting and miniature charts are key to developing a magazine style report that will pack far more information on one page than can ever be done through tables of numbers. More importantly the reader will be presented with charts that will show quickly and easily the important trends. Want to know what your margin has been over the last year? You need a graph which shows you the trend rather than a data table. Same goes for revenue, staff compensation and overheads. Strip away all unnecessary detail on graphs or even use a sparkline to get the essential information across quickly.

Whether it’s evolutionary instinct or education we take in far more information visually rather than analytically. Finance Directors should understand this and construct their reports to appeal to the eye rather than the calculator.

This is not to argue that reports don’t need numbers but their function is twofold. They should be tucked away as the foundation of the report. Unseen and kept in the background they will be the link to your accounting system to ensure your reports are always anchored in reality. They should only appear as the tip of the iceberg only to the extent that they are required to inform the reader.

To work properly a magazine format requires an initial investment of time. Hidden data tables need to be input. Miniature charts need to be set up. Information needs to be linked to ensure the amount of repetitious input is minimised. Headlines should point you to further analysis on the inside pages.

With a little preparation a step change in the usefulness of management reports is possible. Miniature charts will carry more information and have a stronger visual impact. Proper data preparation will cut down the amount of time taken to produce each set of reports. The end result should be that the monthly report becomes a more useful, relevant and more widely read document. Here is a generic example. It’s not perfect but it shows the general principle well, fewer numbers, more graphs.

Magazine format

The author, Simon Collard, has produced 100s of management reports in his 17 years of running finance departments in Marketing Services Agencies. They have, he believes, steadily improved over time to a point he feels confident your agency would benefit. If you find your reports dull and not as informative as they could be why not have a chat over a cup of tea. Contact him via Simon@novemberfriday.wpengine.com