Automated Information Reporting

with Advanced Microsoft Excel VBA

Programme add-ins and functions with Advanced Excel with VBA and get the fully understand of Management Information Reporting and its value to businesses.

Course Duration: 4 days (full-time)

You will learn to…

Recognise the important factors driving performance and how to communicate the impact

Re-evaluate KPIs and identify data needed to track new measures

Create your checklists and ideas to improve efficiency and effectiveness

Develop more effective analysis and reports using Microsoft Excel skills

Create and edit Macros

Use and understand VBA code

Link and combine spreadsheets and other Office applications

Set up advanced automation features

Improve the contribution of the analysis team

Review how you report and communicate information to help others focus on their key issues

About this course

This specialist course developed in-house by our Business Intelligence and Analytics Specialists will enable you to fully understand what Management Information Reporting entails and its value to businesses and their development. Also, it will give you all the necessary skills in advanced Excel with visual basic (VBA). You will be able to write and understand advanced macros to automate work and extend Excel features by programming add-ins and functions. Also, you will be able to manage all the Metrics and KPI’s to be implemented in your Reports, providing you an Introduction to Data Warehousing and how to produce and maintain Dashboards.

4 Benefits of booking this course with us

Book Now

Course Dates and Location

  • This course takes place every month.
  • Main locations: Southampton and London.
  • Please contact us for more details of dates/location.

Testimonials

“The facilitator is very knowledgeable with years of experience, the course delivery was exceptional.”
Anonymous, Course Feedback Form

Related Courses

Share this Training Course

Course Content

Book Now

Course Unit 1: Building Applications with Advanced Microsoft Excel VBA

  1. Visual Basic Editor
  2. Objects, Methods, Properties
  3. Programming Tools
  4. The Menu Bar
  5. Running Code
  6. Run mode and Design mode
  7. Running Code from the development environment & the host application
  8. The Project Explorer
  9. Using the Project Explorer
  10. The Properties Window
  11. Changing a Property
  12. Using Constants
  13. Excel & Variable Constants
  14. Excel Objects
  15. Objects, Properties and Methods
  16. Getting & Setting Properties
  17. Calling Methods
  18. Passing Arguments
  19. Singular Objects & Collections of Objects
  20. Variables
  21. Dimensioning a variable
  22. Using variables in routines
  23. Object Variables
  24. Using Visual Basic Functions
  25. InputBox Function
  26. MsgBox Function
  27. Using a Set Statement
  28. Building Formula Control Structures
  29. If…Then Decision Structures
  30. Logical Operators
  31. Select Case Decision Structures
  32. Case Else
  33. Comparison Operators with Select Case Structure
  34. For… Loops & Do Loops
  35. While…Wend Statement
  36. UserForms
  37. Designing & Creating Forms
  38. Working with Controls
  39. Creating Custom Dialog Boxes
  40. Userform Properties Methods & Events
  41. Event Handling
  42. VBA Editing & Debugging
  43. Auto Macros
  44. Error Handling

Course Unit 2: Management Information Reporting (MIR)

1. Decision Making

• Effective Decision Making
• Steps to Making Effective Decisions

2. Introduction to Management Information Reporting

• Sources of Information and Data
• Uses of Management Information Reporting

3. Enterprise Performance Management

• Defining and Communicating Strategy
• Measuring Performance
• Analysing Performance
• Reporting and reviewing Performance
• Aligning People and Culture

4. Organisational Level and Reports

5. Key Performance Indicators

• What are Key Performance Indicators
• Benefit of KPI’s
• KPI Planning
• KPI metrics
• Identify KPI

6. HR metrics

• Turnover Rate
• Cost Per Hire
• Human Capital ROI
• Inventory to Sales Ratio

7. Service Level Agreement

• Introduction to SLA
• Why SLA is important to Management Information Reporting
• How to use and apply SLA to reports

8. Financial Metrics

9. Marketing/Sales Metrics

10. Designing and Planning Management Information Reports

• Approach to Data Reporting
• Accessing and updating live data from underlying accounting systems
• Implementing key performance indicators and benchmarking
• Building a tailored front end reporting system
• Drill down by different sectors, departments, timeframes or any other user defined variables
• Producing a budgeting model

11. Data Analysis lifecycle

• Using Information and Data Strategy to Analyse Business Objects

12. Data Sources

• Transactional Data
• Business operational Data
• Lifestytatic Report

13. Static Reports

14. Data Preparation and how to identify and deal with missing values

• Importing and Exporting Data

15. Quality Assurance

16. Identifying the right Chart

• Proportion of the Whole
• Column Charts
• Bar Charts
• Line Charts
• Combination Charts
• Sparkline, Data Bars

17. Identifying what you want to say

• Proportion of the Whole
• Trend Analysis
• Relationships
• Comparison
• Distribution

18. Trend Analysis

• Visualisation of trend Analysis

19. Data Visualisation in report

• Common Data visualization Issues

20. Pattern Recognition in Data and Charts

21. Database types

• Transactional Database
• Analytical Database

22. Introduction to Data Warehousing

• Definition of Data Warehousing
• Benefits and challenges of Data Warehousing
• Data Warehousing architecture
• Data mart
• Star Schema
• Snowflake Schema

23. Balance scorecards

24. Dashboards

• What is a Dashboard
• Categorizing Dashboards
• Establishing the user Requirement for Dashboard
• Building a Dynamic report
• Creating Dashboards using Power Pivot Tool
• Creating visually appealing dashboards and group summaries

25. Customer-Based Reports

• Customer Segmentation
• Behavioural analysis

This course is intended for professional analysts that handle activities related to data processing, high effective reports development and KPI’s evaluation.
£1,970 per person, ex VAT