FUN BETWEEN THE SPREADSHEETS

October 11, 2020 Johnny Kipps
forecast5

An Excel-ent solution?

Earlier this month, Public Health England admitted that a glitch in its spreadsheets led to 15,841 positive Covid results not being reported. Why? Not only had Public Health England used an Excel spreadsheet, a program not built to be a serious database manager, but they were using a 2007 version.

This version could handle a maximum of 65,000 rows. When private labs sent in more results than the program could handle, it simply left out the extra data. Talking about the catastrophe on Tim Harford’s BBC 4’s More or Less programme Christina Pagel, Professor of Operational Research at University College London, said: “Excel isn’t meant to store lots of data or to be used as a database. Plus you don’t have an audit trail so there are no records.”

PHE’s “technical hitch” had two consequences: a lengthy delay in track and tracing and a distorted picture of what was happening. “Before the cock-up was noticed, it looked as if cases were in slow decline: afterwards it was clear tha the trajectory continued upwards,” Tim Harford said.

The Internet is replete with Excel Horror stories: one of our favourites (?) that immediately comes to mind is when Lehman Brothers collapsed, Barclays put in a bid for some of its assets. The liquidator sent it a list and, to avoid confusion, Barclays hid the column of assets it didn’t want, and sent the document back. The liquidator printed it out as a PDF, which unhid Excel’s hidden columns with all the Lehman assets. Barclays was embroiled in a lengthy and expensive court case, which it lost, and ended up having to pay for a whole lot of stuff it didn’t want.

Horses for courses

Forecast 5’s creator, Geof Nightingale, says: “Did you know that 38% of all spreadsheets contain errors whilst 90% of people using spreadsheets think they’re correct?

 

“With nine out of 10 spreadsheets (88%) containing errors (Leung, 2014) and with a cell error rate of up to 1 in 20 cells (5%) (Panko, 2015), it is no wonder that your certainty about the figures you are presenting falters. Spreadsheets, while a cheap, easy to use and familiar option, are the cause of your budgeting season’s stress and panic.”

A purpose-built budget and forecast solution like Forecast 5 does a significant amount of processing well beyond a spreadsheet’s capabilities, making it up to 10 times faster.

It creates all the linkages between Profit and Loss records; Cashflow, Balance Sheets and Funds Flow Reports ensuring the double entry rules are maintained.

Compare that to the spreadsheet model:

  • Start with a blank sheet. Spend endless hours building it yourself.
  • Is your budget from last year actually right? Probably not!
  • Do you really want to use it for this year? Doubt it!
  • Who changed it last and deleted that subtotal? Or that linked sheet? What a disaster!
  • Where is the opening balance sheet? What are the rules for cash collection?
  • Have you enough time and energy to spend on the spreadsheet this year?
  • Remember that last minute panic when you finally printed the reports and missed that vital totalling mistake? How embarrassing!
  •  
  • With Forecast 5 we provide a large range of purpose built records, including sales, pre-payments/accruals, stock and wages to simplify the process and ensure the budget is as accurate as possible. We have assets, loans and financed assets. This allows you to take that planned machinery purchase with hire purchase into account, ensuring that you don’t forget the asset capital expenditure, the depreciation, the debt, the interest, the VAT, plus the principal repayment and guarantees the calculations are all correct.

“Having completed the budget, you can perform a “what if” analysis with results reflected instantly, extend the budget out up to 15 years or import your actuals, report variances and update rolling forecasts with confidence that the results will be right every time.”

Click here for a 21-day free trial of Forecast 5, the acclaimed financial forecasting program.