● 调用“规划求解”工具。 选中单元格C5,单击Excel“数据”选项卡上的“分析”组的“规划求解”,弹出“规划求解参数”对话框,其中的各参数填制方法如下: 1、由于先选 中 了单元 格C5,“规划求解”工具自动填写了“$C$5”,注意目标值单元格中必须包含有公式,以便于“规划求解”工具内部计算; 2、确保利润和提成工资都是最大,选择此处的“最大值”; 3、提成工资是未知的变量,此处填写“$C$4”; 4、点击“添加”按钮为“遵守约束”框添加计算的限定条件; 5、在弹出的”添加约束“对话框中的“单元格引用”处输入“$C$4”; 6、点击下拉小三角,选择”>=” ; 7、输入0,意为提成工资是正数,至此添加了第一个限制条件; 8、如果要增加第二个条件,点击“添加”按钮,如果要结束添加,点击“确定”按钮关闭”添加约束“对话框; 9、仿照5-8步,设置第二个条件,注意该条件的“约束”框内输入的是“$C$5*0.052”,两个条件添加完成后,自动添加到“规划求解参数”对话框的“遵守约束”框内,如果要修改、删除,可点击其右侧相应的按钮; 10、点击“求解”按钮,弹出“规划求解结果”对话框,如果勾选其中的“返回规划求解参数”可再次弹出上一步的对话框,如不勾选,则回到EXCEL主界面,点击“确定”按钮,关闭“规划求解结果”对话框。 在提成工资中,“规划求解”工具已经为我们找到了答案: (1)全员的效益工资根据利润总额的5.2%的情况下,利润为2471.48万元,提成工资128.52万元。 紧急着,我们来解决问题(2)全员的效益工资根据利润总额的增幅的比率来计提,随着计提工资的变化,利润变化,导致增长率变化,这又导致提成工资变化,这构成了一个循环。 我们同样利用EXCEL的“规划求解”工具,与条件(1)同样的设置条件,但设置第二个条件,注意该条件的“约束”框内输入的是“$C$7*$C$5”,求解结果如下图: 可见,在(2)全员的效益工资根据利润总额的增幅的比率来计提的情况下,利润为2445.40万元,提成工资154.60万元。 四、验证 如果你有推导的公式,也可以在EXCEL中用公式来验证。 公式(1)利润=(收入-其他成本费用)/1.052 公式(2)利润=【(收入-其他成本费用)*上年利润】1/2 问问上图的计算结果看,规划求解得出了与公式计算完全一致的结果。 如前,如手工推导的公式是简单公式,直接用公式来计算;如果手工推导的公式复杂,则建议采用规划求解工具。但要注意运算公式计算时,不要将公式输入C4,否则会导致循环计算的错误。 五、参考资料 《财务精英都是Excel控:CFO手把手教你学管理会计》2018年5月第1版12月第3次印刷 P177
|