如何用Excel散点图进行资产折旧？

固定资产是指使用期限较长、单位价值较高，在使用过程中保持原有实物形态的长期资产。在会计核算、财务管理中均需对固定资产的支出进行资本化处理，然后在规定的使用年限内进行折旧。固定资产的折旧方法主要有平均年限法、年数总和法、双倍余额递减法、工作量法。本书介绍前三者在Excel中的计算方法。

1.平均年限法

又称使用年限法，是将固定资产的折旧额均衡地分摊到固定资产折旧年限的各个会计期间的一种方法。Excel中它可用数学公式、也可用函数计算其各年折旧额。

（1）Excel数学公式计算法。其主要计算公式如下：

年折旧额＝（固定资产原值-预计净残值）÷预计使用年限

月折旧额＝年折旧额÷12

（2）Excel函数计算法。Excel提供了线性折旧函数Sln，计算平均年限法的折旧额，其年折旧额函数公式如下：

＝SLN(cost，salvage，life)

式中，cost表示固定资产原值；salvage表示固定资产在折旧期末的价值（也称净残值）；life表示折旧期限（也称固定资产的使用寿命）。

2.双倍余额递减法

双倍余额递减法是在不考虑净残值的情况下，以固定资产的期初账面余额为折旧基数，以直线法折旧率的2倍作折旧率来计算各期折旧额的方法。Excel中它可用数学公式、也可用函数计算双倍余额递减各年折旧额。

（1）Excel数学公式计算法。其年折旧额计算公式如下：

年折旧额＝期初固定资产账面净值×年折旧率

年折旧率＝2÷预计使用年限

在双倍余额递减法下，应在折旧年限到期前两年内，将固定资产净值扣除预计净残值后的净额平均摊销。其计算公式如下：

最后两年的年折旧＝（固定资产原值-累计折旧-预计净残值）÷2

（2）Excel函数计算法。Excel提供了余额递减折旧函数DDB，计算双倍、多倍余额递减法指定各期的折旧额（不含最后2年），其年折旧额函数公式如下：

＝DDB(cost，salvage，life，period，factor)

式中，period表示需要计算折旧额的期间，它必须使用与life相同的单位；factor表示余额递减速率，如果省略则为2（双倍余额递减法）。其他参数的含义与平均年限法相同。

3.年数总和法

又称合计年限法，是将固定资产的原值减去净残值后的净额（即折旧总额）乘以一个逐年递减的分数来计算各期固定资产折旧额的一种方法。Excel中可用数学公式、也可用函数计算年数总和法各年折旧额。

（1）Excel数学公式计算法。其年折旧额计算公式如下：

年折旧额＝（固定资产原值-预计净残值）×年折旧率

年折旧率＝尚可使用年限÷预计使用年限的年数总和

（2）Excel函数计算法。Excel提供了年数总和法折旧函数SYD，计算年数总和法指定各期的折旧额，其年折旧额函数公式如下：

＝SYD(cost，salvage，life，per)

式中，per参数即period，表示需要计算折旧值的期间。

(www.xing528.com)

某项固定资产原值为85万元，预计使用年限为5年，净残值率5%。要求用平均年限法、年数总和法、双倍余额递减法分别计算各年折旧额。

Excel中设置不同折旧方法的折旧计算表如图5-25所示，将这些折旧方法各年的折旧额用平滑线散点图反映。

图5-25　折旧模型与平滑散点线（Excel 2019图表格式选项卡）

（1）在A1、A2、A4至A11、B2至D2、B5至D5单元格或单元区域中录入相关文字；在B3至D3单元区域中录入已知数据；合并A1至D1、A2至A3、A4至D4单元区域等。

（2）用线性折旧函数SLN计算平均年限法各年折旧额，其方法如下：

a.选定B6单元格，单击插入函数按钮，选择“财务”类别的SLN函数进入“函数参数”对话框，如图5-26（a）所示。

b.在原值参数Cost中键入“＄B＄3”；在净残值参数Salvage中键入“＄B＄3∗＄C＄3”；在折旧年限参数Life中键入“＄D＄3”。注：绝对引用是因为要自动填充公式。

c.单击“确定”按钮，B6单元格公式为“＝SLN（＄B＄3，＄B＄3∗＄C＄3，＄D＄3）”。自动填充B7至B10单元格的公式。键入B11单元格的求和公式“＝SUM（B6：B10）”。

（3）用SYD函数计算年数总和法各年折旧额，其方法如下：

a.选定C6单元格，单击插入函数按钮，选择“财务”类别的SYD函数进入“函数参数”对话框，如图5-26（b）所示。

图5-26　平均年限法SLN（a）与年数总和法SYD（b）函数

b.在原值参数Cost中键入“＄B＄3”；在净残值Salvage参数中键入“＄B＄3∗＄C＄3”；在折旧年限Life参数中键入“＄D＄3”；在折旧期次Per参数中键入“A6”（相对引用）。

c.单击“确定”按钮，C6单元格公式为“＝SYD（＄B＄3，＄B＄3∗＄C＄3，＄D＄3，A6）”。自动填充C7至C10单元格的公式。键入C11单元格的求和公式“＝SUM（C6：C10）”。

（4）用DDB函数及平均法计算年数总和法各年折旧额，其方法如下：

a.选定D6单元格，单击插入函数按钮，选择“财务”类别的DDB函数进入“函数参数”对话框。

b.在原值参数Cost中键入“＄B＄3”；在净残值参数Salvage中键入“＄B＄3∗＄C＄3”；在折旧年限参数Life中键入“＄D＄3”；在折旧期次参数Per中键入“A6”；在余额递减速率参数Factor中键入“2”（也可省略不填）。

c.单击“确定”按钮，D6单元公式为“＝DDB（＄B＄3，＄B＄3∗＄C＄3，＄D＄3，A6，2）”。自动填充D7至D8单元格的公式。

d.由于双倍余额递减法应在到期前两年内将剩余未提取的折旧总额进行平均计提，所以应在D9单元格键入“＝（＄B＄3-＄B＄3∗＄C＄3-SUM（＄D＄6：＄D＄8））/2”。自动填充到D10单元格中。键入D11单元格的求和公式“＝SUM（D6：D10）”。

（5）绘制3种折旧方法各年折旧额的平滑线散点图，方法如下：

a.选定A5至D10单元区域，在Excel 2003及其以前版本单击工具栏的图表向导按钮，在标准类型的“XY散点图”中选择“平滑线散点图”，单击“完成”按钮；在Excel 2007—Excel 2019中单击“插入/图表/插入散点图或气泡图/带直线和数据标记的散点图（带数据点平滑线散点图）”命令。Excel将自动插入平滑线散点图。

b.右击图表区、绘图区等图表元素（对象），选择“数据源”命令，在数据区域中选择系列产生在“列”（Excel 2007—Excel 2019为“行列互换”命令）。

c.右击左部的坐标轴，选择“坐标轴格式”命令进入“坐标轴格式”对话框，在刻度卡片（Excel 2003及其以前版本）或坐标轴选项按钮（Excel 2007—Excel 2019）中，将最小值改为“50 000”，最大值改为“350 000”，将数值交叉于改为“50 000”，将显示单位选择为“万元”（或万、千、百、10 000等）。

同样地，将下部“数值（X）轴”的最大值改为“5”，主要刻度单位改为“1”。

d.添加横坐标轴标题，在Excel 2007—Excel 2019中，在图表工具格式（或布局）选项卡的“坐标轴标题/主要横坐标轴标题/下方”，将其文字修改为“年”；在Excel 2003及其以前版本中右击“图表区”或“绘图区”，选择“图表选项”命令，在“标题”卡片的“数值（X）轴”中输入“年”，单击“确定”按钮。再将该标题拖动到该坐标轴的左边。

e.拖动图表进行位置、大小等的调整。