No products in the basket.

Registering for this site is easy. just fill in the fields below, and we’ll get a new account set up for you in not time.

Sign in to your Imperial Academy Account

Home
Course
Personal Development
Office Productivity
IT & Software
Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query

Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query

IT & Software

9 students

( Rating)

About This Course

Educational accomplishments and income are closely correlated. Education and skill with endorsed certificates from credible and renowned authorities typically lead to better jobs with attractive salaries. Educated and skilled workers also have lower rates of unemployment. Therefore, skills and endorsed certificates to showcase are essential for people early in their careers.

Imperial Academy offers this premium Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course to ensure security in your career. In addition, this comprehensive Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query course will assist you in building relevant skills that will help you find a job in the related sectors. Also, the Certificate you’ll get after completing the Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query will put your head and shoulder above others in front of potential employers.

Become the person who would attract the results you seek. What you plant now, you will harvest later. So, grab this opportunity and start learning Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query

QLS/ CPD/ CIQ Accredited
24/7 Assistance from our Support Team
100% Online
Self-paced course
Bite-sized Audio-visual Modules
Rich Learning Materials
Developed by Industry Specialists
Career Guidance

Learn at your own pace from the comfort of your home, as the rich learning materials of this premium course is accessible from any place at any time. The advanced course curriculums are divided into tiny bite-sized audio-visual modules by industry specialists with years of experience behind them.

Course Curriculum

Introduction

Course Outline and Introduction

00:04:00
Minimum Requirements for the Course

00:01:00
Prepayments Introduction

00:01:00

Prepaid Expenses Models: Resources Download

Month End Date Prepaid Expenses Amortization Calculation
Exact Prepaid Expenses Payment Date Calculation

Accounting for Prepaid Expenses

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

Excel Formulas Detailed: Introduction to three Excel Models

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

Formula Based Prepaid Expenses Model

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

Calculate Prepaid Expenses Amortisation from Exact Start Date

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

Prepaid Expenses Summary with Power Query and Pivot Table

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

Advanced VBA Prepaid Expenses Amortisation Model

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

BONUS: Dynamic Dashboard for Divisional Profit and Loss statements: Easy Way

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

Power Query & Pivot Tables based Dashboard without any Formulas, Fully Dynamic

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