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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- microsoft excel 2019 formulas and functions
- hp 12c financial calculator quick start guide
- 2 1 2 3 spreadsheets simple and compound interest and
- google spreadsheet round decimals
- haas service and operator manual archive
- section 5 1 compound interest texas a m university
- transaction advanced guide winshuttle
- microsoft excel formulas and functions for accounting
- a brief introduction of pmt ipmt and ppmt excel functions
- functioning excel time calculating spreadsheet
Related searches
- microsoft excel formulas pdf
- microsoft excel codes and formulas
- microsoft excel formulas examples
- list of excel formulas and functions
- microsoft excel formulas cheat sheet
- formulas and functions in ms excel
- complex excel formulas and functions
- advanced excel formulas and functions pdf
- free excel formulas and functions
- all excel formulas and functions
- list excel formulas and functions
- microsoft excel app free download for laptop