Sunday, December 22, 2013

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. 

No comments:

Post a Comment