怪老子ETF線上課程

EXCEL應用-定價方法

日期:2010-06-26

如何定價是每個商務人士必修之課題,這裡提供一個實用的Excel模型,破除傳統的以成本外加的定價模式,而以市場需求為主軸,快速得知最佳利潤之價格。讓價格決策者可以專注於市場之調查,不必煩惱後續之計算。

需求曲線

圖一:需求曲線
需求曲線

每一項產品都有一個市場的需求曲線,也就是在不同價格時,會有不同的數量需求。基本上價格愈貴,需求數量會愈少。定價者必須透過市場調查,得知該項產品之需求曲線。圖一是典型的需求曲線,價格定在p1時,銷售數量會是q1;當價格定在p2時,銷售數量會是q2。

利潤方程式

圖二:p1利潤面積
利潤方程式

當價格於p1時,所得到的利潤會如圖二中的橘色面積。因為當價格為p1時,可銷售數量為q1,成本為C,則利潤的計算方式如下:

利潤 = (p1 - C)*q1

相當於長(p1 - C)及寬q1所圍成的四方型面積。

圖三:p2利潤面積
利潤方程式2

當價格來到p2時,因為p2價格較少,所以數量q2會加大。雖然因為較低價格,每單位利潤會被壓縮,但數量會增加,總利潤如圖三的黃色區域,由(p2-c)及q2所圍成的四方形面積。那麼價格應該訂在p1還是p2,所得到的總利潤會最大?顯然只要比較p1及p2的利潤面積,看哪個面積較大就知道了。然而需求曲線是連續的,沿著價格曲線有無限多點,只要找出當中利潤最大的那一點,就是最佳利潤價格。

Excel求最佳利潤定價

以上只是觀念,在實務上又該如何做呢?最方便的方法就是利用Excel做一個定價模型,使用者只要輸入市場調查資料,以及變動成本,便可立即找出所要答案。這裡提供一個Excel檔案,讀者可以點選下面圖示下載。

Excel檔案下載

圖四:Excel求最佳利潤定價

Excel圖形

第一步:市場調查得知最少三個需求曲線點

利用市場調查方法,找出產品的需求點,最好有四點以上才會較精準。比較正確的做法是極端兩點,也就是最貴及最便宜的兩個點,再外加中間兩點。將市調得到的數值填入A4:B7的儲存格,以及將產品的變動成本填入B1的儲存格。固定成本不必計算,原因是不管定價多少,這成本也不會改變,所以並無影響。

第二步:Excel LOGEST函數求得最貼近之連續需求曲線

只知道四個點是不夠的,必須知道整個需求曲線的方程式,才能夠利用Excel提供之規劃求解,幫我們找出最佳獲利之定價。利用Excel LOGEST函數,立即可找出最貼近市調的指數函數。這需求曲線的方程式如下:

價格 = b*m數量

b的值位於B11儲存格,m的值位於A11儲存格。例如圖四裡的需求曲線方程式為:

 價格 = 2264.5038*0.994661064^數量,圖四右邊的藍色曲線就是以這方程式畫出。

第三步:建構利潤方程式

有了需求曲線方程式就方便了,利用儲存格A15,B15,C15來建構一個利潤方程式。

儲存格 內容 備註
A15 數值 數量
B15 =$B$11*$A$11^A15 價格
C15 =(B15-$B$1)*A15 利潤

第四步:利用目標搜尋求得最佳利潤價格

當利潤方程式建構好了之後,只要調整儲存格A15的數量,就可以立即知道價格(B15)以及利潤有多少(C15)。當然也可以手動找出最大利潤的數量及價格點,但是最方便還是利用Excel的「規劃求解」功能,快速求得答案。注:規劃求解必須安裝增益集才可以使用,請參考附註說明

  1.  點選C15儲存格
  2. 資料->規劃求解,會跳出下圖之視窗,變數儲存格設定A15,按求解就會得到答案

附註

規劃求解(Excel 2007)

下列是節錄微軟的Excel2007關於規劃求解之說明

[規劃求解增益集] 是 Microsoft Office Excel 的增益集 (增益集:將自訂指令或自訂功能新增至 Microsoft Office 的補充程式。)程式,在安裝 Microsoft Office 或 Excel 時就可以取得這個程式。不過,如果要在 Excel 中使用它,必須先行載入。

1.按一下 [Office 按鈕] ,然後按一下 [Excel 選項]。
2.按一下 [增益集],然後選取 [管理] 方塊中的 [Excel 增益集]。
3.按一下 [開始]。
4.選取 [現有的增益集] 方塊中的 [規劃求解增益集] 核取方塊,然後按一下 [確定]。
秘訣 如果 [現有的增益集] 方塊中沒有列出 [規劃求解增益集],請按一下 [瀏覽] 找出此增益集。

如果出現訊息,指出電腦上目前未安裝 [規劃求解增益集],請按一下 [是] 進行安裝。

5.載入 [規劃求解增益集] 之後,[資料] 索引標籤上的 [分析] 群組中就會出現 [規劃求解] 命令。

規劃求解(Excel 2003)

1.在 [工具] 功能表上,按一下 [增益集]。
2.如果要使用的增益集 (增益集:將自訂指令或自訂功能新增至 Microsoft Office 的補充程式。)未出現在 [現有的增益集] 方塊中,請按一下 [瀏覽],然後找出增益集。
3.請在 [現有的增益集] 方塊內,選取想要載入之增益集旁邊的核取方塊,然後按一下 [確定]。
4.如有需要,依循安裝程式上的指示動作。




■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。