[This 2006 writeup was my very first Excel-Visual Basic related article published online – and it offers experience based insights into the thinking required to succeed at using custom spreadsheet automation to boost data recording, analysis and report generation for timely, accurate and profit-boosting BUSINESS decision making]
So Who Needs To Read It?
You will find this article of considerable reading value, if you belong to at least ONE of the following spreadsheet user categories: Owners of Small Businesses – Restaurants/Bars, Hotels, Hospitals, FACTORIES, Consultants etc; Decision Makers/Job Holders in Corporations – Materials Managers, Sales/Marketing Analysts, Financial Analysts/Accountants, Project Engineers; And Anyone desiring to make BETTER sense – and use – of data!
Spreadsheet Automation Facilitates EFFORTLESS Data Handling, Analysis & Report Generation!
Sometimes people don’t really care about learning(or using) all the “cool’ advanced functions, formulas and techniques in the spreadsheet application they use for their work. They just want to punch in their relevant raw data – at any time – and immediately see the computed results generated by the spreadsheet in a manner that can facilitate their IMPORTANT decision making.
At times, they would prefer to click a button that says “Print ABC”, or “Print XYZ” report, instead of having to crawl all over the huge spreadsheet(and get “lost” every now and then), to highlight and print different report pages. Using a custom built data entry form to make data entries into 14 different cells in different parts of a table(at the same time/with one click) would, for them, be “heaven” compared to making the entries one at a time.
This article is meant for those individuals/businesses who sometimes experience a need to use their spreadsheets in the (somewhat “impersonal”?) manner described above, to get the results they want. That need would often arise out of their lack of requisite skills to get the spreadsheet to behave the way they want – or limited time to devote towards incorporating necessary automation to make it do so.
In my article titled You Can Increase Your Profits Without Changing Your Prices, I ended with the following summary:
———————Start Of Excerpt———————
If You Remember Nothing Else, Remember The Following:
1. One good way to maintain and/or significantly increase your profits without raising your prices, is to reduce your Variable Costs(VCs).
2. You can reduce your variable costs by marketing more efficiently (getting more customers at lesser cost, AND maintaining them at lower expense). I once read an article that proposed a new parameter COCS: Cost Of Customers Sold or Served). This could be adopted as a Key Performance Indicator(KPI).
3. You can also reduce your variable costs by innovating more(i.e. developing greater efficiency in your routine internal operations and/or product/service delivery). That way, you would be able to produce/deliver more products and/or services with less effort, in less time, and using less resources. All of these would imply LOWER expenses/costs, leading to INCREASED profit retention per unit of product/service sold.
4. There is saying that: “You cannot manage something, if you do not measure it. Nor can you measure it, if you do not record it”. Spreadsheet tracking will help you conveniently implement and sustain the process of monitoring, controlling and/or reducing your VCs. You will need to do this so as to constantly evaluate progress of your VC monitoring/control and reduction initiatives.
———————End Of Excerpt———————
A DIFFERENT Type Of Automation
The approach to spreadsheet automation that I refer to, is one that seeks to provide functional spreadsheet automation alternatives for ANY spreadsheet user task(s). Typically, solutions like this would require the developer to study existing manual data recording, analysis and report generation systems, then plan – in consultation with users – for incorporation of automation into the spreadsheet to replace them, where possible.
Custom spreadsheet solutions which survive long after the developer has “left the scene”, are often those which users accept because they discover it – among other benefits – makes their work quicker and easier to do. That is why the best person to develop such solutions tend to be one who works in that area, and is therefore familiar with the way the manual system operates. S/he would have an “insider’s” perspective of the best way to introduce automation other users will readily adopt – even as it solves the identified problem(s).
In effect what I am saying is that companies which get the most value from spreadsheet automation(including using it to avoid expenditure on less adaptable commercial off the shelf applications) will be those which empower their users to routinely generate “in house” solutions. In such companies, you will find that only when the requirement becomes considerably specialised or complex, does the IT department get called upon to develop or purchase software solutions for user departments or functions with significant data recording and analysis needs.
So, (when considering the automation I speak about) do not think about spreadsheet documents containing one or two click-able buttons that allow a user print a page or copy some cells from one sheet to another. Instead, I want you to picture an application(or Entreprise Information System) that customises the appearance of your spreadsheet workspace(to take advantage of maximum screen capital available on your PC), and offers you custom “floating” data entry forms.
In addition, visualise it having dynamic query/report drop menu interfaces, and a variety of custom buttons: for navigation(within and between worksheets), printing, data EXPORT (as PDF documents or spreadsheet files), saving, auto-data filtering, auto-charts plotting, auto-backup of files, user login authentication (for documents with sensitive or confidential content) etc.
To experience the type of automation I refer to, you can watch FREE demos of commercial Excel VB driven spreadsheet applications I’ve built for client at www.excelheaven.biz.
How A Well Known Corporate Multinational Used Spreadsheet Tracking/Automation To Repeatedly Cut Spending – And Increase Profits
During my graduate training(while in paid employment), I was redeployed from Guinness Nigeria Plc’s corporate headquarters training office(in Lagos) to the Benin brewery training department (in Edo state), where I was assigned the – additional – job of using a custom Lotus macros driven Variable Cost Analysis spreadsheet application to generate brewery reports for dispatch to headquarters.
The assignment was not an accident. Before being redeployed to Benin brewery, I had been involved in “validating” the numerous complex formulas in the custom Lotus 1-2-3 spreadsheet application during its development. The author – Richard Chambers – was at the time in charge(as Training Coordinator) of training new entrants, and upon discovering my keen interest in learning, often gave me his laptop to “proof” formulas, links etc. It was he who had told his counterpart in Benin brewery(Joe Sheehy) that I could help out with a problem they were having using the application. And I did resolve the problem – resulting in my subsequently becoming responsible for the reports collation using the application.
Variable Costs Control/Reduction was a strategy that worked extremely well for the company. Most memorable for me as a brewer, was the manner in which huge monetary savings/profit gains were made by successfully implementing variable cost reduction initiatives. For instance, a brewing ingredient switch was made to a more readily available local alternative which was many times cheaper, resulting in phenomenal savings and progressively increasing profit earnings even though the price of beer produced was not raised. This practice was routinely applied across the brewing and packaging processes in line with a well thought out plan.
To elaborate further: A one-page Variable Costs Analysis report sheet automatically generated by the custom spreadsheet application I earlier described, was a powerful tool used by top management to quickly assess performances of individual breweries. Among other benefits, the report made it quite easy to realistically compare sister breweries in different locations(even across countries) based on common denominators. Apart from summarising brewing/packaging materials in two groups –
“Over-used/Over-spent”(Losses) and “Under-used/Under-spent”(Savings) – on a monthly basis based on data entered, the program also automatically plotted charts showing trends over a 12 month period.
The VISITING head of the technical function only needed to look at the most recently plotted point on the chart (relative to preceding ones) for a Key Performance Indicator like
Cost per Hectolitres brewed(One Hectolitre = 100 Litres), to know if the brewery had stayed within the approved upper limit of spend(plotted as a straight line target across the same period) or not. Discussions would then take place based on identified “Exceptions”(which could be “good” e.g. savings made or “bad” e.g. monetary loss due to materials over-used), and “Actions To Be Taken” to correct or maintain observed performances agreed upon.
Spreadsheet Tracking As A Crucial Element For Business Development
There are ways you can use spreadsheets to record and track materials usages/stocks, product sales, and other business data such that discrepancies will be easily detected when they do occur. The use of spreadsheets when properly done, can help to uncover the cause(s) of “losses” in virtually any aspect of a business operation.
1. The Pareto Principle – Using spreadsheet tracking, you can easily apply the Pareto principle in deciding which of your income sources and expense channels(i.e. products and services sales) to focus on in order to maximize profits. Considering that you are most likely to use the same marketing/sales resources to serve your customers, it only follows that if you focus on your biggest margin selling products/services, you will get increased profits at more or less the same cost.
One Possible Application: Plotting a pie chart based on income contributions from all your products and services(daily, weekly or monthly), and reviewing the automatically computed percentages/visual pie slices, can give very illuminating insight.
The 80:20 Pareto principle is based on Pareto’s theory that 80% of the results one gets in a particular endeavour will be mainly due to 20% of areas to which one has applied efforts. In business this principle has been found to be true. Your data, properly converted into appropriate performance indicators, will show you where your largest margins come from. You can then channel more time and effort in that direction.
For instance if a company had five drink brands in the market but notices that Brand A, which has a profit margin of at least twice the others is in greatest demand, they could (a) focus production efforts on that brand, so that more bottles go out to trade (b) Apply Best Practice/Continuous Improvement initiatives that would result in lower costs of producing each bottle of Brand A so that even though the market price remains fixed, the company is able to earn increasing profit margins per bottle.
2. Sourcing Capital – For Expansion or Startup. You can make it easier for banks and prospective investors to back you financially and/or take the decision to buy into your business. Those already running their businesses will know that banks like to see detailed business records that show in real terms all aspects of a business’ performance. Without detailed and comprehensive spreadsheet tracking, it might be difficult to show this. Agreed there are software applications that capture most of these. However, sometimes, you want to highlight certain scenarios or trends in a way that an off-the-shelf application cannot accommodate due to the uniqueness of your need. It is in this regard that the use of spreadsheet tracking becomes relevant.
The intelligent use of spreadsheets, combined with the application of the 80:20 rule will help to achieve the foregoing and more. One does not need any expert to start doing these things today. Just take the decision to start keeping daily, accurate records and commit to analysing them. You’ll be surprised to find that you will soon become much more aware of how you’re doing and what aspects of your business you should focus on more to get better returns for your efforts – and you will be able to confidently supply verifiable/authentic financial data to back up any claims you make about your business performance to bankers or prospective investors.
Deciding What Spreadsheet Application To Use
This would ultimately be up to you. The big “fight” has always been between Lotus 1-2-3 and Microsoft Excel. I started out with Lotus 1-2-3 back in 1993 and learnt Lotus Macros programming(via self-tutoring). I eventually used this skill to develop – in my free time – various custom spreadsheet solutions(that were formally adopted for use in the departments I worked in as a brewer/manager in Guinness), before switching to Microsoft Excel in 2001. Subsequently, I developed my Excel Visual Basic spreadsheet programming skills (also via self-tutoring), because the company had chosen to adopt MS Office during the roll over to year 2000.
I believe using either of these two applications should not pose any problems for implementing your spreadsheet automation ideas. This is because both have always been “friendly”, towards making it easy for users to get more functionality out of them by way of custom programming.
You can choose to learn how to do it yourself – or call in someone(an Excel VB Solutions Developer for instance) who knows how. Again, this would be dependent on your purpose, how proficient you are, and/or how much time you have at your disposal. Ultimately, even if someone develops a custom application for you, it should happen with YOUR guidance at every point to ensure that it does EXACTLY what you want it to do, and that you can easily – with your developer’s support/coaching – learn how to make modifications to it in future, without needing to call back your developer.
This last point in my opinion is ONE major benefit you must seek to extract, if you choose to engage the services of a developer. S/he should be able to help you develop (in-house) expertise needed to maintain the application AFTER s/he is gone. If you fail to ensure this, all your cost-savings from using the application might end up being spent paying the developer to maintain the application over time in the future!
From this point on, I will refer to only one of the above mentioned applications, because it is my preferred work environment. That is Microsoft Excel. I believe users of other spreadsheet applications will be able to adapt whatever I say from here for use in their own peculiar environments.
Automating Your Spreadsheet Document : What is Excel VB?
Microsoft Excel is a powerful tool for recording, organising/re-organising, analysing and presenting information. By Excel VB, I refer to Excel VBA – where VBA means Visual Basic for Applications(VBA). VBA refers to the highly extensive and flexible macro programming language developed by Microsoft for use in their MS office applications – Word, Power Point, etc.
VBA is therefore different from the Standalone Visual Basic program used(by professional programmers) for developing commercial quality software applications – though it borrows many of the latter’s features. Think of Excel VBA as being the standalone Visual Basic software, built into Excel for the benefit of Excel users who are not necessarily programmers, but who are keen to exert more control over the application.
So, Excel VB offers any interested users the necessary tools to make the application deliver more functionality. The final product is still an Excel document, but with extra functionalities added using VBA.
Why Excel Visual Basic – and NOT Visual Basic?
1. I always answer this question by asking the following question: What would be the point of “Killing A Fly With A Hammer”?
Let me elaborate. If a method is available that allows us to achieve the same desired result(s) at LOWER cost, with LESS effort and in LESS time, why should we fail to adopt it?
Certain IT persons insist that developing executable applications using the standalone Visual Basic programming language is “better” as it does not limit the user to a particular user application software environment. I concede that this might be a valid point under certain conditions. However, I point out that there are many users who have peculiar needs that do not necessarily require complex solutions.
A lot of people today simply want to get their data recording, (re)organisation, and analysis for decision making done quicker and with less effort. They also want to spend as little money as possible to do this. In other words, they want a cost-effective solution that gives them independence from the solution provider in the long term – without requiring them to undertake laborious skills acquisition immediately.
This category of everyday users of technology described above are the ones I believe need Excel VB solutions of the type I describe. One expects that some of these users will over time develop an interest in acquiring advanced skills needed to develop their own solutions in future – which is why I also encourage them to do so, possibly via self-tutoring, like I did.
2. Another question I ask, in answering the “Why Excel VB?” question is : “Why re-invent the wheel? ”
My experiences(and those of others who favour the use of Excel VB like I do), confirm that to organise, and analyse data for (financial/management) report generation and decision making, you will save hundreds of hours using already in-built, pre-programmed Excel functions compared to a situation where you used Visual Basic proper.
All the functions needed to achieve the above purposes already exist in Excel, so that you don’t have to write them all from scratch as would be the case if you were to use Visual Basic.
3.It works even when you lack “In House” expertise
For the purpose of creating custom financial and business management solutions that solve your identified problems, and give you control/ownership, without burning a hole in your pocket, Excel VB offers an amazing variety of possibilities, in the hands of an adequately skilled user.
In addition, an Excel VB developer( who in many cases will tend to be a user turned developer, and is therefore likely to easily see things from your perspective) – unlike a programmer – is more likely to be positively disposed to working with you to ensure the application meets your practical needs. S/he will readily understand that the final application is meant to help solve a real problem(s), and will therefore build it to match those expectations.
It’s not enough to have a professional with the technical skills to solve your problem. s/he also needs to have the right attitude and background/experience – else the relationship will not work. This is where an Excel VB resource is more likely to add value to you. Most Excel VB Developers have backgrounds in management, accounting, engineering and other fields, which further equips them to be useful to you in “thinking up” better ways to apply your spreadsheet automation to get the most value for your business. You therefore need to choose the right developer, with the appropriate background to match your needs.
I believe the foregoing are compelling justifications for choosing Excel Visual Basic over Visual Basic.
Get Maximum Returns On Your Investment In Spreadsheet Automation By Developing “In House” Expertise
Organisations can deliberately expose their employees to learning events(or self-help tutorials) on spreadsheet solutions development. Such employees can then be challenged to develop in-house solutions that effectively address the business’ peculiar data analysis/report-generation needs as they arise.
A person with proven competence in this area CAN become “notorious” for developing spreadsheet applications, which eliminate drudgery from the process of using MS Excel to handle large amounts of data. Typically, work that takes hours or days will suddenly take minutes or seconds to finish.
The claims I make above are NOT exaggerated. They are in fact based on my personal experiences doing the foregoing (a). As an manager in the challenging, fast-paced manufacturing work environment of a corporate multinational (b). As a solutions developer for individuals and businesses who use MS Excel for their work.
I am keen to encourage individuals and organisations to explore using advanced MS Excel formulas and functions, in conjunction with Excel VBA programming, to develop customised spreadsheet applications that will solve typical problems end-users encounter daily in trying to record/analyse data and generate reports.
The savings – from using your “in house” expertise – in terms of money and man-hours alone, will quickly justify the investment you make in “developing the needed skills” – especially, when you compare what you spend with the cost of purchasing a commercial software application – or even engaging the services of an Excel VB developer.
Summary
The key imperative for most people who handle business data is often “how to use it” to monitor – and where feasible – control performance i.e. they study/anticipate trends in a way that helps decision making towards ensuring REPRODUCTION of desired results, while minimising – or eliminating – undesirable ones.
You CAN transform the way you manage/analyse data(or generate reports) in your business today – and get more usable benefits to the extent that your profits increase. One major step you can take towards achieving this purpose, will be to intelligently use spreadsheet automation to improve data handling/analysis and report generation systems in your business operations – while empowering yourself and/or staff to acquire skills needed to maintain the developed spreadsheet solutions for the future.