Accounting

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsBusiness DevelopmentAccounting

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old February 7th, 2014, 07:57 AM
lqh5961 lqh5961 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Feb 2014
Posts: 3 lqh5961 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 17 sec
Reputation Power: 0
Financial Report Analysis Model (FRAM) Shared

Here i'd like share a financial report analysis model i developed mainly in the past two months.

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.

Worksheet Content Notes Need Input
Part I. Basic Information
CVBI Cover and Basic Information Contains the basic info of the target company Y
CTDF Content and Definition Provides the content of the workbook, and defines some words and abbreviations used in this workbook N
OI Other Information Logs other information not included in financial reports but needed in financial index calculation, like stock price and so on. Y
OFR Original Financial Report Contains "Statement of Financial Position", "Statement of Comprehensive Income", "Statement of Cash Flow", "Supplement Notes to Statement of Cash Flow", "Statement of Extraordinary Income", the calculation of EPS,D-EPS and ROE Y
FRV Financial Report Verification1 Verifies the input in worksheets "3FR" which presents Original / Audited / Managerial Financial Report selected by "Financial Report Seletor" N
Part II. Financial Report Adjustment
TB Trial Balance2 Presents the original and adjusted financial reports, also the adjustments made by both clients and auditors N
AJE Adjusting Journal Entries Logs the Adjusting and Reclassifying Journal Entries Y
3FR Three Financial Reports Provides "Original / Audited / Managerial Financial Report" which could be switched by "Financial Report Selector" on top N
Part III. Financial Report Analysis
FBFR Fixed Base Financial Report Provides Percentage Financial Reports of the selected FR in worksheet "3FR" based on a fixed base year N
CFR Chain Financial Report Provides Percentage Financial Reports of the selected FR in worksheet "3FR" on a chained base N
HFR Homomorphous Financial Report Provides Percentage Financial Reports of the selected FR in worksheet "3FR" in which each year is homomorphous N
FI Financial Indices3 Calculates indices on liquidity, efficiency, financial leverage, profitability, investment return, and indices on liquidity, cash generation, financial elasticity, earning quality of cash flow, also Dupont Analysis, and miscellaneous indices like Altman's Z-Score. N
Part IV. Managerial Financial Report Transformation, Presentation and Analysis
MFRTM Managerial Financial Report Transform Matrices4 Contains a Transform Matrix from Financial Report to Managerial Financial Report Y
MFR Managerial Financial Report (MFR) Presents Managerial Financial Report given the data stored in worksheet "3FR" and "MFRTM" N
FBMFR Fixed Base MFR Provides Percentage Managerial Financial Reports based on a fixed base year N
CMFR Chain MFR Provides Percentage Managerial Financial Reports on a chained base N
HMFR Homomorphous MFR Provides Percentage Managerial Financial Reports in which each year is homomorphous N
MFI Managerial Financial Indices3 Calculates indices on liquidity, efficiency, financial leverage, profitability, investment return, and indices on liquidity, cash generation, financial elasticity, earning quality of cash flow, also Dupont Analysis, and miscellaneous indices of Managerial Financial Reports N

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.

Reply With Quote
  #2  
Old February 7th, 2014, 07:58 AM
lqh5961 lqh5961 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Feb 2014
Posts: 3 lqh5961 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 17 sec
Reputation Power: 0
Could anybody show me how to post url to share the files?

Reply With Quote
  #3  
Old February 28th, 2014, 08:38 PM
lqh5961 lqh5961 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Feb 2014
Posts: 3 lqh5961 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 17 sec
Reputation Power: 0
Financial Report Analysis Model Linked (FRAM Linked) 201402

i have updated the model and sharing links followed.

Updates

Compared with the former version "FRAM 201401", following are the updates in this version "FRAM 201402".

I fell great sorry for the inconvenience and loss caused by the mistakes and poor settings.

"FRAM Linked" and"FRAM" are identical except that, "FRAM Linked" separate the input section from "FRAM" as a single file, and feed "FRAM" with data via links. By doing so, "FRAM Linked" would be more convenient and friendly.

Mistake corrections

1. Correct the mistakes in the formula links in worksheet "MFR".

Setting changes

1. Adjust the formula links of Goodwill net book value and closing balance in worksheet "OFR", to make the model more convenient.
2. Increase the exchange rate decimal digits from 4 to 5. Increase the EPS decimal digits from 2 to 4 in worksheet "OFR", "TB", "3FR" and "MFR". EPS in other worksheets remain unchanged.

Stored on Google Drive.

Folder FRAM Linked 201402


Stored on iask of sina.com

FRAM Linked201402.7z.001

FRAM Linked201402.7z.002

.7z file. Total two files.Please install and use 7-zip (1MB) to unzip the two files together.

FRAM201402.7z

.7z file. Please install and use 7-zip (1MB) to unzip.

Reply With Quote
Reply

Viewing: Tutorialized ForumsBusiness DevelopmentAccounting > Financial Report Analysis Model (FRAM) Shared


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap