|
做可研报告的财务模型，大体的思路就是从假设条件开始，做出三张财务报表，然后再做出项目和资本金的自由现金流量表。这5张表格都做完了，可研报告要求的那些经济分析数据和计算结果也都能做出来了。 基础数据表完成以后，就要开始模型的实际计算内容了。我一般是先做利润表，如果把利润表再拆分，就涉及到收入，成本，折旧等内容。今天我先从成本开始。 新建一个工作表，将工作表的名称改为“制造成本”，做出下面图1的表格。 图1 制造成本表 一、A列 我们先看A列的文字部分。 图1中蓝色框标注的都是公式，没有标注的都是文本输入（就是直接打字进去的）。这样做的目的有两个：一是让整个模型中涉及到的名称前后一致；二是如果基础数据里面的名称内容有修改，表格后面所涉及的有关内容都会自动更新。 后面的文章不会再介绍A列的文本了，如果自己做各表格的时候，尽量遵守名称一致的原则。 下面我们来详细说： 1.单元格A1和单元格A2分别等于“基础数据表”的A1和A3单元格。 2.第8-12行、第18-19行、以及第23-25行的A列中的是公式，分别等于“基础数据表”的各原料、辅料和水电气的名称，也就是等于图2截图中红框标记的部分。 图2 基础数据表 原料及辅料截图 二、D列 先提示一下，为了检查公式是否正确，我一般都会把基础数据表里面的数据先填上，这样后面各工作表编写公式的过程中很容易检查出来公式是否有误。所以，你会看到图2的截图里面数据已经有数字了，不再是0了，这些数据都是我随便编的，不是真实的项目资料，最后计算出来的结论也都没有任何实际意义。 1.年份 图1中第6行的数字，是年份，其实是从0到10，由于篇幅限制，图1的截图只粘贴了前两年。这一行的数据等于“基础数据表”的第9行的运营年份（见图3）。你会看到图1中的B6和C6单元格是0，而不是从1开始的。 图3基础数据表项目时间表截图 2. 原料、辅料、水电气成本 公式原则：= 数量X不含税单价 可以先写一个D8的公式，D8的公式完成以后，用“基础数据表—项目时间表”一文中提到的公式批量填充的方法，完成D9到D12的公式。 3. 消费税 消费税的公式=数量X单位税额 注意，第D13的原料消费税，是用数量乘以消费税的单位税额。使用的是SUMPRODCUT的公式，我会在文章最后单独讲公式。 4. 可抵扣消费税和折旧费 D14的可抵扣消费税和D32的折旧费，用黄色突出显示，因为这两个数据来自后面其他工作表的计算，暂时先空着，用彩色标记出来，等其他工作表计算完成以后再链接过来。 5. 小计、合计 小计、合计都是用EXCEL的SUM公式，把各部分的数字加起来。 注意，各公式计算中都要加上“/10^4”意思是除以10的4次方，相当于折算成万元。因为我们标题上面列出的单位是万元。 三、E到M列 做完D列以后，还是用批量填充的方法，把第2到10年的公式全部填充就完成了整个表格的内容。 后面的文章也不再重复E-M列的方法了，都是批量填充的方法来操作。 附单元格公式 A1的公式：“=基础数据!$A$1” A2的公式：“=基础数据!$A$3” B6的公式：“=基础数据!E10” D8的公式：“=基础数据!$C50*基础数据!$D50/10^4” D9的公式：“=基础数据!$C51*基础数据!$D51/10^4” D10的公式：“=基础数据!$C52*基础数据!$D52/10^4” D11的公式：“=基础数据!$C53*基础数据!$D53/10^4” D12的公式：“=基础数据!$C54*基础数据!$D54/10^4” D13的公式：“=SUMPRODUCT(基础数据!$C$49:$C$55,基础数据!$E$49:$E$55)/10^4” D18的公式：“=基础数据!$C55*基础数据!$D55/10^4” D19的公式：“=基础数据!$C56*基础数据!$D56/10^4” D23的公式：“=基础数据!$C58*基础数据!$D58/10^4” D24的公式：“=基础数据!$C59*基础数据!$D59/10^4” D25的公式：“=基础数据!$C60*基础数据!$D60/10^4” D33的公式：“=基础数据!$C66/10^4” D34的公式：“=基础数据!$C67/10^4” EXCEL公式分割线 1.相对引用和绝对引用 当你使用公式批量填充的方法把D列一直拉到M列时，有没有出现什么状况？比如数字好像很奇怪，或者都变成了0？原本每年的数量和价格没变，怎么公式的计算结果却显示数据变化了呢？ 如果出现上述问题，就说明，你没有仔细看我上面的公式。如果仔细看过，你会发现在引用的数据时，比如“基础数据!$C49”“ 基础数据!$E$49”多出来一个或两个美元符号“$”,这里采用了相对引用和绝对引用。 先说相对引用，我们使用第6行的年份做为例子。 B6的公式“=基础数据!E9”。注意看，这里的E9前面没有美元符号。当我们使用批量填充，横向拖动鼠标从B6一直到M6时，这一行的数字自动变成了0到10。最后一个单元格M6的公式是“=基础数据!P9”。从公式可以看出，随着鼠标的拖动，所引用的单元格的列变化了，行没有变化。也就是说第6行单元格所引用的数据位置会跟着鼠标的变化而变化。这就是相对引用。 至于绝对引用，有两种方式，一种是被引用的单元格的位置固定不变（行和列都不变），另外一种是被引用的单元格的行或者列有一个是变动的。 我们还是先用单元格D8来说明。 D8的公式是“=基础数据!$C49*基础数据!$D49/10^4”。这里只有C和D前面有美元符号。我们把单元格D8的公式批量填充到M8（也就是从第1年到第10年），你会发现M8的单元格的公式“=基础数据!$C49*基础数据!$D49/10^4”没有变化，也就是说只要鼠标是横向拖动，行没有变化，还是引用的49行，而被引用单元格的C和D列前面因为有美元符号“$”,相当于锁定了C列和D列位置，不会随着鼠标横向移动而变化。 下面，我们再把D8的公式纵向拖动，从D8下拉到D12，那么最后一个D12的公式就变成了“=基础数据!$C53*基础数据!$D53/10^4”，你会发现行的数字变化了，列的字母没变。自动从原料1计算到了原料5。 如果我想让被引用单元格的位置不变，比如说表头的A1” =基础数据!A1”，我在A列和行1前面都加上美元符号，公式就变成了“=基础数据!$A$1”，这样一来，即使我在基础数据表的第一行上面加上一行，它的引用还是不变的。如图基础数据表的A1单元格变成了A2，但是制造成本表所以引用的数据还是却没有变化。 注意，绝对引用加美元符号，可以用键盘上面的F4键（多按几次，你会发现什么？），也可以手工输入美元符号，看个人喜好。 2. SUM公式 以单元格D15为例，公式为“=SUM(D8:D14)”。SUM就是加的意思，意思是从D8到D14全部加起来。 如果被加的数字的位置不是在相邻的单元格，数字之间用逗号分隔就可以，如单元格D6合计的公式则是“=SUM(D15,D20,D26,D29,D35)” 3. SUMPRODUCT公式 以单元格D13的公式是“=SUMPRODUCT(基础数据!$C$49:$C$55,基础数据!$E$49:$E$55)/10^4”。我们先忽略“/10^4”，只看前面的数字“基础数据!$C$49:$C$55,基础数据!$E$49:$E$55)”。 我们去基础数据表的这部分看一下。 基础数据!$C$49:$C$55表示C列是数量 基础数据!$E$49:$E$55表示E列是消费税单位数额， SUMPRODUCT公式的意思是：先把每行的两个数据相乘，然后再把各行相乘的结果相加，如下列算式
|
|
|
