Excel财务应用张婷婷

1、Excel 高级财务应用,2013级MPACC 张婷婷,目录,2020/6/22,Excel 高级财务应用,2,目录,2020/6/22,Excel 高级财务应用,3,一、资产负债表设计,格式,2020/6/22,Excel 高级财务应用,4,一、资产负债表设计,公式 货币资金： 年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月, 0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102)； 月初数：=IF($E$2=10,SUMIFS(借,年月, &$G$2,科目编号,101)-

2、SUMIFS(贷,年月, &$G$2,科目编号,101)+SUMIFS(借,年月, &$G$2,科目编号,102)-SUMIFS(贷,年月, &$G$2,科目编号,102)； 月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月,0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102),SUMIFS(借,年月,=&$G$2,科目编号,101)-SUMIFS(贷,年月, =&$G$2,科目编号,101)+SUMIFS(借,年月,= &$G$2,科目编号,102)-SUMIFS(贷,年月

3、,=& $G$2,科目编号,102)； 期末数=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月,0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102),SUMIFS(借,年月,= &$G$2,科目编号,101)-SUMIFS(贷,年月,= &$G$2,科目编号,101)+SUMIFS(借,年月,= &$G$2,科目编号,102)-SUMIFS(贷,年月,= &$G$2,科目编号,102)。,2020/6/22,Excel 高级财务应用,5,二、利润表设计,设置微调控件,2020/6/2

4、2,Excel 高级财务应用,6,二、利润表设计,格式,2020/6/22,Excel 高级财务应用,7,二、利润表设计,格式 主营业务收入： 年初数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501) 月初数：=IF($E$2=10,SUMIFS(贷,年月,&$G$2,科目编号, 501) 月余数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号, 501),SUMIFS(贷,年月,=&$G$2,科目编号,501) 期末数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501),SUMIFS(贷,年月,=&$G$2,科目编号,501),2020

5、/6/22,Excel 高级财务应用,8,三、现金流量表设计,设置微调控件,2020/6/22,Excel 高级财务应用,9,三、现金流量表设计,格式,2020/6/22,Excel 高级财务应用,10,三、现金流量表设计,公式 （1）净利润 = 利润表!F20 （2）折旧 = 资产负债表!E15 （3）应付账款的增加 = 资产负债表!L5-资产负债表!K5 （4）应计费用的增加 = 资产负债表!L7-资产负债表!K7+资产负债表!L8-资产负债表!K8+资产负债表!L11-资产负债表!K11+资产负债表!L12-资产负债表!K12 （5）应收账款的增加 = 资产负债表!E5-资产负债表!D5

6、 （6）经营活动现金流净额 = D5+D6+D7+D8-D9-D10 （7）固定资产增加 = 资产负债表!E19-资产负债表!D19 （8）无形资产增加 = 资产负债表!E22-资产负债表!D22 （9）投资活动现金流净额 = D13-D14-D15 （10）短期银行借款增加 = 资产负债表!L4-资产负债表!K4 （11）筹资活动现金流净额 = D18 （12）现金及其等价物增加（减少） = D11+D16+D19,2020/6/22,Excel 高级财务应用,11,四、财务比率分析,设置微调控件,2020/6/22,Excel 高级财务应用,12,四、财务比率分析,格式,2020/6/22

7、,Excel 高级财务应用,13,四、财务比率分析,公式 短期偿债能力 流动比率 = 资产负债表!F12/资产负债表!M14 速动比率 = (资产负债表!F12-资产负债表!F8)/资产负债表!M14 现金比率 = 资产负债表!F4/资产负债表!M14,2020/6/22,Excel 高级财务应用,14,五、财务比率综合评分分析,格式,2020/6/22,Excel 高级财务应用,15,五、财务比率综合评分分析,格式,2020/6/22,Excel 高级财务应用,16,五、财务比率综合评分分析,公式 （1）流动比率 实际比率：=财务比率分析!C4 差异：=B22-C22 调整分：=D22/E2

8、2 得分：=F22+G22 （2）资产负债率 实际比率：=财务比率分析!C4 差异：=B25-C25 调整分：=D25/E25 得分：=F25+G25,2020/6/22,Excel 高级财务应用,17,六、趋势分析,格式,2020/6/22,Excel 高级财务应用,18,六、趋势分析,数据分析,2020/6/22,Excel 高级财务应用,19,七、杜邦分析体系,设置微调控件,2020/6/22,Excel 高级财务应用,20,七、杜邦分析体系,格式,2020/6/22,Excel 高级财务应用,21,七、杜邦分析体系,公式 （1）权益净利率 =G9*K9 （2）总资产周转率=E15*I1

9、5 （3）权益乘数=K15/(K15-L15) （4）销售净利率=C21/G21 （5）资产周转率=H21/J21 （6）总资产=资产负债表!F25 （7）总负债=资产负债表!M17 （8）净利润=利润表!F20 （9）成本费用=D26+D29+F26+F29+F32 （10）销售收入=利润表!F4,2020/6/22,Excel 高级财务应用,22,八、EVA分析,设置微调控件,2020/6/22,Excel 高级财务应用,23,八、EVA分析,格式,2020/6/22,Excel 高级财务应用,24,八、EVA分析,公式 （1）净利润=利润表!F20 （2）负债比率=资产负债表!M17/资

10、产负债表!F25 （3）所有者权益比率=资产负债表!M24/资产负债表!F25 （4）加权资本成本率=C7*C8+C9*C10 （5）总资产=资产负债表!F25 （6）加权资本成本=C12*C11 （7）EVA=C4-C13,2020/6/22,Excel 高级财务应用,25,九、Z分值法设计,设置微调控件,2020/6/22,Excel 高级财务应用,26,九、Z分值法设计,格式,2020/6/22,Excel 高级财务应用,27,九、Z分值法设计,公式 （1）A1= (资产负债表!F12-资产负债表!M14)/资产负债表!F25 （2）A2= (资产负债表!M21+资产负债表!M22)/资

11、产负债表!F25 （3）A3 =(利润表!F19+利润表!F13)/资产负债表!F25 （4）A4 =资产负债表!M24/资产负债表!M17 （5）A5 =利润表!F4/资产负债表!F25 （6）Z值=B4*D4+B5*D5+B6*D6+B7*D7+B8*D8,2020/6/22,Excel 高级财务应用,28,十、投资决策分析,格式,2020/6/22,Excel 高级财务应用,29,十、投资决策分析,公式 （1）方案的IRR=IRR(B6:H6) （2）方案的IRR=IRR(B7:H7) （3）方案的NPV= NPV($B$3,B6:H6) （4）方案B的NPV= NPV($B$3,B7:H7) （5）方案的IRR排序=RANK(I6,$I$6:$I$7) （6）方案的IRR