制造成本表
做可研报告的财务模型,大体的思路就是从假设条件开始,做出三张财务报表,然后再做出项目和资本金的自由现金流量表。这5张表格都做完了,可研报告要求的那些经济分析数据和计算结果也都能做出来了。
基础数据表完成以后,就要开始模型的实际计算内容了。我一般是先做利润表,如果把利润表再拆分,就涉及到收入,成本,折旧等内容。今天我先从成本开始。
新建一个工作表,将工作表的名称改为“制造成本”,做出下面图1的表格。
图1 制造成本表
![](http://n4.ikafan.com/assetsj/blank.gif)
一、A列
我们先看A列的文字部分。
图1中蓝色框标注的都是公式,没有标注的都是文本输入(就是直接打字进去的)。这样做的目的有两个:一是让整个模型中涉及到的名称前后一致;二是如果基础数据里面的名称内容有修改,表格后面所涉及的有关内容都会自动更新。
后面的文章不会再介绍A列的文本了,如果自己做各表格的时候,尽量遵守名称一致的原则。
下面我们来详细说:
1.单元格A1和单元格A2分别等于“基础数据表”的A1和A3单元格。
2.第8-12行、第18-19行、以及第23-25行的A列中的是公式,分别等于“基础数据表”的各原料、辅料和水电气的名称,也就是等于图2截图中红框标记的部分。
图2 基础数据表 原料及辅料截图
![](http://n4.ikafan.com/assetsj/blank.gif)
二、D列
先提示一下,为了检查公式是否正确,我一般都会把基础数据表里面的数据先填上,这样后面各工作表编写公式的过程中很容易检查出来公式是否有误。所以,你会看到图2的截图里面数据已经有数字了,不再是0了,这些数据都是我随便编的,不是真实的项目资料,最后计算出来的结论也都没有任何实际意义。
1.年份
图1中第6行的数字,是年份,其实是从0到10,由于篇幅限制,图1的截图只粘贴了前两年。这一行的数据等于“基础数据表”的第9行的运营年份(见图3)。你会看到图1中的B6和C6单元格是0,而不是从1开始的。
图3基础数据表项目时间表截图
![](http://n4.ikafan.com/assetsj/blank.gif)
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,但是制造成本表所以引用的数据还是却没有变化。
![](http://n4.ikafan.com/assetsj/blank.gif)
![](http://n4.ikafan.com/assetsj/blank.gif)
注意,绝对引用加美元符号,可以用键盘上面的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)”。
我们去基础数据表的这部分看一下。
![](http://n4.ikafan.com/assetsj/blank.gif)
基础数据!$C$49:$C$55表示C列是数量
基础数据!$E$49:$E$55表示E列是消费税单位数额,
SUMPRODUCT公式的意思是:先把每行的两个数据相乘,然后再把各行相乘的结果相加,如下列算式
+ |
7000x 0 1800x1218 170x1218 27x2105.2 733x2105.2 3x0 0x0 |
= |
400万 |
![](http://n4.ikafan.com/assetsj/blank.gif)