watching now
3 Students

#### What Will You Learn?

• You'll master advanced formulas and functions to prepare accounting schedules (such as prepaid expenses) and many other amortisation models
• You'll get confident at managing prepaid expenses accounting in a professional way
• You'll get skills with dynamic dashboards and data visualization with Power Query
• You'll learn to leverage an awesome data transformation tool - Power Query

#### Requirements

• Some knowledge of double entry accounting systems
• At least some experience with Microsoft Excel (also basics of Pivot Tables)
• A version of Microsoft Excel installed on your computer (preferably 2007 or later)

#### Curriculum

Course consist of total 5h 42min of content, in total.

##### Section 2: Resources for the course
00:02
Final Resources for Prepaid Models
##### Section 3: Accounting for Prepaid Expenses
07:59
Prepaid Expenses (Prepayments) Accounting Definition
02:47
Prepaid Expense Example (How Accounting works for Prepayments)
02:39
02:33
##### Section 4: Excel Formulas Detailed (Intro to three Excel Models)
56:15
Intro to PRO Excel Models and Formulas
05:57
Date Function
04:32
EOMONTH Function
04:11
DATEVALUE function
03:27
IF Function
07:35
IFS Function (Office 365 or Excel 2019 Only)
07:04
VLOOKUP Function
06:46
NAMED Ranges (Name Manager)
03:08
MATCH Function
04:34
INDIRECT Function
01:34
Advanced Version of VLOOKUP Function (All other formulas combined)
07:27
##### Section 5: Formula based Prepaid Expenses Model (Schedule)
42:51
Introduction to Model and Control Panel Tab (Important Sheet Tab)
08:25
Formula Based Prepaid Expenses Model - Deep Dive (Part 1)
04:49
Formula Based Prepaid Expenses Model - Deep Dive (Part 2)
06:22
Formula Based Prepaid Expenses Model - Deep Dive (Part 3)
06:14
IFS Function - Month End date Prepayment calculation
04:09
Prepaid Expenses - Closing Balance Summary Tab (Formula Based Summary)
08:52
Protecting Formulas Cells and Fields in the Model
04:00
##### Section 6: Calculate Prepaid Expenses Amortisation from Exact Start date (Prepayment date)
53:17
Exact Date Prepaid Amortisation calculation Introduction
03:09
Formulas update and Model Changes for Exact Prepaid Exps Calculation
02:57
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 1)
03:56
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 2)
03:22
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 3)
02:19
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 4)
06:34
IFS Function - Exact Date Prepayments Amortisation
03:33
Data Validation Controls (Enhancing Data Input Controls with Protection)
10:21
Bonus Prepayment Model with Opening Balance-1
07:42
Bonus Prepayment Model with Opening Balance-2
09:24
##### Section 7: Prepaid Expenses Summary with Power Query and Pivot Table
43:06
Power Query and Pivot Table Prepayment Summary Table Introduction
05:49
Power Query and Pivot Table Summary - Deep Dive (Part 1)
04:49
Power Query and Pivot Table Summary - Deep Dive (Part 2)
03:47
Power Query and Pivot Table Summary - Deep Dive (Part 3)
05:18
Power Query and Pivot Table Summary - Deep Dive (Part 4)
09:17
Using Array Formulas to Add Formula Protection
04:24
Bonus: Allocate Prepaid Expenditure Cost Centre Wise 1
02:00
Bonus: Allocate Prepaid Expenditure Cost Centre Wise 2
07:31
##### Section 8: Advanced VBA Prepaid Expenses Amortisation Model
14:28
Changing Macros Security in Excel
Complete Walkthrough - Advanced VBA Prepaid Expenses Amortisation Model
05:44
Bonus New Version - Excel VBA Model
08:20
##### Section 9: BONUS: Dynamic Dashboard for Divisional Profit and Loss statements (Easy way)
58:51
Dynamic Dashboard Overview
06:31
Importing Profit and Loss Statements Source Files and creating YTD P&L Sheets
08:11
Creating Dynamic Data Validation
02:10
Creating Named Ranges for Dynamic Table Arrays (to be used in VLOOKUP Function)
03:27
Dynamic Date Column Headings for each Divisional PL Table
02:29
Dynamic Month and YTD Dashboard tables headings (PRO TIP)
02:52
Dynamic VLOOKUP Formula - Preparing First section of the Dashboard
04:02
Creating Rolling Dashboard with Dynamic VLOOKUP Function
08:00
IMPORTANT : Error Checking for your reports/Dashboard (PRO TIP)
02:35
Data Prep for Visualization: AREA Charts (Awesome trick using #NA Function)
04:48
Visualization: AREA Charts for Month - Revenue, Gross Profit and Net Profit Part 1
04:31
Visualization: DONUT Charts : Revenue, Gross Profit and Net Profit (Part 1)
03:00
Visualization: DONUT Charts: Revenue, Gross Profit and Net Profit (Part 2)
06:13
##### Section 10: Power Query & Pivot Tables based Dashboard without any Formulas, Fully Dynamic
56:52
Introduction - Formula-less Dashboard - Fully Dynamic and easily refreshed
05:12
Understanding the data files before building dashboard
02:00
Consolidating Reports with Power Query (Get & Transform) , How to install PQ
07:35
Dynamic File Path Trick in Power Query with Parameters (Amazing trick)
05:57
Conditional Cumulative totals with SUMIFS Function
04:06
Bonus: Conditional Cumulative totals with Power Query Custom Formula (M Code)
06:16
Dashboard Creation - Pivot Table showing Month and YTD KPIs division wise
06:03
Dashboard Creation Donuts Charts linked with Pivot Table (Replicate Charts fast)
08:21
Dashboard Creation - Line Charts
07:56
Update Dashboard with Additional Divisional Data with Few Click (Magical)
03:24
##### Section 11: Conclusion and Thank You
01:48
Thank you and Closing Remarks
01:48

#### Prashant Panchal

##### Excel & BI Enthusiast, CA, Finance & Accounting Professional

Hey, I am Prashant Panchal. I like helping fellow Accountants, Auditors and Analysts to become PRO at using Microsoft Excel.

I have been using Excel for mainly Accounting and Analytics for more than 13 years and I quite enthusiastic about sharing my knowledge with fellow Accountants, Analysts, or any Excel user

I have come across many challenging reporting and analytics situations during my career, and I was able to resolve the same using good old Microsoft Excel Spreadsheet application.

With me, you will find Basic to Advanced Excel formulas and Functions, Tips and Tricks, Excel VBA ready to use codes.

The thing I am most excited to share with you is Modern Excel Tools training and its basics to make your life easier as an Accountant or Analyst for Data cleaning, and Report automation

With Techniques I have acquired using Excel for so many years, I pretty sure I can reduce your routine workflow (time and efforts) to half or even more.

While I love traditional formulas and function, Pivot tables and Excel VBA, I feel that its time to go beyond and discover amazing Power Tools offered by Microsoft these days.

These tools include Power Query for Excel (Get and Transform), DAX formulas Engine with Excel Power Pivot.

Don't worry if you have not heard about these tools, you will learn about them in details (basics to advanced) with me without any pain!

My Speciality lies in Data Analytics and ETL (Extract Transform and Load)

While formulas, Functions, Excel VBA can automate reporting and calculations for your projects, but when you are dealing with repetitive tasks, there is nothing better than Power Query.

So I am a big proponent of Automation of Data and reporting: Source data to Reporting with minimum manual steps involved with 100% Accuracy of Output reports

By the way, I am a Chartered Accountant by Qualification, and well versed with hands-on Corporate Financial Accounting and Reporting for diversified industries

So don't worry you are in good hands and a trusted source for your Excel problems and become Excel superuser

Cheers!

Days
Hours
Minutes
Seconds