Financial Modeling in Excel

Introduction

First and foremost, I would like to extend a special thank you to Professor George A. Haloulakos, MBA, CFA for his guidance, mentorship, and for teaching me this material in 2017 when I was looking to advance myself further in my career.

This material was originally rolled out to a Economics students at UCLA, summer cohort 2018. Throughout this short course, these concepts are illustrated visa vie IMAX Corporation when dealing with actual financial data. The material has been updated to reflect the specific date ranges of 06/22/2018 - 06/22/2021.

What is Financial Modeling?

Financial modeling is an endeavor to shape and/or create an abstract depiction of real financial data.

Why is it helpful or necessary?

It helps condense large data sets into simple spreadsheets that can be used as templates for gaining relevant insights into a company’s performance.

Where can we find the financials (pro forma) for a publicly traded company?

• Yahoo Finance
  
• Wall Street Journal
  
• The investor relations section of the corporation’s website

What tools will we be using?

• Microsoft Excel
  
• Visual Basic for Applications (VBA)

The following sample historical pricing data was imported directly from Yahoo Finance into Excel.

Once any set of data is in Excel it is EASY to manipulate it by removing or expanding what is already there.

For example, we can add % changes in prices over time and averages.

Adding these statistics involves the use of formulas.

However, removing irrelevant data can be done by hiding or deleting columns and/or rows.

When looking at the percent change between 2 values, subtract the old value from the new value and divide the result by the old value as shown:

To get the Open, High, Low, Close (OHLC), we take the average of the cells B2:E2 as shown by the excel formula =AVERAGE($B2:$E2) below. We bring the formula down to the rest of the cells by clicking on the right corner of cell J2, following the same procedure for HLC, starting in cell K2.

Let’s not forget to adjust the formula in the HLC and the OHLC averages by locking in these absolute references.

Before we proceed with more built-in Excel formulas, let us delve into a little bit of VBA (Visual Basic for Applications). VBA is the back-end programming language of Microsoft Excel and other Microsoft Office programs. It is important to ensure that our Microsoft Excel package has the Developer Tab added onto it.

This step is NOT mandatory for entering the visual basic editor but is necessary if you are recording macros to automate various processes.

If you are just writing code (i.e. coding a function), you can enter the VBA environment simply by pressing ALT + F11 on your keyboard.

1. Click on “File.”
2. Click on “Options.”
3. Click on “Customize Ribbon.”
4. Make Sure there is a check mark next to “Developer.”

We’re not going to cover the entirety of VBA programming but will work with the basics.

We want to ensure that we are looking at the correct VBA Project. The project name is the name of the Excel File that you are working within. In this case, it is called: “Financial Modeling Workshop.xlsx.”

In any event, VBA script is inserted into what is called a “module.” So, to insert a module, simply click on “Insert” followed by “Module” from the top menu.

Let’s start by writing a basic function.

One of the most used functions in Financial Modeling is “getformula.” It simply tells us what formula we plugged into any given cell.

---

Application.Volatile: this function is recalculated when any given cell in any workbook in the application window changes value.
If r.HasArray Then: if, then statement stating that if the range r has an array, then the formula (getformula) = some defined array.