《《Excel会计应用》课件—11-Excel-2013在利润管理中的应用》由会员分享，可在线阅读，更多相关《《Excel会计应用》课件—11-Excel-2013在利润管理中的应用（58页珍藏版）》请在金锄头文库上搜索。
1、Excel Excel 在会计中的应用在会计中的应用项目11 Excel 2013在利润管理中的应用利润预测1Excel 2013在本量利分析中的应用2目 录CONTENTS利润预测11.1任务项目引例 假设龙腾公司只生产和销售A产品，预计的销售量、单价和成本等数据如图11-1中的“已知条件”区域所示，要求建立一个分别用销售量、单价、单位变动成本和固定成本表示保本点的计算模型。图11-1 单品种生产时保本点的计算模型Qusetion 1:Qusetion 1: 利润预测有哪些方法？11.1.1 利润预测的方法 比率预测法是根据各种不同的利润指标来预测利润的方法。通常可首先根据基期的利润和销售收
2、入、成本费用、资金总额等有关指标计算出基期的销售收入、成本费用、资金总额来预测利润。有关的计算公式为 预测销售利润预计销售收入销售收入利润率 预计成本费用成本费用利润率 预计资金总额资金利润率1.比率预测法11.1.1 利润预测的方法 经营杠杆系数反映息税前利润变动率和销售变动率之间的关系。根据基期的有关资料计算出经营杠杆系数，再结合预测期销售额的变动率，即可以预测利润，计算公式为 经营杠杆系数（DOL）利润变动率销售量（额）变动率基期边际贡献基期息税前利润 预测期息税前利润基期息税前利润 （1经营杠杆系数销售变动率）预测期税前利润预测息税前利润-预测期债务利息2.经营杠杆系数预测法11.1.
3、1 利润预测的方法 本量利分析法全称为成本-业务量（生产量或销售量）-利润分析法，也称损益平衡分析法，主要根据成本、业务量和利润三者之间的关系分析某一因素的变化对其他因素的影响，可用于利润、成本和业务量的预测。 根据成本、业务量、利润之间的关系，目标利润用公式表述如下： 目标利润销售收入-（变动成本总额固定成本总额） 销售单价销售量-（单位变动成本销售量固定成本总额） 销售量 （单价-单位变动成本）-固定成本总额 销售量单位边际贡献-固定成本总额 边际贡献总额-固定成本总额3.本量利分析法Qusetion 2:Qusetion 2: 利用Excel 2013建立利润预测模型，有哪些操作步骤？1
4、1.1.2 利用Excel 2013建立利润预测模型例11-1 A、B、C三家公司利润预测的有关资料如图11-2的“已知条件”区域所示。要求建立一个分别利用比率预测法、经营杠杆系数法和本量利分析法预测三家公司利润的模型。图11-2 利润预测模型11.1.2 利用Excel 2013建立利润预测模型建立模型的具体计算步骤如下。（1）设计模型的结构，如图11-2的“计算过程与结果”区域所示。（2）在B12中输入公式“$B$7/B4”，并将其复制到B13：C14。（3）在C15中输入公式“C4*B12”，并将其复制到C16：C17。（4）在C18中输入公式“AVERAGE（C15：C17）”。（5）
5、在E12中输入公式“E3*（E4-E5）-E6”（6）在E13中输入公式“E3*（E4-E5）/E12”。（7）在E14中输入公式“E12*（1E13*E7）”。（8）在E15中输入公式“E14-E8”。（9）在E18中输入公式“SUMPRODUCT（G4：H4，（G5：H5-G6：H6）-G7”。模型的运行结果如图11-3所示。11.1.2 利用Excel 2013建立利润预测模型图11-3 利润模型运行结果Qusetion 3:Qusetion 3: 利用Excel 2013分析确保实现目标利润有哪些措施？11.1.3 利用Excel 2013分析确保实现目标利润的措施1.通过采取单项措施
6、确保实现目标利润的分析模型例11-2 某公司2014年的销售量和息税前利润等实际数据以及所确定的2015年的息税前利润目标增长率数据如图11-4的“已知条件”区域所示。要求建立一个对于采取单项措施确保目标利润的完成情况进行分析的模型。图11-4 通过采取单项措施确保实现目标利润的分析模型11.1.3 利用Excel 2013分析确保实现目标利润的措施 建立模型的具体计算步骤如下。 （1）设计模型的结构，如图11-4的“计算过程与结果”区域所示。 （2）在E8中输入公式“B8*（1B9）”。 （3）在H4中输入公式“E4*（B5-B6）-B7”。 （4）单击“数据”菜单，在“数据工具”组中单击“
7、模拟分析”下拉菜单，选择“单变量求解”，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为$H$4，将“目标值”设置为300 000，将“可变单元格”设置为$E$4，然后单击“确定”按钮，最后在系统弹出的“单变量求解状态”对话框中单击“确定”按钮。 （5）在H5中输入公式“B4*（E5-B6）-B7”。11.1.3 利用Excel 2013分析确保实现目标利润的措施 （6）执行“数据”菜单中的“单变量求解”命令，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为$H$5，将“目标值”设置为300 000，将“可变单元格”设置为$E$5，然后单击“确定”按钮，最后在系统弹出的“单
8、变量求解状态”对话框中单击“确定”按钮。 （7）在H6中输入公式“B4*（B5-E6）-B7”。 （8）执行“数据”菜单中的“单变量求解”命令，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为$H$6，将“目标值”设置为300 000，将“可变单元格”设置为$E$6，然后单击“确定”按钮，最后在系统弹出的“单变量求解状态”对话框中单击“确定”按钮。 （9）在H7中输入公式“B4*（B5-B6）-E7”。11.1.3 利用Excel 2013分析确保实现目标利润的措施 （10）执行“数据”菜单中的“单变量求解”命令，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为$H$7，将
9、“目标值”设置为300 000，将“可变单元格”设置为$E$7，然后单击“确定”按钮，最后在系统弹出的“单变量求解状态”对话框中单击“确定”按钮。 （11）在F4：F8中输入公式“E4：E8-B4：B8”。 （12）在G4：G8中输入公式“F4：F8/B4：B8”。 模型的运行结果如图11-5所示。图11-5 利润分析模型运行结果11.1.3 利用Excel 2013分析确保实现目标利润的措施2.通过调整产销结构实现目标利润的决策模型例11-3 某公司2014年生产甲、乙、丙三种产品，其中乙产品属于政府订购的特种物资，订单无法改变，且2015年乙产品的订单与2014年相同。2015年该公司预定
10、的目标利润总额为25万元，其他有关资料如图11-6的“已知条件”区域所示。该公司希望在产品销售收入总额保持不变的前提下，通过调整甲、丙产品的产销结构来实现2015年的目标利润。要求建立一个通过调整甲、丙产品的产销结构来实现2015年的目标利润的决策模型。 在企业同时生产多种产品的情况下，除了可以通过采取如上所述的各种单项措施实现利润目标以外，还可以通过调整产销结构来实现目标利润。11.1.3 利用Excel 2013分析确保实现目标利润的措施建立模型的具体计算步骤如下。（1）设计模型的结构，如图11-6的“计算过程与结果”区域所示。（2）在B11：D11中输入公式“B4：D4/E4”。（3）在
11、B12：D12中输入公式“B6：D6/B4：D4”图11-6 通过调整产销结构实现目标利润的决策模型11.1.3 利用Excel 2013分析确保实现目标利润的措施 由计算结果可以看出，甲产品的销售收入比重最高，但其边际贡献率低；丙产品的销售收入比重最低，但其实际贡献率高。因此，在乙产品维持现状的条件下，为实现目标利润，应适当降低甲产品的销售收入比重，相应增加丙产品的销售收入比重。 （4）在H10中输入公式“（B7F5）/E4”。 （5）在H11中输入公式“（B11-H12）*B12C11*C12（D11H12）*D11-H10”。这里采用的实际综合边际贡献率的计算公式为实际综合边际贡献率=（
12、某种产品的边际贡献率该产品调整后的销售收入比重） （6）执行“数据”菜单中的“单变量求解”命令，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为$H$11，将目标值设置为0，将“可变单元格”设置为$H$12，然后单击“确定”按钮，最后在系统弹出的“单变量求解状态”对话框中单击“确定”按钮。 （7）在D13中输入公式“B11-H12”。 （8）在H13中输入公式“D11H12”。11.1.3 利用Excel 2013分析确保实现目标利润的措施模型的运行结果如图11-7所示。图11-7 利润决策模型运行结果Excel 2013在本量利分析中的应用11.2任务Qusetion 4:Quse
13、tion 4: 利用Excel 2013建立本量利分析表，有哪些操作步骤？11.2.1 利用Excel 2013建立本量利分析表 本量利就是对成本、销量和利润的分析。成本、销量和利润三者之间的变化关系是决定企业是否盈利的关键，本量利分析是定量分析出企业成本、销量和利润三者之间的变化关系。盈亏平衡点指标是企业盈亏分界线，它也由本量利分析引出。 这里需要用到一些公式：成本单位变动成本产量固定费用收入售价产量 在达到盈亏平衡点时，成本等于收入。此时的盈亏平衡量固定费用（售价-单位变动成本）。固定费用保持不变，售价和单位成本可以变动，因此盈亏平衡量是变动的。根据盈亏平衡量，可以求得，盈亏平衡收入盈亏平
14、衡量售价。11.2.1 利用Excel 2013建立本量利分析表 具体操作步骤如下。 （1）新建工作表并输入字段名新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的工作表。 （2）输入数据并设置格式。在B3 ：B18区域输入产量数据，并新建“固定费用”“售价”“单位成本”等新的数据区域备用，如图11-8所示。图11-8 设置格式并输入数据 （3）创建成本公式。在C3中输入公式“（$K$26*B3$C$28）/10000”，并将该公式复制到C4：C18区域。 （4）创建收入公式。在D3中输入公式“（$H$26*B3）/10000”，并将该公式复制到D4：D18区域。 （5）创建利润公式
15、。在E3中输入公式“D3-C3”，并将该公式复制到E4：E18区域，如图11-9所示。11.2.1 利用Excel 2013建立本量利分析表图11-8 设置格式并输入数据11.2.1 利用Excel 2013建立本量利分析表图11-9 创建公式11.2.1 利用Excel 2013建立本量利分析表 （6）设置盈亏平衡线横坐标数据。在C20中输入标题“盈亏平衡线辅助数据”，在C22中输入公式“ROUND（C28/（H26-K26），2）”，并在C23：C25区域输入公式“C22”。 （7）设置盈亏平衡线纵坐标数据。在D22中输入“1 800”，在D23中输入公式“（C23 * H26 ）/10
16、000”，在D24中输入“0”，在D25中输入“-100”。 （8）设置盈亏平衡量和盈亏平衡收入。在B30中输入“盈亏平衡量”，在C30中输入公式“C23”，在B31中输入“盈亏平衡收入”，在C31中输入公式“D23”。同时，为了方便查看，可以设置一下相关单元格的单位和格式，如图11-10所示。11.2.1 利用Excel 2013建立本量利分析表图11-10 设置盈亏平衡量和盈亏平衡收入11.2.1 利用Excel 2013建立本量利分析表 （9）添加新命令。在“自定义快速访问工具栏”中选择“其他命令”。 选定操作区域，执行“格式”菜单中的“自动套用格式”命令，在格式列表框中选取一款格式样式，单击“确定”按钮即可，如图11-11所示。11.2.1 利用Excel 2013建立本量利分析表11.2.1 利用Excel 2013建立本量利分析表图11-11 添加新命令11.2.1 利用Excel 2013建立本量利分析表 （10）添加滚动条。单击“自定义快速访问工具栏”，弹出一个对话框，在下拉列表中选择“不在功能区中的命令”，添加“滚动条（窗体控件）”，如图11-12所示。11.2.1
