21 May Automation & Streamlining Processes
As we begin to re-open businesses and re-shape goals and future needs for organizations, it’s the perfect time to focus on ways to think efficiently, streamline processes, and work smarter, not harder! The problem is companies tend to focus on critical needs of the day and various other priorities, and streamlining initiatives tend to take a back seat. Yet, this is such an important consideration, as valuable hours are lost performing clerical functions unnecessarily, completing analyses in an inefficient manner, or not leveraging tools available to handle consistent routine processes.
Streamlining and automation are very broad topics! Years ago, this meant a paper free environment. As we have moved to the electronic world today, we must take time to ensure we are using our electronic resources in the most efficient and effective manner. Most efficiency improvements can be accomplished with little cost beyond the time involved to review the current functions and consider smarter ways of accomplishing tasks.
Companies should consider resources such as system downloads (i.e. do we spend time pulling reports based on a given set of consistent criteria that perhaps could be scheduled by the existing software; or easily automated); shared directories (i.e. have we established shared directories for the teams to receive valuable data, and /or save completed analyses); shared files (i.e. are there Excel workbooks that would be more optimally used as a shared workbook permitting data integrity and version control); best use of Excel formulas (i.e. are we efficiently building our workbooks with key variable data in one central location and entered once, so that formulas access this location and time needed for updates is minimized); macros (i.e. have we considered using macros for creating PDF’s of consistent data for each cost center).
In addition, to maintain the state-of-the-art edge in efficiency for routine analysis, companies should be exploring the Excel functionalities of Power Queries, Power Pivots, and Power BI, as well as Robotic Process Automation (RPA).
Power Queries: Just think how much time is spent in your organization on the clerical efforts of routinely opening files from a directory and combining into worksheets; cleaning data (i.e. eliminating duplicate or unnecessary columns, splitting column data or concatenating; aligning data on the same row; adding formulas to the data, editing prior period formulas, categorizing, using excel functionalities (i.e. vlookups, pivots); and preparing journal entries, reconciliations, analysis or summarizations. Now imagine opening a prior period workbook; clicking ‘data refresh’ and all the above is done! Human errors are minimized, consistency is established, processes are documented, and more time is available for reviewing data!
Power Pivots: Imagine a Pivot Table on steroids! Instead of running pivots off one spreadsheet (regardless of whether it is part of the workbook or not), Power Pivots allow Pivot Tables to be built from many workbook sources. Perfect for trend reviews as an example!
Power BI / Dashboards: Think about a single database maintained with system downloads and Excel analysis, whereby summaries, reconciliations, metrics, trends, etc., can be automatically produced; where Power BI analyses can be sent to specified people or DASHBOARDS can be established for specified groups. Security can be added so that designated people can have access to designated data (i.e. by Cost Center; Departments, etc.)
Robotic Process Automation. Imagine a Robot that pulls data from a system with given criteria; at a designated time each day; saves the data in specified paths (drives / folders / file names); opens prior period Excel workbooks and performs the ‘Data Refresh’ to execute all items described by the Power Query; pauses the process (if need be) for an analyst to review / modify results; searches 24-7 for new files in a specified folder (Watcher BOT) to continue the process; splits analysis as needed into separate workbooks; saves each workbook in the various specified paths; and sends emails to the specified teams with links to the updated workbooks. Now Imagine journal entry processing (providing users with posted transactions); automatic metrics analysis, reconciliations, or various other analysis automatically generated. The possibilities are endless!
Think about what occurs when we are not focused on continual streamlining in our day to day work. Management requests new reports or analyses; teams often produce results as quickly as possible without consideration for routine updates; hours are lost recreating the same analysis in an inefficient manner; clerical errors occur; surprises develop; time is wasted reviewing bad data, performing root cause analyses, and generating revisions; expected results are delayed with deadlines being missed and decision making on reliable data is negatively impacted.
If we had the time, we could make the changes required on journal entries, reconciliations, analyses, and processes which result in reduced hours, allowing more time for analysis, reduction of human errors, and consistency for replication built into the processes. We could reduce the wasted hours as we produce it each day, have greater reliability in the results produced the first time and have more time to focus on analyzing, drawing conclusions, and providing recommendations, as opposed to spending the time obtaining data, cleaning data, preparing workbook analysis, and checking for clerical errors.
Streamlining and automation come in many forms, but it’s important to continually assess your best path forward for achieving efficiency improvements, controls to quickly generate summaries, and understand statuses, along with best practice for disseminating data.
Ilene Kappel, Director – Sirius Solutions, L.L.L.P.
If you would like further information regarding Automation & Streamling Processes, please complete the form below.