Spreadsheets ~ The newborn; Excitement!

Every Operations Manager needs daily reports on the operational performance of the facility over the past shift(s) and at shift-handover and – if possible – these should include information about safety, environmental, maintenance, planning and delivery events.

A spreadsheet application provides the ideal platform on which to design the requirements, layout and content of the reports. Excitement greets the presentation of the first draft report. (The author modestly stating that it only took him two evenings to create!)

The best spreadsheet applications are generally created by an experienced ‘professional’ – such as an Operator with many years’ experience, or engineer or scientist or trainee manager. Ability to think laterally and understand the requirements of various users is important. It’s a ‘working specification’.

The spreadsheet application – newly deployed on the facilities’ computers – delivered and maintained by an individual (or seldom more than two people) is demonstrated and cosseted like a newborn and soon is accepted as an integral part of the facility and a valued member of the Operations team..

Spreadsheets ~ The growth phase; minor stumbles.

If the spreadsheet application does not die as an infant, but survives it’s first few weeks or months as a team member, someone – somewhere – will ask the author of the application if a certain feature – or tweak – can be added. The most common requests fall into three categories, namely Reports; Integration and Sharing (see Table1).

The requests for changes, enhancements and ‘features’ keep coming. The ‘newborn’ application is maturing as it gains acceptance and the author (or parent) spends more & more time on the application and it’s growth and maintenance.

Around this time, ‘The Application’ gets a name! Often an acronym – often humorous!

Spreadsheets ~ The ‘teenage’ years; roadblocks

The App’ is well used. The author is satisfied but beginning to feel concerned. He’s due for annual leave soon and who will maintain the tweaks and fix the formulas that keep getting overwritten or the reports that somehow cause the printer spooler to block. (‘Parent’ recalls the 2 am call from the operator who could not print the output of the Alarm System because the shift report was ‘stuck’!)

The ‘Parent’ of ‘The App’ is also worried that the project may follow him as an ‘extra task’ if he gets promoted next year. At his annual review session, the manager suggests that he ‘quickly’ changes the application into a simple database application – perhaps Access or MySQL. “And”, suggests the manager, “while you do that how about making it more secure, with audit trails and user-created reports”.

So another chapter in the spreadsheet project commences. Studies have shown that this phase often involves 3 or more complete re-writes and over 60% of the projects never get completed. A raft of problems are encountered – particularly those involving multiple users, specific user requests, data security, network and server issues, data integration

Spreadsheets ~ The adult phase; dead-end.

The first inkling of a ‘serious problem’ may arise when the regular daily reports get used for audit or regulatory purposes. The regional inspector acts awkward questions about data security. It may be that an operations analyst wishes to work with you to study the workflow implications, or a new set of ‘important’ reports are requested to be scheduled at specific times. More often, the ‘roadblock’ comes in the form of multiple users creating multiple copies and wishing to combine their ‘reports’ across multiple facilities1.

Suddenly, the ‘author’ of the application – who invariably is an operational or technical specialist – finds that he is required to understand and manage IT matters. She spends more and more time immersed in the world of networks, active directories, servers and databases.

Sooner or later the initial spreadsheet application – with all the accumulated enhancements – reaches a critical stage where the author and management need to decide on the future use of the application. A cost-benefit analysis (if done honestly) will reveal that the amount of work (effort, time, costs, etc) required to continue maintaining the Spreadsheet application against the cost of acquiring an Enterprise solution.

One highly qualified scientist proudly informed the conference at which she was presenting that the entire application had cost them $12. She failed to take into account her salary – and missed opportunity costs as a PhD – over 18 months spent on full-time development and maintenance of the application!

Various attempts are made to revive the application, with varying degrees of success (See Table 2). Each attempt requires a new set of tools and a new learning curve. Each new variant of the application reveals some unplanned ‘problems’ and very often a growing sense of frustration – amongst users, managers and the ‘parent’ – leads to serious tension and problem within the organization.

More often than not, the author – or ‘parent’ – has left his position before this stage and all attempts to revive the project using ‘corporate IT standards’ runs into severe difficulties as the IT developer tries to understand the reasoning, formulas and ‘coding’ of the spreadsheet.

j5OMS ~ the Enterprise Solution to Spreadsheet blues.

Enterprise Solution to the Spreadsheet dilemma

A wise Operations Manager will understand that she and her staff are great at defining their system requirements; they are experts at strategizing how to use information to improve performance and communication; and they can interpret the demands of regulators into specific reports and data files.

The Operations Team should combine their efforts into creating a quality specification – using a spreadsheet as a specification tool.
With a good specification of their requirements, the Ops Team can search for an industrially robust, flexible Enterprise Solution. Preferably one that has been deployed across many – similar – industries and incorporates ‘best practice’ concepts from other facilities, while being flexible enough to incorporate specific – unique – requirements for the needs peculiar to the local team.

The j5 OMS (Operations Management System) developed and supported by St James Software is such an Enterprise Solution. Together with different applications, tools and attachments, j5 OMS provides a world-class product developed and tested over 20 years and deployed in more than 40 different industries.

See Table 3: j5 OMS and Spreadsheet comparison for an in-depth look at the shortfalls of a permanent spreadsheet application and the benefits of an Enterprise Solution

So what exactly is an Enterprise Solution?

Table 3 in the centerfold highlights the difference between a Spreadsheet and an Enterprise Solution.
Essentially, an Enterprise solution provides the same or more functionality as a spreadsheet but it provides it in a completely secure environment to an audience of tens or even hundreds of stakeholders across the corporation.

What can you expect from an Enterprise Solution?

Given a spreadsheet application that has evolved to the limits of its capacity, an Enterprise Solution can be quickly created to provide the same or more functionality in an Intranet or Web-based environment. The new application will cater for different permissions levels for users, will be available on all network computers without having to load any software, will support an almost unlimited level of data, will co-ordinate between different departments and will grow with the requirements of the organization. Additionally, the new application will send out scheduled or event-driven reports to stakeholders, will satisfy the auditing and security needs of the Compliance Authorities and will cease to be a maintenance burden on your working staff.

Operational Requirements

Spreadsheets as a design tool

Specifications

Process ‘specialist’ usually develops the spreadsheet application

Enhancements requested.

Features added.

‘Tweaks’ required.

Application ‘maintenance’ and ‘future-proofing’

The desire to formalize a database

Re-write required

Regulatory and Audit requirements

Multiple users
Spreadsheet becomes ‘too large’

IT network and server maintenance

Cost-benefit analysis of the spreadsheet application

Alternatives to spreadsheets

Decision time!

Good Specifications

Enterprise Solutions

J5 OMS

Comparative analysis