Excel for Finance & Accounts Professionals

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: 

A Text

A Number


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

Data sources:

Download data

Calculate Data

Look-up data

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

Invoice

Receipt

Expense

Income

JV


The Finance Data Heirarchy

Lists – Charter of Accounts

Transaction Details

Report

Trial Balance


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

Modifying Slicers

Deleting 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

Zero values

Custom number formats


Logical Functions

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

VLOOKUP

Using VLOOKUP to find exact matches

Using VLOOKUP to find approximate matches


Date Functions

Is it a Date?

=TODAY()

=YEAR()

=MONTH()

=DAY()

=DATEDIF()

=NETWORKDAYS.INTL()

=EDATE()


Text Functions to clean data

=SEARCH()

=SUBSTITUTE()

=LEN()

=LEFT()

=RIGHT()

=MID()

=TEXT()

=PROPER()

=TRIM()

=CONCATENATE() (or “&”)


Data Harmonization

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


Aging Analysis

Using Consistent IF Formula

Using Pivot Tables

Using VLOOKUP


Protection

Protecting a workbook

Protecting parts of a worksheet

Applying password protection to a worksheet

Hiding formulas


Conditional Formatting

Conditional formatting with graphics

Data bars

Icon sets

Conditional formatting rules – Highlight Top/Bottom Rules

Conditional Formatting with Formulas


Creating Interactive Financial Dashboards

Dashboard planning

Use of Filters inside Pivots.

Using Pivot Charts

Why keep formatting all Charts manually – let Excel do this work!

Using Slicers

Multiple Charts being controlled by several Slicers – the inter-activity

Protecting your Dashboard