About
LearnDesk
Learn  
Topics
Topics
Topics
Topics
Topics
Topics
Topics
Teach
  Teach Online
  Schedule
  Items
  Billing
  Users
  Marketing
  Analytics
  Settings
  Change School
Account
 

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

Creating Named Ranges for Dynamic Table Arrays (to be used in VLOOKUP Function)

Build Excel Models and Dashboards Using Advanced Formulas and Functions BONUS: Dynamic Dashboard for Divisional Profit and Loss statements (Easy way)
By: Prashant Panchal
3 minutes
Share the link to this page
Copied
  Twitter
  Email
Add the class to your calendar
Print
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$69.99
List Price:  $99.99
You save:  $30
€67.18
List Price:  €95.97
You save:  €28.79
£55.86
List Price:  £79.80
You save:  £23.94
CA$97.87
List Price:  CA$139.82
You save:  CA$41.95
A$107.62
List Price:  A$153.75
You save:  A$46.13
S$94.24
List Price:  S$134.64
You save:  S$40.39
HK$544.83
List Price:  HK$778.36
You save:  HK$233.53
CHF 62.54
List Price:  CHF 89.34
You save:  CHF 26.80
NOK kr774.97
List Price:  NOK kr1,107.14
You save:  NOK kr332.17
DKK kr501
List Price:  DKK kr715.75
You save:  DKK kr214.74
NZ$119.95
List Price:  NZ$171.37
You save:  NZ$51.41
د.إ257.07
List Price:  د.إ367.26
You save:  د.إ110.19
৳8,361.58
List Price:  ৳11,945.63
You save:  ৳3,584.04
₹5,909.84
List Price:  ₹8,442.99
You save:  ₹2,533.15
RM312.71
List Price:  RM446.75
You save:  RM134.04
₦118,484.88
List Price:  ₦169,271.38
You save:  ₦50,786.49
₨19,443.22
List Price:  ₨27,777.22
You save:  ₨8,334
฿2,412.28
List Price:  ฿3,446.26
You save:  ฿1,033.98
₺2,418.32
List Price:  ₺3,454.90
You save:  ₺1,036.57
B$406.01
List Price:  B$580.04
You save:  B$174.03
R1,267.89
List Price:  R1,811.35
You save:  R543.45
Лв131.37
List Price:  Лв187.69
You save:  Лв56.31
₩98,301.65
List Price:  ₩140,436.95
You save:  ₩42,135.30
₪259.10
List Price:  ₪370.16
You save:  ₪111.06
₱4,125.14
List Price:  ₱5,893.31
You save:  ₱1,768.17
¥10,832.35
List Price:  ¥15,475.45
You save:  ¥4,643.10
MX$1,429.79
List Price:  MX$2,042.64
You save:  MX$612.85
QR255.18
List Price:  QR364.56
You save:  QR109.38
P956.90
List Price:  P1,367.06
You save:  P410.16
KSh9,061.51
List Price:  KSh12,945.58
You save:  KSh3,884.06
E£3,475.01
List Price:  E£4,964.52
You save:  E£1,489.50
ብር8,566
List Price:  ብር12,237.67
You save:  ብር3,671.66
Kz63,900.87
List Price:  Kz91,290.87
You save:  Kz27,390
CLP$69,057.73
List Price:  CLP$98,658.13
You save:  CLP$29,600.40
CN¥506.93
List Price:  CN¥724.22
You save:  CN¥217.29
RD$4,217.06
List Price:  RD$6,024.63
You save:  RD$1,807.57
DA9,397.90
List Price:  DA13,426.15
You save:  DA4,028.25
FJ$159.29
List Price:  FJ$227.57
You save:  FJ$68.27
Q540.13
List Price:  Q771.64
You save:  Q231.51
GY$14,638.82
List Price:  GY$20,913.50
You save:  GY$6,274.67
ISK kr9,776.20
List Price:  ISK kr13,966.60
You save:  ISK kr4,190.40
DH703.91
List Price:  DH1,005.63
You save:  DH301.72
L1,275.33
List Price:  L1,821.98
You save:  L546.64
ден4,132.76
List Price:  ден5,904.20
You save:  ден1,771.43
MOP$561.01
List Price:  MOP$801.48
You save:  MOP$240.46
N$1,268.91
List Price:  N$1,812.81
You save:  N$543.90
C$2,574.70
List Price:  C$3,678.31
You save:  C$1,103.60
रु9,449.77
List Price:  रु13,500.25
You save:  रु4,050.48
S/265.32
List Price:  S/379.05
You save:  S/113.72
K281.71
List Price:  K402.47
You save:  K120.75
SAR262.77
List Price:  SAR375.40
You save:  SAR112.63
ZK1,934.92
List Price:  ZK2,764.29
You save:  ZK829.37
L334.42
List Price:  L477.77
You save:  L143.34
Kč1,702.59
List Price:  Kč2,432.37
You save:  Kč729.78
Ft27,646.05
List Price:  Ft39,496.05
You save:  Ft11,850
SEK kr772.42
List Price:  SEK kr1,103.50
You save:  SEK kr331.08
ARS$70,259.44
List Price:  ARS$100,374.93
You save:  ARS$30,115.49
Bs483.50
List Price:  Bs690.75
You save:  Bs207.24
COP$307,238.59
List Price:  COP$438,931.09
You save:  COP$131,692.49
₡35,641.51
List Price:  ₡50,918.63
You save:  ₡15,277.11
L1,768.23
List Price:  L2,526.16
You save:  L757.92
₲546,248.87
List Price:  ₲780,388.98
You save:  ₲234,140.10
$U2,983.14
List Price:  $U4,261.82
You save:  $U1,278.67
zł291.40
List Price:  zł416.31
You save:  zł124.90
Already have an account? Log In
Previous
Next
Home
Search
Saved
Trending Classes
Filter
The latest and the best classes on LearnDesk
Filter by Interest
Close
All Topics
Academics
Business
Creative
Health and Fitness
LifeStyle
Personal Development
Software
Admissions
Engineering
Hardware
Hospitality
Humanities
Chinese
Languages
Maths
Other
Pharma
Research
Science
Social Science
Teaching
Test Preparation
K-12
School
Accounting
Advertising
Analysis
Analytics
Business Communication
eCommerce
Entrepreneurship
Finance
Finance - India
Freelancing
Internet of Things
Digital Transformation
Human Resources
Industry
Management
Marketing
Media
Operations
Other
Project Management
Public Relations
Real Estate
Sales
Strategy
Arts & Crafts
Audio Editing
Audio Production
Dance
Design
Film Production
Music
Photography
Video Production
Writing
Dieting
Fitness
Food Safety
Games
Medical
Medical Professionals
Meditation
Mental Health
Pregnancy
Safety & First Aid
Self Defense
Sports
Beauty & Makeup
Food
Fashion
Gaming
Home Improvement
Parenting
Pet Care & Training
Relationships
Sustainable Living
Travel
Career Development
Learning
Religion and Spirituality
Self-Improvement
Accounting
Amazon Web Services
App Development
Continuous Integration
Backup Software
Business Automation
Computational Fluid Dynamics
Business Intelligence
Computer Aided Design (CAD)
Content Management System
Customer Relationship Management
Database
Data Mining
E-Commerce
Enterprise Asset Management
Enterprise Resource Planning
Game Development
Google Cloud
Linux
Artificial Intelligence
Machine Learning
Master Data Management
Microsoft
Music Software
Network and Security
Open Source
Operating System
Other
Process Management
Productivity Software
Programming Languages
Robotics
Supply Chain Management
Testing
Teaching
Web Development

Sign Up

Sign Up

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.