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