Rail franchising: Excel error strikes again
You might also be interested in
John Stokdyk sadly passed away in June 2023. He had been with the site since 1999, rising from news editor to editor in chief, global editor and head of insight. As a roving editor, he investigated the profession's use of technology around the world. He devoted his spare time to technology history and an oddball collection of stringed...
Replies (24)
Please login or register to join the discussion.
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?
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.
£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 @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.
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.
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.
QA
Because my work is of such fine quality I'll quote £25 million.
Plus I've got a duck pond to maintain.
Spreadsheets
and in the real world Branson gets to keep his franchise so the PR campaign worked very well
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.
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?
It was the wrong hymnsheet.
@sysmod
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!
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.
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?
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?
Transposition?
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.
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....)
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
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.
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.
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.