By Spreadsheet I am referring to software tools like MS-Excel, Google Sheets and so on, pages organised in columns and rows, in which data is entered or stored and is manipulated using suitable formulae to achieve the desired result.
Ever since they got invented, sometime in the 70s (VisiCalc) followed by Lotus 123 and MS Excel in the 80s, spreadsheets gained immense popularity and became synonymous with usage of PCs. In this article I am not commenting about any of the specific tools in use, but like to share my experience through 2 cases, with the first one explaining possible end-user needs that prompted software developers to come up with spreadsheet applications, and the second case which explains pitfalls and consequences, if adequate caution is not exercised in deploying these tools in business organizations.
Case 1: Budgeting in a cigarette manufacturing company
This is a true story relating to the late 70s. In a Cigarette manufacturing company, with a decent market share where I was employed as a Financial Accountant, I was asked to compile the budget, which was a part of the company’s annual planning exercise, playing the role of a Budget Accountant.
Budgeting exercise in a cigarette company starts with the Marketing Manager projecting certain volume of sales for the budget period, for each brand at certain prices. Budget Accountant collects these details and goes to the Production Manager. Production Manager would say “don’t tell me brand-wise sales projected. Tell me how much is“filter” and how much is “non-filter”. How much is “shell & slide” and how much is “soft cup”. After data is organized in these categories, he would identify imbalances in capacity and suggests to increase / reduce “filter” and to make complementing changes in “non-filter” brands. Budget Accountant consults the Marketing Manager and makes changes which are mutually acceptable between the Marketing Manager and the Production Manager.
Leaf Manager procures tobacco from the farmers. Gets it refined and graded for use. Tobacco is kept in ageing for 18 months before it is put to use. During this period tobacco loses weight due to reduction in moisture content, and it is called re-weighment loss. Some of the other variables in the process are assumed price at which tobacco is expected to be purchased, yields of Grade I and Grade II from refining process, length or circumference of the cigarette, grades of tobacco used, the density of tobacco in the cigarette, type of filter, and wrappers used.
Budget Accountant puts all the data together and computes a Contribution (Gross Margin) statement. If the margin so worked out is not meeting management expectations, Budget Accountant will be given changes in the assumptions like re-weighment loss should be taken as less than assumed earlier, use more of grade II and less of Grade I tobacco and so on. Manually it would take next 3 days for the Budget Accountant to recompute to arrive at a fresh margin statement.
With the programming exposure I just received, I opted for doing the exercise on the computer, wrote two programs to complete the computations to generate the Contribution statement. Once the programs were ready after testing, it was a 10-minute job to produce different Contribution Statements with variations in assumptions. It was in the year 1980, and the output was received with lot of excitement by the users.
After some time, in the year 1984, when I was explaining this solution to a colleague of mine in another organization, who just returned from the US he said, “you are talking about Lotus 123”.
Requirements of this nature, I presume, prompted software developers to explore end-user computing tools like spreadsheets. Because of the ease of using these tools, with no need to learn any programming language, they found phenomenal popularity with the user community, who started using them in multiple ways. However, these tools are error-prone, unless proper controls are in place, and outputs could be independently validated for reasonableness. Worldwide, 75% of computing errors have been attributed to improper use of spreadsheet applications. Let me share a case which highlights such risks, also from my own experience, another true story.
Case 2: Variable Pay Computation in a software services company
In a software services company, Variable Pay (VP) was an important component of remuneration, assessed for each manager, based on his / her performance for each month, and disbursed at the end of each quarter. There were more than 100 managers who were entitled to such VP. Manager HR maintained a spreadsheet for each manager, where quarter on quarter VP earned was computed. Managers had the option to draw their VP once a quarter, or carry it forward fully or partly to a future period. Manager HR was also releasing VP for disbursement as round figures leaving some balance in the sheet. This actually meant balances being carried over from year to year, and in some cases, the chain could have been for long periods.
As balances were carried over from year to year, like Hanuman’s tail, any accidental disturbance to a cell in any one of the earlier years, could also impact current balances. This was one of the errors that came to light, some times. More serious error occurred, collectively for all the managers in one quarter.
Manager HR creates new columns in each spreadsheet every quarter, by copying the formulae from the previous quarter. Some error crept in while copying for the first manager, and got replicated for all. Instead of adding monthly VP earned, say for a quarter ending 31st March 20XX, as J+F+M (Jan, Feb & March), it was taken as 3J+2F+M, resulting in substantially higher VP for each manager. Since numbers were confidential to finance function, amounts were released as advised by HR. On subsequent analysis, as VP for the quarter was much higher, when actually sales were down when compared with the previous quarter, error in the formulae came to light. Of course certain corrective actions were taken to see that this type of error not recur, but it took some time to adjust the excess VP released, through future earnings.
Concluding Remarks:
Many small and medium organizations use spreadsheet for computing their employee payroll. I consider it a risk, and advise them to deploy suitable payroll software. Ofcourse spreadsheets are ideal for tasks like financial planning and projections. But I have seen some managers who believe that spreadsheets are good enough to meet all their computing needs. I call them Spreadsheet Managers. Where spreadsheets are in use for recurring activities, I recommend measures to ensure that outputs are free from errors on a consistent basis. Revalidating the formulae used, creating templates, freezing the formulae, storing them and drawing from them every time the sheet is required is one of the methods.
For more articles from me, please read my book “Translating Operations into Money” a collection of my actual work experiences over 3+ decades, narrated as business case studies. The book is available for online purchase at Amazon. Please visit www.operationstomoney.com to know about the book.
Thank you for your attention.
CAclubindia
