## Capital Expenditures and Depreciation

Capital Expenditures aka CapEx is the spending of money to buy or fix assets. CapEx is typically related to buildings, property, equipment. Many financial models are built to help determine growth and expansion plans that require spending money on equipment and other assets. Understanding the relationship between CapEx, deprecation, and the financial statement is a very important aspect of financial modeling.

In the current sample financial model, deprecation and CapEx are not forecasted to change. However as the business grows, additional equipment is needed.
In the new model, we CapEx spending in Years 5,6 and 8.

# Continue reading “Capital Expenditures and Depreciation”

## Days Sales Outstanding, Days Payable Outstanding, and Days Sales Inventory

Days Sales Outstanding (DSO), Days Payable Outstanding (DPO), and Inventory Turns are some key metrics for company analysis. While they are just some simple calculations, they tell are story about how a company is doing.

In the balance sheet assumptions section of the model, see below, we calculate each metric and then make assumptions about the forecast values.

## Circular References in Financial Models

### What is a circular reference?

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

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”

## How to Balance Your Balance Sheet

One of the hardest parts of building a financial model is getting the balance sheet to balance, meaning the basic equation of Assets = Liabilities + Shareholder’s Equity is true.

The balance sheet itself is not the problem, it is usually the cash flow statement that introduces the error.

Here are some tips to make sure your cash flow statement is correct to ensure you calculate the correct ending cash balance.

# General

Make sure you rebuild the historical cash flow statement with formulas, that’s the only way to ensure you’ve accounted for all numbers and everything will flow going forward.

All line items on the balance sheet must be used in the cash flow statement. Continue reading “How to Balance Your Balance Sheet”

## How To Forecast The Income Statement

Forecasting the income statement is the first step to building

# Rebuild the historicals

To forecast the income statement, you have to understand the historicals. So start by rebuilding the financial statements.  This means taking the given values and adding formulas where necessary.

If you want to give it a shot (highly recommended), you can download the values only version and rebuild the financial statements by adding in formulas for all three financial statements. Continue reading “How To Forecast The Income Statement”

## Balance Sheet – Part 1

While the income statement is a tally of revenue and expenses over a period of time, a balance sheet is a snapshot of the current financial standing.

Below is a sample balance sheet.  It is part of a larger working financial model.

To keep it simple, the example includes basic line items including:

 Current Assets Cash Account Receivable Inventory Prepaid Expenses Total Current Assets Long Term Assets Plant, Property & Equipment, Gross Accumulated Depreciation Plant, Property & Equipment, Net Total Long Term Assets Total Assets Current Liabilities Account Payable Accrued Expenses Total Current Liabilities Long Term Liabilities Long Term Debt Total Long Term Liabilities Total Liabilities Shareholder’s Equity Common Equity Retained Earnings Total Shareholder’s Equity

## Basic Financial Model

Embedded above is a basic working financial model for Sophie’s Bicycle Shop. You can download into Excel by clicking the link at the bottom of the embedded spreadsheet.

For a typical modeling assignment, start by reviewing the historical financials, then forecast the future, and finally build some different scenarios such as opening a new store, buying a competitor etc. (I haven’t thought all these out, so I’m open to suggestions).

I’m purposely left out some sub-calculations to keep the model simple, we will hopefully cover at a later time. Continue reading “Basic Financial Model”

## Income Statement aka Profit Loss Statement

Income statement also know as a profit and loss statement is the main financial statement.
It covers revenues and expenses for the company.

```Revenue:
Sales
- Cost of goods sold
= Gross profit

Expenses:
- Salary
- Rent
- Utilities

= Operating Income/(Loss) - aka EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization)

- Depreciation
- Amortization
= EBIT (Earnings before Interest and Taxes)

- Interest
= EBT (Earning before Taxes)

- Taxes
= Net Income aka Earnings```

Operating Income is also known as EDITDA.
EBITDA is a very common term used when talking about a company especially for valuation.
EBITDA is a good proxy for overall health of the company without knowing the full details because it looks at the operating functions of the company and ignores the capital structure of the company.

For sake of simplicity, I’ve ignore dividends and stock expense and other usually immaterial line items.

This is just the basics, we’ll dive into more detail in later posts.

Check out our sample profit and loss statement below.  It is fully downloadable in Microsoft Excel.