5 steps to turn data you already have into actionable business intelligence

Understanding what happens in a business is important. For a small business going with their gut is often the only option for a business owner. Data takes time to collect, and time to convert into meaningful information.

The goal is to maximize the value of the information already being gathered and convert it effectively into meaningful information to make decisions with. One way to do this is to understand the questions that are important to the business, and understand the places where data may already be gathered as a part of normal processes to ensure this data is consolidated and converted with the lowest impact on everyone within the business.

Follow these 5 simple steps:

  1. Identify the problem
  2. Find the right question
  3. Gather the data
  4. Analyze the data
  5. Convert the data into actionable business intelligence

1. Identify the problem

Start by looking at what don’t you know, but do want to know. Or consider a problem/concern you have.

For a restaurant owner, they may simply want to provide the best quality service to their customers.

2. Find the right question

You can start by looking at who you already have on your staff.

Who is your most effective server, what might you consider as the best indicator?

  • Time guests spend at their table?
  • Size of tip?
  • Additional items sold?

3. Gather the data

How do you gather that information? Start with your register, or Point of Sale system. It is already gathering data on every transaction that runs through your restaurant.

4. Analyze the data

Now we understand the questions and the source of the data we can begin to analyze the data.

  • Table time
    • First, in a basic system the original order may be entered in as the guests first place their order. The system will capture the time when a server first enters the order, until the time the server finally clears out the table’s check. The difference is an approximate amount of time each table spends sitting. Is there a limitation? Yes, if the waiter takes forever to enter the order, and is quick on the check the turn around time will be very low, but that isn’t really what happened, and your guests may be very unhappy. So, it is important to understand the limitations to keep a watchful eye on your servers.
    • What if your system is based upon handwritten checks during the meal and a final receipt at the end? Then look at how many receipts the cashier processed during a shift. You have their time cards. 16 tables in 8 hours, is about 2 tables per hour, or 30 minutes per table. Is this perfect? No, but it can start to give you a better idea. If you notice one cashier has a consistent volume than you can start asking more questions, about their behavior and learn if there is something to either improve on with them, or a lesson to be taught to the other servers.
  • Tip Size
    • Size of Tip is another decent measure of quality of service. Total tips may not be the best indicator either, a $100 tip on a $5,000 table is only a 2% tip. Where a $20 tip on an $80 bill is a 25% tip. The guests may not leave a comment card, but built into your accounting system you can see if there is a pattern emerging.
  • Upselling superstar
    • Does one server consistently sell more? If your system isn’t detailed enough to show individual items the size of each check is a simple measure, but if your system can show the items on each check, look for drinks, appetizers and desserts. These are the items that a server will have to hustle more to move, and if one moves more than another there may be something to learn.

5. Convert the data

We talked briefly about it above, but it is more than just to learn about learning who is best and who isn’t. Make sure you have a clear picture of the reality. There may be more to the story than what is on the spreadsheets, a single behavior difference can affect how data is inputted. Consistency is key, when data is inputted, and how can affect data significantly. Also, definitional changes can affect it (what each person believes each item or behavior represents). In a restaurant situation there may be less room for ambiguity, but a small difference on when they ring up the check could be huge. Because if they are quick to get drink orders, and food orders, but slow to get it into the system, there may be no actual difference between how long the guest is at their table, but the perceptual difference from the data will be different. By clearly communicating when and how to perform certain tasks that ambiguity is reduced.

If the data is consistent, and there are noticed trends, understand that this isn’t the time to start slapping gold stars on your high performers and dunce caps on your low performers. Learn what behavior differences impact these metrics and use training to modify behaviors. Understand your staff and how they will behave when they know the data and how they are being measured.

A word of caution

Everyone will try to game the system, if they know what behaviors to adjust to improve the perception of their performance they will do their best. It is your job as a business owner to communicate the real message you want communicated. If you want faster turnover on tables, train the behavior for that, don’t tell your servers this is how you analyzed the data, then they will have the keys to the castle, and will start adjusting their behavior to affect the data not the turnover on tables.


It started with some simple questions, and can turn into a full dashboard and process for identifying high and low performers. High performers should be praised, and retained, low performers should be retrained or replaced. But if you don’t know who is a high or low performer, you may be running on your gut, and the cool bartender may be fun to talk to but his performance may be lacking in performance.

This doesn’t just apply to servers, within your own business consider what questions you have, or even problem. Follow the 5 steps:

  1. Identify the problem
  2. Find the right question
  3. Gather the data
  4. Analyze the data
  5. Convert the data into actionable business intelligence

Best of luck.

Disclaimer: The information contained in this document is provided for informational purposes only and should not be construed as financial or tax advice. It is not intended to be a substitute for obtaining legal, accounting, or other financial advice from an appropriate legal professional, financial adviser or for the purpose of avoiding U.S. Federal, state or local tax payments and penalties.

How a little shoebox created a big obstacle for one small business

Every business has its obstacles, and part of what helps some business owners to succeed and even thrive is their ability to overcome those obstacles. We recently had the opportunity to help one small retail outlet overcome one of their biggest obstacles.

When we began our relationship with a small retail outlet, they explained that they were having issues with their accounting but were convinced they couldn’t solve the problem remotely. Their business wasn’t complicated, but they were on the other side of the country, from our location in Phoenix. They believed that because they were located on the other side of the country, and that they had been slowly accumulating a shoebox filled with receipts that there was no way we could get them set up and begin to help them with their books without sitting down together. And spending hours and hours manually entering transactions.

The result was that they did their own bookkeeping for months, until they on an unrelated trip, ended up in Phoenix and we were able to sit down together.

That was months where they didn’t get any help understanding their transactions, and how they could improve their business, and months where they had to fight through the mundane task of bookkeeping, instead of working on their business.

Fast forward to months later, when we were finally able to sit down together, with their shoebox and their book of signed checks.

3 hours later we had caught up a full years worth of transactions and we were discussing pricing and other higher value issues relating to their business.

Here is how we did it. And we didn’t open the shoebox at all.

A couple key points:

  • They are a majority cash based business, no liabilities, and most transactions take place through their in shop purchases, with only a few being large purchases where they were waiting on cash to come in.
  • This meant that almost every transaction ran through their bank.
  • They also, only had one bank account. Which they kept separate from their personal checking account. That was hugely important.

So, here was the process:

  1. We connected their Quickbooks online account to their bank feed.
  2. We talked out the types of transactions and what each one represented to their business.
  3. We set up rules, for the regular transactions, to help automate transactions processing.
  4. When we were left with only unidentified check numbers, we opened up the checking account and check by check attributed the vendor, and account type.
  5. Then we processed their reports, and began to analyze what we were looking at.

Although, they were very comfortable sitting in a room together and talking through the transactions. We could have done it months prior, and they could have already been making profitable changes in their business. Up to that point, they had been operating on their gut, because they didn’t have an option. Now they could log in to Quickbooks, and see how their business is performing at any time, in real time.

It took a little while, but we were happy to have the opportunity to work together and start to help their store grow.

The lesson learned is that they let that shoebox, and their belief that the job couldn’t be started without sitting down together, hold back their business growth.

Best of luck to you.

Disclaimer: The information contained in this document is provided for informational purposes only and should not be construed as financial or tax advice. It is not intended to be a substitute for obtaining legal, accounting, or other financial advice from an appropriate legal professional, financial adviser or for the purpose of avoiding U.S. Federal, state or local tax payments and penalties.

Bank Reconciliation, why and how.

No one likes Bank Reconciliations, well, maybe. Either way, we recognize that they aren’t fun. But they are important. First we will identify why they are important, then we will talk about how to do them. Because if they aren’t done frequently, they only get harder. And we don’t want to end up in the situation like our stock photo above where it becomes an unsurmountable mountain to climb.


The bank reconciliation is the difference between what has actually happened and what your accounting thinks has happened.

That is important to understand.

Consider a business owner has been working within their Quickbooks and has been writing checks and cashing checks. They are all sitting in Quickbooks. Quickbooks is going to present a balance of, $200,000, for example. Then the business owner looks in their checking account and sees $250,000. So, what is the truth?

They both are.

This is where the reconciliation comes in. It is understanding the differences.

Differences can be caused by:

  • Timing
    • Uncleared checks
    • Uncleared deposits
  • Errors
    • Banking errors
    • Book errors
  • Bank related transactions
    • Fees
    • Interest

By understanding this activity you can better control your cash flows.


  1. Open the bank reconciliation module of your accounting software. Have the bank statement for the period under reconciliation out/open.
  2. Going down the line, of the bank statement, transaction by transaction, within the module check off every check that has cleared the bank as cleared.
  3. Going down the line, of the banks statement, within the module check off every deposit that has cleared the bank.
  4. Enter any transactions that are in the bank statement that were not in the general journal. This may include wire transfers, automatic transactions, and bank fees/interest.
  5. If the ending balance of the bank statement doesn’t match the reconciled general journal amount investigate the following:
    1. Checks recorded in either the bank or general journal at the wrong amount.
    2. Deposits recorded in either the bank or general journal at the wrong amount.
    3. Investigate any other remaining issues.

If the bank made an error, they will need to be contacted. It does happen. So, don’t be afraid to correct it.

As a side note, this is also a good time to identify any old checks still uncleared. Each business is different, but set a period of time, 2 months is probably plenty. Any uncleared check after that time will need to be investigated. Reference our blog on uncleared checks for more details.

When it is all done you should have a report from your software that says the beginning balance, and the ending balance, with a section for uncleared checks. It is important to not dismiss the uncleared checks. Those checks will clear.

Balance                    –  250,000

Uncleared Checks – (200,000)

Available balance    – 50,000

If this is what you see, don’t cut checks that will total more than $50,000, because that is all the cash available.

Helpful hints

The more you do this the better. We know it is a pain, but for our clients we do it weekly. For one, there is only a quarter of the transactions, but it also gives us a weekly clear picture of the exact cash position. If you were to pull statements weekly or daily, but only do the reconciliations monthly you may be in a situation where you think there is $250,000 in the bank, but you forgot about the $300,000 check out there waiting to be cashed.

A bank feed doesn’t eliminate the need for this, unless your business doesn’t use checks. If that is the case, well done. One less thing to worry about.

Best of luck.

Disclaimer: The information contained in this document is provided for informational purposes only and should not be construed as financial or tax advice. It is not intended to be a substitute for obtaining legal, accounting, or other financial advice from an appropriate legal professional, financial adviser or for the purpose of avoiding U.S. Federal, state or local tax payments and penalties.

Pokemon Go – The Accountant’s Way

I’m going to be up front and honest. I play Pokemon Go.

I have my reasons, of which I will discuss, but I do enjoy the game.

But as the game is deceptively complex, and if a “Trainer” wants to catch them all, they need to play smart. And because . . . 4a3e4f9e2c8194f18e6a0ed55ba9b0ccee9469efdb0a6fa08e51a282c0e0e200

The analysis I performed, is similar to what we at Wilson Lake would perform for a business who wanted to determine the the ROI for various service lines. The goal being that in a moment where a business owner needs to make a decision, they can fall back on the results of their ROI analysis to make a snap decision. That is our goal for this. We want to facilitate Trainers making intelligent decisions when they are presented with multiple options and scarce resources. Of course, if you have a lot of Pokeballs, and unlimited time . . .


I play for 3 reasons:

  • As a Dad I play Pokemon Go with my son, it gives us something to engage with together, on even terms. He is much more knowledgeable than me though.
  • Allows an opportunity to teach: patience, savings, and problem solving. It is my chance to sneak in some Dad lessons without him knowing. IMG_6649
  • Because I played Pokemon Blue when it first came out and it is a lot of fun.

**To the left, is our character (named by my son) and our buddy Larvitar.**

When playing it is important to establish goals.

  1. Catch them all
  2. Battling
    1. Access to Rare Candy, to level up rare Pokemon
  3. Increase level
    1. Improves available resources to support catching them all
  4. Gain badges
    1. Brag worthy, but also facilitates catching of Pokemon

We want to be able to identify very clearly, when we have scarce resources, either Pokeballs or time, which Pokemon to catch.

First, as the first two goals are a given, catch new Pokemon, and catch powerful Pokemon. We need to move on to the more complex part, which is what Pokemon will give us the greatest ROI.

We broke them down into classes:

  • Pidgey Class – 12 Candy to evolve
    • Caterpie
  • Rattata Class – 25 Candy to evolve
    • Geodude
    • Sentret
  • Ekans Class – 50 Candy to evolve
    • Growlithe
    • Snubbull
    • Paras

We utilized the following Key Assumptions

  • Only considered the Pokemon with the highest pop up rate.
  • Pokemon are disposed of immediately after evolution
  • Which type of Pokeball used is irrelevant
  • Use of Berry’s and special event bonuses are also irrelevant

At first glance we did a quick analysis of how much experience could be gained by evolving an equal number of Pokemon from each class. For a total of 15, which is the required amount needed to evolve 5 Pidgey, 2 Rattata, or 1 Ekans. If we do this, for each Pokemon, how much experience do we gain?

  • Pidgey – 4000 XP
  • Rattata – 2500 XP
  • Ekans – 1900 XP

Further analysis in the below chart shows how, throwing accuracy, and throw bonuses can affect the ROI.

2nd throw no bonus
2nd Throw w/curve
1st Throw w/curve
1st Throw w/curve & Excellent
1st Throw w/curve & Great
1st Throw w/curve & Nice
XP per evol cycle
% Difference Rattata
% Difference Ekans
XP per evol cycle
% Difference
XP per evol cycle

Accuracy, and first catch bonus count for a lot, and no surprise to anyone, Pidgey is the leader for exp ROI.

Keep in mind that not all Pokemon within these classes are created equal.

Pop up rate is a huge factor, if you are after experience points, a common Rattata class is more valuable than an uncommon one. Because you are more likely to gather the other necessary candy needed to evolve it. An example would be a Rattata vs a Machop. We don’t see many Machop, so a Pokeball spent on the Machop will take longer to materialize as an evolution compared with Rattata.

Game Goals, no matter what, a new Pokemon, or a Pokemon needed to evolve to a new one is top priority. Because of the rare candy that can be gained from raids, Battle capable Pokemon are valuable. The easy one for us is Rhyhorn. We have a few Rhydon, but for Raids, they are great and a better one is always welcome because it can help us to earn Rare Candy.

Experience points. When you aren’t finding a new Pokemon, or one to Raid with, go for experience points, using the Pidgey, Rattata, and Ekans class structure.

Catch the fun ones. My daughter loves ponies. Do we ever battle with, or do anything with Rapidash? No, but my daughter loves to see Ponyta evolve into Rapidash. Same with Pikachu, my son loves that little mouse. So, we catch all of them, and he gets to evolve it as often as possible.

Badges matter, not a lot, but they do. They increase a Trainer’s ability to catch other ones. Before we had a Charizard, we caught a lot of Growlith, because it helped us with the Fire Badge, which facilitated an increased chance of catching every Charmander we found.

In Order of precedence:

  1. New Pokemon or required candy to evolve.
  2. Pokemon for battle.
  3. Pokemon for experience points.
  4. Pokemon for fun.
  5. Pokemon for badges.

Remember to have fun, and be safe.

Click here to see my calculations.

Disclaimer: The information contained in this document is provided for informational purposes only and should not be construed as financial or tax advice. It is not intended to be a substitute for obtaining legal, accounting, or other financial advice from an appropriate legal professional, financial adviser or for the purpose of avoiding U.S. Federal, state or local tax payments and penalties.

How to Build your Chart of Accounts

The Chart of Accounts is like a file cabinet for your transactions. There are a variety of reasons to use the information generated by properly recording your transactions. And how you structure your Chart of Accounts can impact not only the presentation of your financial statements, but also the value of the information which you derive from it.

Consider the users

The financial statements are designed to be used by business owners, management, and stakeholders, who could include banks and regulatory agencies. As a result there are a variety of different groups whose needs will need to be considered. So, it is important to strike a balance between detail and common sense. It is possible to go into excruciating detail, but this creates a burden upon your bookkeeper, and increases the possibility of error.

We believe that you should be detailed, but no more than necessary. An example of this is here.

Keep it Simple

As Albert Einstein said, “Everything should be made as simple as possible, but not simpler.”

We also recommend not to stray too far from the standard model, this is because financial statement users have a certain expectation of where things should be within a financial statement. And unless you want a lot of questions being asked, support those expectations.

At Wilson Lake we have worked hard to ensure our clients have a reasonably standardized format for the chart of accounts. This is because as we move from client to client, we don’t want to drop the ball, and if every chart of accounts is different, and every account name is different than we waste valuable time trying to remember which transaction goes where. Every business is unique, but there are a lot of similarities among them as well.

By the Numbers

Account numbers can be a tool for developing your chart of accounts. They are designed to show at a glance hierarchy, and placement within the financials. For most small businesses, this may not be necessary. Because if your account is named “Truck Repairs and Maintenance,” we can pretty easily guess, this is an expense.

Numbers may become useful if there are similar names, but in multiple places. For example:

5200 Truck Repairs and Maintenance 

6200 Truck Repairs and Maintenance

The 5000 series are Cost of Goods Sold, these would be the repairs to trucks that are used in the production of your good or service. Where the 6000 series may be your sales, and that could be new tires for a salesman’s truck. They belong in separate lines, but are similar in name.

For account names, we recommend at a minimum 6 numerals. Numbers would be assigned based upon hierarchy. Some number are more standard:

010000 – Assets

020000 – Liabilities

030000 – Owners Equity

040000 – Revenue

050000 – Cost of Goods Sold

060000 – Operating Expenses

070000 – Non-Operating Expenses

080000 – Non-Operating Income

090000 – Extraordinary Items

The structure is simple, but not at a glance. The numbers are broken up into 2 digit sets. The further to the right you move, the lower within the hierarchy the account is.

060000 – Operating Expenses

060100 – Selling Expense

060101 – Sales Salaries and wages

Keep in mind that this is a simple numeric structure for a small business, and it allows 99 sub accounts at each level 01-99 for each 2 digit string. If there was a greater need for complexity, an 8 digit code may be necessary.

Greater complexity within your organization will require a greater amount of complexity regarding account the account numbers, but that is something that would require more specialized attention.

Devil is in the Details

As much as we want to keep it simple, details can be helpful as well. As an example consider a construction company. They have vehicles that are used for multiple purposes. And the expenses related to those vehicles needs to be monitored. Using the Chart of Accounts can be a powerful and easy way to do this. Consider two separate vehicles, a construction Crew’s Backhoe, and a Salesman’s truck.

The Backhoe will be on the balance sheet, as well as the income statement and at a glance management may want to know how much it is worth, and how much it is costing during a period. Same with the Salesman’s truck.

So, within the balance sheet under Assets:


Construction Equipment

Backhoe – 1

Sales Equipment

Truck – 1

Accumulated Depreciation

Acc Dep – Construction Equipment

Acc Dep – Backhoe – 1

Acc Dep – Sales Equipment

Acc Dep – Truck – 1

Within the Income Statement:

Cost of Goods Sold

Construction Equipment Expense


Depreciation Expense

Dep Exp – Backhoe – 1

Repairs and Maintenance

Repairs and Maint – Backhoe – 1

Sales Expense

Sales Vehicles Expense


Depreciation Expense

Dep Exp – Truck – 1

Repairs and Maintenance

Repairs and Maint – Truck – 1

At a glance, you can now see, these vehicles impact the financials in a variety of ways, and by breaking them out as separate accounts, it is easy to see if one vehicle is costing more or less than others. Also, by making them sub accounts within other areas it is easy to view things as a batch. This way questions can be answered based upon high level or detailed level analysis.

We also want to point out that we didn’t break out fuel by vehicle. This is not an accident. Not every account needs to be explicitly specific. And the needs of your financial statement users needs to be considered for each account.


Remember, Consider the users, Keep it Simple, and the Devil is in the Details. Also, the chart of accounts is a living structure and don’t be afraid of change, if a need arises, consult the other users of the financials and make a change if needed. Businesses evolve, and the chart of accounts should change with it. The more thought you put into it up front the less change will be necessary but that planning reduces the need for change later on, it doesn’t eliminate it.

Best of Luck.

Disclaimer: The information contained in this document is provided for informational purposes only and should not be construed as financial or tax advice. It is not intended to be a substitute for obtaining legal, accounting, or other financial advice from an appropriate legal professional, financial adviser or for the purpose of avoiding U.S. Federal, state or local tax payments and penalties.