Outcomes
To provide participants with an in-depth knowledge with different types of MS Excel functions, Power BI, Power Query, Power Pivot, Pivot tables and applications that are needed to prepare spread sheets and building financial modelling
Target Group
This programme is a 'must' for all Accountants, financials & administrative
Contents
• Tips and tricks
• Absolute and relative references
• R1C1 reference style
• Functions: ABS, If, Sum if, Round, Match, Index, Index Match, Int, Mod , Average If, If Error, aggregate
• LOOKUP, VLOOKUP, HLOOKUP, XLOOK, XMATCH in building the financial modelling
• Preparation of reports
• Conditional formatting in financial modelling
• Manage rules in Excel to Build a finance model
• Data validation
• Combine values from multiple ranges into one new range
• Remove duplication
• Text to columns
• Using file manager, define name & create from selection in preparing financial modelling
• Scenario analysis
• Goal seeking & back solving
• Using MS Excel in prepare financial statements: Statement of financial position, Statement of comprehensive income and Statement of cash flows
• Cash forecast
• Financial statements and ratios
• Excel & visual basic application (VBA)
• Building Financial models by Recording Macros
• Break–even point in Excel
• Laying out the pivot table
• Modifying the pivot table
• Pivot table examples
• Grouping pivot table items
• Creating a calculated field or calculated item
• Referencing cells within a pivot table – GETPIVOTDATA
• Producing a financial report using a pivot table
• Pivot charts power
• Pivot power
• Query
• Power query
• Power Pivot & Relationships
• Preparing Data for Analysis with Microsoft Excel
• Harnessing the Power of Data with Power BI
• Extract, Transform and Load Data in Power BI
• Power Business intelligence (Power BI)
• Building blocks of Power BI
• Data Modelling in Power BI
• Power BI & Relationships
• Power BI & Visualizations
• Data Analysis and Visualization with Power BI
• Explore data in Power BI
• Data Visualizations
• Power BI & Formulas
• Create analytics reports with Power BI
• Publish and share in Power BI
• The Beneish model
• Loan amortization scheduled
• Calculated payments for a loan based on a constant payments and constant interest rate (PMT)
• Returns the payments on the principal for a given investment based on periodic, constant payments and constant interest rate
• Price, PV and NPV