PRACTICES FOR BUILDING A GOOD DATA MODEL
In this article, I have jot down few points which might be helpful to build a Good Excel Data model. These are:
- Model should be user-friendly.(Excel knowledge of user/customer should be kept in mind)
2. Do Wireframing Activity.(Decide which sheet tabs will be there in the Excel file like input, output, calculation etc. among many).
3. Make a list of inputs required & from where you can get them.
4. 1st few rows should be there for variable name.
5. Every Column should have Unique Name.
6. Each Column should have one type of data.
7. Prefer not to use Merge & Center, instead use Center across Selection
8. Every Cell should have something in it; if left blank intentionally then a note/comment should be there justifying the reason.
9. Enter the data only once & do linking elsewhere from this main source wherever required & also try to prevent “Daisy Chaining”.
10. Use Name Range wherever possible.
11. Column & row heading should be with unit/symbol (like $, %), if any; same to be followed for the data inside.
12. Proper Colour Coding of data should be there with its explanation documented
e.g. Blue font or beige background for input cell.
13. Be consistent in what you write once should repeat itself. (One can use data validation option for this)
e.g. Positive or POSITIVE
14. Allow minimum number of people to work on model to avoid confusion.
15. For Numbers, use Round whenever required.
16. Have Count, Max, Min, zero check.
17. If one is writing 0, then he/she should right the reason in the form of note.
18. Keep model flow logical from left to right/top to bottom to help excel calculate faster & avoid probability of circular referencing
19. Use Spreadsheet provided Styles whenever possible to save time
20. Document your assumption, source data location & unique calculation at the requisite location or in the form of comment/note.
21. Never use a value in your formula except the standard ones (like 24 hrs in a day is standard) that will not change.
22. Document your inflows & outflows of model by flow diagrams.
23. Use same formula per column or per row as desired; if want to break at some point highlight it to indicate.
24. Avoid the temptation to put everything in one sheet; use multiple worksheets for multiple data blocks.(e.g.: one can have different calculation or input sheet as desired)
25. Split the calculations in the Calculations/Working Sheet logically.
26. Highlight the changes done in the model & comments to justify the change.
27. Perform Scenario & Sensitivity Analysis, Stress Testing wherever possible which justifies your output.
28. General notes & instructions about the model should be entered in the cover page of the model, but notes relating to certain worksheet should be placed in top area of that particular sheet.
29. Use Enter key rather than clicking anywhere else when done with formula.
30. Do cell referencing as desired( Absolute, relative or mixed).
31. Protect the Worksheet/Workbook as desired.
32. Use Memory Saving Techniques given below:
32.1. Use less Pivot Tables as they occupy the memory (Copy paste the table)
32.2. Delete Empty Cells in case file size is unexpectedly large as there may be a case of unknown formatting.
32.3. If you have any external links in the file, then open the source file also, as model may work faster.
32.4. Use array formulas as they occupy less memory.
32.5. Remember INDEX, CHOOSE & OFFSET uses less memory than LOOKUP & SUMIF.