Larry

Capital Expenditures and Depreciation

 Balance Sheet, Cash Flow Statement, Financial Statements, Income Statement, Modeling  No Responses »
Jan 102012
 

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 »

 Posted by Larry at 9:59 AM  Tagged with: , , ,

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

 Balance Sheet, Modeling  No Responses »
Dec 292011
 

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 »

 Posted by Larry at 11:37 PM  Tagged with: , , , , , , , ,

Circular References in Financial Models

 Balance Sheet, Financial Statements, General, Modeling  No Responses »
Dec 192011
 

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 »

 Posted by Larry at 4:27 PM  Tagged with: , ,

How to Balance Your Balance Sheet

 Balance Sheet, Cash Flow Statement, Financial Statements, Income Statement, Modeling  No Responses »
Dec 092011
 

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 »

 Posted by Larry at 11:26 PM  Tagged with: ,

How To Forecast The Income Statement

 Income Statement, Modeling  No Responses »
Dec 032011
 

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 »

 Posted by Larry at 11:50 PM  Tagged with: , ,

Balance Sheet – Part 1

 Balance Sheet  No Responses »
Nov 302011
 

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

 

 

 Posted by Larry at 3:03 PM  Tagged with: , , ,

What is financial modeling?

 General  No Responses »
Nov 302011
 

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.

 Posted by Larry at 9:57 AM  Tagged with: ,

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

 General  No Responses »
Nov 302011
 

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.

 Posted by Larry at 9:39 AM  Tagged with: ,

Basic Financial Model

 Balance Sheet, Cash Flow Statement, Financial Statements, Income Statement  No Responses »
Nov 282011
 

 

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 »

 Posted by Larry at 10:39 PM

Excel rocks on Windows, not so much on Mac

 General  No Responses »
Nov 272011
 

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.

 Posted by Larry at 2:25 PM  Tagged with: , , ,
 Older Entries
© 2011 Financial Modeling Tutorial Suffusion theme by Sayontan Sinha