Circular References in Financial Models

What is a circular reference?

A circular reference is when a cell refers to itself directly or indirectly.

Are circular references bad?

In most cases, a circular reference should and can be avoided with some planning. However, in a complex financial, I found it easier to just use circular references in certain areas.

Circular References in Financial Models

Circular references are used to help calculate cash balances. Let’s walk through two typical cases.

Interest

The cash sitting in the bank generates interest. The interest income is taxed and lowers the net income. More cash -> more interest -> more tax -> lowers net income -> effects cash.

See the example below. To determine the amount of interst, we use an average of the forecasted beginning and ending cash balances. It’s not fair to use just the beginning or the ending cash balances to calculate interest because over the time period that balance will change. Continue reading “Circular References in Financial Models”

What is financial modeling?

Financial modeling is the building of a tool to answer questions related to budgeting/forecast, an investment, or any financial decision.

In the context of this site, financial modeling will be about forecasting the future performance of a company and related transactions using a standard three financial statement model in Excel.

Building a financial model is as much an art as it is a science.

When you’re giving a set of historical financial statements, it will be necessary to reconstruct the historical with a model. This is necessary step in order to understand how the numbers fit together. This is the science part.
The art is extending the financial model and forecasting the future performance of the company based on assumptions. Every line item on the income statement and balance sheet can be changed by changing assumptions.

** Interview Tip – When you’re expected to build a financial model, it is very important to get the math right, but it is just as important to be able to explain your assumptions.

Excel rocks on Windows, not so much on Mac

Microsoft Excel is the preferred weapon choice of financial modelers. In fact, I can’t think of any financially oriented people who don’t live and breathe Excel. This is pretty obvious.
But not all Excel’s are created equally. Excel on a Mac is not a financial modeling tool. It’s a simple spreadsheet tool like Google Doc’s Spreadsheets or Mac Numbers. If you’re serious about building financial models, you have to use Excel for Windows. You can see some differences between the Mac and Windows version of Excel.

There are too many shortcomings in the Mac version for you to be efficient and productive – not enough keyboard shortcuts and while Mac Office 2011’s Excel brought back macros, I’ve heard of some issues with them.

I’ve played with different variations of Windows Excel on a Mac including Parallels, VMWare’s Fusion, and Bootcamp. While they all loaded and ran fine, I just never felt comfortable with the keyboard.

So for my setup, I keep a Window’s machine around mostly for Excel and a Mac for other stuff.

Sophie’s Bicycle Shop

As we develop the financial model and related discussions, I find that it’s best to have a business concept in mind.  In the past, I’ve tried web/internet apps, services companies, retail stores, and other business models. Retail stores are the best because it is the easiest to picture in your mind as you think about the money flow.

So we’re going to base our discussion on a fictional bike shop – Sophie’s Bike Shop.

Sophie’s Bike Shop sells bikes (duh!) for adults and children, accessories and parts, and they also have a services component (tune ups, repairs, etc.).

With all that in your mind, let’s get to work.