Course Overview
As everyone knows, Microsoft Office Excel is a powerful tool for analyzing financial data and preparing financial reports. If you are ready to harness Excel for financial reporting and analysis, then this program is for you. you will learn how to put Excel to use to deliver accounting and financial solutions in critical areas such as the following.
- Formatting reports and financial statements using advanced techniques,
- Assembling and printing reports and schedules, and
- Using templates to automate reporting processes.
Additionally, you will learn options for consolidating financial data from multiple sources, how to create Excel visualizations to improve reader’s comprehension of financial information, and advanced data analysis techniques such as PivotTables connected in real-time to your accounting database.
Like most powerful tools, Excel contains features and functions that are not immediately obvious to many users. In this course, you will uncover many of these hidden features to help you prepare computationally accurate and aesthetically pleasing reports in far less time than you might imagine possible. You do not want to miss this opportunity to learn how to take advantage of Excel to overcome many of the reporting and analysis obstacles currently facing you.
Course Objectives
Upon completing this course, you should be able to:
- Implement techniques such as Custom Formats, Conditional Formatting, and the Accounting Format for formatting financial statements and other Excel-based reports more efficiently
- Differentiate between Excel’s default templates and templates for financial reporting and analysis; create templates and apply them to financial reporting and analysis situations
- Apply Excel features, such as Sorting, Data Tables, Subtotal, Filters, and PivotTables to aid in financial reporting and analysis
- List and apply four techniques for consolidating data in Excel
Who this course is for
Accounting, Auditors and financial professionals desiring more knowledge in using Excel for financial statement preparation and analysis as well as those preparing Accounting Schedules to enable preparation of Financial statements
Course contents
- Relative, Mixed & Absolute Cell referencing.
- Logical IF functions, Nested IF Functions, IF AND, IF OR
- Sub-totals
- COUNT, COUNTA, COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS
- Flash fill
- Format Cells
- Look-up functions (Vertical and Horizontal Look-up functions)
- Conditional Formatting
- Data Validation techniques
- Data Protection and encryption
- Printing reports professionally
- Excel Tables and Pivot Tables
- Preparing Aging analysis reports
- Preparing Fixed Asset Register & its summarized schedules
- Introduction to Power Query in Excel
- Designing Chart of Accounts and General Ledger in Excel
- Posting transactions in the General Ledgers using Practical scenarios
- Automating Trial Balance from General Ledger and understand its logic
- Preparing Statement of Profit or Loss (Surplus or Deficit)
- Preparing Statement of Changes in Equity (Changes in Net Assets)
- Preparing Statement of Financial Position
- Preparing Statement of Cash flows
- Preparing Multi-period Financial Statements (IFRS/IPSAS Compliant FS)
- Recording Year-end Adjusting Entries
- The Adjusted Trial Balance (ATB)
- Mapping ATB to the Financial Statements
- Preparing Income Statements from ATB
- Preparing Balance Sheets from ATB
- Preparing Notes to Financial Statement
