alexa
置頂

讓Excel幫你找出公司競爭力!削價競爭不用見骨,這樣算剛剛好

文 / 一流人    
2020-10-24
瀏覽數 12,500+
讓Excel幫你找出公司競爭力!削價競爭不用見骨,這樣算剛剛好
僅為情境配圖。取自pixabay
分享 Line分享分享 複製連結

假設上司這樣徵詢你的意見:「我們的商品銷售價格是1,000圓,但競爭對手一直在降價,或許我們也必須降價才行。不過我們的商品大概降價到多少,利潤會變成零呢?」降到利潤變成零的那一點,就稱作損益兩平點。(本文摘自《外商投資銀行超強Excel製作術》一書,以下為摘文。)

試著把下個月的價格(儲存格G7)從1,000圓改成800圓,則下個月的利潤(儲存格G12)會變成115,000圓。

把下個月的價格從1,000圓調降為800圓。圖/把下個月的價格從1,000圓調降為800圓。

想要讓利潤歸零,似乎還可以再降價。乾脆一口氣把價格設定為500圓好了,這樣一來,下個月的利潤就變成了負200,000圓的大虧損。

再把價格調降至500圓。圖/再把價格調降至500圓。

像這樣憑感覺改變價格,也很難讓利潤剛好等於零。這種收益模型的問題點就是「用調整價值動因的方式來改變利潤很簡單,但要配合目標利潤調整價值動因卻很困難」。

想求損益兩平點的時候,可以使用Excel的「目標搜尋」功能。目標搜尋功能簡單來說就是「倒推」。這是一種很方便的功能,只要設定好利潤的目標值,就能倒推回來算出達成目標值所需的價格。

目標搜尋功能只要點選「資料」→「模擬分析」→「目標搜尋」,就能叫出設定畫面。

「資料」→「模擬分析」→「目標搜尋」。圖/「資料」→「模擬分析」→「目標搜尋」。

在目標搜尋中,指定以下3個條件。

1. 目標儲存格

點選並輸入這次的目標,也就是求下個月利潤的計算式的儲存格(儲存格G12)。

2. 目標值

由於這次的目標是讓利潤變成零,因此輸入「0」。

3. 變數儲存格

由於這次是為了讓下個月的利潤變零,所以要改變「下月的價格」,因此點選該儲存格(儲存格G7)。

輸入目標儲存格、目標值、變數儲存格再按確定。圖/輸入目標儲存格、目標值、變數儲存格再按確定。

最後按下「確定」後,下個月的價格數字就會如下圖所示,變成690圓。換句話說,在價格變成690圓時,利潤剛好會等於零。

結果利潤變成零,價格變成690圓。圖/結果利潤變成零,價格變成690圓。

自己動手更改價格很難找到「690」這個答案,但只要使用目標搜尋的功能,即可順利找到損益兩平點。

目標搜尋不僅可以用來尋找損益兩平點,也可以用在目標利潤的調整上。舉例而言,當上司看到這個收益模型,問說:

我希望下個月的利潤可以從325,000圓盡量提高到400,000圓……這樣平均材料成本要下降多少才能達成? 

此時也可以用目標搜尋功能來計算。

想讓下個月的利潤(儲存格G12)從325,000圓→400,000圓。圖/想讓下個月的利潤(儲存格G12)從325,000圓→400,000圓。

此時,在目標搜尋中要設定的條件是以下3項:

1. 目標儲存格:下個月的利潤(儲存格G12)

2. 目標值:400,000

3. 變數儲存格:下個月的平均材料成本(儲存格G10)

如果把利潤(儲存格G12)的目標值設為400,000圓......圖/如果把利潤(儲存格G12)的目標值設為400,000圓......

從結果可知,只要能夠把材料成本從500圓壓低至429圓,即可達成400,000圓的目標利潤。

平均材料成本變成429圓。圖/平均材料成本變成429圓。

在規劃事業計畫的過程中,經常會碰到「嗯,營收比想像中的少,不知道能不能增加銷售量來提高營收?」這種需要調整的情況。在這種情況下,目標搜尋功能即可派上用場。

在使用目標搜尋功能時,有一點要注意的是,變數儲存格(此例中為下個月的平均材料成本)必須是手動輸入的數字。如果這個儲存格有參照到其他儲存格或包含計算式的話,就會像下圖這樣,出現「儲存格必須包含數值」的錯誤訊息。

「變數儲存格」若包含計算式(此例中為G9),會出現錯誤訊息。圖/「變數儲存格」若包含計算式(此例中為G9),會出現錯誤訊息。

《外商投資銀行超強Excel製作術:不只教你Excel技巧,學會用數字思考、表達、說服,做出最好的商業決策!》一書,熊野整著,劉格安譯,時報出版。圖/《外商投資銀行超強Excel製作術:不只教你Excel技巧,學會用數字思考、表達、說服,做出最好的商業決策!》一書,熊野整著,劉格安譯,時報出版。

數位專題
加入返鄉創業的行列
分享 Line分享分享 複製連結
職場企業經營與管理
您可能會喜歡