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.


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:


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.


Click OK.


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.



You take the left, I’ll take the right

I recently finished re-reading Michael Gerber’s “The E-Myth Revisited”.  It is one of my go-to books whenever I find an organization that appears to be dysfunctional.  I find myself using parts of his model on my own organizations – since typically professional firms believe everyone should do everything.  This isn’t to knock professional firms but this approach is the single biggest obstacle to healthy and effective growth that I know.

“You take corporations and I take LLC’s”, seems like a great business model for accountants but it is simply a permutation of the “You take the left and I take the right” approach to management.  It doesn’t address the actual needs of the business and it doesn’t leave room for growth.  How do you hire an employee to fit that sort of arrangement?

It is even worse for other types of businesses.  I have worked hard to try and straighten out small businesses who grew into a disaster.  It is in those instances that I find comfort in the E-Myth; technicians who didn’t want to work for a boss suddenly have the worst boss in the world and so do his 12 employees.  All of whom are stepping on each other, tripping over inventory, losing tools, upsetting customers and generally eating up profits.

And in the meantime the owner is working 14-16 hours a day 7 days a week. Until he collapses.  What was the owner doing in those hours?  Everyone else’s job.

Organization, structure, discipline are tough to live with.  I get it.  As Mr. Gerber implies, you start your business because you want to simply work – you want a job.  You want to do it your way, meeting your customers needs by you always being available.  And, as he points out, it can’t work.  You simply don’t know it yet.

The problem is inertia.  Actually, that isn’t true.  The problem is the owner-technician’s inertia.  Try to take the owner out of the picture and you are accused of planning a mutiny.  Never mind the reality that the owner has already lost control; he talked to a friend who had a buddy who hired a consultant and that consultant ended up buying the company for almost nothing.  Never mind the reality that a company built around the owner is worth almost nothing.

Are you looking at a situation like that?  Are you the sole owner-technician of a small business where it seems that you re-do everything your employees already did?  Do you feel you are the only person qualified to make a sales call?  To build the widget?  To make a collection call?

Perhaps you are in a partnership and hoped that by making your best friend/employee an owner she would work 140 hours a week just like you?  Re-doing everything someone else already did, calling the same customers, shipping extra widgets…

This is where I depart from the E-Myth.  The author makes it sound like you can be down this path and somehow recover.  Sorry, it simply isn’t going to work that way.  Inertia is working against you.

Think about it.  You hired a bookkeeper because you hated doing the books and it ate into the time to make a sales call and build the product.  You still hate doing the books plus you don’t know how.  And if you do the books who will make the sales calls?  And don’t even get you started on sales people:  They make deals that you can’t keep and then they leave and take the customer.  No, it is better if you keep things they way they are and just work harder, right?

No.  It is not better; not for you, not your business, not your employees.  You can make the change but you can’t fight the inertia.  You may, however, be able to start deflecting your path.  The goal is not to slow you down but rather start changing the direction so that you start going in the direction you want.

More on this in my next article.  Have a great day.

You, Your Business and Internal Controls

Some of the most exciting people to have as clients are driven small business owners.  Some of the most exacerbating people to have as clients are driven small business owners.  While they want to make their first billion in sales, they also don’t want anything to stand in the way of them making those sales; even if it keeps them out of hot water.

Invariably, the entrepreneur comes to a decision point; start putting others in charge of areas of the business or shrink the business back to a manageable level.  The really driven, focused owner starts hiring managers.  The rest, well they ignore the advice.

The excuse for not hiring managers is mostly permutations of the, “It is my company, I made it so therefore only I can control it.”  Really?  Look at your balance sheet:

  • You have uncollected receivables from 9 months ago
  • You have nothing recorded for inventory but your shop is stuffed to the rafters with stuff
  • You barely have enough cash on hand to make next weeks payroll
  • You have loaned the company $250,000
  • The bank loaned you $500,000

I can go on but I think you get the picture.  You are not in control.  Because you are the only person who makes decisions, you are merely at fault for what is happening.  Being “in control” in business doesn’t mean making all the decisions, it means that there are natural checks and balances which make sure that one person can start a transaction and someone else verifies it.

So, what should you do as a first step to start building a solid internal control structure?

Start acting ethically and responsibly.

I have worked with small business owners who don’t like safety rules.  I have walked into shops where employees are grinding metal and are not wearing goggles.  Guess what?  I carry a set of goggles in my backpack.  I have walked onto job sites where employees are not wearing hardhats.  Yes, I carry my own hardhat in the car.  The point to make is that you need to set the tone that safety matters.  And it is not just about safety, it is about acting ethically and responsibly at every moment.

Set the example: Be the example.  This one little rule applies to every small business and starts everyone on the path towards better decision making.

Reward good behavior when you see it.  I worked with a contractor who sat back and watched an employee help a subcontractor who dropped a bucket of nails off the back of a truck.  Nails scattered everywhere.  The employee stopped what he was doing and helped shovel up the mess.  It was the right thing for the employee to do.  The owner missed a great opportunity to teach his employees how to act responsibly and reinforce actions that benefit everyone.

Don’t be arbitrary as you teach employees to be arbitrary.  A business owner employed his daughter to work reception.  She would on occasion come back a few minutes late from lunch.  The owner berated her out in the open about setting a poor example.  Another employee came back over an hour late from lunch and he joked with him.  You might think it is not showing favoritism by abusing your receptionist/daughter but it is in fact an arbitrary enforcement pattern.  If the rule is “In your seat at 1pm” then make sure you enforce the rule on everyone, not just the convenient target who won’t fight back.

Strong controls begin with the tone at the top.  If you take shortcuts, your employees will take shortcuts.  If you pad your expense account, your employees will add time to their work week.  You cannot grow and be successful if everyone is always looking for the easiest route.  Believe it or not, your success as an entrepreneur is totally based on the success of the people you hire.  Act accordingly.

Have a great day.  If you would like to learn more about how to start implementing effective internal controls that won’t break the bank, feel free to write me anytime for a free consultation.  I can help you understand how to grow your business while also keeping it under control.  If you would like to learn more about C.O.R.E. and how our services can help your business and association, check out our website.