Master MS Excel for financial data analysis & manipulation | Tableau to visualize financial data & make financial models
What you will learn
Master advanced functions and formulas in MS Excel for financial data analysis.
Explore techniques to manipulate and cleanse financial data effectively using Excel.
Discover how to create dynamic financial models and perform accurate financial forecasting.
Develop expertise in using Tableau to visualize financial data and build interactive dashboards.
Create visually compelling charts, graphs, and visualizations for financial analysis in Tableau.
Analyze financial statements and perform ratio analysis to evaluate financial performance.
Apply your skills to real-world financial scenarios and case studies, making data-driven decisions.
Enhance your ability to communicate financial insights through effective data visualization.
Description
If you are a finance professional or aspiring financial analyst looking to elevate your skills in data analysis and visualization, then this course is designed just for you. Are you ready to unlock the power of MS Excel and Tableau to become a master in financial analytics?
In this comprehensive course, you will dive deep into the world of financial analysis, learning how to leverage the capabilities of MS Excel and Tableau to manipulate, analyze, and visualize financial data. Get ready to take your financial analysis skills to new heights and impress your colleagues and stakeholders with powerful insights and visually compelling presentations.
In this course, you will:
- Develop proficiency in MS Excel for financial data analysis, including advanced functions, formulas, and techniques.
- Master the art of financial data manipulation and cleansing in Excel, ensuring accuracy and reliability.
- Learn how to create dynamic financial models and perform financial forecasting using Excel.
- Explore the power of Tableau in visualizing financial data, building interactive dashboards, and generating meaningful reports.
- Discover techniques for creating financial charts, graphs, and visualizations that effectively communicate insights.
- Gain hands-on experience in analyzing financial statements, performing ratio analysis, and conducting financial performance assessments.
- Apply your skills to real-world financial scenarios and case studies, sharpening your analytical abilities.
- Understand the importance of data-driven decision-making in finance and how analytics can drive strategic outcomes.
In today’s data-driven financial landscape, mastering the tools and techniques of financial analytics is essential for success. Whether you are an analyst, accountant, or finance professional, this course will equip you with the skills and knowledge to excel in your career. Don’t miss this opportunity to become a sought-after financial analytics expert.
Enroll now and embark on your journey to master financial analysis with Excel and Tableau!
English
language
Content
Introduction
Introduction
Course Resources
Prerequisites: Excel formulas and functions
Basic Formula Operations
This is a milestone!
Mathematical Functions – Part 1
Mathematical Functions – Part 2
Textual Formulas – Part 1
Textual Formulas – Part 2
Logical Formulas
Date-Time Formulas
Lookup Formulas_(V Lookup, Hlookup, Index-Match)
Time Value of Money
Future Value
Excel: Simple Interest and Compound Interest
Net Present Value
Internal Rate of Return
EXCEL: NPV and PV for calculating Present Value
Excel: Internal Rate of Return
Excel: NPV and IRR for particular dates
Analysis of Profit and Loss statement
Horizontal and Vertical Analysis
Excel: Vertical Analysis
Excel: Horizontal Analysis
Loan Repayment Calculations
Loan Repayment concepts
Excel: Calculating Payments
Excel: Calculating Rate or Term
Methods of Calculating Depreciation
Depreciation Methods and Straight Line Method
Double Declining Balance Depreciation
Sum of Years Digits Method
Excel: Calculating Depreciation
Financial Ratio Analysis
Theory: Financial Ratios
Excel: Building a Template for Financial Ratio Analysis
Financial Charts and Dashboard – Introduction
Financial Charts – Introduction
Charts and Dashboards using Excel
Excel Charts – Categories of messages that can be conveyed
Elements of charts
The Easy way of creating charts
Bar and column charts
Formatting charts – Part 2
Formatting Charts – Part 2
Line Charts
Area Charts
Pie and Doughnut Charts
Why we should avoid Pie charts
Scatter plot or XY chart
Frequency Distribution and Histograms
Stock Charts
Sparklines
Waterfall Chart
Heat Maps
New! Maps Chart – only for Excel 2019 and above
Maps chart
Case Study – Finance Dashboard
Financial Dashboard – Introduction
Financial Dashboard – Time Series of Profit & Profit Margin
Financial Dashboard – Trend of Revenue and Projection
Financial Dashboard – Expense Analysis using Pie Chart
Financial Dashboard – Target monitoring using Column chart
Financial Dashboard – Collating and Formatting to create a Dashboard
Pivot Tables
Pivot Tables
Pivot Charts
NEW! Importing financial data from external data sources
Importing tables from PDF – Excel 2021 and office 365
Importing data from websites – Excel 2019 and office 365
Predictive Analytics – Introduction
Linear Regression – Introduction
Basics of Statistics
Types of Data
Types of Statistics
Describing data Graphically
Measures of Centers
Measures of Dispersion
Machine Learning – Introduction
Introduction to Machine Learning
Building a Machine Learning Model
Getting Data Ready for Regression Model
Gathering Business Knowledge
Data Exploration
The Data and the Data Dictionary
Univariate analysis and EDD
Discriptive Data Analytics in Excel
Outlier Treatment
Identifying and Treating Outliers in Excel
Missing Value Imputation
Identifying and Treating missing values in Excel
Variable Transformation in Excel
Dummy variable creation: Handling qualitative data
Dummy Variable Creation in Excel
Correlation Analysis
Creating Correlation Matrix in Excel
Creating Regression Model
The Problem Statement
Basic Equations and Ordinary Least Squares (OLS) method
Assessing accuracy of predicted coefficients
Assessing Model Accuracy: RSE and R squared
Creating Simple Linear Regression model
Multiple Linear Regression
The F – statistic
Interpreting results of Categorical variables
Creating Multiple Linear Regression model
Finance Case Study – Linear Regression
Case Study Linear Regression – Introduction
Case study Linear Regression – Data
Case study Linear Regression – Preprocessing
Case study Linear Regression – Results
Case study Linear Regression – Prediction
Tableau
Why Tableau
Tableau Products
Installation and getting started
Installing Tableau Desktop and Public
About the data
Connecting to data
Live vs Extract
Combining data to create a Data model
Combining data from multiple tables
Relationships in Tableau
Joins in Tableau
Types of Joins in Tableau
Union in Tableau
Physical Logical layer and Data models
The visualization screen – Sheet
Data categorization in Tableau
Types of Data – Dimensions and Measures
Types of Data – Discreet and Continuous
Changing Data type in Tableau
Most used charts
Bar charts
Line charts
Scatterplots
Customizing charts using Marks shelf
Marks cards
Dropping Dimensions and Measures on marks card
Dropping Dimensions on Line chart
Adding marks in scatterplot
Other important charts
Text tables, heat map and highlight tables
Pie charts
Area charts
Creating custom hierarchy
Tree map
Dual combination charts
Creating Bins
Histogram
Grouping and Filtering data
Grouping Data
Filtering data
Dimension filters
Measure filters
Date-Time filters
Filter options
Types of filters and order of operation
Customizing visual filters
Sorting options
Map chart in Tableau
How to make a map chart
Considerations before making a Map chart
Marks card for customizing maps
Customizing maps using map menu
Layers in a Map
Visual toolbar on a map
Custom background images
Territories in maps
Data blending for missing geocoding
Calculations and Analytics
Calculated fields in Tableau
Functions in Tableau
Table calculations theory
Table calculations in Tableau
Understanding LOD expressions
LOD expressions examples
Analytics pane
Sets and Parameters
Understanding sets in Tableau
Creating Sets in Tableau
Parameters
Dashboard and Story
Dashboard part -1
Dashboard part – 2
Story
Appendix
Connecting to SQL data source
Connecting to cloud storage services
The final milestone!
Congratulations and about your certificate
Bonus lecture