Beginner to Pro in Excel: Financial Modeling and Valuation

This opening section will take you through the basics of Excel. An easy-to-understand introduction that will bring you up to speed in no time.

Some of the topics and concepts discussed in this lesson:

·Row and Column references

·Name box

·Formula bar

·The Ribbon

·Format cells

·Basic operations in Excel: Copy, Paste, Paste Special, Cut, Undo, Redo

Learning how to use keyboard shortcuts is an essential part of your Microsoft Excel training. The slides in this lesson will enable you to pick up a number of useful keyboard combinations that will dramatically increase the efficiency of your work

The IF function tests whether a certain condition is true or false. It works in the following way:

1.Excel tests whether the logical test, which is the first argument of the function, is satisfied

2.If the logical test is satisfied, Excel delivers value A (the second argument of the function)

3.If the logical test is not satisfied, Excel delivers value B (the third argument of the function)

SUM functions are widely used in Excel. They allow us to add the numbers within a given cell range

1.SUM has only one argument – the range of cells to be summed

2.SUMIF allows us to add the cells within a range, which satisfy a given criterion

3.SUMIFS is able to add up values in a range of cells that satisfy multiple conditions

This lesson is dedicated to Excel’s COUNT functions. They are applied when we need to count the cells within a range that contain numbers, text, or satisfy specific conditions. In particular we will be examining:

1.COUNT - counts the number of cells that contain numbers

2.COUNTA - counts the number of cells that contain text

3.COUNTIF - returns the number of cells that satisfy a specific condition

4.COUNTIFS - counts the number of cells within a given cell range, if multiple conditions are satisfied

This lesson describes the formula syntax and application of the AVERAGE and AVERAGEIF functions in Excel.

1.AVERAGE finds the mean of a specified range of cells

2.AVERAGEIF finds the mean of a specified range of cells, if a given condition is satisfied

Text functions are an indispensable tool for any Excel user. In this lesson, we will focus on three types of text functions:

1.Text functions extracting a part of a cell - LEFT, RIGHT and MID

2.Functions changing the case of a text - UPPER, LOWER and PROPER

3.Combining cell values of multiple cells - & and CONCATENATE

In this video tutorial, we will continue to present to you popular functions in Microsoft Excel;

1.MAX finds the highest value in a range of cells

2.MIN allows us to obtain the lowest value in a range of cells

ROUND would allow you to round numbers in Excel

Transferring data efficiently is at the heart of your success in Microsoft Excel. This lesson provides an easy to understand introduction to two of the venerable functions in Excel:

1.Vlookup – finds a lookup value in the leftmost column of a table and returns a corresponding value

2.Hlookup – finds a lookup value in the top row of a table and returns a corresponding value

The combination of Index and Match is our favorite tool for looking up data. It is a flexible instrument that overcomes some of the shortages of Vlookup and Hlookup;

1.Index extracts the value lying at the intersection of a particular row and column

2.Match returns the relative position of an item

3.The combination of Index & Match - Index needs as an input relative positions within a range, Match delivers those relative positions by finding the lookup value

Error messages are undesired in Excel spreadsheets for several reasons:

1.If a range contains a cell with an error, this impedes us from carrying out operations with the entire range

2.They disturb the eye of Excel Users

3.A file containing error messages cannot be shown to external users

IFERROR resolves these issues. The function tests whether a given cell contains an error, and if it does it replaces the error message with a value specified by the Excel user

CHOOSE is a great tool for Financial Modeling. It allows Excel users to build various scenarios, which can be critical when dealing with an uncertain environment and building a dynamic model.

CHOOSE has n arguments, which can be divided into two groups – its index number (the first argument) and the rest of the arguments.

·The Index Number determines, which argument will be selected

·The rest of the arguments are the values from, which CHOOSE selects

Goal Seek is part of the “What-if” analysis tool within Excel. It is a nice instrument that allows users to find a desired result for a given parameter by changing other parameters related to it.

Three inputs are required in order to use the Goal Seek functionality:

1.A cell to set

2.The value to which we would like to set the cell

3.A cell to modify

This lecture introduces you to Data Tables. A powerful tool enabling you to provide sensitivity analysis for a number of parameters. Data Tables illustrate perfectly the influence that a given parameter has on the final output

Pivot Tables are one of Excel’s most powerful features. They allow us to synthesize and elaborate, with ease, large amounts of data.

In this lesson we will learn how to:

1.Create a Pivot Table

2.Introduce different variables to the Pivot Table Report

3.Choose the type of calculation that you want to use to summarize data

An exercise focusing on the practical application of “Sumifs” - a very interesting Excel function that allows you to create dynamic tables in an efficient way.

The next part of the course is structured as a Case Study and would allow you to:

1.Apply in practice what we have seen so far

2.Work on a real-world task

3.Enhance your spreadsheet formatting skills

4.Learn how to create advanced charts

Before managing big data, you must understand it first. That is why in this lesson we will focus on understanding our data source. The data in the source sheets is structured in the following way:

1.Name of P&L account

2.Partner company number (indicates whether a transaction was registered with a related or with an external counterparty)

3.Name of Partner company

4.Registered amount

5.Accounting code

The next video of our Case Study we will show you how to order a source worksheet. The operations to be carried out include:

1.Writing titles on the top of the source tables

2. Placing filters

3.Align the data in a coherent way

4.Eliminate unnecessary rows

When we have a large data extraction, we have to be sure that we will be able to manage it efficiently. A good way to do that is by creating a code, which summarizes the important information about each line item

Gathering data from different years (and different sheets) on a single sheet is crucial. It allows the user to classify all the data in a coherent way when attributing a P&L category

In the previous video we were able to organize all of the line item codes in the same sheet. In this lesson we will apply VLOOKUP in order to fill in some of the fields of our Database

The Database sheet unites all three years of data together. The only fields that are missing are the actual amounts that were observed for each line item during the three years.

In this lesson, we will fill in the amounts observed for each code with SUMIF. In addition to that, we will use a method that would help us understand whether we have worked correctly or not.

In one of our previous lessons we used VLOOKUP in order to fill some of the fields in the Database sheet. In this video we will show you two things:

1.INDEX & MATCH is a suitable replacement of VLOOKUP in Microsoft Excel

2.In certain situations INDEX & MATCH is more powerful than VLOOKUP

Given that the Database sheet contains a large amount of rows, we have to group such information into categories. This so-called “Mapping” exercise is necessary as it would allow us to produce a concise (and meaningful) P&L statement

In this lesson, we will organize all categories obtained in the “Mapping” exercise into a P&L statement. In order to do that we will have to:

1.Copy all categories into a new sheet

2.Remove duplicate values

3.Order the categories in order to form a meaningful P&L format

Correct formatting will result in a greater initial and lasting impression of your work. In this lesson we will add a few important formatting details to our existing P&L structure – place units of measurement and financial period on top, add subtotal and total lines etc.

In this video we will populate the ready P&L structure with the numbers from the Database sheet by using SUMIF

We already said that it is very, very important to use checks in order to verify if the result that was obtained is correct. What if it is not?

In this lesson we will learn how to find mistakes by using COUNTIF.

Percentage variations and incidence on total are useful tools, which highlight the dynamics of a given business. In this lesson we will learn how to add them to our P&L statement.