Data Reconciliation Using Excel
COURSE INTRODUCTION
The use of Excel is widespread in the industry. It is a very powerful data analysis tool and almost all big and small businesses use Excel in their day to day functioning. This course is designed to give you a working knowledge of Excel with the aim of getting to use it for more advance topics in Business Statistics. The course is designed keeping in mind two kinds of learners - those who have very little functional knowledge of Excel and those who use Excel regularly and wish to enhance their skills. The course takes you from basic operations such as reading data into excel using various data formats, organizing and manipulating data, to various advanced functionality of Excel. All along, Excel functionality is introduced using easy to understand examples which are demonstrated in a way that learners can become comfortable in understanding and applying them.
Who Should Attend?
Business professionals, accountants, finance analysts, senior and junior accountants, business analysts, accounting and finance professionals, research professionals, marketing and sales, administrative staff, supervisors, general staff from any function who need to learn and apply state-of-the-art techniques to their daily business reporting, reconciliations, and analysis.
Pre-requisite: Intermediate-level Excel knowledge.
Target Competencies
Reporting, analyzing, and reconciling
Data modeling
Integration with external data sources
Report structuring techniques
Automation and macros
Charting and visualization techniques After completing this course, participants will be able to,
Use their Excel expertise in data slicing and dicing, data massaging, data aggregation, data integration with Access, web, text, SQL, and other databases using pivot tables
Perform advanced and dynamic data validations
Design outstanding visualization charts, dashboards, scorecards, and flash reports
Develop master-level report solutions using advanced form controls and buttons
Record, write and edit powerful macros that will perform routine tasks in no-time
Course Content
Day 1
Essential reporting requirement skills
o The 19 rules of pivot tables and pivot charts
o Slicer techniques
o Advanced pivot charts techniques
o Multiple consolidation ranges
o Retrieving external data using Microsoft query
o Importing text files using MS query
o Connecting to access databases
o Connecting to SQL databases
o Importing from data connection wizard
o Importing from Microsoft query
o Customizing connections properties
Day 2
Advanced data structuring techniques
o Custom and advanced data validation
o Creating and managing innovative conditional formatting
Day 3
Charting and visualization techniques
o Creating dynamic labels
o Using the camera tool
o Working with formula-driven visualizations
o Using fancy fonts
o Leveraging symbols in formulas
o Working with sparklines
o Creating unconventional style charts
o Fancy thermometer charts
o Colored chart bars
o New Excel 2013/2016 Charts
Day 4
Building report solutions
o Conceptualizing and understanding report solutions
o Developing a report solution
o Configuring spreadsheet report data options
o Enabling background refresh
o Refreshing data when opening the file
o Combo-box data modeling tool
o List-box data modeling tool
o Form controls data modeling tools
o Spinner
o Option-button modeling
o Check-box data models
o Combo and group-box
Day 5
Macro charged reporting
o Recording, editing, testing VBA macros
o Building a macro driven reconciliation program
o Building budget variance reporting program
o Building an email based report distribution program
o Building a macro based report printing system
o Building a vendor and invoice analysis report