《Excel在会计与财务管理中的应用》 教案 项目九 本量利分析

Excel在会计与财务管理中的应用课程教案课程名称：Excel在会计与财务管理中的应用课程号：课程学分：2参考学时：32课程性质：专业必修课适用专业：经管类专业上课时间：授课教师：负责人：审核单位：年月日-1-一、课程基本信息课程名称Excel在会计与财务管理中的应用课程号课程性质专业必修课开课部门课程负责人课程团队授课学期学分/学时2/32授课语言汉语课程简介Excel是使用极为广泛的电子表格软件，其以操作简便、功能强大而著称，非常适合缺乏计算机编程语言知识和对数据处理要求不高的财务管理人员使用。本书以Excel2010为基础，通过实例详细介绍了Excel在会计电算化中的应用，并应用Excel对企业运营中的会计流程和财务管理问题进行成因分析。本书应用实例典型，内容丰富，充分利用宏、MicrosofQuery组件、数据透视表组件等完成会计电算化的设计。书中各项目详细介绍了实例的操作步骤，学习者只需按照书中介绍的步骤一步步地实际操作，就能完全掌握本书的内容。授课班级

二、课程授课教案项目九讲课主题本量利分析学时2学时教学目标(1)理解成本性态分析和本量利分析的基本概念与理论知识；(2)掌握Excel中相关分析、回归分析等数据统计分析方法的应用；(3)学会应用Excel解决实际案例中遇到的本量利分析问题。任务目标(1)了解成本性态分析方法；(2)掌握Excel中利用散点图进行简单相关分析；(3)掌握利用函数开展相关分析；(4)能利用数据分析工具确定相关系数。(5)了解本量利分析常用的公式；(6)学会使用Excel计算边际贡献、销售利润等相关数据；(7)使用本量利分析方法为企业做出正确的决策。教学设计授课提纲及重难点分析教学方法及课程思政设计教学时间任务一成本性态分析知识准备一、成本定义及分类(一)成本定义成本是指企业在生产经营过程中，为达到一定目的而应当或可能发生的各种经济资源的价值的牺牲或代价。在管理会计的范畴中，成本的时态可以是过去时、现在完成时或将来时。这与财务会计强调的历史成本概念有较大的差异。(二)成本分类(1)按经济用途分为制造费用和非制造费用。(2)按实际发生时态分为历史成本和未来成本。二、成本性态分析(一)成本性态的含义成本性态是指在一定条件下成本总额与特定业务量之间的依存关系。按照成本与产量的依存关系，成本可分为三大类:固定成本、变动成本、混合成本(二)成本性态分析定义成本性态分析是指在成本性态分类的基础上，按照一定的程序和方法，将全部成本划分为固定成本和变动成本两大类，并建立成本函数模型的过程。成本函数模型通常用y=a+bx来模拟，其中y为成本总额，a为固定成本总额，b为单位变动成本，x为业务量，bx为变动成本总额。(三)成本性态分析方法1.账户分析法账户分析法也称会计分析法或直接分析法，是指根据各有关成本账户的具体内容直接判断成本与业务量之间的依存关系，看其是更接近固定成本还是更接近变动成本，从而确定成本性态的一种成本分解方法。2.工程分析法工程分析法是一种比较合理的混合成本的分解方法。其根据产品生产中的投入产出关系，即各种材料、人工、费用的消耗与产量之间的直接关系来合理区分哪些消耗是变动成本，哪些是固定成本。因为技术测定过程复杂且成本过高，所以工程分析法一般较少采用。3.历史成本法历史成本法也称数学分解法，是指根据过去若干周期(月或年)数据所反映的实际成本与业务量之间的依存关系，对混合成本采用适当的数学方法加以分解，来确定成本性态的一种成本分解方法。历史成本法是混合成本分解方法中最常用的一种方法。根据资料利用的具体形式不同，历史成本法可分为高低点法、散布图法和回归分析法。本项目主要介绍回归分析法。回归分析法是指根据一定期间业务量与相应混合成本之间的历史资料，利用微分极值原理计算出最能反映业务量与成本之间关系的回归直线，从而确定成本性态的一种方法。回归分析法是计算结果最为精准的方法，其具体步如下:(1)计算相关系数r，判断业务量x与成本y之间的线性关系。(2)通过微分极值原理(最小二乘法原理)计算出回归直线方程中的固定成本a和单位变动成本b。(3)将求出的a、b代入直线方程y=a+bx中，得到成本性态分析模型。任务资料福源公司是一家生产制造企业，自2010年以来，公司主要经营的产品是A、B、C。福源公司2010—2021年A、B、C三种主营业务产品的产量和总成本如表9-1所示。要求:(1)分别利用散点图、函数、数据分析方法分析福源公司2010-2021年主营业务产品(A、B、C)的产量与总成本数据之间的关系(相关系数)。(2)利用回归分析方法分析福源公司2010-2021年主营业务产品(A、B、C)的产量与总成本数据之间的关系(a和b的值)。(3)预测福源公司2022年主营业务产品成本并计算主营业务产品成本的构成。任务操作一、输入数据(1)打开Excel，单击“保存”按钮，将Excel工作簿另存为“本量利分析.xlsm”，如图9-1所示。(2)将Sheet1工作表重命名为“成本分析与预测”，建立“成本分析与预测”工作表,如图9-2所示。(3)在“成本分析与预测”工作表中输入2010—2021年主营业务产品的产量和总成本的相关数据，如图9-3所示。二、通过相关分析方法分析福源公司2010—2021年主营业务产品(A、B、C)的产量与总成本数据之间的关系(一)福源公司2010—2021年A产品的产量与总成本之间的相关关系1.采用散点图进行简单相关分析(1)选择图表类型。选中A3:C16单元格区域，选择“插入”选项卡，执行“图表”组中的“散点图”命令，出现散点图的几种类型，如图9-4所示。(2)选择“带平滑线和数据标记的散点图”，随即打开带平滑线和数据标记的散点图。选择“图表工具-设计”选项卡，单击“图表布局”组右下方的下拉箭头，选择布局8,如图9-5所示。(3)编辑散点图。双击散点图中的“图表标题”,即可对其进行编辑,把标题修改为“A产品成本分析与预测”。双击散点图中的横轴，打开“设置坐标轴格式”对话框，在“坐标轴选项”的“最小值”中输入“2010”，在“最大值”中输入“2022”，在“主要刻度单位”中输入“2.0”，在“次要刻度单位”中输入“0.4”，如图9-6所示。结论:通过散点图可以比较直观地看出，当A产品产量变化时，其总成本数据大多呈现同样的分布走势，因此得出福源公司2010-2021年A产品产量与总成本数据之间存在着一定程度的正相关关系。2.采用函数进行简单相关分析(1)计算相关系数。在B18单元格中输入“相关系数”，在C18单元格中输入公式“=CORREL(B4:B15,C4:C15)”，按Enter键，即得到福源公司2010-2021年A产品的产量与总成本之间的相关系数为0.94671687;在C19单元格中输入“=PEARSON(B4:B15,C4:C15)”按Enter键，可以得到相同的结果，如图9-7所示。(2)计算协方差。在B20单元格中输入“协方差”，在C21单元格中输入公式“=COVARIANCE.S(B4:B15,C4:C15)”，按Enter键，即得到福源公司2010-2021年A产品的产量与总成本之间的协方差为35937599.18，如图9-8所示。(3)除了上述直接输入公式方式外,还可以通过“插入函数"命令进行计算。以“协方差的计算为例，选择“公式”选项卡，执行“函数库”组中的“插入函数”命令，打开“插入函数”对话框，在“或选择类别”下拉列表中选择“统计”，在“选择函数”列表框中选择“COVARIANCE.S”函数，如图9-9所示。(4)单击“确定”按钮，打开“函数参数”对话框，单击“Arrayl”后的折叠按钮，选中B4:B15单元格区域;单击“Array2”后的折叠按钮,选中C4:C15单元格区域,如图9-10所示。单击“确定”按钮，会得到相同的结果(协方差为35937599.18)。3.利用数据分析工具确定相关关系除了用函数功能计算两个变量的相关系数和协方差外，Excel2010数据分析工具中还提供了专门进行相关分析的工具，用于计算两个变量的相关系数和协方差。(1)选择“数据”选项卡，执行“分析”组中的“数据分析”命令，打开“数据分析”对话框，在“分析工具”中选择“相关系数”，如图9-11所示。(2)单击“确定”按钮，打开“相关系数”对话框，进行图9-12所示设置。(3)同样，在“分析工具”中选择“协方差”并执行类似的步骤，则可以计算协方差，如图9-13和图9-14所示。结论:从图9-15所示的结果不难看出，福源公司2010-2021年A产品产量与成本数据之间的相关系数很高，达到了0.94以上，说明两者之间正相关的程度很强，也在一定程度上说明两者之间有进行回归的必要性。因为回归分析研究的是自变量或者解释变量对因变量的影响关系，如果参与分析的解释变量和被解释变量之间的相关系数很小，就没有必要进行回归分析。我们可以运用前面讲解的三种方法对B、C两种产品进行成本分析与预测。(二)福源公司2010—2021年B产品的产量与总成本之间的相关关系1.采用散点图进行简单相关分析运用前面介绍的方法绘制B产品成本分析与预测散点图，具体操作步骤及过程不再赘述，最终形成的散点图如图9-16所示。通过散点图可以比较直观地看出，与A产品的走势相同，福源公司2010-2021年B产品产量与成本数据之间同样存在着一定程度的正相关关系，理由是当B产品产量变化时，其总成本数据大多呈现同样的分布走势。2.计算相关系数和协方差可以运用前面插入函数和数据分析的方式进行简单相关分析，计算得到福源公司2010-2021年B产品产量与成本数据之间的相关系数为0.912203707，协方差为23982570.75具体计算结果如图9-17所示。结论:从图9-17所示的结果不难看出，福源公司2010-2021年B产品产量与成本数据之间的相关系数很高，达到了0.91以上，说明两者之间正相关的程度很强，也在一定程度上说明两者之间有进行回归的必要性。(三)福源公司2010—2021年C产品的产量与总成本之间的相关关系1.采用散点图进行简单相关分析运用前面介绍的方法绘制C产品成本分析与预测散点图，具体操作步及过程不再赘述，最终形成的散点图如图9-18所示。通过散点图可以比较直观地看出，与A、B产品的走势相同，福源公司2010-2021年C产品产量与成本数据之间同样存在着一定程度的正相关关系，理由是当C产品产量变化时，其总成本数据大多呈现同样的分布走势。2.计算相关系数和协方差结论：从图9-19所示的结果不难看出，福源公司2010—2021年C产品与成本数据之间的相关系数很高，达到了0.94以上，说明两者之间正相关的程度很强，也在一定程度上说明两者之间有进行回归的必要性。三、通过回归分析方法分析福源公司2010—2021年主营业务产品(A、B、C)的产量与总成本数据之间的关系(一)福源公司2010—2021年A产品的产量与总成本之间的回归关系1.利用函数进行一元线性回归分析(1)在B23单元格中输入“截距”,在C23单元格输入公式“=INTERCEPT(C4:C15,B4:B15)"，按Enter键，即可得到福源公司2010-2021年A产品产量与成本数据之间的回归截距。(2)在B24单元格中输入“斜率”，在C24单元格中输入公式“=SLOPE(C4:C15.B4:B15)”，按Enter键，即可得到福源公司2010-2021年A产品产量与成本数据之间的回归斜率。(3)在B25单元格中输入“判定系数”，在C25单元格中输入公式“=RSQ(C4:C15.B4:B15)”，按Enter键，即可得到福源公司2010-2021年A产品产量与成本数据之间的回归判定系数，计算结果如图9-20所示。(4)除了直接输入公式的方法外，也可以通过“插入函数”命令加以计算。例如，计算“截距”，选中B27单元格，选择“公式”选项卡，执行“函数库”中的“插入函数”命令，打开“插入函数”对话框，在“或选择类别”下拉列表中选择“统计”，在“选择函数”列表框中选择“INTERCEPT”函数，如图9-21所示。(5)利用函数的数组形式进行计算。选中B31:C35单元格区域,输入公式“=LINEST(C4C15,B4:B15,1,1)”，并按Shift+Ctrl+Enter组合键执行数组运算，得到数组运算的结果,如图9-23所示。2.利用数据分析工具进行回归分析(1)选择回归工具。选择“数据”选项卡,执行“分析”组中的“数据分析”命令,打开“数据分析”对话框，在“分析工具”列表框中选择“回归”选项，如图9-25所示，单击“确定”按钮，随即打开“回归”对话框。(2)设置“回归”选项。在“回归”对话框中首先设置“输入”内容,单击“Y值输入区域后面的折叠按钮，选中$C$3:$C$15单元格区域:单击“X值输入区域”后面的折叠按钮选中$BS3:$BS15单元格区域。因为输入区域包含标志项，所以选中“标志”复选框;选中“置信度”复选框，并默认为95%。然后设置“输出选项”，在“新输出区域”中选中SB$38单元格区域。如果选中“新工作表组”单选按钮，则表示将输出结果显示在一个新的工作表上。选中“残差”“正态分布”中的全部复选框,以观察残差、标准残差、残差图、线性拟合图以及正态概率图等信息，如图9-26所示。(二)福源公司2010—2021年B产品的产量与总成本之间的回归关系我们可以直接采用上述讲解的两类方法进行分析，由于本项目篇幅的限制，本部分仅利用数据分析工具研究福源公司2010-2021年B产品的产量与总成本之间的回归关系。对于利用函数进行一元线性回归分析，读者可以利用课余时间进行练习。从图9-35中可以得到以下结论：第一部分是回归统计分析的结果,MultipleR为0.912203707,RSquare为0.832115602AdjustedRSquare为0.815327162，标准误差为2571.52361，观测值为12。其中，需要特别注意的是RSquare与AdiustedRSquare分别为回归模型的判定系数以及修正的判定系数，在很大程度上代表着模型的解释能力，相应的数值越大，说明模型的解释能力越强。本例中回归模型的判定系数以及修正的判定系数都接近0.8，说明模型解释能力很好，或者选取的解释变量能够解释被解释变量的大部分信息。第二部分是回归模型的方差分析结果，df代表自由度，模型的F统计值为49.56479657显著性P值(SignifcanceF)为3.53996x10-5，远小于通常具有显著性意义的0.05，说明模型非常显著。第三部分是回归模型的变量系数值、变量系数的标准误差、T统计量、显著性P值以及95%的置信区间等信息。本例中产量的变量系数值为1.242412868，变量系数的标准误差为0.17647341，T统计量为7.040227026，显著性P值为3.53996x10-5，95%的置信区间为[0.849205607,1.635620128];常数项的变量系数值为3588.365759，变量系数的标准误差为1623.042855，T统计量为2.210887869，显著性P值为0.051483202，95%的置信区间为[-27.99908519,7204.730603]。从结果中可以看出，自变量产量和常数项的系数都为正，而且非常显著(远小于通常具有显著性意义的0.05)，这说明固定成本为正值而且非常显著，同时产量会显著作用于成本总额，或者说变动成本对于总成本的影响是构成重要性的。依据这一结果，我们也可以写出福源公司2010—2021年B产品产量与成本数据之间的回归方程：成本=3588.365759+产量×1.242412868(三)福源公司2010—2021年C产品的产量与总成本之间的回归关系我们可以直接采用上述讲解的两类方法进行分析，由于本项目篇幅的限制，本部分仅利用数据分析工具研究福源公司2010-2021年C产品的产量与总成本之间的回归关系。对于利用函数进行一元线性回归分析，读者可以利用课余时间进行练习。(1)选择回归工具。选择“数据”选项卡，执行“分析”组中的“数据分析”命令打开“数据分析”对话框，在“分析工具”列表框中选择“回归”选项，如图9-41所示，单击“确定”按钮，打开“回归”对话框。(2)设置“回归”选项。在“回归”对话框中首先设置“输入”内容，单击“Y值输入区域”后面的折叠按钮，并选中$G$3:$G$15单元格区域;单击“X值输入区域”后面的折叠按钮，并选中$FS3:SGS15单元格区域。结论：第一部分是回归统计分析的结果,MultipleR为0.946696426,RSquare为0.896234123,AdjustedRSquare为0.8858575535，标准误差为195.0459565，观测值为12。其中，需要特别注意的是RSquare与AdiustedRSquare分别为回归模型的判定系数以及修正的判定系数，在很大程度上代表着模型的解释能力，相应的数值越大，说明模型的解释能力越强。本例中回归模型的判定系数以及修正的判定系数都接近0.9，说明模型解释能力很好，或者选取的解释变量能够解释被解释变量的大部分信息。第二部分是回归模型的方差分析结果，df代表自由度，模型的F统计值为86.37079436显著性P值(SignifcanceF)为3.0978x10-6，远小于通常具有显著性意义的0.05,说明模型非常显著。第三部分是回归模型的变量系数值、变量系数的标准误差、T统计量、显著性P值以及95%的置信区间等信息。本例中产量的变量系数值为0.124649304，变量系数的标准误差为0.013412397，RT统计量为9.293588885，显著性P值为3.0978x10-6，95%的置信区间为[0.094764621,0.154533987];常数项的变量系数值为398.2355439，变量系数的标准误差为232.6468712,T统计量为1.711759723，显著性P值为0.117723383，95%的置信区间为[-120.1339887.916.6050765]。从结果中可以看出，自变量产量和常数项的系数都为正,而且非常显著(远小于通常具有显著性意义的0.05)，这说明固定成本为正值而且非常显著，同时产量会显著作用于成本总额，或者说变动成本对于总成本的影响是构成重要性的。依据这一结果，我们也可以写出福源公司2010—2021年C产品产量与成本数据之间的回归方程：成本=398.2355439+产量×0.124649304四、对福源公司2022年主营业务产品成本进行预测在C16单元格中输入公式“=C23+B16*C24”,在E16单元格中输入公式“=E23+D16*E24”，在G16单元格中输入公式“=G23+F16*G24”。分别假定福源公司2022年A产品产量为12000件、B产品产量为10000件、C产品产量为18000件的情况下，得到福源公司2022年主营业务产品成本预测结果，如图9-49所示。说明：本例中对福源公司2022年主营业务产品成本预测基于前述确定的回归分析方程。A产品：成本=6137.435535+产量×1.86930706B产品：成本=3588.365759+产量×1.242412868C产品：成本=398.2355439+产量×0.124649304在C17单元格中输入公式“=B16*C24/C16”，在E17单元格中输入公式“=D16*E24/E16”，在G17单元格中输入公式“=F16*G24/G16”。在分别假定福源公司2022年A产品产量为12000件、B产品产量为10000件、C产品产量为18000件的情况下，得到福源公司2022年主营业务产品成本构成预测结果(变动成本占比)。五、计算福源公司2022年主营业务产品成本构成通过计算发现，福源公司2022年A产品变动成本占比为78.52%，B产品变动成本占比为77.59%，C产品变动成本占比为84.93%。福源公司2022年三种主营业务产品的变动成本占比都比较高。37min任务二本量利分析知识准备一、本量利分析的基本假设(一)成本性态分析假设成本性态分析假设是假定成本性态分析工作已经完成，全部成本费用已经按成本性态划分为固定成本和变动成本两部分。成本性态分析假设是本量利分析的基础，也是本量利分析的出发点。(二)相关范围假设1.期间假设不管是固定成本还是变动成本，固定性和变动性都体现在特定期间内，金额大小也是在特定期间内加以计量得到的。随着时间的推移，固定成本及其内容会发生变化，单位变动成本及其内容也会发生变化。2.业务量假设成本按成本性态划分为固定成本和变动成本是在一定业务量范围内分析和计量的结果，当业务量发生变化尤其是变化较大时，成本性态就有可能发生改变。(三)模型线性假设1.固定成本不变假设本量利分析中的模型线性假设首先是固定成本不变，用模型表示为y=a。也就是说，在企业经营能力的相关范围内，固定成本保持不变，即在一定期间和业务量范围内固定成本的曲线为一条水平线。2.单位变动成本不变假设与固定成本不变假设近似,单位变动成本不变假设也是在一定的相关范围内保持不变。单位变动成本与业务量之间的完全线性关系用模型表示为y=bx。基于完全线性假设，变动成本的曲线表现为一条从原点出发的直线，该直线的斜率就是单位变动成本。3.销售单价不变假设假设销售单价也在相关范围内保持不变，这样销售收入与销售量之间也呈线性关系，用模型表示为y=px(p为销售单价)。(四)产销平衡假设假设当期产品的生产量和业务量相一致，不考虑存货变动对利润的影响，即假设每期生产的产品总量总是能在当期全部售出，产销平衡。假设产销平衡，主要是在盈亏平衡分析时不考虑存货的影响。因为盈亏平衡是一种短期决策，仅仅考虑特定时期全部成本的收回，而存货中包含了以前时期的成本，所以不在考虑范围内。二、本量利分析的基本原理本量利分析考虑的相关因素主要包括销售量、销售单价、销售收入、单位变动成本、固定成本、利润等。这些因素之间的关系可以用下列基本公式来反映:利润=销售收入-总成本=销售单价x销售量-(变动成本+固定成本)=销售单价x销售量-单位变动成本x销售量-固定成本=(销售单价-单位变动成本)x销售量-固定成本三、本量利分析的相关概念边际贡献也称贡献毛益或边际利润，是指产品的销售收入减去变动成本之后的金额，是用来衡量企业经济效益的一项重要指标。边际贡献通常有两种表现形式:一是总额概念，称边际贡献总额(Tcm)，通常简称“边际贡献”，是指产品的销售收入总额减去变动成本总额后的余额;二是单位概念，称单位边际贡献(cm)，是指产品的销售单价减去单位变动成本后的余额，反映的是每一个单位产品的创利能力，即每增加一个单位产品销售可提供的创利额。两者用公式表示如下：边际贡献=销售收入-变动成本=(销售单价-单位变动成本)×销售量=单位边际贡献×销售量单位边际贡献=销售单价-单位变动成本任务资料本任务沿用任务一的案例，福源公司是一家生产制造企业，自2010年以来，公司主要经营的产品是A、B、C三种产品。福源公司2022年A、B、C三种主营业务产品的总销售收入预测数据如表9-2所示。要求：(1)利用福源公司2022年主营业务产品销售收入预测数据，结合任务一的相关结果,计算福源公司2022年的边际贡献、销售利润等，针对每种主营业务产品进行点评分析。(2)假定在第(1)步中计算得到的福源公司2022年主营业务利润数据不够理想，未达到股东预期，管理层提出了以下三种改进方案，在Excel2010中运用本量利分析方法帮助股东做出正确的决策。①不再生产预测产生亏损的A产品，其他产品(B、C)维持原生产量不变。②不再生产预测产生亏损的A产品，将释放出的产能用于B和C的生产，其中合理预计B产品产量将增加40%，C产品产量将增加10%。③继续生产预测产生亏损的A产品，但压缩为原定计划的70%，将释放出的产能用于B产品的生产，合理预计B产品产量将增加36%。任务操作一、输入数据(1)打开Excel“本量利分析.xlsm”文件，选择一张空白工作表，重命名为“本量利分析”，建立“本量利分析”工作表。将A1:E1单元格区域合并并居中，在其中输入“本量利分析”，字体设置为“宋体”，字号设置为“18”号，并进行加粗处理:在A2:E2单元格区域中分别输入“产品”“A产品”“B产品”“C产品”“总计”。(2)在B4:E8单元格区域设置公式，如表9-3所示。(3)输入以上内容后，最终形成的福源公司2022年本量利分析预测结果如图9-52所示。结论:从图9-52中可以看出，福源公司2022年销售利润预测为亏损状态，即-99.53770731万元，预测结果不甚理想。其中，A产品销售利润预测为-2699.120253万元，B产品销售利润预测为2241.505565万元,C产品销售利润预测为358.0769808万元。在资源和条件具备一定调整可能性的情况下，福源公司需要对当前的生产计划进行必要调整，以获得更加优异的经营业绩，回报股东的信任。二、方案分析(1)在第一种备选方案下，利用福源公司2022年主营业务销售收入预测数据，结合任务一的相关结果，计算福源公司2022年的边际贡献、销售利润等。第一种备选方案为不再生产预测产生亏损的A产品，其他产品(B、C)维持原生产量不变。复制“本量利分析”工作表，并将其重命名为“本量利分析(方案一)”工作表，将B7:D8单元格区域中的内容清除后合并并居中，在E7单元格复制并粘贴总固定成本数据(仅粘贴数值)，将B3:B6单元格区域内的数值直接删除，在E8单元格输入“=E3-E4-本量利分析!B7”,(虽然不再生产A产品，但A产品产生的固定成本是一直存在的，所以销售利润需要减去A产品产生的固定成本)，得到的最终结果如图9-53所示。结论:从图9-53中可以看出，福源公司2022年销售利润预测仍为亏损状态，而且号损的更多，达到了-3537.852989万元，预测结果更加不理想。这是因为A产品为亏损主营业务