Best Practice Spreadsheet Design & Build

Working with our clients we produce or re-engineer existing information processes to create spreadsheets that deliver what is needed.  When we build Excel spreadsheets we ensure that they are:
  • Capable - The spreadsheet must deliver as required, quickly, believably and attractively. 
  • Understandable - Other professionals will be able to pick up a spreadsheet that we built, and understand it sufficient to be able to maintain it - within 5 minutes. 
  • Reliable - the whole approach will deliver as required, and within that, the formulas, functions and approach taken is appropriate.
  • Consistent - tab usage is appropriate, calculations are transparent and regular.
  • Fast - The speed of response to the consumer is very important: in general, we keep file sizes small and calculations efficient so that users with lower specification machines can still use them. 
It is imperative that spreadsheets deliver information in a manner which supports better decision making. 

Benchmark  Having worked with many Qualified Accountants in many large organisations, we have constructed a Benchmark against which we offer to evaluate and score client spreadsheets. This is made slightly complex because the fundamental purpose plus the audience for whom spreadsheets are intended, affect the built-in capabilities and robustness required from spreadsheets. 

Ease of use is essential in all spreadsheets - commonly this means explaining what formulas achieve in such a manner that any competent spreadsheet professional could understand it - and then maintain the spreadsheet. Wherever possible, efforts should be made to minimise the build time for the overall model,  keep complexity to a minimum, and crucially to ensure that the maintenance of the file in future months and years is as low as sensibly appropriate. 

Information Flow Redesign is a critical early stage in spreadsheet build. The individuals who create business critical spreadsheets must have a sound knowledge of Business Processes, or Process Mapping in order to generate  spreadsheets that genuinely add value. 

When rebuilding and simplifying spreadsheets specialists should always adopt best practices. For more information on this see our STANDARDS page. In summary these are:
  1. Clarity of Objective
  2. Segregation of Data Types (input, output, calculations)
  3. Information Flow based on Accurate Original Data
  4. Data Combination and Processing Transparency
  5. Readable Formulas and Functions
  6. English based readable Macros (if appropriate)
  7. Attractive Final Reports
  8. User Self Selected Options automated (if appropriate)
Better business models arise when the organisation seeks out the root factors that generate the key pillars of organisational existence (revenue, costs and time) and understand how these are influenced and changed. Commonly, this adds two deeper layers into forecasting models - but it adds much more significantly to the decision making process. 

 
 
 
Spreadsheet Simplification

Our spreadsheets use simple functions that your staff will understand and follow, so that computations are transparent and understandable. We adopt Best Practice and so ensure that all formulas are understandable and appropriate to the level of expected spreadsheet knowledge of the user and administrator.

Structure: The key to simplification is to have a plan (or map) of the information flow, and what is required on each tab. 

Technical: Formulas and Functions can become incomprehensible in the wrong hands. The use of range names is crucial, and the simple number of formulas and how they are integrated should be completed by a competent individual. 

Macros: Generally, we recommend that simple Macros are included in the spreadsheet, so that simple tasks can be performed at the click of a button (e.g. updating the imported data, or printing out a key information report). If macros are used, they must be annotated to describe what the Visual Basic is doing, and the code must be easily understandable in ordinary English. Explanations must be at least every 10 lines of code. 

Built in checks: Every spreadsheet should have adequate controls and alerts within the design, so that routine users know if the data is not correct, or where data is missing. This is simple enough to implement, but is often overlooked. 

When your spreadsheet follows Best Practice, it will be:
  • Easy to understand
  • Easy to review
  • Capable of being relied upon
  • Delivering confidence


 
 
 
Project Timescale

You will need a reliable and knowledgeable person to project manage and lead the implementation in your business. Better still, have a small project team that will include your key divisions and departments.  

For each department to implement the changes, the stages are:
  • Project comprehension.
  • Gather personal knowledge.
  • Agree data structures and protocols.
  • Implement.
  • Monitor, review and modify as necessary. 

It is possible to implement these changes within 3 months in one department, where everyone understands quickly what is needed, and IT become facilitators rather than barriers. For some, it may be that  a phased implementation is sensible - this may depend on your clients and external constraints.


 
 
 

What is the cost?


Fundamentally the total costs will reflect the time taken to deliver what you need. This will vary according to the number of staff you employ, and the level of their expertise in Excel.  
In this project there will be the following costs: 
  1. Initial key person knowledge creation
  2. Technical Planning
  3. IT implementation 
  4. Roll out to department
  5. Training of staff
  6. Roll out to client interface
  7. Monitor and improve
We operate on the basis that you will have a much more successful implementation if your staff lead the change, and not our consultants. Use us as your specialist and knowledgeable experts and advisers - this will minimise external costs and maximise the gain from success. 



 
 
 
Technical Complexity must be appropriate

It is always possible to hire in specialists who can develop advanced spreadsheets that use complex and difficult formulas,complex macros, or who create all sorts of advanced statistics and detailed calculations that could be useful.  We recommend that this complexity is avoided as it will become redundant very quickly, need updating - and then your decision making is being outsourced to the specialists. 

We recognise that the full extent of spreadsheet complexity must be within the competence of the staff available to maintain them. Therefore, you will need to recognise three aspects of skills:
  1. The skills and competence of your staff and colleagues
  2. The skills required to update and maintain the spreadsheets
  3. The level of skills required to build the spreadsheets