BEST PRACTICES USING EXCEL IN CORPORATE ENVIROINMENTS 2

Principle of the Nail or the Screw: (Or Using the right tool for the job at hand)

In an environment of monthly and recurring processes such as marketing, finance or similar, and especially in areas of high volume and high complexity as Corporate Finance, using basic Excel It is not enough to do a good job, and usually creates high levels of "Excel Hell" fast

Part 1 Principles and Best Practices

Part 2 Current Page

Part 3 Hammer and Screws and Examples

Part 4 Databases, PowerPivot, PowerBI, Conclusions

Any area or department that insist on violating the principle of the nail or the screw in their Excel models won′t have enough time to implement continuous improvement processes, and as we saw in Part 1, this will create, sooner rather than later, dangerous periods of Excel Hell.

This principle will sound a little strange in this article. After all, I'm an analyst using Excel as my main tool, and I am a declared Excel lover, and follower of many blogs devoted to the subject. Then, Why I am saying that “traditional Excel” (NO Office 365, PowerBi free Add-Ins and/or advance knowledge of the tool) sometimes is NOT a solution?

To explain, we need to talk about:

· How a professional becomes an Excel Pro

· What are the limitations of Excel (specially with large OR complex data)

 THE EXCEL PROS

Many Finance people, accountants, analysts, and other professionals that use a lot of data and analysis like marketing and sales LOVE Excel... everyone in the team, from the assistants, analysts and managers learn to use it, and some eventually become what the community calls Excel Pros

Becoming an Excel Pro Excel happens, through facing increasingly complex and demanding challenges using Excel, the tool we know and love ...(I hope)

Eventually, the Excel Pro faces a key project. Something important enough, with complex and increasingly large volumes of data, and suddently Excel (and our long trust in our knowledge and use of the tool) faces a crisis...We cannot find a reasonable simple and elegant solution for the proyect, and we end up building something that deep down we know is going to be hard to use, maintain and improve repeatedly in the future…

The reason for this crisis is that Excel until recently (Ejem ... PowerPivot) was NOT designed to handle the Standard Process (Collection and Manipulation of data, Analysis and Reporting) described in article 1, at the level of complexity and volume of data that departments of accounting, finance or similar are currently facing

THE OPINION OF MICROSOFT AND OTHER EXPERTS

It is very important to mention that this is something that Microsoft knows, and has published in official blogs, books, and other forums, and that the Microsoft MVP (Most Valuable Professionals) working in companies from the US to India have repeatedly mention since at least 2004 to the present.

(Meaning, Microsoft and others have been conveying the message of the weaknesses of Excel for at least the past 10 years.)

 What are the weaknesses of Excel that Microsoft and others mention?

COLLECTION AND HANDLING OF DATA (Excel is not a database program.)

 An Excel file can contain sheets with several data tables, and use these tables for analysis with formulas and/or Pivot Tables. This very important method works well for:

• - Small Companies, with low volumes and complexity of data
• -Few security requirements
• -Little need for collaboration (more than one user using the same Excel file)
• -Plenty of time
• -No Possibility to use an audit trail

What happens when our data, our company and the requirements are larger and more complex?

That is, what happens when:

• -We handle large tables with half a million records or more
• -Data Containing groups and hierarchies
• -We need more data security (in an Excel file with data tables, ANYONE that uses the file can copy it with the sensitive or private data that the file contains)
• -More Than one user use the same file at the same time?

-We need to add complex Business Logic to a model or table that need to be updated recurrently (like every 15 days or monthly)

The short answer is: We start to see problems with “traditional” Excel as a tool... (and yes, some of this problems CAN be solved using Office 365 and the free PowerBi Add-Ins, and I will mention these final solutions in later articles…)

I define high volumes of data as 200,000 or more records in a data table that has more than 3-4 columns.

I define high complexity of data when for our analysis we need to relate (join) 3 or more tables and/or add several new columns for our business logic

(You can have big but simple data, and you can have small but complex data, and then you can have both big and complex data, witch is usually the case in corporate settings)

Traditional Excel is not designed to handle this situation optimally. You can force it to do so (abusing vlookups and adding complex columns with business logic to a table you need to update monthy?,) and there ARE certainly many companies and departments with huge and complex data silos residing inside tables in Excel, but any veteran user of these models of Excel that is honest can attest that these models violate many or even All the fundamental features that handles continuous improvement processes:

 (See article 1, the features are: Data integrity, Speed of Response of the Model, File weight, degree of automation possible, capacity for human error.)

The last point is worth emphasis: Forcing Standard Excel (Without using any PowerBi add-ins) and without some database design knowledge to be something that is not (a database designed to handle high volumes and complexity of data in a flexible, efficient and secure way for later analysis) IS possible, BUT if you do it in a way that attacks and damages our 5 features of continuous improvement, you WILL end up gradually but almost certainly condemn to Excel Hell.

DATA ANALYSIS

Excel is very good at analyzing data, particularly using medium to advanced knowledge of Pivot Tables as an analytical tool. In fact, I dare say that if the problems of High Volume or High Complexity of data collection and handling are resolved, Excel IS able to analyze this data

REPORTING

Like Data Analysis, if the problems of data collection and handling are resolved, Excel is fairly capable, using Lookup formulas (Vlookup , Sumifs, GetPivotData and others) To create adecuate reports

I say "fairly capable", because with increased demands for reporting, it is sometimes necessary to produce more dynamic and interactive reports (which avoids bringing with us the long "Support file" full of tables and data, used when management asks a question that is not included in our original static report.) (Statics report can hardly do basic drill-in, cannot view different perspectives of the data, etc.)

With slicers, pivot-table-based dashboards and other recent tools (and Maybe PowerView in Excel) we CAN make more visual and interactive reports in the Excel environment ...

CONCLUSION OF PART 2

Thus we see that the main weakness in Excel for analysis and reporting on large corporate settings with high volume and complexity of data lies in Step 1. Collection and processing of Data. However, as much useful as it is to identify the problem, it does make it less relevant.

Step 1 is the first part of our Standar Process, and if we fail to access and manipulate data efficiently and effectively, or we do it by hurting our 5 areas of Continuous Improvement, we will very probably still get Excel Hell sooner than later ...

  So, How we then escape Excel Hell?

In the next chapter of this series we will discuss WHY even with these clear weaknesses Excel still reigns supreme (and I believe will continually to do so in the near future) as a tool for analysis and reporting, and define specific steps and best practices designed to prevent or break the “Excel Hell cycle”, giving us enough time to create or maintain our continuous improvement processes and thus eliminate or drastically reduce the Excel Hell in our life …

Your comments are welcome ... have you experience periods of Excel Hell in your business?, How do you handle them?