Companies spend lots of money on connectivity, hardware, and software in order to compete in today’s information age. We’re not all massive enterprises like Amazon, however, with millions of dollars and dedicated Research & Development Departments to handle data analysis. But that’s okay. Even small and medium-sized businesses can analyze their information to make better business decisions. And that can mean the difference between purchasing stock your manager has a “gut feeling will be the next big thing” and seeing what your customers are showing you they want or need so you can give them more of it. Plus, it won’t cost a thing.
You have the data. We’re going to show you how to get actionable insights from it using tools you already have. These tools, as mentioned in the first sentence, are a means to access and collect data. Connectivity allows you to communicate with the internet and your satellite locations; servers (hardware) provide the central processing and storage capacity; databases (software) hold all of the data entered into your information systems; and personal computers act as a platform so you can interact with all of it. Technology allows your business to collect enormous amounts of information, and what you do with it can be the difference between gaining a competitive advantage or…. not.
3 Rules for Effective Analysis
- The first step to leveraging your data is to make sure it is being entered accurately. There is often a disconnect between those who enter the data at a company and those who use it to make decisions. While it’s fine that not everyone understands how each piece of information is used and how much it weighs into various decisions, it is critical that everyone sees the capturing and handling of the data with the same level of importance. If you have employees who think there is no point to what they’re doing, then they are far more likely to get sloppy or careless with the information. Keep in mind the old adage “garbage in, garbage out.” Having inaccurate info will prevent you from making effective decisions. And this leads nicely into Rule #2.
- Decide what data you need, and use it. There’s a dangerous temptation to collect every piece of information possible, thinking it may be necessary somewhere down the line. What’s important to remember, however, is that you will be paying for all of this, whether it’s in staff time collecting and updating the data and/or storing the data. For example, it may seem like a good idea to capture your customers’ birthdates. But ask yourself what you’re going to do with that information. If you are not planning to send out birthday cards or special offers to help them celebrate it, then perhaps you do not need to collect this extra PII – personally identifiable information – especially when having it can mean paying for a higher level of data security as well.
- Once you know what you want to capture, and your staff knows why it matters, the next step is to identify a baseline or industry standard to measure against. As with deciding what data to collect, choosing key performance indicators, or KPIs, can easily become overwhelming. Different businesses measure different things depending on their goals for growth and how they view success. And goal-setting itself is a much larger discussion. So we’re going to assume you have a mission and a vision for your company and that you know what you want your company’s legacy to be. Your KPIs should feed into this. One way to figure out the most valuable KPIs for your business is to identify the areas that are associated with the highest levels of revenue or cost. Making changes in those areas should have the most impact on moving your business where you want it to go, and measuring your performance against industry standards will give you context to avoid tunnel vision.
Now we can begin to look at the data and find the variables needed to measure our KPIs.
How to Analyze like an Expert
While consultants are great and outsourcing can be the answer to a lot of business problems, sometimes what we need is right at our fingertips. If you have Microsoft Office, you already have access to a very powerful analytical tool: Excel. Knowing how to use features beyond the AutoSum and Average functions can seem daunting, so we’re going to focus on 5 functions you can put into use right away.
◊ NOTE: For these first two Excel functions, you will need to install the Analysis ToolPak add-in. You can ask your managed IT services provider to handle this for you. To install it on your home computer, go to File -> Options, and click on “Add-Ins.” Make sure the dropdown menu at the bottom shows Manage: “Excel Add-ins” and click “Go.” Click in the box next to “Analysis ToolPak” and click OK. (If the box is already checked, then you already have the add-in activated.) You may also see an option for “Analysis ToolPak – VBA.” This version allows for programming in the Visual Basic language which developers often use for building functions and creating macros. Most of us don’t need this.
Once your add-in is enabled, additional tools will be available in Excel under the “Data” tab in the “Analysis” section. You will see Solver, which helps find the optimal solutions for different types of decision problems. Say you want to know how much you should spend on advertising. You can use this tool to see your projected amount of profit based on your projected amount of advertising, and you can customize the results with constraints—setting a max advertising spend, for example. The “Data Analysis” group includes a variety of functions to help calculate different quantitative statistics. One of those is called Regression, which can look at a dependent variable and see how correlated it is with independent variables. For instance, if your business has multiple regions and you want to see the relationship between advertising expense and sales revenue, you could use Regression analysis to see the strength of their linear relationship.
The last three tools we’ll discuss here are available in Excel without having to add the Analysis ToolPak. Under the “Data” tab in the “Forecast” section, inside the “What-If Analysis” drop down, there is a function called Goal Seek. This allows you to set a goal in one cell and have Excel figure out how much a different factor (cell) has to change to reach that goal. For example, let’s say we’re looking at ROI on a rental property, and we want to improve that ROI by charging more rent. By using Goal Seek, we can ‘Set cell’ as the cell our ROI is currently in, enter the ‘To value’ that we want the ROI to become, and select the cell that the current rent amount is in in the ‘By changing cell’ field. That will tell us how much rent to charge to get our desired ROI.
Another tool in that same “What-If Analysis” dropdown is called Data Table. This can be useful when you want to try out different values in a formula and see their results in a table view. For instance, if we purchase our rental property with a down payment at a certain interest rate but want to see what the ROI would be if we put more or less down at a greater or lesser rate, this tool can calculate all that.
Next to the “What-If Analysis” button, you should see Forecast Sheet. This tool can help you make projections if you have historical data to start with. Sticking with our real estate example, if you have the last 10 years’ property tax data in a spreadsheet, you can use this button to create a forecast for the next 5 or 10 years. Obviously real estate has a lot of factors that a simple Excel tool will not be able to incorporate, such as economic indicators, climate impacts, and generational behavior trends, but it can give you a quick idea based on the history you have.
As always with business advice, we advise you to consult with professionals before taking action. These tools can be very useful, but you may not know to factor in certain costs or regulations that a professional will know and handle properly for you. This information is designed to be a starting point only: a way for you to play with scenarios and help see relationships in the data that you may not have considered. It should be quick and easy to try out, it’s free (provided you have Microsoft Office), and ideally, it will spark ideas or lead you to questions that you’ll discuss with your colleagues and trusted advisors.
Like this kind of business insight? Sign up to receive our monthly newsletter.