David Lyford-Smith

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

ICAEW Excel tips: Spreadsheet competency framework launches

David Lyford-Smith

14th Jul 2016

4 comments

David Lyford-Smith

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

The ICAEW’s IT faculty’s technical manager announces the launch of their spreadsheet competency framework which detects how capable spreadsheet users are.

The big Excel story for us at ICAEW this month was the publication of the Spreadsheet Competency Framework. At a launch event on the 6 July, around 70 experts from across the fields of modelling, recruitment, and training joined us in the Great Hall to discuss the new publication. Simon Hurst has talked a bit about the framework before, but you can get up to speed by reading the full guide here.

The framework is a simple system for helping classify how able different spreadsheet users are. It has four levels, and a mapping of common Excel tasks and skills to that system of four levels. We want to provide a guide that will help anyone that needs to have a high-quality, clear communication around spreadsheet ability and know that their meaning is understood by others.

Whether you are looking to hire or get hired, arrange some training or just do a bit of self-teaching, I think the framework will help you out. ‘Proficient with Microsoft Excel’ is a great cliché of CVs – we hope to be able to move past that to something a little more meaningful.

In particular, the framework is a free conceptual document, and available to anyone that wants to read it. It’s possible that training and/or certifications will be designed around it in the future, and we’re open to discussing those commercial opportunities with the companies that want them, but right now the focus is on raising the bar for communicating about spreadsheet competency.

Tip of the Week – On a day like TODAY

I’m a big fan of making date calculations in Excel – simplifying things like projecting deadlines, counting debtor days, and so forth. Excel can do most simple date calculations with just basic arithmetic. For example, to calculate the difference between dates stored in cells A1 and B1, you can just do =A1-B1. Or to find a date 30 days after a date in cell C1, you can use =C1+30. These simple manipulations work because Excel stores dates as serial numbers.

The TODAY function, written simply as =TODAY(), is a great time-saver with these types of applications – because it will automatically update to show the current date when the workbook is being viewed. This lets you calculate e.g. the number of days before a deadline automatically, without having to update the current date as you go. Likewise this is useful for computing prepaid/accrued amounts.

TODAY uses the system date – the one set for your computer and which drives e.g. the desktop clock. If that is wrong for any reason – or if e.g. someone in a different time zone is viewing the same workbook – then it will be adjusted accordingly.

Sometimes you may get data that includes times as well as dates – usually in a combined cell that will look like a number with a decimal element if converted into the General format. In this case, you might prefer to use the current-date-and-time function, NOW, written as =NOW().

Principle of the Month

This month’s selection from the 20 Principles for Good Spreadsheet Practice is #1:

Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly.

Appropriately enough for the first principle, this is a very fundamental one. It reminds us all that you can’t just sleepwalk into how spreadsheets are used in your business – it’s important to consider how important they are and how they are used, and then consider how to create, implement, and communicate a plan of best practice based on that information. For example, if you only use spreadsheets sparingly, then the 20 Principles themselves are probably enough of a standard. But in a spreadsheet-heavy environment like a financial modelling organisation, then a more formal modelling standard might be more appropriate.

David Lyford-Smith is a Technical Manager in the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more. Find out more at this link.

Tags:

Excel
ICAEW

Replies (4)

Please login or register to join the discussion.

By Fastlane

22nd Jul 2016 15:17

For a something that is sorely needed, and with such promise, the outcome delivered is very disappointing and underwhelming!

In my view, the weighting (i.e. Core must know item vs Beneficial should know a fair bit about) of competencies across the four roles identified significantly dumbs-down the level of knowledge required (particularly at the top end).

If Developers are determined to "represent the truly expert spreadsheet users, who are familiar with most of the core functionality of spreadsheet packages, and are able to develop high-complexity spreadsheets in a multi-user environment", why would you NOT expect them to "must know" ALL the items listed - plus two important elements not covered at all: customization of the spreadsheet application, and use of templates!??

I would be very reticent about hiring a so-called developer/expert who was not able to explain complex calculations, couldn't detect inconsistent formulas, couldn't readily demonstrate Group sheets or Goto Special, or all types of formula (i.e. Rounding approaches, Text, Financial, Date, etc.), AND couldn't explain and build a custom number format, among much else!

Thanks (0)

Replying to Fastlane:

By David Lyford-Smith

25th Jul 2016 09:44

Hi - David here.

To direct a response to your post - yes, certainly experts should have a great deal more knowledge. But our feeling was that mandating every item was excessive - for example, someone may well be an accomplished model builder, data analyst, and VBA developer, but have not needed to use (say) Go To Special, and hence didn't know it already. That person is clearly an expert user - but spreadsheet packages are very wide and expecting someone to know "everything" before calling them an expert is unrealistic.

Now, certainly there is room for distinction between the 1% that are at the doorway of the Developer level and the 0.1% that know most or all of what we have listed - but we didn't feel that the framework should be focusing on that section of the audience when there are already so many excellent tools for identifying top-end experts out there. We feel the largest benefit is in building robust minimum standards, not aspirational top-end ones. Certainly if the skills you list are important for a role, then go ahead and require them - but many others would not need them. Ultimately the journey of a 'super user' will require learning most everything on this list - but realistically someone is a top 1% expert long before then.

Thanks (0)

Replying to Savia:

By Fastlane

29th Jul 2016 04:56

David
Most of the competencies listed are not what I consider top-end, but functionality (& little in the way of technique) that most Advanced users (but not yet "experts" or "gurus") ought to know, hence my call that this framework dumbs-down the knowledge required - particularly by the Developer role. As such, I don't know how you can reasonably argue that proficiency in all the competencies listed is "aspirational" and that "many others would not need them."

What are the "so many excellent tools for identifying top-end experts"?

I'd be interested to learn where you got your statistics regarding the "1% that are at the doorway of the Developer level and the 0.1% that know most or all of what we have listed" - I think there are far more users in that space. I would also argue that a user who is an "accomplished model builder, data analyst, and VBA developer" as you describe is more a niche expert rather than application expert, and by definition this framework is about knowledge of spreadsheet applications in general.

Thanks (0)

Replying to Fastlane:

By David Lyford-Smith

02nd Aug 2016 11:21

I think that perhaps there is a difference in our focuses that is leading to the difference in our interpretation of the spreadsheet user landscape. Our committee of volunteers that worked on the creation of this covers training providers, financial modellers, VBA developers, and so on. Especially from the training data, our hypothesis that the overwhelming majority of users are non-expert is borne out. Microsoft's own telemetry data shows that the majority of spreadsheet files contain no formulas whatsoever. Our concern is with minimum standards - and certainly I would expect a Developer to, as quoted elsewhere in the document, be able to learn most any new Excel feature given the need to. The levels are about the pre-existing, universal, minimum knowledge that certifies an expert. For example, we had many modellers that had never used a PivotTable in their lives - but could create large and complex financial models. There are differences in how expertise manifests.

In terms of the tools I mentioned, there are several qualifications and assessments on the commercial market. As a non-commercial, it's not really appropriate for me to recommend any particular one.

Ultimately, if for your own purposes the 'Developer' standard is too low - then by all means, expect more! We are not looking to be a panacea for spreadsheet knowledge here - we know that many will have specific situations that one standard cannot reflect. But I wanted to take the time to thank you for your engagement with this - it is helpful to me to think about additional perspectives for when we revisit and update this in future.

Thanks (0)