SPECIALISTS IN FINANCIAL MODELLING
0 item(s) \$0.00 AUD + GST

# Tutorials

Microsoft and other providers have quite a wide range of free online tutorials which are often overlooked by those seeking to improve their Excel skills.  There are hundreds of free Excel tutorials available from Microsoft, so to make it easier, we've compiled a list of those which relate specifically to Financial Analysis.

Prior to coming on a Plum Solutions Financial Analysis workshop or online course, we recommend that you have at least a working knowledge of Excel.  During the course we assume you are already using Excel as part of your work.  For a recommended list of what you should know before coming on a course, please see the prerequisites.

Below is a list of Excel tools and functions you'll find useful for modelling and analysis.  Click on the links below to go directly to the tutorial on the host websites:

Course DescriptionContentExcel VersionLevelLength

## BASIC

You should know this before coming on any of the Plum Solutions Training Courses.

Add numbers using formulas, buttons, and functions

• Using the SUM function to add
• Using the SUMIF function to add data meeting a certain criteria
Any Basic

3 videos; around 13 minutes in total

How to Use Excel

You should know this before coming on any of the Plum Solutions Training Courses.

(Note that the PRODUCT function is not often used by modellers, however)

Chapter 1: The Basics: Setting the Foundation

Chapter 2: Dig Deeper & Gain Insights with Formulas

Chapter 3: Powerful Time Saving Excel Functions

• What are Excel formulas, and why should you learn them?
• Using the SUM and PRODUCT functions
• Dividing and subtracting
• COUNTIF, SUMIF and IFS functions
• Pivot Tables
• Filtering
• Conditional Formatting
Any Basic

Three chapters; around 30 pages

Basic Maths in Excel

You should know this before coming on any of the Plum Solutions Training Courses.

(Note that the PRODUCT function is not often used by modellers, however)

Learn to use Excel as a calculator using formulas, and then start basic functions.

• Using basic functions in Excel such as SUM and PRODUCT (Note:  The PRODUCT function is not often used by modellers)
• Operator order; what part of your formula Excel acts on first, second, third, and so on?
• More complex formulas in Excel 2013. Learn how to create more complex formulas using multiple operators, cell references, and functions.
AnyBasic

4 videos; around 14 minutes in total

Creating a Basic Chart in Excel

You should know this before coming on any of the Plum Solutions Training Courses.

(How to create a Combo Chart is covered in many of our courses)

Learn how to create some of the more common types of charts such as pie chart, bar chart and line chart, and determine which is best for which type of data.

• Create the chart or graph that’s most applicable for your data. Coping without the chart wizard that's no longer available in later versions of Excel.
• Learn how to create pie, bar, and line charts, depending on what type of data you start with.
• After you have finished creating the chart, learn to customize it to show additional chart elements, such as titles and data labels, making it look exactly the way you want.
• The combo chart was added to Excel 2013.  Change the chart type of one or more data series and add a secondary vertical axis in the combo chart.
• To copy a chart to another worksheet in the same or a different workbook, right-click the chart and click Copy.
2013Basic

5 videos: around 18 minutes in total

Create your first Excel 2013 workbook

Creating Tables is covered in the Data Analysis & Dashboards course.  You will find it easier if you are familiar with these concepts prior to coming on the course.

See how to do everyday basic tasks in Excel 2013.

• Start using Excel opening an existing workbook, or use a template
• Convert Data to Tables
• Save and print an Excel workbook
• Learn to calculate formulas for your tables and use cell references to automatically update totals when you change values in a table.
• See how to write a basic formula, and use auto fill to quickly complete adding data into cells
2013Basic

5 videos; around 13 minutes in total

Cell References

You will find it useful if you are familiar with this concept before coming on any of the Plum Solutions Training Courses.

Each cell in the grid of the Excel worksheet is identified by its reference. When you use cell references in a formula, Excel calculates the answer automatically using the numbers in the referenced cells.

• Understanding the importance of cell referencing; each cell is identified by its reference—the column letter and row number that intersect at the cell's location. For example, a cell in column D and row 5 is cell D5.
• When you use cell references in a formula, Excel calculates the answer using the numbers in the referenced cells. When you change the value in a cell, the formula calculates the new result automatically.
• Now that we know how to create a formula that adds two cells. Now let’s copy the formula down the column so it adds the other pairs of cells.
AnyBasic

3 videos; Around 5 minutes in total

Using AutoFill and Flash Fill

These are handy tools, but you do not need to know them before coming on any Plum Solutions Training Courses.

Entering repetitive information can be very time-consuming and tedious.  AutoFill and FlashFill are fantastic time savers.

• Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells.
• Use Flash Fill, new in Excel 2013, to fill out data based on an example. Flash Fill typically starts working when it recognises a pattern in your data, and works best when your data has some consistency
2013Basic

3 videos; Around 8 minutes in total

## INTERMEDIATE

This is handy, but you do not need to know this before coming on any Plum Solutions Training Courses.

You can add and subtract time in Excel almost like you subtract other types of numbers.

• Adding time. You can add time using the autosum, or simply by adding the cells together using the + operator. You can also add a unit of time, such as 1½ hours, to a time of day, such as 10:00 a.m., by using the TIME function.
• Subtract time. You can subtract time in Excel using a formula, almost like you subtract other types of numbers. As with adding time, you can also subtract a unit of time, such as 1½ hours, from a time of day, such as 10:00 a.m., by using the TIME function.
AnyIntermediate

2 videos; around 7.5 minutes in total

Average a group of numbers

This is handy, but you do not need to know this before coming on any Plum Solutions Training Courses.

We often need to find the average and weighted averages for use in financial modelling and data analysis.

• Average a group of numbers; using the AutoSum on the Ribbon to calculate an average as well as to add numbers in Excel.
• The AVERAGEIF function returns the average of cells in a range that meet criteria you provide, just like the SUMIF and COUNTIF does.
• Usually when you calculate an average, all of the numbers are given equal weighting or significance. An ordinary average is calculated by adding the numbers together and then divided by the number of numbers in the range. With a weighted average, one or more numbers is given a greater significance, or weight.  We can do this using the SUMPRODUCT function.
AnyIntermediate

3 videos; around 8 minutes in total

Make the Switch to Excel 2013

Useful to know if you've recently upgraded to Excel 2013

What's New in Excel 2013

When you first start using Excel 2013, even everyday basic tasks may seem difficult.  Gain a brief overview of the major changes.

• The top 5 things that have changed in Excel 2013.
• 2013 is a little different in a few ways. Take an in-depth look at the features, such as starting it up, or saving your file. Also learn how to convert your .xls file to an .xlsx file.
• Once you get organised in Excel 2013, there are three major places to find things: in the workbook, on the ribbon, and in the Backstage.
• Take a tour of the ribbon and learn where all your favorite buttons and menus are in Excel 2013.
• Save your Excel file either to your computer, or to an online service like OneDrive. Then find out how to share it with others, even if they are running an older version
2013Intermediate

5 videos; around 14 minutes in total

Sort and Filter Data

This is handy, but you do not need to know this before coming on any Plum Solutions Training Courses.

When using Excel for the purpose of Data Analysis, sorting and filtering are very useful.

• Sort data in Excel quickly, in just a few clicks. To change the order of your data, sort it. To focus on a specific set of data, filter a range of cells or a table.
• As well as sorting numbers, you can also sort text and dates, either alphabetically, by format (such as color), or by a custom list you create.
• Use the AutoFilter to filter for blank cells, non-blank cells, and use criteria to filter, filter by cell or font color.
• The Advanced Filter can also be used to create more powerful filters, such as filtering for unique records and using operators such as OR.
AnyIntermediate

4 videos; around 16 minutes in total

Use Conditional Formatting

Conditional formatting is extremely useful in financial modelling and data analysis.  It is used in varying degrees in most of our courses, and you will find it useful if you are familiar with this concept before coming on any of the Plum Solutions Training Courses.

Excel has some easy ways to get more meaning out of the numbers in your model with conditional formatting.

• Conditional formatting provides visual cues to help you quickly make sense of your data. For example, it’ll clearly show highs and lows, or other data trends based on criteria you provide.
• Say you want to see, at a glance, what tasks in a list are late. In other words, those tasks with due date dates before today. Or which tasks aren’t due for a week or more. Conditional formatting will allow you to see that at glance and what’s more, the formats will change according to today’s date.
• Use the Quick Analysis tool to conditionally format cells in a range that have duplicate text, unique text, and text that’s the same as text you specify.
• Learn how to quickly copy a cell’s formatting, remove conditional formatting from specific cells, and remove all conditional formatting from an entire worksheet.
AnyIntermediate

4 videos; around 8.5 minutes in total

Take conditional formatting to the next level

This is handy, but you do not need to know this before coming on most Plum Solutions Training Courses.

Advanced conditional formatting using formulas is covered in the Advanced Financial Modelling course.

Let’s go a few steps further and see how to apply conditional formatting to cells, tables, PivotTables, and worksheets, as well as learn to use formulas in conditional formatting.

• Learn how to apply conditional formatting to specific cells, tables, PivotTables, and worksheets. You can also format cells that are blank or contain errors.
• To control more precisely what cells will be formatted, you can use formulas to apply conditional formatting.
• In the Conditional Formatting Rules Manager you can choose to show formatting rules for areas of a workbook that have rules, such as the current selection or a specific worksheet. You can then create, edit, and delete rules as well as manage the precedence of rules for the cells or worksheet you selected.
AnyIntermediate

3 videos; around 12.5 minutes in total

Using VLOOKUPS

VLOOKUPs are very useful in modelling, and it is recommended that you are familiar with them prior to coming on most Plum Solutions Training Courses. You do not need to know them prior to coming on the Introduction to Financial Modelling course, however.

One of the most commonly-used functions by financial modellers, and yet the most prone to error, the VLOOKUP is a critical starting point for any aspiring modeller.

• When and how to use the VLOOKUP
• An explanation of each of the function's arguments.
• Look up values on a different worksheet
• How to use absolute cell references to copy a VLOOKUP formula down a column.
AnyIntermediate

4 videos; around 11.5 minutes in total

Create a PivotTable to analyse your data

You should know this before coming on a Data Analysis & Dashboards course.

PivotTables can help you make sense of your data, and are a powerful way to summarise, analyse, explore, and present your data in a report

• Learn what PivotTables and PivotCharts are and how you can use them to summarise and analyse data in Excel 2013.
• Create a manual PivotTable if you prefer to design the PivotTable yourself.
• Sort, filter, summarise, and calculate your PivotTable data
• Filter your PivotTable data with slicers and timelines. PivotCharts visually show your PivotTable, making trends easier to see.
AnyIntermediate

4 videos; around 15 minutes in total

This is covered in the Introduction to Financial Modelling course.

• Add a password to protect worksheets or your entire workbook and control whether others can open or make changes to them.
• To help improve your privacy in Excel, a password adds protection to workbooks and worksheets to prevent others from changing, moving, or deleting important data.
AnyIntermediate

2 videos; around 8.5 minutes in total

The basic IF function is a requirement of all  Plum Solutions Training Courses.

We cover a basic IF, AND and OR and their use in a financial model in the Introduction to Financial Modelling course.  More complex nested IF functions are covered in the Intermediate Financial Modelling in Excel course.

Learn complex examples and variations of the IF function in Excel, including nested IF functions, IF with AND and OR, COUNTIFS and SUMIFS, and AVERAGEIFS and IFERROR.

• Refresher of the IF function
• Nested IF functions, (one IF function inside another), allow you to test multiple criteria and increases the number of possible outcomes.
• See how to extend the functionality of the IF function by nesting AND and OR functions.
• COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met. SUMIFS adds the cells in a range that meet multiple criteria.
• AVERAGEIFS returns the average of all cells that meet multiple criteria. IFERROR returns the value specified if AVERAGEIFS evaluates to an error.

5 videos; around 21 minutes in total

Create and Manage Data Validation Drop-down Lists

Data Validations are taught in the Introduction to Financial Modelling course.  You should know this before coming on the Intermediate Financial Modelling in Excel course.

Reduce error in your models by restricting the possible inputs the user can enter by using drop-down lists.
• Enter data more quickly and accurately when you use a drop-down list to limit the entries users can make in a cell. When someone selects a cell, the drop-down list’s down-arrow appears, and they can click it and make a selection.
• Use a comma-delimited list, a cell range, or a named range to define the options in a drop-down list.
• Add input and error messages to help people decide what drop-down list option to select, and even to let them know that a cell contains a drop-down list.  You can create a message that appears when they select a cell, or that shows an error message when a drop-down list option isn't selected properly.
• To prevent changes to your drop-down list data, you can hide the columns, rows, or the entire worksheet that contains the data. And unhide them if you need to make changes. You can also lock and password protect the cells on the worksheet or the entire worksheet.
AnyAdvanced4 videos; around 15 minutes in total

Array Formulas

Also see this article: Should we use Array Formulas in everyday models?

Array formulas are taught in the Advanced Financial Modelling in Excel course. You do not need to know this prior to coming on any Plum Solutions courses.

The array formula is probably one of the most powerful tools in Excel - but also the most under-used.  For some reason it is a tool that is shunned by many - even advanced users - of Excel.  But just because most people don’t know how to use them, does that mean they should be avoided?

• Create array formulas (often called Ctrl Shift Enter or CSE formulas), to perform calculations that generate single or multiple results.
• An array formula can also return multiple values. Calculate totals, averages, and maximum values using the SUM, AVERAGE, and MAX functions.
• Editing or deleting an array formula requires special steps, and is not as straightforward as it seems.

3 videos; around 10 minutes in total

Calculate multiple results by using a data table

Also see this article: Sensitivities, Scenarios, What-if Analysis - What's the Difference?

and this video: Scenario Analysis Methods.

Data Tables are taught in the Advanced Financial Modelling in Excel course. You do not need to know this prior to coming on any Plum Solutions courses.

A data table is a range of cells in which you can change values in some in some of the cells and come up with different answers to a problem.

Data Tables are a great way of performing sensitivity analysis in Financial Modelling.

• Data table basics
• Create a one-variable data table
• Add a formula to a one-variable data table
• Create a two-variable data table
• Speed up calculation on a worksheet that contains data tables
Any

Understanding Power Pivot in Excel 2013

A brief introduction to Power Pivot is given in the Data Analysis & Dashboards course. You do not need to know this prior to coming on any Plum Solutions courses.

Power Pivot lets you import massive amounts of data from different sources and mash it together to create useful reports. Learn what it is, and how you should be using it.

• Quick tour of the Power Pivot window
• The difference between implicit and explicit calculated fields
• How to create a calculated column
• How to create a calculated field (also known as a measure)
• How to create a hierarchy
• A quick look at Power View

2013

15 minute webinar video

Understanding the Excel Data Model

A brief introduction to the Data Model is given in the Data Analysis & Dashboards course. You do not need to know this prior to coming on any Plum Solutions courses.

Fundamental to Self Service BI is the Excel Data Model in order to perform fast reporting, analysis and visualisation in both Excel and Power Pivot.

• What an Excel Data Model is
• How to get data into a Data Model
• How to create relationships between tables in the model
• How to extend the model by using Power Pivot

Power Pivot: Powerful data analysis and data modeling in Excel

A brief introduction to Power Pivot is given in the Data Analysis & Dashboard  course. You do not need to know this prior to coming on any Plum Solutions courses.

It is possible to create a Data Model to use both in ordinary Excel, as well as in Power Pivot.  This tutorial looks at the difference between the two.

• Comparison of tasks such as importing data, creating tables and relationship, and calculations between Excel and Power Pivot
• How the data is stored
• Saving it to SharePoint

One page tutorial

Work with Macros

A brief introduction to Macros is given in the Advanced Financial Modelling course. You do not need to know this prior to coming on any Plum Solutions courses.

You want to automate a repetitive task in Excel so that you can do the task again with a single click. The best way to do that? Record a macro. And the macro recorder is the easiest way to create a new macro in Excel.

• The fastest way to automate a repetitive task in Excel so that you can do the task again with a single click is to record a macro
• See how to edit a macro in a special text editor called the Visual Basic Editor. Visual Basic is the programming language that macros are recorded in.
• To make sure your macro is always available on your computer, save it to your Personal Macro Workbook, which is a special hidden workbook that loads every time you start Excel.
• Link your macro to a button and make it available in two places: In a custom group on the Developer tab, and on the Quick Access Toolbar.