Excel财务数据模型与模板疯狂实战

【课程大纲】

第一篇Excel财务建模之理论基础

1.  Excel财务建模的概念与方法论

2.  Excel财务模型的结构设计

2.1 数据输入区、数据计算区和数据输出区

2.2 输入区：输入模型需要的各种假设条件

2.3 计算区：完成各种数据计算的区域

2.4 输出区：集中展示模型的各种假设条件和计算结果，一般包括报表与图表

3.  Excel财务模型的调整与持续优化

第二篇Excel财务建模必备的技术修炼

1.  经典常用的函数是财务建模的灵魂

1.1 IF、IFERROR、CHOOSE等条件选择或条件纠错函数

1.2 VLOOKUP、HLOOKUP、LOOKUP等查询函数

1.3 SUMIF、SUMIFS、COUNTIF、COUNTIFS及SUMPRODUCT等求和计数函数

1.4 INDEX、OFFSET、INDIRECT及MATCH等引用偏移、引用转换、定位函数

1.5 TEXT文本格式转换函数，还有一些新函数如XLOOKUP、FILTER函数等

2.  定义名称技术让财务模型简约且易于维护

3.  交互触发技术让财务模型更智能更自动化

3.1 表单控件技术与ActiveX控件技术

3.2 数据验证（有效性）提供下拉列表

4.  条件格式让财务模型的格式显示呈现动态效果

5.  数据透视表、数据透视图与切片器给财务模型的智能化提供巨大想象空间

6.  PowerQuery及PowerBI模型，为Excel财务模型打开无限广阔的世界

7.  数据隐身与数据保护让财务模型的呈现与安全增添保障

第三篇构建经典的财务数据模板或模型

模型1年度资金预算模型

1) 表单控件“滚动条”的插入与设置

2) 图表的辅助区域设置及坐标轴标签的公式设计

3) 利用柱形图的涨跌柱线展示资金的收入与支出

4) 把文本框与动态变化的单元格用公式连接起来

模型2用模拟运算表制作利润敏感度分析模型

1) 单因素敏感度分析，用模拟运算表展现单价的变动

2) 插入表单控件“微调框”并设置，精细调节固定成本与变动成本

3) 双因素敏感度分析，用模拟运算表同时模拟单价和销量的变动

4) 用条件格式根据单元格值展现不同的填充颜色

模型3运输费用或网点布局测算模型

1) 隐秘功能“规划求解”，帮你求解最小运输费用

2) 隐秘功能“规划求解”，助你进行网点布局决策分析

模型4材料自制或外购决策模型

1) 插入表单控件“微调框”并设置，对相关变量进行微调

2) 制作多个用于绘图的辅助数据区域

3) 用带直线的散点图和普通散点图制作更直观的动态决策分析图表

模型5财务报表自动化模板

1) 使用宏表函数批量制作各个分表的超链接

2) 使用透视表和VLOOKUP函数等实现财务报表的动态汇总

3) 运用SUMIF函数汇总得到现金流量表

模型6动态杜邦分析模型

1) 用MATCH函数制作图表所需的辅助区域

2) 借助INDIRECT函数与滚动条控件构建杜邦分析结构图

3) 利用双饼图组合图表设计净利润仪表盘和权益净利率仪表盘

模型7基于管理费用科目余额表的年度汇总分析模板

1) 对12个月份的原始表格批量用函数公式增添若干辅助列

2) 插入表单控件“选项”按钮与ActiveX控件“组合框”

3) 运用INDEX、SUMIF及INDIRECT等函数对多表按部门或科目进行汇总

模型8产品进销存Excel管理系统

1) 运用形状、图表、超链接等功能制作导航主页

2) 利用下拉框表单控件、切片器功能及查询函数制作供货商、产品列表

3) 利用透视表和经典的查询公式、求和公式及条件格式等实现出入库动态统计

4) 运用数组公式实现一对多查询，实时统计需要紧急采购的产品信息

5) 使用数据验证及各种经典函数公式对产品的进销存情况进行多条件的动态查询

模型9应收账款管理系统

1) 应收结余的实时计算及应收账款汇总，竟然只是用了SUMIF函数

2) 这种应收账龄分析表真的很烧脑，SUMPRODUCT函数无处不在且千头万绪

3) 动态生成往来对账函梦寐以求，此刻已经丝滑般成为现实

模型10供应商管理系统

1) 供应商查询、供应商评级与评级雷达图，背后都有经典函数的影子

2) 付款提醒、发票查询、付款查询与对账单，被这几个函数收拾得服服帖帖

3) 供应商余额统计汇总，耳熟能详的几大求和计数函数主力悉数闪耀登场

李承高老师的其它课程