Analyst Corner - Calculating A Public Firm's Beta

Hello again! Today I will write quickly about how to calculate a publicly traded firm's Beta, for CAPM "cost on equity" purposes.  It's pretty simple so I will keep it short and sweet.

First, you will need to have Microsoft Excel handy.  This is the hardest step actually - the rest is plug and play.

Make one column of data showing the monthly average price for the stock you're interested in, for a number of years ending in the current year.  How far you go back will depend on what the market did during that time (you don't want data selection bias) and what the stock did during that time (did the company change its core business from one sector to another, if so then you're most interested in the current business period).  Then, to the right of this data, do simple division to get the percentage changes month to month (simple division of the current month divided by the previous month, minus 1).  These monthly percentage changes will be part 1 of your source data for the Beta calculation.  Let's call them Data Group A

Make a second column of data showing the monthly average S&P Index quotes for the same period.  Or whatever index best applies to the country or business sector of the stock you're looking at.  The index should be representative and not taken from a different country's stock market.  Like you did for the stock price data above, to the right of all the S&P Index quotes you will add the percentage change of that month versus the previous month (simple division of the current month divided by the previous month, minus 1).  These monthly percentage changes are the second data source for our stock Beta calculation.  Let's call them Data Group B.

The Beta calculation is very simple.  Beta, as many of us know, is simply the Covariance of the individual stock price returns and market returns, divided by the Variance of stock and the market returns.  On Excel this is super easy, there are functions for Covariance and Variance built into the program.

So in this case, you add a cell on the spreadsheet called "Covariance of Stock & Market Returns" and in the cell to the right of that cell, type the text "=COVAR.P" which will then prompt you for the data you want to find the Covariance for.  Highlight all Data Group A with your mouse, and then type "," after you've highlighted it... the comma then moves you to the second group of data modeled in the Covariance function.  Then you highlight all Data Group B with your mouse, and finally type ")" which closes the function.  Hit the "return" key and you'll have the Covariance of Data Group A and Data Group B.  This is the numerator in your Beta calculation.

To get the denominator in your Beta calculation, simply go to another cell and call it "Variance of Stock Returns" and to the right of that cell, type the text "=VAR.P" which will then prompt you for the data.  Highlight all of Data Group A with your mouse, then type ")" to close out the function.  This gives you the Variance of the stock returns.  Repeat the same step for Data Group B and call is "Variance of Market Returns."  Multiply them together and this is the denominator in your Beta calculation.

Beta then, is simply dividing the numerator by the denominator.  Boom you've got your Beta.  If you have a non-listed asset you want to track Beta for, and you have access to good price/value data for the asset, then you can do the same thing for it, and replace the non-listed price/value data in the column to generate the Data Group A.  But beware price smoothing and appraisals as a source for price/value data of non-listed assets, particularly less liquid heterogeneous assets (like real estate, for example).  The data may not be representative or stale and at minimum would need to be unsmoothed in such instances.

Analyst Corner - Calculating A Firm's Enterprise Value

Hello everyone, I am starting a new sub-chain of blog posts that I will put up from time to time regarding financial modeling and analyst fundamentals.  If you have a topic you'd like me to cover - assuming I know it well to speak on it - I would be happy to address it, feel free to write a comment to my post with your request.

How To Calculate A Firm's Enterprise Value
When valuing companies, analysts and investors often must determine their most accurate calculation for the Enterprise Value (EV) of  firm.  By the simplest definition, a firm's EV is the value of its core business activities - the a starting point foundation for arriving at M&A offers, stock price valuations, and so forth.  For companies with liquid, publicly traded debt and equity instruments, the short-hand approach to determine EV is simply to take the market value of all the firm's equity (i.e., the number of public shares multiplied by the price per share), add to that the market value of the firm's debt (also can taken from public exchanges if it's publicly traded), and then subtract cash and cash equivalents (listed in the Balance Sheet).  Put another way, you take all the net assets and the debt of a firm (which is usually used for fixed asset CAPEX anyway), subtract the cash out (since cash is not a unique value to a firm, and if you bought the firm it would be without of this cash anyway), and this gives you the EV.

Simple, right?  Well not always - many firms do not have publicly traded debt and/or equity, and even when they do have one or both of those, often analysts will be looking to see if those items are mispriced in the market.  Such mispricings - aka market inefficiencies - are where the money is made and where analysts demonstrate their value.

Calculating EV By Using The Balance Sheet
The roughest way to approximate a firm's EV is to look at its balance sheet and make some adjustments.  Specifically, there are two adjustments to make.  First, put everything on spreadsheet in front of you, assets on the left side and liabilities + equity on the right side. Then, create a new specific item on the left side and the right side - on the left side the new item, which appears above Fixed assets, is called "Net Working Capital."  On the right side, above the first item that is non-financial long term liabilities (Pension Liabilities for example), create a new item called "Net Debt."

Now comes the first adjustment.  Take Cash and Cash Equivalents (including Marketable Securities) away from Current Assets, and move them to the right hand side of the Balance Sheet, subtracting them from Total Debt of the firm, to arrive at the new line item "Net Debt."

And the second adjustment.  Take Current Liabilities that do not refer to debt (aka operations related Current Liabilities such as Accounts Payable and Taxes Payable), and subtract those from the remaining Current Assets on the left hand side, to arrive at your new "Net Working Capital" line item on the left hand side of the Balance Sheet.

If the firm has no publicly traded debt or equity, this simple process will give you a rough sketch of a firm's EV just using the Balance Sheet.  Note that all items on a firm's balance sheet are generally different from the market value of the same items, because they reflect historic values for the most part - the values as of when they were originally entered to the balance sheet.

To account for valuation discrepancies between balance sheet values and current market values, when a firm has publicly traded equity an analyst can make a slight corrective adjustment to the process above.  He or she can replace the book value of equity on the firm's Balance Sheet with the real market value of the firm's publicly traded equity (taken from Yahoo Finance or Bloomberg, etc.).  This will create an imbalance between the right hand side and the left hand side of the Balance Sheet.  To correct this, the analyst can manually change the left hand item value for "Goodwill" to cover the difference, so that the left and right hand sides match again.  At the bottom of each side, the matching value is the firm's estimated EV.

Calculating EV By Using A Firm's Consolidated Statement Of Cash Flows
Every firm includes a consolidated statement of cash flows (CSCF) in its financial statements.  Cash flows generated by the firm over the defined period of the statement are broken into three easy to separate categories: operating cash flows, investment cash flows, and financial cash flows.

There are many formulas for calculating EV of a firm, and one applies particularly here.  EV can be defined as the sum of all expected free cash flows to the firm (FCF) discounted by its weighted average cost of capital (WACC).  I will discuss WACC in more detail in future post.  For now, the important thing is to know how to get FCF from a firm's CSCF, and then we assume here you've got the WACC handy already.

To estimate a firm's FCF based on its CSCF, the analyst does the following three general things (corresponding to the three sections of the CSCF itself).  For operating cash flows, the analyst generally leaves everything alone and keeps things as they are.  But oppositely, the analyst totally eliminates/disregards all financial cash flows.  For investment cash flows, the analyst makes a few tactical adjustments - all cash flows related to investments and the purchase and sale of financial assets are deleted.  The rest of investment cash flows - the ones related to investment in assets used to produce a firm's business income - are left included.  The analyst adds the operational cash flow to the "adjusted" investment cash flow, and then adds in Interest After Tax (Interest expense multiplied by 1-Tax Rate) to get the FCF.  This FCF can then estimated across future years by an analyst's estimated growth rate, while also discounted by WACC (divided by 1+WACC in year 1, etc.) to get the firm's  EV.

Calculating EV Using A Firm's Income Statement And Balance Sheet Together
Another formula for calculating a firm's EV is:  EV = (EBIT)(1-Tax Rate)-(Change in Net Working Capital)-(Increase in Fixed Assets).  This can be also broken down into: EV = (EBIT)(1-Tax Rate)-(Increase in non-cash Current Assets)+(Increase in non-debt Current Liabilities)-(Increase in Fixed Assets).

With that in mind, if you have the firm's Income Statement combined with its Balance Sheet, you can easily calculate EV using all these line items above.  Just plug and play.  The tax rate is determined by looking at the Income Statement, and dividing the firm's Income Tax Expense line item by its Income Before Tax line item.

I hope this was helpful to someone, if you have other financial modeling/valuation topics you'd like me to cover here I am happy to try my best efforts.


 

What Is Blockchain? How Does It Work?

Blockchain is a term that gets bandied about a lot lately. It has become the "hot" buzzword in numerous different industries including the financial industry, the tech industry, the government public sector, the healthcare industry, the legal industry and elsewhere.  But what does the term mean, exactly?

Simply put, a blockchain refers to a distributed ledger that contains unchangeable blocks of digital data - each block is attached to next one by cryptography, in a chain. It is a way for many people to see the same record of data/events/transactions all over the world in real time, and to request/get things from the system on pre-agreed terms. A distributed ledger is defined by Wikipedia as "a consensus of replicated, shared, and synchronized digital data geographically spread across multiple sites, countries, or institutions." Basically a bunch of data that is recorded/tracked/updated simultaneously so that all users/viewers see the most up to date version at all times.

Distributed ledgers tend to have the following common properties:

• Use of cryptography to attach blocks of data and confirm/reconcile transactions (so that the system is not susceptible to fraud or manipulation)
• Many copies of the ledger (and their simultaneous updates in real time) are available for viewing/tracking by everyone with access
• Different levels of access control (viewing, editing, or both) can be set for users of the ledger - regulators and validators can be defined by the system - features can be set to public or private
• Irreversible - once the ledger is updated and new data is recorded to it, the outcome is permanent and cannot be edited/manipulated later

Blockchain 1.0 refers to cryptocurrencies for cash transactions, while blockchain 2.0 refers to all financial applications except for currency transfers. Blockchain 3.0 refers to all applications outside the realm of currency and finance. See my prior January post on this topic, if you have questions. With this in mind, blockchain 2.0 and 3.0 offer exciting efficiency and cost improvements to business processes via the use of smart contracts.

Many smart contracts currently run on Ethereum. They create automatic, essentially free processes that are autonomous, self-sufficient and decentralized. Pre-agreed events trigger the execution of the contract, and execution and value transfer aspects to the contract proceed in an automatic manner. In this way, companies can remove middle men and costs from their business models to deal directly with counter parties without the need for costly escrow agents, approval/verification gateway service providers, claim validators, etc. By removing middle men, you also reduce the likelihood of errors within a process.

There are even now efforts to use the blockchain to create autonomous organizations, companies and societies. DAO (decentralized autonomous organization), DAC (decentralized autonomous corporation), and DAS (decentralized autonomous society) all use blockchain technology and smart contracts to create autonomously run entities - a virtual crossover if you will between a technological process and daily life.

Blockchain's key value added is that it reduces the need for people to do work - it automates and safeguards sensitive transactions and interactions in a trustless world. The technology is still very much in its early adaptation phase, much like the Internet was in the 1990s, but its long-term impact on businesses and industries may be profound.