Simple Consolidated Financial Statement Model

 

The consolidated financial statement (CFS) model serves as the basis for creating pro-forma projected financial statements. It also is the foundation to both a discounted cash flow (DCF) and leveraged buyout (LBO) valuation model. These can vary in complexity depending on the needs of the end user and while most contain key elements, the actual design will vary. After a class discussion about CFS in my graduate program, I decided to create a simple example starting with a blank sheet and using the figures from the class as a foundation for model. 

To begin with, I will provide a summary of each seven sections created. Following this, I will offer some advice for creating models such as these, including errors to watch out for. Lastly, a short list of design features and their basic explanations has been included to assist the user in their understanding of the model.

The Seven Sections

Within this consolidated financial statement model, I have included seven sections. These include not only the three primary financial statements, but also supporting schedules. The first statement is a key ratio summary for the entire model. This was included as the first section to allow the user to quickly review the financial health of the firm over the modeled period.

The next section is the consolidated income statement. This section creates a pro-forma income statement that includes two historical years and seven projected years. The total revenue is forecasted based on an annual expected growth rate and COGS is based on a percentage of sales remaining constant from 2015E through 2021E. R&D and SG&A expense are calculated as a percentage of sales with independent calculations for each year. The tax rate is entered in the left side of the tax column; however, there is an annual tax override line which allows users to change the effective tax rate for any year. In the absence of this metric, the model uses the stated rate from the left column. Dividends are calculated on an annual per share basis and shares outstanding are expected to remain constant from 2013A.

The balance sheet is very standard with inputs feeding in from other supporting sections and does not warrant any detailed explanation. One noteworthy area is the inflow from the working capital section into the current assets and current liability section. Also the short-term and long-term debt flows in from the debt schedule. The cash flow statement is very much the same. The primary details that should be noted is the inflow from the debt schedule for the financing section of cash flow statement along with the inflow of CapEx from the deprecation schedule.

The Working capital schedule outlines the expected days sales outstanding (DSO), inventory turnover days and days payables outstanding (DPO) for each of the forecasted periods. From these metrics, the forecasted account balances can be calculated. The change in net working capital then flows back into the cash flow statement under changes in cash flow from operations.

The depreciation schedule begin with the forecasted CapEx for each period along with the expected useful life of the asset. The first portion of the schedule calculated book depreciation based on straight-line over the asset’s respective useful life and assumes zero salvage value. The second portion gives the accelerated deprecation values for each asset of each period. These are fictitious number created for illustrative purposes only, but would be representative of accelerated deprecation offered under tax purposes. The third portion computes the depreciation under the accelerated tax method for each respective period. Finally the difference is calculated. The tax rate is fed in from the income statement to calculate the deferred taxes. These are then fed into the balance sheet for either a deferred tax asset or liability.

Lastly the debt schedule. This section begin with the cash at the beginning of the year and then adds cash inflow available for debt repayment from the cash flow statement. There is a line item for the desired minimum cash, which is assumed to remain constant through the entire period; the end of this portion shows the excess cash available for debt repayment. For both the short-term and long-term debt portions the beginning balance is shown first.

Next the mandatory payment and optional payment is calculated. The mandatory payment is expected to remain constant through the model. The option payment is the maximum possible while still maintaining the minimum cash balance. In the event that the firm has less than the desired minimum cash balance, the additional cash is drawn from debt. The short-term debt is either paid or draw from first then any remaining transaction occurs in the long-term debt. The short-term debt also has a celling figure that limits the maximum draw on the credit line. For both the short-term debt and the long-term debt, there is a toggle figure for additional payments/draws; 0 = no additional, 1 = additional.

Interest expense for both short-term and long-term debt is calculated based on average balance based on the interested rate entered in the left column for each respectively. Lastly, the cash balance at the end of the year is calculated and interest income is calculated based on average cash balance. All of these figures flow back into the income statement, balance sheet and statement of cash flows.

Errors to Watch Out For

If you have ever created a financial model from scratch you know there are many little nuances that you must be on the look-out for. While I will not name all of these, I would like to point out a few that are key to success. First and foremost, you balance sheet must BALANCE! This can be a daunting task, but make sure it balances with primarily all formula driven figures (ie: do not go and change a number to “make it work”). Be very careful in your cash flows and debt schedule. These two are very interrelated and you must have them feeding from each other properly or you will never have a functional model. While I am not going to outline the exact process I used for this, I can recommend that you follow my formulas and see how the model is calculating. Lastly, use formula in every place possible. The more formula driven figures the easier it is to ensure a properly functioning model.

Key Feature of the Model

Just a few features to point out. I have created the entire model in a single sheet and used grouping to allow the user to collapse/expand the sections for ease of viewing. Having the entire model in a single sheet makes it significantly easier to both audit and write your formulas. If at all possible, I recommend keeping your model to a single sheet.

There is an “ERROR” checker embedded in the heading of each section. In the upper right hand corner is a formula that looks to see if the balance sheet balances for every period in the model. If it doesn’t, “ERROR” will appear in red font. In doing this, it will allow the user to be well aware of issues in the before making a decision using the model.

The date is entered for the first actual period under the “Key Financial Ratios” section and the next either period’s dates are calculated based on end of 12 months forward. The rest of the dates in the entire sheet are fed from this first set of inputs. This will ensure uniformity within the model.

Some formatting issues. Color coding is essential. Black font represents text or formula driven cells while blue font indicates a user input. This allows the user to easily distinguish between these two. Also, the only cell that was merged was the top heading, all other cells were simply centered. The cell width and height was left the same for each cell in the model, this is great for uniformity. Sum bars were used to indicate sums and a thick black boarder is at the bottom of each section. After the model was completed, the cell outlines were removed. Doing these simple formatting processes can really make your model look great and provide ease of use.

I hope you enjoyed this short narrative of my consolidate financial model. A link to this model can be found at the bottom of this post and under the Excel Files section of my blog. 

Simple CFS Model

DCF Model #1

The Discounted Cash Flow model is a staple in any valuation tool kit. While these tend to vary from model to model depending on the user’s specific needs, there are a few common aspects. A DCF generally includes as assumptions section that is used to model the forecasted pro-forma financials. It is also common to include a sensitivity analysis for certain key metrics; these are Weighted Average Cost of Capital (WACC) and Discount Rate for most DCF models. Despite difference between models, at heart all DCFs are intended to estimate intrinsic value of an asset based on expected future cash flows discounted to present value.

To help me gain a better understanding of DCF models and to improve my Excel skills, I started with a blank spread sheet and went to work. I used sample DCF models that I have seen elsewhere for some of the formatting ideas, however I did fully create every aspect of this model. 

At this time, I am simply going to post the model for review. However, I do plan to have future posts that explain specific parts of this model. As a note, I used rough inputs for Microsoft to help better demonstrate the functionality of the model; however, there was no rigor put into the assumptions, they are for illustrative purposes only. 

DCF Model #1

Simple Deferred Tax Asset & Liability Explanation

If you have ever taken a look at the balance sheet of almost any publicly traded firm, you will probably notice either a deferred tax asset or a deferred tax liability. While these are relatively simple to understand, I wanted to take a few moments to talk about how these are created and how they will impact not only the GAAP financials, but also the valuation process.

To begin with, what is a deferred tax account? In the U.S. all public companies are required to follow U.S. Generally Accepted Accounting Principles (GAAP) for financial reporting. However, for tax purposes, they follow a slightly different system of accounting. The difference in treatment for certain items can leave a firm actually having different amount of taxable income for accounting books purposes than for tax. Some of these differences are permeate other are simply temporary. Let’s discuss the differences and see some examples.

Permanent Differences

Certain expense are never taxable or are never tax deductible under U.S. Tax law. Take for example the investment in a municipal bond; the coupon stream for muni bonds is never taxable at the Federal level. If a firm is invested in a muni bond, they will show a higher book income versus a taxable income. Now let’s look at a federal fine for pollution. While the firm will use this as an expense for accounting purposes (hopefully extraordinary) they are not allowed to write it off on their tax income and therefore will have a higher tax income than book income. Since issues like these are never reversed, the income tax expense associated with these is factored in on the tax expense for the respective period.

Temporary Differences

While permanent differences are relatively simply and straight forward, temporary differences can be much trickier. Take for example the differences in depreciation methods. Most firms will choose to depreciate an asset under the straight line method, however under U.S. Tax law a system of depreciation called MACRS is used. This is essentially and accelerated deprecation system. This will generally cause the firm to have higher book taxable income in the early years and lower book taxable income in the later years versus the tax accounting income.

If a firm has a book taxable income of $1,000 but a tax accounting taxable income of $800 with a 40% tax rate what will they expense and what will they pay? For accounting purposes they will have income tax expense of $400; however, they will actually only have a tax liability of $320. Now since the difference is temporary and will reverse at a later time, they must account for this. To do this they will create an account-either an asset or a liability. From a valuation stand point, we must remember that the cash flows will be effected due to the differences between actual tax expense and tax liabilities paid in cash.

Since I think some of this can be a bit hard to explain in writing, I have decided to create a short screen cast. I hope this helps make this issue a bit clear.

I do understand that this is not an extensive explanation of deferred tax assets or liability, but I do hope it lays the foundation for a better understanding. 

Excel Model: Defered Taxes & Valuation Example