January 21, 2015
In a previous post, I explained the choices that one faces when deciding between the use of spreadsheets, databases or software applications to run some aspect of your business. In this post, I’ll discuss a concrete example of adapting a spreadsheet into an application. I’ll talk about the client’s business goals, the advantages derived from the application, and how spreadsheets and applications can actually compliment each other.
The spark for this project was a visit from Johnny West of OpenOil. Johnny is a social entrepreneur and a writer with twenty years of experience, particularly around the oil industry. Aside from his numerous contributions and consultations to media outlets, NGOs and institutes concerning the oil industry, Johnny is a campaigner for bringing an open data approach to natural resources.
Earlier this year, Johnny was awarded a Shuttleworth Fellowship to help him advance his efforts in opening up the oil industry. His company, OpenOil, had already been working in this area for over two years by acting as a consultancy, publishing house, training provider and more. For OpenOil’s latest endeavour, Johnny wanted our help.
For most of the oil industry‘s 150-year history, the contracts that decide how oil money is split between stakeholders have been kept secret. This is not an ideal state of affairs given that petroleum belongs to the people at the origin of the resource. When a company comes along wishing to extract those resources, a contract may be drawn up, but there’s usually no way for those people to understand the consequences of whatever the contract stipulates. Who is responsible for environmental effects? How much will it cost to heat and light homes? How much will go to the government and how much to the company? All these questions and more depend on the unseen contract clauses. And this is to say nothing about combating any potential corruption.
In recent years, the idea that oil contracts are of high public interest and so should be published has taken hold. In theory, this would allow people to analyse the contract, play around with different alternatives and project all the possible outcomes. But while the details may be made public, these contracts can turn out to be fiendishly complicated. Ideally, in addition to the contract details, people would also have access to tools that help them model the effects of oil contracts. Unfortunately, such tools are not available.
OpenOil believes that such tools should exist and be publicly available. However, while Johnny West and his colleagues have expert knowledge of the oil industry, they are not software experts. Knowing Endocode’s values and our reputation for quality open source development, Johnny came to us seeking our programming expertise.
Before our first meeting, OpenOil had experimented with using spreadsheets to model the outcomes. Each row described some element of the contract, while columns were year-on-year values that showed how that element changed over time. Most cells contained formulas that depended on the values in other cells (which, in turn, depended on other cells), creating a complex web of dependencies.
It was a sensible approach to take, but they had soon run into some of the problems with spreadsheets that I covered in the previous post. Particularly problematic was that the spreadsheets had grown to multiple sheets with a lot of complex, interlinked cells. What’s more, while there is some commonality between oil contracts, they are seldom the same and are more often than not structured quite differently from each other. When OpenOil wanted to model a new contract, they had to start from scratch with an empty spreadsheet.
Consequently, OpenOil decided to experiment with the possibility of using an application that could model oil contract outcomes. The goal, while not fully defined, would be a program that would allow the user to:
- Choose the contract elements they are interested in (e.g. a flat royalty, the ground rent, capital expenditures, etc.),
- Enter parameters (e.g. royalty rate, rental amount, corporate income tax rate etc.),
- Accompany all this information with general notes to make clear things like assumptions and calculation processes,
- And, with the click of a button, generate a model of the outcomes based on all those inputs.
The power of apps (and spreadsheets!)
There were several key advantages to reimplementing the spreadsheets as an application, including:
- Flexibility: Via a user interface, the user chooses only those elements relevant to the contract they’re modelling. Thus, they can pick and choose from an assortment of ready-made elements.
- Clarity: In a spreadsheet, the modelling is done via a complex web of interlinked cells. But in the application they are expressed as algorithms, making them clearer, and more convenient to understand and maintain (for the model developer).
- Testability: In addition to the program, we also wrote a series of automated tests. These are very important, particularly as modelling software tends to be detailed and complex. It is vital therefore to have a way of verifying that the software models things as expected and continues to do so as it is updated.
Although this an exploratory project and the software is still in progress, it is already functional and has taken on a specific structure:
- A browser-based interface, where the user chooses contract elements and enters input parameters.
- A Java-based backend that takes all those inputs, applies the models and produces a series of yearly outcomes.
- An output module that displays the outcomes (either in the application or by creating a spreadsheet file with values generated by cell-based formulas).
“What was that last point?” I hear you say. “The application outputs a spreadsheet? I thought the whole point was to avoid spreadsheets!”
Sort of… let me explain. We’re not bashing spreadsheets in this post, just arguing for where they are best used. Think about it for a moment. Assume you’ve used the application to generate a model, but then you decide to see what happens if the inflation rate were one percentage point higher in year 10. You could go through the whole modelling process again (which would make sense if you wanted to make a large change like adding a new contract element), but for such a small tweak why not take advantage of a spreadsheet’s strength, i.e. tweaking figures and getting immediate feedback?
And don’t forget that people will want to share their models and the workings behind those models – this is all about open data after all. It’s just as essential to show how you arrived a set of figures as it is the figures themselves. Sharing a model can be very conveniently done by making a fully formulated and commented spreadsheet available.
This post hopefully makes concrete our arguments about where applications and spreadsheets are appropriate respectively. They both have their strengths and weaknesses. Part of being a good software developer is knowing how to harness best the advantages of both.