Here i’d like share a financial report analysis model i developed mainly in the past two months.
Files stored on Google Drive:
Folder FRAM https://drive.google.com/folderview?id=0B4E833-fCIhYNFpkTWgxbmxlZkE&usp=sharing FRAM用戶手冊(請閱此先).docx https://drive.google.com/file/d/0B4E833-fCIhYZ1lqRG5jeTI1Qkk/edit?usp=sharing FRAM User Manual (Readme first please).docx https://drive.google.com/file/d/0B4E833-fCIhYSE1qcGZfUmVISVk/edit?usp=sharing FRAM (6y) -ShenKai(002278.SHE).xlsx https://drive.google.com/file/d/0B4E833-fCIhYSUNscVFQSDdpZnc/edit?usp=sharing FRAM (10y) -ShenKai(002278.SHE).xlsx https://drive.google.com/file/d/0B4E833-fCIhYT1ZKbi1DRW9CV1E/edit?usp=sharing FRAM (16y) -ShenKai(002278.SHE).xlsx https://drive.google.com/file/d/0B4E833-fCIhYcFBnYnZxRGRUSmc/edit?usp=sharing
(16* 0.618 ≈ 10, 10 * 0.618 ≈ 6) Files stored on i-share of sina.com:
http://ishare.iask.sina.com.cn/f/38222013.html
User Manual Thank you, Supernatural powers! I believe there are Supernatural powers who are almighty and helping me working on this file. Many incredible ideas which are beyond my intelligence run to my mind, especially when I am confused. This file owes much to many. A special thanks goes to the support of Mr. Yuri Shaw「肖 猷傑 先生」, a software engineer. His unintended words guide me to find out the supporting and the reason of the current and non-current framework of financial reporting. For example, whether cash in bank and bank deposit with fixed term, deferred tax assets and liabilities (DTA & DTL) should be reported as two parts, and later I found it discussed in an amendment to “IAS 12 Income Taxes”. Without his advice, this model would be rewritten once again. I thank Mr. Jun Soo「蘇 俊 先生」, also a software engineer, teaches me the design principle of separating input, process and output, and this solves the great difficulty of the structure of this file, greatly push the work forward. There are two person with the same name “Jinhua Lee” I must thank to. The discussion with the first Mr. Jinhua Lee「李 金華 先生」 (CICPA, helps me make clear about many accounting issues and the structure of Managerial Financial Report, especially the Supplement Notes to the Statement of Cash Flow. The other “Jinhua Lee”, Mr. Ivan Li「李 金華 先生」, a software engineer, teaches me knowledge and techniques about data base. Spreadsheet is in fact a database. I have benefited from Mr. Cunhua Chow「周 存華 先生」, a researcher, who provides me with the important raw material for testing the model, and Mr. Zhou Teng「鄧 舟 先生」. The discussion with them helps me realize the techniques, art and difficulties in valuation. I am grateful to Mr. Eric Cheung「張 楊 先生」, CICPA, and other friends, discussion with them help to make this model much better. Thanks to Annny Cheung「張 珉 女士」, Ms. Xiaolan Mo「莫 曉嵐 女士」, Ms. Suzy Han「한 수지 님」, Mr. Zhihao Xu「徐 志豪 先生」, lawyer, and Mr. Lexiang Yu「于 樂翔 先生」, CICPA, the joy and encouragement you give me always fill me with pleasure and power. Thanks to my colleagues and leaders, and my previous companies, from which and in where I learn a lot. They are my treasured experience in my life. Last but not least, I must thank my family, they provide great support for my efforts. Lincoln Hwang January, 2014 QQ: 1804165734, Email: 0. Warnings 1. It is strongly recommended to run this file on Microsoft Excel 2010 or above. In fact, this model was produced on Microsoft Excel 2013. 2. It is strongly recommended to read this “User Manual” through before using it. Also making sure that the cell you are changing do not contain formula is very important. Because only several worksheets need input, and not all cells in those worksheet need input, either. These would be illustrated in the sections followed. 3. Do not open other files since this model is so heavy that working on multiple files might make your computer slow. This file is heavy mainly due to the worksheet “TB” which contains many columns to store data for many years. One way to reduce the size is to delete unused columns of years. Three versions would be provided here, containing 6 years, 10 years and 16 years respectively. (16 * 0.618 ≈ 10, 10 * 0.618 ≈ 6) 4. This model could not be simplified simply using the function “Chinese Conversion” in Microsoft Excel, because there are formulas containing traditional Chinese which would be out of function after conversion. 1. Function and Limits This “Financial Report Analysis Model” (FRAM) is a workbook made from Microsoft Excel software. English version is available for every worksheet. To do this, you could unhide the columns to the right of the column in Chinese to get the column in English. This file is structured as the following chart shows. As per “Application Guide to CAS No.30 Presentation of Financial Statements”, financial statements and notes are classified into several format, i.e., general corporate, commercial banks, insurance companies, securities companies and other business type. This “Financial Report Analysis Model” (FRAM) only applies to General Corporate format. All the financial report items and accounts come from “General rules for the information disclosure of public offering companies No. 15 - financial report” issued by CSRC, which is the least requirement for financial reporting disclosure. And this also helps to make the information required in this FRAM available. As some info could not be found in financial report disclosure, and the Managerial Report of Cash Flow could not be accurate. And the solution here is to put the difference of “Entity Cash Flow” and “Financing Cash Flow” into the item “Others” in “Entity Cash Flow”. Because “Financing Cash Flow” are more easy to be accurate. The idea of treating the difference like this comes from the reporting of “National Income and Product Accounts (NIPA)” which use “Statistical discrepancy” to reconcile the income and product sides of the accounts. Due to the limits of accounting and financial report analysis, sometimes too much calculation would lead to “precise errors” which are accurate but in fact wrong. Less is more. For managerial financial reports, the financial component percentage need estimation. So, Fixed-base MFR, Chain MFR and Homomorphous MFR are simplified. Besides, forecast financial reports and valuation are omitted. The main reason is that this model is for general purpose while forecast and valuation is a job needs customized specification. For example, the estimation of PPE (Property, Plant and Equipment) and related depreciation depends much on the info in investment, and the transformation of CIP (Construction in Progress) and their estimated economic life. Besides, for agricultural companies, the estimation of Productive Biological Assets and biological transformation. In addition, the calculation of β (beta), the estimation of interest rate, inflation rate and equity capital cost in the following 7-10 years are tough but required in some models. All these add to the complication of the valuation. This work should be customized for specific company or industry which is not the intention of this model. However, this model provides the ready-to-use data for forecast. For example, Microsoft Excel provides functions like “Forecast”, “Trend”, “Slope”, “Intercept” and others which could be directly apply to the data in Financial Reports and Managerial Financial Reports. They are useful to calculate the parameters used in regression. Many more valuable valuation models and templates are available in this website, Damodaran Online, New York University, 2. Brief Introduction 2.1. Brief Introduction to Financial Report Analysis (FRA) – Benefit-cost analysis, the limits and cost of FRA The limits of financial report analysis could be primarily classified into three causes below. 1. Limitation of Accounting. Standard accounting data could not provide the whole info about the company, because many aspects are not recognized, measured and reported in accounting. 2. Dirty raw materials. Many companies are reluctant to report accurate and detailed financial data, even audited, for various reasons, like making accounting not very accurate to add to the difficulty of tax inspection so as to avoid tax burden, business secrets and so on. 3. Poor financial index selection, calculation and interpretation. There are various financial indices could be used and calculated to reflect many aspects of operation, financing and investment. Hence, the selection of financial indices is a serious and hard job. Sometimes even the right financial indices being selected and calculated, knowing their presumption and limits and how to interpret them requires many techniques and much experience. Due to the fact that many important aspects are not reported in financial data, and the limit of financial analysis, the benefit is also limited. The low benefit and the limits of financial report analysis make it unwise to spend too much on analysis. Hence, a general and “one-stop” financial report analysis is much of necessary. 2.2. Brief Introduction the “Financial Report Analysis Model” (FRAM) This FRAM contains several Worksheet as follows. All those worksheets need input have been highlighted in light green. There are total 18 worksheets in this workbook and only 5 worksheets need input. All other worksheets presents data and need no input at all. Note 1 The “Financial Report Selector” on the top of worksheet “3FR” provides an option to choose one of the Original / Audited / Managerial Financial Reports to be analyzed. Be caution that all the financial indices, managerial financial indices, and other worksheets are based on the report you selected in worksheet “3FR”. Note 2 1. The adjustments are classified into adjustments made by clients and by auditors, hence there are two ending balances. The debit and credit side of client adjustments are composed of two parts, as shown in the formulas. The First ones are the adjustments made by client herself/himself. The second ones are the adjustments made by auditors and then are accepted by clients. The debit and credit side of auditor adjustments are all the adjustments made by auditors, no matter whether the clients accept or not. 2. Please make sure that all the adjustments are made to adjust the detail account listed in the worksheet “OFR” (Original Financial Report), i.e., those items or accounts that need input in worksheet “OFR”. Or else, the adjustment entries might not be posted into worksheet “TB”. Note 3 1. While calculating financial indices, ROE (also known as RONA, Return on Net Asset) is simplified as “Return on Ending Equity” (ROEE), which take the ending balance of equity as denominator, not “Return on Weighted Average Equity” (ROWAE) as defined in Accounting Standards. Similarly, while calculating managerial financial indices, RONOA is defined as “Return on Ending Net Operating Asset of Ending Balance” (RONOA), not “Return on Weighted Average Net Operating Asset” (ROWANOA) as theory implies. This simplification methods apply to many other indices listed, just to simplify the calculation. 2. Some issues about Turnover. The ratio of times of turnover provides an index to measure the efficiency of transformation. When we are talking about turnover, there are two transformations happen, one the transformation from others to object, the other is the transformation from object to others. For example, suppose during a period, the headcounts of opening, roll-on, roll-off and closing employees are denoted as EO, EN, EF and EC respectively. Then the below relation exists. E_O+E_N-E_F=E_C Two indicators are available to access the turnover rate of employees, defined as below. Turnover of roll on T_N≡E_N/(Average Headcount of Employee During This Period)=E_N/(((E_O+E_C))⁄2) Turnover of roll off T_F≡E_F/(Average Headcount of Employee During This Period)=E_F/(((E_O+E_C))⁄2) For the same reason, the turnover for assets (like Account Receivables, Inventories, etc.,) and liabilities (like Account Payable, Account Paid in Advance, etc.,) should also be defined in these two aspects. Hence, the cumulative amount of debit and credit side should be used in the calculations. This is the principle in deciding which item to use in calculation. You should keep in mind the presumption, meaning and indication of the financial indices. However, in practice, we would use other item instead of the cumulative amount of debit and credit side. For example, sales, instead of the cumulative amount of debit side of Account Receivables, is used in calculation of AR turnover and DSO as approximation. That is ok for most cases. As illustrated above, the cumulative amount of debit side of AR should be used in calculating turnover of transformation from goods sale and service render to Account Receivables, while the cumulative amount of credit side of AR should be used in calculating turnover of transformation from Account Receivables to Cash. 3. When Calculating turnover days, we distinguish net working days from calendar days. The turnover days calculated with net working days are used for operation performance measurement, for there is a significant difference of 16.67% between 6 workdays per week and 7 workdays per week. The turnover days calculated with calendar days are used for finance performance measurement, for calendar days are often used in interest calculation. Of course, there are also significant difference among companies which rule their employees to work over time, work two shifts or three shifts a day. However, there are various difficulty in measuring these effects. Note 4 To simplify the input of the financial component proportion into the “MFRTM” (Managerial Financial Report Transform Matrices), it is assumed that the financial component proportion of provision, impairment, changes in the fair value and etc. is the same as the financial component proportion of the original value for each item/account. (As shown in the formulas, what need your input is only the financial component proportion of original value.) However, this need not be true. It is only true when provision, impairment, changes in the fair value and etc. are also proportional to their original value. For example, if bad debt provision is calculated by Markov Chain Method instead of Percentage Method, then bad debt provision is not necessary proportional to their original value. The financial component percentage needs estimation. For example, the operating “Cash in Bank” could be estimated by calculating the average turnover of net cash flow from operating activities over cash in bank before listing, then the remaining part is financial component. The percentage in the example is calculated by this method.