Course Overview
This course includes 13 sections and 26 video lectures focused on building automated prepaid expenses models and dynamic dashboards. You’ll learn how to use advanced Excel formulas, VBA scripting, Power Query, and Pivot Tables to develop robust financial reports and dashboards.
Throughout the course, you will:
- Understand how to build formula-based prepaid expenses models
- Automate prepaid expenses amortisation using VBA
- Create dynamic dashboards for divisional profit and loss statements
- Use Power Query and Pivot Tables to generate fully dynamic reports without formulas
- Download and work with example resources to practice your skills
Learning Outcomes
By the end of this course, you will be able to:
- Develop advanced Excel formulas for financial modelling
- Automate complex tasks using VBA macros
- Use Power Query to transform and analyse data efficiently
- Build dynamic, formula-free dashboards with Pivot Tables and Power Query
- Design and manage prepaid expenses amortisation models
- Apply skills to improve efficiency in accounting and data analysis tasks
- Receive a CPD-accredited certificate confirming your expertise in advanced Excel dashboard creation
Why Enrol in This Course?
This course is ideal for professionals seeking to enhance their Excel capabilities beyond basic functions. It offers insights on automating financial models and creating interactive dashboards that can save time and improve reporting accuracy.
You’ll benefit from:
- CPD-certified training tailored to accounting and data analysis professionals
- Downloadable resources to practice and apply your skills
- Flexible online learning with lifetime access
Who is This Course for?
The Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course is suitable for:
- Accountants and finance professionals
- Data analysts and business intelligence professionals
- Excel users looking to automate reporting and dashboard creation
- Anyone wanting to improve their advanced Excel skills for work efficiency
Prerequisites for This Course
Basic knowledge of Microsoft Excel is recommended. Familiarity with formulas and Pivot Tables will help you get the most from this course.
Certification
Upon completing the course and passing the final exam, you will receive a CPD QS Accredited Certificate from Apex Learning. This certification demonstrates your knowledge and skills as a nursing assistant in the UK. The Digital version of the certificate is available for £9.99 only, and you can order a hard copy of the certificate for £14.99 only.
Career Path
Completing this course can enhance your skills for roles such as:
- Financial Analyst – £28,000 to £45,000 per year
Analyse financial data and build automated reports. - Business Intelligence Analyst – £30,000 to £50,000 per year
Develop dashboards and data visualisations for business insights. - Accountant – £25,000 to £40,000 per year
Manage financial reporting and automate accounting processes. - Data Analyst – £27,000 to £45,000 per year
Analyse and interpret complex data sets using advanced Excel techniques.
Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query | CPD Accredited Course Online – UK
Microsoft Excel remains a crucial tool for data analysis, accounting, and reporting. The Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course is a CPD-accredited online training designed to equip you with advanced Excel skills to automate dashboards and financial models efficiently. This course is perfect for accountants, analysts, and Excel users aiming to streamline their reporting with powerful formulas, VBA macros, and Power Query.
Course Overview
This course includes 13 sections and 26 video lectures focused on building automated prepaid expenses models and dynamic dashboards. You’ll learn how to use advanced Excel formulas, VBA scripting, Power Query, and Pivot Tables to develop robust financial reports and dashboards.
Throughout the course, you will:
- Understand how to build formula-based prepaid expenses models
- Automate prepaid expenses amortisation using VBA
- Create dynamic dashboards for divisional profit and loss statements
- Use Power Query and Pivot Tables to generate fully dynamic reports without formulas
- Download and work with example resources to practice your skills
Learning Outcomes
By the end of this course, you will be able to:
- Develop advanced Excel formulas for financial modelling
- Automate complex tasks using VBA macros
- Use Power Query to transform and analyse data efficiently
- Build dynamic, formula-free dashboards with Pivot Tables and Power Query
- Design and manage prepaid expenses amortisation models
- Apply skills to improve efficiency in accounting and data analysis tasks
- Receive a CPD-accredited certificate confirming your expertise in advanced Excel dashboard creation
Why Enrol in This Course?
This course is ideal for professionals seeking to enhance their Excel capabilities beyond basic functions. It offers insights on automating financial models and creating interactive dashboards that can save time and improve reporting accuracy.
You’ll benefit from:
- CPD-certified training tailored to accounting and data analysis professionals
- Downloadable resources to practice and apply your skills
- Flexible online learning with lifetime access
Who is This Course for?
The Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course is suitable for:
- Accountants and finance professionals
- Data analysts and business intelligence professionals
- Excel users looking to automate reporting and dashboard creation
- Anyone wanting to improve their advanced Excel skills for work efficiency
Prerequisites
Basic knowledge of Microsoft Excel is recommended. Familiarity with formulas and Pivot Tables will help you get the most from this course.
Assessment Method
Assessment for the course is conducted through an automated multiple-choice exam. A score of 60% is required to pass and earn the CPD Accredited Certificate. Reflective assignments are provided to enhance your learning, with expert tutor feedback available.
Certification
Upon completing the course and passing the final exam, you will receive a CPD QS Accredited Certificate from Apex Learning. This certification demonstrates your knowledge and skills as a nursing assistant in the UK. The Digital version of the certificate is available for £9.99 only, and you can order a hard copy of the certificate for £14.99 only.
Career Path
Completing this course can enhance your skills for roles such as:
- Financial Analyst – £28,000 to £45,000 per year
Analyse financial data and build automated reports. - Business Intelligence Analyst – £30,000 to £50,000 per year
Develop dashboards and data visualisations for business insights. - Accountant – £25,000 to £40,000 per year
Manage financial reporting and automate accounting processes. - Data Analyst – £27,000 to £45,000 per year
Analyse and interpret complex data sets using advanced Excel techniques.
Frequestly Asked Questions
What is this course about?
This CPD-accredited course teaches advanced Excel techniques, including VBA, Power Query, and dynamic dashboard creation.
Is this course suitable for beginners?
Basic Excel knowledge is recommended; this course focuses on advanced skills.
Does it include practical resources?
Yes, downloadable resources are provided to practice the models and dashboards.
Is the certificate recognised?
Yes, the CPD QS Accredited Certificate is recognised by UK employers.
How do I enrol?
Click “Enrol Now” to start your course with lifetime access.
Course Curriculum
- 13 sections
- 26 lectures
- 00:00:00 total length
-
Course Outline and Introduction 00:04:00 -
Minimum Requirements for the Course 00:01:00
-
Prepayments Introduction 00:01:00
-
Month End Date Prepaid Expenses Amortization Calculation 00:00:00
-
Exact Prepaid Expenses Payment Date Calculation 00:00:00
-
Prepaid Expenses Accounting Definition: Prepayments 00:03:00
-
Prepaid Expense Example: How Accounting works for Prepayments 00:03:00
-
Advantages and Disadvantages of Prepaid Expenses 00:03:00
-
Introduction to PRO Excel Models and Formulas 00:06:00
-
Date Function 00:05:00
-
EOMONTH Function 00:04:00
-
DATEVALUE function 00:03:00
-
IF Function 00:08:00
-
IFS Function (Office 365 Only) 00:07:00
-
VLOOKUP Function 00:07:00
-
MATCH Function 00:05:00
-
INDIRECT Function 00:02:00
-
NAMED Ranges: Name Manager 00:03:00
-
Advanced Version of VLOOKUP Function 00:07:00
-
Introduction to Model and Control Panel Tab (Important Sheet Tab) 00:08:00
-
Formula Based Prepaid Expenses Model – Deep Dive (Part 1) 00:05:00
-
Formula Based Prepaid Expenses Model – Deep Dive (Part 2) 00:06:00
-
Formula Based Prepaid Expenses Model – Deep Dive (Part 3) 00:06:00
-
IFS Function – Month End date Prepayment calculation 00:04:00
-
Prepaid Expenses – Closing Balance Summary Tab (Formula Based Summary) 00:09:00
-
Protecting Formulas Cells and Fields in the Model 00:04:00
-
Exact Date Prepaid Amortisation calculation Intro 00:03:00
-
Formulas update and Model Changes for Exact Prepaid Exps Calculation 00:03:00
-
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 1) 00:04:00
-
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 2) 00:03:00
-
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 3) 00:02:00
-
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 4) 00:07:00
-
IFS Function – Exact Date Prepayments Amortisation 00:04:00
-
Data Validation Controls (Enhancing Data Input Controls with Protection) 00:10:00
-
Bonus: Prepayment Model with Opening Balance Calculation (Part 1) 00:08:00
-
Bonus: Prepayment Model with Opening Balance Calculation (Part 2) 00:09:00
-
Additional Material: Resources 00:00:00
-
Power Query and Pivot Table Prepayment Summary Table Intro 00:06:00
-
What is Power Query and Some Awesome Resources for Power Query learning 00:07:00
-
Power Query and Pivot Table Summary – Deep Dive (Part 1) 00:05:00
-
Power Query and Pivot Table Summary – Deep Dive (Part 2) 00:04:00
-
Power Query and Pivot Table Summary – Deep Dive (Part 3) 00:05:00
-
Power Query and Pivot Table Summary – Deep Dive (Part 4) 00:09:00
-
Using Array Formulas to Add Formula Protection 00:04:00
-
Bonus: Allocate Prepaid Expenditure Cost Centre Wise – 1 00:02:00
-
Bonus: Allocate Prepaid Expenditure Cost Centre Wise – 2 00:08:00
-
Bonus: Prepayment Model with Opening Balance Calculation (PQ and PT Version) 00:13:00
-
Changing Macros Security in Excel 00:05:00
-
Complete Walkthrough – Advanced VBA Prepaid Expenses Amortisation Model 00:06:00
-
Bonus : New Version – Excel VBA Model for Prepayment Expenditure 00:08:00
-
Dynamic Dashboard Overview 00:07:00
-
Importing Profit and Loss Statements Source Files and creating YTD P&L Sheets 00:08:00
-
Creating Dynamic Data Validation 00:02:00
-
Creating Named Ranges for Dynamic Table Arrays 00:03:00
-
Dynamic Date Column Headings for each Divisional PL Table 00:02:00
-
Dynamic Month and YTD Dashboard tables headings (PRO TIP) 00:03:00
-
Dynamic VLOOKUP Formula – Preparing First section of the Dashboard 00:04:00
-
Creating Rolling Dashboard with Dynamic VLOOKUP Function 00:08:00
-
IMPORTANT : Error Checking for your reports/Dashboard (PRO TIP) 00:03:00
-
Data Prep for Visualization: AREA Charts (Awesome trick using #NA Function) 00:05:00
-
Visualization: AREA Charts for Month – Revenue, Gross Profit and Net Profit 00:05:00
-
Visualization DONUT Charts Revenue, Gross Profit and Net Profit (Part 1) 00:03:00
-
Visualization DONUT Charts Revenue, Gross Profit and Net Profit (Part 2) 00:06:00
-
Introduction – Formula-less Dashboard – Fully Dynamic and easily refreshed 00:05:00
-
Understanding the data files before building dashboard 00:02:00
-
Consolidating Reports with Power Query (Get & Transform) , How to install PQ 00:08:00
-
Dynamic File Path Trick in Power Query with Parameters (Amazing trick) 00:06:00
-
Conditional Cumulative totals with SUMIFS Function 00:04:00
-
Bonus: Conditional Cumulative totals with Power Query Custom Formula (M Code) 00:06:00
-
Dashboard Creation – Pivot Table showing Month and YTD KPIs division wise 00:06:00
-
Dashboard Creation Donuts Charts linked with Pivot Table (Replicate Charts fast) 00:08:00
-
Dashboard Creation – Line Charts 00:08:00
-
Update Dashboard with Additional Divisional Data with Few Click (Magical) 00:03:00
-
Thank you 00:02:00
-
Ultimate Prepaid Expenditure Model (Super Bonus) 00:02:00
-
Resources – Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query 00:00:00
-
Claim Your Certificate 00:00:00
Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query
Save 95% - Sale Ends in
5 Star course rating
£19.99
£419
Save 95% - Sale Ends in
TAKE ALL COURSES FOR £49This course includes:
-
Duration:6 hours, 8 minutes
-
Access:1 year access
-
Level:
-
CPD Points:10