Using Microsoft Excel To Supercharge Your Profits
By Lorry Rifkin
Microsoft Excel is omnipresent in the business world today. It is used to help run millions of business. Users have all levels of training from formal classes to learning from a fellow employee’s hands on training.
Because it’s so easy to start using Excel and seeing numbers result from that use, little thought is given to using these spreadsheets as a competitive weapon to drive productivity and profitability by integrating them in a company’s comprehensive business plan.
In my 25 years of using Microsoft Excel several reasons constantly surface, that prevent this powerful business tool from being used to develop smart systems that supercharged profitability.
- A lack of thorough training. Excel is a complex tool. Complex tools require complex training to maximize their use. Train your people up, don’t dumb them down to the lowest level of user.
- A focus on learning features versus learning what feature gives the most productive result.
- The failure to integrate business spreadsheets directly into a company’s accounting, CRM and other core business programs by using ODBC technology or import export routines.
- The fear of using VBA macros and code to automate, test for errors, and improve efficiency.
- Knowing when to abandon a spreadsheet for a more productive or effective tool.
Consider the 2 system definitions (slightly condensed) below from Lee Thayer a leadership Guru.
Dumb system – Output of the system is unreliable and marginally competent.
Smart System – Output elevates the performance of competent people
How many times have you built and used a spreadsheet only to find out latter that the results were wrong? The usual cause is you built a dumb system.
This does not mean your mission critical spreadsheets have to be complicated to deliver accurate results or improve productivity. But it does mean the process to build them must result in a smart system.
How do you build a smart spreadsheet? By using a consistent step by step process beginning with:
1. Asking why do you need to develop the spreadsheet with questions like these below.
- Is the information not available in another source system?
- Is it not formatted correctly?
- Is it not sorted the way you like?
- Is there data but not the calculated fields that you want to see?
- Do you want a “what if” model, that works using variable input?
2. Determine the desired objective of the spreadsheet, the source of the core data, and where and how the results will be used.
Most spreadsheets are used as a database, a model, or analysis of some set of data to produce some form of information, or a combination of both.
A typical model is a financial projection of some type.
One often overlooked point is to create a test case of data that delivers a known outcome so you can test the model when you are done with development.
3. For reliability and accuracy determine if the base data can be loaded electronically thru ODBC or an import from another system.
Determine what Excel feature or spreadsheet design can deliver the desired object with the least effort and highest accuracy.
Many users stick to the tools they know and don’t use the tools that work best.
Ask yourself or your employees if they know:
- When would you use a subtotal versus a pivot table?
- How to use data filtering and sorting to quickly find data?
- How to design a financial model’s formatting independent of the data so you can quickly change data sets and model different assumptions?
- How to collapse rows and columns to have one model present summary and detail information?
- The advantage of R1C1 notation for cell references instead of A1 notation?
- How to protect cells, sheets, and workbooks from user overwrites?
- How to test to make sure the model works?
- How to record a simple VBA tool that marks the path on all printed documents so you can find where you saved them?
4. Determine a consistent naming and directory structure so you can find and determine what a spreadsheet does by the name.
How much time is wasted finding where you put that spreadsheet model you built last year?
Decide on a calendar or functional schema. Develop a dictionary of abbreviated business entities, segments product lines etc. Require all documents to be printed with a path.
5. Documentation of what the spreadsheet is designed to do and how it is to be used.
This is critical to add data reliability and the ability to audit. It is the step most often omitted. Add an instructions tab and a notes tab to document important items within the spreadsheet itself.
Created with a consistent methodology, your company’s spreadsheets can help improve workflow and make you more money. It requires teaching and training a few high power users in your organization and then having them be the trainers for others.
Oak Hill Business Partners is a professional services firm serving small and mid-sized businesses. Oak Hill provides Growth Management Services, Mergers & Acquisitions, and Financial Institution Services. Our deep experience allows us to work “where strategy meets execution” – our strategy and our execution are based on years of experience doing the work.
Lorry Rifkin is a Partner with Oak Hill Business Partners specializing in finance and operations. He has more than 25 years of experience working with businesses in many different industries. His background includes finance, operations, process improvement/cost savings, technology integration, and turnaround management.