Automation in Excel Reporting B USINESS / F INA NCIA L A NA LYSIS - B E ST PR ACT ICE S B Y MA R CO K IE SE WETT ER

marco kiesewetter

Overview

• Why Automation?

• Never again Copy & Paste

• Using VBA for massive calculations

• The Master Setup Page

• Error Tracking

marco kiesewetter

Why Automation?

marco kiesewetter

Why Automation?

• Time savings

• Faster turnaround time, more frequent updates possible

• Freed up time for new developments & process improvements

• Reduction of human error

• Copy & paste, involved manual updates cause mistakes

• Overlooked adjustments of values, dates, names & calculations

marco kiesewetter

Never Again Copy & Paste

marco kiesewetter

Use of ETL / SQL

• In many cases it is possible for IT departments to access business systems via an API or other tools and to store the data on local SQL servers (ETL)

• Access the data from these SQL servers instead

• Instant push-of-a-button updates possible, even for end users of reports

• Possibility to move many calculations and table mash-ups into SQL

• No need for lengthy calculations in Excel

• Especially SUMIFs can take very long in Excel but can be lightning fast in SQL

• No more “pulling down formulas” next to pivot tables, which can easily be overlooked

• Many VLOOKUPs can be achieved in SQL already

• Same results every time

marco kiesewetter

No copy & paste on manual reports • Even if the need for manual reporting out of ERPs exist, some automation can still

be achieved

• For example using reports out of Salesforce:

• Prepare a network location to which everyone who will update an Excel report has access

• Run the Salesforce report and save it as CSV in this location, give it a meaningful name but do not include date or time in the name

• Access this CSV file with Excel (Data > Get External Data > From Text) instead of pasting its data into an Excel sheet • Alternatively link it to an Access database, do mash-ups & custom calculations in Access and use this

database as source for Excel

• Next time simply run the Salesforce report again and overwrite the CSV file with the new download • The connection to this CSV in your Excel/Access file will not change but it will now pull the fresh data

marco kiesewetter

Using VBA for massive calculations

marco kiesewetter

Why use VBA for calculations? • Large reports with many calculations on many rows of data can take very

long to calculate

• Sometimes calculation can become too complex

• I.e. dynamically translating “5 hours between May 10 and May 20” into correct amounts for each calendar week that includes part of this time period.

• These Excel calculations are very resource intensive. In most cases it is not possible to use the computer for other work at the same time

• These calculations may repeat before saving a spreadsheet, costing even more idle time

• Using VBA for large calculations leaves the computer usable and the calculated data is “values only” data, which speeds up saving and working with the file

marco kiesewetter

Also use VBA for automation

• Updating several pivot tables from several external data sources in the right order

• Automatically start pulling data and calculating the report before you arrive at work in the morning

• Copying formulas and pasting them as values for the distribution version of the spreadsheet

• Deleting and hiding tabs that are needed for running the report but that do not need to be in the distribution version

• Automatically saving reports in the right folders with the right file name (that can include date and time of saving in the name)

marco kiesewetter

The Master Setup Tab

marco kiesewetter

The Master Setup Tab

• One tab with all settings, error checks and buttons for VBA automation

• Have slicers for all pivot tables that need updates and/or checks of specific filters

• Have error checks all in one place, visible together at any time

• Have all manual setup values and calculated master values (i.e. report date) in one place

• Have an update history available to see what automation ran and when

marco kiesewetter

Master Setup Tab SOME EX AMPLES AND EX PLANAT IONS

marco kiesewetter

Example of a Master Setup Tab

marco kiesewetter

Automatically start pulling data and run calculations in the morning to have it ready when you arrive at work

marco kiesewetter

Console style log that shows step by step what the automation did, when it did and how long it took (Helps with performance improvements and trouble shooting)

marco kiesewetter

Automatically determining today’s date and yesterday’s date for change reporting (Automate that on a Monday: Yesterday = Friday) Allow overrides to account for holidays, unscheduled updates etc.

marco kiesewetter

Arrange automation buttons in order with a brief guideline on how to update the report Color code instructions to help noticing where to make changes

marco kiesewetter

Error Tracking

marco kiesewetter

Check if formulas next to pivot tables reach all the way down to the last line of the pivot table. Conditional Formatting on Errors to make them stand out right away. Similar error checks should be used to compare data points that should be consistent across sheets if everything works as expected.

marco kiesewetter

Other Possible Checks on the Setup Tab • Compare totals across tabs and display differences

• Is one total different? Why?

• Compare totals to expected values

• Do the numbers make sense?

• Compare dates to expected values

• Are data sources out of date / wrong?

• Explore ratios and evaluate their validity

• i.e. hours worked month to date : days so far in the month

• Use conditional formatting to emphasize values that appear wrong

Thank You QUE ST IO NS?

Reach out: https://www.linkedin.com/in/marcokiesewetter