Core Data Analytics: Excel and Power BI Skills for Accounting and Finance Professionals
Data Analytics Course Description
It’s no surprise that technology and the big data it produces have become ubiquitous in day-to-day operations of most large businesses. Data scientists, data analysts, and non-IT professionals with data analytics knowledge have become critical in their organization’s quest to transform raw data into workable formats, which can be used to identify trends and draw meaningful conclusions.
Accounting and finance professionals can both set themselves apart from their colleagues and future-proof their careers by developing an expertise in data analytics. Professionals both in public accounting and in industry can use these skills to make sense of the massive quantities of financial information available to evaluate business performance, identify and manage risk, and analyze customer behavior to anticipate market trends more efficiently and accurately than ever before.
In this ten-part series, we’ll concentrate on the Power BI tools in Microsoft Excel® that enable accounting, auditing, and finance professionals to develop their own self-service data analysis.
Who Should Take This Course?
Auditors: Staff Auditors, Senior Auditors, Internal Auditors, Government Auditors
Accountants: Staff Accountants, Senior Accountants, Supervisors, Managers, Management Accountants
Analysts: Business Analysts, Financial Analysts
Full certificate Course
$299
What's Included
10 hours of Instruction
Level: Intermediate
100% Online
and On-Demand
and On-Demand
Mobile-Friendly
Video Lectures
Video Lectures
Earn a Professional
Certificate
Certificate
Top-Rated
Instructors
Instructors
Prerequisites
2+ years of experience working with Excel
Advance Preparation
Install Power Pivot and Data Analysis ToolPak
Resume Skills
On-Demand Modules Featured in this Course
Module 1: Data Extraction and Data Sources
Course Description
This 1-hour course serves as the introductory module to data analytics. Users of the course will learn the basic terminology used in performing data analytic-based tasks or working with data analytics. An understanding of the sources of data and importance in combining multiples sources is provided. Users will be introduced to and will practice the basics of Microsoft’s Power Query.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Understand the basic terminology, applications, and uses of data analytics
- Understand the relationships that exist between different sources of data
- Demonstrate the ability to perform basic Power Query functions
- Demonstrate the ability to import Excel workbooks into Power Query
- Demonstrate the ability to connect to external data sources using Power Query
Module 2: Data Preparation Using Get and Transform
Course Description
In this one-hour course users will learn to apply Power Query to extract, transform, and load data for analysis. Users will learn how to manipulate data once it is loaded into the platform and will practice the manipulation during the course. Using the same data as the instructor, users will perform an exercise to clean and transform a basic general ledger with an emphasis on preparing the data for analysis.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Apply Power Query to extract, transform, and load (ETL) data
- Understand and apply the basics of data cleaning
- Understand what is M Code and how it is used
- Apply ETL processes to a dataset
Module 3: Formulas, Named Ranges, and Formula Accounting
Course Description
This one-hour source will introduce users to table functions in Excel and Power BI. Users will demonstrate an ability to apply and utilize named ranges and naming conventions to data. Specific focus is given to common formulas and functions within Excel. Common pitfalls in formula implementation are discussed. Users will utilize exercises alongside an instructor to practice formula and function skills.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Apply formulas to existing data and debug errors in formulas
- Understand and apply the basics of data cleaning
- Understand the importance of naming conventions in Excel tables
- Under the basic formula language and implementation guidance and be able to apply that guidance to common formulas used in data analytics
Module 4: Excel Tables
Course Description
In this one-hour course users will apply Excel and Power BI tools and skills directly to tables in Excel. Users will work alongside the instructor to create named tables, structure references, enter formulas, and prepare the tables for input into Power Query. Users will be introduced to the preparation of first level of statistics, descriptive statistics. Emphasis will be placed on mean, median, and standard errors.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Apply skills learned to create and structure tables
- Determine the appropriate descriptive statistics to present and present them in a structured format
- Create structured references and understand their relation to formula creation
- Create formulas and apply troubleshooting techniques to formulas created
Module 5: PivotTables
Course Description
This one-hour course provides user with hands-on practice using PivotTables in Excel. Users will learn and apply skills to create, structure, and analyze data using PivotTables. The benefits and limitations of PivotTables are discussed. Power Pivot is introduced and users will be introduced to Online Analytics Processing within Power Pivot.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Understand the uses, benefits, and limitations of PivotTables in Excel
- Apply Excel skills to create, structure, and operate PivotTables using predefined data
- Be able to perform sorting, filtering, and calculations within PivotTables
- Understand Online Analytical Processing and when it used in Power Pivot
Module 6: Data Modeling
Course Description
This one-hour course focuses primarily on Power Query, Power Pivot, and V-LOOKUP skills. Users will learn how to join tables using various techniques and add-ons in Excel. Users will understand how to utilize the tools while maintaining data integrity. The majority of the course focuses on hands-on data exercises. Users will apply skills to create relationships and analyze the data using Power Query.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Understand types of data relationships that exist within the data (e.g., linear, normal, etc.)
- Be able to apply VLOOKUP and index match formulas in Excel
- Be able to append and merge multiple data sets into one
- Create relationships in Power Pivot
Module 7: Calculated Columns and Dimensions
Course Description
This one-hour course will focus on the use of Excel with Power BI and Power Query to organize and analyze data. Users will primarily work with calculated columns generated from existing data. They will walkthrough hands-on exercises to create calculated columns, add dimensions to existing data tables, and create hierarchies within the data tables. Special attention will be paid to Star Schema analytical modelling. To explore practical applications, users will apply the skills to journal entry testing.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Understand evaluation context-row and filter context
- Understand the requirements to create calculated columns and apply that understanding to data
- Be able to create a dimension table in Power Query
- Be able to create hierarchies in Excel with calculated columns
- Apply calculated column skills to dataset related to journal entry testing.
Module 8: Advanced Formulas – Array Formulas and DAX Formulas
Course Description
This one-hour data analytics course will introduce formula arrays and DAX formulas to users in Power BI and Excel. Users will learn the skills to create and evaluate array formulas and DAX formulas. An understanding of implicit for explicit intersections and measures within the formulas will be discussed. Users will apply the understanding to data and work alongside the instructor to create calculated columns in Power Pivot using DAX formulas and array formulas.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Understand the basic requirements to create array and DAX formulas using Power BI and Excel
- Understand how DAX formulas relate to calculated columns and how they can be created within calculated columns
- Apply understanding of DAX formulas to create calculated columns using Power Pivot
- Be able to create hierarchies in Excel with calculated columns
Module 9: Artificial Intelligence – Basic Concepts in Excel
Course Description
This one-hour course serves as an introduction to artificial intelligence and how artificial intelligence is utilized in Excel. Users will be introduced to correlation and trend functions in Excel. The basics of linear regression and its application in Excel will be discussed. Specific attention will be given to artificial intelligence functions in Excel, including, but not limited to, forecasting, trend analysis, regression analysis, and predictive modeling.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Understand what is artificial intelligence and how it relates to data analytics.
- Understand the basics of performing linear regression and apply that understanding to data
- Be able to accurately model data and create a prediction model
- Be able to apply trend analysis and forecasting functions to data in Excel
Module 10: Visualizations and Reporting
Course Description
This one-hour course serves as an introduction to data visualization and reporting using Power BI and Excel. Users will understand the benefits of using Power BI to create data visualizations and how Power BI differs from previous functions in Excel for visualizations. A hands-on exercise will be applied where the user will work alongside the instructor to create dashboarding for journal entry testing. Specific focus will be paid to the relation among various tools for data analytics: Power Query, Power Pivot, PivotTables, DAX formulas, and storyboarding.
Learning Objectives
- Understand the applicable Excel skills that will be required in making the transition to data analyst
- Understand what data visualization tools are available in Excel and Power BI and how they are superior to previous Excel tools.
- Apply skills learned to create a dashboard from journal entry testing data
- Apply skills to present the output of analytical modeling in an easy to interpret form and evaluate the output of the analytics
- Be able to use the camera tool in Power BI to create dashboards
Full certificate Course
All On-Demand Modules Listed
$299
Virginia Collins, CPA, CFE, CITP
Meet The Instructor
Virginia Collins is a former Manager in the Quality Control Department of Berdon LLP. With more than 35 years of experience in public accounting, she has advised clients across many industries. Leveraging broad experience in auditing, accounting, and internal controls, she has expertise in information technology including data analytics, Excel, and active data. Ms. Collins is a Certified Informational Technology Professional (CITP), which is a specialty designation awarded by the AICPA to CPAs with the unique ability to bridge business and technology. She was appointed a Committee Member of the AICPA Credential Committee where she has been actively involved in increasing the use of Computer Assisted Auditing Techniques (CAATs) by CPAs. Her experience extends to planning of engagements — applying CAATs techniques and training staff on their use, internal control I.T. review, quality control review of engagements, data analytics, continuous auditing and business intelligence, and advising on the requirements of Section 404 of the Sarbanes-Oxley Act.
Find out more about our course
Try Some of Our Classes for Free
What’s the Benefit of a Professional Certificate?
Only Surgent has the 35+ year proven track record of educating professionals of all experience levels with an unmatched industry expertise and best-in-class learning technology to ensure you’re equipped with the skills you need for career success.
Surgent IQ’s professional certificate program explores topics in much greater detail than you’d typically find anywhere else, like in a classroom setting, and are meant to teach very specific, practical concepts that can be immediately applied in your day-to-day. And because our courses are designed to address the needs of specific job functions, you can be sure you’re choosing the exact course your career will benefit most from.
By earning a professional certification, you demonstrate to current employers and future hiring managers that you’ve got just the right skills to bring value to their organization.
Here’s how else you’ll benefit:
Future-proof your career
The accounting and finance industries may be going through a period of flux, but by acquiring the forward-looking skills that drive innovation for businesses, you can ensure you’re able to succeed in today’s and tomorrow’s climate.
Make the switch you’ve been thinking about
Adding a professional certification to your resume is a surefire way to demonstrate to hiring managers that you’ve got the skillset they’re looking for.
Take the next step in your profession
Build marketable expertise in the most in-demand areas of your industry, and take your career to the next level.