ECEL在會(huì)計(jì)和財(cái)務(wù)管理中運(yùn)用第11章

第11章經(jīng)典實(shí)例：銷售決策分析●11.1要點(diǎn)分析●11.2經(jīng)過(guò)建立決策模型進(jìn)展?fàn)I銷決策●11.2.1產(chǎn)品定價(jià)決策問(wèn)題描畫與處理的根本思緒●11.2.2構(gòu)建銷售利潤(rùn)最大化的產(chǎn)品定價(jià)決策模型●11.2.3產(chǎn)品定價(jià)決策模型中有關(guān)變量的圖表分析●11.3數(shù)據(jù)表在凈利潤(rùn)敏感度分析中的運(yùn)用●11.3.1產(chǎn)品銷售凈利潤(rùn)值敏感度分析●11.3.2價(jià)風(fēng)格整引起的利潤(rùn)變化●11.3.3價(jià)錢與數(shù)量同時(shí)變動(dòng)引起的利潤(rùn)變化●11.4單變量求解在銷售利潤(rùn)目確實(shí)定中的運(yùn)用●11.4.1確定目的利潤(rùn)對(duì)應(yīng)的銷售收入●11.4.2確定新產(chǎn)品的保本銷售量●11.5運(yùn)用“規(guī)劃求解〞工具分析營(yíng)銷決策●11.5.1規(guī)劃問(wèn)題的特點(diǎn)以及“規(guī)劃求解〞工具的組成●11.5.2利用“規(guī)劃求解〞工具處理規(guī)劃求解問(wèn)題的流程●11.5.3利用“規(guī)劃求解〞工具確定商品運(yùn)輸方案●11.5.4利用“規(guī)劃求解〞工具分析設(shè)置快捷酒店網(wǎng)點(diǎn)規(guī)劃●11.2經(jīng)過(guò)建立決策模型進(jìn)展?fàn)I銷決策

營(yíng)銷決策是指對(duì)有關(guān)產(chǎn)品市場(chǎng)運(yùn)營(yíng)和銷售活動(dòng)的目的、方針、戰(zhàn)略等艱苦問(wèn)題進(jìn)展選擇和決斷的過(guò)程。營(yíng)銷決策是企業(yè)市場(chǎng)營(yíng)銷中的中心問(wèn)題，它必需建立在充分的市場(chǎng)調(diào)查和市場(chǎng)預(yù)測(cè)的根底之上?！?1.2.1產(chǎn)品定價(jià)決策問(wèn)題描畫與處理的根本思緒企業(yè)市場(chǎng)營(yíng)銷中的一個(gè)重要決策問(wèn)題就是產(chǎn)品定價(jià)。而銷售利潤(rùn)那么是企業(yè)管理追求的一個(gè)重要目的，但是銷售利潤(rùn)的大小與產(chǎn)品的定價(jià)具有重要關(guān)聯(lián)關(guān)系。假設(shè)產(chǎn)品價(jià)錢定得過(guò)高，那么單位產(chǎn)品的銷售利潤(rùn)相應(yīng)的也會(huì)添加，但是總的銷售量能夠會(huì)減少，從而會(huì)影響總的產(chǎn)品銷售利潤(rùn)；反之產(chǎn)品價(jià)錢定的過(guò)低，雖然銷售數(shù)量能夠會(huì)大幅的增長(zhǎng)，但是由于單位產(chǎn)品的銷售利潤(rùn)不在，也會(huì)影響到總的產(chǎn)品銷售利潤(rùn)。下面是一個(gè)產(chǎn)品定價(jià)決策的問(wèn)題描畫，并給出相應(yīng)的問(wèn)題處理的根本思緒。如表11-1所示，為某公司根據(jù)市場(chǎng)分析，對(duì)其消費(fèi)的某產(chǎn)品在不同價(jià)錢程度下的預(yù)測(cè)銷售量。假設(shè)某公司消費(fèi)該產(chǎn)品的全年固定本錢為每年150萬(wàn)無(wú)，全年的消費(fèi)能為55萬(wàn)

套，每套產(chǎn)品的變動(dòng)成為20元。請(qǐng)問(wèn)：某公司應(yīng)該如何對(duì)該產(chǎn)品定價(jià)？●11.1要點(diǎn)分析

問(wèn)題分析：這里的產(chǎn)品定價(jià)，應(yīng)該就是以銷售利潤(rùn)最大化為目的，因此需求構(gòu)造銷售利潤(rùn)的計(jì)算公式。然后根據(jù)相關(guān)變量之間的關(guān)系，這里的銷售利潤(rùn)計(jì)算的公式如下。L=S-C=PQ-(F＋V×Q)＝(P-V)×Q－F其中：L為全年銷售利潤(rùn)。S為全年銷售額。C為全年銷售本錢。P為銷售價(jià)錢。F為全年固定本錢。V為單位變動(dòng)本錢。Q為預(yù)測(cè)的銷售量。經(jīng)過(guò)上面公式，就可以計(jì)算出來(lái)不同價(jià)錢程度下，對(duì)應(yīng)不同預(yù)測(cè)銷售量時(shí)的銷售利潤(rùn)；然后可以根據(jù)銷售利潤(rùn)最大化的原那么，即可將其對(duì)應(yīng)價(jià)錢作為最正確的決策價(jià)錢?！?1.2.2構(gòu)建銷售利潤(rùn)最大化的產(chǎn)品定價(jià)決策模型下面就可以得用Excel建立模型，來(lái)進(jìn)展以上的銷售利潤(rùn)輔助計(jì)算，操作步驟如下。步驟1根據(jù)知數(shù)據(jù)以及銷售利潤(rùn)計(jì)算公式，制造如圖11-1所示的表格。步驟2選取C9單元格，輸入公式“＝C4〞，獲取第一個(gè)價(jià)錢程度，然后向右拖動(dòng)填充柄，不斷復(fù)制公式到I9單元格獲取其他的價(jià)錢程度，如圖11-2所示。價(jià)格（元）60553530254025預(yù)測(cè)銷量（萬(wàn)套）15103640555056表11-1不同價(jià)錢程度下的預(yù)測(cè)銷售量

步驟3選取C10單元格，輸入公式“＝C5〞，獲取第一個(gè)預(yù)測(cè)銷售量，然后向右拖動(dòng)填充柄，不斷復(fù)制公式到I10單元格，獲取其他預(yù)測(cè)銷售量，如圖11-3所示。步驟4選取C11單元格，輸入公式“=C9*C10〞，獲取第一個(gè)不同銷售量銷售收入，然后向右拖動(dòng)填充柄，不斷復(fù)制公式到I11單元格，獲取其他不同銷售量銷售收入，如圖11-4所示。圖11-1銷售利潤(rùn)最大化的產(chǎn)品定價(jià)所用表格圖11-2獲取其他價(jià)錢程度圖11-3計(jì)算出其他預(yù)測(cè)銷售量圖11-4計(jì)算出不同銷售量銷售收入

步驟5選取C12單元格，輸入公式“=C5*$I$6〞，獲取第一個(gè)總變動(dòng)本錢，然后向右拖動(dòng)填充柄，不斷復(fù)制公式到I12單元格，獲取其他總變動(dòng)本錢，如圖11-5所示。步驟6選取C13單元格，輸入公式“=$D$6〞，計(jì)算出固定本錢，如圖11-6所示。圖11-5計(jì)算出其他總變動(dòng)本錢圖11-6計(jì)算出固定本錢步驟7選取C14單元格，輸入公式“=C12＋$C$13〞，獲取第一個(gè)總本錢，然后向右拖動(dòng)填充柄，不斷復(fù)制公式到I14單元格，獲取其他的總本錢，如圖11-7所示。步驟8選取C15單元格，輸入公式“=C11-C14〞，計(jì)算出第一個(gè)價(jià)錢程度下的銷售利潤(rùn)然后向右拖動(dòng)填充柄，不斷復(fù)制公式到I15單元格，計(jì)算出其他價(jià)錢程度下的銷售利潤(rùn)。經(jīng)過(guò)以上的操作，計(jì)算出各種不同價(jià)錢程度下的銷售利潤(rùn)，效果清秀圖11-8所示。步驟9選取C17單元格，輸入公式“="本產(chǎn)品售價(jià)應(yīng)定為"&INDEX(C10:I10,MATCH

(MAX(C15:I15),C15:I15,0))&"元,此時(shí)對(duì)應(yīng)銷售量為"&INDEX(C4:I4,MATCH(MAX(C15:I15),

C15:I15,0))&"萬(wàn)套，預(yù)測(cè)銷售利潤(rùn)為"&MAX(C15:I15)&"萬(wàn)元，得出銷售利潤(rùn)最大化的產(chǎn)品定價(jià)決策模型的分析結(jié)果，如圖11-9所示。圖11-7計(jì)算出其他的總本錢圖11-8計(jì)算出不同價(jià)錢程度下的銷售利潤(rùn)圖11-9銷售利潤(rùn)最大化的產(chǎn)品定價(jià)決策模型的分析結(jié)果●11.2.3產(chǎn)品定價(jià)決策模型中有關(guān)變量的圖表分析下面就來(lái)根據(jù)產(chǎn)品定價(jià)決策模型制造數(shù)據(jù)圖表，操作步驟如下。步驟1選取B14:I14單元格區(qū)域，按下Ctrl鍵，依次選取B12:I12單元格區(qū)域、B14:I14單元格區(qū)域和B15:I15單元格區(qū)域。步驟2在“插入〞選項(xiàng)卡下的“圖表〞組中單擊“折線圖〞按鈕，在展開(kāi)的下拉列表中單擊“帶數(shù)據(jù)標(biāo)志的折線圖〞圖標(biāo)，如圖11-10所示。步驟3如圖11-11所示，經(jīng)過(guò)上述操作，制造出的帶數(shù)據(jù)標(biāo)志的折線圖的初始效果。圖11-11帶數(shù)據(jù)標(biāo)志的折線圖的初始效果步驟4適當(dāng)調(diào)整圖表放置位置，將其挪動(dòng)到適宜位置。步驟5右擊圖表程度坐標(biāo)軸，從快捷菜單中選擇“刪除〞命令，如圖11-12所示，刪除縱坐標(biāo)軸。步驟6右擊圖例，從快捷菜單中選擇“設(shè)置圖例格式〞命令，如圖11-13所示。步驟7彈出“設(shè)置圖例格式〞對(duì)話框，在“圖例選項(xiàng)〞選項(xiàng)卡的右側(cè)窗格中單擊圖例位置“底部〞單項(xiàng)選擇按鈕，在“邊框顏色〞選項(xiàng)卡的右側(cè)窗格中單擊“實(shí)線〞單項(xiàng)選擇按鈕，并選擇顏色“黑色〞，如圖11-14所示。步驟8在圖表中，右擊“價(jià)錢程度〔元〕〞折線條，從快捷菜單中選擇“添加數(shù)據(jù)標(biāo)簽〞命令，如圖11-15所示，為數(shù)據(jù)添加標(biāo)簽。圖11-10單擊“帶數(shù)據(jù)標(biāo)志的折線圖〞圖標(biāo)圖11-13選擇“設(shè)置圖例格式〞命令圖11-12選擇“刪除〞命令圖11-14“設(shè)置圖例格式〞對(duì)話框圖11-15選擇“添加數(shù)據(jù)標(biāo)簽〞命令步驟9右擊添加的數(shù)據(jù)標(biāo)簽，從快捷菜單中選擇“設(shè)置數(shù)據(jù)系列格式〞命令，如圖11-16所示。步驟10彈出“設(shè)置數(shù)據(jù)系列格式〞對(duì)話框，設(shè)置“系列選項(xiàng)〞系列繪制在“次坐標(biāo)軸〞，如圖11-17所示，從面建立雙軸圖表。圖11-17設(shè)置數(shù)據(jù)系列格式圖11-16選擇“設(shè)置數(shù)據(jù)系列格式〞命令步驟11右擊圖表的繪圖區(qū)，從快捷菜單中選擇“設(shè)置繪圖區(qū)格式〞命令，如圖11-18所示。步驟12彈出“設(shè)置繪圖區(qū)格式〞對(duì)話框，設(shè)置填充色為“純色填充〞，填充顏色

為“淺橙色〞，如圖11-19所示。圖11-19設(shè)置繪圖區(qū)格式圖11-18選擇“設(shè)置繪圖區(qū)格式〞命令步驟13添加圖表標(biāo)題“銷售利潤(rùn)最大情況下產(chǎn)品定價(jià)決策的圖表分析〞，并進(jìn)展一定的文字格式效果設(shè)置。

步驟14適當(dāng)調(diào)整圖表的大小，然后設(shè)置A1:S1,A18:S18,A2:A18,S2:S18單元格區(qū)域的填充色、調(diào)整寬度，做出模型周圍的邊框效果，構(gòu)造出基于Excel的銷售利潤(rùn)最大化的產(chǎn)品定價(jià)決策模型，如圖11-20所示。圖11-20銷售利潤(rùn)最大化的產(chǎn)品定價(jià)決策模型最終制造效果●11.3數(shù)據(jù)表在凈利潤(rùn)敏感度分析中的運(yùn)用

敏感度分析又稱“What-if分析〞，其用途是測(cè)定當(dāng)一個(gè)參數(shù)發(fā)生變化時(shí)，由該參數(shù)所帶的中間變量是如何變化，以及由中間變量引起的最終結(jié)果會(huì)發(fā)生什么樣的變化?！?1.3.1產(chǎn)品銷售凈利潤(rùn)值敏感度分析在Sheet2任務(wù)表中新建如圖11-21所示的表格，左邊部分給出了某公司本月產(chǎn)品的簡(jiǎn)易損益表，而右邊部分給出了從根本數(shù)據(jù)輸入開(kāi)場(chǎng)，到最終的凈利潤(rùn)輸出為止，中間的數(shù)據(jù)計(jì)算規(guī)那么。從圖中可以看出，本月該產(chǎn)品的凈利潤(rùn)約為840萬(wàn)元。如今假設(shè)一下，下個(gè)月想實(shí)現(xiàn)凈利潤(rùn)到達(dá)870萬(wàn)元，請(qǐng)?jiān)谥荒芨膭?dòng)銷售價(jià)錢和銷售數(shù)量的情況下，對(duì)凈利潤(rùn)值的敏感度進(jìn)展分析。經(jīng)過(guò)改動(dòng)銷售價(jià)錢和銷售數(shù)量，可以進(jìn)展利潤(rùn)值敏感度的分析，引起利潤(rùn)值的變化，更加方便的選擇到達(dá)即定值的數(shù)據(jù)。下面在Excel中利用“模擬運(yùn)算表〞工具分析處理該問(wèn)題。圖11-21某公司某產(chǎn)品本月的簡(jiǎn)易損益表●11.3.2價(jià)風(fēng)格整引起的利潤(rùn)變化下面就以在上述的產(chǎn)品損益表中，根據(jù)銷售價(jià)錢的變化，來(lái)模擬凈利潤(rùn)的變化為

例，闡明利用“模擬運(yùn)算表〞工具進(jìn)展單要素敏感度分析的操作。詳細(xì)操作步驟如下。步驟1將Sheet3任務(wù)表重命名為“單要素敏感度分析〞，然后將某公司某產(chǎn)品本月的簡(jiǎn)易損益表復(fù)制到該任務(wù)表中，并在其右側(cè)建立單變量數(shù)據(jù)表的初始構(gòu)造，如圖11-22所示。步驟2在D5單元格和D6單元格分別輸入75和76，然后選取D5:D6單元格區(qū)域，向下拖動(dòng)填充柄不斷到D20單元格，在E4單元格中輸入“＝B21〞，結(jié)果如圖11-23所示。圖11-23利用公式和復(fù)制功能填入數(shù)據(jù)圖11-22單變量數(shù)據(jù)表的初始構(gòu)造步驟3給要進(jìn)展數(shù)據(jù)敏感度分析的數(shù)據(jù)表范圍設(shè)置填充色。首先選取D4:E20單元格區(qū)域，在“數(shù)據(jù)〞選項(xiàng)卡下的“數(shù)據(jù)工具〞組中單擊“模擬分析〞按鈕，從彈出的菜單中選擇“模擬運(yùn)算表〞命令，如圖11-24所示。步驟4本例是在列上模擬銷售價(jià)錢的變化，銷售價(jià)錢的數(shù)據(jù)在B5單元格中存儲(chǔ)，所以在彈出的“模擬運(yùn)算表〞對(duì)話框中在“輸入援用列的單元格〞文本框中輸入“$B$5〞，如圖11-25所示。步驟5單擊“確定〞按鈕，單變量數(shù)據(jù)表制造完成，效果如圖11-26所示。在每一個(gè)價(jià)錢數(shù)字的后面，曾經(jīng)計(jì)算出在其他變量都堅(jiān)持不變的情況下，對(duì)應(yīng)凈利潤(rùn)的金額。從圖中可以看出，在堅(jiān)持其他變量都不變的情況下，要想實(shí)現(xiàn)凈利潤(rùn)到達(dá)870萬(wàn)，只需將價(jià)風(fēng)格整到81元，也就是上漲1元即可。此時(shí)對(duì)應(yīng)的凈利潤(rùn)為8761406元。

圖11-24選擇“模擬運(yùn)算表〞命令圖11-25“模擬運(yùn)算表〞對(duì)話框圖11-26單要素利潤(rùn)敏感度分析的結(jié)果●11.3.3價(jià)錢與數(shù)量同時(shí)變動(dòng)引起的利潤(rùn)變化進(jìn)展單變量敏感度分析，只是分析改動(dòng)一個(gè)輸入變量對(duì)公式計(jì)算結(jié)果的影響。假設(shè)想要分析兩個(gè)變量對(duì)公式計(jì)算的影響，就需求進(jìn)展雙變量敏感度分析，這可以經(jīng)過(guò)運(yùn)用雙變量數(shù)據(jù)來(lái)實(shí)現(xiàn)。

圖11-27創(chuàng)建雙變量數(shù)據(jù)表的框架例如，前面經(jīng)過(guò)改動(dòng)銷售價(jià)錢一個(gè)參數(shù)，分析單變量的利潤(rùn)敏感度，其結(jié)果不一定完全準(zhǔn)確。由于當(dāng)價(jià)錢變化時(shí)也能夠會(huì)引起銷售數(shù)量也發(fā)生一定的變化，所以有必要一同去思索。下面同時(shí)思索價(jià)錢和數(shù)量?jī)蓚€(gè)要素，進(jìn)展雙變量利潤(rùn)敏感度分析，詳細(xì)操作步驟如下。步驟1插入一個(gè)任務(wù)表，并重命為“雙要素敏感度分析〞。步驟2將某公司某產(chǎn)品本月的簡(jiǎn)易損益表復(fù)制到“雙要素敏感度分析〞任務(wù)表中，并在其右側(cè)建立雙變量數(shù)據(jù)表的初始構(gòu)造，接著在F2:L2單元格區(qū)域中輸入銷售數(shù)量，并在E2單元格輸入“＝B21〞，結(jié)果如圖11-27所示。步驟3選取E2:L18單元格區(qū)域，在“數(shù)據(jù)〞選項(xiàng)卡下的“數(shù)據(jù)工具〞組中單擊“模擬分析〞按鈕，在展開(kāi)的下拉列表中選擇“模擬運(yùn)算表〞命令，如圖11-28所示。

圖11-28選擇“模擬運(yùn)算表〞命令步驟4彈出“模擬運(yùn)算表〞對(duì)話框，在“輸入援用行的單元格〞文本框中輸

入“$B$6〞，在“輸入援用列的單元格〞文本框中輸入“$B$5〞，如圖11-29所示。步驟5單擊“確定〞按鈕，結(jié)果如圖11-30所示。圖11-29“模擬運(yùn)算表〞對(duì)話框圖11-30按照價(jià)錢和數(shù)量得到的雙變量數(shù)據(jù)表結(jié)果

圖11-31單擊“介于〞按鈕步驟6為了將結(jié)果為870萬(wàn)元的數(shù)字用特殊效果顯示，可以為模擬結(jié)果設(shè)置條件格式，選取E3:L18單元格區(qū)域，在“開(kāi)場(chǎng)〞選項(xiàng)下的“款式〞組中單擊“條件格式〞按鈕，在展開(kāi)的列表中選擇“突出顯示單元格規(guī)那么〞選項(xiàng)，然后單擊“介于〞按鈕，如圖11-31所示。步驟7彈出“介于〞對(duì)話框，設(shè)置條件格式，如圖11-32所示，最后單擊“確定〞按鈕。圖11-32“介于〞對(duì)話框步驟8前往任務(wù)表，在圖11-33中可以看到，凈利潤(rùn)大于870萬(wàn)元的“價(jià)錢/數(shù)量〞組合，都分布到整個(gè)區(qū)域?qū)蔷€的右下部分。根據(jù)價(jià)錢與數(shù)量之間的關(guān)系，其中只需一部分的組合具有較大的能夠性。例如F15、H9、K3等單元格，都是曾經(jīng)到達(dá)了870萬(wàn)元的凈利潤(rùn)，并且也是比較可行的幾種方案。以F15單元格為例，意思就是“價(jià)錢假設(shè)漲到87元，銷售只需到達(dá)40萬(wàn)個(gè)，就可以實(shí)現(xiàn)凈利潤(rùn)870萬(wàn)元〞。而H9單元格那么意味著“價(jià)錢假設(shè)堅(jiān)持81元不動(dòng)，銷售量只需到達(dá)50萬(wàn)個(gè)，也可以實(shí)現(xiàn)凈利潤(rùn)到達(dá)870萬(wàn)元〞。圖11-33對(duì)雙變量敏感度分析的結(jié)果進(jìn)展條件格式化設(shè)置●11.4單變量求解在銷售利潤(rùn)目確實(shí)定中的運(yùn)用

在“營(yíng)銷決策.xlsx〞任務(wù)簿中新插入一張任務(wù)表，在任務(wù)表中制造某公司編制的本月?lián)p益簡(jiǎn)表，如圖11-35所示。在右邊部分是各變量之間的相互關(guān)系及計(jì)算方法，左邊部分“本月數(shù)字〞一列中帶有填充色的單元格是利用D列中的計(jì)算公式進(jìn)展計(jì)算得出的結(jié)果。問(wèn)題分析：從如圖11-34可以看出，本月凈利潤(rùn)為78萬(wàn)元左右。假設(shè)公司確定下個(gè)月凈利潤(rùn)要到達(dá)80萬(wàn)元，其他原始數(shù)據(jù)和計(jì)算模型中各個(gè)參數(shù)都堅(jiān)持不變。那么，銷售收入應(yīng)該增長(zhǎng)到多少，才干實(shí)現(xiàn)凈利潤(rùn)到達(dá)80萬(wàn)元的目的呢？圖11-34某公司編制的本月?lián)p益簡(jiǎn)表下面將引見(jiàn)如何運(yùn)用“單變量求解〞工具來(lái)處理上述問(wèn)題?！?1.4.1確定目的利潤(rùn)對(duì)應(yīng)的銷售收入下面采用單變量求解的方法確定目的利潤(rùn)對(duì)應(yīng)的銷售收入，詳細(xì)操作步驟如下。步驟1選定要確定目的利潤(rùn)對(duì)應(yīng)的銷售收入的單元格，也就是凈利潤(rùn)數(shù)據(jù)所在的B16單元格。步驟2在“數(shù)據(jù)〞選項(xiàng)卡下的“數(shù)據(jù)工具〞組中單擊“模擬分析〞按鈕，在展開(kāi)的下拉列表中選擇“單變量求解〞命令，如圖11-35所示。步驟3彈出“單變量求解〞對(duì)話框，在“目的值〞文本框中輸入“800000〞，

在“可變單元格〞文本框中，輸入這里需求確定的可變量—銷售收入所在的“$B$2〞單元格，如圖11-36所示。步驟4單擊“確定〞按鈕，彈出如圖11-37所示的“單變量求解形狀〞對(duì)話框，闡明曾經(jīng)找到解，并且與目的值完全一致。圖11-35選擇“單變量求解〞命令圖11-36“單變量求解〞對(duì)話框步驟5單擊“確定〞按鈕，得到單變量求解的結(jié)果，如圖11-38所示，要實(shí)現(xiàn)凈利潤(rùn)增長(zhǎng)到800000.00元，銷售收入需求添加到11217548.57元。圖11-37“單變量求解形狀〞對(duì)話框圖11-38單變量求解的結(jié)果

●11.4.2確定新產(chǎn)品的保本銷售量新插入一張任務(wù)表，然后在任務(wù)表中制造“新開(kāi)發(fā)產(chǎn)品的保本點(diǎn)計(jì)算〞表格，如圖11-39所示。其中，B4:C14單元格區(qū)域中是某公司為一種新產(chǎn)品進(jìn)展銷售利潤(rùn)測(cè)算，按照目前設(shè)定的數(shù)據(jù)，計(jì)算出來(lái)的銷售利潤(rùn)為負(fù)數(shù)。下面利用“單變量求解〞工具確定新產(chǎn)品保本銷售。假設(shè)如今其他數(shù)據(jù)都不能改動(dòng)，而獨(dú)一可以做出改良的是設(shè)法增大產(chǎn)品的銷售數(shù)量。請(qǐng)問(wèn)：銷售數(shù)量必需增長(zhǎng)到多少時(shí)，才干實(shí)現(xiàn)本產(chǎn)品的銷售可以“保本〞？下面就來(lái)引見(jiàn)如何利用單變量求解處理這個(gè)問(wèn)題，詳細(xì)操作步驟如下。圖11-39“新開(kāi)發(fā)產(chǎn)品的保本點(diǎn)計(jì)算〞表格步驟1將B4:C14單元格內(nèi)容復(fù)制到B17:C27單元格區(qū)域。步驟2選定C27單元格，也就是銷售利潤(rùn)所在的單元格，在“數(shù)據(jù)〞選項(xiàng)卡下

的“數(shù)據(jù)工具〞組中單擊“模擬分析〞按鈕，在展開(kāi)的下拉列表中選擇“單變量求解〞命令，如圖11-40所示。步驟3彈出“單變量求解〞對(duì)話框，在“目的值〞文本框中輸入數(shù)字“0〞，

在“可變單元格〞文本框中，輸入這里需求確定的可變量—銷售收入所在的“$C$19〞單元格，如圖11-41所示。圖11-40選擇“單變量求解〞命令圖11-41“單變量求解〞對(duì)話框步驟4單擊“確定〞按鈕，彈出如圖11-42所示的“單變量求解形狀〞對(duì)話框，闡明曾經(jīng)找到一個(gè)解，并且與目的值是完全一樣的。步驟5單擊“確定〞按鈕，得到單變量求解的結(jié)果，如圖11-43所示，可以看

出“保本點(diǎn)〞銷售為90000，也就是說(shuō)假設(shè)其他各量都不變，必需銷售90000個(gè)以上的本產(chǎn)品，才干實(shí)現(xiàn)贏利。圖11-42“單變量求解形狀〞對(duì)話框圖11-43利用單變量求解確定保本銷售量●11.5運(yùn)用“規(guī)劃求解〞工具分析營(yíng)銷決策

在實(shí)踐的銷售管理中，要完成一工程標(biāo)義務(wù)的時(shí)候，就會(huì)經(jīng)常會(huì)存在著多個(gè)要素，這些要素都具有一定的約束條件。下面將引見(jiàn)如何在約束條件下進(jìn)展最優(yōu)確定?！?1.5.1規(guī)劃問(wèn)題的特點(diǎn)以及“規(guī)劃求解〞工具的組成對(duì)于規(guī)劃求解問(wèn)題的處理，在Excel中專門提供了“規(guī)劃求解〞加載宏工具。1．規(guī)劃問(wèn)題的特點(diǎn)在方案管理當(dāng)中，往往會(huì)遇到各種各樣的規(guī)劃問(wèn)題，例如：人力資源的調(diào)度、產(chǎn)品消費(fèi)的安排、運(yùn)輸線路的規(guī)劃、消費(fèi)資料的搭配、采購(gòu)批次確實(shí)定等。這類問(wèn)題有一個(gè)共同的要求，那就是：假設(shè)合理的利用各種約束資源從而到達(dá)最正確的經(jīng)濟(jì)效益，也就是到達(dá)產(chǎn)量最高、利潤(rùn)最大、本錢最低、費(fèi)用最省等目的。普通來(lái)說(shuō)，這類規(guī)劃問(wèn)題都具有如下三個(gè)特點(diǎn)?！?〕所求的問(wèn)題都有一個(gè)單一的目的，如求消費(fèi)的最低本錢、求運(yùn)輸?shù)淖钫_的道路、求產(chǎn)品的最大盈利、求產(chǎn)品周期的最短時(shí)間以及求其他目的函數(shù)的最優(yōu)值等?！?〕總是有明確的不等式約束的條件。比如庫(kù)存不能低于一定的數(shù)量，否那么就會(huì)呵斥原料短缺或產(chǎn)品缺貨的情況；消費(fèi)的產(chǎn)品不能超越一定的額度，否那么會(huì)呵斥商品積壓等?！?〕問(wèn)題都有直接或間接影響約束條件的一組輸入值。2．Excel“規(guī)劃求解〞工具的組成規(guī)劃求解就是在滿足約束條件的前提下，調(diào)整決策變量，實(shí)現(xiàn)目的函數(shù)最優(yōu)。因此，可以說(shuō)規(guī)劃求解工具包括決策變量、目的函數(shù)和約束條件三部分?！?〕決策變量：是指在實(shí)踐的問(wèn)題當(dāng)有有一些待處理的未知要素，一個(gè)規(guī)劃問(wèn)題中能夠會(huì)有一個(gè)決策變量，也有能夠會(huì)有多個(gè)決策變量。一組決策變量代表一個(gè)規(guī)劃求解的方案。在Excel的規(guī)劃求解模型中，決策變量通常利用可變單元格進(jìn)展表示?！?〕目的函數(shù)：用來(lái)表示規(guī)劃求解要到達(dá)的最終目的，如求最短途徑、最大利潤(rùn)、最小本錢、最正確產(chǎn)品組合等。在規(guī)劃模型中，目的函數(shù)應(yīng)該是決策變量的函數(shù)。也就是說(shuō)，在Excel中目的函數(shù)與可變單元格有著直接或間接的聯(lián)絡(luò)，它可以進(jìn)線性函數(shù)〔對(duì)應(yīng)規(guī)劃問(wèn)題稱為“線性規(guī)劃〞〕，也可以是非線性函數(shù)〔對(duì)應(yīng)規(guī)劃問(wèn)題稱為“非線性規(guī)劃〞〕?！?〕約束條件：是指實(shí)現(xiàn)目的的限制條件，規(guī)劃求解能否有解，與約束條件有著親密的關(guān)系，它對(duì)可變單元格中的值起著直接限制的作用。約束的條件可以是等式，也可以不是等式?！?1.5.2利用“規(guī)劃求解〞工具處理規(guī)劃求解問(wèn)題的流程某電腦銷售公司主要銷售“戴爾〞和“華碩〞兩種筆記本。因擔(dān)憂進(jìn)貨積壓，公司規(guī)定“戴爾〞筆記本一次性最多進(jìn)貨200臺(tái)，“華碩〞筆記本一次性最多進(jìn)貨200臺(tái)。而受公司與資金的限制，兩種筆記本的單次進(jìn)貨量不得超越300臺(tái)。其中“戴爾〞筆記本的平均利潤(rùn)為400/臺(tái)，假設(shè)“戴爾〞筆記本每多進(jìn)10臺(tái)，單臺(tái)“戴爾〞筆記本的利潤(rùn)會(huì)增長(zhǎng)5元?！叭A碩〞筆記本的平均利潤(rùn)為350元/臺(tái)，假設(shè)“華碩〞每臺(tái)筆記本多進(jìn)10臺(tái)，單臺(tái)“華碩〞筆記本的利潤(rùn)會(huì)上漲8元。那么該公司如何確定兩種筆記本的單次進(jìn)貨量，才干使效益最大化呢？下面引見(jiàn)如何運(yùn)用“規(guī)劃求解〞工具來(lái)處理問(wèn)題，詳細(xì)操作步驟如下。步驟1在“開(kāi)發(fā)工具〞選項(xiàng)卡下的“加載項(xiàng)〞組中單擊“加載項(xiàng)〞按鈕，如圖11-45所示。步驟2彈出“加載宏〞對(duì)話框，在“可用加載宏〞列表中選中“規(guī)劃求解加載項(xiàng)〞復(fù)選框，最后單擊“確定〞按鈕，如圖11-46所示。步驟3這時(shí)在“數(shù)據(jù)〞選項(xiàng)卡下的“分析〞組中會(huì)添加一個(gè)“規(guī)劃求解〞命令，闡明加載勝利，然后在Sheet5任務(wù)表中建立如圖11-47所示的規(guī)劃模型步驟4在B4單元格中輸入公式“＝SUM(B2＋B3)〞，按Enter鍵計(jì)算總進(jìn)貨量，接著在B6單元格中輸入公式“=(400＋B2/10*5)*B2＋(350＋B3/100*8)*B3〞，按Enter鍵計(jì)算總利潤(rùn)，如圖11-48所示。步驟5單擊“數(shù)據(jù)〞選項(xiàng)，在“分析〞組中選擇“規(guī)劃求解〞命令，如圖11-49所

示。步驟6彈出“規(guī)劃求解參數(shù)〞對(duì)話框，在“設(shè)置目的〞文本框中輸入“$B$6〞，然后在“經(jīng)過(guò)更改可變單元格〞文本框中輸入“$B$2:$B$3〞，接著選中“最大值〞單項(xiàng)選擇按鈕，如圖11-50所示，最后單擊“添加〞按鈕。圖11-45單擊“加載項(xiàng)〞按鈕圖11-46“加載宏〞對(duì)話框步驟7彈出“添加約束〞對(duì)話框，在“單元格援用位置〞文本框中輸入“$B$2〞，選擇“<=〞約束條件，在“約束值〞文本框中輸入“200〞，如圖11-51所示，再單擊“添加〞按鈕。圖11-47規(guī)劃模型框架圖11-48輸入相關(guān)公式圖11-49選擇“規(guī)劃求解〞命令圖11-50“規(guī)劃求解參數(shù)〞對(duì)話框步驟8在“添加約束〞對(duì)話框中為B3單元格添加約束條件，如圖11-52所示，再單擊“添加〞按鈕。圖11-51為B2單元格添加約束條件圖11-53為B4單元格添加約束條件圖11-54參數(shù)設(shè)置后的結(jié)果圖11-52為B3單元格添加約束條件步驟9在“添加約束〞對(duì)話框中繼續(xù)為B4單元格添加約束條件，如圖11-53所示，再單擊“添加〞按鈕。步驟10前往“規(guī)劃求解參數(shù)〞對(duì)話框，單擊“求解〞按鈕，如圖11-54所示。步驟11彈出“規(guī)劃求解結(jié)果〞對(duì)話框，選中“保管求解結(jié)果〞單項(xiàng)選擇按鈕，單擊“確定〞按鈕，如圖11-55所示。步驟12此時(shí)，在任務(wù)表中即可看到求解結(jié)果，如圖11-56所示。當(dāng)“戴爾〞筆記本單筆進(jìn)貨200臺(tái)，“華碩〞筆記本單筆進(jìn)貨100臺(tái)時(shí)，獲得的利潤(rùn)最大。

圖11-56規(guī)劃求解的計(jì)算結(jié)果●11.5.3利用“規(guī)劃求解〞工具確定商品運(yùn)輸方案某公司需將存儲(chǔ)在三個(gè)物流中心L1、L2、L3的同一種貨物，分別運(yùn)輸?shù)紺1、C2、C3三個(gè)城市。L1、L2、L3的發(fā)貨數(shù)量分別為300噸、200噸、300噸，C1、C2、C3的貨物需求量分別為270噸、230噸、300噸。每個(gè)物流中心到各個(gè)城市的價(jià)錢如表11-2所示。下面要經(jīng)過(guò)計(jì)算來(lái)設(shè)計(jì)運(yùn)輸方案，才干使公司本錢最低。圖11-55“規(guī)劃求解結(jié)果〞對(duì)話框圖表11-2每個(gè)物流中心到各個(gè)城市的價(jià)錢C1C2C3可發(fā)貨數(shù)量（噸）L100300300L220000200L3702300300需要收貨數(shù)量（噸）270230300

發(fā)貨地址運(yùn)用費(fèi)用

〔元/噸〕物流中心

由于各個(gè)物流中心到各個(gè)城市的運(yùn)費(fèi)是不同的，而各個(gè)物流中心的可發(fā)貨數(shù)量，以及各個(gè)城市的可收貨數(shù)量也都是有限制的，要想確定運(yùn)輸本錢的最小值，就必需求求設(shè)計(jì)的方案同時(shí)能滿足這些條件。步驟1按照如圖11-58所示的款式建立規(guī)劃求解的模型。其中，B2:B4單元格區(qū)域?yàn)椴煌锪髦行南蚋鱾€(gè)城市的發(fā)貨的數(shù)量，作為未來(lái)規(guī)劃求解中的可變單元格；E2:E4單元格區(qū)域是各物流中心的實(shí)踐發(fā)貨數(shù)量，各單元格運(yùn)用的公式分別為“＝SUM(B2:D2)〞、“＝SUM(B3:D3)〞、“＝SUM(B4:D4)〞；B5:D5單元格區(qū)域是各物流中心的實(shí)踐發(fā)貨數(shù)量，各單元格運(yùn)用的公式分別為“＝SUM(B2:B4)〞、“＝SUM(C2:C4)〞、“＝SUM(D2:D4)〞；E2:G4單元格區(qū)域以及B5:D7單元格區(qū)域是本問(wèn)題的六個(gè)約束控制條件；D11:D13單元格區(qū)域是根據(jù)標(biāo)題中的條件輸入的各物流中心到各城市的運(yùn)輸本錢價(jià)錢；F5單元格是用來(lái)計(jì)算運(yùn)輸本錢，屬于規(guī)劃求解中的目的單元格，公式為“=SUMPRODUCT(B2:D4,B11:D13)〞。步驟2在“數(shù)據(jù)〞選項(xiàng)卡下的“分析〞組中單擊“規(guī)劃求解〞按鈕，彈出“規(guī)劃求解參數(shù)〞對(duì)話框，在“設(shè)置目的〞文本框中輸入“$F$5〞，然后在“經(jīng)過(guò)更改可變單元格〞文本框中輸入“$B$2:$D$4〞，接著選中“最小值〞單項(xiàng)選擇按鈕，最后單擊“添加〞按鈕，如圖11-59所示。步驟3彈出“添加約束〞對(duì)話框，在“單元格援用位置〞文本框中輸入“$B$2:$D$4〞，選擇“>=〞約束條件，在“約束值〞文本框中輸入“0〞，再單擊“添加〞按鈕，添加第一個(gè)約束條件，如圖11-60所示。步驟4在“單元格援用位置〞文本框中輸入“$B$5:$D$5〞，選擇“=〞約束條件，在“約束值〞文本框中輸入“$B$7:$D$7〞，再單擊“添加〞按鈕，添加第二個(gè)約束條件，如圖11-61所示。圖11-58運(yùn)輸本錢最小問(wèn)題的規(guī)劃求解模型圖11-59“規(guī)劃求解參數(shù)〞對(duì)話框圖11-60設(shè)置第一個(gè)約束條件圖11-61設(shè)置第二個(gè)約束條件步驟5在“單元格援用位置〞文本框中輸入“$E$2:$E$4〞，選擇“=〞約束條件，在“約束值〞文本框中輸入“$G$2:$G$4〞，如圖11-62所示。步驟6單擊“確定〞按鈕，前往“規(guī)劃求解參數(shù)〞對(duì)話框，單擊“求解〞按鈕，如圖11-63所示。步驟7彈出“規(guī)劃求解結(jié)果〞對(duì)話框，選中“保管規(guī)劃求解的解〞單項(xiàng)選擇按鈕，單

擊“確定〞按鈕，如圖11-64所示。步驟8此時(shí)，在任務(wù)表中即可看到求解結(jié)果，如圖11-65所示，可知最正確運(yùn)輸方案為:物流中心的L1的300噸貨物全部運(yùn)輸?shù)紺3城市；物流中心的L2的200噸貨物全部運(yùn)輸?shù)紺1城市；物流中心的L3的300噸貨物運(yùn)輸?shù)紺1城市70噸和C2城市230噸。這樣各個(gè)城市的需求量都得到了滿足，各個(gè)物流中心也沒(méi)有超越發(fā)貨數(shù)量，總運(yùn)輸本錢為57150元。圖11-62設(shè)置最后一個(gè)約束條件圖11-63參數(shù)設(shè)置后的結(jié)果圖11-64“規(guī)劃求解結(jié)果〞對(duì)話框圖11-65規(guī)劃求解的計(jì)算結(jié)果

表11-3各區(qū)域中每一個(gè)網(wǎng)點(diǎn)的初始投資額及其年利潤(rùn)的預(yù)測(cè)值●11.5.4利用“規(guī)劃求解〞工具分析設(shè)置快捷酒店網(wǎng)點(diǎn)規(guī)劃M快捷酒店方案進(jìn)入T市進(jìn)展網(wǎng)點(diǎn)規(guī)劃，根據(jù)該市的整體情況，該公司營(yíng)銷將其分成了中心主城區(qū)、經(jīng)濟(jì)開(kāi)發(fā)區(qū)、周邊郊縣區(qū)和高新技術(shù)區(qū)四個(gè)區(qū)域，并從這四個(gè)不同區(qū)域中擬訂了12個(gè)網(wǎng)點(diǎn)設(shè)置Li(i=1，2，3……，12)。根據(jù)市場(chǎng)調(diào)查，這12個(gè)網(wǎng)點(diǎn)位置的初始投資額和每年的利潤(rùn)預(yù)測(cè)值，如表11-3所示〔單位:萬(wàn)元〕。核心主城區(qū)經(jīng)濟(jì)開(kāi)發(fā)區(qū)周邊郊縣區(qū)高新技術(shù)區(qū)L1L2L3L4L5L6L7L8L9L10L11L12所需投資額1001801161002101942428810981178124預(yù)測(cè)年利潤(rùn)324530286470962542327850科目區(qū)域目前，公司總部提出了如下的網(wǎng)點(diǎn)規(guī)劃指點(diǎn)原那么。在該市中心主城區(qū)，至少要從L1、L2、L3、L4中選擇開(kāi)發(fā)兩個(gè)網(wǎng)點(diǎn)。在該市經(jīng)濟(jì)開(kāi)發(fā)區(qū)，至少要從L5、L6中選擇開(kāi)發(fā)一個(gè)網(wǎng)點(diǎn)。在該市周邊郊縣區(qū)，至多要從L7、L8、L9中選擇開(kāi)發(fā)兩個(gè)網(wǎng)點(diǎn)。在該市高新技術(shù)區(qū)，至多要從L10、L11、L12中選擇開(kāi)發(fā)兩個(gè)網(wǎng)點(diǎn)。請(qǐng)問(wèn):在該市的總投資額度不能超越1000萬(wàn)元的前提下，該飯店的營(yíng)銷部應(yīng)該選擇哪幾個(gè)地點(diǎn)來(lái)開(kāi)發(fā)和建立快捷連鎖酒店，才干使總的年利潤(rùn)最大？快捷連鎖酒店運(yùn)營(yíng)地點(diǎn)的設(shè)置問(wèn)題，是一種連鎖企業(yè)經(jīng)常遇到的網(wǎng)點(diǎn)規(guī)劃問(wèn)題，類似的還有超市的網(wǎng)點(diǎn)規(guī)劃、銀行儲(chǔ)蓄所的網(wǎng)點(diǎn)規(guī)劃等。對(duì)于每一個(gè)網(wǎng)點(diǎn)，都存在開(kāi)發(fā)與不開(kāi)發(fā)兩種情況，所以這個(gè)問(wèn)題普通用0～1整數(shù)規(guī)劃問(wèn)題來(lái)處理。

根據(jù)標(biāo)題中的要求，要先建立網(wǎng)點(diǎn)規(guī)劃問(wèn)題的規(guī)劃求解模型。設(shè)Xi為0～1的變量〔其中i＝1，2，3…，12〕，也說(shuō)是說(shuō)Xi=0或者是Xi=1。其中結(jié)果為0表示Li網(wǎng)點(diǎn)沒(méi)有被選中，而結(jié)果為1那么表示Li網(wǎng)點(diǎn)被選中。本問(wèn)題的目的就是使總利潤(rùn)最大，因此，目的函數(shù)為：MAXz=32X1＋45X2＋30X3＋28X4＋64X5＋76X6＋96X7＋25X8＋42X9＋32X10＋78X11＋50X12。約束條件有以下幾條?！?〕全部投資總預(yù)算不大于1000萬(wàn)，也就是說(shuō)：100X1＋180X2＋116X3＋100X4＋210X5＋194X6＋242X7＋88X8＋109X9＋81X10＋178X11＋124X12≤1000。〔2〕在中心主城區(qū)，至少選擇兩個(gè)，那么有X1＋X2＋X3＋X4≥2。〔3〕在經(jīng)濟(jì)開(kāi)發(fā)區(qū)，至少選擇一個(gè)，那么有X5＋X6≥1?！?〕在周邊郊縣區(qū)，至多項(xiàng)選擇擇兩個(gè)，那么有X7＋X8＋X9≤2?！?〕在高新技