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