Microsoft Excel Formulas and Functions for Accounting ...

To enhance members' knowledge and skills in contemporary application software, ACCA Hong Kong will be organising a series of IT training courses which will be delivered by experienced tutors of Infocan Training Limited. Details of the courses are as follows:

Microsoft Excel Formulas and Functions for Accounting & Financial Professionals (ACCA19B01)

KEY TOPICS

Date and Time Functions

SUMIF, SUMPRODUCT, ROUND, ROUNDUP,

TODAY, NOW, DATE, TIME, YEAR, MONTH, DAY,

ROUNDDOWN

HOUR, MINUTE, SECOND

Statistical Functions

Database Functions

AVERAGEA, COUNTA, MODE, MEDIAN

DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, Text Functions

DPRODUCT, DSUM Information Functions

EXACT, LEFT, RIGHT,MID, LEN, SUBSTITUTE, TEXT, VALUE, TRIM, UPPER, LOWER, PROPER,

COUNTBLANK, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, ISTEXT

CONCATENATE, DOLLAR Accounting Functions

Logical Functions

SLN, DDB, SYD, VDB

IF, AND, OR, NOT, TRUE, FALSE

Financial Functions and Formulas

Lookup Functions

PMT, IPMT, PPMT, PV, FV

HLOOKUP, VLOOKUP, LOOKUP, MATCH

Introduction to financial formulas

Math and Trigonometry Functions ABS, COUNTIF, INT, MOD, RAND, PRODUCT

Uses of financial functions and formulas Array Formulas

Introduction to arrays

Uses of array formulas

PREREQUISITE: A working knowledge of using PCs in the Microsoft Windows environment is required, and a familiarity

with the Microsoft Excel interface would also be an advantage

SOFTWARE VERSION: Office 2016

COURSE DURATION: 9 Hours (3 sessions ? 3 hours per session)

Microsoft Excel Advanced Formulas and Functions for Accounting and Financial Professionals (ACCA19B02)

KEY TOPICS

Reference Functions

Summarise data by combining use of functions

INDIRECT, OFFSET, INDEX, MATCH

Combo formula technique

Information Functions

Use functions to retrieve metadata for collaboration

CELL, ISNA Database Functions Review and More

Attractive Dashboard Presentation Visualise performance data by Dashboard

DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, Meter indicator

DMIN, DGET

Arrow indicator

Incorporate INDIRECT / OFFSET / DGET with other Colour bar indicator

database and logical functions

More Complicated Text Extraction with Functions

Creating Framework for Data Manipulation from

FIND, SEARCH

Multiple Sources

Creating an Aging Report with Date Functions

Enhance the usage of advanced filter with formula DATEDIF, NETWORKDAYS, DAYS360

criteria

Table Referencing Sample on Exchange Rate

Prevent inaccurate result by utilising sets of

Get data from Internet through Web Query

information function

PREREQUISITE: Attended the "Excel Formulas and Functions for Accounting and Financial Professionals" course

SOFTWARE VERSION: Office 2016

COURSE DURATION: 9 Hours (3 sessions ? 3 hours per session)

Presenting Business Data using Microsoft Excel (ACCA19B03)

Summarizing Data Effectively Chart handling mechanism Chart components Simple chart creation Work with charts

Choose the Best Chart for Your Presentation Understand charting purpose Choose from different chart types Combine chart types Create customized chart template

Capturing Attention to Data Create data series for charting Add, delete, modify chart series Series naming Plotting order Display partial data only Source data from multiple worksheets Handle missing data Hidden data series Convert data series Multiple axis

Creating Vivid Charts with Charts Object Overview Fills and Borders Background and Titles

KEY TOPICS Series and Legend Axes and Gridlines Data Labels and Data Table Eye-catching 3-D features

Forecasting with Trend Lines and Error Bars Understand trend lines Linear trend lines Non-linear trend lines Move average trend lines Show tolerance on error bar Trend lines enhancement

Shaping Up Your Charts Auto shape Smart arts Word arts Other graphic types

Distinctive Showcases of Conditional Formatting Key Performance Indicators (KPIs) Signal on Strength and Weakness Traffic Light Arrow

Inserting Excel Charts into PowerPoint Embedded chart Linked chart

PREREQUISITE: General knowledge of Windows and Excel 2016

SOFTWARE VERSION: Office 2016

COURSE DURATION: 6 Hours (2 sessions ? 3 hours per session)

Developing Business Applications Using Microsoft Excel Macros and VBA (ACCA19B04)

KEY TOPICS

Introduction to Excel Macros

Excel Workbooks and Worksheets

What is a macro?

Work with common worksheet tasks / workbook tasks

Record a macro

VBA Codes

Run a macro

Usage of variables and different data types

Relative references

User Interaction, MsgBox and InputBox

Assign a macro to a button on a worksheet

Conditions such as If...Then...Else

Assign a macro to a button on the Quick Access

Logical Select Case

Toolbar

Looping such as Do Loop

Editing Excel Macros

Structure of Procedures

The Visual Basic editor

Call procedures

The project explorer

Organise procedures

The code window

Controls, Dialogue Boxes and Forms

Procedures

Built-in dialogue boxes

Insert comments

Custom forms

Print Visual Basic module

Add form controls

Working with Objects

Control and dialogue box events

Introduction to objects and collections

Handle form controls

Control objects

Debugging and Error Trapping

Use the object browser

Use debug window

Use objects, properties and methods

Break mode

The Range Object

Instant watch

What is the range object?

Stepping through code

The cells property

Error handling

The range property

Flexible ranges

Range contents

PREREQUISITE: A thorough working knowledge of Microsoft Excel is required. No knowledge of Visual Basic is

needed

SOFTWARE VERSION: Office 2016

COURSE DURATION: 18 Hours (6 sessions ? 3 hours per session)

Effective Use of Microsoft Excel 2016 for Business Analysis (ACCA19B05)

KEY TOPICS

Preprocess Clean Up Your Data With Text Functions

PivotTable PivotTable Fundamentals

LEFT, RIGHT, MID

Introduce PivotTable

FIND, LEN TRIM UPPER, LOWER, PROPER

Understand How The PivotTable Summarize Data Create A Basic PivotTable

Clean Up Your Data Before Creating PivotTable

SUBSTITUTE

Insert A PivotTable

TEXT, VALUE Filtering

Use Advanced Filter To Screen Your Data Understand Advanced Filter Set Up Criteria Table Set Complicated Criteria For Filtering Use Formula To Help Filtering

Lookup and Reference Use VLOOKUP Formula To Reference Data Understand The VLOOKUP Formula VLOOKUP Reference Table Setting Error Handling On VLOOKUP Compensate VLOOKUP With Other Reference Functions Fix Source Data With Converting Data Between Text And Number

Dynamic Data Range Introduce Table Create And Use Table Quick Summary Using Total Row Remove Duplicate In Table Notation In Table When Writing Formula

Data Validation Ensure Data Correctness By Validation Different Type Of Data Validation Create A Drop Down List For Entry Provide An Input Message Change Alert Type For Allowing Exception

Data Consolidation Summarize Similar Structured Data

Understand Different Area In A PivotTable Add Fields Into PivotTable Extract Data For Each Summarized Entry Customize Fields In A PivotTable Change Summarizing Method On Data Field Change Number Format Of Data Field Create Running Total Or Other Representation Format Your PivotTable Report Change The Layout Of The PivotTable Apply Styles To The PivotTable Control The Way You View Your Pivot Data Hide And Show Grand Totals And Subtotals Perform Sorting And Rearranging Field Orders Apply Filter To Show Interested Items Apply Slicer To Quickly Selected Items Create New Grouping With Date And Numerical

Fields Manual Grouping Fields To Create Meaningful

Analysis Update Data In PivotTable

Modification In Data Source Insertion Of New Data Create And Use PivotChart Create PivotChart Format The PivotChart Filter With PivotChart Filter Pane Macro Record Macro For Daily Routine Macro Recording Run Macro

Consolidation By Position

Consolidation By Category

PREREQUISITE: Basic to intermediate knowledge of Excel.

SOFTWARE VERSION: Excel 2016

COURSE DURATION: 6 Hours (2 sessions ? 3 hours per session)

Level 1: Dashboard and Report by using Excel 2016 (ACCA19B06)

KEY TOPICS

Introduce Excel Dashboard and Report

Transpose Data Set

What are Dashboards and Reports?

PivotTable Driven and PowerView Dashboards

Dashboard Design Principles at a Glance

PivotTable Driven Dashboards Overview

Tabular Report Techniques

Introduce PivotTable

Table Design Best Practices

Customize PivotTable

Enhance Report with Custom Number and Conditional Filter Data on PivotTable

Formatting

PowerView Dashboard Overview

Use Symbols to enhance report

Animate Data on Map

Build dashboard with Camera tool

Automate Dashboard

Introduce Sparkline

Incorporate New Data Automatically

Customize Sparkline

Auto Refresh PivotTables

Chart on the Dashboard

Interactive Controls

Chart for Trending Techniques

Introduce Form Controls

Top and Bottom Ranking and Emphasizing

Check Boxes for Toggling chart series on/off

Relationship and Frequency Tracking Histograms

List Boxes for controlling multiple charts

Variance or Performance Chart

Option Buttons for showing views in on chart

Bullet Graphs

Buttons

Dynamic Labels and Text Formulas

Combo Box for Changing chart data

Data Model for Dashboard

Dynamic Named Range

Build Data Model

Dynamic Ranges for Charts

Data Model Best Practices

Dynamic Unique List

Excel Functions for Data Model

Dynamic Date Filters

Work with Excel Tables

Rollover Technique

Use Multiple Consolidation Ranges

Scroll and Sort Table

Internal Data Model Overview

Slicer Interactivity

Integrate External Data

Introduce Slicers

Power Query Overview

Create Timeline Slicers

Edit Queries

Slicers as Form Controls

Transform Dates

PREREQUISITE: General knowledge of Windows and Excel.

SOFTWARE VERSION: Office 2016

COURSE DURATION: 6 Hours (2 sessions ? 3 hours per session)

Level 2: Analyzing and Visualizing Big Data with Excel (ACCA19B07)

KEY TOPICS

Introducing Excel Big Data Big data concepts and their application in business

Excel Data Model Essentials Perform data analysis in Excel using pivot charts and slicer on given data worksheet Explore an Excel data model, its content, and its structure

Use Power Query Introduce get and transform the new Power Query Use the Query Editor to group or Combine Use Google Sheets as your source data Connecting to and using data from Facebook SQL Server data connections Get and transform multiple files in folder

Visualizing and Presenting Data Analytic result Create advanced DAX Use advanced text query to import data from a formatted Excel report Explore ways to create stunning visualizations in Excel Publish Excel data model and report to Power BI

PREREQUISITE: General knowledge of Windows and Excel.

SOFTWARE VERSION: Office 2016

COURSE DURATION: 6 Hours (2 sessions ? 3 hours per session)

Level 3: Analyzing Data with Power BI (Course Code: ACCA19B08)

*** Include a Digital Microsoft Official Courseware, valued at $1,000

KEY TOPICS

Introduction to Self-Service BI Solutions Introduction to business intelligence Introduction to data analysis

Using databases as a Power BI data source The Power BI service Shaping and Combining Data

Introduction to data visualization

Power BI desktop queries

Overview of self-service BI Considerations of self-service BI Microsoft tools for self-service BI Introducing Power BI Power BI The Power BI service

Shaping data Combining data Modelling Data Relationships DAX queries Calculations and measures

Power BI mobile apps Power BI Data

Interactive Data Visualizations Creating Power BI reports

Using Excel as a Power BI data source

Managing a Power BI solution

PREREQUISITE: General knowledge of Windows and Excel.

SOFTWARE VERSION: Office 2016

COURSE DURATION: 12 Hours (4 sessions ? 3 hours per session)

Introduction of IT Audit to Accountants (Course Code: ACCA19B09)

KEY TOPICS

Overview of IT general controls

Components of IT general controls (ITGCs)

What are IT audit and IT general controls?

Systems development

How does IT audit interface with a financial statement Computer operations

audit?

Program change control

Understanding the IT environment Introduce the IT components of a company

Access control to program and data Overview of IT system configurations

IT systems and data related to accounting

Identify the differences between ITGCs and

configurations Highlights of risks on configurations

PREREQUISITE: NIL

SOFTWARE VERSION: NIL

COURSE DURATION: 6 Hours (2 sessions ? 3 hours per session)

Collaboration in Word, Excel and PowerPoint (Course Code: ACCA19B10)

KEY TOPICS

Word 2016

Create, manage and collaborate

Documents for review

Review mark-up and comments

Merge changes into document

Broadcast a slide show to a remote audience

Insert, View and Edit Comments Track changes

Presentation into a video Integration between MS Office Applications

Document in email

Import or export text files

Excel 2016

Import content from other applications in MS

Password protect workbook

Office

Lock worksheet

Insert a worksheet

Shared workbook

Linked Excel chart table

Merge workbook

Reuse slides from another presentation file

Track changes PowerPoint 2016

Insert outline Share contact data between Excel and Outlook

Save a document to OneDrive

PREREQUISITE: General knowledge of Windows and Office.

SOFTWARE VERSION: Microsoft Office 2016 COURSE DURATION: 6 Hours (2 sessions ? 3 hours per session)

Time Management for Microsoft Outlook (Course Code: ACCA19B11) [ New Course ]

KEY TOPICS

Time Management Concept in your daily work

Archive Outlook Data

Task Importance

About Archiving Data

Organize your Task

Enable And Disable AutoArchive

Before Sending Messages Use Spell Check

Run The Manual Archive One-Click for Group of People

Set Importance

Personal Distribution Lists

Delay Message Deliver Recall A Message Add An Attachment To An E-Mail Message Open Or Save An E-Mail Message Attachment Create And Use Signatures Mail-Tips

Share and Organize You and Your Team Calendar Navigate The Calendar Group Scheduling Meeting Requests And Responses Calendar Preview In Meeting Requests Share And Allow Other Users To View Your

Review Your Massage In a Smart Way

Calendar

Managing Mailbox Size View By Conversation Read Receipts And Tracking Manage Views Find Messages Flag Messages And Setup Reminder Use Categories

From E-mail to Task To-Do Bar And The Tasks Screen Create And Delete A Task Assign A Task To Someone Else

Access Anywhere About Outlook Web Access (OWA) Access OWA

Manage Folders

Manage Mail Rules

Share And Allow Other Users To View Your Mail

PREREQUISITE: General knowledge of Windows and Office.

SOFTWARE VERSION: Microsoft Office 2016 COURSE DURATION: 6 Hours (2 sessions ? 3 hours per session)

Creating E-forms by using Adobe Acrobat (Course Code: ACCA19B12) [ New Course ]

KEY TOPICS

Introducing Acrobat What is Portable Document Format (PDF)? What is Acrobat Reader? What is PDF Writer? Difference between Acrobat Reader and Acrobat

Understanding the Working Area Use Palettes and the Navigation Pane Magnify and reduce the view

Converting A Document To PDF Convert MS Office Application files to PDF files Convert web pages to PDF files

Navigating A PDF Document Use Bookmarks Use Hyperlinks Use Thumbnails Use Navigation Buttons

Creating Comment in a PDF Document Use the Comments Palette Use the Comments Tools Use the Text Make Up Tools Use the Graphics Make Up Tools

Creating a PDF Form by using IRC3111A(10/2017) Getting started Converting PDF files to interactive PDF forms Assigning form field properties Adding form fields Distributing forms Collecting from data Share And Allow Other Users To View Your Mail Working with form data Managing fields with tab sequence Control error message

Advanced function for Adobe forms

Adding validations by simple script Conditional forms fields Confine data input

Working Within a Web Browser

Review and add comments within a web browser

Add digital signatures within a web browser Extracting Text and Images from PDF to Other

Document

Save PDF documents as RTF or images Extract images from PDF documents Protecting Shared Documents

Restrict printing and changing Add password protection Set Security Controls Searching Content in PDF

Checking document properties Accessibility W3C checking

Concept of Accessibility Enable Action Wizard Configuration checking Review comment

PREREQUISITE: General knowledge of Windows and Office.

SOFTWARE VERSION: Adobe Acrobat

COURSE DURATION: 6 Hours (2 sessions ? 3 hours per session)

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download