Advanced Financial Management in Power BI

a) In-Built visual Vs Market Place visual of Power BI b) Explore “Drill Down” and “Drill Up” features c) Explore “Small Multiples” features d) Artificial insight features in charts e) Table Vs Matrix Visual i) Drill Down and Drill Up by Row ii) Drill Down and Drill Up by Column f) Explore Conditional Formatting features g) Understand the Syntax of DAX h) Concept of DAX like i) Object, ii) Operators and Their Types, iii) Measures, iv) Filters, v) Variable and Comments i) Aggregation Vs Interaction Functions j) Explicit and Implicit Measure k) Calculated Column and Measures l) Filter and Row Context Hands-on Training: o Learn how to write DAX Functions o Explore the features of Power BI visuals

a) Understand the concept of Date-Based Analysis and its important in corporate world. b) Conduct analysis based on Dates i) Previous Period Analysis (Previous Month, Previous Quarter & Previous Year) ii) Growth Analysis (DoD %, MoM %, QoQ % & YoY %) iii) Same Period Previous Year (Feb 21 Vs Feb 22, Q1 2021 Vs Q1 2022, etc.) iv) To Date Analysis (MTD, QTD, or YTD) Hands-on Training: o Time Intelligence Analysis using DAX

a) Understand the important of “Variable” as an element of DAX and how it can be useful. b) Explore the use of “FORMAT” and “UNICHAR” DAX functions and how it is applicable in corporate reporting. c) Display the big numbers with enhanced visualizations techniques i) Big Number of Actual Sales ii) Absolute and Relevant Variance with up/down arrow iii) Dynamic Formatting of color [Red and Green] d) Conduct Category and Sub-Category wise analysis showing following in a comparative manner: i) Current Amount ii) Previous Year Amount iii) Absolute Variance Amount iv) Absolute Variance in Data bars [Red and Green] v) Relative Variance Amount vi) Relative Variance in Data bars [Red and Green] e) Add slicers form period, locations, channels etc. Hands-on Training: o Enhanced visualizations and dynamic conditional formatting using DAX

a) Explore the “Numeric Range” features of “What-if Parameters” in Power BI: i. Conduct Quadrant Analysis to analyze the products in four Quadrants: 1) To Keep, 2) To Develop, 3) To Evaluate & 4) To Retain ii. Create a Scattered Plot Chart with show Actual Sales on Y-axis and Sales Variance %age on X-axis. iii. Add two What-if Parameters of Target Sales and Target Sales Variance with a default slicer. iv. Add “Reference Line” through Format Panel of the Scattered Chart and assignment dynamic value using the FX symbol of Value in “Line” v. Create a measure for “Conditional Formatting” for the Quadrant Analysis to dynamically color code “Green”, “Grey”, “Orange” and “Red” vi. Assign the “CF Quadrant Measure” for the dynamic color in the respective quadrants using the “Color” option in the “Format Panel” b) Explore the “Field Parameters” features of “What-if Parameters” in Power BI: i. Create a Dynamic Slicers to change the Calculations, period and information with a single click as mostly used in C-level Dashboards. ii. Create a Line chart to show sales trends and Table chart to show sales by Products. iii. Add “Field Parameters to show iv. Dynamic Measures like Sales, COGS, Gross Profit and Gross Margin. v. Dynamic Period like Years, Quarters and months. vi. Dynamic Information like Product, Category, Location, Sales Person and Types of Sales. Hands-on Training: o Create a Dynamic Quadrant Analysis Dashboard with What-if Parameters.

a) The Actual Data will be for 9 months and Budget Data will be for 12 months. b) Calculate Last Day of Sales using DAX Function. c) Create a Dynamic Dax measure to ensure Apple-to-Apple comparison so that Budget Data of the same period of Actual data is taken for comparison d) Add a Dynamic Titles of the report with the ending date. e) Display the big numbers with enhanced visualizations techniques i) Big Number of Actual Sales ii) Absolute and Relevant Variance with up/down arrow iii) Dynamic Formatting of color [Red and Green] f) Create a Decomposition Tree Analysis using two “Field Parameters” to dynamically change the calculations: i) Sales Variance ii) Sales Variance Percentage g) Take a Visual from Market Place and create “Waterfall Chart to show the journey from Budget to Actual i) Positive Contributors ii) Negative Contributors Hands-on Training: o Step-by-step guidance for Performance Analysis Dashboard for regular business analysis.

a) Create multiple measures to facilitate the advanced visualization like: i) Maximum Point ii) Green Maximum Point iii) Red Maximum Point b) Explore the advanced features of “Column-Layout” to give space between category and series c) Explore the advanced features of “Data Labels” to add user-defined labels on the desired columns with dynamic color [Green and Red] d) Explore advanced features of “Error Bars” to add both “Green Maximum Point” and “Red Maximum Point”. Hands-on Training: o Step-by-step guidance for the creation of Advanced Performance Analysis

a) Understand Data Source setting and its importance. b) Learn how to make the Fixed Elements of M Language Dynamic using “Parameters”. c) Use Parameters to dynamically change the source in Power Query. d) Learn how you can dynamically do the Table Transformations like: i) Adding and Removing Rows ii) Splitting the columns as needed iii) Grouping the columns for better analysis iv) Promoting rows to make more sense of the Table Hands-on Training: o Step-by-step guidance for automation of regular report using User Interface [UI] of Power Query

a) Learn how you can dynamically do the Text & Numeric Transformations: i) Create Calendar from a single column of Date ii) Add Column using Custom Column and Column from Example iii) Add Conditional Column with parameters already provided in the Table. iv) Lean how Pivot/Un-Pivot functions and how it is useful in business analysis. b) Learn to connect Power BI with sources which are mostly used by business and Finance Professionals: i) Excel and CSV File ii) Folders and PDF c) Learn how to “Add” and “Amend” the M Code. d) Explore the relationship with M Code with “Applied Steps”. e) Understand the Syntax of M-Code. f) Learn the 5 important points to write M-Code g) Write first M-Code from scratch with few examples. h) Learn the uses of Functions and how it can be used in automation process. i) Learn the components of Tables: i) List and its uses in automation process ii) Record and its uses in automation process j) Create Tables, List and Record using M-Code Hands-on Training: o Automation of regular transformation work to convert “Raw Data into “Analysis Ready Data”

a) Understand the concept of Data Modeling and why it has an advantage over “Lookup” Functions. b) Understand 3 types of relationships i) One-to-One ii) One-to-Many iii) Many-to-One c) Understand three types of Cardinalities i) Down Side ii) Up-Side iii) Both Sides Hands-on Training: o Step-by-step guidance to understand Data Modeling through example

a) Prepare and transform data from multiple financial sources using Power Query like Un-Pivot the table and assignee the correct data types to all columns. b) Create Calendar Table in Power Query and Close and apply all the changes so that the Data is loaded in Power BI. c) Connect all the Tables in Power BI i) Actual Monthly Trial Balance Table ii) Budget Table iii) Forecast Table d) Explore DAX measures to calculate necessary figures of Income Statement for Actual and Budget: i) Revenue ii) Direct Costs iii) Gross Profit iv) Selling and Distribution Cost v) General & Admin Cost vi) Operating Income vii) Finance Cost viii) Other Income / (expense) ix) Profit before Tax x) Tax xi) Net Profit xii) EBITDA Hands-on Training: o Step-by-step guidance for Data Transformation and basic calculations of Income Statement.

a) Understand the use of “Table” and “Matrix” with an appropriate setting to use the Financial Reporting Format. b) Understand the concept of “Variable” in DAX functions. c) Explore the use of “Income Statement Template” with Top Line and Bottom Line at their place d) The Actual TB is for 7 months therefore the Budget Table will also be restricted to 7 months for Apple to Apple to comparison using DAX. e) DAX Functions to cater to Financial Calculations including: i) Actual YTD ii) Budget YTD iii) Vs Budget YTD f) Prepare Income Statement for following variants: i) Monthly Income Statement ii) Project – Wise Income Statement iii) YTD Income Statement Hands-on Training: o Step-by-step guidance for various variants of Income Statement using DAX.

a) Current Month Analysis will ensure the figures will be calculated for the last date of Actual TB Table dynamically. Following calculations will be performed using DAX: i) Actual Current Month ii) Budget Current Month iii) Vs Budget Current Month b) For Full Year Analysis, the Actual figures will be taken for 7 months and the Forecast figures will be taken for the remaining 5 months using DAX: i) Expected (Actual 7 months + Forecast 5 months) ii) Budget Full Year iii) Vs Budget c) Understand the concept of “Variable” in DAX functions. d) Explore the use of “Income Statement Template” with Top Line and Bottom Line at their place e) Prepare Income Statement for following variants: i) Current Month Income Statement ii) Full Year Income Statement f) Develop Live Chart to show Actual, Budget and Forecast in the same visual g) Create Advance KPI Card using “New Card” visual by calculate the key figures using DAX: i) Budget ii) Absolute and Relative Variance with Budget iii) Same Month Last Year iv) Absolute and Relative Variance with Same Month Last Year v) Last Year YTD vi) Absolute and Relative Variance with Last Year YTD Hands-on Training: o Step-by-step guidance for various variants of Income Statement using DAX.

a) Explore DAX measures to calculate necessary ratios of Income Statement: i) Gross Profit Margin ii) EBITDA Percentage iii) EBIT Percentage iv) Net Profit Margin b) Create Advance KPI Card using “New Card” visual by calculate the key figures using DAX: i) Budget ii) Absolute and Relative Variance with Budget iii) Same Month Last Year iv) Absolute and Relative Variance with Same Month Last Year v) Last Year YTD vi) Absolute and Relative Variance with Last Year YTD c) Explore how to add “Dynamic Commentary” using DAX in a Table visual where different projects can be classified based on certain thresholds of ratios into: i) Good Performer ii) Average Performer iii) Poor Performer. Hands-on Training: o Step-by-step guidance for Dynamic Profitability Ratio Analysis using DAX.

a) Explore DAX measures to calculate necessary figures of Balance Sheet: i) Current Assets and Non-Current Assets ii) Current Liabilities and Non-Current Labilities iii) Owners’ Equity b) Then calculate Balance Sheet related Ratios like: i) Current Ratio and Cash Ratio ii) Debt to Equity c) Explore the use of “Balance Sheet Template” and add the respective values in “Table” and “Matrix” visual Hands-on Training: o Step-by-step guidance for the creation of Balance Sheet Analysis Dashboard.

a) Create multiple measures to facilitate the advanced calculations like Cash flow from: i) Operating activities ii) Investing activities iii) Financing activities b) Explore the use of “Cash Flow Statement Template” and add the respective values in “Table” and “Matrix” visual Hands-on Training: o Step-by-step guidance for the creation of Cash Flow Statement Analysis Dashboard.

a) Using Date option in Power Query, A new column "Age" is added, which calculates the age of each invoice based on a fixed reference date (31st May 2024). b) The resulting Age column is then converted to a whole number for consistency c) An "Ageing Group" column is added to classify each invoice based on the number of days past due. The classification i.e. 01 to 30 days and more. d) To ensure the correct order of ageing groups in reports and visuals, a "Sorting" column is added. e) Create measures of “AR Over Due”, “AR Not Due” and “AR Gross Amount”. And add them in Table visual and different visual for the analysis of decision maker. Hands-on Training: o Step-by-step guidance to create Dynamic Customer Ageing Analysis using Power Query and DAX

a) Add the multiple tables like Cash Table, Bank Table, Income Table and Expenses Tables. b) Create calculated columns or measures in the data model to calculate KPIs such as Net Cash Flow, Beginning Cash Balance, Ending Cash Balance, Total Cash Inflows, Total Cash Outflows, etc. Hands-on Training: o Step-by-step guidance to create Dynamic Daily Cash Flow Management using DAX and Data Modeling.

a) Add high level visuals related to i) Income Statement ii) Balance Sheet iii) Cash Flow Statement. iv) Accounts Receivable v) Accounts Payable vi) Monthly Cash Flow Management Hands-on Training: o Step-by-step guidance for the creation of CFO Dashboard.

a) Understand the Science behind Data Visualization. i) Iconic Memory ii) Short-Term Memory iii) Long-Term Memory b) Understand “Eye-Tracking Method for selection of appropriate charts. c) Understand the “Rules” for the selection of appropriate visuals for appropriate financial data d) Best Practice for mostly used business charts e) IBCS Chart of Business Reporting f) Use of Appropriate Color Theme. g) Dashboards – Types and Layouts & Structure. h) Z-Patterns for Dashboard Design i) Ideals Fonts for Text and Numbers j) Use Storyboarding to Design Layouts. k) Storytelling Element in an Effective Dashboard like clarity, visual cues, meaning equivalents, attract focus. l) Ideals Dashboards with 11 Take away of the course. Hands-on Training: o Step-by-step guidance for Data Visualization Techniques. o Step-by-step guidance for creating Storytelling Dashboards and Reports.