Save content
Have you found this content useful? Use the button above to save it to your profile.

Rail franchising: Excel error strikes again

10th Oct 2012
Save content
Have you found this content useful? Use the button above to save it to your profile.

Spreadsheet modelling errors are being blamed for the botched bidding process on the £9bn West Coast Main Line rail franchise contract.

The unravelling of the franchise bidding process was the result of frenetic legal and political lobbying last week. But informed sources pointed their fingers at a flawed Department for Transport (DfT) forecast model as the main reason for its humiliating and costly U-turn.

The four bidders will be refunded a total of around £40m; with three other franchise bids put on hold, the ultimate cost to the taxpayer could rise to as much as £300m.

After awarding the contract to FirstGroup on 15 August, the department faced a very public counter attack from current franchise holder Virgin Rail.

Virgin commissioned specialist transport lawyers at Europa Partners to report on the DfT analysis and their criticisms of the forecasting and risk models formed the basis of Virgin’s application for a judicial review of the decision.

The then transport minister Justine Greening called in auditors from her old firm PwC to review the process and data. She was moved in the ministerial reshuffle on 4 September but PwC alerted her successor Patrick McLoughlin that errors had been found in the way the risks were evaluated in each bid.

“Mistakes were made in the way in which inflation and passenger numbers were taken into account, and how much money bidders were then asked to guarantee as a result,” the DfT said when it reversed its bid decision.

At the heart of the bid process were spreadsheet projections of ticket sales, passenger numbers, and investment plans from each bidder. According to Channel 4 News, these forecasts were matched against projections of gross domestic product and inflation until 2026 to convince the DfT that money would be forthcoming from the franchise holder at some point in that timeline.

The issue has been debated on the European Spreadsheet Risk special interest group (EuSpRIG) Yahoo! forum. Some suggested the forecasting errors and risk assessments were procedural and would affect any computation, whether in a spreadsheet or not.

One of the flaws in the process was that the economic assumptions were only checked at a late stage, when the transport department calculated the size of the risk bond to be paid by the bidder.

When FirstGroup was initially awarded the contract, an up-front £200m security payment had been factored into the deal in case it walked out; Virgin claimed its calculations showed this should have been nearer £600m.

Another member of the EuSpRIG group commented, “When there is a financial incentive to put in assumptions that may get past the scrutiny of those with less insight than the proposers, bad data sometimes does get in.” In other words, an incorrect figure may not always be a mistake.

Three civil servants were suspended as a result of what the new transport minister called “deeply regrettable and completely unacceptable mistakes” in the way the bid process was managed.

Government spokesmen insisted that ministers could not be expected to spot errors in highly technical spreadsheets, but Margaret Hodge, chair of the Commons Public Accounts Committee, said ministers were to blame as the DfL has a “lousy record” for rubber-stamping inflated passenger growth numbers. Her committee, and the Commons Treasury select committee, will be examining the episode in more detail.

In the meantime, the DfT and PwC are reviewing what happened and why, with a view to delivering an initial report by the end of October.

Replies (24)

Please login or register to join the discussion.

By TaxTeddy
10th Oct 2012 13:56

BIG predictions

".......these forecasts were matched against projections of gross domestic product and inflation until 2026...."

Pehaps they should tell the Treasury what they know?

Thanks (2)
By duncanphilpstate
11th Oct 2012 10:53

When were PwC called in and with what brief?

Interesting that it seems Greening called in PwC before she was moved. That suggests that she had been told/realised that something was wrong with the bid process/ Or maybe it was just in reaction to Branson pushing at the door of the Law Courts?

No doubt Virgin have a model of their own and put what they knew of the First bid into it and came up with something unbelievable.

In this type of forecasting bid process, where 15 years require huge assumptions and there is mega-money at stake, wouldn't it be sensible and worthwhile to follow the aircraft inducstry approach and have three different independently developed models cross-checking each other? Relying on one model reduces the opportunity to spot the wrong conclusion.

Thanks (1)
By jndavs
11th Oct 2012 11:07


How on earth does putting in a bid cost £40 million?

Thanks (0)
By machon
11th Oct 2012 12:05

£40 million

Putting in a bid doesn't cost £40 million - Virgin have stated that theirs cost £14 million. The £40 million is the cost of all four bidders, only two of which went through the final stages. In this respect the article is wrong.

The cost of bidding for rail franchise has gone up enormously over the years as problems have been encountered and DfT has tried to stop them happening again by asking for more paperwork. It's a huge overhead on the industry.

Thanks (2)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
11th Oct 2012 13:53

Thanks @machon - article corrected

The sums involved threw me a bit as well... leading me to be somewhat over optimistic about the way the compensation figures were derived (pot/kettle syndrome?). Thanks for your input, Machon - you sound quite well informed about the process.

We're waiting to find out more details from the PwC/DfT report and the Commons hearings, but I'm still trying to pin down the rumour suggested by Richard Murphy, among others, that the Excel forecasts submitted by FirstGroup exceeded the number of passengers that the franchise’s rolling stock would be able to carry...

Any further details and background welcomed - private message me via AccountingWEB if you'd prefer to do so more discreetely.

Thanks (1)
By justsotax
11th Oct 2012 14:08

phew...only £14 million....

for a minute I thought costs were getting out of hand....

Thanks (1)
By jndavs
11th Oct 2012 15:19

£14 million

For a cashflow?

Not a bad days work!

Thanks (0)
By Stalytax
11th Oct 2012 16:21

How to save £7 million

If any of the parties want to rebid, I'll do it for a mere £7 Million. 

For that money, I'll even check the cells in the spreadsheet.


Thanks (4)
Replying to Kate Cottrell:
By duncanphilpstate
12th Oct 2012 11:18

Low balling? what lowballing?

I'll do it for £6.5 million!

Thanks (0)
By dominovision
12th Oct 2012 11:32

Printing presses are ready to roll

Won't really be a cost to the government really, am sure they are warming up the printers already (not that they have had chance to cool down from the other £375bn they have printed) and will just roll off a new crisp £40m batch of her majestys finest paper to cover the refund.

Thanks (1)
By jndavs
12th Oct 2012 11:45


Because my work is of such fine quality I'll quote £25 million.

Plus I've got a duck pond to maintain.

Thanks (0)
By Ken of Chester le Street
12th Oct 2012 13:21

Spread sheets

Mine don't work either!

Thanks (0)
By hopeydaze
12th Oct 2012 15:00


and in the real world Branson gets to keep his franchise so the PR campaign worked very well

Thanks (0)
By brian.barrett
12th Oct 2012 15:42

Probably not spreadsheet error

From what is being said in this article, I would have to agree with the european interest group in that it is not an error as a result of the spreadsheet, but an error that would have ocurred anyhow even if it was done on the back of a [very large] fag packet.

As such I am a little annoyed with the headlines that feed the reader to the post stating that it is a 'spreadsheet error'.

One thing that it does highlight, however, is that all spreadsheet models must provide a clear path as to what builds up the final figures, and must show intermediate information that has some link with reality (admittedly difficult when using models of inflation until 2026) so that both the spreadsheet author, the reviewers and the intended receipients can see whether the figures look realistic.

The input assumptions should also have some check on how critical they are - i.e. what effect a small change in the assumption has on the final figure.

Thanks (0)
By sysmod
12th Oct 2012 16:08

Why not all the same hymnsheet?

If it was a DfT projection error, would not all the bidders have been working from the same data?

What was different about Virgin that they realised something was wrong?

And why did Virgin take the legal route rather than simply pointing out a flaw that inflation was not taken into account which would have blown it open earlier?




Thanks (0)
Replying to I'msorryIhaven'taclue:
By machon
15th Oct 2012 09:42

It was the wrong hymnsheet.


All the bidders were working from the same data. Unfortunately, that data was wrong, so the bids against the data were meaningless.

Virgin are running the franchise at the moment so are best placed to spot errors in the model.

Virgin maintain (in various articles that I've read in the specialist and general press) that they did notify the DfT of the errors but that they were ignored.

I should add that I have no inside knowledge here; I'm just an interested observer who reads everything on the subject!


Thanks (1)
By bseddon
12th Oct 2012 16:23

Hunt the scapegoat

I agree with brian.barrett that the headline is a problem.  It confirms the mindset among spreadsheet users that, somehow, they are not to blame because they were using a spreadsheet.  And based on comments by one of the staff put on gardening leave it seems the DfT is casting around for scapegoats.  Being unable to fight back, the humble spreadsheet is an obvious patsy.

According to the report by Robert Peston in his BBC blog the problem was due the assessed risk of the figures for passenger numbers and profits assumed out in 2025.  Spreadsheets do not assign risk.  Humans do that.  They may use a spreadsheet to compute a risk value based on some assumptions but, again, a human is responsible for those assumption not the spreadsheet.  Even if the spreadsheet were perfect and clearly documented, the assumptions could still be inappropriate or inconsistent leading to an invalid assessment of the value of a bid.


Thanks (0)
By EMichaelJakins
12th Oct 2012 18:45

Don't blame the spreadsheet.

I had one of the first copies of Lotus 123 (remember that?) in the UK and have been writing spreadsheets ever since.

Any error in my spreadsheets have been down to the idiot writing the sheet (Me!) or entering the data (Again probably "your's truely"!).

They should have checked what they have written and done some tests to prove that the resulant modle worked!  There is just no excuse for making such a basic error.

In this instance I have every sympathy for the Minister.  They are not there to check the detail of figures given to them by Civil Servants, they are there to make policy.

Mind you I do think that previous Governments should never have saddled the country with such a convoleted sytem of running a railway!

Perhaps the way forward is to totally re-think the whole francise system and set up a more joined up system - but not back to nationisation?

Thanks (0)
By glenbogle
13th Oct 2012 13:06

Rubbish In Rubbish out

Well .

Might have expected this! Events

Some one messed up. It is covered in the veil of arcane secrecy we have seen with PFI etc.

Was it a wrong formula ? incorreect use of data?


Civil servants have been suspended. Why - the one making the decision is the MINISTER - an elected person not the Civil Servants who are minions of government?  The ministers who made the call are gone - How lucky is that?

Was the Department for Transport using faulty data supplied by the bidders? or as in previous messes did they use faulty data from Government sources? How much of an error was there in their calculations or was the whole basis flawed? If Branson/Virgin ticked all the boxes better  then First group why would you not have a second look?

Did anyone do a costvalue analysis or check how  First Group could claim that they believed that they could expect to reduce sonme fairs over the life of their franchise? Never mind if this would be in real  terms or just in some economic theoretical format. 

Especially when the FirstGroup have  apparently decided to give up the  WestofEngland FirstGreatWestern franchise? 

However,my memory reminds me someone called Branson got the Westcoast Line Franchise in the first competition promising 140 mph on trains and paying  back money to HMG whuich failed  miserably - in fact the Blairwitch government had to backtrack and put place huge subsidies to Virgin Rail to keep them toottling along. Luckily he had an excuse in that Network Rail  had completely messed up on the refurbishment of the West coast main line. But he bought 'Pendolinos' which  are not allowed to travel poast each other on many stretches of current track( still do not exceed 125MPH).

Who can we trust is definitely not answered yet. Virgin Air has immediately launched London to manchester flights to compete with the new rail operator.

I admit that I consider that  Privatising Rail was criminally insane in the model set up by John Major's Tory govt - all that has done is balkanise all the lines trains and operators. Management consultants  etc must think it is wonderland or christmas every day. I'm not envious of that work as It does not help the ordinary [person so far as I can see.  


Thanks (1)
By justsotax
15th Oct 2012 10:05

And this is why computers

will never take over the world....because unfortunately they are worked by humans (or programmed by same said people).


Leave unchecked at your own risk....(or in this case the taxpayers risk....oh to have a lowly pension and paid below the national average in an environment where mistakes are not tolerated....unfortunately the same cannot be said about this employer....)



Thanks (0)
By glenbogle
15th Oct 2012 11:11

The wrong data used

Thank you.

So the right answer was never possible.

Did no one try saying what is the right basis to start from?

and run a comparison with given data?

Oh dear - I think The hymnsheet speaks with forked tongue.

A sense check on government ( ie an election) would appear to be called for with The Coalition Condems being given due consideration for a good try that has clearly got lost.

I'l just awy and slaughter some burds

Thanks (0)
By duncanphilpstate
15th Oct 2012 22:46

Did no one cross-check this model/decision?

I was thinking this evening as this story trundles on - a bit like one of Virgin's trains - that it seems bizarre that there wasn't or at least doesn't seem to have been any independent check of the result within DfT. I mean, here you have a multi-billion pound contract covering 15 years, and the result pops out and everyone accepts it? It doesn't make sense. 

Thanks (0)
By Marion Hayes
17th Oct 2012 17:04

Virgin trains...
gladly were never ones I used but friends spoke of standing room only and poor service - and were very jealous of my journeys on the East Coast line with GNER. My memory says the first franchise seemed to be awarded as a consolation prize for not getting the lottery but I could be mistaken.

Thanks (0)
By Terence_Craven
15th Apr 2013 15:51

How To Avoid Errors In Your Spreadsheet

It's a worrying trend that we're reading on almost a weekly basis. Huge companies reporting massive losses due to Spreadsheet errors.

The truth is many of these errors can be easily avoided. I wrote a post over at Microsoft Training dot net, outlining simple tips and techniques that can be used to eliminate human error and automate the system so that mistakes are no longer made.

Thanks (0)