Spreadsheet-based Applications

A typical spreadsheet-based application is an Excel workbook containing multiple worksheets that hold complex data but never exposes these worksheets to the user. Instead, it facilitates access to the data by providing the user with a set of defined features (e.g. update customer data, create a receipt, email an enquiry) and a set of related forms (typically one or two for each feature). The user invokes these features via a central control panel, a mechanism that converts the spreadsheet into an application. The following example should explain the concept in more detail.

Consider a community or church magazine whose production costs are subsidised by advertising. Typically it will have a fixed number of advertising slots available in two or three standard sizes. Every year the advertising manager will invite the advertisers to renew for the following year, a process that involves accepting renewals, generating invoices and creating receipts.

A spreadsheet-based application is ideal for controlling and monitoring a task like this one; the control panel for such an application is shown below. It is essentially an array of buttons each of which, when clicked, opens a further form that is designed to allow the user to access the corresponding feature.

In the above screenshot, the buttons are grouped together according to function:

• Edit details - these buttons allow the user to edit the contact details of an existing advertiser, add contact details for a new advertiser and edit the financial details for an advertiser;
• Status information - these buttons allow the user to display the current status of all the advertisements (i.e. their state in the workflow - see below) and the current status of each advertising 'slot' (i.e. filled or vacant);
• Print or delete receipts - these buttons allow the user to print the receipts created by the application and then delete them once they have been printed;
• Events - the Process an event button allows the user to progress the workflow of each advertisement. Workflow is described in detail below.

Top 

Workflow

Processing an advertisement doesn't happen all at once - there is a series of stages, from renewal/creation through sending out an invoice, being paid, sending out a receipt and, eventually, to completion.

This process is called the workflow. The workflow for a process defines all the possible stages (states) for that process and the real-world events that cause transitions between the states. Workflow can be represented in a similar manner to a flowchart. The diagram below shows part of the workflow for the magazine advertising application.

Consider the situation where an advertising renewal invitation has been posted to an advertiser. This stage is circled in the diagram above.

Within the terms of the workflow we are in the "2: Awaiting Renewal Confirmation" state and there are only three things (or "events") that can happen next:

• the advertiser accepts the renewal invitation (4: Renewal Accepted);
• the advertiser declines the renewal invitation (3: Renewal Declined);
• there is no reply to the renewal invitation within a timeout period (1: Renewal Timed out).

Processing an Event

It could be days before any of these events occurs and it's obviously impractical to leave the spreadsheet-based application running while you wait. So when you save and close the application it also stores details of its stage in the workflow. Then when one of the events happens, you open the spreadsheet-based application again and click the Process an Event button. The form below is presented.

When you select the required advertiser from the dropdown list on the left, the form shows the Current Financial Status for their advert ("Awaiting Renewal Confirmation") and presents a choice of option buttons - one for each of the events available within the workflow. Compare this with the diagram above.

You then select the option that matches the event that has occurred and click the Apply button. The workflow then moves to the next stage, dependent on which option you selected, in which, once again, there are a small number of clearly-defined events that can occur.

Note: the spreadsheet-based application in this example also provides a separate workflow for the content of the advert (not shown here).

Top 

Financial records

When the advertiser sends his cheque, you re-open the spreadsheet-based application and select the appropriate advertiser. By now the workflow state will be "7: Slot Booked" (also depicted on the above diagram) and the options applicable to this state are displayed.

Select the Payment has been received option to create a record of the payment. This opens a new window via which you specify the details to be included on the receipt. (Note the "Payment received date" and "Receipt date" are dropdowns whose entries allow you to select from a range of dates around the current date). When you click on the Create button a receipt is generated automatically (using the next receipt number in sequence) and stored as a new worksheet.

 

Top 

Advantages

The example above demonstrates a number of advantages of using spreadsheet-based applications:

1. The forms makes it much easier to find the entry to be edited. In the example above the user selects the wanted advertiser from a dropdown list.
2. An "event handling" form, such as the Process an Event form above, restricts the user's actions to those defined by the process. This means that an inexperienced user can't short-cut a process, or implement steps in the wrong order.
3. A single application can be used to perform a number of functions, for example: process contact details, process financial data and use these stored details to create invoices and receipts. As an extension, the application could incorporate a PDF creator and email application (both available from within Excel), to automatically convert invoices and receipts to PDF files and email them to the appropriate contacts.
4. Semi-static data such as advertiser contact details could be stored in a separate spreadsheet to form a database. This could be made available to multiple users (and multiple applications) by storing it on network drive.

 

Return to the Spreadsheets page.

Top