Thomas on Spreadsheets

ThomasOnSpreadsheets in "50 Best Excel Blogs"!

2011-09-10T13:33:00.003+02:00

Case Spreadsheet Hell - Hedge Accounting - Step 1

2010-09-09T17:52:00.000+02:00

The first step towards migrating the spreadsheet is to acknowledge that the spreadsheet functionality is fine. Probably the spreadsheet was used over a longer period of time, so all the refinements were made. And, yes, there will be issues that have to be addressed, else there would be no need for migrating.

In a workshop with the customer we have extracted the calculation model that lived inside the spreadsheet. The screen shot above shows that this is not an easy case, since there are a lot of cell references (and we were lucky, because it was only one spreadsheet and not a spaghetti of sheets. The trick is to use some kind of modeling technique that allows mixing of data structures and calculations. We call them 'Decision Models'.

The Decision Model describes the data structure, the data flow, the calculations and the relations. It just uses a couple of standard symbols for depicting data, models, connections and manual input. A nice consequence of using this type of modeling is that the actual spreadsheet user can read it and understand it. So based on the spreadsheet model we create the Decision Model and discuss this with the creator of the spreadsheet. If this is accepted we can move on to the next step.

Case Spreadsheet Hell - Hedge Accounting

2010-08-09T16:57:00.000+02:00

A nice example of Spreadsheet Hell is a customer, a mortgage bank, that was using spreadsheets to calculate the monthly amortization figures for the coming 10 years. They've build a large spreadsheet with a tab that contained a lot of calculations. Of course this was monthly data, so this tab had to be copied every month, and the formulas had to be connected to the summary tab. Ouch.

In fact this spreadsheet was really 3D. No 4D to be exact. It contained the amounts per hedge per month per month in the future per fact. Imagine putting this on a 2D sheet. You have to make some tough decisions there: either you unfold the data one one tab, rendering it unreadable, or unfold the data across many tabs and creating a monster.

The other problem was the yield function. This function could not be used on a month level. So they used it on a yearly level and dividing the result by 12. Thus less precise results.

O, and there was another problem. The input data had to be copied manually several times per month. Hopefully they copied all records and hopefully the right data.

And then we had the problem that when the hedge manager Bill was using the spreadsheet, everyone else had to wait before making changes.

And the auditor was not pleased that there was no way in telling who made what change.

And the Security department was complaining about authorization. And it was not SOXA compliant. It was nearly impossible to maintain the sheets.

We changed this in a couple of days. Read more about how we did it soon.

Nuclear Power Plant

2010-06-30T10:41:00.000+02:00

When I was reading Winnie the Pooh on Problem Solving, one of the ways to solve a problem was to look at it differently. So you have to leap a ditch without getting wet. How are you going to solve this? Imagine you have to leap an ocean, that would require some unorthodox methods. The trick is to apply these solutions to your ditch.

Control room at a nuclear power plant —Courtesy Tennessee Valley Authority

Now you're back at your little spreadsheet. Let's suppose this spreadsheet is not used for controlling your company, but for controlling a nuclear power plant 5 miles from your house.

Would you trust it? No. Would you have built it differently? Of course you would.

This is what you would have done differently.

Design
Before starting dumping your brain into an empty sheet, you would start to make a design. Yes, writing stuff down on a piece of paper before you start. The data involved, the calculations, the presentations, blah, blah... Don't use any old school design techniques like ER diagrams or UML, because this is something we simply do not understand. And we have to, because we are the ones that are going to build this baby.

Make a test plan
This is the least favorite one. There aren't a lot of people that like testing. Why? I don't know. Maybe because it detects errors in our work? Anyway, it is important. Everyone agrees on that. And remember: we are going to control that nuclear power plant. In this test plan we will test the components of our design and also the entire interaction between the components. We will define some test scenarios to test with. Do I really have to? Yes!

Build
Finally! We can start building our spreadsheet. We weren't used to waiting so long, but it was worth it. During the building we will use some design principles we thought of during the design phase, like separation of data, calculations and the presentations. Maybe at some point we will impress the IT guy! Maybe we should put all the data into one sheet and put the calculations in another, so we can easily update the calculations later.

Test
Hmmm. Why would you want to test something that obviously works? I've just built it, you must think. Remember: Nuclear Power Plant...Melt Down... All Right. But don't do it yourself. Let someone else do the dirty work. And don't get offended when he or she actually finds something.

Launch
Then the big day arrives. The launch of your NPPCSS (Nuclear Power Plant Controlling Spreadsheet). Nothing can go wrong anymore...

...D%#$mn! They are using Excel 2003!

Swiss Army Knife

2010-06-25T10:39:00.000+02:00

In a recent discussion about Excel boundaries, someone stated

"It just seems easier to sell corporate I.T. on a "new technology" to address the pain rather than developing support for a methodology that sustains the business needs without the pain." (see post).

In other words: we should improve the existing spreadsheet situation and alleviate the pain. This does not address the fundamental gaps in spreadsheet technology.

Spreadsheet technology is fundamentally wrong for implementing enterprise critical applications for obvious reasons:

It lacks database technology for storing data (input data and results)
Like normalising datastructures in databases, we need to normalise the calculations
How about supporting multiple users? Enterprise critical assumes that people collaborate based on the spreadsheet
Centralised application management is not really possible because we scatter the organisation with spreadsheets all over the place.

And yes, spreadsheet technology is great:

It's available everywhere.
Everyone can use it. There is no real need for training. In fact most people are self-educated.
Ultrafast delivery: build today, publish tomorrow.

But with the solutions offered to "sustain the business needs without the pain", I really get the feeling we are trying to extend our tool with everything that is possibly missing. It is possible to build a house with a Swiss Army Knife if you would only extend it with a level, saw, Phillips screwdriver, chisel, trowel, drill and a hammer. It is a pain to use it and you still will be tired...

Conservatives

2010-06-21T22:25:00.000+02:00

People don't want to change. Although this crossed my mind when looking at the current spreadsheet world. Like in real politics, people are whining about their spreadsheets being not multi user, it's lacking a real database, everyone can access it. It's a real burden. But no one steps up and says "We need a change" or "We demand better products".

Why is that? Why does everyone accept it?

The answer is easier than you think. Think of alternatives. You could try to find that boxed solution that replaces your spreadsheet.

Likely? I think not.

Then we have another alternative in five easy steps:
1. Analyse your spreadsheet, learn a specification language like UML and write it down in a functional requirements document.
2. Discuss this with your IT representative.
3. He or she will come back at you in a couple of weeks with an estimation.
4. After you sign the contract the application will be developed in some .NET language by some programmer with no knowledge whatsoever on your spreadsheet domain.
5. ...

Likely? I think not.

This is why people don't change. Because they can't. They want to but they can't. It's time for someone to step up and say "Yes we Can"!

Cool Anago Demo

2010-05-25T13:47:00.000+02:00

Alternative for spreadsheet mania.

Simple... or not?

2010-05-17T23:57:00.000+02:00

It seems so simple: creating a spreadsheet that calculates Sales per product based on the Units Sold per Customer, Product and Month. What's actually needed for this? A table with the Units Sold per Customer per Product and per Month. Hmmm. This is a three dimensional structure.

How are we going to solve this one?

Okay, let's flatten our three dimensional structure. We could solve it by using a pivot table, but this would incur other problems later when trying to link formulas to it. We put both the customers and the products on the rows and months on the columns. Luckily we do not have that many customers and products!

The aggregation from units per customer to units per product is done by using a formula:

And so we continue. Let's put the price table on our sheet and hey presto we have our sales figures. Simple isn't it?

No, not really! What are the problems with this spreadsheet?

When we have additional products, I have to insert rows all over the place and add formulas...Hopefully I won't make any mistakes
Same thing when we have new customers (hopefully soon)
I have to enter all numbers manually
I have to copy all the formulas with possible errors
My manager would like to see the products first and the customers on the second column. (Help! )
...

It can be done differently. Anago Software enables you to migrate enterprise critical spreadsheets to a real web based enterprise application. See www.anago.co.uk for more information.

Excel versus C++

2010-05-09T15:55:00.000+02:00

What is the reason that everyone can use Excel to build applications and a few can use C++ for this? Yes, Excel is a development environment, like Visual Studio, JDeveloper and Eclipse. But there is a difference. I'm not talking about the ability to program, but the ability not to program. The reason why everyone can use it, is that you cannot use Excel for writing a flight simulator or a device drive or some embedded software for a copier. You can 'only' use Excel for budgeting, storing addresses, calculating P&L, risk calculations and more. The key is: Functional Scope or in other words being capable of not doing everything.

Most common problems implementing complex spreadsheet apps

2010-05-05T22:52:00.000+02:00

I'm thinking of a list of most common problems when implementing complex spreadsheet applications. Maybe there are some generic solutions for it, maybe some third party components or maybe it's just bad luck.

Data Integrity and Synchronisation
One of the problems is when you use a list of items (like a list of employees or products) all over the place, you have to maintain them in all places. Wouldn't it be nice if it were possible to define a list somewhere and to reuse that list on different sheets. For example one sheets contains the worked hours per employee and another one the private addresses. Any ideas?

Authorization
How about authorizing specific users for specific sheets (or cells) in the spreadsheet. Is this possible without the use of vba?

Separation of Code and Data
It's so easy to create a complex spreadsheet with code and data mixed. But when you've distributed your app to other users and you need to apply a fix, there is a problem. There's no way of sending them an update of some formulas. You have to apply the fix directly to their spreadsheets.

Multidimensional Data
Spreadsheets are two-dimensional. The real world is not. How do you store three, four, or more dimensional data and keep it maintainable?

I didn't want to. Really.

2010-05-04T22:07:00.000+02:00

Okay, so you didn't intend to write that Enterprise Critical Spreadsheet. It started of as a handy app for your personal use until that day that you gave it to Tom. Tom loved it! And Tom gave it to Susan and to Donald. And suddenly you were asked to created something to link the sheets from these departments. No problem for you. And d*&#mn! they found some bugs. Now what?

This is where it starts. The little app has to support multiple users, it has to be maintainable, it has to connect to our business warehouse, bring world peace and fly to the Moon! This is beyond you. So you call Adam from IT. He sends over Catherine, one of his team members, and after she tries to grasp your app she writes it down in nice UML diagrams.

Help! Is this what you want? You don't know. Let's give it a go.

After three months coding Catherine's ready to demo the new App 2.0. Not quite it. But it's a start. You don't want to abandon her immediately. Then another two months pass. And another. And the shiny new web app starts to fade. It can do the basic stuff and if you ask to throw in some more, people start frowning and looking away. What do you do?

Excel 2010 breaks through 4GB limit!

2010-05-03T23:29:00.000+02:00

The latest version of Excel 2010 is released as a 64 bit version. This version breaks through the so annoying 4GB limit. Finally Excel can be used for building --really big-- Enterprise Critical Spreadsheets. American research shows that 0.8% to 1.8% of all cells contain errors. I ask myself: how many errors will fit into 4GB?