To Excel using Excel

Number crunching is a prerequisite for managing a complex business challenge. Before the advent of computers, handling a large volume of data was not only difficult but also overwhelming. Computers have made the task very easy. They have driven a new lease of life into "lifeless numbers" and made them "alive". Numbers are now capable of speaking for themselves and provide new insights into business situations, which in past was very difficult if not impossible. Undoubtedly, Microsoft's "Excel" has played  a significant role in facilitating this activity. However, for one to excel using "Excel" focusing on some simple principles can be very handy. The note is an attempt to summarise these principles. These principles have the power to not only improve efficiency but can sharpen one's ability to analyse complex business problems with ease.

Sophistication is Simple

The biggest symptom of an incorrect approach is the symptom of it become increasing complex. Isaac Newton once said that "Nature is pleased with simplicity, and nature is no dummy". Since "numbers" are the greatest gift of nature, and they emulate this rule perfectly. Hence, if the solution to a business challenge is becoming increasing complex, then one must be surely on the wrong track. That is a good enough symptom to do a course correction. "Simplicity" acts as a beacon for us to be able to a navigate complex business problem without getting lost in between. So the model that we use must be as simple as possible.

Simple is Elegant

How can we know that our model is simple? It has to look elegant. Elegance is a perfect measure of simplicity. Nature in its native form is simple and beautiful. Once contaminated it becomes ugly and complex. Hence, the biggest challenge is to synthesise a complex business problem and structure it into a model as simple as possible. The symptom of elegance is that the numbers in the model share a common harmony. It should look beautiful in formatting and colour. The formulas and formatting used in the cells can be seamlessly dragged from one end of the table to the other end without it getting disturbed. Distortions (in formulas/formatting/colours) and should be avoided as much as possible, and marked clearly with a specific colour in case they are absolutely necessary. 

Elegance is Robust

The biggest challenge of a business modelled in Excel is its "maintainability". As you traverse through the problem you might have to make changes. Some of these changes are extremely difficult to visualise in the beginning. Just like the measure of simplicity is elegance, the measure of elegance is the ability of the model to scale with relative ease. For this one has to keep in mind the following.

1. Keep the input data sheet deprived of all formulas.

2. Keep the working sheet deprived of all inputs except a handful absolutely necessary. These input cells should be clearly marked.

3. Formulas should not get impacted by the addition of new rows and columns.

4. Formulas should not have any hardcoded values. 

5. Use same column and row identifiers in all sheets for easy processing (VLOOKUP and SUMIF formulas etc)

6. Multidimensional data input sheet should be colour coded (for easy maintainability/debugging and identifying incorrect inputs).  

7. A secondary sheet should be generated by these colour codes for inputting into the worksheets (you might need to write a simple function). 

8. Avoid using VBA Macros as much as possible as it does not scale.Instead, if needed use user defined functions. 

9. Document the changes made in a separate sheet for every version.

10. Use "Conditional formatting" for displaying multiple dimension in a single table. (Caution : Conditional formatting is very powerful but one needs to be careful of not cutting and pasting information on cells which are conditionally formatted, as it will damage it sometimes beyond repair). 

11. Keep the formulas as simple as possible. Bigger the formula the larger are the chances of error.

Robustness Scales

The biggest challenge one faces is to be able to put to leverage one's past work in the future models. That is only possible if one is careful in managing the robustness of the current model that he is working on. This model should scale and can withstand future changes/modifications without much difficulty. This will prevent repetitions and will enhance efficiency and productivity. The enticement of saving time by following shortcuts in the interim is the reason of a model falling apart in future. Hence, one has to train one's mind to avoid that religiously.

All of us can excel using Excel. This does not require much training and complex programming skills. It only needs one to be disciplined in following simple steps as identified above. This will make us more productive and working a more pleasurable experience.