Advanced Level Formulas for Accountants

by Paula Guilfoyle
access120 days access
cpd hours4 CPD hours

This course will enable you to:

  • Get to grips with the more complex formulas in excel
  • Use named ranges to quickly navigate a workbook
  • Read complex formulas with ease
  • Calculate future value, carry out one and two variable analysis, use reference functions, and create dynamic ranges and user defined functions
  • Carry out quick calculations and analysis on your data
  • Understand functions such as PMT, Goal Seek, Scenario Manager, OFFSET and INDIRECT

Excel is an extremely powerful tool for accountants but are you using it to its full potential? Here we look at how to use Excel at an advanced level and the benefits that can bring.

This course will help you develop the skills you need to carry out quick calculations, whether you're trying to calculate loan repayments, or carry out what if analysis on your data. You'll learn how to calculate future value, carry out one and two variable analysis, use reference functions, and create dynamic ranges and user defined functions.

Financial Functions in Excel

  • How do I calculate future value in Excel?
  • How do I calculate effective interest rate?
  • How do I calculate present and net present values?
  • How do borrowers use the PMT function?
  • How do I use the PMT function from the lenders view?

Analysis for Accountants

  • How can I carry out a one variable analysis in Excel?
  • How can I carry out a two variable analysis in Excel?
  • What is Scenario Manage, Goal Seek and Solver?
  • How do I use Scenario Manager?
  • How do I use Goal Seek?
  • How do I use Solver?

Reference Functions

  • What are reference functions and how do they benefit Accountants?
  • What is the OFFSET function?
  • How do I use OFFSET?
  • What is the INDIRECT Function?
  • How do I use INDIRECT and overcome errors?
  • How do I use Rows and Columns?
  • What is CHOOSE function?

Named Ranges and User Defined Functions

  • What are named ranges and what are the benefits of use for Accountants?
  • How do I set up Named Ranges?
  • How do I find and edit and use named ranges?
  • How do I set up a dynamic named range?
  • What Accountants should know about User Defined Functions?
  • How to create a UDF?
Paula Guilfoyle is a member of the Institute of Certified Public Accountants in Ireland. She practiced in Industry for over 15 years, working with both small and large private companies, before moving on to teach Excel and Power BI to accountants and professionals. Paula has given many talks and presentations in the areas of Excel, spreadsheet use, PowerBI and business intelligence, and has published many articles on the same subjects.
ACCA logo

ACCA partner with to provide high quality CPD for members. As an ACCA member, you are required to complete at least 40 relevant units of CPD each year, where one unit is equal to one hour. 21 units must be verifiable; the other 19 can be non-verifiable.

Verifiable CPD
Your course counts as verifiable CPD, if you can answer "yes" to these questions:

  1. Was the learning activity relevant to your career?
  2. Can you explain how you will apply the learning in the workplace?

You select courses that meet these criteria, and as you complete each course you get a CPD certificate so you can provide ACCA with the evidence that you undertook the learning activity.

    You need to sign in or register before you can add a contribution.