Skip to content

Automated Information Reporting with Advanced Microsoft Excel VBA

Pairview Training

Summary

Price
£2,376 inc VAT
Study method
Onsite
Duration
4 days
Qualification
No formal qualification
Certificates
  • Certificate of completion - Free
Additional info
  • Tutor is available to students

Overview

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.

Description

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

Questions and answers

Certificates

Certificate of completion

Digital certificate - Included

Reviews

Currently there are no reviews for this course. Be the first to leave a review.

Leave a review

Legal information

This course is advertised on reed.co.uk by the Course Provider, whose terms and conditions apply. Purchases are made directly from the Course Provider, and as such, content and materials are supplied by the Course Provider directly. Reed is acting as agent and not reseller in relation to this course. Reed's only responsibility is to facilitate your payment for the course. It is your responsibility to review and agree to the Course Provider's terms and conditions and satisfy yourself as to the suitability of the course you intend to purchase. Reed will not have any responsibility for the content of the course and/or associated materials.

FAQs

Study method describes the format in which the course will be delivered. At Reed Courses, courses are delivered in a number of ways, including online courses, where the course content can be accessed online remotely, and classroom courses, where courses are delivered in person at a classroom venue.

CPD stands for Continuing Professional Development. If you work in certain professions or for certain companies, your employer may require you to complete a number of CPD hours or points, per year. You can find a range of CPD courses on Reed Courses, many of which can be completed online.

A regulated qualification is delivered by a learning institution which is regulated by a government body. In England, the government body which regulates courses is Ofqual. Ofqual regulated qualifications sit on the Regulated Qualifications Framework (RQF), which can help students understand how different qualifications in different fields compare to each other. The framework also helps students to understand what qualifications they need to progress towards a higher learning goal, such as a university degree or equivalent higher education award.

An endorsed course is a skills based course which has been checked over and approved by an independent awarding body. Endorsed courses are not regulated so do not result in a qualification - however, the student can usually purchase a certificate showing the awarding body's logo if they wish. Certain awarding bodies - such as Quality Licence Scheme and TQUK - have developed endorsement schemes as a way to help students select the best skills based courses for them.
View all