Advanced Excel Formulas for Accountants

by Paula Guilfoyle
access120 days access
cpd hours4 CPD hours

This course will enable you to:

  • Employ Excel’s suite of financial functions to calculate loan repayment and investment returns
  • Use functions such as Goal Seek and Scenario Manager, to analyse data and solve problems including both one and two variable analysis
  • Use OFFSET, INDIRECT and other Lookup and Reference functions to reference data from other worksheets, create dynamic functions and carry out lookups
  • Use named ranges and user defined functions to make navigation easier and formulas simper to read

Excel is an extremely powerful tool for accountants but are you using it to its full potential? Take your Excel skills to the next level, by mastering more advanced techniques, and understanding how they can be applied in your finance role.

This course will help you develop the skills you need to carry out complex calculations, like calculating loan repayments, and what-if analysis on your data. You'll learn how to calculate future value, use one and two variable analysis and make the most of reference functions. You'll also discover how using named ranges and user defined functions can make you more efficient in your work.

Financial functions

  • Future value
  • FV function
  • Effective interest rate
  • EFFECT function
  • Present and net present values
  • NPV function
  • Borrowers and the PMT function
  • Lenders and the PMT function

Analysis for accountants

  • One variable analysis
  • Two variable analysis
  • What are Scenario Manager, Goal Seek and Solver?
  • Scenario Manager
  • Goal Seek
  • Calculating profit
  • Activating Solver
  • Solver

Reference functions

  • The benefits of reference functions
  • OFFSET function
  • INDIRECT Function
  • Using INDIRECT to overcome errors
  • CHOOSE function

Named ranges and user defined functions

  • Benefits of named ranges
  • Setting up named ranges
  • Find and edit named ranges
  • Setting up a dynamic named range
  • Dynamic named ranges
  • User Defined Functions
  • Creating a UDF
  • LET function
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.