| 1.1 |
- Revision of Advanced Excel Functions:
- Lookup & Referencing functions : Hlookup/Vlookup, Indirect, Choose, Offset, Index
- Logical functions : If, Iserror
- Text functions : Left, Right, Mid, Concatenate
- Placing Form Controls On A Worksheet
- Create, define and delete Range Names
|
| 1.2 |
- Company Case Study – A Real Company
- Understand The Current Reporting And Analytic Needs Of Companies
- Overcome The Constraints Faced By Accountants
|
| 1.3 |
- Conceptual Framework
- Reuseable Database Structure
- Build Logical Process Block
- Design Relevant Reporting Templates
|
| 1.4 |
- Database Management
- Database Components
- Features Of Excel Databases
- Database Design Rules
|
| 1.5 |
- Manage Data From ERP System
- Download Text Data From ERP System
- Import Data From External ERP System Using Text Import Wizard
- Easy Text Manipulation Using Various TEXT Functions
|
| 1.6 |
- Design Master Control Sheet that Connects numerous reports
- Automate Data-Updating Tasks
- Create Meaningful Range Names And Unique Identifiers For Automation
- Replacing "Hard-Wired" Formulas With "LIVE" Formulas
- Flexible Cell Referencing For Optimal Speed In Updating Tasks
|
| 1.7 |
- Easy Data Navigation Using Ready Tools
- Smart Combination Of Excel Referencing Functions
|
| 1.8 |
- Managing Erroneous Functions Results
- Auto Detect And Prevention Of Errors In Commonly Used Formulas
- Decision-Making Functions
- Create Nested Functions
|
| Product and Country Sales Reporting Models |
| 1.9 |
- Common Problems Faced By The Reporting Team
- Reports Requirements Of Management And Board
|
| 1.10 |
- Data Empire For Reporting
- Construct Data Tables And Its Range Names
- Setup Logic Block For Processed Data
|
| 1.11 |
- An Interactive Product Report
- Consolidate Data Into Useful Information In Logic Block
- Sustainable Excel Model Through The Months Using Interactivity Tools
- Dynamic Data Source For Interactive Charts
|
| 1.12 |
- A Wide Focus Country Sales Report
- Easy download and import of external data from the Web
- Simplified Model Building Using Flexible Cell Linking Formulas
- Incorporate Meaningful Self-Compute Variance Analysis
- Powerful Visuals Representation Using Charts
|
| Business Reporting Model |
| 2.1 |
- A Key Summary Business Report
- Summarizing Key Results Useful For Management
- Drill Down Capability To Detail Reports
- Moving 12-Month Financial Dashboard Putting Key Results Into Perspective
|
| 2.2 |
- Putting Them All Together
- Useful Tools And Shapes To Create A User Friendly Main Menu
- Special Buttons And Commands To Connect All The Reports
- Bullet-Proof The Workbook Preparing For E-Reporting
|
| 2.3 |
- Final Presentation and Individual Assignment Showcase
- Using Excel As The More Interactive Presentation Tool
- You Wouldn't Believe It's Excel : Tips And Tricks
|
| Investment Evaluation Model |
| 2.4 |
- Case Study – A Real Company
- Buy Or Lease Analysis
|
| 2.5 |
- Using Standard Templates
- Loan Amortization
- Auditing Toolbars
- Input Area
|
| 2.6 |
- Process Area
- Investment evaluation using DCF calculations
- Option Box To Select Alternative Evaluation Methods
- Interactive Decision Recommendations Based On DCF Results
|
| 2.7 |
- Report Area
- One-Variable Data Table To Display DCF Results On Varying Discount Factor
- Break-Even Analysis Chart
|
| 2.8 |
- Sensitivity Analysis
- Using Goal Seek
- Sensitivity "What If" Analysis Using Excel Two-Variable Data Table
|
| 2.9 |
- Macro , VBA Editing
- Bullet-Proofing Your Macros From Errors In Cell-Referencing Due To Changes In Spreadsheet Structure
|
| 3.1 |
- Case Study – A Real Company
- Design of Templates for input data
- Protection of templates
- Data validation of input data
|
| 3.2 |
- Budgeting Process
- Sales Budget
- Cost of Sales Budget
- Preparation of the Profit & Loss statement
|
| 3.3 |
- Linked Profit & Loss, Cash Flow And Balance Sheet Statements
- Interactive Formulas To Vary Credit Terms For AR And AP
- Ensuring Integrity Of Balance Sheet With Built-In Balancing Check
|
| 3.4 |
- Scenario Simulation Models
- "What-If" Simulations Without Re-Writing Formulas
- Implementing Spinners And Control Forms
- Creative presentation of scenarios
- Implementing Stepped Expenses In Budget
|
| 3.5 |
- Financial Interpretation Of Budget Results - Providing Additional Value to Budget Analysis
- Capital Restructuring
- Asset Monetization
- Re-Financing Options
- Outsourcing Opportunities
|
| 3.6 |
- Reinforce Techniques Learnt
- Project Work
- Briefing Of 3 Project Choices – Real Company Cases
- Technical Assistance
- 1-Hour Complimentary Technical Assistance Provided To Assist Implementing Of Newly Acquired Skills In The Workplace
|
