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

Ohh the CFA…

I found out late yesterday that I will be a recipient of the 2014 CFA scholarship, allowing me to pursue the charter. With this said, I will be adding a new topic area within my writings—my CFA journey. Some of these writings might be as simple as discussing the processes I am conducting to register, prepare and take the exam. Other writings might include a discussion and analysis of a particular topic; doing this will allow me to solidify my knowledge of this particular subject in turn helping me to be better prepared for exam day. I hope you enjoy.

With the above said, I will be quickly discussing my first CFA issue—the passport ID. Due to heightened identification verification processes required by the CFA Institute, all test takers must have an international travel ID prior to registering for the exam. This cannot be a passport card, it must be a passport book. I personally have had no prior reason to secure a passport, therefore I found myself spending the first half of today researching the process for obtaining a passport. I have submitted my application and according to the travel.state.gov site, they currently have an approximate four week turnaround. With the holidays, I do not expect to receive it any time before the New Year. Luckily, even if I do not receive my passport until mid-January, I will have enough time to register for the level one exam and order my study materials and still allow adequate preparation time. I guess lesson learned for today was to not procrastinate. If I would have waited a few weeks or more before looking into the exam registration process, I might have found myself tight on time for preparation.

Stay tuned for future CFA related posts. 

Goodbye Mouse, Hello Keyboard

This post is for all the Excel nerds out there; hope you enjoy.

Since the second semester of my undergraduate program, I have been a huge fan of Microsoft’s Excel. I enjoyed creating spreadsheets to track my grades and I even got really creative and made my own GPA calculator. At the time, I felt like I knew a lot about Excel; boy was I wrong.

Fast-forward a couple of years and I now in my graduate program. A lot has changed during this time, one thing being my knowledge of Excel. Through my undergraduate program I used Excel almost daily for class projects or to track something in my personal life. This left me with a great understanding of the program and some of its capabilities. However, I still had much more to learn.

Once of my goals for the first part of my graduate program was to teach myself advanced financial modeling on Excel. I have completed FactSet’s DealMaven financial modeling course and am in the early stages of working through Breaking into Wall Street’s modeling course. One common these to these programs is the reference to keyboard shortcuts, which makes sense to increase productivity. However, one comment really stood out—it was said that you should attempt to avoid using the mouse as much as possible.

This, at first, seemed near impossible; yet I still attempted it. After a few weeks of working through modeling, I find myself being able to complete more and more tasks without the need for a mouse. And let me tell you, this has drastically increased my productivity within Excel. It is amazing how much faster you can work through the creation of a model once you know the key strokes to complete common tasks.

One really great Excel add-in that helped not only increase my productivity, but also made it easy to reduce use of the mouse was Macabacus. At the time of this post, I have only used the free version add-in and it is truly amazing. They provide intuitive short-cut keys to complete common modeling tasks such as changing formatting, font floor cycle and increase/decrease decimal places. Another great feature is their advanced formula audit tools. If you have ever build or worked with an in-depth Excel model you know how important it can be to audit your formulas—Macabacus makes this daunting task seem a little bit easier.

Long story short, this add-in is amazing and well worth checking out if you are an avid Excel user. Here is the link to their site with the free download; did I mention they offer some great free models? Enjoy. 

Book Review: The Buy Side

Title: The Buy Side: A Wall Street Trader's Tale of Spectacular Excess

Author: Turney Duff

Publication Date: 06/04/2013

Author's Site            Amazon

-------------------------------------------------

Sex, drugs, money and a hedge fund trader; Turney Duff’s memoir of his Wall Street career takes the reader through his unorthodox start on Wall Street, rise to fame and personal battles that came with the job that ultimately lead to his demise.

Duff begins his novel by describing the events that lead him from growing up in Kennebuck, Maine pursuing a career in journalism after graduating from Ohio University to living in New York City looking for any work possible. Through a connection fostered by his uncle, Duff is able to land a job at Morgan Stanley and begins to learn the ropes of Wall Street.

Through a combination of perseverance and sheer luck, Duff lands a place as a trader for Raj Rajaratnam’s massive hedge fund, Galleon Group. After some time at the fund, he is given the opportunity to conduct proprietary trading for the fund, and starts to see a different side of the Wall Street Culture.

As a buy side trader, expense accounts run deep and Duff finds himself being wine and dinned on a regular basis. This continues when Duff leaves Galleon to become trade for Argus Partners. However, while at Argus, he is encouraged to make stronger connects with sell-siders and begins to enjoy more of the perks being offered. His business dinners transform from a glass of wine over a streak dinner to an eight ball of coke and non-stop shots of tequila, and no party would be complete without hookers—lots of them.

Soon Duff finds himself enjoying this lifestyle a little too much. He comes to this realization after meeting the mother of his future child, Jenn. After learning he has a daughter on the way, Duff attempts to settle down and tells Jenn that he will stop using coke and drinking as much. However, Duff finds it much harder to do than he expects.

Eventually, Duff enters himself into rehab and attempts to start clean at a new firm, J.L. Berkowitz. This is short-lived. Soon he will be back to using and hiding it from everyone. He hates himself but can’t seem to stop. This will cost him more than just his buy side ji8ob. His girlfriend and mother of his child, Jenn, leaves him and he finds his two million dollar home nearing foreclosure. With an intervention from a close friend, Duff enters back into rehab.

Duff does an excellent job taking the reader through these events in a way that makes it hard to put the book down. He has opened the door to a Wall Street culture that has remained hidden from Main Street and does so in a way that excites the reader. I would highly recommend this read to anyone interested in a great story of one man’s find of excess on Wall Street. 

Leases and Their Role in Valuation

A lease is a contractual agreement in which one party (the lessor) agrees to allow another party (the lessee) use an asset for a stated period of time. In return, the lessee agrees to make fixed cash payments to the lessor. Since the title does not transfer to the lessee, this is not considered a sale and thus the asset is not recorded on the books of the lessee, rather it stays on the books of the lessor. While this makes sense, there is a problem. Leases are allowing a firm to obtain an asset in return for a future commitment of payments, this is essentially adding an asset to the firm and a corresponding liability. When a firm accounts for this transaction as a lease rather than a sale, it is essentially able to avoid putting the liability on their balance sheet; this is often referred to as “off-balance sheet financing”. This can lead to investors underestimating the liabilities a firm has and therefore poses a risk to the investing community.

Enter capital leases. Under U.S. GAAP, two forms of leasing exist. The first is an operating lease and the second being a capital lease; with the former reflecting the above discussion. Capital leases on the other hand are required to be accounting for in a different manner in which the asset and corresponding liability are reflected on the lessee’s balance sheet. A lease will be classified as a capital lease if it satisfies any one of the following requirements.

1. The life of the lease exceeds 75% of the economic life of the asset.
   (Lease a truck for 9 years that has a useful life of 10 years)
     
2. The present value all future lease commitments is greater than 90% of the current fair market value of the asset.
   (The PV of lease payments is $1,000, when the FMV of the asset is $850)  
   
   
3. The title of the asset transfers from the lessor to the lessee at the conclusion of the lease.
   (After 10 years of leasing a computer, the title of ownership transfers to the lessee) 
         
4. There is bargain purchase price option included in the lease.
   (At the end of a machinery lease, the lessee can purchase the asset for $100, despite the FMV at that time being significantly higher)

In essence, a lease that meets any of the criteria above--while not meeting the legal requirements of a sale--is an economic sale. Since this is the case, the asset being leased is removed from the books of the lessor and placed on the books of the lessee. In addition, the present value of all lease payments are considered a liability on the lessee’s balance sheet and an asset on the lessor’s. This does have a valid reasoning since these type of leases are essentially a form of capital financing and thus should be included in the liabilities section of the balance sheet. Rather than the lessee considering the payment to the lessor an expense, it is now an amortization of the liability with the difference between the payment and the amortized amount classified as interest expense (another indication of debt financing). In addition, the lessee can now depreciate the asset over its useful life.

While much more could be said about capital lease (a possible future topic), I believe from a valuation stand point it is relatively clear as to the treatment. Since they are already classified as assets and a corresponding liability, not too much more needs deep consideration. However, I do not feel the same for operating leases.

If a lease agreement does not meet any of the four criteria outlined above, it will be considered an operating lease. Under this treatment, the lessee does not record the asset nor the corresponding liability. The payments are simply considered lease expense. This is a preference for many firms, as it will give the appearance of a stronger balance sheet (lower debt); thus, it is common for firms to attempt to structure leases in a manner that will allow operating lease treatment under U.S. GAAP. One of the most common assets to be classified as operating leases, while taking on more of an appearance as capital financing, is retail real estate. Since the economic life can be very long for a retail location and the lessor generally has no interest in transferring title at the end of the lease, it is relatively easy to structure a deal in such a way that it will be classified as an operating lease.

With the above said, think about industries such appear retailers and restaurants. In these industries, many operating leases are created for the real estate being used in the ordinary course of business. While these leases are structured in such a way that they are treated as operating leases, they do constitute a large portion of the operating expenses and, in my opinion, represent a form of debt financing. I say this due to the fact that they allow the firm the use of an asset in return for a requirement of a fixed payment; sounds a whole lot like debt financing to me.  

From my perspective, it would be necessary to take the expected future payments of these leases (in many cases this figure can be found on the firm’s 10-K) and discount them to their present value and capitalize them, adding both an asset and liability to the pro-forma statements. The discount factor should be the firm’s pre-tax cost of debt for the firm (pre-tax since the payment being discounted is a pre-tax item). In addition, these payments should no longer be treated as lease expense, but as interest expense. The net effect of this process would be an increase to EBIT, EBITDA, and the weight of debt in the WACC calculation.

Since the EBIT, EBITDA and WACC all play a very important role in a DCF valuation, this process can be key to an accurate calculation in some cases. Is this always right? As with most valuation topics, there can be a long debate about how valid this theory is. However, I do believe that in certain industries that rely heavily on operating leases, not creating pro-forma financials with these operating leases capitalized will create an error in your final valuation.

As a last note, while I believe this process to be an accurate treatment for many operating leases, in cases where the operating leases do not represent a major portion of the firm’s expenses, I do not believe this process will yield a material difference the final valuation. 

Book Review: The New Financial Deal

Title: The New Financial Deal: Understanding the Dodd-Frank Act and Its (Unintended) Consequences

Author: David A. Skeel

Publication Date: 12/07/2010

Publisher's Site            Amazon

-------------------------------------------------

David Skeel’s The New Financial Deal: Understanding the Dodd-Frank Act and Its (Unintended) Consequences provided a basic overview of the Dodd-Frank Act that lacked in-depth analysis and rigor. Skeel, a Professor of Corporate Law at Penn with an expertise in Bankruptcy and an extensive Curriculum Vitae promises to disseminate the Wall Street Reform and Consumer Protection Act in a meaningful way while also providing his suggestions to correct major flaws he sees within this piece of legislation. While both of these promises are kept, neither are done so in a manner that yield significant value to the reader.

Skeel begins with his own explanation of government intervention within the financial system ranging from the marriage between Bear Sterns and JP Morgan Chase, to the collapse of Lehman Brothers and the “bailouts” of American International Group (AIG), Chrysler and General Motors. This includes basic explanations of the events that occurred and his opinions on their flaws. A brief outline on the original drafters of the bill, including the involvement from Timothy Geithner, Henry Paulson, Ben Bernanke, and of course Christopher Dodd and Barney Frank, was also included.

After laying out the basic framework for the Dodd-Frank Act, Skeel then proceeds to break down the content into key regulatory reforms enacted under this law. This include the new derivative reforms, capital requirements, Consumer Protection Agency, new resolution tools at the FDIC’s disposal, and international regulations changes. Each of these topics were broken down into bite-sized sections that provide a basic overview of what the law will mean and its implementation.

Skeel also introduces his imaginary example bank referred to as Bank of the World, a massive would-wide financial conglomerate. He uses this example bank through multiple sections to demonstrate how the law would hypothetically impact the firm under a given set of circumstances, particularly the negative impact it may have.

Lastly, Skeel provides two chapters that give his opinion on changes that could be made to the existing law which, he claims, would have a profound impact on undoing a large part of the damage created by Dodd-Frank. This includes changes to treatments of derivatives under the current bankruptcy law and resolution constraints under the FDIC.

While the author did provide a basic overview of the Dodd-Frank Act, he failed to provide any deep explanations that yield useful understanding. This theme was kept constant through the book and was especially prominent in his chapters of proposed changes. I do understand that this piece of legislation is far too large to provide a full detailed explanation; however, I believe that the author failed to provide enough information to educate the reader in a manner that would allow a reasonable understanding of the topic. Through the text, the author makes clear that he believe much of the Dodd-Frank Act is harmful but yet fails give any more than a simple reasoning why. How can one be expected to agree with his point of view without being provided with a strong argument for the case? Being a former student and current professor of law, one would expect this to be common practice. If the author wants his readers to agree with his point-of-view, more emphasis should have been placed on explanations and understanding.

My particular area of concern was his proposed changes. I can confidently say that I do not have enough information to form an opinion on whether or not these changes would have any meaningful impact. The author does a great job at outlining what he proposes as changes, but fails miserably in providing any explanation of why these changes would prove to be positive. He simply provides a short explanation of how his hypothetical bank, Bank of the World, would be impacted differently under his changes versus the current law and how these would be positive. However, I do not believe that providing a simple one situational example can shed any real light on the impact of his proposed changes. I believe the author needs to revise this piece of work and provide a more rigors analysis of how his proposed changes would improve or fix the Dodd-Frank legislation.

On a bright note, I do believe the author used his extensive academic background that focuses on corporate law to provide useful insight into the benefits associated with bankruptcy. A great deal of reference was made to how current bankruptcy law could have resolved with many of the situations that Dodd-Frank is attempting to deal with. The author also indicates that he would like to see changes in the current law that provide preferential treatment to derivatives in bankruptcy proceedings. This was great insight from an individual with great expertise in this field.

In conclusion, David Skeel has created a great framework for what could have been a very informative book. I believe that the author would create a significantly stronger piece of writing by expanding on his current edition. While I do believe that I have gained a better understanding of the Dodd-Frank through reading this text, I would have liked to have gained a better understanding of the true implication of the law. While I am not overall satisfied with this book, due to its relatively short length and semi-useful information, I do recommend it as a read for anyone interested in the Dodd-Frank Act. 

30-Year Return Probability Simulation

Ahh, the wonder of statistics; a great tool they can be while remaining a complete mystery to many. Now take basic statistical methods and couple them with the power of Excel and what do you get? An interesting simulation! Okay, some might really question my motives for completing this; however they are actually quite simple, I am seeking to improve my modeling and simulation skills within Excel and what a better way than to practice.

I began the process by determine what I wanted to simulate, in this case it was the expected return of five assets over a thirty year holding period. Why five assets over thirty years? In all honesty, they were arbitrary; essentially they served as a simple basis for the creation of my model. In addition, the inputs that found in the download file have also been arbitrarily chosen for illustrative purposes only.  

After getting my basic idea down, I outlined how the simulation would work. I wanted to use expected return and risk as the metrics for each individual investment. Expected return would be measured as a percent change on an annual basis. Risk would be measured as the standard deviation of the expected return. In addition to these two important asset specific measures, each asset would be assigned a weight based on their allocation in the portfolio as a whole. Lastly, to help better represent the expected return on a monetary basis, a starting portfolio balance would be necessary. While a monetary measure was not essential, I felt it would allow a better “feel” for the expected returns as opposed to solely a percentage change being displayed.

The next step would be to create a random expected return for a single year, then repeat this over thirty years and compound the growth. In Excel I used the “=NORM.INV” function with requires the following syntax (probability,mean,standard_dev). The probability portion corresponds to the location on a normal distribution. The mean in this case would be the represented by the expected annual return for each respective asset. Standard_dev would be the assigned risk measure of standard deviation of return for each respective investment. By using the “=RAND()” functions, I was able to generate random numbers between 0 and 1 which represent probabilities. Now would be a great time for a quick Statistics-101 review.

A normal distribution is a commonly occurring continuous probability distribution in which data is equally distributed above and below the mean in a bell curve pattern. The central limit theorem states that under mild conditions the mean of randomly selected independent variables will be approximately normally distributed. For this model we assume the returns of each asset will be normally distributed. This essentially means that 99% of all returns will fall within plus or minus three standard deviations from the mean or expected return. This theory is widely used in practice with what is referred to as a normal distribution table. This table shows the relationship between the number of standard deviations above or below the mean and the cumulative probability of this occurring. For example, there is a 1% chance of experiencing a return that is three standard deviations below the mean. Since cumulative probability is used, a 99% probability would represent three standard deviations above the mean; this reflects that 99% of all occurrences fall below this level.

Back to the simulation. By using the excel functions as described above, a random probability will be drawn for each annual return for each individual asset. The Excel formula then takes this random probability and matches it with the respective location on the cumulative normal distribution table; this will yield the number of standard deviations above or below the mean the random sample will fall. Next, this randomly determined number of standard deviation is then multiplied by the standard deviation of returns for each respective asset and then added to the expected (mean) return to yield a random annual return for each asset.

To help better explain this, let us walk through an example. We will use “asset X” which has an expected return of 10% and a standard deviation of returns of 3%. Following the central limits theorem, 99% of all returns for this asset will fall between plus or minus three standard deviation or in this case 9%. Thus, it can be said with 99% confidence that the expected return will be between 1% and 19% (10-9=1, 10+9=19). Now if our random number generator in Excel returns .24 we will look this number up on a cumulative normal distribution table and find the corresponding standard deviations to be negative 0.0948. Next, we will take this number and multiply it by the standard deviation of 3% to get negative 0.284%. Lastly, we will add negative 0.284% to the expected return of 10% to get a random return of 9.7156%.

Clearly, this would be a dunging task if done with manual calculations, luckily we have Excel. This simulation completed the aforementioned process of determining a random return for each asset each year over thirty years. For each year, the respective return on each individual asset is weighted by its allocation within the portfolio to determine the total expected annual return based on the random returns. Finally, the ending value of the portfolio is determined based on the starting balance multiplied by one plus the annual return. This is completed over thirty years to determine the ending portfolio balance after thirty years of random returns calculated from the expected return of each asset and the respective standard deviations of return.

This process of 150 random returns based on expected return and risk for each respective asset yielded a possible outcome for the portfolio. While this would give us a single possible estimate, I wanted to allow for a deeper analysis of total possible outcomes. To do this, I used Excel’s Data Table function to create 1,000 replications of this process. This causes Excel to create the 150 random variables needed to calculate a possible outcome for a single set of thirty years and record the ending value as one data point then complete the process again to get a second data point, so on and so forth. Clearly, this would not be feasible to complete this process 1,000 times without the use of Excel; a total of 150,000 calculations were made.

After completing the simulation of 1,000 possible ending balances for the portfolio, I then calculated the mean value of the possible ending balanced of the portfolio, this served as the expected ending balance; the standard deviation was also measured off this sample. The compound annual growth rate (CAGR) was calculated by taking the expected ending portfolio balance and dividing by the beginning balance to determine the delta then taking the 30^th root (for the number of years of returns being calculated).

Lastly, I wanted to also include a visual illustration of the return distributions using a histogram. I chose to use 201 bins with the expected return being the 101^st bin. By using 201 bins, I was able to have 100 bins above and below the expected return. To determine width of each bin, I took the calculated standard deviation of the returns and multiplied it by two then divided it by 100. This allows the histogram to display return that fall within plus or minus two standard deviations of the mean; according to the empirical rule of normal distribution, two standard deviations will represent approximately 95% of all data points. 

-----------------

While this serves as a simple explanation of the process used to complete this simulation, I intend on creating a second post analyzing the results. I have included a link to download the simulation below. Enjoy.

Excel File Download.

About This Blog

Everyone, or in this case everything, deserves some form of an introduction. With this said, I would like to introduce you to my blog. My name is Alex Drost and I am passionate about business, particularly accounting and finance with a deep interest in valuation; by many accounts, I am a “nerd”. Many times, I find myself wishing I had someone to discuss a particular business topic with, but often fail to find an interested listener. To remedy this situation, I have embarked on the journey of blogging. While there will be no one single topic, the overall theme of my writings will revolve around my passions for accounting and finance. I invite you to subscribe to my blog and follow me through my writings; maybe, just maybe you can be that interested listener I am seeking.