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.
CapEx Forecast

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.

Continue reading “Days Sales Outstanding, Days Payable Outstanding, and Days Sales Inventory”

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”

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.

For a working model, start with the basic financial model.

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”

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”