ADVANCED EXCEL FOR PRACTISING ACCOUNTANTS & CFO

This is practically based on Excel formulas applied in the accounting and finance industry, after this, you will be able to:

1. Reconciled Bank with Excel formulas
2. Reconciled VAT on I tax and Ledger
3. Develop a stock management system
4. Develop payroll, PAYE, CSV, P10, P9
5. General Automation of Excel workbook
6. Free Excel templates on the same
7. Reconciliation of KRA Auto populated VAT return.

                           LEVEL I 

Advanced Excel Shortcuts 

• Extensive exercise on Excel shortcuts 
• Doing away with the mouse 
• Mastering the shortcut window 
• General understanding of Microsoft Excel.
• Cell formatting 
• Conditional formatting 

Data Presentation with charts 

• Types of charts and their specific uses. 
• Sorting and Filtering data 
• Sort data as per value, color, icons 
• Filter based on Font/Cell Color 
• Advanced filter criteria 
• Sub totaling. 
• Understanding References 
• Relative, Absolute & Mixed reference 
• Referencing different workbook 
• Utilizing Dynamic Named Ranges for writing formulae 

Manipulating Dates 

• Dates formats 
• Extracting Day, Month, or Year from a date value. 
• Extracting Day, Month, or Year from a date value using TEXT Formula. 
• Extracting the end month from a date value. 

           LEVEL II   

 Advanced Excel Formulas 

• Logical Functions 
• Ifs and Nested Ifs Statement 
• If, AND, OR function. 
• Error handling using IFERROR 
• Text Formulas 
• CONCATENATE 
• FIND V/S SEARCH 
• LEFT, RIGHT, MID, TRIM 
• UPPER, PROPER, LOWER 
• Lookup Formulas 
• VLOOKUP & HLOOKUP 
• XLOOKUP 
• INDEX & MATCH 
• SUMPRODUCT Function. 
• SUMIF & sumifs 
• Comparison of XLOOKUP,VLOOKUP, INDEX MATCH & DGET Function

Data Analysis Report with PivotTables & Pivot Charts 

• Analyzing and generating reports of large data using PivotTables 
• Creating PivotTables, editing and updating changes in PivotTable. 
• Using Slicers in Pivot Table 
• Creating Pivot Chart 

Data Validations and Protection 

• Specifying a valid range of values for a cell. 
• Specifying a list of valid values for a cell. 
• Creating Drop down list. 
• Auditing tool. 

Financial Analysis and Formulas 

Project Appraisal 

• Using IRR 
• Using NPV 
• Using PMT 
• Using PV 
• Using FV 

Loan Modelling 

• Modelling instalments. 
• Modelling loan balances. 
• Making a decision between a flat-rate v/s reducing balance priced loans o Effective interest rates. 

Budgeting under Scenarios 

• Introductions to planning scenarios; Best Case, Base Case & Worst Case Scenario. 
• Use of Combo Box and CHOOSE function to automate scenario analysis. 
• Build a scenario-based budget model. 

Introduction to Excel Macros and VBA for Programming 

• Introduction to Excel Macros 
• Sheet Controls and User Form Controls 
• R
  

  ---

  ---

  ecording simple macros 

 Financial Data Visualization

•   Recommended charts