Self-Devpt. Nuggets™

This article explans – with practical examples – how developing in-house expertise in automation of Microsoft Excel.can dramatically boost productivity – and profits – in your company.. Microsoft Excel is a powerful tool for analyzing and presenting information.

Too frequently however, many users – individuals and organizations – fail to realize the extensive range of functionalities offered by this application. They therefore decide that a need exists for a special application that will do things like:” automatically analyse, manage a database, chart and print reports of “ accounting, financial or other data for them in a pre-defined format etc.

Above: Video tutorial for the advanced version of my Payslip Generator purchased and used by companies within and outside Lagos.

Their often-erroneous assumption is that the desired functionalities are not obtainable in existing standard spreadsheet applications like Microsoft Excel, which they already have. So they engage a software developer to design a standalone windows based application to do those tasks.

The Problem

That’s all well and good – if they can afford the huge costs that often result from such acquisitions. For instance, with the requirement to enforce the software anti-piracy laws, many organizations must get a licence for each copy of software to be put on every one of their PCs. So, imagine a company that has over 170 managers and maybe 1000 staff that use PCs. That’s a lot of money!

But then such big companies might be able to afford it. What about the smaller businesses that get into such expenses because they believe they have no choice?

Even it you had such money, why not spend it more prudently and get greater value for that which you spend? That is after all why one runs a business – to make (more) profit!

The Alternative Proposed

Above: Video demonstration of the latest upgrade version of my Excel-VB Driven Livestock Feed Formulation Software. This has been on sale since 2007, as an complement to my popular Feed Formulation Handbook. It has been purchased by buyers within and outside Nigeria. Get details here.

The question then becomes “Is there any other way to achieve the same objective?” My answer is that there is! The alternative is one that puts the user in a position where inhouse custom automated spreadsheet solutions can be developed using standard spreadsheet applications like Microsoft Excel.

This way, your organization would only then have to invest in employee training and time, to have appropriate and convenient solutions developed by one of your own staff!

It also means that if/when changes occur in your processes (which happens all the
time), you will only need to get one of your in-house experts/ developers to effect modifications to accommodate such changes. Only when a really complex and major need arises, would you then have to consider going out to get a developer or pick a package off-the-shelf.

Additional Advantages Of The Proposed Alternative

Most of the advanced functionalities/automation that users desire (or need) can be easily achieved in Excel using its extensive range of functions/tools coupled with its powerful programming language – Visual Basic for Applications (VBA) in Excel.

Above: Video demonstration of my Excel-VB driven Business/Personal Contacts Data Manager

There are custom pre-programmed Excel spreadsheet applications solutions – some called Enterprise Information Systems – that have been developed using VB for Excel, which most uninitiated users would insist could not have been produced using Microsoft Excel.

Microsoft has described Visual Basic for Excel as a complete development environment, consistent with the stand-alone version of Visual Basic, and shared by all Office applications. Having used Visual Basic myself, I could not agree more.

Certain kinds of scenarios, (some listed below) if existing in an establishment, might require an organization to seriously consider having its staff trained on “Mastering Microsoft Excel”.

1. Management of routinely collated and analysed data in spreadsheets is still done manually, even though the tasks carried out on such data tend to be fairly repetitive. Through training, they could learn how to use Visual Basic Macros to automate daily simple and/or complex tasks.

2. In using Excel, they often go the “long way” via the standard menu hierarchies to effect specific commands. This will detract from their productivity while using the application. Through training, they could learn numerous shortcut techniques, tips and tricks to make more efficient use of Excel. This would result in drastic productivity improvements in spreadsheet use on their jobs.

3. Not many of them can confidently handle Excel and expose its many capabilities to other colleagues or new entrants. Through training, they could gain confidence needed to educate colleagues etc on the use of Excel. In a short time, the knowledge will spreads to others, with positive benefits to the business.

For best results from training, a Visual Basic for Excel Micro Information Specialist (i.e. someone who not only knows VB for Excel programming, but can also understand your processes and provide relevant examples, solutions etc) should handle such training.

Apart from teaching them advanced ways to use Excel, he could also teach your staff how to develop/maintain customised automated spreadsheet packages to solve their routine data management, and reporting problems. (Click here to request a custom Training Course/Coaching program).

Justification For Custom Automated Spreadsheet Solutions

I worked for over seven years in a large, multinational manufacturing organization. Within that time I had become widely recognized in the company for developing various custom automated spreadsheet solutions that dramatically facilitated “raw” data collection/analysis from the production and packaging processes. Quite often, large corporate, network-based software applications do not address “shop-floor level” data collation/analysis challenges that individual employees encounter daily.

Above: A Video Demonstration (No Audio) of a Custom Financial Reporting Application I Built For A Medium Sized Hospital. Click here to read the interesting story of how I won the project to build this app.

This problem frequently exists because corporate-level software packages are designed with a broad user group in mind. As such, they do not cater fully for the variety of users’ peculiar needs arising from differing operational processes.

For instance, even though Nigerian Breweries and Guinness Nigeria Plc both brew “beer”, the way they individually collate, document and analyse records from the beer production process for management reporting differ significantly. This is not just because different people run the companies, but also because their raw materials are different, and they process their beers differently!

Now, the two companies would ideally want to produce reports that are easy to compare with those from other brewing companies’ (or even their sister companies) within or outside the country. They would therefore want data from the lowest levels of the production processes, to be channeled – intact – to the management reporting level in what should be a fairly standard format. That is of course already being done in both companies on a regular basis.

However, it’s the “HOW” of doing it that poses a significant challenge, leading to questions like those below:

1. How much effort and time does it currently take them to get that data? How much effort and time should it take?

2. How many employees are currently involved in getting the data? How many really need to be involved if the process was efficient?

3. If one employee collated the data this month, and another next month, would there be a difference in the reliability of the resultant summaries posted into the networked corporate database?

The above, are the kinds of questions that need to be asked in order to check for existence of data collation/handling inefficiencies in routine data, management processes in an organization. Let’s face it, if accurate data were not being fed upwards for use by management, the effects would not only be inaccurate interpretations of the business’ performance, but also a danger of its possible collapse in the longer term.

Summary Of Benefits

The development of custom automated spreadsheet solutions would effectively bridge the existing gap between manual data collation done by employees, and the automated data handling by corporate software applications.

Being able to develop such solutions will give oganizations the capability to produce simple yet reliable remedies to data management and reporting problems as the need arises.

The developed packages are also likely to be more effective – and relatively inexpensive (having been produced by an employee – who “understands” the process) compared to those done by outsider(s).

Cost conscious organizations can save huge amounts of money by empowering their employees to develop a mastery, firstly of Microsoft Excel, and then of Visual Basic for Excel. Note that true mastery of Microsoft Excel is imperative, if one is to be effective at developing robust and reliable automated spreadsheet solutions.

Examples of Specific Areas of Potential Application

The following are specific areas for which automated spreadsheet applications could be (and have been) successfully developed. Note that by virtue of their being customized and inexpensive, automated spreadsheets applications can also be easily acquired for use by individuals – on their jobs or for personal use (see item 3. below):

1. A VB for Excel Enterprise Information system could be developed to retrieve, summarise – and present graphically or otherwise – data from an external database like Access, Oracle etc.

2. A custom automated Excel spreadsheet application could be developed to

a. Collate raw “process” data, which will then be inputted into a larger application or corporate database.

b. Collate and analyse process or other data for use – by individual employees on special projects; by departments for internal checking/controls, or in performance monitoring/testing etc.

3. A nifty VB for Excel automated spreadsheet package could be developed for specific application by self-employed individuals (or small businesses) e.g. an automatic sales recording/invoice generation spreadsheet

BUILT-IN EXCEL SOLUTIONS DEVELOPMENT

Self-Development Academy’s Excel Heaven™ is the FIRST provider of Preprogrammed and Custom Spreadsheet based solutions in Nigeria. We offer Workbook Auditing/Optimisation and VBA Automation; Custom Spreadsheet Software Development; Sales; Job-Based Spreadsheet Coaching; and Consulting/Advisory Services on effective application of spreadsheets for business use.

See video demos at http://www.youtube.com/user/TKSolagbadeSDAc

Visit our Excel Heaven mini-site at : http://www.excelheaven.spontaneousdevelopment.com

NB: Our Custom Automated Excel Visual Basic applications are provided as-is with no warranty of any kind. We also cannot assume responsibility for either any programs provided here, or for any advice that is given since we have no control over what happens after our code or works leave us.

=======

Invite Me To Speak

If you’re reading this after 31st March 2013, I would have begun travelling slowly across West Africa as a Location Independent Entrepreneur. If you’re in West Africa click here to invite me to speak to your members or group on this topic.

Above: In May 2012 he was the Guest Speaker at the Centre for Entrepreneurship Development’s Annual Semester Entrepreneurial Lecture at Yaba College of Technology in Lagos. (Click here to download the PowerPoint slidesshow and mind map I used for that talk). 

If you’re in West Africa click here to invite me to speak to your members or group on “How to Use Excel-VB Automation To Boost Your Profits.”

=======