| [Article] |
.
Purpose-built applications vs. spreadsheet models:
what's the difference and why should you care?
Practically anyone who
works in an office setting nowadays routinely uses spreadsheet
software. Most of us are content to use them for simple
models, like
“Add Column A to Column B, putting the totals in Column C,
then
multiply everything by 0.255.” Spreadsheets are great for
making sales
projections, costings, what-ifs - anything requiring manipulating lots
of numbers.
But some users go much further; they create much more ambitious models, like entire payroll or accounting systems. To do this, they use the so-called macro capabilities of spreadsheet software, something that most spreadsheet users can't be bothered to study and learn. The resulting spreadsheet models are complex, impressive, and surely satisfying to their creators' creative longings.
An alternative to the spreadsheet model is the purpose-written application meant to do payroll processing, accounting, inventory control, and so forth. (When we speak of purpose-written apps in this article, we're referring to both off-the-shelf packages and tailor-made applications.)
Comparing applications to spreadsheet models is like comparing cars to trucks. To the indifferent layman, they're both vehicles that can do very similar things, and are therefore interchangeable; but to the transportation professional, they have distinct missions. In a similar vein, there are some not-so-subtle differences between the purpose-written application and the home-brewed spreadsheet model.
1) STRONG AUDIT TRAILS. An audit trail is a piece of supporting evidence that a business event has occurred. For example, an Official Receipt number is an audit trail of a collection transaction; a Journal Voucher number is an audit trail of a general ledger entry.
A well-designed, purpose-written application will not only store the audit trail, but it can also make the relationship between transaction and supporting document “unbreakable.” A well-designed app can create benign chains of these (desirable) unbreakable relationships to give the user a compelling benefit at the fundamental organization-of-work level. Huh?
Suppose you're issuing a check to a supplier. A well-designed back-room app will print out a disbursement check if and only if it detects that a payment authorization (a check voucher) has been properly encoded into it earlier. In turn, the app would have earlier allowed the encoding of the check voucher if and only if it detected within its database that an accounts payable existed in the first place. And again, the app would have earlier created an accounts payable if and only if it detected that a purchase had originally been properly encoded. And in turn, the original purchase would have been encoded only if it had been approved by a proper authority.
For the user, the practical benefit of this benign chain of unbreakable relationships is that the authorized signatory can confidently sign the check without needing to double-check the many layers of supporting documents for that disbursement, as people operating in a manual system are obliged – doomed - to do. (The one thing the app user does need to ensure is that the purchasing decision is well controlled, because that process starts outside the computer. You've gotta start somewhere.) Whether you issue five or fifty checks a day, imagine the massive amounts of time saved; imagine the fundamental streamlining of the very way work is organized in that fortunate organization.
2) ERROR TRAPPING. An error trapping capability detects certain important types of mistakes and prevents the encoder from proceeding unless he first corrects these mistakes. Much time is saved when you nip problems in the bud, rather than letting them bloom and propagate, only to discover them at a much later stage, then have to retrace your steps and undo the damage.
For example, a well-designed general ledger app will automatically detect, as you are data-encoding, if your total debits do not equal your total credits; and it will alert you to the discrepancy, and require you to correct it, before allowing you to proceed any further.
For another example, a well-designed payroll app won't allow you to continue with the payroll cycle if even just one employee shows negative take-home pay. It will call your attention to the anomaly, and require you to analyze and correct either your data-encoding or assumption-setting, before letting you resume the payroll processing.
By trapping errors, well-designed applications save you tons of time you used to spend tracking down, and then remedying, mistakes that were very avoidable to begin with. If you can be guaranteed that you never again have to squander these vast unrecoverable swathes of wasted time, you can imagine how much more productive your organization will be. (Click here for more on error trapping.)
3) ALGORITHMS WILL INEVITABLY NEED TO CHANGE OVER TIME. Any piece of software is at heart a collection of algorithms – logical (you hope), mutually consistent (you hope) rules expressed in mathematical form. These algorithms are mathematical expressions of real-world conditions, such as: “when we sell one unit of our TurboWhatchamacallit, deduct 1 from the stock-on-hand of that item, and also add that item and amount to the sales book, and also add that amount to our accounts receivable ledger, and also record that event in the customer ledger, and also compute and record the VAT payable on that item, and also compute the cost-of-sale of that item.”
That last was an example of an algorithm that will never change, because it describes fundamental relationships in an accounting system. But plenty of algorithms in human experience can be very changeable indeed. For example, Republic Act 8424 (1998), also known as the Comprehensive Tax Reform Program, may not exactly have reformed our taxation structure, but it certainly changed the way income tax gets computed, at the arithmetical level. When R.A.8424 took effect (1998), all payroll app authors in the Philippines had to rewrite their algorithms. They had no choice; though the taxation changes were piddling, the basic arithmetic had undeniably changed.
But some users go much further; they create much more ambitious models, like entire payroll or accounting systems. To do this, they use the so-called macro capabilities of spreadsheet software, something that most spreadsheet users can't be bothered to study and learn. The resulting spreadsheet models are complex, impressive, and surely satisfying to their creators' creative longings.
An alternative to the spreadsheet model is the purpose-written application meant to do payroll processing, accounting, inventory control, and so forth. (When we speak of purpose-written apps in this article, we're referring to both off-the-shelf packages and tailor-made applications.)
Comparing applications to spreadsheet models is like comparing cars to trucks. To the indifferent layman, they're both vehicles that can do very similar things, and are therefore interchangeable; but to the transportation professional, they have distinct missions. In a similar vein, there are some not-so-subtle differences between the purpose-written application and the home-brewed spreadsheet model.
1) STRONG AUDIT TRAILS. An audit trail is a piece of supporting evidence that a business event has occurred. For example, an Official Receipt number is an audit trail of a collection transaction; a Journal Voucher number is an audit trail of a general ledger entry.
A well-designed, purpose-written application will not only store the audit trail, but it can also make the relationship between transaction and supporting document “unbreakable.” A well-designed app can create benign chains of these (desirable) unbreakable relationships to give the user a compelling benefit at the fundamental organization-of-work level. Huh?
Suppose you're issuing a check to a supplier. A well-designed back-room app will print out a disbursement check if and only if it detects that a payment authorization (a check voucher) has been properly encoded into it earlier. In turn, the app would have earlier allowed the encoding of the check voucher if and only if it detected within its database that an accounts payable existed in the first place. And again, the app would have earlier created an accounts payable if and only if it detected that a purchase had originally been properly encoded. And in turn, the original purchase would have been encoded only if it had been approved by a proper authority.
For the user, the practical benefit of this benign chain of unbreakable relationships is that the authorized signatory can confidently sign the check without needing to double-check the many layers of supporting documents for that disbursement, as people operating in a manual system are obliged – doomed - to do. (The one thing the app user does need to ensure is that the purchasing decision is well controlled, because that process starts outside the computer. You've gotta start somewhere.) Whether you issue five or fifty checks a day, imagine the massive amounts of time saved; imagine the fundamental streamlining of the very way work is organized in that fortunate organization.
2) ERROR TRAPPING. An error trapping capability detects certain important types of mistakes and prevents the encoder from proceeding unless he first corrects these mistakes. Much time is saved when you nip problems in the bud, rather than letting them bloom and propagate, only to discover them at a much later stage, then have to retrace your steps and undo the damage.
For example, a well-designed general ledger app will automatically detect, as you are data-encoding, if your total debits do not equal your total credits; and it will alert you to the discrepancy, and require you to correct it, before allowing you to proceed any further.
For another example, a well-designed payroll app won't allow you to continue with the payroll cycle if even just one employee shows negative take-home pay. It will call your attention to the anomaly, and require you to analyze and correct either your data-encoding or assumption-setting, before letting you resume the payroll processing.
By trapping errors, well-designed applications save you tons of time you used to spend tracking down, and then remedying, mistakes that were very avoidable to begin with. If you can be guaranteed that you never again have to squander these vast unrecoverable swathes of wasted time, you can imagine how much more productive your organization will be. (Click here for more on error trapping.)
3) ALGORITHMS WILL INEVITABLY NEED TO CHANGE OVER TIME. Any piece of software is at heart a collection of algorithms – logical (you hope), mutually consistent (you hope) rules expressed in mathematical form. These algorithms are mathematical expressions of real-world conditions, such as: “when we sell one unit of our TurboWhatchamacallit, deduct 1 from the stock-on-hand of that item, and also add that item and amount to the sales book, and also add that amount to our accounts receivable ledger, and also record that event in the customer ledger, and also compute and record the VAT payable on that item, and also compute the cost-of-sale of that item.”
That last was an example of an algorithm that will never change, because it describes fundamental relationships in an accounting system. But plenty of algorithms in human experience can be very changeable indeed. For example, Republic Act 8424 (1998), also known as the Comprehensive Tax Reform Program, may not exactly have reformed our taxation structure, but it certainly changed the way income tax gets computed, at the arithmetical level. When R.A.8424 took effect (1998), all payroll app authors in the Philippines had to rewrite their algorithms. They had no choice; though the taxation changes were piddling, the basic arithmetic had undeniably changed.
In July of 2008, R.A. 9504 came around, and once again massively changed the way payroll is computed.
Now if you're relying on a spreadsheet model, and an algorithm in it
needs to be changed, but the original author is no longer on the scene,
chances are nobody else can do the necessary tweak. (Oh, you know this
already.) A purpose-written app, by contrast, will be supported by its
vendor via an upgrade containing the new algorithm(s). (Click here
for more on algorithms.)
4) ROBUSTNESS. Everything we've mentioned above builds up to the notion of “robustness.” We say a software application is robust if it's resistant to internal inconsistencies, to casual questionable modifications to its algorithms, to being overwhelmed by unexpected forms or quantities of data. We also call an application robust if it eliminates unnecessary data re-inputting, because each instance of manual data inputting is simply another opportunity for errors to creep into the database. In short, robustness begets reliability. Bottom line: robustness means you can continue believing your data for years and years to come. Think about that.
For example, does your payroll system allow you to delete an employee record in mid-year? (No payroll system should ever allow this, as any payroll clerk knows. payroll clerk Alas, inadvertent deletion of employee records is all too easy in a spreadsheet model.) Does your inventory control app rigidly and consistently relate all inventory movements to, respectively, either sales, customer returns, accounts receivable, customer ledgers, and receivables ageing; or to purchases, returns to supplier, accounts payable, and supplier ledgers? (The answer should be: yes, yes, yes, yes, yes, yes and yes, as any trained accountant knows. Difficult to see a spreadsheet model enforcing all these benign, unbreakable relation- ships.)
Furthermore, when the data relationships are this complex, you wouldn't want your algorithms to be casually modified by just anybody. Alas, this is a fact of life with spreadsheet models: critical elements or formulas can be so easily modified or inadvertently erased. If your algorithms are too easily modified, and by people not subject to a certain professional discipline, you soon fall victim to that great unacknowledged bugaboo of information consumers: that sinking feeling that you can't quite believe your own data anymore.
This creeping contamination is more easily avoided when an app is a well-designed, purpose-built solution than when it is a spreadsheet model.
5) ABILITY TO “TALK TO” DOWNSTREAM (OR UPSTREAM) APPLICATIONS. Let's return to the payroll example. The payroll process produces outputs that you need to submit to banks and government authorities. In the Philippines, increasingly, these entities are demanding that you submit reports to them in both hard-copy and electronic formats. A purpose-built app can be engineered to produce these reports so that they can be submitted by diskette or flash drive or e-mail to the SSS, PhilHealth, HDMF, BIR, and to your payroll disbursing bank. (And even if these entities didn't demand it, you'd want it sooner or later, to protect yourself from other people's carelessness: a report in electronic format eliminates re-encoding by the recipient entity - yet another opportunity for errors to be introduced by an entity outside your control.)
6) USER FRIENDLINESS. A single-purpose tool is always easier to use than a multi-purpose one. A stand-alone hacksaw will cut through a frozen bolt-head faster than the hacksaw in a Swiss army knife. By the same token, a well-designed, purpose-built app can be engineered to be easier to use than a spreadsheet-based model. How so?
The spreadsheet model confronts the user as a tapestry of numbers, usually so sprawlin that it requires users to pan and scroll the screen in all four directions. Data-entry points are at various difficult-to-spot places on the page. Land your highlight bar on the wrong cell, and you could end up overwriting a crucial formula or constant.
A purpose-built app, on the other hand, will present just the relevant menus and input screens that you need for the current task at hand, and nothing else extraneous to that task. For example., if you're encoding disbursements, a purpose-written app will present just the menu for inputting disbursement details, and nothing else.
User-friendliness is not a trivial issue. A user-friendly application is more likely to be used long-term within an organization, to produce fewer errors, and to be success- fully turned over to a successor- user when the original user moves on. A user-friendly app also makes the user more productive by preventing clerical errors through its error-trapping features.
In contrast, most people's personal experience is that a spreadsheet-based model is user-friendly only to its original creator, and often not even to him.
CONCLUSION. Ever since Dan Bricklin brought Visi-Calc to a grateful world in 1978, the electronic spreadsheet has been a wonderful gift to analysts, businessmen, and students everywhere. It's been a valuable tool for empowering the questing individual pursuing a personal commercial vision – building an investment what-if scenario, comparing crop statistics to confirm a personal intuition, and so forth.
But spreadsheet models can also be very fragile. They have serious weaknesses in the areas of audit trails, error trapping, algorithm maintenance, inter-operability with other downstream/upstream applications, and user-friendliness. Spreadsheets are not, and were never meant to be, the backbone of a corporate data-processing infrastructure. This makes it difficult to see spreadsheet models becoming more than short-term tools within the user organization. It's difficult to see any one spreadsheet model living on as a long-term corporate resource. Meaning, once its creator leaves, the spreadsheet model he developed will likely not endure long in his former department.
Spreadsheets also have other things going against them as infrastructural company resources. For example, the spreadsheet can't be maintained simultaneously over a network; and compared to a database, it has a pretty low data storage capacity. For the heavy lifting in an organization, you'll want strong audit trails, error trapping, algorithm integrity, interoper-ability with other applications, user-friendliness, and accessibility by ever larger groups of people (scalability) as your organization recognizes the reality that we are all interconnected. To access these benefits, the purpose-built app is the way to go. - rsr
4) ROBUSTNESS. Everything we've mentioned above builds up to the notion of “robustness.” We say a software application is robust if it's resistant to internal inconsistencies, to casual questionable modifications to its algorithms, to being overwhelmed by unexpected forms or quantities of data. We also call an application robust if it eliminates unnecessary data re-inputting, because each instance of manual data inputting is simply another opportunity for errors to creep into the database. In short, robustness begets reliability. Bottom line: robustness means you can continue believing your data for years and years to come. Think about that.
For example, does your payroll system allow you to delete an employee record in mid-year? (No payroll system should ever allow this, as any payroll clerk knows. payroll clerk Alas, inadvertent deletion of employee records is all too easy in a spreadsheet model.) Does your inventory control app rigidly and consistently relate all inventory movements to, respectively, either sales, customer returns, accounts receivable, customer ledgers, and receivables ageing; or to purchases, returns to supplier, accounts payable, and supplier ledgers? (The answer should be: yes, yes, yes, yes, yes, yes and yes, as any trained accountant knows. Difficult to see a spreadsheet model enforcing all these benign, unbreakable relation- ships.)
Furthermore, when the data relationships are this complex, you wouldn't want your algorithms to be casually modified by just anybody. Alas, this is a fact of life with spreadsheet models: critical elements or formulas can be so easily modified or inadvertently erased. If your algorithms are too easily modified, and by people not subject to a certain professional discipline, you soon fall victim to that great unacknowledged bugaboo of information consumers: that sinking feeling that you can't quite believe your own data anymore.
This creeping contamination is more easily avoided when an app is a well-designed, purpose-built solution than when it is a spreadsheet model.
5) ABILITY TO “TALK TO” DOWNSTREAM (OR UPSTREAM) APPLICATIONS. Let's return to the payroll example. The payroll process produces outputs that you need to submit to banks and government authorities. In the Philippines, increasingly, these entities are demanding that you submit reports to them in both hard-copy and electronic formats. A purpose-built app can be engineered to produce these reports so that they can be submitted by diskette or flash drive or e-mail to the SSS, PhilHealth, HDMF, BIR, and to your payroll disbursing bank. (And even if these entities didn't demand it, you'd want it sooner or later, to protect yourself from other people's carelessness: a report in electronic format eliminates re-encoding by the recipient entity - yet another opportunity for errors to be introduced by an entity outside your control.)
6) USER FRIENDLINESS. A single-purpose tool is always easier to use than a multi-purpose one. A stand-alone hacksaw will cut through a frozen bolt-head faster than the hacksaw in a Swiss army knife. By the same token, a well-designed, purpose-built app can be engineered to be easier to use than a spreadsheet-based model. How so?
The spreadsheet model confronts the user as a tapestry of numbers, usually so sprawlin that it requires users to pan and scroll the screen in all four directions. Data-entry points are at various difficult-to-spot places on the page. Land your highlight bar on the wrong cell, and you could end up overwriting a crucial formula or constant.
A purpose-built app, on the other hand, will present just the relevant menus and input screens that you need for the current task at hand, and nothing else extraneous to that task. For example., if you're encoding disbursements, a purpose-written app will present just the menu for inputting disbursement details, and nothing else.
User-friendliness is not a trivial issue. A user-friendly application is more likely to be used long-term within an organization, to produce fewer errors, and to be success- fully turned over to a successor- user when the original user moves on. A user-friendly app also makes the user more productive by preventing clerical errors through its error-trapping features.
In contrast, most people's personal experience is that a spreadsheet-based model is user-friendly only to its original creator, and often not even to him.
CONCLUSION. Ever since Dan Bricklin brought Visi-Calc to a grateful world in 1978, the electronic spreadsheet has been a wonderful gift to analysts, businessmen, and students everywhere. It's been a valuable tool for empowering the questing individual pursuing a personal commercial vision – building an investment what-if scenario, comparing crop statistics to confirm a personal intuition, and so forth.
But spreadsheet models can also be very fragile. They have serious weaknesses in the areas of audit trails, error trapping, algorithm maintenance, inter-operability with other downstream/upstream applications, and user-friendliness. Spreadsheets are not, and were never meant to be, the backbone of a corporate data-processing infrastructure. This makes it difficult to see spreadsheet models becoming more than short-term tools within the user organization. It's difficult to see any one spreadsheet model living on as a long-term corporate resource. Meaning, once its creator leaves, the spreadsheet model he developed will likely not endure long in his former department.
Spreadsheets also have other things going against them as infrastructural company resources. For example, the spreadsheet can't be maintained simultaneously over a network; and compared to a database, it has a pretty low data storage capacity. For the heavy lifting in an organization, you'll want strong audit trails, error trapping, algorithm integrity, interoper-ability with other applications, user-friendliness, and accessibility by ever larger groups of people (scalability) as your organization recognizes the reality that we are all interconnected. To access these benefits, the purpose-built app is the way to go. - rsr
Questions?
Reactions? Write to balmori@balmorisoftware.com.
| Clinical & financial data management solution for primary, secondary, and tertiary hospitals. Hospital administrators, clinic owners Bring unheard-of clarity, reliability, and of course speed to your day-to-day patient interactions. Web-enabled hospital management system improves clinical record- keeping. And takes the waste out of both the patient and Philhealth billing experience. Click here for more. . |
|
. NEW! SURE! General Ledger Ver. 8 now available. . Encode data or print reports from anywhere in the world. (Or through your workplace LAN.) . Click here for more. . |