Grab the chance to earn a scholarship!

Study a desired course for free & get money
assistindo agora
3 Alunos
73 Lições
Iniciante

O que você vai aprender?

  • 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

Grade Curricular do Curso

5h 42m
Seção 2: Resources for the course
00:02
Final Resources for Prepaid Models
Seção 3: Accounting for Prepaid Expenses
07:59
Prepaid Expenses (Prepayments) Accounting Definition
02:47
Prepaid Expense Example (How Accounting works for Prepayments)
02:39
Advantages and Disadvantages of Prepaid Expenses
02:33
Seção 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
Seção 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
Seção 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
Seção 7: Prepaid Expenses Summary with Power Query and Pivot Table
43:06
Power Query and Pivot Table Prepayment Summary Table Introduction
05:49
What is Power Query? Read about it here and also how to download?
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
Seção 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
Seção 9: BONUS: Dynamic Dashboard for Divisional Profit and Loss statements (Easy way)
58:51
Dynamic Dashboard Overview
06:31
Resources for the section, download it here
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
Seção 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
Resources for the section, download it here
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
Seção 11: Conclusion and Thank You
01:48
Thank you and Closing Remarks
01:48

Descrição

Requisitos

  • 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)

Sobre o Instrutor

Prashant Panchal

Excel & BI Enthusiast, CA, Finance & Accounting Professional
91.4% dos estudantes avaliaram este instrutor como excelente!
Comentários 3
Alunos 50
Cursos 3

Avaliações da plataforma BitDegree

Nossos alunos dizem Excelente
9.5 de 10
trustpilot logo 1 trustpilot logo