Software tools enable us to be more productive and efficient. Learn how to leverage the powerful features of Excel for accounting, auditing and other finance tasks. Explore the use of Excel as an audit and analysis tool, as well as, selected features that help prepare accurate reports. Explore how certain fraud analysis may be accomplished with Excel, including the implementation of Benford's law.
This in-depth two-day course introduces Microsoft Excel users to the advanced features of the software applications. Participants would be able to utilize the use of Excel for accounting purposes such as using Lists in Excel, working with Pivot Tables, Common Sizing using worksheets and working with Charts. In-class exercises will involve use of Excel tools for accountants, Scenarios, Payment and Depreciations functions. After completing this course, students would be able to make the most of Excel in line with accounting tasks.
The course does not use Excel VBA and is totally non-technical.
The Foundations & Concept of Finance Data in Excel
Text – the problem of Spaces and cases.
Dates – the problem of “looks like date but does not behave like date”
Numbers beyond 15 digits
Data cleaning and extraction with Functions.
Handling extraction of “Month” and “Year” from a date as:
Finance Data in “Excel TABLES”
Why copy formulas in the column after you have typed one?
Why expand “range” after addition of new row or column to data.
Use of Excel Tables – end of Excel “range” way.
Understand the benefits of using an Excel table over a range of data
Convert a data range into an Excel table
Review of the Excel table contextual tab
Quickly format a table with pre-defined table styles
Filter data within a table
Quickly add columns and rows into an existing table
Add a Total row to a table for quick calculations
Automatically add a function to all rows within a table
Learn the benefits of using an Excel table to create a PivotTable
Why name a Table with “tbl”?
The Finance Data SOURCE
Manually enter data
All in ONE warehouse.
Function: NOT for Reports but for Data
Named Ranges – backbone of Financial Reports
Define Name – Cell
Define Name – Range
Define Name – Constant
Range Naming Rules
Creating and using Range names
Defining names for ranges
Using a named range as a reference in a formula
The Create from Selection command
The Apply Names command
The Name Manager
Using the Name Manager to change and delete names
The Documents that have Finance Data
The Finance Data Heirarchy
Lists – Charter of Accounts
Revenue and Cost Reports and Data Analysis with PivotTables
The Four Critical Pivot Table Options
Format Numbers (Not Cells)
Report format: Style and Design Components to Format a Report
The Report: Sum, Count
The Report: Concept Of Data Cubes
The Problem Of Pivot Report not taking New Data
Commin Sizing Accounting Statements - Show Amount as Of Total
Automatically Getting NEXT month’s Reports with Slicers
Slicers: One Pivot - Multiple Reports
Concept of Horizontal Slicers and Vertical Slicers
Super glue your slicers so that even Excel can not move them
Controlling Multiple Pivot Tables / Pivot Charts with one Slicer
The concept of “Connections”
Display Financial Reports in THOUSANDS and MILLIONS - Advanced Number Formatting
Using special number formats
Built-in and custom formats
Custom number formats
The IF function
Editing conditions in a formula
Creating nested functions
The OR, AND functions
Nested IF functions
The IFERROR function
11. Lookup Functions
Using lookup functions
Using VLOOKUP to find exact matches
Using VLOOKUP to find approximate matches
Is it a Date?
Text Functions to clean data
=CONCATENATE() (or “&”)
Text to Columns to harmonize data
Handling Ledger Codes – NUMBER Vs TEXT
Converting “1” to “0001”
Converting 305 as NUMBER to 305 as TEXT.
Calculate Qtr, HY, FY
With NESTED IF MONTH, YEAR formula
With VLOOKUP Formula
Custom Sorting of Finance Data
“CEO” cost center to come before “ADMIN” Cost Center, followed by “SALES” Cost Center.
UAE to show at top followed by Oman, then Bahrain, and finally Saudi Arabia
Using Consistent IF Formula
Using Pivot Tables
Protecting a workbook
Protecting parts of a worksheet
Applying password protection to a worksheet
Conditional formatting with graphics
Conditional formatting rules – Highlight Top/Bottom Rules
Conditional Formatting with Formulas
Creating Interactive Financial Dashboards
Use of Filters inside Pivots.
Using Pivot Charts
Why keep formatting all Charts manually – let Excel do this work!
Multiple Charts being controlled by several Slicers – the inter-activity
Protecting your Dashboard