Excel For Accounting and Finance 

MICROSOFT EXCEL FEATURES CAN FAST TRACK BUSINESS PROCESSING

January 31, 2024

Program overview

Accounting and finance management is immensely important in navigating a business. If you are in accounting or finance profession, you know how much analysis you need to do to understand each element or aspect of it. Excel for Accounting and Finance masterclass will not only let the participants master MS Excel, but also learn to use the material in improving their career.

Objective

1. Develop a “toolkit” of Excel tips and techniques
2. Sensitize and stress test Excel outputs from models
3. Problem solve within an Excel environment
4. Analyze large volumes of data
5. Rank data and analyze large data volumes
6. Data mine large data volumes and analyze
7. Confidently use Excel’s keyboard shortcuts to improve their speed and efficiency
8. Confidently develop Excel formulae in order to build robust solutions
9. Quickly and consistently format presentation-quality spreadsheets

 

Who Should Participate

Running the show in accounting or finance for a long time (or maybe for a few years), Accounting and Finance professionals know what role MS Excel plays in getting things right. Participating in this workshop may act as a refresher and participants may also learn a thing or two about the spreadsheet management.

 

Key Topics / Topic Outline

Icebreaker/Energizer
Expectation Setting: Course, Trainer/Facilitator, Co-Participants, Self

 

Pre-Assessment

Setting The Goal

 

Organizational Situationer

1. Vision, Mission, Goals
2. Values
3. Needs Assessment

 

Internal Mission Statement

1. Evaluation
2. Idealization (Where do we want to go?)

 

Module 1: Get Quicker, Faster

1. Setting up your profile
2. Building your own quick access toolbar
3. Calculation options
4. Keyboard shortcuts (to make you 8 times faster)
5. Navigation
6. Highlight and moving data
7. Spreadsheet manipulation
8. Print set up for perfect printing
9. Exercise: a keyboard shortcut exercise fixing errors on a spreadsheet without use of a mouse

 

Module 2: Efficient Presentation Protocols

1. Inputs vs. formulae
2. PDF document tricks for efficient data extraction into Excel
3. Customizing numbers (percentages, multiples, dates, thousands, millions, etc)
4. Styles – the quick way to format and create formats that can be used again and again and again
5. Building and developing a style template
6. Merger styles between models
7. Exercise: a format exercise with data

 

Module 3: Building Flexible Formulas in Excel

1. Relative vs. Absolute referencing
2. Using names to increase formula efficiency
3. Creating cell and range names
4. Applying and redefining names
5. Naming conventions
6. Exercise: code a revenue matrix using relative and absolute formulae
7. Logical statements
8. IF, AND, OR functions
9. Advanced logicals with flags
10. Formula layout to simplify
11. Snapping formulae (Alt-Enter)
12. Data management and analysis within Excel
13. Data sorting and filtering
14. MAX, MIN, AVERAGE
15. SUMIF, COUNTIF
16. “Wild-card” SUMIF, COUNTIF
17. Data mining in Excel
18. HLOOKUP
19. VLOOKUP
20. CHOOSE
21. INDEX
22. INDEX (MATCH)
23. OFFSET
24. Exercise: Excel skills will be developed that will allow attendees to analyze, rank and disaggregate large volumes of data
25. Working with text and dates in Excel
26. Text strings (dynamic text)
27. TEXT functions
28. RIGHT
29. LEFT
30. LEN
31. EDATE
32. EOMONTH
33. Building diagnostics into your model to monitor and sanity checks outputs
34. Working with circularity in Excel
35. Conditional formatting – uses and dangers
36. Auditing tools

 

Module 4: Building Flexible Charts in Excel

1. Chart set ups
2. Chart formatting
3. Dynamic labels
4. Building bridge charts

 

Module 5: Spreadsheet Debugging Skills

1. Building diagnostics into models
2. Auditing skills
3. Watch windows
4. Using the camera
5. Link elimination
6. Circularity issues
7. Unnecessary macro creation
8. F5 functionality
9. Exercise: using Excel skills attendees will fix and eliminate Excel errors and present the spreadsheet in a professional format

 

Module 6: Analyzing the Output

1. Sensitivity analysis in Excel
2. Techniques to minimize memory usage
3. Self centering data tables
4. Exercise: building data tables that sensitizes outputs and includes built in diagnostics to ensure that the data remains up-to-date
5. Pivot tables
6. Paste linking to Power Point
7. Illustration case study: formatting, analyzing and evaluating large data sets, bringing together all of the skills covered over the two days

 

Setting The Goal

1. Organizational Situationer
2. Idealization (Where do we want to go?)

 

Post-Assessment

Interactive Session/Workshop
CLOSING
Open Forum | Question & Answer

 

Evaluation

Instructional Methods / Methodology: The course will be delivered through lecture and discussion using Microsoft Powerpoint and whiteboard. Spot recitations and group activities will be made throughout the duration of the seminar

Featured Speaker:

JEFF-MIKE SMITH SULE CPA, CIA

Principal Auditor
American Data Exchange Company

Jeff-Mike Smith Sule is a national speaker on topics that involve external and internal audit, IT audit, real estate, and environmental planning, accounting and taxation, bookkeeping, business process management, and organizational transformation.

He graduated First Honorable Mention in secondary school and Magna Cum Laude in PUP-Manila with a bachelor’s degree in accountancy.

He worked as an IT and later on Financial Auditor at SGV and Co. Concurrent and after his stint at SGV, he has held various management position and become faculty member of various educational institutions such as Ateneo de Manila University, De La Salle University, Mapua, Far Eastern University, PCU and Polytechnic University of the Philippines. He was also an accreditor of the Association of Local Colleges and Universities

He also worked for ÆON Credit Service, under various management capacities: Internal Control Manager, Portfolio Manager and Internal Audit Manager. He is currently connected with American Data Exchange Company as a principal auditor while doing freelance consultancy engagements.

He is a Fellow of the Royal Institute of Accountants based in Singapore, a Certified Internal Auditor, a Scrum Fundamentals Certified, a Certified Bookkeeper under The Institute of Certified Bookkeepers based in the United Kingdom, holder of NCIII (National Certification in bookkeeping) under TESDA, and an Accredited Accounting Teacher and Accredited CPA in Commerce and Industry Practice. He has four active professional licenses under PRC, to wit, Certified Public Accountant, Real Estate Appraiser, Real Estate Broker, and Environmental Planner. He placed Top 7 and Top 2 in the 2010 CPA Licensure Examination and 2014 Real Estate Appraiser Licensure Examination, respectively.