The course has been designed to equip participants with powerful Finance-based report generation capabilities, auto-consolidation techniques, search and find capabilities using XLOOKUP, FILTER, LOOKUP, VLOOKUP, INDEX-MATCH, CHOOSE functions, and other Financial oriented functions, including creating User Defined Functions (a must for Finance people who wish to create their own particular Finance-based functions) and apply built-in date/time/financial tools, as well as text manipulation, with data validation and built-in user forms for easier data capture. A most notable feature of the workshop is the introduction of a simplified formula creation, with the capability to “fetch named” ranges from anywhere in the workbook, providing for faster, more elegant, and English-like formula creation, which in turn enables users to debug formulas of whatever complexity, and at the same time, enabling the tracing of formula precedents and dependencies.
Upon completion of this training course, participants are expected to be capable of creating powerful dashboards, auto-linked worksheets, and named ranges which provides easier and faster workbook navigation. Likewise, they should be able to link workbooks, to form cohesive and auto-updating capabilities. As an extra, participants would also practice the built-in function of forecasting in Excel, as well as using its powerful goal seek capabilities.
Participants would also be able to create dynamically changing data visualization using powerful slicers, with named measures or calculated fields.
At the conclusion of this 3-day webinar training course, participants are expected to be capable of creating navigable workbooks, with electronic dashboards, and automatically updating data sources, tied-in with various data visualizations and Pivot tables. They should be conversant with names, hyperlinks, dashboards, parameters for pivot charts, slicers or report filters, and other advanced and productive capabilities.
During the workshop session, several seatworks, which have to be completed and submitted using the MS Excel version 2010/2016/Excel Office 365, will be administered, to enhance the skills of the participants.
Who Should Attend
- Anyone who is serious about learning Excel for finance
- Everyone involve with Finance function and operations
- 3 days, 3 hours each day
- Participants must have been using Microsoft Excel 2010 and above four at least one-year continuous usage.
The following are the subjects to be taken up during the workshop proper
- Foundation for Advanced Techniques
- About the MS Excel Cheat Sheet
- Customizing MS Excel Ribbon and Quick Access Tool Bar
- The MS Excel Date and Time Feature
- Advanced Report Generation Techniques
- Automating and Simplifying Report Updates the Easy Way
- Data entry of Subsidiary data (includes Sales, Cash Receipts, Inventory, Purchases, Cash Disbursements, Chart of Accounts)
- Introducing data visualization using data and date slicers
- Applying built-in financial, statistical, database date and time functions
- Creating actionable insights in Excel Dashboards and Data Visualization
- Formula Audit and Tracing
- Displaying all formulas within a worksheet
- Generating the following financial reports:
- General Ledger statement
- Income statement
- Balance sheet
- Financial Ratios (profitability ratio, Debt-to-Equity ratio)