Module 1 Financial Modelling Introduction
- What is financial modelling
- Best practice in financial modelling
- Art in an effective Excel model
Module 2 Excel Skills Needed for Financial Modelling
- Conditional format
- Absolute & relative cell reference
- Switch row & column of chart
- Set up secondary axis
- Group data in pivot table
- What-if analysis skills
- Formula dimension to deal with nested if statement
- How to apply an effective model in Excel
- How to escape from “excel hell”
Module 3 Steps to Building the Financial Models
- Where to start to build a model
- Financial models – logical structure design
- Financial models – data input
- Case: Convert data table to data list
- Financial models – process/calculation
- Case: Consistent formulas in one area
- Case: Avoid hard code in formula
- Case: Running total calculation
- Financial models – output
- Case: Report format design
Module 4 Data Cleansing Skills
- How to deal with blank record
- How to deal with duplicate records
- How to deal with text
- How to find & replace certain values
- How to get data from PDF files
Module 5 Maintaining Financial Model
- Techniques to reduce error
- Common Excel error types
- How to correct and suppress error
- How to set up error checking module
Module 6 Financial Models for FA Daily Tasks
- How to use financial models in accounting jobs
- Decode accounting system logic
- Sales & accounts receivable procedures
- Case: Accounts receivable aging report
- Case: Bad debt provision
- Purchase & accounts payable procedures
- Case: Cash reimbursement form
- Case: Rebuild an inherited model
- Payroll procedure
- Case: Timesheet calculation & accrual treatments
- Cash management procedure
- Case: Accrued interest income model
- Fixed asset management procedure
- Case: FA & Depreciation model with variety asset categories
- How to design simple, clear, and readable model to deal with complex business tasks
- Project accounting procedure
- Case: Project review model