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

 

 

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.

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”

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.

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.