A little Excel Excitement

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.

 

 

Planning over budgeting

Somedays it feels like all we do is parse semantics, doesn’t it?

Budgeting, planning, it is all the same thing, right?  Well, no – not really.  Yes, far too often they are used interchangeably, but the truth is that planning is far more important than budgeting.  Budgeting is really about allocating your resources.  Planning identifies what you want to accomplish and how your resources can help you make that happen.

This is why the distinction is important.  I am starting to help clients plan for the next year and part of that is looking at what we have accomplished through today.  It is part looking at the numbers and part looking at the strategy and seeing if we are off target.

To me, there is nothing worse than not being able to answer the question, “How did we get here?”  Or worse, hearing the glib answer, “We performed well (poorly).”  Duh.

What was the plan?  Did we think we were going to break into a new market?  Did we succeed?  What is the acquisition cost per new customer?  Are we getting repeat business?  Can we grow in that are?  At what cost?

There is nothing harder to do than listen to someone say how profits are holding steady, while knowing that it is because money is not being spent on carrying out the plan.  Of course you have profit, we are not spending money on project X, which, by the way, two years ago we all agreed that it was going to be the flagship product within 5 years.  Why did the company plan to allocate thousands of dollars on advertising if we don’t want to spend it?

These are hard questions to listen to, much less respond.  No one likes having their parade rained upon, and yet, well, there it is. The plan drives how resources are acquired and spent, otherwise you just have a budget.

That isn’t to say budgeting isn’t a useful tool, it is.  But my experience has been, budgets are typically devoid of planning.  We spend money in that area last year, lets continue to spend money in that area.  We generated revenues selling widgets last year, lets budget to generate 5% more in sales.  There is no plan, no vision, nothing to be held accountable to until it is too late.

I do lots of budgeting for non-profits and the most fun I have is helping them rethink the budget process into the planning process.  We start by looking at the big picture and then start asking how we get there.  Then, once we get consensus, we start putting numbers to actions.  It is extremely rewarding to see these professionals see the results and understand that there is a reason for spending money, other than the satisfaction of having money to spend.

So take time, do planning.  Think about where you want to be and how you want to get there and THEN allocate resources.  You will get tremendous benefits from it, mostly from thinking about how you are going to get from A to B.  If you need help, let me know, I am always open to helping companies and non-profits think about their plans and how they translate into action and money.

 

Understanding cost drivers

A few years back, I was brought in by the president of a small business which manufactured and installed custom storage systems for offices and restaurants.  His concern was that either his accounting system had a bug in it or that someone was stealing from him.

This came to light when he ran his most recent quarter profit and loss; it seems that for the first time in over 7 years, he lost money.  His shop has never been busier.  He is getting lots of highly profitable contracts so the quarterly results don’t make any sense.  I asked to see quarterly information and got to work.

The first report I reviewed was the profit and loss report.  Sure enough, there was a $53K unabsorbed overhead amount  This happens in one of two ways – quarterly overhead went through the roof or the driver, in this case direct labor hours, were substantially lower.  Sure enough, the direct labor hours were down about 10%, indicating that labor wasn’t recorded.  Which would be really strange as the controller had been doing this job for almost 5 years and consistently made the overhead allocation adjustment correctly.

overhead1

So I asked the president if there were any major changes.  His response was that he decided to outsource all of his installations.  As a test, he explained,  the company in the prior quarter elected to put the installation out to bid on one project and the price came in at about 60% of what his costs were.  So he decided that he was going to “sell” the installation department to his department supervisor who would then quote jobs as an independent contractor.

I explained to him that what he was seeing was a direct result of the decision to outsource without knowing all the available facts.

Fact: There were two different departments, each with its own overhead costs and driver

Fact: the Company was using a single driver addressing the total overhead

Conclusion: The same (essentially) overhead dollars were being allocated over fewer direct labor hours, leading to larger unabsorbed overhead since the rate was not adjusted to reflect that fewer hours were being “sold”.

I then showed him a spreadsheet of what this actually looked like:

overhead2.png

The company rate of $41 was correct, but only for its overall purpose, allocating company overhead to production costs.  In reality, very little of the overhead went to support the installation department.  As a matter of fact, when separated, it became obvious to him that the shop was being subsidized by the installation department.

When they bid a job and included the rate of $41 to the install hours, the company was in fact generating an additional $27 in revenue which went to the bottom line.  But this was hidden from the controller, the president and the estimating department.  Thus, the $100 per hour revenue rate appeared high when compared to the $75 rate that the subcontractor offered.

In truth, had the company been facing lost estimates, they could have reduced the hourly rate for installations from $100 to about $65 and still earned a decent profit.  But you have to look deeper into your company structure in order to understand that options like that are available to you.

When most of your costs are fixed, then basing make or buy decisions on your overhead absorption rate can be dangerous.  The key is understanding that allocating costs by way of hours turns that fixed cost into an illusory variable cost.  You begin to think that by eliminating the driver, the cost goes away as well.  It doesn’t work.

Once the president understood this, he was able to convince the installation team to rejoin the company, although he did have to make some concessions as to bonuses when it came to profit earned on installation jobs.  And with this information, the company went through the various areas of the business and examined how costs were incurred and allocated to projects to even more effectively estimate contracts and keep their bids competitive while improving their profitability.

 

Leading versus Lagging

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.