When spreadsheets are created ad-hoc, the usage of time steps tends to be inconsistent: advancing by rows in one sheet, columns in another, and even a mix of the two in the same sheet. Sometimes steps will be weeks, other times months, quarters, or years. This is confusing for users and reviewers, leads to low trust, increases the time for updates and audits, and adds to the risks of the spreadsheet.
A better way is to make all calculations follow a consistent layout, either across rows or columns, and use that layout for all calculations, regardless if it requires a few more rows or columns. For example, one way to make calculations consistent is with time steps going across the columns and each individual calculation going down the rows:
Author(s): Stephan Mathys
Publication Date: June 2021
Publication Site: Small Talk at the Society of Actuaries
Cell maps are intended as tools for reviewing spreadsheets. If you spot an error or an inconsistency in the cell map this should be recorded and, if practicable, corrected.
The cell mapping software provides a method for recording a reviewer’s comments. All comments are linked to a specific map (or data table), The comments for a workbook under review are collated in a single worksheet.
Jørgen Schøler Kristensen, Medical Director at Aarhus University Hospital, explains the error as follows:
– When patients with a civil registration number beginning with 0 had to be entered, the civil registration number was declared invalid if a hyphen was missing. So 26 percent of the civil registration numbers we have reported have not been entered correctly, so they have not received the invitation in their e-box to be vaccinated as they should have had, says Jørgen Schøler Kristensen.
Patients born the first nine days of any month and having been unlucky with a hyphen were forgotten.
You might recall the “London Whale” incident in 2012, when notorious trader Bruno Iksil — whose other monikers include the White Whale and even Voldemort — conducted a series of credit default swaps that cost JPMorgan Chase $6.2 billion.
What you might not know, however, is that half the loss was incurred by a simple Excel spreadsheet error.
Bloomberg reports that the Excel model, which relied heavily on copy and pasting of information, accidentally “underestimated risk by half.”
The sheer versatility and accessibility of the spreadsheet has made it the Swiss Army Knife of modern day productivity, inserting itself into almost every workflow across every industry. Over the past three decades, spreadsheets have become the de facto way for information to be collected, distributed and analysed.
But, as our operational and computational needs become ever greater, the limits of Excel become clear, and opportunities emerge for companies and tools to replace the spreadsheet.
The Canadian Revenue Agency (CRA) presented a spreadsheet of Canada Emergency Wage Support (CUSS) that was riddled with errors and cut some business subsidies in half.
To qualify for SSUC – which has so far helped nearly 400,000 companies with grants totaling $ 59 billion – employers must fill out “spreadsheets”: Excel spreadsheets that ARC has been offering on its website since March 15th.
However, the spreadsheet available to companies for payment periods from July 5 to August 29 contained several major errors until last Friday, nine days before the deadline for grant applications.
This mistake had the effect of drastically reducing support for businesses, but only for some owners and their families. It only exists in the French version of the spreadsheet.