Self-Devpt. Nuggets™

My new Career Development book titled “The Workplace Warrior™: Using Smart Spreadsheet Automation for People & Process Performance Enhancement” will showcase real life memos (like the one below) regarding my informal Spreadsheet Solutions Developer role in Guinness. [“To: Mr. TAYO SOLAGBADE. From: PRODUCTION MANAGER’S SECRETARY (L). Date: 7th May 1998…]

No one asked me to do it. I chose to do it of my own free will, where ever and whenever I saw the need for it.

I did not get any company award for using my spare time, over the course of 7 years, to develop a wide range of spreadsheet automation solutions, to boost data handling and report generation in different departments.

But I did get lots of commendation and recognition, and was rewarded with rapid career advancement opportunities.

It all began one year after I joined the company as a graduate trainee and was redeployed to Benin brewery from Lagos, where I had trained – as a brewer.

Within a matter of days I was soon relating one on one with senior managers on calculating company usage rates and other performance parameters. (I’ve written several articles about it all in the past. They will all be compiled into the book volume I’m now working on).

I had learnt from working with my expatriate training manager, Richard Chambers, to build elaborate, nested formulas into Lotus 1-2-3 spreadsheet cells . Lotus was the app we used from 1994, when I joined till 2000, when the company moved from Lotus Smart Suite to MS Office).

It was my ability to do the above, and my passion for data analysis and reporting, that helped me quickly come to grips with the complex array of performance indices that needed to be computed daily, weekly, monthly and for the year.

The work involved helped me appreciate why Chambers was developing an automated spreadsheet application to enable fast and reliable reports generation by breweries.

And that made me sensitive to identifying opportunities to do the same when I got to the breweries.

Now, he never sat me down to give me lectures on spreadsheet use. Instead my interest made me spend time watching him at work every day I got the opportunity.

Since I’d already gotten basic computer appreciation training before joining the company, I was able to then tutor myself, to do what I saw him do.

As time went on, Chambers would give me his laptop, and ask me to check the many nested formulas in massive spreadsheets he’d built into the brewery reporting application.

Following my training, Chambers had recommended to his colleague in Benin brewery that I could help them with problems they had with his app. After I did so, news had spread to Lagos, and so I got called up whenever they had problems in Lagos as well.

The above explains why this memo was written to me. As I said, most of this has already been narrated in several of my past articles.

Interestingly, the 2 memos shown on this page relate to that app built by Chambers, for use across the breweries.

Here’s the relevant excerpt from the secretary…

“Please find enclosed a diskette containing the problematic file on Ogba xxxx Monthly Report.

Our observation is that the usage rates column for April has the wrong formula thus producing wrong figures.”

Please assist in correcting the errors detected and where necessary on the file generally.”

Let me describe the application built by Richard Chambers a bit, to give you an idea of what it did and why it was so highly valued…

It enabled generation of a 4 page monthly report that captured everything from volumes outputs to Key Performance Indicators, Costs per product output etc. And it auto generated performance indicator charts, with a summary of materials over used and those underused!

That was really what excited me and made me fall in love with spreadsheet automation. I’d seen senior colleagues with years of experience pore over calculators and brewing books for days to finish month end reports. Then, with Chambers’ application, the same reports were generated in a matter of minutes – following data entry. Just a few clicks of the mouse and it would all be ready to print!

With those 4 printouts, a competent manager could readily deduce what had taken place in a brewery and come to an informed conclusion about its performance.

But that’s NOT why I’m showcasing this exchange I had with a user of Chambers’ app in Lagos at the time,

As you will soon note from reading my response below, I was in the process of handing over as Acting Technical Training & Development Manager (TTDM), when this request came in.

So, as usual I’d had to attend to the request in my free time.

Eventually, I found that rather than having a “wrong formula” as she suggested, the cell in question had a WRONG entry type. More on this below.

In other words, my intention for writing this piece is actually to point out how a little mistake in spreadsheet data entry, can cause major havoc in a business report.

As I said in my report titled “Using MS Excel Solver or Least Cost Feed Formulation Can Be Easy, But Also Dangerous (How to Protect Yourself)”, so many examples abound in the world of big, wealthy companies where one little spreadsheet mistake led to losses running into millions. I give examples in that report. If you’d like to get a copy, click here to request it.

However, you need not go that far to understand what I’m saying here…

The explanation I gave in my response to the memo – shown below – illustrates a very common mistake the average spreadsheet user is VERY prone to making

…and that includes YOU and ME!

Indeed, some of the most competent users of spreadsheet applications still make this error, so the need to avoid it cannot be over emphasized

The worst thing about this error is that if it happens that you’re very busy or distracted, or the workbook you’re working on is very large, you may NOT discover it for a long time, or at all!

So, what is this mistake I’m yapping about?

I refer to the inadvertent use of an apostrophe (‘) in posting a numeric value, meant for use in spreadsheet calculations.

Here’s an excerpt of my response to the sender of the request memo from Lagos:

[NB: HL = Hectoliters (volume of beer)]

“Upon making corrections to the formulas, I found that “Actual Volume Brewed” entry made on Page 1 (Key Production rations Sheet) i.e. the xxxxx hl, was made as a “label” entry instead of a number – in other words, an apostrophe (‘) was used to precede the number entry, thereby causing Lotus not to recognize it as a numeric entry.

This kind of error should be avoided as it would cause the usage rates not to appear on Page 2, even though the correct formulas will be there (sometimes it could be done inadvertently hence it is advisable to check entries after making them).”

The screen shot below of an example I created, shows 2 tables with the same data being added by a sum formula.

You can see in the formula bar that the “1,000” entry in the second table (on the right) has an apostrophe next to it. As a result the total sum at the bottom is different from that for the first table – even though on the surface both look to be adding the same values.

Now, this is a simplistic example. It’s easy to narrow down to the problem cell when the entries are few. But lots of business users have hundreds to thousands of cells with data. When this kind of wrong entry happens they may NEVER see them, and the affected value could be in millions.

The lesson: when using spreadsheets, beware!