利润规划模型设计与应用.docVIP

利润规划模型的设计与应用 　　一、基本模型设计 　　1、设置滚动条。利润规划模型如图1所示，假定单价、单位变动成本、销量和固定成本的变动幅度在30％和30％之间，四个窗体滚动条分别用于设置单价、单位变动成本、销量和固定成本的变动幅度，并将滚动条的当前值、最小值、最大值、步长和页步长分别设置为30、0、60、1、10，单元格链接分别指定为“D12”、“D13”、“D14”、“D15”。因为滚动条的值在0～60之间，而变动-幅度在-30％～30％之间，所以在“C12”单元格定义公式“=D12／100-30％”，并将该公式复制到“C13：C15”，这样便得到了单价、单位变动成本、销量和固定成本的变动幅度；然后还要定义变动后的单价、单位变动成本、销量和固定成本。在“E12”单元格输入公式“=C3*(1+c12)”，即变动后单价等于原单价加单价变动额。在“E13”单元格输入公式“=E3*(1+c13)”，即变动后单位变动成本等于原单位变动???本加单位变动成本变动额。在“E14”单元格输入公式“=C4*(1+c1 4)”，即变动后销量等于原销量加销量变动额。在“E15”单元格输入公式“=E4*(1+C15)”，即变动后固定成本等于原固定成本加固定成本变动额。这样，通过调整滚动条的值就能得到单价、单位变动成本、销量和固定成本的变动幅度以及变动后的值。 　　 　　2、设置公式。计算结果区域的相关公式设置如下：(1)税前利润公式。在“C6”单元格输入公式“=(C3-E3)*C4-E4”，即税前利润等于“(单价一单位变动成本)×销量一固定成本”。(2)保本点作业率。在“E6”单元格输入公式“=E7／(C3*C4)”，即保本点作业率等于“保本点销售额÷(单价×销量)”。(3)保本点销量。在“C7”单元格输入公式“=E4／(C3-E3)”，即保本点销量等于“固定成本一(单价一单位变动成本)”。(4)保本点销售额。在“E7”单元格输入公式“=C3*C7”，即保本点销售额等于“单价×保本点销量”。(5)安全边际。在“C8”单元格输入公式“=C3*C4-E7”，即安全边际等于“单价×销量一保本点销售额”。(6)安全边际率。在“E8”单元格输入公式“=C8／(C3*C4)”，即安全边际率等于“安全边际÷(单价×销量)”。(7)单价敏感系数。假定p代表单价，v代表单位变动成本，s代表销量，f代表固定成本，y代表利润，则在“C9”单元格输入公式“=C4*C3／C6”。(8)单位变动成本敏感系数。在“E9”单元格输入公式“=-C4*E3／C6”。(9)销量敏感系数。在“c10”单元格输入公式“=(C3-E3)*C4／C6”。(10)固定成本敏感系数。在“E10”单元格输入公式“=-E4／C6”。 　　变动分析区域的相关公式设置如下：(1)变动后利润。根据利润计算公式，利用变动后的单价、单位变动成本、销量和固定成本计算变动后利润，在“C16”单元格输入公式“=(E12-E13)*E14-E15”。(2)利润变动额。利润变动额等于变动后利润减原税前利润，所以在“E16”单元格输入“=C16-C6”。(3)利润变动百分比。利润变动百分比等于利润变动额除以原税前利润，所以在“C17”单元格输入公式“=E16／C6”。(4)保本点作业率。根据保本点作业率计算公式，利用变动后的单价、销量、保本点销售额计算新的保本点作业率，在“E17”单元格输入公式“=E18／(12*E14)”。(5)保本点销量。根据保本点销量计算公式，利用变动后的固定成本、单价和单位变动成本计算新的保本点销量，在“C18”单元格输入公式“=E15／(E12-E13)”。(6)保本点销售额。新的保本点销售额等于新的保本点销量乘以变动后单价，所以在“E18”单元格输入公式“=C18*E12”。(7)安全边际。根据变动后的单价、销量和新的保本点销售额计算新的安全边际，在“C19”单元格输入公式“=E12*E14--E18”。(8)安全边际率。新的安全边际率等于新的安全边际除以变动后的销售额，所以在“E19”单元格输入公式“=C19／(E12*E14)”。 　　 　　二、设置数据有效性 　　 　　在利润规划模型中，只能输入单价、单位变动成本、销量、固定成本等基本数据。为了保证输入数据的合法性，可以设置数据有效性。假定单价、单位变动成本、销量、固定成本只能是大于0的小数，可作如下设置：选中“C3：C4”、“E3：E4”，然后选择菜单“数据／有效性”，出现对话框，在该对话框的“设置”页，将“允许”设置为“小数”，将“数据”设置为“大于”，将“最小值”设置为O，最后单击“确定”按钮。 　　 　　三、制作本量利分析图 　　 　　1、设置数据源。图表的数据源如图2所示，假定销量在0～3000之间