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

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 email FREE demos of sample automated Excel VB driven spreadsheet applications like (1) a Training Tracking Database™, (2) a Five(5) Year Income & Expense Monitor™ or (3) an Export Packing List & Invoice Generator™ to yourself using the file mailer on my website.

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

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 email FREE demos of sample automated Excel VB driven spreadsheet applications like (1) a Training Tracking Database™, (2) a Five(5) Year Income & Expense Monitor™ or (3) an Export Packing List & Invoice Generator™ to yourself using the file mailer on my website.

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

---------------------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 email FREE demos of sample automated Excel VB driven spreadsheet applications like (1) a Training Tracking Database™, (2) a Five(5) Year Income & Expense Monitor™ or (3) an Export Packing List & Invoice Generator™ to yourself using the file mailer on my website.

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

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 email FREE demos of sample automated Excel VB driven spreadsheet applications like (1) a Training Tracking Database™, (2) a Five(5) Year Income & Expense Monitor™ or (3) an Export Packing List & Invoice Generator™ to yourself using the file mailer on my website.

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

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 f