About
About Us
Help
Privacy Policy
Terms of Service
LearnDesk
Learn
Academics
Business
Creative
Health and Fitness
Lifestyle
Personal Development
Software
Topics
Admissions
Engineering
Hardware
Hospitality
Humanities
Chinese
Languages
Maths
Pharma
View All Topics
Topics
Accounting
Advertising
Analysis
Analytics
Business Communication
eCommerce
Entrepreneurship
Finance
Finance - India
Freelancing
View All Topics
Topics
Arts & Crafts
Audio Editing
Audio Production
Dance
Design
Film Production
Music
Photography
Video Production
Writing
View All Topics
Topics
Dieting
Fitness
Food Safety
Games
Medical
Medical Professionals
Meditation
Mental Health
Pregnancy
Safety & First Aid
View All Topics
Topics
Beauty & Makeup
Food
Fashion
Gaming
Home Improvement
Parenting
Pet Care & Training
Relationships
Sustainable Living
View All Topics
Topics
Career Development
Learning
Religion and Spirituality
Self-Improvement
View All Topics
Topics
Accounting
Amazon Web Services
App Development
Continuous Integration
Backup Software
Business Automation
Computational Fluid Dynamics
Business Intelligence
Computer Aided Design (CAD)
View All Topics
Teach
Teach Online
Schedule
Items
Billing
Users
Marketing
Analytics
Settings
Change School
Account
Log In
Sign Up
Build Excel Models and Dashboards Using Advanced Formulas and Functions
Introduction
Course Outline
Preview
Minimum Requirements for the Course
Preview
Prepayments Introduction
Preview
Download All Resources for the course
MonthEnd Prepaid Expenditure Model
Exact End Date Prepaid Expenditure Model
Accounting for Prepaid Expenses
Prepaid Expenses (Prepayments) Accounting Definition
Preview
Prepaid Expense Example (How Accounting works for Prepayments)
Advantages and Disadvantages of Prepaid Expenses
Excel Formulas Detailed (Intro to three Excel Models)
Intro to PRO Excel Models and Formulas
Preview
Date Function
EOMONTH Function
DATEVALUE function
IF Function
IFS Function (Office 365 Only)
VLOOKUP Function
MATCH Function
INDIRECT Function
NAMED Ranges (Name Manager)
Advanced Version of VLOOKUP Function (All other formulas combined)
Formula based Prepaid Expenses Model (Schedule)
Introduction to Model and Control Panel Tab (Important Sheet Tab)
Preview
Formula Based Prepaid Expenses Model - Deep Dive (Part 1)
Formula Based Prepaid Expenses Model - Deep Dive (Part 2)
Formula Based Prepaid Expenses Model - Deep Dive (Part 3)
IFS Function - Month End date Prepayment calculation
Prepaid Expenses - Closing Balance Summary Tab (Formula Based Summary)
Protecting Formulas Cells and Fields in the Model
Calculate Prepaid Expenses Amortisation from Exact Start date (Prepayment date)
Exact Date Prepaid Amorisation calculation Intro
Formulas update and Model Changes for Exact Prepaid Exps Calculation
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 1)
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 2)
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 3)
Formulas Update for Exact Date Prepaid Exps Amortisation (Part 4)
IFS Function - Exact Date Prepayments Amortisation
Preview
Data Validation Controls (Enhancing Data Input Controls with Protection)
Download the File - ExactData-Opening-Balance-Models
Bonus: Prepayment Model with Opening Balance Calculation (Part 1)
Bonus: Prepayment Model with Opening Balance Calculation (Part 2)
Prepaid Expenses Summary with Power Query and Pivot Table
Power Query Introduction and my blog posts
Power Query and Pivot Table Prepayment Summary Table Intro
Preview
Power Query and Pivot Table Summary - Deep Dive (Part 1)
Power Query and Pivot Table Summary - Deep Dive (Part 2)
Power Query and Pivot Table Summary - Deep Dive (Part 3)
Power Query and Pivot Table Summary - Deep Dive (Part 4)
Using Array Formulas to Add Formula Protection
Bonus: Allocate Prepaid Expenditure Cost Centre Wise 1
Bonus: Allocate Prepaid Expenditure Cost Centre Wise 2
Download the new version of file here
Bonus: Prepayment Model with Opening Balance Calculation (PQ and PT Version)
Download the new version of file here
Advanced VBA Prepaid Expenses Amortisation Model
Changing Macros Security in Excel
Complete Walkthrough - Advanced VBA Prepaid Expenses Amortisation Model
Bonus : New Version - Excel VBA Model for Prepayment Expenditure
New Version - Excel VBA Model for Prepayment Expenditure
BONUS: Dynamic Dashboard for Divisional Profit and Loss statements (Easy way)
Download Resources Files here
Dynamic Dashboard Overview
Preview
Importing Profit and Loss Statements Source Files and creating YTD P Sheets
Creating Dynamic Data Validation
Creating Named Ranges for Dynamic Table Arrays (to be used in VLOOKUP Function)
Dynamic Date Column Headings for each Divisional PL Table
Dynamic Month and YTD Dashboard tables headings (PRO TIP)
Dynamic VLOOKUP Formula - Preparing First section of the Dashboard
Creating Rolling Dashboard with Dynamic VLOOKUP Function
IMPORTANT : Error Checking for your reports/Dashboard (PRO TIP)
Data Prep for Visualization: AREA Charts (Awesome trick using #NA Function)
Visualization: AREA Charts for Month - Revenue, Gross Profit and Net Profit
Visualization: DONUT Charts : Revenue, Gross Profit and Net Profit (Part 1)
Visualization: DONUT Charts: Revenue, Gross Profit and Net Profit (Part 2)
Power Query & Pivot Tables based Dashboard without any Formulas, Fully Dynamic
Download Resources Files here
Introduction - Formula-less Dashboard - Fully Dynamic and easily refreshed
Preview
Understanding the data files before building dashboard
Consolidating Reports with Power Query (Get & Transform) , How to install PQ
Dynamic File Path Trick in Power Query with Parameters (Amazing trick)
Conditional Cumulative totals with SUMIFS Function
Bonus: Conditional Cumulative totals with Power Query Custom Formula (M Code)
File Dowload: Bonus: Conditional Cumulative totals with Power Query Custom Formula (M Code)
Dashboard Creation - Pivot Table showing Month and YTD KPIs division wise
Dashboard Creation Donuts Charts linked with Pivot Table (Replicate Charts fast)
Dashboard Creation - Line Charts
Update Dashboard with Additional Divisional Data with Few Click (Magical)
Thank you
Ultimate Prepaid Expenditure Model (Super Bonus)
Thank you
Introduction - Formula-less Dashboard - Fully Dynamic and easily refreshed
Build Excel Models and Dashboards Using Advanced Formulas and Functions
Power Query & Pivot Tables based Dashboard without any Formulas, Fully Dynamic
By:
Prashant Panchal
5 minutes
Share
Share the link to this page
Copied
Facebook
Twitter
WhatsApp
LinkedIn
Email
Add to Calendar
Add the class to your calendar
Add to Google Calendar
Add to Apple Calendar
Add to Yahoo Calendar
Add to Outlook Calendar
Print
Completed
Previous
Next
Sign Up
Signup to access thousands of classes
Continue with Facebook
Continue with Google
OR
Continue with Email
Already a member?
Log In
By registering for a LearnDesk account, you agree to our
Terms of Service
and
Privacy Policy
.
Share
Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.
Share with Google Contacts