| 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 |
|
00:00:00 |
|
Exact Prepaid Expenses Payment Date Calculation |
|
00:00:00 |
| 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 |
|
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 |
|
00:00:00 |
| Prepaid Expenses Summary with Power Query and Pivot Table |
|
Power Query and Pivot Table Prepayment Summary Table Intro |
|
00:05:00 |
|
What is Power Query and Some Awesome Resources for Power Query learning |
|
00:14: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 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 |
| Thank you |
|
Thank you |
|
00:02:00 |
|
Ultimate Prepaid Expenditure Model (Super Bonus) |
|
00:02:00 |
|
Visualization: AREA Charts for Month – Revenue, Gross Profit and Net Profit |
|
00:05:00 |
| Resources |
|
Resources – Microsoft Excel: Automated Dashboard Using Advanced Formula, VBA, Power Query |
|
00:00:00 |
Add to Cart
