Mastering MS Excel Financial Functions For Accountants

This course is not for starters, rather, it has been packaged to enable casual and intermediate users with financial related deliverables to take their MS Excel skills to another level of proficiency.
This is a hands-on course that will showcase efficiencies with the use of advanced financial functions while introducing super user shortcuts.

Training Objectives
During this training, participants will learn how to;
❖ Automate recurring processes
❖ Use the most powerful features of Excel
❖ Know which features and functions should be used for various high-level tasks
❖ Entering and editing formulas
❖ Absolute and relative cell referencing
❖ Conditional formatting
❖ Learn to record macros
❖ Data validation and creating drop-down lists

Learning Outcomes
At the end of this course participants will be able to;
❖ Project financial statements including key schedules
❖ Perform financial statement analysis
❖ Perform sensitivity and scenario analysis for Budget
❖ Understand visual basic: macro and user-defined functions
❖ Manipulate and create pivot charts
❖ Secure worksheets and workbooks
❖ Understand IF functions and other efficiency gains
❖ Learn using advanced Excel tools
❖ Prepare Final Accounts using Pivot Tables

Course Outline

ANALYZING LOANS, PAYMENTS & INTEREST
❖ PMT Calculating a loan payment
❖ PPMT & IPMT Calculating principal and interest per loan payment
❖ CUMPRINC & CUMIPMT Calculating cumulative principal and interest btw periods
❖ ISPMT Calculating interest paid during a specific period
❖ EFFECT and NOMINAL Finding nominal and effective interest rates
❖ ACCRINT and ACCRINTM Calculating accrued interest for investments
❖ RATE Discovering the interest rate of an annuity
❖ NPER Calculating the number of periods in an investment

CALCULATING DEPRECIATION
❖ SLN Calculating depreciation using the straight-line method
❖ DB Calculating depreciation using the declining balance method
❖ DDB Calculating depreciation using the double-declining balance method
❖ SYD Calculating depreciation for a specified period
❖ VDB Calculating declining balance depreciation for a partial period
❖ AMORDEGRC Calculating depreciation using a depreciation coefficient
❖ AMORLINC Calculating depreciation for each accounting period

THE AMORTISATION TABLE
❖ Introduction to amortization
❖ The elements and building blocks
❖ A case study
❖ Building a complete amortization table manually
❖ Building a complete amortization table with the PMT function
❖ Checks & control

DATA ANALYSIS WITH PIVOT TABLE
❖ Introduction
❖ Creating & pivoting
❖ Summarizing data
❖ Formatting the pivot table
❖ Applying conditional formatting
❖ Creating & manipulating Pivot charts

FORMULA AND FUNCTION TIPS AND SHORTCUTS
❖ Displaying and highlighting formulas
❖ Auditing tools
❖ Using entire row/column references
❖ Copying column formulas instantly
❖ Converting formulas to values with a simple drag
❖ Updating values without formulas
❖ Simplifying debugging formulas
❖ Enhancing readability with range names
Creating 3D formulas to gather data from multiple sheets

FORMULA AND FUNCTION TOOLS
❖ Understanding the hierarchy of operations in Excel formulas
❖ Using the Formulas tab on the Ribbon for locating functions
❖ Using the Insert Function button for guidance with unfamiliar functions
❖ Using and extending AutoSum button capabilities
❖ Using absolute and relative references in formulas
❖ Using mixed references in formulas

IF AND RELATED FUNCTIONS
❖ Exploring IF logical tests and using relational operators
❖ Creating and expanding the use of nested IF statements
Using the AND and OR functions with IF to create compound logical tests

LOOKUP AND REFERENCE FUNCTIONS
❖ Looking up information with VLOOKUP and HLOOKUP
❖ Finding approximate matches with VLOOKUP
❖ Finding exact matches with VLOOKUP
❖ Nesting lookup functions
❖ Using VLOOKUP with large tables
❖ Finding table-like information within a function with CHOOSE
❖ Locating data with MATCH
❖ Retrieving information by location with

INDEX
❖ Using MATCH and INDEX together

Target Audience

Accountants, Financial Analysts, Auditors, Equity Analysts, Budgeting Managers, Credit Controllers & anyone with corporate finance & accounts responsibilities.

Mode Of Delivery                                                                                                                                                                                                                         Face-to-face learning with instructor-led hands-on practical demonstration and learning. Each participant is expected to work individually on a  computer         2 days of learning sessions
Course material and Course Certificate will be issued
Virtual learning available on request