Category: marketing

A little Excel Excitement

~ C.O.R.E. Beliefs by John ~ Leave a comment

The other day at a client meeting we were discussing Pareto and the concept that 80% of the revenues were generated by 20% of the customers and It was likely that 80% of profit was generated from them as well.  Management was having a difficult time picturing this, but fortunately we had prepared for this meeting by using the Excel Data Analysis add-in.

The first graph we presented was the histogram of revenues generated by their clients.

histogram1

The most telling thing for management was that 40 of their customers generated less than $1,000 of revenue.  And notice how many generated under $15K in revenue.  Suddenly, they could see new patterns by changing the visualization.  So, if you haven’t tried to graph this yet using data analysis, here are steps to consider.

First, create a table with the information you want to chart.  In this case it was the customer and revenues for 2018.  Next, in your Excel, click on Data Analysis and the following modal box pops up:

histogram2

Select histogram.  It then advances to the next pop-up which asks for the data range and the Bin Range.  A little bit about the bin range:  It is the breakpoints you want to use to group your information.  In this case, I ran a filter to determine the largest amount and set the bin increment just above it.  In this case, we want to see the Pareto effect, so click on the Pareto (sorted histogram) box and also the Cumulative Percentage box.  And then chart the output.

histogram5.png

Click OK.

histogram8

After Excel generates the histogram table file and graph, you may need to make some modifications to get the graph to look the way you want.  In this case, I wanted to sort by revenue impact of the various bins.  To do that, I multiplied the frequency by the bin amount and then sorted the table file by the total bin revenue generated.  Finally, I added a calculated percentage for each bin and the cumulative percentage.  I then set the Cumulative Percentage to use the new percentage I calculated. In this case, The impact of sales between $15,000 and $25,000 account for almost 80% of revenues, even though their frequency is less than 15%.

Consider the impact that this type of presentation can have on a strategic conversation.  What does your staffing level look like if we focused our energy on that small group of customers?  What message resonates best with that group?  Should we discontinue sales to small buyers?  We had a great conversation from less than an two hours worth of work – mostly spent in defining the table structure, gathering the data and importing it.

Raw data and numbers are useful in the right context, but if you want to stimulate an effective business meeting, show some graphs which group data in ways that make your team think about what they are doing and why.

 

 

Leading versus Lagging

~ C.O.R.E. Beliefs by John ~ Leave a comment

Accounting is a universally accepted lagging indicator.  Profits are so last month, the balance sheet is yesterday’s news, and don’t get me started on net book value of equipment.  As strange as it sounds though, most people making decisions seem to be ok with all the things that happened yesterday and in some cases things that happened years ago.

One of my favorite lines in a presentation is, “Running your business by your accounting information is like driving with your windshield blacked out and being forced to steer by looking in the rearview mirror.”  It is dangerous and will ultimately run you off the road and yet many people find comfort in looking at past performance and remembering the good old days.  But you have to start looking at other things in order to make better decisions.

Let’s start with revenues.  Quick question, are you tracking your sales funnel?salesfunnelex

Looking at this, we can begin to make an educated guess at where sales are heading next month and perhaps beyond.  With $30K sales in final negotiations and $80K in the proposal stage, you know that with a closing ratio of about 45%, you are looking at close to $50K in revenues closing in the next month.  Meaningful?  Compared to saying that the company did $42K last month and $68K in the same month last year?

revenueproductsyr.png

While it might not seem like a leading indicator, this one could be if properly used.  This tracks the revenue by products, not based upon their model, but based upon the year that it was originally introduced.  If your company prides itself on bringing new products to market but you are unsure how new products fare, this could be an eye opener.  In this particular case, the bulk of the revenues is generated by legacy products, followed closely by near-legacy product sales.  Is this a problem?  Perhaps, especially if you find out that your advertising and promotional dollars are being spent to keep legacy products in front of customers, or that you are spending a ton of money on advertising the new hotness and it is not taking off.

This reminds me of a recent conversation I had with a client.  It seems that one of their major customers is going to merge and most likely will no longer buy products from them. The obvious question, how are you going to address the concern?

No problem he says.  They are going to lay off employees.  We have been spending almost $750K a year on R&D and we haven’t gotten anything out of it.

I was voting on cutting senior management compensation by 98% and moving them to some sort of incentives based on new products and new channel sales but I guess slowly going bankrupt by starving the company of new products is a much safer bet.  After all, every “VP” should be guaranteed a paycheck.

Find a creative way to look at your company’s data, especially sales.  If you are not tracking sales prospects, start now.  Your sales people will give you lots of reasons why it won’t help, but don’t take no for an answer.  If you have new products that are not selling, find out why.  My bet is that somewhere along the way there is a disincentive to either buy or sell.  Customers are getting a better deal on your old products or your sales peoples’ commissions are better on legacy products.  Or it is a dog and you need to dump it!

Don’t simply rely upon accounting reports when it comes to managing your business.  Get creative, tell your controller or CFO to get creative when it comes to predicting future sales and expenses.  Yesterday’s news is important to someone, but that someone doesn’t have to be you.

 

Alignment

~ C.O.R.E. Beliefs by John ~ Leave a comment

Growing a business can be challenging.  It doesn’t help that there are lots of books and internet articles explaining how so-and-so did it with no investment and no effort.  Those stories might be inspiring, but they don’t always tell you the whole story.  What those stories aren’t telling you might cause you to chase a plan for longer than you normally would.  No small part of that has to do with alignment.

Alignment in business is all about making sure your marketing, your message and ultimately your assets all support your core business.  A great example of this came from a company I was assisting as an outsourced controller.  At a strategic retreat, the leadership decided it wanted to start going after larger commercial construction projects.  This was a great idea but it meant more than just saying “here we are.”

First we had to deal with the fact that most manufacturers did not adequately plan for their liquids and gas piping.  And yet, in order to do the job well, the piping needed to be planned.  Client’s did not have specialists on staff to handle this nor did most architectural firms.  Don’t get me wrong, they had a good idea of how it needed to be, but some things are highly specialized.  To be successful the Company needed to invest in an engineering team.

The engineering team needed the right tools.  Auto-cad, plotter and a quiet place to do the design work.  Not to mention a large space where the team could meet with clients to review the plans and requirements.

The company then needed to get the message out to the construction community.  The company’s sales team needed to be armed with information to help clients in their selection process.  We had to successfully educate them that lowest bid is not always the most appropriate bid.

Finally, the company needed to address its pricing model.  There was an obvious disconnect that was hamstringing the growth and adoption of the new service.  The company was moving from a repair and maintenance service to a construction service

but management was still using their service rate to try and price the construction.

rate analysis

The company was pricing local jobs about 20% higher than out of town work.  This was driven by two issues – first daily mobilization from an office no nearer than 15 miles from the nearest likely construction site and second applying the service rate to the total time.  The service rate worked well for service – it required some technical skill to diagnose a problem with a gas distribution system and the client expected to pay for the “emergency” nature of the call-out which included travel time to get to the job.

This doesn’t always apply in construction.  And because of this, the company was not completely aligned.  Local jobs were being lost and the company had work coming out of its ears hundreds of miles away.  The crews were tired of being away all of the time and it was harder to manage if things went sideways at the jobsite.

So, the sales manager, the engineering manager and I sat down and figured out how to get aligned.  The estimate was redesigned to charge a lower rate for mobilization in town.  The engineer generated a bill of materials for every job – one problem was that local jobs were not getting the same supporting documents since it didn’t seem like a problem to run around town and pick up parts when the crew was local – all of which allowed the company to still make a substantial profit on jobs, since the company was now able to land local contracts which reduced wear and tear on vehicles and employees.

rate analysis rev 1

As your company leader, always make sure your entire business is aligned.  The greatest service in the world won’t make you a dime if customers don’t agree with the pricing.

Understanding Overhead

~ C.O.R.E. Beliefs by John ~ Leave a comment

When I first start evaluating a financial statement, I try to group costs together logically.  It is far too typical for most businesses to rely upon the canned reports and these are almost always prepared in GL Number order.  But a clearer picture can be developed by grouping the costs of revenues separate from the overhead and the overhead into 4 main groups.

The overhead groups I prefer follow the Throughput model:

  • Labor Overhead
  • Marketing Overhead
  • Facilities Overhead
  • General

A little bit about the groupings:  Labor overhead includes not only those who are on payroll but also consultants and outsourced staffing.  So, for instance, if your company outsources janitorial services, this expense is reported in labor, not facilities.  My approach is to put all labor into labor overhead, including production labor, unless it is truly variable – which most is not these days.

General is the catch-all classification.  There are two services I typically would group into general – legal and auditing.  While both are still people performing services, these are services which your business typically cannot provide internally.  Otherwise, if it cannot clearly fit into one of the other three groups, put it in general for now.

Let’s say your business does $1.0 Million in sales monthly.  Your direct material costs are $350K and you have depreciation on equipment which manufactures the products you sell of $50K.  Throughput, which is the measure of how much money you generate to cover overhead and profit, is $600K.

Continuing our analysis:

  • Labor Overhead runs $400K  or 66.7% of throughput
  • Marketing Overhead is $50K  or 8.3% of throughput
  • Facilities Overhead is $50K or 8.3% of throughput
  • General Overhead is $30K or 5.0% of throughput

In total you are spending $530K to generate $600K of throughput.  88.3% of every dollar you bring in is consumed by your overhead, of which most is tied up in labor costs.  You see, when you separate out your labor into different categories, such as production labor, sales commissions, accounting and office staff, you can lose sight of the total amount you are spending to generate throughput.  It isn’t that these separate amounts are not important, but when you are looking at leveraging your business, having expenses scattered everywhere can lead to a misunderstanding.

Properly grouped, we can start analyzing.  There would be two points of reference to the analysis, average and best case.  Both of which can be found in the prior year’s records.  For the average, I would recommend taking the last 5-7 years of information and reformatting to match the groupings above.  You are looking for a trend and what you will likely find is that throughput has remained fairly steady but labor overhead has crept up.  It isn’t necessarily bad, but it does indicate that more money is being paid out for peoples time but the company is not getting much in return.

The comparison to best case can be a real eye opener though.  Here you find the year where there was the most profit and then compare where you are today with the overhead structure in place when the company made vast profits.  In almost all cases, you will find that the company increased spending across the board relative to that maximum profit year.

So, instead of giving a bonus to the employees and management, the company raised base compensation.  The company went from a 50,000 sf facility to 100,000.  When you study this great year you start realizing that perhaps it was luck and you were betting it would continue – only to find out it didn’t.

GAAP statements have their purpose.  But managing to GAAP can be dangerous to the bottom-line.  It is all too easy to want to capitalize everything into your inventory but that means that today’s costs are probably being buried and will be recovered in a later year.  But in the meantime, your costs are possibly growing out of control which is impacting your current cash flow picture and may even hurt you in the future if you have to reduce prices to be competitive.

Consider using the Throughput model for evaluating your internal financial statements.  I think you will be surprised at how much information it can provide you to help you make better business decisions.

If you would like more information or would like to discuss how effective analysis can help you understand your operations and profitability, feel free to contact mecontact me anytime.  I am here to help.