Excel Automated Reports

In our daily work, we receive and analyze a set of periodic reports, like daily transactions, weekly progress reports, monthly financial statements, ...etc. These reports provide the users with information regarding the company’s performance, allowing the users to analyze the numbers and make decisions.

As analysts, we use these polished reports as raw data, because we will need to generate aggregates, create insights and show comparative reviews to management and executives, who want to decide upon the latest changes.

In our example today, I used a sample monthly service report which includes 3 user segments, A, B, C and have some KPIs, per segment like number of users, revenues, transactions, ARPU, AUPU, and location.

These measures are updated to a year report or a history report for analysis.

A Dashboard is a type of graphical user interface that often provides at-a-glance views of key performance indicators (KPIs) relevant to a particular objective or business process.

Dashboard design is a mix of art and science since it combines facts, numbers, and insights with the art of colors, layout arrangements and storytelling, aiming to convey the view of KPIs at-a-glance

Although the dashboard design is not part of our course, I included a sample dashboard showing main KPIs and a set of charts for rolling 6 months back from the date of the report, for example, Feb to Jul 2019, Mar to Aug 2019, ...etc

The dashboard is usually interactive and is supplied with slicers, drop boxes, cross filters, ..etc. The main purpose of this course is to show how to automatically retrieve the data from raw tables, so we will use a simple spin button in the dashboard to switch between different months and update all elements in the dashboard.

Once the data processing is done on Excel, it is needed to use it to update the management report, which normally is delivered as a presentation.

Statistically, an update of one slide with figures and charts takes an average of 20 minutes, which makes the update of a lengthy executive report of 20 slides as a day-long job. In practice, I was given an assignment to update a 48 slides report with around 82 charts and tables and a countless number of text statements. I used to update this one in 3 days.

In this course, we will learn to reduce this long time into seconds, by just a click of a button.

In a separate course, you can also learn to extend this level of automation by adding a report listener in outlook, which waits for the monthly report in your emails and triggers an excel routine to update the raw data table, then adjust the dashboard for the latest month, then creates the slides..

... and also, you can extend this operation by sending the slides to a distribution list through email.

Although these functions are not too complicated and may be used widely, it is necessary to give it a quick review to make sure all students are at the same level of understanding.

As these functions are the core functions we are going to use in the course, I suggest you experiment with it a few times so that the coming lectures would be easily understood.

To build the updatable slides, we start with an existing presentation, in our example, we used an executive report containing a dashboard, other charts, tables and a set of changeable text, like "in August, revenues increased by 2.4% over September" and "Number of transactions slightly decreased by -0.6%"

Setting up this presentation for update requires:

• Replacing the changeable text with text tags

• Adding tags to charts

• Converting tables to images and adding tags to the converted tables

Text Tags: replace the changeable text and is enclosed by 2 square brackets.

example:
"in August, revenues increased by 2.4% over September" to be changed to
"in [This monthName], revenues [RevenueChangeMoM] by [RevenueChange%MoM] over [LastMonthName]"

Chart Tags: added to chart images (place holders) by editing its Alt Text value as follows
right-click the chart image, select Edit Alt Text then write the tag in the field,

example: [UsersBySegment]

Table Tags: before we add the tags, we copy the table and paste it again as image, then we follow same steps as chart tags, except that we modify the tag to be in a double square brackets

example: [[SegmentsTable]]

The modified table tag is meant to inform the updating engine that this table will be updated from a text table in Excel

After completing all changes in the slides, it should be saved in your working folder, then return to setting sheet and update the Template variable with full path

example: C:\Work\Udemy\Auto Dashboards\Materials\ABC template.pptx

We will need also to add another variable in the settings to suggest the output file name for the updated slides. This one can contain dynamic information like the report date, creation date, ..etc

Example: cell C23 to be named OutputFileName and given a value ="Service Dashboard - " & ReportFullDate & " - " & ReportFileDate

Although the changes here may take some time, it worths the effort. You will see how much time you will save when you start the update process. I assure you this is gonna be amazing!

Similar to the integration we had between Excel and PowerPoint, you can also integrate Excel and Outlook to do two more fancy functions:

1. Create a listener in Outlook to intercept specific emails which carries the report feeds (e.g. monthly reports from data warehouse), then save the email attachment to a predefined folder, open Excel and ask it to run a VBA tool to import that data into the raw data sheet, finally let Excel creates the latest executive report.

2. Outlook to send the generated executive report to a predefined distribution list, you can ask outlook to report to you the delivery and read receipt.

.. That way, your corporate reports can run automatically from the instance when monthly feeds come into your email, till it is processed, published and distributed.