利润规划模型的设计与应用

利润规划模型的设计与应用　天津财经大学　吕志明　一、基本模型设计　单元格输入公式“＝Ｃ３　Ｃ７”，即保本点销售额等于“单价Ｘ保本点　销量”。（５）安全边际。在“ｃ８”单元格输入公式“＝Ｃ３＂Ｃ４一Ｅ７”，　１、设置滚动条。利润规划模型如图１所示，假定单价、单位变　动成本、销量和固定成本的变动幅度在－３０％和３０％之间，四个窗　即安全边际等于“单价×销量一保本点销售额”。（６）安全边际　率。在“Ｅ８”单元格输入公式“＝Ｃ８／（Ｃ３＂Ｃ４）”，即安全边际率等于　“安全边际－（单价×销量）”。（７）单价敏感系数。假定Ｐ代表单　体滚动条分别用于设置单价、单位变动成本、销量和固定成本的　变动幅度，并将滚动条的当前值、最小值、最大值、步长和页步长　分别设置为３０、０、６０、１、１０，单元格链接分别指定为“Ｄ１２”、　“Ｄ１３”、“Ｄ１４”、“Ｄ１５”。因为滚动条的值在０．－６０之间，而变动　幅度在－３０％～３０％之间，所以在“Ｃ１２”单元格定义公式　“＝Ｄ１２／１００－３０％”，并将该公式复制到“Ｃ１３：Ｃ１５”，这样便得到了　单价、单位变动成本、销量和固定成本的变动幅度；然后还要定义　变动后的单价、单位变动成本、销量和固定成本。在“Ｅｌ２”单元格　输入公式“＝Ｃ３　（１＋Ｃ１２）”，即变动后单价等于原单价加单价变动　额。在“Ｅｌ３”单元格输入公式“－－－Ｅ３　（１＋Ｃ１３）”，即变动后单位变动　成本等于原单位变动成本加单位变动成本变动额。在“Ｅｌ４”单元　格输入公式“＝Ｃ４　（１＋ｃ１４）”，即变动后销量等于原销量加销量变　动额。在“Ｅｌ５”单元格输入公式“＝Ｅ４　（１＋ｃ１５）”，即变动后固定成　本等于原固定成本加固定成本变动额。这样，通过调整滚动条的　值就能得到单价、单位变动成本、销量和固定成本的变动幅度以　及变动后的值。　Ｂ　Ｃ　Ｄ　Ｅ　ｌ　利润规划模型　２　基本数据　３　单价　５Ｏ．ｏ０　单价变动成本　２０．０ｏ　４　销量　ｌ５０ｏ．０ｏ　固定成本　３０ｏｏ０．ｏ０　５　计算结果　６　利润总额　ｌ５Ｏｏｏ．０ｏ　保本点作业率　６６．６７％　７　保本点销量　ｌ０ｏＯ．０ｏ　保本点销售额　５０ｏｏ０．ＯＯ　８　安全边际　２５Ｏｏｏ．０ｏ　安全边际率　３３．３３％　９　单价敏感系数　５．０ｏ　单位变动成本敏感系数　一２．０ｏ　１０　销量敏感系数　３．０ｏ　固定成本敏感系数　－２．ＯＯ　ｌｌ　变动分析　ｌ２　单价　０％　５０．０ｏ　ｌ３　单价变动成本　Ｏ％　２０．０ｏ　１４　销量　１Ｏ％　１６５０．０ｏ　ｌ５　固定成本　０％　３０ｏｏ０．０ｏ　１６　变动后利润　１９５００．０ｏ　利润变动额　４５００．０ｏ　１７　利润变动百分比　３０．０ｏ％　保本点作业率　６０．６１％　ｌ８　保本点销量　ｌｏｏＯ．０ｏ　保本点销售额　５０ｏｏ０．０ｏ　１９　安全边际　３２５００．０ｏ　安全边际率　３９３９％　图１　２、设置公式。计算结果区域的相关公式设置如下：（１）税前　利润公式。在“ｃ６”单元格输入公式“＝（Ｃ３－Ｅ３）　Ｃ４一Ｅ４”，即税前　利润等于“（单价一单位变动成本）×销量一固定成本”。（２）保　本点作业率。在“Ｅ６”单元格输入公式“＝Ｅ７／（Ｃ３　Ｃ４）”，即保本点　作业率等于“保本点销售额÷（单价Ｘ销量）”。（３）保本点销量。　在“ｃ７”单元格输入公式“－－Ｅ４／（Ｃ３－Ｅ３）”，即保本点销量等于“固　定成本・（单价一单位变动成本）”。（４）保本点销售额。在“Ｅ７”　甜　近孔・综合版２ＯＯ６￣１２期　价，ｖ代表单位变动成本，ｓ代表销量，ｆ代表固定成本，Ｙ代表利润，　则在“ｃ９”单元格输入公式“＝Ｃ４　Ｃ３／Ｃ６”。（８）单位变动成本　感系数。在“Ｅ９”单元格输入公式“＝－Ｃ４　Ｅ３／Ｃ６”。（９）销量敏感　系数。在“Ｃ１０”单元格输入公式“＝（Ｃ３－Ｅ３）　Ｃ４／Ｃ６”。（１０）固定成　本敏感系数。在“Ｅ１０”单元格输入公式“＝－Ｅ４／Ｃ６”。　变动分析区域的相关公式设置如下：（１）变动后利润。根据利　润计算公式，利用变动后的单价、单位变动成本、销量和固定成本计　算变动后利润，在“Ｃ１６”单元格输入公式‘ｌ＿　１２－Ｅ１３）＊Ｅｌ４一Ｅｌ５”。　（２）利润变动额。利润变动额等于变动后利润减原税前利润，所以　在“Ｅｌ６”单元格输入“＝Ｃ１６一ｃ６”。（３）利润变动百分比。利润变　动百分比等于利润变动额除以原税前利润，所以在“Ｃ１７”单元格输　入公式“＝Ｅ１６／Ｃ６”。（４）保本点作业率。根据保本点作业率计算公　式，利用变动后的单价、销量　保本点销售额计算新的保本点作业　率，在“Ｅｌ７”单元格输入公式“＝Ｅ１８／（Ｅ１２＊Ｅｌ４）”。（５）保本点销量。　根据保本点销量计算公式，利用变动后的固定成本、单价和单位变　动成本计算新的保本点销量，在“Ｃ１８”单元格输入公式“＝Ｅｌ５／　（Ｅ１２－Ｅ１３）”。（６）保本点销售额。新的保本点销售额等于新的保本点　销量乘以变动后单价，所以在“Ｅｌ８”单元格输入公式“＝Ｃ１８＊Ｅｌ２”。　（７）安全边际。根据变动后的单价、销量和新的保本点销售额计算　新的安全边际，在“Ｃ１９”单元格输入公式“＝Ｅｌ２＊Ｅ１４－Ｅ１８”。（８）安　全边际率。新的安全边际率等于新的安全边际除以变动后的销售　额，所以在“Ｅｌ９”单元格输入公式“＝Ｃ１９／（Ｅ１２　Ｅｌ４）”。　二、设置数据有效性　在利润规划模型中，只能输入单价、单位变动成本、销量、固定　成本等基本数据。为了保证输入数据的合法性，可以设置数据有效　性。假定单价、单位变动成本、销量、固定成本只能是大于０的小　数，可作如下设置：选中“Ｃ３：Ｃ４”、“Ｅ３：Ｅ４”，然后选择菜单“数据　／有效性”，出现对话框，在该对话框的“设置”页，将“允许”设置’　为“小数”，将“数据”设置为“大于”，将“最小值”设置为０。最　后单击“确定”按钮。　三、制作本量利分析图　１、设置数据源。图表的数据源如图２所示，假定销量在０＂３０００　之间，用模拟运算表生成数据。（１）在“Ｈ３”单元格输入公式　“＝Ｅ１４　Ｅ１３＋Ｅ１５”，即总成本等于总变动成本加固定成本，各单元　格的数据含义同图１；在“Ｉ３”单元格输入公式“－－Ｅｌ２＋Ｅｌ４”。即销　售收入等于销量乘以单价；在“Ｊ３”单元格输入公式“＝Ｉ３一Ｈ３”．即　利润总额等于销售收入减总成本。（２）假定销量在０￣３０００之间　维普资讯 http://www.cqvip.com 变动，变动幅度为５００，在“Ｇ４”单元格输入０，在“Ｇ５”单元格输　入５００，然后选中“Ｇ４：Ｇ５”，拖动填充柄直到“Ｇ１０”单元格，此时　各单元格以５００为等差自动填充。（３）选中区域“Ｇ３：Ｊ１０”，然后　选择菜单“数据／模拟运算表”，出现对话框，将引用列的单元格　设置为“Ｅｌ４”即销量所在单元格，此时各销量对应的总成本、销　固定成本等基本数据后，在计算结果区域便会自动显示税前利润、　保本点作业率、保本点销量、保本点销售额、安全边际、安全边际　率、单价敏感系数、单位变动成本敏感系数、销量敏感系数和固定　成本敏感系数等指标，并可进行相关分析。　２、本量利的图形化分析。可以直接根据本量利分析图了解总　售收人、利润总额等数据自动填充。　Ｇ　Ｈ　Ｉ　Ｊ　成本、销售收人、利润总额与销量的关系，同时可以清楚地看到保　销售收入　８２５００　０　２５Ｏ０ｏ　５Ｏ０（）ｏ　２　３　４　．销量　０　５０ｏ　ｌＯ０ｏ　总成本　６３Ｏ０ｏ　３Ｏ０（）ｏ　）ｏ０ｏ　５Ｏ０（）ｏ　利润总额　ｌ９５０ｏ　—３Ｏ００ｏ　—ｌ５Ｏ０ｏ　Ｏ　本点销量即利润等于０时的销量。当调整模型中的单价、单位变动　成本、固定成本等数据时，本量利分析图也会根据当前数据自动调　整，显示当前的总成本线、销售收入线和利润总额线。　３、分析实现目标利润的条件。（１）采取单项措施确保目标利　润实现。要计算利润增长１５％、其他条件不变的情况下销量的增　５　６　７　ｌ５０ｏ　６ｏｏ０ｏ　７５Ｏ０ｏ　ｌ５Ｏ０ｏ　８　２Ｏ０ｏ　７Ｏ０（）ｏ　１００００Ｏ　３０００ｏ　９　２５０ｏ　８Ｏ０（）ｏ　１２５Ｏｏ０　４５ｏｏＯ　ｌＯ　３Ｏ０ｏ　９ｏｏ（）ｏ　ｌ５ｏｏ（）ｏ　６０Ｏ０ｏ　图２　２、制作图表。（１）选中区域“Ｇ４：Ｊ１０”，然后选择菜单“插入／　图表”，出现图表制作向导。（２）将图表类型设置为平滑线散点　图，然后单击“下一步”按钮。（３）在接下来的对话框中的“系　列”页，分别为系列１、系列２、系列３指定名称“总成本”、“销售　收入”、“利润总额”，然后单击“下一步”按钮。（４）将图表标题　设置为“本量利分析图”，然后单击“完成”按钮。调整图表的大　小及位置到合适为止，如图３所示。　ｅＯ　０００　—２Ｏ。。０　●　田３　这样，本量利分析图就生成了。通过本量利分析图。可以清楚　地看到总成本、销售收入、利润总额与销量的关系，同时可以清楚　地看到保本点销量即利润等于０时的销量。当在图１所示的模型　申通过滚动条调整单价、单位变动成本、固定成本等数据时，本量　利分析图也会根据当前数据自动调整。　四、保护工作裹　’　在利润规划模型中，只能输入单价、单位变动成本、销量、固　定成本等基本数据。所以，可将这些单元格以外的其它单元格保　护起来，方法是：选中“Ｃ３：Ｃ４”、“Ｅ３：Ｅ４”以及滚动条的链接单元　格“Ｄ１２：Ｄ１５”，然后选择菜单“格式，单元格”，出现对话框，在该　对话框的“保护”页，取消“锁定”复选框的选中状态，然后单击　“确定”按钮。最后，选择菜单“工具／保护／保护工作表”对工作　表进行保护。这样，用户只能输入单价、单位变动成本、销量、固定　成本等基本数据，而不能编辑其它单元格。　五、模型的应用　１、各指标的计算及分析。当输入单价、单位变动成本、销量、　长率，先将其它因素的变动率保持为０，然后凋整销量滚动条将利　润变动百分比调整至１５％，由于销量敏感系数为３，可以看到，销　量变动百分比为５％。（２）采取多项措施确保目标利润实现。已知　销量可以增长５％．固定成本将增长５％，要计算单价不变的情况　下，利润增长２０％时单位变动成本的下降，先凋整滚动条使单价　变动率、销量变动率、固定成本变动率分别为０、５％和５％，然后再　凋整单位变动成本滚动条直到利润变动百分比为２０％为止。可以　看到，当单位变动成本下降７％时，目标利润增长１９．７％，当单位变　动成本下降８％时，目标利润增长２１．８％，所以要确保目标利润实　现，单位变动成本下降百分比必须在７％和８％之间，可以使用内　插法来计算确定的百分比，令其为Ｘ，则　ｘ一７％　＝　＿７　：兰！：墨　＝ｌ８％一７％　ｌ　解得ｘ＝７．１４％。　为了简化，在该模型中假定各因素的变动百分比最小单位只　能是１％，实际上，可以设置各滚动条使各因素的变动百分比最小　单位为０．０１％，这样就可以进行比较精确的计算和分析了。　４、敏感分析。（１）使企业由盈利转为亏损的各因素变动率。以　单价为例，利用滚动条将其它因素的变动百分比设置为Ｏ，然后调　整单价滚动条，一直到变动后利润额小于等于０为止。可以看到，　当单价下降２０％时，变动后利润将等于０，所以在其它因素不变的　情况下，单价下降２０％以上将会使得企业由盈利转为亏损。对于　单位变动成本、销量、固定成本等其它因素可作类似分析。（２）各　因紊变动对利润变化的影响程度。利用单价敏感系数、单位变动成　本敏感系数、销量敏感系数和固定成本敏感系数可以分别判断单　价、单位变动成本、销量和固定成本的变化对利润变化的影响程　度。在该模型中，可以看到，单价敏感系数、单位变动成本敏感系　数、销量敏感系数和固定成本敏感系数分别为５、一２、３、一２。其中，　单价敏感系数和销量敏感系数为正数，说明利润与单价和销量正　相关，在其它因素不变的情况下，单独提高单价或销量将使利润增　加，单独降低单价或销量将使利润减少；单位变动成本敏感系数和　固定成本敏感系数为负数，说明利润和单位变动成本、固定成本负　相关，在其它因素不变的情况下，单独提高单位变动成本或固定成　本将使利润减少，单独降低单位变动成本或固定成本将使利润增　加。其中，对于利润变动影响最大的是单价，单价变动ｌ％将使利　润同向变动５％；其次是销量，销量变动ｌ％将使利润同向变动　３％；最后是单位变动成本和固定成本，单位变动成本和固定成本　变动ｌ％，将使利润反向变动２％。　（编辑熊年春）　ｔｌ＃ｔｔｔ￣・综台版２００６ｇｌｌ１２期　麟