本量利分析基本原理应用

本量利分析在成本性态分析和变动成本法的基础上,使用数量化的模型来提示企业的变动成本、固定成本、相关业务量、销售单价和利润之间在数量上的相互依存关系。企业为了实现目标利润,可以使用本量利分析的方法进行利润规划与管理,从而为实现利润最大化找到最佳途径。

先来看一下本量利的基本关系式:

利润=销售单价×销售量-单位变动成本×产量-固定成本总额

如果产量等于销售量,则关系式变为:

利润=(销售单价-单位变动成本)×销售量-固定成本总额

在以上关系式中,包含了5个变量。这5个变量互相联系,只要给出其中4个变量,就可以求出第5个变量的值。通过以上关系式,可以进行本量利分析,包括盈亏临界点分析、单因素变动对目标利润的影响分析、多因素变动对目标利润的影响分析和各个因素变化对目标利润变化影响的敏感程度分析。

Excel 数据图表分离

为了让数据与图表分离开来,也可以将图表移动到单独的新工作表。具体操作步骤如下。

STEP01:打开“固定资产折旧分析.xlsx”工作簿,切换至“直线法”工作表。选中要移动的图表,切换至“图表设计”选项卡,在“位置”组中单击“移动图表”按钮,打开“移动图表”对话框,如图24-53所示。

STEP02:打开“移动图表”对话框后,在“选择放置图表的位置”列表下单击选中“新工作表”单选按钮,然后在右侧的文本框中输入新工作表的名称“直线法折线图”,单击“确定”按钮后,便可以将图表移动到新的工作表,如图24-54所示。折线图移动后的效果如图24-55所示。

图24-53 单击“移动图表”按钮

图24-54 选中图表放置位置

将图表移动到新的工作表

图24-55 将图表移动到新的工作表

Excel 更改折旧图表类型

除了可以使用柱形图比较数据的不同之外,还可以使用其他图表类型,例如折线图。如果要更改图表类型,可以按以下步骤进行操作。

STEP01:选中插入的簇状柱形图,单击鼠标右键,在弹出的隐藏菜单中选择“更改图表类型”选项,如图24-50所示。

图24-50 选择“更改图表类型“选项

STEP02:随后会打开“更改图表类型”对话框,切换至“所有图表”选项卡,单击左侧导航栏中的“折线图”标签,在对话框右侧选择一种折线图的类型,这里选择“带数据标记的折线图”选项,然后单击“确定”按钮完成图表的更改,如图24-51所示。

STEP03:此时,工作表中的簇状柱形图会更改为带数据标记的折线图,效果如图24-52所示。

图24-51 选择折线图类型

图24-52 图表更改为折线图

Excel 折旧图表元素设置

下面为图表添加标题,以便让使用者清楚图表的用途。

STEP01:选中插入的簇状柱形图,切换至“图表设计”选项卡,在“图表布局”组中单击“添加图表元素”下三角按钮,在展开的下拉列表中选择“图表标题”选项,然后在展开的级联列表中选择“图表上方”选项,如图24-44所示。

STEP02:在“图表标题”文本框中输入标题“固定资产折旧分析——直线法”,如图24-45所示。

图24-44 选择标题位置

图24-45 为图表添加标题

下面为坐标轴添加标题,以便让使用者清楚坐标轴的用途。

STEP01:选中插入的簇状柱形图,切换至“图表设计”选项卡,在“图表布局”组中单击“添加图表元素”下三角按钮,在展开的下拉列表中选择“坐标轴标题”选项,然后在展开的级联列表中选择“主要横坐标轴”选项,如图24-46所示。

STEP02:在“主要横坐标轴”文本框中输入标题“机器名称”,如图24-47所示。

图24-46 选择“主要横坐标轴”选项

输入主要横坐标轴标题

图24-47 输入主要横坐标轴标题

STEP03:再次选中插入的簇状柱形图,切换至“图表设计”选项卡,在“图表布局”组中单击“添加图表元素”下三角按钮,在展开的下拉列表中选择“坐标轴标题”选项,然后在展开的级联列表中选择“主要纵坐标轴”选项,如图24-48所示。

STEP04:在“主要纵坐标轴”文本框中输入标题“单位:元”,如图24-49所示。

图24-48选择“主要纵坐标轴”选项

图24-49 输入主要纵坐标轴标题

Excel 选择折旧数据源

下面为图表选择数据源。

STEP01:选中插入的簇状柱形图,切换至“图表设计”选项卡,在“数据”组中单击“选择数据”按钮,打开“选择数据源”对话框,如图24-41所示。

图24-41 单击“选择数据”按钮

STEP02:打开“选择数据源”对话框后,选择图表中要显示的数据源,例如这里在“水平(分类)轴标签”列表框中依次勾选“单价”“数量”“原值”及“残值率”复选框,如图24-42所示。此时,工作表中的图表如图24-43所示。

图24-42 选择数据源

图24-43 选择数据源的图表效果图

Excel 选择折旧图表类型

本节以插入柱形图为例介绍具体操作步骤。

STEP01:打开“固定资产折旧分析.xlsx”工作簿,切换至“直线法”工作表。选中工作表中的任意单元格,如H3单元格,切换至“插入”选项卡,在“图表”组中单击“插入柱形图或条形图”下三角按钮,在展开的下拉列表中选择“二维柱形图”列表中的“簇状柱形图”选项,如图24-39所示。

STEP02:此时,当前工作表中会插入一个柱形图,如图24-40所示。

图24-39 选择图表类型

图24-40 插入的图表效果图

Excel 固定余额递减法计算折旧值(DB函数)

采用固定余额递减法计算折旧,使用的是固定余额递减法折旧函数——DB函数。DB函数的语法为:


DB(cost,salvage,life,period,month)

其中,month参数为第1年的月份数,如果省略,则假设为12。固定余额递减法用于计算固定利率下的资产折旧值,函数DB使用下列计算公式来计算一个期间的折旧值:(cost-前期折旧总值)*rate。式中rate=1-((salvage/cost)^(1/life)),保留3位小数。第1个周期和最后1个周期的折旧属于特例。

对于第1个周期,函数DB的计算公式为:cost*rate*month/12。对于最后一个周期,函数DB的计算公式为:((cost-前期折旧总值)*rate*(12-month))/12。

STEP01:按照上述复制“直线法”工作表的方法复制“双倍余额递减法”工作表,将复制后的“双倍余额递减法(2)”工作表重命名为“固定余额递减法”,并清除J4:J15单元格区域中的数据,保留文本和表格源格式,最终效果如图24-33所示。

STEP02:接下来计算本期折旧额。选中J4单元格,切换至“公式”选项卡,单击“函数库”组中的“插入函数”按钮,打开“插入函数”对话框,如图24-34所示。

图24-33 准备固定资产折旧分析表

图24-34 单击“插入函数”按钮

STEP03:打开“插入函数”对话框后,单击“或选择类别”选择框右侧的下拉按钮,在展开的下拉列表中选择“全部”选项,然后在“选择函数”列表框中选择“DB”函数,单击“确定”按钮打开“函数参数”对话框,如图24-35所示。

STEP04:打开“函数参数”对话框后,在Cost文本框中输入“E4”,即固定资产的原值;在Salvage文本框中输入“G4”,即固定资产残值;在Life文本框中输入“H4”,即固定资产的可使用年限;在Period文本框中输入“I4”,即固定资产的当前年限;在Month文本框中输入“MONTH(B4)”,即第1年的月份数。输入完毕在这些文本框的右侧会出现具体数值,并在下方计算出指定期间固定资产的折旧值。最后单击“确定”按钮完成函数的插入操作,如图24-36所示。此时如果选中工作表中的J4单元格,可以看到公式“=DB(E4,G4,H4,I4,MONTH(B4))”,如图24-37所示。

STEP05:选中J4单元格,将鼠标光标定位于单元格右下角,使用填充柄工具向下复制公式至J15单元格,通过自动填充功能计算出其他机器的本期折旧额,计算结果如图24-38所示。

图24-35 选择函数

图24-36 设置函数参数

图24-37 计算第1台机器的本期折旧额

图24-38 计算其他机器的本期折旧额

Excel 双倍余额递减法计算折旧值(DDB函数和VDB函数)

采用双倍余额递减法计算折旧,使用的是双倍余额递减法折旧函数——DDB函数和VDB函数。双倍速余额递减法也是一种加速折旧法,指的是不考虑固定资产预计净残值,根据每期期初固定资产账面余额和双倍的直线法折旧率计算固定资产折旧。本节采用VDB函数进行折旧计算。VDB函数的语法为:


VDB(cost,salvage,life,start_period,end_period,factor,no_switch)

其中,start_period参数为进行折旧计算的起始期间,start_period参数必须与life的单位相同。end_period参数为进行折旧计算的截止期间,end_period参数必须与life的单位相同。factor参数为余额递减速率(折旧因子),如果factor参数被省略,则假设为2(双倍余额递减法)。如果不想使用双倍余额递减法,可改变factor参数的值。no_switch参数为一逻辑值,指定当折旧值大于余额递减计算值时,是否转用直线折旧法。如果no_switch参数为TRUE,即使折旧值大于余额递减计算值,Excel也不转用直线折旧法;如果no_switch参数为FALSE或被忽略,且折旧值大于余额递减计算值时,Excel将转用直线折旧法。除no_switch参数以外的所有参数必须为正数。具体计算步骤如下。

STEP01:按照上述复制“直线法”工作表的方法复制“年数总和法”工作表,将复制后的“年数总和法(2)”工作表重命名为“双倍余额递减法”,并清除J4:J15单元格区域中的数据,保留文本和表格源格式,最终效果如图24-30所示。

图24-30 准备固定资产折旧分析表

STEP02:利用双倍余额递减法计算计算本期折旧。选中J4单元格,在编辑栏中输入公式“=IF(AND(I4<H4,MONTH(B4)<12),VDB(E4,G4,H4,I4-1,I4)*MONTH(B4)/12+VDB(E4,G4,H4,I4,I4+1)*(12-MONTH(B4))/12,IF(AND(I4<H4,MONTH(B4)=12),VDB(E4,G4,H4,I4-1,I4),IF(AND(YEAR($G$2)-YEAR(B4)=H4,MONTH(B4)<12),VDB(E4,G4,H4,I4-1,I4)*MONTH(B4)/12,0)))”,然后按“Enter”键返回,即可计算出第1台机器的本期折旧额,如图24-31所示。

STEP03:选中J4单元格,将鼠标光标定位于单元格右下角,使用填充柄工具向下复制公式至J15单元格,通过自动填充功能计算出其他机器的本期折旧额,计算结果如图24-32所示。

图24-31 计算第1台机器的本期折旧额

图24-32 计算其他机器的本期折旧

Excel 年数总和法计算折旧值(SYD函数)

采用年数总和法计算折旧,使用的是年数总和法折旧函数——SYD函数。年数总和法是加速折旧法的一种,用固定资产原值减去预计净残值后的余额作为基数,乘以一个逐年递减的分数来计算每期的折旧额。分数的分子表示固定资产还可以使用的年限,分母代表使用年限逐年数字的总和。SYD函数的语法为:


SYD(cost,salvage,life,per)

其中,per参数为指定要计算折旧的期间,时间单位与life相同。SYD函数用于计算某项固定资产按年数总和法计算的指定期间的折旧额。

STEP01:打开“固定资产折旧分析.xlsx”工作簿,在“直线法”工作表的标签处单击鼠标右键,在弹出的隐藏菜单中选择“移动或复制”选项,如图24-23所示。

图24-23 复制工作表

STEP02:随后会打开“移动或复制工作表”对话框,在“下列选定工作表之前”列表框中选择“(移至最后)”选项,勾选下方的“建立副本”复选框,然后单击“确定”按钮完成工作表的复制操作,如图24-24所示。

STEP03:工作表复制完成后,在“直线法”工作表的后会新增一个“直线法(2)”工作表,将“直线法(2)”工作表重命名为“年数总和法”。在“年数总和法”工作表中删除“K3:N15”单元格区域中的全部内容,将I3单元格和J3单元格中的文本标题分别修改为“当前年限”“本期折旧”,然后仅清除I4:J15单元格区域中的数据,保留其文本和表格源格式。最终将工作表修改为如图24-25所示的效果。

图24-24 复制工作表

图24-25 准备固定资产折旧分析表

STEP04:首先计算当前年限。选中I4单元格,在编辑栏中输入公式“=IF(YEAR($G$2)-YEAR(B4)>H4,H4,YEAR($G$2)-YEAR(B4))”,然后按“Enter”键返回,即可计算出第1台机器的当前年限,如图24-26所示。

STEP05:选中I4单元格,将鼠标光标定位于单元格右下角,使用填充柄工具向下复制公式至I15单元格,通过自动填充功能计算出其他机器的当前年限,计算结果如图24-27所示。

图24-26 计算第1台机器的当前年限

图24-27 计算其他机器的当前年限

STEP06:接下来计算本期折旧。选中J4单元格,在编辑栏中输入公式“=IF(AND(I4<H4,MONTH(B4)<12),SYD(E4,G4,H4,I4+1)*(MONTH($G$2)-MONTH(B4))/12+SYD(E4,G4,H4,I4)*MONTH(B4)/12,IF(AND(YEAR($G$2)-YEAR(B4)=H4,MONTH(B4)<12),SYD(E4,G4,H4,I4)*MONTH(B4)/12,0))”,然后按“Enter”键返回,即可计算出第1台机器的本期折旧额,如图24-28所示。

STEP07:选中J4单元格,将鼠标光标定位于单元格右下角,使用填充柄工具向下复制公式至J15单元格,通过自动填充功能计算出其他机器的本期折旧额,计算结果如图24-29所示。

图24-28 计算第1台机器的本期折旧额

图24-29 计算其他机器的本期折旧额

Excel 直线法计算折旧值(SLN函数)

采用直线法计算折旧,使用的是直线法折旧函数——SLN函数。直线法是企业计算固定资产折旧最常用的一种方法。SLN函数的语法为:


SLN(cost,salvage,life)

其中,cost参数表示固定资产原值(即例子工作表中的“总金额”),salvage参数表示固定资产报废时的预计净残值(即例子工作表中的“残值”),life参数表示折旧期限,也就是固定资产预计使用年限。使用SLN函数可以计算固定资产在使用期间每期按平均年限计算的线性折旧额。

STEP01:首先计算月折旧额。选中K4单元格,切换至“公式”选项卡,单击“函数库”组中的“插入函数”按钮,打开“插入函数”对话框,如图24-12所示。

图24-12 单击“插入函数”按钮

STEP02:打开“插入函数”对话框后,单击“或选择类别”选择框右侧的下拉按钮,在展开的下拉列表中选择“全部”选项,然后在“选择函数”列表框中选择“SLN”函数,单击“确定”按钮打开“函数参数”对话框,如图24-13所示。

STEP03:打开“函数参数”对话框后,在Cost文本框中输入“E4”,即固定资产的原值(总金额);在Salvage文本框中输入“G4”,即固定资产残值;在Life文本框中输入I4,即固定资产的可使用月数。此时在这些文本框的右侧会出现具体数值,并在下方计算出固定资产的每期线性折旧费。最后单击“确定”按钮完成函数的插入操作,如图24-14所示。此时如果选中工作表中的K4单元格,可以看到公式“=SLN(E4,G4,I4)”,如图24-15所示。

图24-13 选择函数

图24-14 输入函数参数

STEP04:选中K4单元格,将鼠标光标定位于单元格右下角,使用填充柄工具向下复制公式至K15单元格,通过自动填充功能计算出其他机器的月折旧额,计算结果如图24-16所示。

图24-15 计算第1台机器的月折旧额

图24-16 计算其他机器的月折旧额

STEP05:接下来计算本期折旧。选中L4单元格,在编辑栏中输入公式“=(IF(AND(YEAR($G$2)>YEAR(B4),YEAR($G$2)<(YEAR(B4)+H4)),MONTH($G$2),IF(YEAR($G$2)=YEAR(B4)+H4,MONTH(B4),IF(YEAR($G$2)=YEAR(B4),MONTH($G$2)-MONTH(B4),0))))*K4”,然后按“Enter”键返回,即可计算出第1台机器的本期折旧额,如图24-17所示。

STEP06:选中L4单元格,将鼠标光标定位于单元格右下角,使用填充柄工具向下复制公式至L15单元格,通过自动填充功能计算出其他机器的本期折旧额,计算结果如图24-18所示。

STEP07:接下来计算累计折旧额。选中M4单元格,在编辑栏中输入公式“=J4*K4”,然后按“Enter”键返回,即可计算出第1台机器的累计折旧额,如图24-19所示。

图24-17 计算第1台机器的本期折旧额

图24-18 计算其他机器的本期折旧额

STEP08:选中M4单元格,将鼠标光标定位于单元格右下角,使用填充柄工具向下复制公式至M15单元格,通过自动填充功能计算出其他机器的累计折旧额,计算结果如图24-20所示。

图24-19 计算第1台机器的累计折旧额

计算其他机器的累计折旧额

图24-20 计算其他机器的累计折旧额

STEP09:最后需要根据机器的原值金额和累计折旧金额计算出机器的账面价值。选中N4单元格,在编辑栏中输入公式“=E4-M4”,然后按“Enter”键返回,即可计算出第1台机器的账面价值,如图24-21所示。

STEP10:选中N4单元格,将鼠标光标定位于单元格右下角,使用填充柄工具向下复制公式至N15单元格,通过自动填充功能计算出其他机器的账面价值,计算结果如图24-22所示。

图24-21 计算第1台机器的账面价值

图24-22 计算其他机器的账面价值