Los Articulos mas relevantes para PYMES los puedes encontrar haciendo click aqui
1 Comment
In Part 3 of this series, we saw that if Excel is a hammer, loading and handling big and/or complex volumes of data is like placing a screw. Inserting a screw with a hammer is neither efficient nor effective ... it is simply the wrong tool for that part of the job. Other parts of the Series Part 1 Principles and Best Practices Part 2 Excel Weaknesses Part 3 Hammers and Nails and Case Studies Part 4 Current Page But if basic or Intermediate Excel is not the right tool in these cases, what is it? and just as important, How I can implement these new tools so they can work with Excel over the shortest period of time and at the lowest possible cost? Generally, Best practices strongly recommends that when working with Big Data the appropriate tool is a database, along with ways of using Excel more effectively with data in general and databases in particular. And the good news for us analysts, financiers, managers and others, is that implementing these tools do not have to be neither long nor expensive. To tell you truth, the 2 best tools to start addressing these challenges are free... but I'm getting ahead. ADVANTAGES OF USING A DATABASE WITH EXCEL First, it is important to know that databases were CREATED specifically to handle large and complex data. If Excel was originally created for ad-hoc and non-Recurring analysis, databases were DESIGNED taking into account the 5 characteristics of our continuous improvement process (see Part 1) (Integrity of Data, Speed Response of Model, File weight, degree of automation possible, capacity for error.) It is caring for these five characteristics that will reduce or eliminate the Excel Hell and the huge time this rob us in our work Volumes from 100k to 1 million records, where we start having performance, Business Logic and Excel Automation challenges are the entry levels of data expected in a small database. It is a matter of PRICE AND TERRITORY But if this is correct, if a Database Management Systems (DBMS) such as Access, Microsoft SQL Server and Hyperion has been used, from some time now, with good success in corporate settings, to support Excel in handling large and complex data, why so many analysts, managers and CFOs do not know or implement these tools? The most obvious answer is PRICE. Implementing DBMS is expensive, sometimes very expensive. If you have recently spoken with someone from Oracle, IBM or SAP you know exactly what I mean ... we're talking easily between $ 25,000- $ 150,000 or a lot more ... But then I think: we have Access right?, a more than competent DBMS for the job, why we still do not implement it? The simplest answer I have found is that the databases are not our usual territory, EXCEL IS. As Excel Pros (part 2) We have worked in Excel for a long time, and we fought hard to master what we know. Databases for most of us is a strange concept, more associated with IT staff wich accounting and finance generally have a love-hate relationship with. I can almost hear the complain of hundreds of fellow Managers and CFOs: "As an analyst or Finance guy, I feel the powerful desire to control the cost, the tool and the process for the key elements of my work!... If I spent 10-15 years mastering Excel, and half or more of my processes are already based on Excel, you better bet that this “database” that you mention either is IN Excel or works VERY well with it. I don’t what to become an IT guy and learn and talk differently just because we are handling more data Now…” MEET NEW FRIENDS! The best way to continue using Excel and not harm our process or allocated budget, is to start with simple but powerful databases that we already have in our arsenal, (although we did not know,) so you have no extra cost, and start to apply them in simpler and less critical area of our processes so we can master them, and later we can scale them to more and more key processes. What Tools I recommend?, 3 years ago I would say Access first, but after the enormous progress that Microsoft has made with Excel in the area of Business Intelligence, I prefer to speak of new best friends, PowerPivot, then, of his cousins, PowerQuery and PoweView For those who have not yet had time to find out the PowerPivot Add-In, it is an extremely powerful and free tool, part of the new suite of Business Intelligence for Excel developed for Microsoft Excel 2010, 2013 and 2016. PowerPivot incorporate in Excel a real database, fitting the skills and work style of the Excel Pros, allowing us to work faster and in new ways with large and/or complex data CONCLUSION OF THIS DELIVERY The findings we already have are revealing but mildly unsettling: 1. In large or complex data (Part 3) We are facing a new reality, and to place screws and not nails, a screwdriver must be incorporate our toolbox. 2. NOT doing it in a dynamic and increasingly complex work situations WILL take us into quicksands with data (part 1 and part 2) and will stop us from developing continual improvements process, which eventually will condemn us (again) to Excel Hell (Part 1) 3. Using new tools, even in Excel, and even if they are relative simple to implement is a challenge, but we can do so continuously and gradually, free and with our current staff, in small steps and processes each month until we master them and implement then in more complex and key processes... In the end, that's how humans evolved right?, as the challenges we face grow, man (or woman) invents and adds to its arsenal more powerful tools to manage those challenges The very personal question that you must ask yourself is: What do you prefer to do? Implement tactical changes to reduce your Excel Hell (since you're probably already overworked!,) and use that time to gradually strengthen your knowledge and skills of these new and powerful tools (PowerBI) to continually improve our processes? -or- Facing present Problems of NOT implementing features of continuous improvement (Data Integrity, Speed Response Model, File weight, degree of automation possible, capacity for error.) AND being in a constant fear that we will have even worst Excel Hell in our Future In the Next article, we will begin to talk about: 1. tactics that can be implemented in Excel to accelerate and automate our reports. We will discuss Push Vs. Pull methods of bring data into Excel and how we can automate these processes 2. Then we will further introduce PowerPivot, which according to Bill Jelen, the famous Mr. Excel in person and author of over 40 books on Excel, described as the best tool to Excel implemented in the last 20 years ... (after PivotTables ;-)) Long Live to the Excel Pros! FOR A HAMMER, ALL PROBLEMS ARE NAILS…(But, what happens when we have a screw?) This is the third of a series of articles about my experiences with the Corporate use of Excel, if you want, you can read Part 1 and Part 2. As we saw in Part 2, The Excel Pros have so much experience and are so confident in Excel that for us, all problems in accounting, finance and other areas can be solved with more sophisticated uses of Excel. The problem with that belief, as we saw in the previous article is that in the step of Collection and Handling of Big/Complex data, Excel originally was not designed nor was recommended to be the tool to use. There comes a point in the Collect / Manipulate / Add business logic to the data, that moving from simple data to large/complex data becomes not like moving from a small to a larger nail, but like replacing the nail with a screw. In my experience, large and complex data DO NOT requires MORE: more personal, more hours, more work... placing a screw is NOT done better with a BIGGER hammer (or by using THREE hammers by the way,) the solution (one that is sometimes hard to apply even for me,) is to REPLACE, at those times, the Hammer with a different tool. So, in this particular stage, it is best to rely on new tools in Excel that DO work like a screwdriver. I remember when I fought for the first time face to face with problems of Excel as a tool. It was in the process of Budgeting, Controlling and Forecasting: At that time, we spent 3-4 days a week staying until 7.30-8.00 pm onwards ... on Month ends things were even worst. WHY we stayed so late? One reason is that someone, somewhere, made a mistake in the process: - It was often the burden of data: At some point the data upload for the current month was wrong, incomplete or replaced by mistake data from previous months -Other times, someone had mistakenly changed a cell with formulas in a Excel file with many tabs, large and complex, altering the final values. One important note for all managers and VPs in the financial and Accounting Areas: These human errors generally are NOT due to incompetent staff, but the fact that every month you have to repeat the process of loading very long and complex Data, and, sometimes in top of the data you have to add new tables or columns with business logic and analysis... The more complex the process, the more steps there are, and more steps also means more ways to commit a mistake. Statistically, with enough number of repetitions, someone is going to make an error (it could be small, it could be large) WHAT ARE THE MOST INNOVATIVE COMPANIES DOING TO SOLVE THIS CHALLENGE? In part 4 of our installment we will discuss two free tools, PowerPivot and Power Query,that can support Excel to help manage large and complex DATA, and further in the articles,we will develop procedures to gradually go from where we are to where we want to be, in term of reducing our Excel Hell (term is explained in Part 1) As always, your feedback will be greatly appreciated ... Long live the Excel Pros! 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 See 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? Hello everyone, this will be the first part in a series of articles on best practices I've learned through colleagues, bosses or myself (usually throught mistakes :-)) on preparing analysis and reports with Excel for Finance, Accounting and other areas in medium to large companies ... 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"? LO APRENDIDO USANDO EXCEL EN FINANZAS CORPORATIVAS 5. Mejores Prácticas para TRAER data a Excel15/4/2015 RESUMEN DEL ARTICULO Generalmente, los contadores, financieros, gerentes de ventas y operaciones, etc., Usan Excel para analizar su información. Esta información puede llegar de diferentes lados. Cuando trabajamos en una Pequeña o Mediana empresa, esta información generalmente viene de nuestro programa de Contabilidad (Peachtree o similar.) Cuando trabajamos en un corporativo o una transnacional, la información generalmente nos llega de uno (o varios) ERP (Enterprise Resource Planning) como SAP o similar, y/o desde otros sistemas como un CRM (Customer Relationship Management.) Otras capitulos en esta serie Parte 1 Principios y Mejores Practicas parte 2 Las Debilidades de Excel parte 3 Casos Reales y Encuesta parte 4 Bases de Datos, Powerpivot, PowerBI, Conclusiones parte 5 Página Actual Independiente de donde venga la información, el paso siguiente es similar, necesitamos un proceso para traer la información de nuestros sistemas a Excel, para poder analizar la data. Como hacerlo generalmente es responsabilidad de los Gerentes o VP de área, asesorados o no por IT. Saber las opciones que tenemos y como decidir la correcta no es para nada trivial. Tomar la decisión incorrecta en esta área afectara todo el resto del proceso de Análisis y reportaje, ya sea metiéndonos en terribles periodos de Excel Hell basados en Spreadmarts, o permitiéndonos un proceso rápido, fluido y poderoso. De más está decir que el resto de la cadena de mando (Gerentes, Analistas, etc.,) se ven tremendamente afectado por esta decisión, ya que son los que trabajan el día a día de este proceso. En este artículo, posiblemente mencionaremos muchos terminos y conceptos que no conozcas, todos los terminos en Azul te abrirán páginas con información adicional a estos términos. Lo mas importante sin embargo, es que explores y que te des cuenta de la cantidad de opciones que tienes para trabajar data en Excel, y posteriormente explores con más profundidad las opciones que mas te interen o necesites... Idealmente, estas decisiones se deben de tomar en conjunto con el departamento de IT de la empresa, ya que las mejores soluciones a estos retos contienen tanto elementos de la lógica del negocio de los departamentos como conceptos más relacionados a IT. Sin embargo, es muy común en nuestros países latinos una falta de integración entre IT y áreas como contabilidad, finanzas o ventas, lo que dificulta que conozcamos todas las opciones que tenemos y tomar la decisión más sensata. Cuando traemos Data para analizar en Excel, hay 3 puntos a considerar: Cuanta data traemos? · Aquí tenemos 2 opciones, traer la data completa o traer solo porciones o resúmenes de la data. Como traemos la data? · Tenemos 2 métodos, el método Push o el Pull. Como Almacenamos y estructuramos la data? · Nos apoyamos o no en un Data Warehouse o Data Mart o no? Que opciones tomamos al traer la data determinarán enormemente lo que podemos hacer en nuestro análisis, y también determinará si caemos terriblemente en un Excel Hell o si trabajamos de manera optima. A continuación, discutimos cada punto a considerar… CUANTA DATA TRAEMOS A EXCEL? Como vimos en el resumen, para responder esta pregunta, tenemos 2 opciones: · Traer la data completa · Traer solo porciones o resúmenes de la data. Traer toda la data generalmente es lo ideal, pero puede ser como el sumo grande de la imagen...pesado, complicado y poco ágil. Si queremos trabajar con Big/Complex data de manera ágil en Excel, necesitaremos apoyarnos con otras herramientas...A continuación, exploraremos estos temas: TRAER LA DATA COMPLETA Ventajas Traer la data completa nos permita trabajar con el nivel mas alto de detalle (granulidad.) Cuando estamos evaluando data transaccional, a veces queremos llegar al nivel de transacciones individuales para nuestro análisis, por ejemplo cuando estamos controlando costos, y queremos ver si las transacciones individuales de un rubro en particular se adecúan o no a nuestros presupuesto. Lo mismo puede decirse del área de ventas, cuando queremos ver a nivel de ventas individuales de un cliente. Desventajas Las desventajas de este método es que después de cierto punto, se puede tener problemas en Excel con el volumen y la complejidad de la data. Es fácil traer 20,000 transacciones desde Peachtree para analizarlas, pero es mucho más pesado traer más de 1 millón de registros para evaluar por ejemplo, las transacciones individuales de una Transnacional en Brasil registradas en un SAP ERP. Es vital ser consciente que las diferencias en volumen y complejidad determinará las estrategias que podemos seguir, y lo que sirve para casos pequeños en PYMES generalmente no es la solución en casos mas grandes o complejos. Como mencionamos en artículos anteriores de esta serie, cuando trabajas con más de 50,000 registros y/o trabajas con varias tablas de datos, puedes empezar a notar problemas en Excel en nuestras 6 Areas de mejora (Integridad de la data, Velocidad de respuesta de archivo, Peso del archivo, Nivel de automatización, Complejidad del Modelo, Capacidad de cometer un error.) Creo que una enorme porción del Excel Hell que experimentan las empresas se deben a que desean extraer data grande y compleja en Excel, cuando Excel tiene limitaciones técnicas importantes en este sentido (1 millón de registros, toda la data se guarda en RAM, capacidad de crear modelos MUY complejos,) o cuando no considera la opción de traer data parcial o resumida Como tomamos la decisión? Dependiendo del volumen y complejidad de la data… Para 50,000 transacciones, tablas con pocas columnas y no más de 2 o 3 tablas de datos, No hay ningún problema en usar las prácticas comunes de Excel. Extrae la data en diferentes tablas, “aplana” las tablas uniendo columnas de varias tablas con Vlookup o similar, y luego realiza el análisis con Pivot Tables y fórmulas. Para data más grande o compleja, NO uses las prácticas comunes de Excel, ya que gradualmente estarás creando un Spreadmart, con todos los problemas relacionados que en artículos anteriores de esta serie hemos discutido... Para trabajar de manera optima en Excel En casos de Big/Complex data, necesitas tomar en cuenta los otros puntos a tratar, y hacerte preguntas como: -Puedes extraer resúmenes o porciones de la data con SQL o técnicas similares? -Puedes usar métodos Pull para simplificar y automatizar el proceso? -Tienes la opción de construir un pequeño Data Mart con PowerPivot para organizar y agregar la data? -Tienes la opción de usar SQL server Analysis Server (SSAS) o similar? Si tienes un buen equipo en el área de IT de tu empresa puedes discutir estas preguntas también con ellos. Si le haces estas preguntas y ellos te miran con cara de no tener idea de lo que estás diciendo, por favor considera seriamente hablar con el CEO de tu empresa para actualizar los conocimientos técnicos de tu departamento de IT :-) Si tienes dudas o experimentas resistencia en cuanto a la importancia de incorporar Bases de datos para trabajar con Excel, ya escribimos un articulo sobre ese tema... TRAER SOLO PORCIONES O RESUMENES DE LA DATA Ventajas En muchas ocasiones, no necesitaras el máximo nivel de detalle para hacer un análisis y reporte. Cuando estas realizando un Estado de Resultado de un proyecto en particular, no siempre necesitas tener la data de cada transacción, sino que puedes tenerla agregada a nivel de rubros como ingresos, costos, y gastos. En ese caso no tienes necesariamente que traer todo la data a Excel, sino que puedes agregarla primero, cosa que posiblemente tendrás ya hecho si tienen un Data Mart a tu disposición, o puedes realizarlo de manera más manual usando Queries con SQL o PowerQuery. Otra opción es traer la data completa a PowerPivot (Add In gratuito, mayor capacidad de guardar data, mejor capacidad de compresión, posibilidad de usar un modelo Relacional) y trabajar con data resumida con tablas dinámicas. Otra opción será traer solo las columnas que necesitas de las tablas de datos (data parcial,) guardar la data en un Pivot Chache, y trabajar con data resumida con Tablas dinámicas Las ventajas de traer sola la porción de la data que necesitas es tener archivos de Excel mas livianos, y puede simplifica mucho el proceso del análisis inicial de la data, además de evitar las complicaciones de tener columnas de data innecesaria. Obtener data resumida puede hacerse de manera relativamente sencilla usando SQL. Las ventajas de resúmenes (agregaciones o jerarquías) es que puede simplifica aún más el proceso del análisis, ya que puedes tener la data agregada por región, año o producto y solo usar ese dato agregado y no toda la data que se requiere para hacer la agregación. Obtener data agregada puede ser dificil de hacer usando SQL, pero se pueden usar Data Mart basados en Bases de Datos Dimensionales (OLAP,) como PowerPivot o SSAS Desventajas Es más difícil trabajar a nivel de transacciones o registros individuales (pero es aun posible con SSAS o Hyperion) . Sin embargo, esto debes balancearlo con las dificultades que obtienes con Excel cuando trabajas con mas de 50,000 de registros y varias tablas de datos. Cuando Manejas Excel que pesan mas de 25MB y tienes modelos extremadamente complejos para hacer tareas de análisis y reportes periódicos (1 vez a la semana o al mes, como ciertos reportes, como el control de presupuesto, apoyo en el cierre mensual contable, y trabajar con Big data,) estas seriamente invitando al Excel Hell a que entre en tu proceso, con todos los riesgos y daños que eso implica. Además, aunque el límite físico de Excel es alrededor de 1 Millón de registros, obtendrás problemas de rendimiento mucho antes de eso... Como tomamos la decisión? Dependiendo del nivel de detalle de la data que necesitas, el volumen y complejidad de la data, y los recursos y conocimientos que tengas. Del nivel de más simple al más complejo, (y también de más barato al más caro,) podemos resumir tus opciones así: GENERALMENTE GRATUITOS, solo requieres conocimiento del tema: Principios de bases de Datos, Powerpivot, PowerQuery, Access, SQL language, PAGADOS, mas complejos pero más poderosos SQL Server, SQL Analysis Server, Hyperion y Cognos En el proximo articulo, seguiremos descutiendo: Como traemos la data? y Como Almacenamos y estructuramos la data? Por favor regístrate en nuestro Newsletter para recibir nuestros artículos en tu correo... Muchos Saludos y larga vida a los Excel Pros I almost didn't post this article, the issue of burnout is very delicate, and in some companies or people, almost taboo. Besides, this article is a summary of Michael Leiter and Christina Maslach fantastic psychology research on Burnout, and is written in the style of scientific research. Nevertheles, the ideas and insights included in their original article and the suggestion for mitigating burnout are so powerful that I decided to keep the scientific style wrting on this summary... My main reason for publishing this article is that I almost felt into burnout in finance and accounting related fields in by career, an I know that people that are dealing with this situation are desesperatly looking for options, ideas, and ways fo dealing with it...And I would have love to find this ideas earlier in my career. In part 2 of these series, I will include why is so easy for Accounting and Finance teams to fall into burnout and talk about technical solutions (in Excel and PowerPivot of course :-)) that can help... Without more introduction, here is the summary of the article: THE BASIC HISTORY OF BURNOUT For several decades, the term “burnout” has been used to describe a fundamental disconnect between the worker and the workplace. The basic story goes like this: "the worker entered a job with positive expectations, enthusiasm, and the goal to be successful in the job. Over time, things changed – and now the worker has an overwhelming exhaustion; feelings of frustration, anger and cynicism; and a sense of ineffectiveness and failure." The initial flame has burned out. The experience impairs both personal and social functioning on the job, and thus carries some real costs for the individual worker, the people affected by him or her, and for the organization as a whole. While some people may quit the job as a result of burnout, others will stay on but will only do the bare minimum rather than their very best. DEFINITION OF BURNOUT Burnout is defined as a psychological syndrome of exhaustion, cynicism, and inefficacy, which is experienced in response to chronic job stressors. Exhaustion The exhaustion component represents the basic individual stress experience. It refers to feelings of being overextended and depleted of one’s emotional and physical resources. Cynicism The cynicism represents the interpersonal context dimension of burnout. It refers to a negative, callous, or excessively detached response to various aspects of the job. It usually develops in response to the overload of exhaustion. Is self-protective at first, an emotional buffer of “detached concern.” But the risk is that the detachment can result in the loss of idealism and the dehumanization of others. Inefficacy The third component of inefficacy represents the self-evaluation dimension of burnout. It refers to feelings of incompetence and a lack of achievement and productivity in work. In some instances, it appears to be a function, to some degree, of either exhaustion or cynicism, or a combination of the two (Byrne, 1993; Lee&Ashforth, 1996). A work situation with chronic, overwhelming demands that contribute to exhaustion or cynicism is likely to erode one’s sense of effectiveness. However,in other job contexts, inefficacy appears to develop in parallel with the other two burnout aspects, rather than sequentially (Leiter, 1993). Here the lack of efficacy seems to arise more clearly from a lack of relevant resources, while exhaustion and cynicism emerge from the presence of work overload and social conflict. Incidence Unlike acute stress reactions, which develop in response to specific critical incidents, burnout is a cumulative reaction to ongoing occupational stressors. With burnout, the emphasis has been more on the process of psychological erosion, and the psychological and social outcomes of this chronic exposure, rather than just the physical ones. Because burnout is a prolonged response to chronic interpersonal stressors on the job, it tends to be fairly stable over time. SIX AREAS OF WORKLIFE We had identified six key domains: workload, control, reward, community, fairness, and values (Leiter&Maslach, 1999; Maslach&Leiter, 1997, 1999). The first two areas are reflected in the Demand-Control model of job stress (Karasek & Theorell, 1990), and reward refers to the power of reinforcements to shape behavior. Community captures all of the work on social support and interpersonal conflict, while fairness emerges from the literature on equity and social justice. Finally, the area of values picks up the cognitive-emotional power of job goals and expectations. Workload The most obvious, and most commonly discussed area of worklife is overload: job demands exceeding human limits. People have to do too much in too little time with too few resources. Increasing workload has a consistent relationship with burnout, especially with the exhaustion dimension (Cordes & Dougherty, 1993; Maslach et al., 2001; Schaufeli & Enzmann, 1998). Structural models of burnout have shown that exhaustion then mediates the relationship of workload with the other two dimensions of burnout Both qualitative and quantitative work overload contribute to exhaustion by depleting the capacity of people to meet the demands of the job. The critical point occurs when people are unable to recover from work demands. That is, acute fatigue resulting from an especially demanding event at work – meeting a deadline or addressing a crisis – need not lead to burnout if people have an opportunity to recover during restful periods at work or at home (Shinn et al., 1984). When this kind of overload is a chronic job condition, not an occasional emergency, there is little opportunity to rest, recover, and restore balance. Such exhaustion can lead to a deterioration in the quality of the work and a disruption of collegial relationships. A sustainable workload, in contrast, provides opportunities to use and refine existing skills as well as to become effective in new areas of activity (Landsbergis, 1988). It builds involvement by opening new opportunities, and by removing concern about work overwhelming personal capacity. A sustainable workload stops the cycle of exhaustion that is a driving force in the experience of burnout for many people. Control The Demand-Control theory of job stress (Karasek & Theorell, 1990) has made the case for the enabling role of control. This area includes employees’ perceived capacity to influence decisions that affect their work, to exercise professional autonomy, and to gain access to the resources necessary to do an effective job. As human beings, people have the ability to think and solve problems, and want to have the opportunity to make choices and decisions. In other words, they want to have some input into the process of achieving the outcomes for which they will be held accountable. Control problems occur when workers have insufficient authority over their work or are unable to shape the work environment to be consistent with their values. A sense of efficacy is unlikely to occur when workers are feeling buffeted by circumstances or powerful people within the organization. A major control problem occurs when people experience role conflict. Many burnout studies have found that greater role conflict is strongly and positively associated with greater exhaustion (Cordes & Dougherty, 1993; Maslach et al., 1996). Role conflict arises from multiple authorities with conflicting demands or incongruent values, and people in this situation cannot exercise effective control in their job. Contradictory demands interfere with their capacity to set priorities or to commit themselves fully to their work. Role conflict is not simply an indicator of additional work demands, but is emotionally exhausting in itself (e.g. Siefert et al., 1991; Starnaman & Miller, 1992). Moreover, role conflict is, almost by definition, a direct signal of an authority problem at work. It means that a worker’s preferred role is out of sync with important qualities of the job, such as supervisors’ expectations, client demands, or ethical constraints. The critical issue is not the amount or even the type of work demands, but the consistency of those demands with the capacity to determine the job. Reward The reward area of worklife addresses the extent to which rewards – monetary, social, and intrinsic – are consistent with expectations. Lack of recognition from service recipients, colleagues, managers, and external stakeholders devalues both the work and the workers, and is closely associated with feelings of inefficacy (Cordes & Dougherty, 1993; Maslach et al., 1996). When people feel neglected by the material and social reward system of an organization, they feel out of sync with its values. In contrast, consistency in the reward dimension between the person and the job means that there are both material rewards and opportunities for intrinsic satisfaction (Richardsen et al., 1992). Intrinsic rewards (such as pride in doing something of importance and doing it well) can be just as critical as extrinsic rewards, if not more so. What keeps work involving for most people is the pleasure and satisfaction they experience with the day-to-day flow of work that is going well (Leiter, 1992). An enjoyable workflow supports both psychological well being and physical health, and is also the source of recognition from others. The results of various studies have shown that insufficient reward (whether financial, institutional, or social) increases people’s vulnerability to burnout (e.g.Chappell & Novak, 1992; Glicken, 1983; Maslanka, 1996; Siefert et al., 1991). Community Community is the overall quality of social interaction at work, including issues of conflict, mutual support, closeness, and the capacity to work as a team. People thrive in community and function best when they share praise, comfort, happiness, and humor with people they like and respect. In addition to emotional exchange and instrumental assistance, this kind of social support reaffirms a person’s membership in a group with a shared sense of values. Unfortunately, some jobs isolate people from each other, or make social contact impersonal. However, what is most destructive of community is chronic and unresolved conflict with others on the job. Such conflict produces constant negative feelings of frustration and hostility, and reduces the likelihood of social support. Burnout research has focused primarily on social support from supervisors, coworkers, and family members (Cordes & Dougherty, 1993; Greenglass et al., 1994; Greenglass et al., 1988; Maslach et al., 1996). Distinct patterns have been found for informal coworker support and supervisor support (Jackson et al.,1986; Leiter & Maslach, 1988). Supervisor support has been more consistently associated with exhaustion, reflecting the supervisors’ impact on staff members’ workload. Coworker support is more closely related to accomplishment or efficacy, reflecting the value staff members put on the expert evaluation by their peers. A sense of community has been found to buffer the impact of feelings of inequity at work (Truchot & Deregard, 2001). Regardless of its specific form, social support has been found to be associated with greater engagement (Leiter & Maslach, 1988; Schnorpfeil et al., 2002). Research on the social context of burnout has also attended to the broader issues associated with a sense of community in an organization (Drory & Shamir, 1988; Farber, 1984; Royal & Rossi, 1996). Research on community orientation (Buunk & Schaufeli, 1993) provides a distinct but consistent perspective. Both of these approaches consider ways in which the overall quality of personal interactions among people in an organization have an impact on the relationships people have with their work. The consistent finding through this research is that a lively, attentive, responsive community is incompatible with burnout. People’s subjective appraisal of their social context – their sense of community with colleagues or their communal orientation towards service recipients – reflects the extent to which the organizational community is consistent with their expectations. Fairness Fairness is the extent to which decisions at work are perceived as being fair and people are treated with respect. Fairness communicates respect and confirms people’s self-worth. Mutual respect between people is central to a shared sense of community. Unfairness can occur when there is inequity of workload or pay, or when there is cheating, or when evaluations and promotions are handled inappropriately. If procedures for grievance or dispute resolution do not allow for both parties to have voice, then those will be judged as unfair. Relevant research on procedural justice (e.g. Lawler, 1968; Tyler, 1990) has shown that people are more concerned with the fairness of the process than with the favorableness of the outcome. People use the quality of the procedures, and their own treatment during the decision-making process, as an index of their place in the community. They will feel alienated from that community if they are subject to unfair, cursory, or disrespectful decision-making. In contrast, a fair decision is one in which people have an opportunity to present their arguments and in which they feel treated with respect and politeness. Thus, fairness shares some qualities with community, as well as with reward. Fairness has also emerged as a critical factor in administrative leadership (e.g. White, 1987). Employees who perceive their supervisors as being both fair and supportive are less susceptible to burnout, and are more accepting of major organizational change (Leiter & Harvie, 1997, 1998). It appears that employees value fairness in itself and consider it to be indicative of a genuine concern for the long-term good of the organization’s staff, especially during difficult times. When employees are experiencing stress, they look to management not only for problem solving, but for optimism, fairness, and high expectations for organizational and personal performance. They expect that management will give due consideration to people’s contributions and will allocate resources and opportunities equitably (and not to the personal advantage of privileged individuals or cliques). Values The values area is at the heart of people’s relationship with their work. It encompasses the ideals and motivations that originally attracted them to the job. It is the motivating connection between the worker and the workplace that goes beyond the utilitarian exchange of time for money or advancement. Contributing to a meaningful personal goal is a powerful incentive for individuals. When this work contributes as well to the organizational mission, people may be rewarded with additional opportunities for meaningful work. As such, mutually compatible values produce a self-perpetuating dynamic that supports engagement. However, when there is a values conflict on the job, it can undermine people’s engagement with work. The greater the gap between individual and organizational values, the more often staff members find themselves making a trade-off between work they want to do and work they have to do. In some cases, people might feel constrained by the job to do things that are unethical and not in accord with their own values. For example, in order to make a sale or to obtain a necessary authorization, they might have to tell a lie or be otherwise deceptive or not forthcoming with the truth. People can also be caught between conflicting values of the organization, as when there is a discrepancy between the lofty mission statement and actual practice, or when the values are in conflict (e.g. high quality service and cost containment do not always co-exist). In other instances, there may be a conflict between their personal aspirations for their career and the values of the organization, as when people realize that they entered an occupation with mistaken expectations. One resolution of the tension resulting from value conflicts is to bring personal expectations in line with those of the organization (Stevens & O’Neill, 1983); another is to leave the organization in search of more fulfilling career opportunities (Pick & Leiter, 1991). The distress associated with value conflicts and the lengths to which people go to reduce the associated tension are indicative of their central role in the burnout and engagement process. Research has found that a conflict in values is related to all three dimensions of burnout (Leiter & Harvie, 1997). MISMATCH BETWEEN PERSON AND THE JOB A consistent theme throughout this research literature is the problematic relationship between the person and the environment, which is often described in terms of imbalance or misalignment or misfit. For example, the demands of the job exceed the capacity of the individual to cope effectively, or the person’s efforts are not reciprocated with equitable rewards. There is a long history within psychology of trying to explain behavior in terms of the interaction of person and environment, and this is particularly evident within the fields of personality and of vocational psychology (e.g. see Chartrand et al., 1995; Walsh et al., 1992). Many of these interactional models viewperson and environment as independent entities, but characterize them along commensurate dimensions so that the degree of fit, or congruence, between person and environment can be assessed. This approach is evident in some of the earliest models of job-person fit (French et al., 1974, 1982), in which better fit was assumed to predict better adjustment and less stress. Subsequent theorizing continued to highlight the importance of both individual and contextual factors (see Kahn & Byosiere, 1992), and recent research continues to utilize this person-environment approach (e.g. Finnegan, 2000; Lauver & Kristof-Brown, 2001; O’Reilly et al., 1999). Thus, a model of job-person fit would seem to be an appropriate framework for understanding burnout. However, prior conceptualizations of job-person fit are limited in terms of their direct application to this phenomenon. For example, the “person” is usually framed in terms of personality or an accurate understanding of the job, rather than in terms of emotions or motivations or stress responses. Similarly, the “job” is often defined in terms of specific tasks, and not the larger situation or organizational context. The notion of “fit” is often presumed to predict such outcomes as choice of job/occupation or of organization (entry issues), or adjustment to the job (newcomer issues). In contrast, burnout involves a later point in the process, when the person has been working for a while and is experiencing a more chronic misfit between self and the job. Thus, the challenge is to extend the job-person paradigm to a broader and more complex conceptualization of the person situated in the job context. The Areas of Worklife Scale Our goal was to develop a measure that would apply the concept of job-person fit to the assessment of the six key areas of worklife, in a generic format that could be utilized easily by a wide range of employees. We chose to focus on the fit This new measure, the Areas of Worklife Scale, has the potential to provide useful diagnostic information to organizations interested in interventions to deal with burnout (Leiter & Maslach, 2000). The developmental research found that the new scale had a consistent factor structure across these initial samples and showed consistently high correlations with the three burnout dimensions measured by the Maslach Burnout Inventory-General Scale (MBI-GS; which is the general version of the MBI that can be used with all occupations). The AWS is available through Leiter and Maslach (2000) or through Leiter and Maslach (2002).
RETO DE NEGOCIO: Empresa: Quimicos S.A. O Ejemplo 1 O Presentar el Dashboard Dinámico de Ventas que creamos en nuestro video anterior de tal manera que los vendedores puedan revisar fácilmente su desempeño y compararlo con los demás O Ejemplo 2 O La empresa esta desarrollando un presupuesto colaborativo (de abajo hacia arriba) y necesita que cada departamento llene un documento de ingresos y gastos proyectados A QUIEN VA ORIENTADO: O Contadores, Analistas Financieros, Gerentes de Finanzas y Gerentes Generales, entre otros HERRAMIENTAS DE EXCEL UTILIZADAS: O Tablas, Tablas Dinamicas , GetPivotData, Principios de Dashboards (Slicers, Graficas) OTROS CONCEPTOS IMPORTANTES: O Onedrive O Excel Online O Office 365 OTROS VIDEOS O Como hacer reportes gerenciales con Excel IDEA PRINCIPAL No vamos a trabajar los documentos colaborativos en el escritorio, sino en la nube O Ventajas O Acceso por “cualquiera” en cualquier plataforma O “Una sola versión de la verdad” en un solo archivo O Eliminamos el envio y recibido de toneladas de corrreos con diversas versiones del archivo O Es mas fácil de colaborar por separado o al mismo tiempo
Curso Completo (Gratuito o con Descuento)
https://www.udemy.com/reportes-gerenciales-interactivos-excel/?couponCode=DASHBOARD_YOUTUBE3 Reto de Negocios: Quimicos S.A. Obtener Información de ventas por vendedor y categoría de cliente del sistema contable y transformar la data en 2 reportes que ayuden en el manejo diario del negocio A Quien va orientado: Contadores, Analistas Financieros, Gerentes de Finanzas y Gerentes Generales Herramientas de Excel usadas: Tablas Pivot Tables Principios de Dashboards (Slicers, Graficas) Dificultad del ejercicio Básico a Intermedio Proceso Utilizado Importar la data Crear tablas de datos Análisis con Tablas Dinámicas Reportes de resultados Dashboard Gráfico Interactivo Distribución del Reporte (Correo, OneDrive, Office.com) OTROS RECURSOS O Como trabajar con reportes o presupuestos colaborativos con Excel sin morir en el intento O Lo aprendido usando Excel en Finanzas Corporativas Parte 2. Las Debilidades de ExcelOscar ZapataHace 1 mes (editado) ATENCION: Obten GRATIS un cupon a mi curso completo en Udemy! ...A todos los seguidores del canal...Finalmente aprobaron mi curso en Udemy!!!, y para celebrarlo, emiti 100 cupones para que los miembros del canal puedan accesar gratis a mi curso (el precio normal del curso es de 50$ en beta y 120$ precio final)... Solo les pido que usen el cupon si realmente estan interesados en el tema, y que evaluen positivamente el mismo, y si hace falta mejoras, que me las comuniquen en la seccion de Q&A ANTES de evaluar el curso...abojo el link para usar el cupon gratuito... https://www.udemy.com/reportes-gerenciales-interactivos-excel/?couponCode=DASHBOARD_YOUTUBE3 |
AuthorOscar Zapata is a Business and Financial Analyst with an MBA in INCAE. He is the Managing Partner of Impulso Empresarial Categories
All
key termsArchives
January 2018
|