When a term in this article has no clear or specific meaning to you, internet as always is your friend. With this disclaimer, let's start ...
Read
Part 1 Current Page
Part 2 Excel Weaknesses in corporate settings
Part 3 Hammers and Screw, and Examples
Part 4 Databases, PowerPivot, PowerBI
Part 5 Push Vs. Pull
DEVELOPMENT OF MODELS AND REPORTS IN EXCEL
1. The models and reports should be as simple and flexible as possible.
By simple, I mean easy to create, understand and use, especially by someone who was not the original creator of the model. That is, a model that use pivot tables to analyze the data is preferable to other methods involving less standard structures, or more complex formulas and processes, because it is simpler
By Flexible, I mean that one model should be able to be used for the greatest number of cases using the least amount of changes in the model. Data models with inputs that are used to change the month of the report or the area to be analyzed are more flexible than rigid models that do not accept inputs
THE STANDARD PROCESS
2. The standard process to develop a model or report, applicable for Excel, it is:
a. Get the Data, prepare it (ETL) and Add Business Logic
b. Analyze the data
c. Prepare the report
3. The model or report must be continuously optimized, improving details on each stage of the standard process. Improvements in initial stages generally create greater impact in efficiency and effectiveness than improvements at later stages. That is, usually, an improvement in step a) -obtain data and prepare it- creates a greater improvement than c) Preparation of the reports. This is because as always if the model get bad data, garbage will come out of it
4. Continuous improvements should be done gradually, in small increments, and in each financial or sprint cycle (if you use Agile PM. ;-)) Failure to carry out this process continuously allow or worsen any level of "Excel Hell " in an area
EXCEL HELL
1. Excel Hell is a term used by many analysts worldwide (search the internet!) And is defined as:
"The status of a department (accounting, finance, HR) when more time is spend massaging or giving shape to the data in Excel than performing a useful analysis of that data, in a way that will provide value to the company"
2. Assuming a dynamic situation in the area (the area faces increasingly volumes of transactions, more management reports or requests, more sophisticated reports, or the occurrence of Mergers and Adquisitions, etc.) Not having a strategy specifically designed to handle Excel, warrants the gradual occurrence of Excel Hell
3. A useful metric for measuring the degree of Excel Hell in the enterprise is to measure overtime (after 5.30 pm) necessary in an accounting cycle (aprx., 30 calendar days) for finance or other related areas may to complete all reports, presentations, and other responsibilities required of them.
4. continuous periods of more than three months of serious Excell Hell may jeopardize the optimal team's ability to respond effectively and efficiently to the demands of the area.
5. Periods of severe Excel Hell of 6-12 months or more seriously jeopardize the ability for optimal response of team, and have the potential to affect the mood and health of all.
6. With a long enough and severe enough period of Excel Hell, any team, regardless of their initial capacity, knowledge, talent or experience level reaches their level of incompetence
7. incompetent teams made mistakes which have serious repercussions on the integrity of the data, the response speed , and eventually the confidence that the company has in its finance department or other area
CONTINUOUS IMPROVEMENT
As mentioned, one of the strategies used to counter Excel Hell is a conscious process of continuous improvement, enhancing each stage of the standard process in our Excel models or files.
THE 5 ASPECTS OF CONTINUOUS IMPROVEMENT
a. The integrity of the data
b. The speed of the model
c. Weight Excel file (affects the speed of response)
d. Level of automation possible.
e. Reduction of the ability to make a mistake
In the next installment we will describe methods and best practices of continuous improvement, and finally in the last article of this series use these best practices to develop specific operational methods (how to) that will simplify our daily work ...
Do you experience Excel Hell in your company or area? Which steps do your team generally take steps to control their potential "Excel Hell"?