EXCEL在會計和財務(wù)管理中運用第11章課件

第11章經(jīng)典實例：銷售決策分析●11.1要點分析●11.2通過建立決策模型進行營銷決策

●11.2.1

產(chǎn)品定價決策問題描述與解決的基本思路●11.2.2構(gòu)建銷售利潤最大化的產(chǎn)品定價決策模型●11.2.3產(chǎn)品定價決策模型中有關(guān)變量的圖表分析●11.3數(shù)據(jù)表在凈利潤敏感度分析中的應(yīng)用●11.3.1產(chǎn)品銷售凈利潤值敏感度分析●11.3.2價格調(diào)整引起的利潤變化●11.3.3價格與數(shù)量同時變動引起的利潤變化●11.4單變量求解在銷售利潤目標(biāo)確定中的應(yīng)用●11.4.1確定目標(biāo)利潤對應(yīng)的銷售收入●11.4.2確定新產(chǎn)品的保本銷售量●11.5使用“規(guī)劃求解”工具分析營銷決策第11章經(jīng)典實例：銷售決策分析●11.1要點分析●11.5.1

規(guī)劃問題的特點以及“規(guī)劃求解”工具的組成●11.5.2利用“規(guī)劃求解”工具解決規(guī)劃求解問題的流程●11.5.3利用“規(guī)劃求解”工具確定商品運輸方案●11.5.4利用“規(guī)劃求解”工具分析設(shè)置快捷酒店網(wǎng)點布局●11.5.1規(guī)劃問題的特點以及“規(guī)劃求解”●11.2通過建立決策模型進行營銷決策

營銷決策是指對有關(guān)產(chǎn)品市場經(jīng)營和銷售活動的目標(biāo)、方針、策略等重大問題進行選擇和決斷的過程。營銷決策是企業(yè)市場營銷中的核心問題，它必須建立在充分的市場調(diào)查和市場預(yù)測的基礎(chǔ)之上。●

11.2.1產(chǎn)品定價決策問題描述與解決的基本思路企業(yè)市場營銷中的一個重要決策問題就是產(chǎn)品定價。而銷售利潤則是企業(yè)管理追求的一個重要目標(biāo)，但是銷售利潤的大小與產(chǎn)品的定價具有重要關(guān)聯(lián)關(guān)系。如果產(chǎn)品價格定得過高，那么單位產(chǎn)品的銷售利潤相應(yīng)的也會增加，但是總的銷售量可能會減少，從而會影響總的產(chǎn)品銷售利潤；反之產(chǎn)品價格定的過低，雖然銷售數(shù)量可能會大幅的增長，但是因為單位產(chǎn)品的銷售利潤不在，也會影響到總的產(chǎn)品銷售利潤。下面是一個產(chǎn)品定價決策的問題描述，并給出相應(yīng)的問題解決的基本思路。如表11-1所示，為某公司根據(jù)市場分析，對其生產(chǎn)的某產(chǎn)品在不同價格水平下的預(yù)測銷售量。假設(shè)某公司生產(chǎn)該產(chǎn)品的全年固定成本為每年150萬無，全年的生產(chǎn)能為55萬

套，每套產(chǎn)品的變動成為20元。請問：某公司應(yīng)該如何對該產(chǎn)品定價？●11.1要點分析

●11.2通過建立決策模型進行營銷決策

營銷決策是問題分析：這里的產(chǎn)品定價，應(yīng)該就是以銷售利潤最大化為目標(biāo)，因此需要構(gòu)造銷售利潤的計算公式。然后根據(jù)相關(guān)變量之間的關(guān)系，這里的銷售利潤計算的公式如下。L=S-C=PQ-(F＋V×Q)＝(P-V)×Q－F其中：L為全年銷售利潤。S為全年銷售額。C為全年銷售成本。P為銷售價格。F為全年固定成本。V為單位變動成本。Q為預(yù)測的銷售量。通過上面公式，就可以計算出來不同價格水平下，對應(yīng)不同預(yù)測銷售量時的銷售利潤；然后可以根據(jù)銷售利潤最大化的原則，即可將其對應(yīng)價格作為最佳的決策價格?！?/p>

11.2.2構(gòu)建銷售利潤最大化的產(chǎn)品定價決策模型下面就可以得用Excel建立模型，來進行以上的銷售利潤輔助計算，操作步驟如下。步驟1根據(jù)已知數(shù)據(jù)以及銷售利潤計算公式，制作如圖11-1所示的表格。步驟2選取C9單元格，輸入公式“＝C4”，獲取第一個價格水平，然后向右拖動填充柄，一直復(fù)制公式到I9單元格獲取其他的價格水平，如圖11-2所示。價格（元）60553530254025預(yù)測銷量（萬套）15103640555056表11-1不同價格水平下的預(yù)測銷售量問題分析：這里的產(chǎn)品定價，應(yīng)該就是以銷售利潤最大化為目標(biāo)，因

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

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

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

C15:I15,0))&"萬套，預(yù)測銷售利潤為"&MAX(C15:I15)&"萬元，得出銷售利潤最大化的產(chǎn)品定價決策模型的分析結(jié)果，如圖11-9所示。步驟5選取C12單元格，輸入公式“=C5*$I$6圖11-7計算出其他的總成本圖11-8計算出不同價格水平下的銷售利潤圖11-9銷售利潤最大化的產(chǎn)品定價決策模型的分析結(jié)果●

11.2.3產(chǎn)品定價決策模型中有關(guān)變量的圖表分析下面就來根據(jù)產(chǎn)品定價決策模型制作數(shù)據(jù)圖表，操作步驟如下。步驟1選取B14:I14單元格區(qū)域，按下Ctrl鍵，依次選取B12:I12單元格區(qū)域、B14:I14單元格區(qū)域和B15:I15單元格區(qū)域。圖11-7計算出其他的總成本圖11-8計算出不同價格步驟2在“插入”選項卡下的“圖表”組中單擊“折線圖”按鈕，在展開的下拉列表中單擊“帶數(shù)據(jù)標(biāo)記的折線圖”圖標(biāo)，如圖11-10所示。步驟3如圖11-11所示，經(jīng)過上述操作，制作出的帶數(shù)據(jù)標(biāo)記的折線圖的初始效果。圖11-11帶數(shù)據(jù)標(biāo)記的折線圖的初始效果步驟4適當(dāng)調(diào)整圖表放置位置，將其移動到適合位置。步驟5右擊圖表水平坐標(biāo)軸，從快捷菜單中選擇“刪除”命令，如圖11-12所示，刪除縱坐標(biāo)軸。步驟6右擊圖例，從快捷菜單中選擇“設(shè)置圖例格式”命令，如圖11-13所示。步驟7彈出“設(shè)置圖例格式”對話框，在“圖例選項”選項卡的右側(cè)窗格中單擊圖例位置“底部”單選按鈕，在“邊框顏色”選項卡的右側(cè)窗格中單擊“實線”單選按鈕，并選擇顏色“黑色”，如圖11-14所示。步驟8在圖表中，右擊“價格水平（元）”折線條，從快捷菜單中選擇“添加數(shù)據(jù)標(biāo)簽”命令，如圖11-15所示，為數(shù)據(jù)添加標(biāo)簽。圖11-10單擊“帶數(shù)據(jù)標(biāo)記的折線圖”圖標(biāo)步驟2在“插入”選項卡下的“圖表”組中單擊“折線圖”按鈕，圖11-13選擇“設(shè)置圖例格式”命令圖11-12選擇“刪除”命令圖11-14“設(shè)置圖例格式”對話框圖11-15選擇“添加數(shù)據(jù)標(biāo)簽”命令步驟9右擊添加的數(shù)據(jù)標(biāo)簽，從快捷菜單中選擇“設(shè)置數(shù)據(jù)系列格式”命令，如圖11-16所示。步驟10彈出“設(shè)置數(shù)據(jù)系列格式”對話框，設(shè)置“系列選項”系列繪制在“次坐標(biāo)軸”，如圖11-17所示，從面建立雙軸圖表。圖11-13選擇“設(shè)置圖例格式”命令圖11-12選擇“刪圖11-17設(shè)置數(shù)據(jù)系列格式圖11-16選擇“設(shè)置數(shù)據(jù)系列格式”命令步驟11右擊圖表的繪圖區(qū)，從快捷菜單中選擇“設(shè)置繪圖區(qū)格式”命令，如圖11-18所示。步驟12彈出“設(shè)置繪圖區(qū)格式”對話框，設(shè)置填充色為“純色填充”，填充顏色

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

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

敏感度分析又稱“What-if分析”，其用途是測定當(dāng)一個參數(shù)發(fā)生變化時，由該參數(shù)所帶的中間變量是如何變化，以及由中間變量引起的最終結(jié)果會發(fā)生什么樣的變化?！?/p>

11.3.1產(chǎn)品銷售凈利潤值敏感度分析在Sheet2工作表中新建如圖11-21所示的表格，左邊部分給出了某公司本月產(chǎn)品的簡易損益表，而右邊部分給出了從基本數(shù)據(jù)輸入開始，到最終的凈利潤輸出為止，中間的數(shù)據(jù)計算規(guī)則。步驟14適當(dāng)調(diào)整圖表的大小，然后設(shè)置A1:S1,A從圖中可以看出，本月該產(chǎn)品的凈利潤約為840萬元?，F(xiàn)在假設(shè)一下，下個月想實現(xiàn)凈利潤達到870萬元，請在只能改變銷售價格和銷售數(shù)量的情況下，對凈利潤值的敏感度進行分析。通過改變銷售價格和銷售數(shù)量，可以進行利潤值敏感度的分析，引起利潤值的變化，更加方便的選擇達到即定值的數(shù)據(jù)。下面在Excel中利用“模擬運算表”工具分析解決該問題。圖11-21某公司某產(chǎn)品本月的簡易損益表●

11.3.2價格調(diào)整引起的利潤變化下面就以在上述的產(chǎn)品損益表中，根據(jù)銷售價格的變化，來模擬凈利潤的變化為

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

圖11-24選擇“模擬運算表”命令圖11-25“模擬運算表”對話框圖11-26單因素利潤敏感度分析的結(jié)果●

11.3.3價格與數(shù)量同時變動引起的利潤變化進行單變量敏感度分析，只是分析改變一個輸入變量對公式計算結(jié)果的影響。如果想要分析兩個變量對公式計算的影響，就需要進行雙變量敏感度分析，這可以通過使用雙變量數(shù)據(jù)來實現(xiàn)。圖11-24選擇“模擬運算表”命令圖

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

圖11-28選擇“模擬運算表”命令步驟4彈出“模擬運算表”對話框，在“輸入引用行的單元格”文本框中輸

入“$B$6”，在“輸入引用列的單元格”文本框中輸入“$B$5”，如圖11-29所示。步驟5單擊“確定”按鈕，結(jié)果如圖11-30所示。圖11-29“模擬運算表”對話框圖11-30按照價格和數(shù)量得到的雙變量數(shù)據(jù)表結(jié)果圖11-28選擇“模擬運算表”命令步驟4彈

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

在“營銷決策.xlsx”工作簿中新插入一張工作表，在工作表中制作某公司編制的本月?lián)p益簡表，如圖11-35所示。在右邊部分是各變量之間的相互關(guān)系及計算方法，左邊部分“本月數(shù)字”一列中帶有填充色的單元格是利用D列中的計算公式進行計算得出的結(jié)果。問題分析：從如圖11-34可以看出，本月凈利潤為78萬元左右。假如公司確定下個月凈利潤要達到80萬元，其他原始數(shù)據(jù)和計算模型中各個參數(shù)都保持不變。那么，銷售收入應(yīng)該增長到多少，才能實現(xiàn)凈利潤達到80萬元的目標(biāo)呢？圖11-33對雙變量敏感度分析的結(jié)果進行條件格式化設(shè)置●圖11-34某公司編制的本月?lián)p益簡表下面將介紹如何運用“單變量求解”工具來解決上述問題?！?/p>

11.4.1確定目標(biāo)利潤對應(yīng)的銷售收入下面采用單變量求解的方法確定目標(biāo)利潤對應(yīng)的銷售收入，具體操作步驟如下。步驟1選定要確定目標(biāo)利潤對應(yīng)的銷售收入的單元格，也就是凈利潤數(shù)據(jù)所在的B16單元格。步驟2在“數(shù)據(jù)”選項卡下的“數(shù)據(jù)工具”組中單擊“模擬分析”按鈕，在展開的下拉列表中選擇“單變量求解”命令，如圖11-35所示。步驟3彈出“單變量求解”對話框，在“目標(biāo)值”文本框中輸入“800000”，

在“可變單元格”文本框中，輸入這里需要確定的可變量—銷售收入所在的“$B$2”單元格，如圖11-36所示。步驟4單擊“確定”按鈕，彈出如圖11-37所示的“單變量求解狀態(tài)”對話框，說明已經(jīng)找到解，并且與目標(biāo)值完全一致。圖11-34某公司編制的本月?lián)p益簡表下面將介紹如何運用“單圖11-35選擇“單變量求解”命令圖11-36“單變量求解”對話框步驟5單擊“確定”按鈕，得到單變量求解的結(jié)果，如圖11-38所示，要實現(xiàn)凈利潤增長到800000.00元，銷售收入需要增加到11217548.57元。圖11-37“單變量求解狀態(tài)”對話框圖11-38單變量求解的結(jié)果圖11-35選擇“單變量求解”命令圖11-36“單變量

●

11.4.2確定新產(chǎn)品的保本銷售量新插入一張工作表，然后在工作表中制作“新開發(fā)產(chǎn)品的保本點計算”表格，如圖11-39所示。其中，B4:C14單元格區(qū)域中是某公司為一種新產(chǎn)品進行銷售利潤測算，按照目前設(shè)定的數(shù)據(jù)，計算出來的銷售利潤為負數(shù)。下面利用“單變量求解”工具確定新產(chǎn)品保本銷售。假設(shè)現(xiàn)在其他數(shù)據(jù)都不能改變，而唯一能夠做出改進的是設(shè)法增大產(chǎn)品的銷售數(shù)量。請問：銷售數(shù)量必須增長到多少時，才能實現(xiàn)本產(chǎn)品的銷售能夠“保本”？下面就來介紹如何利用單變量求解解決這個問題，具體操作步驟如下。圖11-39“新開發(fā)產(chǎn)品的保本點計算”表格步驟1將B4:C14單元格內(nèi)容復(fù)制到B17:C27單元格區(qū)域。步驟2選定C27單元格，也就是銷售利潤所在的單元格，在“數(shù)據(jù)”選項卡下

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

在“可變單元格”文本框中，輸入這里需要確定的可變量—銷售收入所在的“$C$19”單元格，如圖11-41所示?！?1.4.2確定新產(chǎn)品的保本銷圖11-40選擇“單變量求解”命令圖11-41“單變量求解”對話框步驟4單擊“確定”按鈕，彈出如圖11-42所示的“單變量求解狀態(tài)”對話框，說明已經(jīng)找到一個解，并且與目標(biāo)值是完全一樣的。步驟5單擊“確定”按鈕，得到單變量求解的結(jié)果，如圖11-43所示，可以看

出“保本點”銷售為90000，也就是說如果其他各量都不變，必須銷售90000個以上的本產(chǎn)品，才能實現(xiàn)贏利。圖11-42“單變量求解狀態(tài)”對話框圖11-43利用單變量求解確定保本銷售量圖11-40選擇“單變量求解”命令圖11-41“單變量求●11.5使用“規(guī)劃求解”工具分析營銷決策

在實際的銷售管理中，要完成一項目標(biāo)任務(wù)的時候，就會經(jīng)常會存在著多個因素，這些因素都具有一定的約束條件。下面將介紹如何在約束條件下進行最優(yōu)確定?！?/p>

11.5.1規(guī)劃問題的特點以及“規(guī)劃求解”工具的組成對于規(guī)劃求解問題的解決，在Excel中專門提供了“規(guī)劃求解”加載宏工具。1．規(guī)劃問題的特點在計劃管理當(dāng)中，往往會遇到各種各樣的規(guī)劃問題，例如：人力資源的調(diào)度、產(chǎn)品生產(chǎn)的安排、運輸線路的規(guī)劃、生產(chǎn)材料的搭配、采購批次的確定等。這類問題有一個共同的要求，那就是：如果合理的利用各種約束資源從而達到最佳的經(jīng)濟效益，也就是達到產(chǎn)量最高、利潤最大、成本最低、費用最省等目標(biāo)。一般來說，這類規(guī)劃問題都具有如下三個特點。（1）所求的問題都有一個單一的目標(biāo)，如求生產(chǎn)的最低成本、求運輸?shù)淖罴训穆肪€、求產(chǎn)品的最大盈利、求產(chǎn)品周期的最短時間以及求其他目標(biāo)函數(shù)的最優(yōu)值等。（2）總是有明確的不等式約束的條件。比如庫存不能低于一定的數(shù)量，否則就會造成原料短缺或產(chǎn)品缺貨的情況；生產(chǎn)的產(chǎn)品不能超過一定的額度，否則會造成商品積壓等。（3）問題都有直接或間接影響約束條件的一組輸入值。2．Excel“規(guī)劃求解”工具的組成規(guī)劃求解就是在滿足約束條件的前提下，調(diào)整決策變量，實現(xiàn)目標(biāo)函數(shù)最優(yōu)。因此，●11.5使用“規(guī)劃求解”工具分析營銷決策

在實際可以說規(guī)劃求解工具包括決策變量、目標(biāo)函數(shù)和約束條件三部分。（1）決策變量：是指在實際的問題當(dāng)有有一些待解決的未知因素，一個規(guī)劃問題中可能會有一個決策變量，也有可能會有多個決策變量。一組決策變量代表一個規(guī)劃求解的方案。在Excel的規(guī)劃求解模型中，決策變量通常利用可變單元格進行表示。（2）目標(biāo)函數(shù)：用來表示規(guī)劃求解要達到的最終目標(biāo)，如求最短路徑、最大利潤、最小成本、最佳產(chǎn)品組合等。在規(guī)劃模型中，目標(biāo)函數(shù)應(yīng)該是決策變量的函數(shù)。也就是說，在Excel中目標(biāo)函數(shù)與可變單元格有著直接或間接的聯(lián)系，它可以進線性函數(shù)（對應(yīng)規(guī)劃問題稱為“線性規(guī)劃”），也可以是非線性函數(shù)（對應(yīng)規(guī)劃問題稱為“非線性規(guī)劃”）。（3）約束條件：是指實現(xiàn)目標(biāo)的限制條件，規(guī)劃求解是否有解，與約束條件有著密切的關(guān)系，它對可變單元格中的值起著直接限制的作用。約束的條件可以是等式，也可以不是等式?！?/p>

11.5.2利用“規(guī)劃求解”工具解決規(guī)劃求解問題的流程某電腦銷售公司主要銷售“戴爾”和“華碩”兩種筆記本。因擔(dān)心進貨積壓，公司規(guī)定“戴爾”筆記本一次性最多進貨200臺，“華碩”筆記本一次性最多進貨200臺。而受公司與資金的限制，兩種筆記本的單次進貨量不得超過300臺。其中“戴爾”筆記本的平均利潤為400/臺，若“戴爾”筆記本每多進10臺，單臺“戴爾”筆記本的利潤會增長5元?！叭A碩”筆記本的平均利潤為350元/臺，如果“華碩”每臺筆記本多進10臺，單臺“華碩”筆記本的利潤會上漲8元。那么該公司如何確定兩種筆記本的單次進貨量，才能使效益最大化呢？下面介紹如何使用“規(guī)劃求解”工具來解決問題，具體操作步驟如下。步驟1在“開發(fā)工具”選項卡下的“加載項”組中單擊“加載項”按鈕，如圖11-45所示。可以說規(guī)劃求解工具包括決策變量、目標(biāo)函數(shù)和約束條件三部分。步驟2彈出“加載宏”對話框，在“可用加載宏”列表中選中“規(guī)劃求解加載項”復(fù)選框，最后單擊“確定”按鈕，如圖11-46所示。步驟3這時在“數(shù)據(jù)”選項卡下的“分析”組中會增加一個“規(guī)劃求解”命令，說明加載成功，然后在Sheet5工作表中建立如圖11-47所示的規(guī)劃模型步驟4在B4單元格中輸入公式“＝SUM(B2＋B3)”，按Enter鍵計算總進貨量，接著在B6單元格中輸入公式“=(400＋B2/10*5)*B2＋(350＋B3/100*8)*B3”，按Enter鍵計算總利潤，如圖11-48所示。步驟5單擊“數(shù)據(jù)”選項，在“分析”組中選擇“規(guī)劃求解”命令，如圖11-49所

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

圖11-56規(guī)劃求解的計算結(jié)果●

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

發(fā)貨地址運用費用

（元/噸）物流中心圖11-56規(guī)劃求解的計算結(jié)果●11.5

因為各個物流中心到各個城市的運費是不同的，而各個物流中心的可發(fā)貨數(shù)量，以及各個城市的可收貨數(shù)量也都是有限制的，要想確定運輸成本的最小值，就必須要求設(shè)計的方案同時能滿足這些條件。步驟1按照如圖11-58所示的樣式建立規(guī)劃求解的模型。其中，B2:B4單元格區(qū)域為不同物流中心向各個城市的發(fā)貨的數(shù)量，作為將來規(guī)劃求解中的可變單元格；E2:E4單元格區(qū)域是各物流中心的實際發(fā)貨數(shù)量，各單元格使用的公式分別為“＝SUM(B2:D2)”、“＝SUM(B3:D3)”、“＝SUM(B4:D4)”；B5:D5單元格區(qū)域是各物流中心的實際發(fā)貨數(shù)量，各單元格使用的公式分別為“＝SUM(B2:B4)”、“＝SUM(C2:C4)”、“＝SUM(D2:D4)”；E2:G4單元格區(qū)域以及B5:D7單元格區(qū)域是本問題的六個約束控制條件；D11:D13單元格區(qū)域是根據(jù)題目中的條件輸入的各物流中心到各城市的運輸成本價格；F5單元格是用來計算運輸成本，屬于規(guī)劃求解中的目標(biāo)單元格，公式為“=SUMPRODUCT(B2:D4,B11:D13)”。步驟2在“數(shù)據(jù)”選項卡下的“分析”組中單擊“規(guī)劃求解”按鈕，彈出“規(guī)劃求解參數(shù)”對話框，在“設(shè)置目標(biāo)”文本框中輸入“$F$5”，然后在“通過更改可變單元格”文本框中輸入“$B$2:$D$4”，接著選中“最小值”單選按鈕，最后單擊“添加”按鈕，如圖11-59所示。步驟3彈出“添加約束”對話框，在“單元格引用位置”文本框中輸入“$B$2:$D$4”，選擇“>=”約束條件，在“約束值”文本框中輸入“0”，再單擊“添加”按鈕，添加第一個約束條件，如圖11-60所示。步驟4在“單元格引用位置”文本框中輸入“$B$5:$D$5”，選擇“=”約束條件，在“約束值”文本框中輸入“$B$7:$D$7”，再單擊“添加”按鈕，添加第二個約束條件，如圖11-61所示。因為各個物流中心到各個城市的運費是不同圖11-58運輸成本最小問題的規(guī)劃求解模型圖11-59“規(guī)劃求解參數(shù)”對話框圖11-60設(shè)置第一個約束條件圖11-61設(shè)置第二個約束條件步驟5在“單元格引用位置”文本框中輸入“$E$2:$E$4”，選擇“=”約束條件，在“約束值”文本框中輸入“$G$2:$G$4”，如圖11-62所示。步驟6單擊“確定”按鈕，返回“規(guī)劃求解參數(shù)”對話框，單擊“求解”按鈕，如圖11-63所示。步驟7彈出“規(guī)劃求解結(jié)果”對話框，選中“保留規(guī)劃求解的解”單選按鈕，單

擊“確定”按鈕，如圖11-64所示。步驟8此時，在工作表中即可看到求解結(jié)果，如圖11-65所示，可知最佳運輸方案為:物流中心的L1的300噸貨物全部運輸?shù)紺3城市；物流中心的L2的200噸貨物全部運輸?shù)綀D11-58運輸成本最小問題的規(guī)劃求解模型圖11-59C1城市；物流中心的L3的300噸貨物運輸?shù)紺1城市70噸和C2城市230噸。這樣各個城市的需求量都得到了滿足，各個物流中心也沒有超過發(fā)貨數(shù)量，總運輸成本為57150元。圖11-62設(shè)置最后一個約束條件圖11-63參數(shù)設(shè)置后的結(jié)果圖11-64“規(guī)劃求解結(jié)果”對話框圖11-65規(guī)劃求解的計算結(jié)果C1城市；物流中心的L3的300噸貨物運輸?shù)紺1城市70噸和

表11-3各區(qū)域中每一個網(wǎng)點的初始投資額及其年利潤的預(yù)測值●

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

根據(jù)題目中的要求，要先建立網(wǎng)點布局問題的規(guī)劃求解模型。設(shè)Xi為0～1的變量（其中i＝1，2，3…，12），也說是說Xi=0或者是Xi=1。其中結(jié)果為0表示Li網(wǎng)點沒有被選中，而結(jié)果為1則表示Li網(wǎng)點被選中。本問題的目標(biāo)就是使總利潤最大，因此，目標(biāo)函數(shù)為：MAXz=32X1＋45X2＋30X3＋28X4＋64X5＋76X6＋96X7＋25X8＋42X9＋32X10＋78X11＋50X12。約束條件有以下幾條。（1）全部投資總預(yù)算不大于1000萬，也就是說：100X1＋180X2＋116X3＋100X4＋210X5＋194X6＋242X7＋88X8＋109X9＋81X10＋178X11＋124X12≤1000。（2）在核心主城區(qū)，至少選擇兩個，則有X1＋X2＋X3＋X4≥2。（3）在經(jīng)濟開發(fā)區(qū)，至少選擇一個，則有X5＋X6≥1。（4）在周邊郊縣區(qū)，至多選擇兩個，則有X7＋X8＋X9≤2。（5）在高新技術(shù)區(qū)，至多選擇兩個，則有X10＋X11＋X12≤2。步驟1根據(jù)上述分析，建立規(guī)劃求解問題的模型表格，并進行相關(guān)項目的格式設(shè)置和公式輸入，如圖11-66所示。其中：C4:N6單元格區(qū)域是已經(jīng)知道數(shù)據(jù)所在的單元格區(qū)域；C10:N10單元格區(qū)域是決策變量（可變參數(shù)）所在的單元格區(qū)域；G13:K17單元格區(qū)域是約束條件所在的單元格區(qū)域；M15單元格（合并了M15:N17單元格區(qū)域后的單元格）為目標(biāo)函數(shù)所在的單元格；選取G13單元格，輸入公式“=SUMPRODUCT(C5:N5,C10:N10)”；選取G14單元格，輸入公式“=SUM(C10:F10)”；在G15單元格中輸入公式“=SUM

(G10:H10)”；在G16單元格中輸入公式“=SUM(I10:K10)”；在G17單元格中輸入公式“=SUM(L10:N10)”；在M15單元格中輸入公式“=SUMPRODUCT(C10:N10,C6:N6)”。步驟2在“數(shù)據(jù)”選項卡下的“分析”組中單擊“規(guī)劃求解”按鈕，彈出“規(guī)劃求解參數(shù)”對話框，在“設(shè)置目標(biāo)”文本框中輸入“$M$15”，然后在“通過更改可變單元格”文本框中輸入“$C$10:$N$10”，接著選中“最大值”單選按鈕，最后單擊“添加按鈕”，如圖11-67所示。根據(jù)題目中的要求，要先建立網(wǎng)點布局問題圖11-66快捷酒店網(wǎng)點布局決策分析的規(guī)劃求解模型圖11-67“規(guī)劃求解參數(shù)”對話框圖11-68添加第一個約束條件圖11-69添加第二個約束條件步驟3彈出“添加約束”對話框，在“單元格引用位置”文本框中輸

入“$C$10:$N$10”，選擇“bin”約束條件，在“約束值”文本框中輸入“二進制”。然后單擊“添加”按鈕，如圖11-68所示步驟4在“單元格引用位置”文本框中輸入“$C$10:$N$10”，選擇“>=”約束條件，在“約束值”文本框中輸入“0”。然后單擊“添加”按鈕，如圖11-69所示。步驟5在“單元格引用位置”文本框中輸入“$G$13”，選擇“<=”約束條件，

在“約束值”文本框中輸入“$K$13”。然后單擊“添加”按鈕，如圖11-70所示。圖11-66快捷酒店網(wǎng)點布局決策分析的規(guī)劃求解模型圖11-圖11-70添加第三個約束條件圖11-71添加第四個約束條件圖11-72添加最后一個約束條件圖11-73設(shè)置好參數(shù)的“規(guī)劃求解”對話框步驟7在“單元格引用位置”文本框中輸入“$G$16:$G$17”，選擇“>=”約束條件，在“約束值”文本框中輸入“$K$16:$K$17”，如圖11-72所示。步驟8單擊“確定”按鈕返回“規(guī)劃求解”對話框，單擊“求解”按鈕，如圖11-73所示。步驟6在“單元格引用位置”文本框中輸入“$G$14:$G$15”，選擇“>=”約束條件，在“約束值”文本框中輸入“$K$14:$K$15”。然后單擊“添加”按鈕，如圖11-71所示。圖11-70添加第三個約束條件圖11-71添加第四個約束圖11-74“規(guī)劃求解結(jié)果”對話框圖11-75規(guī)劃求解的計算結(jié)果步驟9彈出“規(guī)劃求解結(jié)果”對話框，選中“保存規(guī)劃求解結(jié)果”單選按鈕，單擊“確定”按鈕，如圖11-74所示。步驟10此時，工作表中即可看到求解結(jié)果，如圖11-75所示，最佳網(wǎng)點布局方案為:在核心主城區(qū)的L1、L2、L3和L4進行網(wǎng)點開發(fā)；在經(jīng)濟開發(fā)區(qū)的L5和L6進行網(wǎng)點開發(fā)；在高新技術(shù)區(qū)的L11進行網(wǎng)點開發(fā)。圖11-74“規(guī)劃求解結(jié)果”對話框圖11-75規(guī)劃求解的第11章經(jīng)典實例：銷售決策分析●11.1要點分析●11.2通過建立決策模型進行營銷決策

●11.2.1

產(chǎn)品定價決策問題描述與解決的基本思路●11.2.2構(gòu)建銷售利潤最大化的產(chǎn)品定價決策模型●11.2.3產(chǎn)品定價決策模型中有關(guān)變量的圖表分析●11.3數(shù)據(jù)表在凈利潤敏感度分析中的應(yīng)用●11.3.1產(chǎn)品銷售凈利潤值敏感度分析●11.3.2價格調(diào)整引起的利潤變化●11.3.3價格與數(shù)量同時變動引起的利潤變化●11.4單變量求解在銷售利潤目標(biāo)確定中的應(yīng)用●11.4.1確定目標(biāo)利潤對應(yīng)的銷售收入●11.4.2確定新產(chǎn)品的保本銷售量●11.5使用“規(guī)劃求解”工具分析營銷決策第11章經(jīng)典實例：銷售決策分析●11.1要點分析●11.5.1

規(guī)劃問題的特點以及“規(guī)劃求解”工具的組成●11.5.2利用“規(guī)劃求解”工具解決規(guī)劃求解問題的流程●11.5.3利用“規(guī)劃求解”工具確定商品運輸方案●11.5.4利用“規(guī)劃求解”工具分析設(shè)置快捷酒店網(wǎng)點布局●11.5.1規(guī)劃問題的特點以及“規(guī)劃求解”●11.2通過建立決策模型進行營銷決策

營銷決策是指對有關(guān)產(chǎn)品市場經(jīng)營和銷售活動的目標(biāo)、方針、策略等重大問題進行選擇和決斷的過程。營銷決策是企業(yè)市場營銷中的核心問題，它必須建立在充分的市場調(diào)查和市場預(yù)測的基礎(chǔ)之上。●

11.2.1產(chǎn)品定價決策問題描述與解決的基本思路企業(yè)市場營銷中的一個重要決策問題就是產(chǎn)品定價。而銷售利潤則是企業(yè)管理追求的一個重要目標(biāo)，但是銷售利潤的大小與產(chǎn)品的定價具有重要關(guān)聯(lián)關(guān)系。如果產(chǎn)品價格定得過高，那么單位產(chǎn)品的銷售利潤相應(yīng)的也會增加，但是總的銷售量可能會減少，從而會影響總的產(chǎn)品銷售利潤；反之產(chǎn)品價格定的過低，雖然銷售數(shù)量可能會大幅的增長，但是因為單位產(chǎn)品的銷售利潤不在，也會影響到總的產(chǎn)品銷售利潤。下面是一個產(chǎn)品定價決策的問題描述，并給出相應(yīng)的問題解決的基本思路。如表11-1所示，為某公司根據(jù)市場分析，對其生產(chǎn)的某產(chǎn)品在不同價格水平下的預(yù)測銷售量。假設(shè)某公司生產(chǎn)該產(chǎn)品的全年固定成本為每年150萬無，全年的生產(chǎn)能為55萬

套，每套產(chǎn)品的變動成為20元。請問：某公司應(yīng)該如何對該產(chǎn)品定價？●11.1要點分析

●11.2通過建立決策模型進行營銷決策

營銷決策是問題分析：這里的產(chǎn)品定價，應(yīng)該就是以銷售利潤最大化為目標(biāo)，因此需要構(gòu)造銷售利潤的計算公式。然后根據(jù)相關(guān)變量之間的關(guān)系，這里的銷售利潤計算的公式如下。L=S-C=PQ-(F＋V×Q)＝(P-V)×Q－F其中：L為全年銷售利潤。S為全年銷售額。C為全年銷售成本。P為銷售價格。F為全年固定成本。V為單位變動成本。Q為預(yù)測的銷售量。通過上面公式，就可以計算出來不同價格水平下，對應(yīng)不同預(yù)測銷售量時的銷售利潤；然后可以根據(jù)銷售利潤最大化的原則，即可將其對應(yīng)價格作為最佳的決策價格?！?/p>

11.2.2構(gòu)建銷售利潤最大化的產(chǎn)品定價決策模型下面就可以得用Excel建立模型，來進行以上的銷售利潤輔助計算，操作步驟如下。步驟1根據(jù)已知數(shù)據(jù)以及銷售利潤計算公式，制作如圖11-1所示的表格。步驟2選取C9單元格，輸入公式“＝C4”，獲取第一個價格水平，然后向右拖動填充柄，一直復(fù)制公式到I9單元格獲取其他的價格水平，如圖11-2所示。價格（元）60553530254025預(yù)測銷量（萬套）15103640555056表11-1不同價格水平下的預(yù)測銷售量問題分析：這里的產(chǎn)品定價，應(yīng)該就是以銷售利潤最大化為目標(biāo)，因

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

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

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

C15:I15,0))&"萬套，預(yù)測銷售利潤為"&MAX(C15:I15)&"萬元，得出銷售利潤最大化的產(chǎn)品定價決策模型的分析結(jié)果，如圖11-9所示。步驟5選取C12單元格，輸入公式“=C5*$I$6圖11-7計算出其他的總成本圖11-8計算出不同價格水平下的銷售利潤圖11-9銷售利潤最大化的產(chǎn)品定價決策模型的分析結(jié)果●

11.2.3產(chǎn)品定價決策模型中有關(guān)變量的圖表分析下面就來根據(jù)產(chǎn)品定價決策模型制作數(shù)據(jù)圖表，操作步驟如下。步驟1選取B14:I14單元格區(qū)域，按下Ctrl鍵，依次選取B12:I12單元格區(qū)域、B14:I14單元格區(qū)域和B15:I15單元格區(qū)域。圖11-7計算出其他的總成本圖11-8計算出不同價格步驟2在“插入”選項卡下的“圖表”組中單擊“折線圖”按鈕，在展開的下拉列表中單擊“帶數(shù)據(jù)標(biāo)記的折線圖”圖標(biāo)，如圖11-10所示。步驟3如圖11-11所示，經(jīng)過上述操作，制作出的帶數(shù)據(jù)標(biāo)記的折線圖的初始效果。圖11-11帶數(shù)據(jù)標(biāo)記的折線圖的初始效果步驟4適當(dāng)調(diào)整圖表放置位置，將其移動到適合位置。步驟5右擊圖表水平坐標(biāo)軸，從快捷菜單中選擇“刪除”命令，如圖11-12所示，刪除縱坐標(biāo)軸。步驟6右擊圖例，從快捷菜單中選擇“設(shè)置圖例格式”命令，如圖11-13所示。步驟7彈出“設(shè)置圖例格式”對話框，在“圖例選項”選項卡的右側(cè)窗格中單擊圖例位置“底部”單選按鈕，在“邊框顏色”選項卡的右側(cè)窗格中單擊“實線”單選按鈕，并選擇顏色“黑色”，如圖11-14所示。步驟8在圖表中，右擊“價格水平（元）”折線條，從快捷菜單中選擇“添加數(shù)據(jù)標(biāo)簽”命令，如圖11-15所示，為數(shù)據(jù)添加標(biāo)簽。圖11-10單擊“帶數(shù)據(jù)標(biāo)記的折線圖”圖標(biāo)步驟2在“插入”選項卡下的“圖表”組中單擊“折線圖”按鈕，圖11-13選擇“設(shè)置圖例格式”命令圖11-12選擇“刪除”命令圖11-14“設(shè)置圖例格式”對話框圖11-15選擇“添加數(shù)據(jù)標(biāo)簽”命令步驟9右擊添加的數(shù)據(jù)標(biāo)簽，從快捷菜單中選擇“設(shè)置數(shù)據(jù)系列格式”命令，如圖11-16所示。步驟10彈出“設(shè)置數(shù)據(jù)系列格式”對話框，設(shè)置“系列選項”系列繪制在“次坐標(biāo)軸”，如圖11-17所示，從面建立雙軸圖表。圖11-13選擇“設(shè)置圖例格式”命令圖11-12選擇“刪圖11-17設(shè)置數(shù)據(jù)系列格式圖11-16選擇“設(shè)置數(shù)據(jù)系列格式”命令步驟11右擊圖表的繪圖區(qū)，從快捷菜單中選擇“設(shè)置繪圖區(qū)格式”命令，如圖11-18所示。步驟12彈出“設(shè)置繪圖區(qū)格式”對話框，設(shè)置填充色為“純色填充”，填充顏色

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

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

敏感度分析又稱“What-if分析”，其用途是測定當(dāng)一個參數(shù)發(fā)生變化時，由該參數(shù)所帶的中間變量是如何變化，以及由中間變量引起的最終結(jié)果會發(fā)生什么樣的變化?！?/p>

11.3.1產(chǎn)品銷售凈利潤值敏感度分析在Sheet2工作表中新建如圖11-21所示的表格，左邊部分給出了某公司本月產(chǎn)品的簡易損益表，而右邊部分給出了從基本數(shù)據(jù)輸入開始，到最終的凈利潤輸出為止，中間的數(shù)據(jù)計算規(guī)則。步驟14適當(dāng)調(diào)整圖表的大小，然后設(shè)置A1:S1,A從圖中可以看出，本月該產(chǎn)品的凈利潤約為840萬元。現(xiàn)在假設(shè)一下，下個月想實現(xiàn)凈利潤達到870萬元，請在只能改變銷售價格和銷售數(shù)量的情況下，對凈利潤值的敏感度進行分析。通過改變銷售價格和銷售數(shù)量，可以進行利潤值敏感度的分析，引起利潤值的變化，更加方便的選擇達到即定值的數(shù)據(jù)。下面在Excel中利用“模擬運算表”工具分析解決該問題。圖11-21某公司某產(chǎn)品本月的簡易損益表●

11.3.2價格調(diào)整引起的利潤變化下面就以在上述的產(chǎn)品損益表中，根據(jù)銷售價格的變化，來模擬凈利潤的變化為

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

圖11-24選擇“模擬運算表”命令圖11-25“模擬運算表”對話框圖11-26單因素利潤敏感度分析的結(jié)果●

11.3.3價格與數(shù)量同時變動引起的利潤變化進行單變量敏感度分析，只是分析改變一個輸入變量對公式計算結(jié)果的影響。如果想要分析兩個變量對公式計算的影響，就需要進行雙變量敏感度分析，這可以通過使用雙變量數(shù)據(jù)來實現(xiàn)。圖11-24選擇“模擬運算表”命令圖

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

圖11-28選擇“模擬運算表”命令步驟4彈出“模擬運算表”對話框，在“輸入引用行的單元格”文本框中輸

入“$B$6”，在“輸入引用列的單元格”文本框中輸入“$B$5”，如圖11-29所示。步驟5單擊“確定”按鈕，結(jié)果如圖11-30所示。圖11-29“模擬運算表”對話框圖11-30按照價格和數(shù)量得到的雙變量數(shù)據(jù)表結(jié)果圖11-28選擇“模擬運算表”命令步驟4彈

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

在“營銷決策.xlsx”工作簿中新插入一張工作表，在工作表中制作某公司編制的本月?lián)p益簡表，如圖11-35所示。在右邊部分是各變量之間的相互關(guān)系及計算方法，左邊部分“本月數(shù)字”一列中帶有填充色的單元格是利用D列中的計算公式進行計算得出的結(jié)果。問題分析：從如圖11-34可以看出，本月凈利潤為78萬元左右。假如公司確定下個月凈利潤要達到80萬元，其他原始數(shù)據(jù)和計算模型中各個參數(shù)都保持不變。那么，銷售收入應(yīng)該增長到多少，才能實現(xiàn)凈利潤達到80萬元的目標(biāo)呢？圖11-33對雙變量敏感度分析的結(jié)果進行條件格式化設(shè)置●圖11-34某公司編制的本月?lián)p益簡表下面將介紹如何運用“單變量求解”工具來解決上述問題。●

11.4.1確定目標(biāo)利潤對應(yīng)的銷售收入下面采用單變量求解的方法確定目標(biāo)利潤對應(yīng)的銷售收入，具體操作步驟如下。步驟1選定要確定目標(biāo)利潤對應(yīng)的銷售收入的單元格，也就是凈利潤數(shù)據(jù)所在的B16單元格。步驟2在“數(shù)據(jù)”選項卡下的“數(shù)據(jù)工具”組中單擊“模擬分析”按鈕，在展開的下拉列表中選擇“單變量求解”命令，如圖11-35所示。步驟3彈出“單變量求解”對話框，在“目標(biāo)值”文本框中輸入“800000”，

在“可變單元格”文本框中，輸入這里需要確定的可變量—銷售收入所在的“$B$2”單元格，如圖11-36所示。步驟4單擊“確定”按鈕，彈出如圖11-37所示的“單變量求解狀態(tài)”對話框，說明已經(jīng)找到解，并且與目標(biāo)值完全一致。圖11-34某公司編制的本月?lián)p益簡表下面將介紹如何運用“單圖11-35選擇“單變量求解”命令圖11-36“單變量求解”對話框步驟5單擊“確定”按鈕，得到單變量求解的結(jié)果，如圖11-38所示，要實現(xiàn)凈利潤增長到800000.00元，銷售收入需要增加到11217548.57元。圖11-37“單變量求解狀態(tài)”對話框圖11-38單變量求解的結(jié)果圖11-35選擇“單變量求解”命令圖11-36“單變量

●

11.4.2確定新產(chǎn)品的保本銷售量新插入一張工作表，然后在工作表中制作“新開發(fā)產(chǎn)品的保本點計算”表格，如圖11-39所示。其中，B4:C14單元格區(qū)域中是某公司為一種新產(chǎn)品進行銷售利潤測算，按照目前設(shè)定的數(shù)據(jù)，計算出來的銷售利潤為負數(shù)。下面利用“單變量求解”工具確定新產(chǎn)品保本銷售。假設(shè)現(xiàn)在其他數(shù)據(jù)都不能改變，而唯一能夠做出改進的是設(shè)法增大產(chǎn)品的銷售數(shù)量。請問：銷售數(shù)量必須增長到多少時，才能實現(xiàn)本產(chǎn)品的銷售能夠“保本”？下面就來介紹如何利用單變量求解解決這個問題，具體操作步驟如下。圖11-39“新開發(fā)產(chǎn)品的保本點計算”表格步驟1將B4:C14單元格內(nèi)容復(fù)制到B17:C27單元格區(qū)域。步驟2選定C27單元格，也就是銷售利潤所在的單元格，在“數(shù)據(jù)”選項卡下

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

在“可變單元格”文本框中，輸入這里需要確定的可變量—銷售收入所在的“$C$19”單元格，如圖11-41所示?！?1.4.2確定新產(chǎn)品的保本銷圖11-40選擇“單變量求解”命令圖11-41“單變量求解”對話框步驟4單擊“確定”按鈕，彈出如圖11-42所示的“單變量求解狀態(tài)”對話框，說明已經(jīng)找到一個解，并且與目標(biāo)值是完全一樣的。步驟5單擊“確定”按鈕，得到單變量求解的結(jié)果，如圖11-43所示，可以看

出“保本點”銷售為90000，也就是說如果其他各量都不變，必須銷售90000個以上的本產(chǎn)品，才能實現(xiàn)贏利。圖11-42“單變量求解狀態(tài)”對話框圖11-43利用單變量求解確定保本銷售量圖11-40選擇“單變量求解”命令圖11-41“單變量求●11.5使用“規(guī)劃求解”工具分析營銷決策

在實際的銷售管理中，要完成一項目標(biāo)任務(wù)的時候，就會經(jīng)常會存在著多個因素，這些因素都具有一定的約束條件。下面將介紹如何在約束條件下進行最優(yōu)確定?！?/p>

11.5.1規(guī)劃問題的特點以及“規(guī)劃求解”工具的組成對于規(guī)劃求解問題的解決，在Excel中專門提供了“規(guī)劃求解”加載宏工具。1．規(guī)劃問題的特點在計劃管理當(dāng)中，往往會遇到各種各樣的規(guī)劃問題，例如：人力資源的調(diào)度、產(chǎn)品生產(chǎn)的安排、運輸線路的規(guī)劃、生產(chǎn)材料的搭配、采購批次的確定等。這類問題有一個共同的要求，那就是：如果合理的利用各種約束資源從而達到最佳的經(jīng)濟效益，也就是達到產(chǎn)量最高、利潤最大、成本最低、費用最省等目標(biāo)。一般來說，這類規(guī)劃問題都具有如下三個特點。（1）所求的問題都有一個單一的目標(biāo)，如求生產(chǎn)的最低成本、求運輸?shù)淖罴训穆肪€、求產(chǎn)品的最大盈利、求產(chǎn)品周期的最短時間以及求其他目標(biāo)函數(shù)的最優(yōu)值等。（2）總是有明確的不等式約束的條件。比如庫存不能低于一定的數(shù)量，否則就會造成原料短缺或產(chǎn)品缺貨的情況；生產(chǎn)的產(chǎn)品不能超過一定的額度，否則會造成商品積壓等。（3）問題都有直接或間接影響約束條件的一組輸入值。2．Excel“規(guī)劃求解”工具的組成規(guī)劃求解就是在滿足約束條件的前提下，調(diào)整決策變量，實現(xiàn)目標(biāo)函數(shù)最優(yōu)。因此，●11.5使用“規(guī)劃求解”工具分析營銷決策

在實際可以說規(guī)劃求解工具包括決策變量、目標(biāo)函數(shù)和約束條件三部分。（1）決策變量：是指在實際的問題當(dāng)有有一些待解決的未知因素，一個規(guī)劃問題中可能會有一個決策變量，也有可能會有多個決策變量。一組決策變量代表一個規(guī)劃求解的方案。在Excel的規(guī)劃求解模型中，決策變量通常利用可變單元格進行表示。（2）目標(biāo)函數(shù)：用來表示規(guī)劃求解要達到的最終目標(biāo)，如求最短路徑、最大利潤、最小成本、最佳產(chǎn)品組合等。在規(guī)劃模型中，目標(biāo)函數(shù)應(yīng)該是決策變量的函數(shù)。也就是說，在Excel中目標(biāo)函數(shù)與可變單元格有著直接或間接的聯(lián)系，它可以進線性函數(shù)（對應(yīng)規(guī)劃問題稱為“線性規(guī)劃”），也可以是非線性函數(shù)（對應(yīng)規(guī)劃問題稱為“非線性規(guī)劃”）。（3）約束條件：是指實現(xiàn)目標(biāo)的限制條件，規(guī)劃求解是否有解，與約束條件有著密切的關(guān)系，它對可變單元格中的值起著直接限制的作用。約束的條件可以是等式，也可以不是等式?！?/p>

11.5.2利用“規(guī)劃求解”工具解決規(guī)劃求解問題的流程某電腦銷售公司主要銷售“戴爾”和“華碩”兩種筆記本。因擔(dān)心進貨積壓，公司規(guī)定“戴爾”筆記本一次性最多進貨200臺，“華碩”筆記本一次性最多進貨200臺。而受公司與資金的限制，兩種筆記本的單次進貨量不得超過300臺。其中“戴爾”筆記本的平均利潤為400/臺，若“戴爾”筆記本每多進10臺，單臺“戴爾”筆記本的利潤會增長5元?！叭A碩”筆記本的平均利潤為350元/臺，如果“華碩”每臺筆記本多進10臺，單臺“華碩”筆記本的利潤會上漲8元。那么該公司如何確定兩種筆記本的單次進貨量，才能使效益最大化呢？下面介紹如何使用“規(guī)劃求解”工具來解決問題，具體操作步驟如下。步驟1在“開發(fā)工具”選項卡下的“加載項”組中單擊“加載項”按鈕，如圖11-45所示?？梢哉f規(guī)劃求解工具包括決策變量、目標(biāo)函數(shù)和約束條件三部分。步驟2彈出“加載宏”對話框，在“可用加載宏”列表中選中“規(guī)劃求解加載項”復(fù)選框，最后單擊“確定”按鈕，如圖11-46所示。步驟3這時在“數(shù)據(jù)”選項卡下的“分析”組中會增加一個“規(guī)劃求解”命令，說明加載成功，然后在Sheet5工作表中建立如圖11-47所示的規(guī)劃模型步驟4在B4單元格中輸入公式“＝SUM(B2＋B3)”，按Enter鍵計算總進貨量，接著在B6單元格中輸入公式“=(400＋B2/10*5)*B2＋(350＋B3/100*8)*B3”，按Enter鍵計算總利潤，如圖11-48所示。步驟5單擊“數(shù)據(jù)”選項，在“分析”組中選擇“規(guī)劃求解”命令，如圖11-49所

示。步驟6彈出“規(guī)劃求解參數(shù)”對話框，在“設(shè)置目標(biāo)”文本框中輸入“$B$6”，然后在“通過更改可變單元格”文本框中輸入“$B$2:$B$3”，接著選中“最大值”單選按鈕，如圖11-50所示，最后單擊“添加”按鈕。圖11-45單擊“加載項”按鈕圖11-46“加載宏”對話框步驟2彈出“加載宏”對話框，在“可用加載宏”列表中選中“規(guī)步驟7彈出“添加約束”對話框，在“單元格引用位置”文本框中輸入“$B$2”，選擇“<=”約束條件，在“約束值”文本框中輸入“200”，如圖11-51所示，再單擊“添加”按鈕。圖11-47規(guī)劃模型框架圖11-48輸入相關(guān)公式圖11-49選擇“規(guī)劃求解”命令圖11-50“規(guī)劃求解參數(shù)”對話框步驟7彈出“添加約束”對話框，在“單元格引用位置”文本框中步驟8在“添加約束”對話框中為B3單元格添加約束條件，如圖11-52所示，再單擊“添加”按鈕。圖11-51為B2單元格添加約束條件圖11-53為B4單元格添加約束條件圖11-