本量利分析模型

#邊際貢獻又稱為“邊際利潤”表中，“邊際貢獻”是指銷售收入減去變動成本后的余額,或“貢獻毛益”等。單位邊際貢獻是指單件產(chǎn)品銷售收入減去單位變動成本后的余額。邊際貢獻又稱為“邊際利潤”上表中沒有數(shù)據(jù)的單元格為需要運算的數(shù)據(jù)。步驟5:計算“單位變動成本”。點擊B2單元格，選擇【公式】菜單中的【自動求和】按鈕，在參數(shù)輸入狀態(tài)下用鼠標(biāo)選擇 B3:B5單元格區(qū)域，然后按回車。步驟6:計算“固定成本”。點擊B8單元格，用與步驟2相同的方法將B9:B11單元格區(qū)域求和。步驟7:計算“變動成本”，變動成本=銷量*單位變動成本。點擊B14單元格，輸入公式“=B13*B2”并回車，步驟&計算“總成本”，總成本=固定成本+變動成本。點擊B15單元格，輸入公式“=B8+B14”并回車。步驟9:計算“銷售收入”，銷售收入=銷量*銷售單價。點擊B16單元格，輸入公式“=B13*B1”并回車。步驟10:計算“邊際貢獻”，邊際貢獻=銷售收入=變動成本。點擊B17單元格，輸入公式“=B16-B14”并回車。步驟11:計算“利潤”，利潤=銷售收入-總成本，點擊B18單元格，輸入公式“=B16-B15”并回車。表中“銷量”數(shù)據(jù)B13單元格不用輸入數(shù)據(jù)，計算公式輸入完成后的量本利分析表如誚售單價170里位受動感本旳單件產(chǎn)品人工成本24品材料韓11單件產(chǎn)殆其自制苣更10車位邊際貢就125固定成*誚售單價170里位受動感本旳單件產(chǎn)品人工成本24品材料韓11單件產(chǎn)殆其自制苣更10車位邊際貢就125固定成*107000管卻人FiT竊固迂毎售裁用2100)資產(chǎn)折舊費30003A E12L34EU67IL-LLF-成本收貢晝功戌售麗010700000-107003

任務(wù)3:使用【規(guī)劃求解】工具確定盈虧臨界點銷量步驟1:加載【規(guī)劃求解】工具。由于 Excel2010應(yīng)用程序安裝后默認(rèn)不加載“規(guī)劃求解”工具，所以，如果要使用【規(guī)劃求解】功能時，需要手動加載【規(guī)劃求解】工具，加載“規(guī)劃求解”工具的方法如下。①點擊【文件】菜單下的【選項】命令，彈出【Excel選項】對話框，如圖15-5①點擊【文件】菜單下的【選項】命令，彈出【Excel選項】對話框，如圖15-5所示。圖15-5【Excel選項】對話框在【Excel選項】對話框窗口中選擇【加載項】按鈕，然后點擊【轉(zhuǎn)到】按鈕，彈出【加載宏】對話框，如圖15-6所示。

21雷左 ?X可FITJ］21雷左 ?X可FITJ］航⑶.和特用也克中用計淵工時T鑿捍匸生助自動燦.圖15-6【加載宏】對話框在【加載宏】對話框窗口中，選中【規(guī)劃求解加載項】 ，如圖15-7所示。自鈿化???自鈿化???X冃于tt化和處式豐殆勺工具圖15-7選中【規(guī)劃求解加載項】點擊【確定】按鈕，系統(tǒng)會自動安裝相關(guān)軟件，軟件安裝完成后會在【數(shù)據(jù)】選項卡中出現(xiàn)【規(guī)劃求解】按鈕，如圖 15-8所示?！?.04H15■氣繪山監(jiān)-lMic.D5tftIf―1 ■-.-「m邇喘H全酢盼--￡tT詢■MKMtfidKM■-訐電氐*電巨j-^t^iffiw疔沁' *r ■圖15-8加載【規(guī)劃求解】功能后的【數(shù)據(jù)】數(shù)據(jù)選項卡步驟2:使用【規(guī)劃求解】工具求解出盈虧臨界點銷量值。點擊 B18單元格，選擇【數(shù)據(jù)】選項卡，點擊【規(guī)劃求解】按鈕，彈出【規(guī)劃求解參數(shù)】對話框，如圖 15-9所示。

堪丄機.-U.l堪丄機.-U.lTfflri翼耳卿驚執(zhí)飜鞫跚轉(zhuǎn)艸軸丄詠蝴海卿哦如忙宋解世7 吳宙旭》圖15-9【規(guī)劃求解參數(shù)】設(shè)置對話框,步驟3:在對話框窗口中設(shè)置【規(guī)劃求解參數(shù)】 ，選中【目標(biāo)值】前的單選按鈕，設(shè)置目標(biāo)值為0，點擊【通過更改可變單元格】下面的文本框，然后點擊 B13單元格，其他選項保持默認(rèn)值不變，如圖15-10所示?？?.匚圧鼓冷創(chuàng)養(yǎng)無僚奩塑徉員御I)-空1.匚圧鼓冷創(chuàng)養(yǎng)無僚奩塑徉員御I)-士揮求町向法詛》 羊垛惶um蠹込役mi墾t苑^威劃承餅i般酬輔戦性現(xiàn)腑I京駅於I》 矣甸炬)圖15-10設(shè)置盈虧臨界點銷量時的【規(guī)劃求解參數(shù)】步驟4:點擊【求解】按鈕，彈出【規(guī)劃求解結(jié)果】對話框，如圖 15-11所示。nM.ft根當(dāng)丈射nM.ft根當(dāng)丈射圖5-11【規(guī)劃求解結(jié)果】對話框步驟5:點擊【確定】按鈕，求解結(jié)果如圖 15-11所示。123451234567e銷售塹價單檢孌動成本17045單件產(chǎn)品人工成本Z4申件產(chǎn)品材料費11里牛產(chǎn)品其它制茜裁10里位幻際奇就125?jt5ET1O70X管理人員工資500003000021000A E10資產(chǎn)折舊疔11固定銷售貫用1QL4-017成K收貢

星動成售際潤

嶺變總銷邊利L4-017成K收貢

星動成售際潤

嶺變總銷邊利8563852014552CL4532Oin7nnn7.27GE-09圖15-11圖15-11【規(guī)劃求解】結(jié)果步驟6:點擊B18單元格，選擇【開始】選項卡，在【數(shù)字格式】菜單中選擇【數(shù)字】選項。完成盈虧臨界點銷量的最終效果圖見圖 15-1所示。任務(wù)4:制作量本利分析圖步驟1分別在D1：G1單元格區(qū)域的單元格中輸入字段名“銷量、邊際貢獻、固定成本、利潤”，并設(shè)置居中，如圖15-12所示。C1)E固;TPt本圖15-12輸入圖表字段名步驟2:在D2:D2單元格區(qū)域輸入數(shù)量“0、200、400、…、2000”。選擇D2單元格，輸入“0”，選擇D3單元格，輸入“200”，同時選擇D2、D3兩個單元格，然后用鼠標(biāo)拖曳選中區(qū)域的填充句柄向下填充，直至顯示2000時放開鼠標(biāo)。步驟3:計算“邊際貢獻”。在E2單元格輸入公式“=D2*$B$6”，可以直接輸入，或選擇E2單元格，輸入“=”符號，然后用鼠標(biāo)點擊D2單元格，再輸入“*”符號，再用鼠標(biāo)點擊B6單元格，按F4把B6轉(zhuǎn)換為絕對地址，向下填充。步驟4:輸入“固定成本”。選擇F2單元格，輸入“107000”，或輸入單元格引用公式“=$B$8”，然后向下填充。步驟5:計算“利潤”。選擇G2單元格，輸入公式“=D2*$B$1-D2*$B$2-$B$8”并向下填充，公式的含義是“利潤=數(shù)量*銷售單價-數(shù)量*單件產(chǎn)品人工成本-固定成本”。完成輸入的量本利數(shù)據(jù)如圖15-13所示。BCDE7C1170雀魚回芒曲本和潤2卅0auuLU70UU-IDTDO.3里件產(chǎn)朋人工眩本2430025000.00L0700002DOO4單件產(chǎn)昌村科費1140050000.00L07000-5TD0D51C600rtooo.ooL070D0-32D00B125aoo10C000.00107CD0-7D0D7.000123000.001070001SD0D舊定或車10?D(lD1^00150000.0010700043DOD?語理人員工資1400175000.no107000■'iSDOO10資產(chǎn)折日費30000laoo200000.DOL070D01113宜體吿矛用21DOO1900225000L66107000118D(?D122aoo250000.0010700(1圖15-13量本利數(shù)據(jù)步驟6:選擇D1:G12單元格區(qū)域.步驟7:選擇【插入】選項卡，點擊【散點圖】按鈕，彈出圖表類型選擇菜單， 如圖15-14所示。

?A口耳布扇忖 寶聞咀風(fēng)y|S3看w* 比冊■>?L>O豈.-E!具I三走10107000：6i>00:D00021000帶半応出競t點:IB1氐曲簡撫瓦*.u&D0900ICiJO12001100-z■Uuu<2C?d-?t?dlacxjd■^CMJCleeooo930001LSOOO143t?o356L>O豈.-E!具I三走10107000：6i>00:D00021000帶半応出競t點:IB1氐曲簡撫瓦*.u&D0900ICiJO12001100-z■Uuu<2C?d-?t?dlacxjd■^CMJCleeooo930001LSOOO143t?o35638520liDKO1S55201:7'：uj：k(M即界右耳皐X曬.fFHu蘭衆(zhòng)據(jù)萄迺故’則可圖15-14選擇【散點圖】類型步驟8:在菜單中選擇【帶平滑線的散點圖】命令，彈出量本利分析圖，如圖15-15所示。邊麗貢I? 固證哺克圖15-15量本利分析圖步驟9:用鼠標(biāo)右擊圖例，在彈出的菜單中選擇【設(shè)置圖例格式】命令，彈出【設(shè)置圖例格式】對話框，如圖15-16所示。

圖15-16【設(shè)置圖例格式】對話框步驟10:在【設(shè)置圖例格式】對話框中選擇【圖例位置】為【底部】鈕，設(shè)置效果如圖15-17所示。，然后點擊【關(guān)閉】按300000.00ZSCDOO.IXl200000DOI'smrndoIODO圖15-16【設(shè)置圖例格式】對話框步驟10:在【設(shè)置圖例格式】對話框中選擇【圖例位置】為【底部】鈕，設(shè)置效果如圖15-17所示。，然后點擊【關(guān)閉】按300000.00ZSCDOO.IXl200000DOI'smrndoIODOCC.DO5QOOO.DO□DO(SOOOOOC)(lGDOOO.tKj(130000.M)on-oXOQO 1500 20002SJC—— □ 利閏-圖15-17設(shè)置【圖例位置】在底部步驟11:在圖表中添加“盈虧臨界點垂直參考線”準(zhǔn)備垂直參考線數(shù)據(jù)。選擇A22單元格，輸入“盈虧臨界點垂直參考線”在A23:A26單元格區(qū)域中輸入X軸坐標(biāo)標(biāo)注點“856”，“856”為盈虧臨界點銷量。在B23:B26單元格區(qū)域中輸入Y軸坐標(biāo)標(biāo)點“200000（任意的）、107000（盈虧臨界點邊際貢獻）、0（與X軸的交叉點）、-100000（任意的）”，如圖15-18所示。

222320000024107000026B5E-100000圖15-18垂直參考線引用數(shù)據(jù)選中繪圖區(qū)，選擇【圖表工具】下的【設(shè)計】選項卡，點擊【選擇數(shù)據(jù)】按鈕，彈出【選擇數(shù)據(jù)源】對話框，如圖 15-19所示。圖15-19添加圖例項在彈出的【選擇數(shù)據(jù)源】對話框中點擊【圖例項（系列）】下的【添加】按鈕，彈出【編輯數(shù)據(jù)系列】對話框，如圖 15-20所示。1I轆屎列值②:—逸捶區(qū)域施揉區(qū)威T社系到值①;圖15-20編輯數(shù)據(jù)系列設(shè)置垂直參考線參數(shù)。點擊【系列名稱】下的文本框，然后點擊 A22單元格；點擊【X軸系列值】下面的文本框，然后選擇 A23:A26單元格區(qū)域；點擊【Y軸系列值】下面的文本框，刪除其中的內(nèi)容，然后選擇 B23:B26單元格區(qū)域。設(shè)置結(jié)果如圖 15-21所示。

；牡至列值迫：-本里利廿析序臨::-$帖羽T軸系列偵腫；=擊里利^tf'tBt?3-m?fi|「處■誥虧勵弄點垂宜參肖第圖15-21垂直參考線參數(shù)設(shè)置結(jié)果「處■誥虧勵弄點垂宜參肖第點擊【確定】按鈕，返回到【選擇數(shù)據(jù)源】對話框窗口，這時【圖例項】中多了一項圖15-22添加垂直參考線后的數(shù)據(jù)源【盈虧臨界點垂直參考線】，如圖15-22圖15-22添加垂直參考線后的數(shù)據(jù)源點擊【確定】按鈕，圖形中插入了一條垂直參考線，如圖 15-23所示。圖15-23插入垂直參考線后的量本利分析圖步驟12:調(diào)整垂直參考線線型。①在垂直參考線的任意一個標(biāo)記點位置點擊左鍵，選中垂直參考線，如圖 15-24所示。一邊陌貢黙一邊陌貢黙—固是應(yīng)鬲 刊伺一盈虧臨界冃爭頁奪荷毀圖15-24選擇垂直參考線②在選中的垂直參考線標(biāo)注點上點擊右鍵，在彈出的菜單中選擇【設(shè)置數(shù)據(jù)系列格式】命令,②在選中的垂直參考線標(biāo)注點上點擊右鍵，在彈出的菜單中選擇【設(shè)置數(shù)據(jù)系列格式】命令,彈出【設(shè)置數(shù)據(jù)系列格式】對話框，如圖 15-25所示。圖15-25設(shè)置線型【寬度】③在對話框中選擇【線型】選項，把線型【線寬】調(diào)為“1磅”然后點擊