98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel for Finance Professionals
Face to face / Online closed & onsite training. Restaurant lunch included at STL venues.
- 1 day Instructor-led workshop
Syllabus
Who is this course for?
Excel users from finance-related roles looking to extend their knowledge of Excel in a financially driven context.
This course is a part of a wider portfolio of our Excel courses London.
Prerequisites
Attendance or equivalent knowledge of our Excel Intermediate course.
Benefits
Gain best practice techniques in pulling financial information into Excel, common pitfalls to avoid when using key financial functions and some useful presentation tips. The opportunity to use example financial statements such as cashflows and look at common size statements.You may also be interested in our VBA courses.
Course Syllabus
Setting up a Financial Worksheet
Layouts of input cells and formulas
Tracing formula precedents, dependents, and errors
Common mistakes to avoid
Conditional formatting to analyse data
Highlighting alternative row/columns of data
Highlight mistakes / errors / omissions / repetitions
Using conditional formatting to create simple & effective dashboards
Working with data
Importing data; formats, field labels,
web query options (stock info)
Use TYPE to identify data type of existing cell contents
Text functions; FIND, MID,
TRIM excess space in cells
Use lookup functions to retrieve and compare data
Cashflow spreadsheets
Income & Expenditure tracking table
Create a Pivot Table
Using the Pivot Table with filters to analyse cashflow
Using a Pivot Chart to track cashflow
Financial functions
Considerations such as:
- PMT, FV, IRR, NPV
- When to use XIRR and XNPV
- Function with cashflows
- Assumptions to avoid
- APR vs APY
Reviewing financial statements
Example balance sheets, income statements, cashflow
statements & shareholders equity
Benefits of using common size statements for comparisons
Setting up common size statements; balance sheets & income
Defining key ratios to compare and updating changes
Prices & Dates
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Online training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
24 months access to trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
Training formats available
|
Testimonials
Imperial War Museum
Jamie Carlin,
Project Officer
The course itself was fantastic, however I feel like there could be a potential for more modular courses for smaller groups if attendees were given the chance to input into what parts of the course they would like to focus on beforehand. Jens was excellent at taking the conversation in the appropriate directions, but I feel like if this was a part of the course introduction/structure I would have had more confidence in asking these kinds of questions.
Excel for Finance Professionals
Panaci Services Ltd
Gabriela Mackie,
Finance Professional
This is my third course with Excel. I went to a different course with a well known branded accounting practice , but I found STL superior in terms and manner of teaching . I recommend it as a high quality course . Thank you
Excel for Finance Professionals
1993
Joe Richards,
Financial Accountant
Really excellent three days, Jens' knowledge really is unbelievable and he is incredibly patient and generous with his time, I feel like I have learnt a huge amount to help me stand-out at the day job - would highly recommend.
Excel for Finance Professionals
Related courses: Finance for nonfinancial London.
Training manual sample
Below are some extracts from our Excel for Finance Professionals manual.
Unit 5: Financial Functions
In this unit, you will learn how to:
Calculate with financial functions
The most common use of Excel is to perform calculations involving money. Every day people make thousands of financial decisions based on figures in a spreadsheet. These decisions range from simple to complex. Can I afford to buy a new car in the next 18 months? Will a business result in a positive cash flow after 5 years?
PMT
The PMT (Payment Monthly Term) function returns the loan payment (principal plus interest) per period assuming constant payment amounts and a fixed interest rate.
Syntax
PMT(rate,nper,pv,[fv],[type])
For example, suppose you borrow £6,000 to buy a car and plan to pay it off on monthly instalments at an interest rate of 6.25% over 18 months. The PMT function calculates that the repayment to be made is £350.07 at the end of every month.
Note that Rate is the monthly interest rate so is divided by 12.
Nper is the number of monthly payments to be made.
Pv is the monthly payment amount and is entered as a negative because it is a payment.
Fv is a cash balance you may wish to attain after the last payment is made (assumed to be 0 if omitted).
Type determines when the monthly payment is to be paid (0 or omitted for end of month, 1 for beginning of month).
Note that £350.07 x 18 = £6,301.24 so the amount of interest to be paid in this example is £301.24.
FV
The FV or Future Value function calculates the future value of an investment based on constant payments and a constant interest rate.
Syntax
FV(rate,nper,pmt,[pv],[type])
For example, if you pay £250 into the bank every month at an annual interest rate of 6.5% for 2 years what will be the future value of your investment?
In C6 create the following FV function by selecting Formulas, Financial, FV
As before
Rate is the monthly interest rate
Nper is the number of months 24/12
Pmt is -£250 as it is a payment
PV is an optional lump-sum amount that is paid at the beginning of the investment. In this case it is 0.
Type is again for when the monthly payment is paid (End of the month if omitted). This function =FV(C5/12,24,-C4) returns £6,389.03 as the future value of the investment.
Now suppose you invest £2,000 as a lump-sum as well as making regular payments at the end of each month for 2 years.
Note again that the Pv and Pmt are entered as negative values because they are both payments.
This time the FV function returns £8,665.89 which now includes the initial lump-sum with interest as well as the monthly payments and their interest.
IRR
Another useful financial function for businesses is the Internal Rate of Return IRR. This function calculates the rate of return on a series of regular cash flow income (positive values) and payments (negative values).
Syntax
IRR(values, [guess])
In the example above the IRR is 9% for the income amounts shown and the initial cost payment of 70,000.
Values: the range of payment and income values.
Guess: is optional and set to 10% if omitted.
Note: A #NUM! error message appears if an answer could not be found after 20 cycle calculations. In that case enter a guess for the IRR %.
NPV
NPV is a useful modelling function. It allows you to calculate the Net Present Value - the key word is present, i.e. the current value of your cashflows.
Feed in your cashflows and rate to calculate the NPV.
Syntax
NPV(rate,value1, value2…)
NPV assumes the cashflows are regular.
A use of this could be comparing investing in a stock versus earning interest on the same amount in a bank account.
XIRR
XIRR behaves the same as IRR, but allows for irregular cashflows.
Syntax
XIRR(values, dates)
In the example above, the cashflows are in column B and the dates are in column C. The formula in C9 will look like
=XIRR(B3:B7, C3:C7)
The internal rate of return is 37%.
XNPV
XNPV behaves the same as NPV, but allows for irregular cashflows.
Syntax
XNPV(rate,values,dates)
In the above example a discount rate of 10% is applied over the cashflows for net present value of £2,954.46.
Function with cashflows
Assumptions to avoid
APR vs APY
Annual Percentage Rate (APR) is the annual rate of interest paid on credit products. It is generally used to allow borrowers to compare different offers. A mortgage with an interest rate of 4% could have an APR of 4.4% once other fees are taken into account.
Annual Equivalent Rate (AER) is most often seen in relation to savings accounts. It demonstrates what rate of interest you will earn depending on how often interest is added to your account.
An account that pays interest monthly will have a lower interest rate than one that pays annually because the account will benefit from compound interest sooner. They could have the same AER.
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...