本文分析了一个公开的爱尔眼科财务模型，指出了其中的问题，包括Working Capital的错误、Balance Sheet未配平、Debt Schedule缺失、DDM和DCF估值错误，以及敏感性分析未使用Data Table。建议在收到此类模型时，应谨慎评估并考虑从零开始重建模型。
友情提示：阅读本文时配合模型查看效果更佳，模型领取方式见文末。
本文中点评的爱尔眼科的模型是小马通过公开渠道获得的资料，这类资料常出现于网上流传的“干货分享 - 20G 大礼包”中。这些大礼包里的模型，要么是财务数据停留在99年-00年的，要么是一些北美教材里面附带的excel 训练材料，参考价值不是很大。
今天这个爱尔眼科的模型，已经是其中相对较新的模型，且格式类似于一些研究所出品，因此，我们今天就以它为例，给大家说说收到其他人(比如投行或者标的公司)发来的模型应该如何去看。
1. 本次被诊断模型概览
首先，模型内容请看以下截图：
Output 页面
损益表
资产负债表
估值
敏感性分析
由于篇幅原因其他的几个tab 暂时不贴出了。
2. 被诊断模型各个tab之间的逻辑关系
整个模型一共有12个tab：
Home
Income Statement
Balance Sheet
Cash Flow Statement
Working Capital
Income – 即为revenue buildup
Product – 根据产品线对于营收分类
Valuation
Competition
Assumption
Analysis
Annual Report
这么多个tab，它们互相之间是什么勾稽方式？下面，小马就先给大家总结一下模型的内部逻辑。
以下2个tab 是模型的input：
Annual Report tab 用于填入历史年报数据
Product tab 用于填入年报里的具体营收的拆分
以下tab 是模型的核心假设和control 页面
Income tab 可以根据自己的假设设定未来各个细分营收以及其对饮应收成本的增长率，为control 页面
以下这些tab 是模型的核心建模页面
Income statement，
balance sheet
cash flow statement
Working capital tab 根据根据周转天数预测了未来的working capital；以及有个简单的PP&E buildup (gross PP&E – depreciation = net PP&E)
Valuation tab 即为DCF 估值的计算
其余tab 为非核心tab，可以忽视，例如：
Competition
Analysis
Home
3. 被诊断模型的审阅
打开模型后的直观感受是，模型的格式还算简洁明了，但是在很多个tab都隐藏着一些假设，这些全都是黑色字体，找起来比较费劲。因此，我们首先使用F5快速定位单元格，找出所有
Hardcode的数字：
第一步，按F5打开窗口如下：
第二步，点击窗口里的左下角special，打开后续窗口
第三步，在窗口里选中constants，并选中下面的numbers：
第四步，点击OK，就会选中所有的hardcode的数字，就可以一键全部调成蓝色了
接下来便是检查的模型的时间了。有兴趣的同学可以先自己对于这个模型进行一下找错，然后再看下面的答案。
4. 被诊断模型问题
4.1 Working Capital 错误
Working capital tab同时包含了对于营运资金和固定资产的的计算以及预测。Working capital的话都是基于周转填数对于未来的预测，基本正确 – 但是这里要提醒大家的是，模型里的对于未来预测时使用的turnover days 全部和前一年一致，但其实一般用前三年平均的turnover days 是更加常见的。
下面的固定资产部分，这里的错误就很多了。模型里不管是在建工程和在建工程转固竟然都是hardcode 的0， 累计折旧是hardcode 的10。
这是什么意思呢？模型中公式的意思是：固定资产为去年的固定资产+在建工程转固(数额为0)-累计折旧(每年假定增长10)。所以根据这个模型的逻辑，固定资产在未来不会增加，且每年只能折现10，也就意味着400的已有固定资产可以折旧40多年。
下面是模型里的固定资产截图：
正确的做法应该是：假设每年固定资产按照一定的比率增加(如果有管理层预测更好)，折旧按照15-25年来直线折旧；特殊情况下，也可以使用加速折旧，或者在自然资源行业可以按产量占据储量的百分比折旧
4.2 Balance Sheet 没有配平
这个算是一个比较大的错误。打开balance sheet 看到row 4-6 的这个区域，截图如下：
这个模型里竟然有一行叫做财务平衡项。这是什么意思呢，简单来说，就是反正模型配不平，就直接在上面加一行没平的差值，然后hardcode 进去用来强平模型。
这里必须需要强调，这种做法是十分不规范的。希望大家不要采用这种方式来强行配平balance sheet。
4.3 Debt schedule 完全没有搭建
模型中缺失了一个标准debt schedule的搭建。一个正常的
debt schedule应该包括：
Beginning balance：
Debt repayment
Debt drawdown
Ending balance
下图模型里的这种方式显然是不合格的：
4.4 估值部分出现重大失误
估值部分同时使用了DDM和DCF，首先我们看一下DDM。DDM (Discount Dividend Model) 在实务中运用的还是比较少见的，主要原因是在中国能稳定发放dividend 的公司寥寥无几，而模型的关键前提假设就是公司的分红十分稳定。
下面是模型里关于分红率的假设：
从原模型里的假设看出，模型里假设第一阶段的股利分发为26%。小马细查了下历史上公司的股利分发，分别为2009年5%， 2010年44.3%， 以及2011年25.5%。对于这种股利分发不在一个水平的公司，小马不建议使用DDM，得出的估值以及股价肯定是不准确的。
接下来我们再来看DCF页面。DCF的假设是这样的。
不知道大家有没有注意到，在DDM和DCF里使用的贝塔的数字并不一致 - DCF里的beta假设是1.1，而DDM里用的是0.8。这种同一页出现不一样的数字，肯定是需要添加批注解释的。
不过，错误远远不止这些。我们注意到，在DCF估值里使用的WACC只有5%，和债务成本一致，而且债务比率显示是100%，和真实比率不符。很明显，这是一个错误：
我们查了一下债务比率的公式(如上)：
(短期债务+ 长期债务)/( 短期债务+ 长期债务 + 股本*Home tab E1)。
那Home tab E1是什么呢？
一查原来是“爱尔眼科”……这个失误真是非常严重了。
此外算债务比率时使用股本也是不对，应该要用book equity 而不是share capital
如果把失误加入的“Home！E1”去除的话，WACC立马升到了9.0%。
4.5 Sensitivity Table 没有使用 Data Table
最后在Val tab还有两个敏感性分析，画风也是非常迷。虽然是敏感性分析，但是完全没有用data table！想知道怎么做的吗？请看下图：
原来是使用公式挨个计算出来的，真是辛苦了。人类的正常操作，应该是直接使用excel 内置的what if analysis – data table(模拟运算表)。
如果当你收到了这样的一个模型，一般我会建议大家放弃在其基础上修改，而是直接从零做起会比较快。
5. 从零开始的模型搭建
我们重新搭建了爱尔眼科的财务模型，财务数据更新至2018年，具体包括：
Revenue Buildup,
Cost Buildup,
Income Statement,
Balance Sheet,
Cash Flow Statement,
PP&E Schedule,
Debt Schedule
WACC Buildup
DCF Valuation和Sensitivity Analysis
Revenue Buildup
Cost Buildup
Income Statement:
Balance Sheet:
PP&E and Debt Schedule
Cash Flow Statement
Discounted Cash Flow and Sensitivity Analysis
更多实用性的估值建模技巧，欢迎参加小马的线上课程/线下面授课程。
END
想要获取文中模型，请在公众号后台回复A16。
文章中涉及到的各种Excel 公式技巧、模型审阅方法都会在我们的【估值建模课程】中进行介绍，想要了解更多关于课程的内容，请点击下方课程介绍链接或咨询课程顾问。
更多课程信息请参考下方链接
【火热报名中】
【估值建模课程】北京线下班课开始报名 + 今日内推机会分享
课程详情请联系课程顾问
【往期课程回顾】
一顿火锅，引发一场12小时特训
北京4月末线下班顺利结课
小马估值的创始人是几位来自于北大光华、哥大、沃顿等院校的校友，曾经供职于Goldman Sachs、JP Morgan、Rothschild、Man Group等顶尖投行、PE和对冲基金。
在日常工作中，大家发现很多candidate的理论知识都很不错，或者学习过CFA、CPA，但是在实习工作中需要用到的东西仍然要从头教起，从Excel的公式函数快捷键、到Trading Comparables/Transaction Analysis，从什么是Enterprise Value和Equity Value、到如何搭建一个DCF 现金流折现模型，可能这些东西刚刚教会，三个月的实习期也快结束了……
因此，我们结合自己入职时的Training经历，将各个投行的培训内容提炼重点，设计成为期两天(12小时)的实战性课程，以期帮助正在求职的、已经拿到offer但是对实习/工作要求感到难以胜任的同学迅速掌握估值建模的基本方法和技巧。
直接拉到底的同学，再写一遍获取方式给你们，请给贴心的阿呆点个在看，右下角的朋友让我看到你们的小手手！
END
想要获取文中模型，请在公众号后台回复A16。
1609
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
