CVA考试相关:关注CVA公众号和官网公布信息为准。有问题随时咨询CVA课程顾问-李老师,电话:17769955044,微信同号。

Excel在财务管理中的高级应用:设计并创建各种财务分析模型

财务分析 cva 824℃

Excel在财务管理中的高级应用:设计并创建各种财务分析模型

2 Excel 财务管理高级应用

Part1 设计并创建各种财务分析模型

1.1. 国际通用财务预测模版解析

构成要素:

1 假设(Asumption)

通货膨胀,税率,利率,折旧方法

2 项目预测

收入(Sales),成本(Cost),费用(Expense),固定资产预测(FA)

3 损益表(P&L ),资产负债表(BS),现金流量表(Cash flow )

当前数(Current)

累计数(Year to date)

实际数(Actual )

预算数(Budget )

4 比较(Comparation )

5 仪表盘(Dash board )

1.2. 控件的运用

1.2.1. 调出【开发工具】选项卡

点击office 按钮-excel 选项-常用-“在功能区显示开发工具选项卡”

1.2.2. 控件类型与选择

组合框:用于项目较多时

列表框:用于项目较少时

滚动条:用于需要调整数值时,常用于测算模型

按钮: 用于执行命令或宏

复选框:用于多选

1.2.3. 菜单,按钮,滚动条等效果的制作

选择【开发工具】【插入】【表单控件】,选择【组合框】。

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

按下鼠标左键,拖放出合适的大小后释放鼠标,出现下拉菜单形状。选中该下拉菜单,

Excel2007应用培训袁志刚选择【设置控件格式】

Excel在财务管理中的高级应用(财务人员必备)

在【控制】菜单上,去选择相应的数据源。【三维阴影】选项是下拉菜单显示效果设定。

Excel在财务管理中的高级应用(财务人员必备)

【单元格链接】:该处指定的单元格中将存储在控件中被选中的项目在数据源中的序列号。我们经常利用该序列号引用控件选中的值,还可以利用该值制作动态图表。

1.2.4.从窗体中获取数据到单元格并参与运算

1 对滚动条进行设置

右键-【设置控件格式】-【控制】-【单元格链接】,选择一个单元格

2 让控件控制表格的计算

假设点击一下滚动条,希望变动1%的幅度,则公式应该设为:=当前值*(1+(单元格链接/50-1)/2),这样数值可以每次点击1%的幅度正反向变动。

1.3. 模拟运算表

1 模拟运算表概念

其实是把若干个相同公式的输入生成对应的值简化成一个公式的输入产生对

应的值。

2 模拟运算表与普通公式的区别

模拟运算表

一次性输入公式,如有更改也只需要改一个地方

不用过多考虑在公式中使用绝对引用还是相对引用

表格中的数据无法单独修改

公式中引用的参数必须引用“输入引用列/行的单元格”指向的单元格普通的运算方式(公式,然后复制到各个对应的地方)

公式需要复制到每个对应的单元格

需要详细考虑每个参数在复制中需要发生的/不发生的变化,以决定使用绝对引

用还是相对引用

每次如果需要更改公式,就要将所有的地方再重新复制一遍

表中的数据可以单独修改

公式中引用的参数直接指向数据列/行

1.4. 敏感性分析模型

1概念

研究和制约利润的有关因素发生某种变化时,利润变化程度的一种分析方法。

那些对利润影响大的因素称为敏感因素,反之,称为非敏感因素

一般情况下,影响利润的因素有4个:价格、单位变动成本、销售量和固定成

2敏感系数:

敏感系数是反映敏感程度的指标

某因素的敏感系数=利润变化(%)/该因素变化(%)

3判断标准:

敏感系数的绝对值>1,即当某影响因素发生变化时,利润发生更大程度的变化,

该影响因素为敏感因素

敏感系数的绝对值<1,即利润变化的幅度小于影响因素变化的幅度,该因素为

非敏感因素

敏感系数的绝对值=1,即影响因素变化合导致利润相同程度的变化,该因素亦

为非敏感因素

1.5. 公司估值模型(Company Valuation)

•公司估值的概念

•企业价值

•账面价值

– 资产负债表上的资产,反映历史成本,用于会计目的

•市场价值

– 如股票价值,反映未来收益,投资者关注的是市场价值•公司估值

– 如股票价值,反映未来收益,投资者关注的是市场价值•公司估值

•公司估值是指着眼于公司本身,对公司的内在价值进行评估。公司内在价值决定于公司的资产及其获利能力。

•公司估值是投融资、交易的前提。一家投资机构将一笔资金注入企业,应该占有的权益首先取决于企业的价值。

•公司估值的作用

•公司估值有利于我们对公司或其业务的内在价值进行正确评价,从而确立对各种交易进行订价的基础。

•筹集资本(capital raising);

•收购合并(mergers & acquisitions);

•公司重组(corporate restructuring);

•出售资产或业务(divestiture)

•财务模型和公司估值是投资银行的重要方法,广泛运用于各种交易。

•估值的不确定性

•巴菲特:“如果一项资产目前市价只是略低于其内在价值,我们没有兴趣买

4.Excel2007应用培训

入它;只有在有‘显著折扣’时我们才会买入”

•估值最根本的方法:彻底了解这家公司

•主要估值方法

•可比公司法

•历史交易法

•现金流贴现法(DCF)

•DCF:Discounted Cash Flow 现金流量折现

•DCF的方法是指:预测未来现金流,并用贴现率和终值计算方法计算现金流的贴现值,以此计算企业价值。

•使用情形

– 在发达国家市场中,各种数据比较完善,公司日趋成熟,DCF成为通用的估值方法。

– 中国DCF方法似乎受到许多局限,一方面是基础数据缺乏,另一方面是一部分公司的持续经营能力令人怀疑。

•优点

– 完整的评价模型,理论上最完善

– 考虑公司发展的长期性,承认资金时间价值

•缺点

– 数据估算具有高度的主观性与不确定性

•货币的时间价值与贴现

– 货币具有时间价值

– 当前的一元钱比未来的一元钱值钱

– 原因:货币投资可以产生增值

– 比如存入银行可以产生利息,未来就不止一元钱了。

•贴现

– 未来的现金流需要使用一个贴现率(如银行利率),折算成今天值多少钱,这个折算的过程叫贴现。

•估值

– 对公司估值时,不是简单地把未来产生的现金流直接相加,而是选取一个恰当的贴现率,将未来的现金流贴现到现在,然后相加。

– 对公司估值时,不是简单地把未来产生的现金流直接相加,而是选取一个恰当的贴现率,将未来的现金流贴现到现在,然后相加。

加权平均资金成本(WACC)

•如何确定贴现率?

– 一般使用W ACC(加权平均资本成本)作为贴现率

– 巴菲特使用美国长期国债利率

•WACC概念

– 英文Weighted Average Cost of Capital的缩写。W ACC代表公司整体平均资金成本,可用来衡量一个项目是否值得投资;项目的回报必须不低于W ACC。•WACC计算

– 计算W ACC时,先算出构成公司资本结构的各个项目如普通股、优先股、公司债及其他长期负债各自的资金成本或要求回报率,然后将这些回报率按

各项目在资本结构中的权重加权,即可算出加权平均资本成本。

•计算公式

– WACC=(债务/资本)*债务成本*(1-企业所得税税率)+(1-债务/资本)*股权成本

– 其中,债务成本和股权成本用债务人和股东要求的收益率表示。

– 公司估值现金流贴现法就是把公司所有投资者的现金流,包括债权人和股权投资者的现金流折现,而折现率则是加权平均资本成本。因此,W ACC是

投资公司价值评估的一个重要的计算参数。

•股本成本的计算

– 股权成本=无风险收益率+风险溢价*Beta

– 无风险收益率:国债利率或银行利率

– 风险溢价:股票市场长期平均的回报率高出无风险收益率的部分

– Beta:特定企业相对于股票市场的波动性,反映特定企业自身的风险

– 如何计算Beta?

– 首先要分析可比公司的beta,由于可比公司的资本机构可能与被估值企业差异很大,所以要将可比公司的beta折算成无杠杆的beta

– 可比公司无杠杆beta的平均值/中间值用作被估值企业的风险系数,为反映财务风险,需要重新考虑被估值企业的资本结构,计算有杠杆beta •无杠杆beta和有杠杆beta的关系:

– D=债务,E=股权,t=税率,Pref./E=优先股

– 无杠杆beta=有杠杆beta/(1+(D/E)(1-t)+(Pref./E))

●IPO定价与发行公司估值的关系

•IPO概念

•IPO定价(Initial Public Offering)是指首次获准公开发行股票上市的公司与其承销商共同确定的将股票公开发售给特定或非特定投资者的价格。

•两者关系

•公司估值是IPO定价的基础

•IPO定价与公司估值不能等同。IPO还通过选择合适的发售机制来确定影响价格的因素(如市场需求)以最终敲定价格

●自由现金流量

•自由现金流是一种财务方法,用来衡量企业实际持有的能够回报股东的现金。指在不危及公司生存与发展的前提下可供分配给股东(和债权人)的最大现金额。

•自由现金流量等于企业的税后净

营业利润(即将公司不包括利息收支的营业利润扣除实付所得税税金之后的数额)加上折旧及摊销等非现金支出,再减去营运资本

•自由现金流量等于企业的税后净营业利润(即将公司不包括利息收支的营业利润扣除实付所得税税金之后的数额)加上折旧及摊销等非现金支出,再减去营运资本

的追加和物业厂房设备及其他资产方面的投资。它是公司所产生的税后现金流量

总额,可以提供给公司资本的所有供应者,包括债权人和股东。”

•自由现金流量=(税后净利润+利息+折旧及摊销等非现金支出)一(资本支出+营运资本增加)

•中国计算公式

•= 经营活动产生的现金流量净额–资本性支出

•= 经营活动产生的现金流量净额–(购建固定、无形和其他长期资产所支付的现金–处置固定、无形和其他长期资产而收回的现金净额)

●公司估值财务模型

•建立财务模型的重要步骤:

– 在excel里建立历史的现金流量表,并把资产负债表,损益表和现金流量表链接在一起

•现金流计算

– 营运资本=存货+应收账款+其他流动资产-应付账款-其他流动负债

6

– 现金=净利润+折旧-营运资本-资本支出+新增股本-股利+债务

● 建模-财务模型架构

• 经营预测

– 收入预测

– 成本费用预测

– 可以使用占收入的百分比来表述

– 运营资本预测

– 根据有关资产,负债相对于收入或成本的周转天数来计算

– 存货周转天数

– 应收账款周转天数

– 应付账款周转天数

– 资本支出预测

• 融资政策

– 债务政策

– 以还债为首要任务,还是保持适当的债务比例?何时是偿还债务的最佳时机?如何达到

最佳资本结构?

– 融资政策

– 计划新增多少股本或贷款

– 运营资本政策

– 需要保持的最低现金

• 模型建立

– 格式

– 手工输入,公式,链接分别使用不同色彩标识

– 逻辑清晰

– 适当使用名称定义

– 注释和帮助系统

– 灵活性

– 便于对变量进行调整

1.6. 新产品上市测算模型

● 制作含税控件

• 开发工具-插入-表单控件,选择“复选框”,绘制复选框形状

• 右键点击复选框,设置控件格式,在控制标签下,设置单元格链接

Excel在财务管理中的高级应用(财务人员必备)

• 设置价格公式为:=if(b1=true ,价格,价格

/1.17)

Part2 高级图表设计与制作

2.1. 经典商务图表观摩与分析

2.2. 恰当选择图表类型的原则

2.3. 制作图表的信噪比原则

我们在做设计的时候,要考虑的不是还能添加一些什么元素,而是要考虑还能不能再减少一些什么元素。

2.4. 专业商务图表的制作

2.4.1.顶级商务图表的重要标志—配色

1.获取商业周刊风格的图表样本:

搜索图片网址:http://www.doczj.com/doc/66ce240ee45c3b3567ec8be3.html

2.利用colorpix软件获取商业周刊图表配色RGB值

3.调整Excel色彩为商业周刊风格的彩色设置

【图表工具-格式选项卡】【形状填充】【其他填充颜色】【自定义】,对其RGB的值进行设定:

Excel在财务管理中的高级应用(财务人员必备)

2.4.2.控制线,趋势线等新的图表元素的添加

控制线通常为一条水平线,它可以是平均值线,也可以是预算线,或者某个特定的值,用以评估实际情况是否超过了平均水平或者达到了预算,并以直观的形式提供预警。

制作方法:

1.利用average函数得出样本数据的平均值,并以该数值在数据表中添加一列辅助数

据,然后利用数据表制作柱形图。

8.Excel2007应用培训

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

第 9 页 共 29 页

2. 选中红色average 数据系列,调整其图表类型为折线图,即可得到水平的控制线

Excel在财务管理中的高级应用(财务人员必备)

3. 还可以添加其他必要的各种元素,用于强化图表主题。

2.4.

3. 各类必要格式调整

1. 将图表西文和数字字体改为Arial

2. 将横坐标刻度线删除

选中横坐标,选择【图表工具】【布局选项卡】【设置所选内容格式】,“主要刻度线类型”选择“无”。

3. 数据系列分类间距改为40%

选中某数据系列,右键【设置数据系列格式】

4. 对数据排序后再制作柱形图

5. 消除折线图两端的空白:选中横坐标,右键【设置坐标轴格式】,将位置坐标轴改

为在刻度线之上选项:

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

2.5. 不同数量级的图表比较—指数化

用于比较不同数量级的变化趋势。用各期数据/基期数据之后得到的指数做图表,其中,基期是第一期数据。

2.6. 高速图表的途径

2.6.1.快速复制图表样式

如果需要制作一个和现有图表完全相同的图表,不需要重复对各类格式进行设置,只需复制图表,【选择性粘贴】【格式】,即可。

2.6.2.将满意的图表存为模版

如果希望将制作好的图表保存下来供今后调用,选择【另存为模版】;调用时,选择【更改图表类型】,在【模版】类别中选择自己的模版即可。

2.7. 双坐标图表

利用下面的数据制作双坐标图表:

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

在数据系列格式界面上,将系列绘制在由“主坐标轴”改为“次坐标轴”

Excel在财务管理中的高级应用(财务人员必备)

再次选中系列“完成率”,右键菜单选择【更改系列图表类型】:

Excel在财务管理中的高级应用(财务人员必备)

2.8. 用甘特图表示项目进程

制作甘特图,需要以下4列数据:

Excel在财务管理中的高级应用(财务人员必备)

首先制作堆积条形图;然后点击【设计】菜单下的【选择数据】,选中“结束日期”系列,按【删除】按钮将其删除。

Excel在财务管理中的高级应用(财务人员必备)

在【布局】菜单下,利用当前所选内容功能区的下拉菜单,选择“垂直轴”,然后点击【设置所选内容格式】按钮:

Excel在财务管理中的高级应用(财务人员必备)

在出现的【设置坐标轴格式】对话框上,将【逆序类别】选项打勾,将横坐标轴交叉选项改为【最大分类】选项,将纵坐标的项目排列次序反转:

Excel在财务管理中的高级应用(财务人员必备)

12

选择折线图,点确定

Excel在财务管理中的高级应用(财务人员必备)

双坐标图表制作完成。

选择【插入】菜单,选择图表区域中的柱形图,第一种子图表类型簇状柱形图。

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

点击横坐标上“计划数”右侧位置,则可以选中完成率的数据系列

Excel在财务管理中的高级应用(财务人员必备)

点击右键,在右键菜单中选择【设置数据系列格式】:

10

选中开始日期数据系列,右键点击,选择【设置数据系列格式】选项

数据系列格式对话框中将边框和填充都改选为无。

Excel在财务管理中的高级应用(财务人员必备)

此时开始日期数据系列隐藏:

Excel在财务管理中的高级应用(财务人员必备)

此时需要将横坐标开始日期改为真正的项目开始日期:

在excel中,每一个日期都对应一个数值,选中B2,右键点击,选择【设置单元格格式】,将其数字类型改为“常规”,查看2008-7-1对应数值为39630,点击【取消】退出:

Excel在财务管理中的高级应用(财务人员必备)

选中横坐标,点击右键,选择【设置坐标轴格式】,切换到【刻度】,将最小值改为39630,同样将横坐标最大值改为真正的项目结束日期39680。

Excel在财务管理中的高级应用(财务人员必备)

甘特图制作完成。

2.9. 动态图表观摩与思路分析

选中北京,深圳,上海工作表上A2:M7的区域,分别定义北京,深圳,上海的名称;

选择【开发工具】【插入】【表单控件】,选择“选项”按钮,画出如下三个选项按钮:

Excel在财务管理中的高级应用(财务人员必备)

右键选中选项按钮,在右键菜单中选择【设置控件格式】,在跳出的对话框中选择【控制】标签,在【单元格链接】中选择A16:

Excel在财务管理中的高级应用(财务人员必备)

在A17单元格中输入公式:=choose(a16,”北京”,”上海”,”深圳”)

将北京工作表上的业务列表复制粘贴到制作双曲线图表的当前工作表上;

选中A3,A4单元格,选择【数据】菜单中的数据有效性:

Excel在财务管理中的高级应用(财务人员必备)

将【有效性】对话框上的有效性条件,由【任何值】改为【序列】

Excel在财务管理中的高级应用(财务人员必备)

在【来源】框中选择粘贴过来的业务列表

Excel在财务管理中的高级应用(财务人员必备)

在B16单元中写入以下公式,并复制到该行1-12月的单元格中:

Excel在财务管理中的高级应用(财务人员必备)

依据此数据表制作双曲线图

Excel在财务管理中的高级应用(财务人员必备)

Part3 管理仪表盘

3.1. 公司管理仪表盘设计效果观摩

3.2. 仪表盘制作思路

如果数据源为数据列表,那么可以使用透视表作为制作仪表盘的工具。

如果数据源不是数据列表,那么可以选择恰当的函数与公式来制作仪表盘。

3.3. 利用透视表制作仪表盘

1.创建3个表,分别命名为数据源,计算表,仪表盘,分别放置原始数据,计算过程和终

端用户访问界面。

2.利用原始数据创建透视表,如下图:

Excel在财务管理中的高级应用(财务人员必备)

3.选择“数据透视表工具选项卡”-“选项”-“数据透视图”,创建透视图

4.右键点击透视图,选择移动图表,将透视图移动到仪表盘工作表上。

5.选中透视表,选择“数据透视表工具”-“选项”-“移动数据透视表”,把透视表也移

动到仪表盘工作表上。

6.将透视表主体部分隐藏,只保留报表筛选区域与透视图,这样就完成了动态的仪表盘中

的一个指标,如下图:

Excel在财务管理中的高级应用(财务人员必备)

3.4. 利用函数等制作仪表盘

1.目标:制作仪表盘上的一个指标:可分区来看的产品销量的动态指标.

2.首先利用透视表得到分区与产品的列表,然后制作分区的列表框:

“开发工具”-“插入”-“表单控件”-“组合框”

右键点击绘制好的组合框,右键菜单里选择“设置控件格式”,在数据源区域里选择分区列表,在单元格链接里选择分区列表上方的单元格:

Excel在财务管理中的高级应用(财务人员必备)

3.在任意单元格里,输入函数:

=Choose(c2,c3,c4, (20)

4.在产品列表右侧的列里输入以下函数:

Excel在财务管理中的高级应用(财务人员必备)

5.根据查询结果制作柱形图,此图表为动态指标,在列表框中选择不同分区,即可查看不

同分区的不同产品的销量情况,如下图:

Excel在财务管理中的高级应用(财务人员必备)

Part4 重整数据流

4.1. Excel表格结构

4.1.1.数据列表

数据列表是数据库内表的结构,其主要特征是:每一列都是一个独立的维度。这种结构适合快速地对数据进行各种整理与分析。

4.1.2.二维表

二维表是指一个表格的行与列中各有一个维度。这种表格不适合快速对数据进行各种整理与分析。

4.2. 链接数据库与OLAP报表制作

ODBC的介绍

存储数据并不是Excel的特长,Excel设计和优化的目的是综合、分析及显示数据。它仅有一些基本的数据库功能,当用户觉得简便的重用性大于功能时,就应该使用它们。

Excel中有一些方法,可以将这些工具和真实数据库管理系统紧密结合在一起,利用这些方法,用户可以创建一些系统,在数据库管理系统的强大功能和工作表的功能中达到一种平衡。

ODBC是开放式数据库互连(Open Database Connectivity)的缩写。它是一种标准,许多数据库程序和数据库管理系统都采用这种标准。如果用户有一个数据源,如与ODBC兼容的Access数据库,那么就可以将数据轻松的导入到Excel中。不是所有的数据库都有同样的结构。为了确定这些数据库是否可以和其他应用软件相连,ODBC软件生产商提供了驱动程序,在安装Microsoft Office时,按转程序会自动地为Access、Paradox、d BASE以及SQL Server 等安装几个驱动程序。在将数据从数据

库导入到Excel中之前,用户需要确认自己想使用的数据库类型。这样,Excel才能知道使用何种驱动程序和数据库相连。

ODBC是开放式数据库互连(Open Database Connectivity)的缩写。它是一种标准,许多数据库程序和数据库管理系统都采用这种标准。如果用户有一个数据源,如与ODBC兼容的Access数据库,那么就可以将数据轻松的导入到Excel中。不是所有的数据库都有同样的结构。为了确定这些数据库是否可以和其他应用软件相连,ODBC软件生产商提供了驱动程序,在安装Microsoft Office时,按转程序会自动地为Access、Paradox、d BASE以及SQL Server 等安装几个驱动程序。在将数据从数据库导入到Excel中之前,用户需要确认自己想使用的数据库类型。这样,Excel才能知道使用何种驱动程序和数据库相连。

在确定想要使用的数据源类型后,还必须提供一个查询(query)。查询是用标准的结构查询语言写出的一系列指令,这种查询语言一般形成SQL.那些指令提供了一些信息,如使用数据库中的哪些表,需要表中哪些字段的数据,以及在选择要检索记录的过程中是否运用某些标准等。Microsoft Office还有一个叫做Microsoft Query的程序。从数据库导入数据不久, Microsoft Query就开始运行了。它将帮助用户设计查询,并从数据库中检索所需要的记录。

从数据库导入数据是一个能够两步完成的过程:首先确定数据源,然后再创建查询命令。点击“获取数据”。

选择“新数据源”,按确定按钮。

Excel在财务管理中的高级应用(财务人员必备)

输入数据源名称,选择相应的数据库驱动,点击“连接”。

Excel在财务管理中的高级应用(财务人员必备)

在下图界面上点击“选择”按钮。

Excel在财务管理中的高级应用(财务人员必备)

在下图界面上去相应路径选择数据库,在左侧选中数据库后点击确定按钮。

Excel在财务管理中的高级应用(财务人员必备)

回到下图界面后选择默认表。

Excel在财务管理中的高级应用(财务人员必备)

新数据源建好后出现在数据源列表中,选中后确定。

Excel在财务管理中的高级应用(财务人员必备)

在数据表中选择需要的字段。

Excel在财务管理中的高级应用(财务人员必备)

可以利用某字段对数据进行筛选。

Excel在财务管理中的高级应用(财务人员必备)

点击完成,进入透视表设置界面。

Excel在财务管理中的高级应用(财务人员必备)

对透视表进行布局,以下步骤同透视表操作。

Excel在财务管理中的高级应用(财务人员必备)

第 21 页 共 29 页

4.3. 多种数据整理方法

4.3.1. 整合多个数据表

我们经常需要将几个表格中的数据整合成一张表,如下图所示,将5个月的数据(目前分布在5张表格上)整合到一个总表上:

Excel在财务管理中的高级应用(财务人员必备)

我们使用的方法是利用office 的查询工具query 进行的,这样做的优点在于今后对于新数据的更新可以自动化刷新,而不需要重复的进行整合操作(如复制粘贴)。

下面开始整合工作:

选择合适的数据源类型,这里选择自其它来源中的

Microsoft query:

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

选择excel files*:

Excel在财务管理中的高级应用(财务人员必备)

选择要导入数据的excel文件:

Excel在财务管理中的高级应用(财务人员必备)

出现选择数据表的界面:

Excel在财务管理中的高级应用(财务人员必备)

如果以上界面提示错误,点击以上界面的“选项”按钮,出现以下界面,将系统表打勾即可:

Excel在财务管理中的高级应用(财务人员必备)

任选一个字段到右侧,点下一步按钮:

Excel在财务管理中的高级应用(财务人员必备)

直到以下界面出现,选择第二项,点完成按钮:

Excel在财务管理中的高级应用(财务人员必备)

此时出现query程序界面,点击工具条上SQL按钮:

Excel在财务管理中的高级应用(财务人员必备)

出现以下界面:

Excel在财务管理中的高级应用(财务人员必备)

在界面中将SQL语句改为以下:

Excel在财务管理中的高级应用(财务人员必备)

出现以下提示,点确定按钮:

Excel在财务管理中的高级应用(财务人员必备)

出现数据查询结果,选文件菜单-将数据返回EXCEL:

Excel在财务管理中的高级应用(财务人员必备)

选择在新工作表中存储数据:

Excel在财务管理中的高级应用(财务人员必备)

几张数据表被成功地整合在一起。

该查询数据表与数据源保持着动态链接关系,可以随时刷新数据,不需要重复操作。右键菜单里选择刷新数据即可对数据进行刷新。

4.3.2.利用透视表进行同比分析

1 “插入”选项卡-数据透视表,将日期字段拖入行标签,将销量字段拖入“数值”区域,拖入三遍。

2 光标放入行标签区域,点击右键,在右键菜单中选择“组合”,选中“月”和“年”:

Excel在财务管理中的高级应用(财务人员必备)

3 对“年”字段进行筛选,值选择2000年与2001年,然后在右侧的字段列表工作区中,将“年”拖入列标签:

Excel在财务管理中的高级应用(财务人员必备)

4 右键-数据透视表选项,勾选“合并且居中排列带标签的单元格”,并去掉行总计与列总计的勾选:

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

第 27 页 共 29 页

5 光标放置在“销量2”列中,右键-“值字段设置”,切换到“值显示方式”标签,选择“差异”的显示方式,将基本字段设置为“年”,将基本项设置为2000年:

Excel在财务管理中的高级应用(财务人员必备)

6光标放置在“销量3”列中,右键-“值字段设置”,切换到“值显示方式”标签,选择“差异百分比”的显示方式,将基本字段设置为“年”,将基本项设置为2000年,然后将表格中的空列隐藏即可。

4.3.3. 利用透视表进行环比分析

1 “插入”选项卡-数据透视表,将日期字段拖入行标签,将销量字段拖入“数值”区域,拖入三遍。

2 光标放入行标签区域,点击右键,在右键菜单中选择“组合”,选中“月”和“年”:

Excel在财务管理中的高级应用(财务人员必备)

3 右键-数据透视表选项,勾选“合并且居中排列带标签的单元格”,并去掉行总计与列总计的勾选:

Excel在财务管理中的高级应用(财务人员必备)

Excel在财务管理中的高级应用(财务人员必备)

28

5 光标放置在“销量2”列中,右键-“值字段设置”,切换到“值显示方式”标签,选择“差异”的显示方式,将基本字段设置为“月”,将基本项设置为“上一个”:

Excel在财务管理中的高级应用(财务人员必备)

6光标放置在“销量3”列中,右键-“值字段设置”,切换到“值显示方式”标签,选择“差异百分比”的显示方式,将基本字段设置为“月”,将基本项设置为“上一个”,然后将表格中的空列隐藏即可。

4.3.4. 利用透视表计算累计数

1 “插入”选项卡-数据透视表,将日期字段拖入行标签,将销量字段拖入“数值”区域,拖入两遍。

2 光标放入行标签区域,点击右键,在右键菜单中选择“组合”,选中“月”和“年”:

Excel在财务管理中的高级应用(财务人员必备)

3 将光标放置在“销量2”列内,右键菜单中选择“值字段设置”,切换到“值显示方式”,选择“按照某一字段汇总”,选择“日期”字段:

Excel在财务管理中的高级应用(财务人员必备)

转载请注明:CVA注册估值分析师 » Excel在财务管理中的高级应用:设计并创建各种财务分析模型

喜欢 (2)or分享 (0)


培训咨询:李老师,电话:17769955044,微信同号。