alexa
置頂

「未來三年會賺錢嗎?」老闆最關心的問題,一張Excel就能算出來!

文 / 一流人    
2020-10-09
瀏覽數 23,850+
「未來三年會賺錢嗎?」老闆最關心的問題,一張Excel就能算出來!
僅為情境配圖。取自pexels
分享 Line分享分享 複製連結

編按:
一張excel表,到底要涵蓋哪些數據,才能精算出成本與收益?老闆最關心的收益與支出,你該如何分析與拆解?(本文摘自《外商投資銀行超強Excel製作術》一書,以下為摘文。)

如何建立模擬模型

以下將根據設定好的題目,示範如何建立模擬模型。

汽車銷售門市的案例研究

●主角:汽車銷售門市的課長,負責門市的營運和收支管理等,過去沒有製作收益預測資料的經驗

●課題:區域經理要求:「希望各門市主管製作門市未來三年的收益預測資料。」

●基本數值設定:

營業收入包含銷售汽車的收入和提供售後服務的收入

銷售汽車的收入——第一年3億元,第二年以後每年成長3%

售後服務的收入——預期為穩定的收入,每年維持在2億元

費用包括薪資支出和銷售管理費

薪資支出——員工人數35人×每人平均年薪700萬元

銷售管理費——每年維持在2.3億元

1. 先思考收益結構

接下來,我們就試著站在主角,也就是門市課長的立場,開始建立收益預測的模型吧。不過話說回來,突然被要求用Excel做商業模擬,要從哪裡開始呢?即使打開了Excel,應該也有很多人就這麼僵在電腦前,不知道該如何是好。

思考收益結構。圖/思考收益結構。

首先,我們要做的第一件事,就是畫出模型的「設計圖」。把這個商業模式創造收益的要素,彙總成樹狀的結構圖。

汽車銷售的收益結構如上圖所示,只要知道右方藍色底色的數值,就可以由右向左,一層層算出每一個項目,最後算出淨利。換句話說,要模擬收益數字,就要試著更動藍色部分的數字。換言之,藍色部分的數字是創造營業淨利的要素,又稱為「價值動因」。

要建立模擬模型,必須先像這樣明確區分出哪些部分是價值動因(可更動的數字),哪些部分不是價值動因(不需更動的數字)。

把這些項目整理成收益結構,再利用Excel呈現,就會得到如下圖的表格。

以下是希望各位在製作表格時的注意事項:

用顏色區分數字,手動輸入的數字為「藍色」,計算的結果為「黑色」。因此,價值動因的數字為手動輸入的「藍色」,只要調整藍色的數字,就可以模擬營業淨利的變動。

把收益結構製作成Excel表格。圖/把收益結構製作成Excel表格。

項目名稱的部分,要把細項向右縮排。這樣一來,收益結構便一目瞭然,例如,「營業收入」是由「汽車銷售」和「售後服務」構成。

2. 建立收益結構的訣竅

思考收益結構有兩個重點,一是「先大致完成架構」,二是「盡量讓數字連動」。

第一項「先大致完成架構」,意思就是一開始的項目最好不要太多。以這個案例來說,就是不要在一開始就把銷售汽車的收入再細分成A車、B車、C車的銷貨收入。

為什麼要這麼做呢?因為Excel的作業非常繁複,常常做著做著,耐心就被消磨掉,「算不下去了,我放棄!」為了避免這樣的狀況發生,一開始最好簡化收益結構的項目,先大致完成架構,才能確保最後能夠順利完成收益結構圖。如果無論如何都必須細分項目的話,也請先整理出簡單的收益結構,之後再慢慢細分下去。

第二項重點「盡量讓數字連動」,就是要讓收益結構的項目一層一層緊密連動。以這個案例來說,薪資支出=員工人數×平均每人薪資支出,因此,薪資支出就是與員工人數互相連動的項目。這樣的連動能夠精細到什麼程度,對於模擬的準確度是很重要的關鍵。

思考假定情況

我在幫企業做員工訓練時,發現有不少企業或事業部門只會做一種版本的收益計畫,這其實沒什麼意義因為光靠一種版本並不能夠理解經營的風險,唯有假定各種情況,改變價值動因,才能夠預測當計畫順利時,可以創造多少利益;在最惡劣的情況下,又必須承擔多少損失。

再回到汽車銷售門市的案例,區域經理管轄區域內多處營業據點,無法一一監督每一家門市,難以預期各別的風險,因此,門市的負責人必須把所有可能面臨的風險都向上呈報,讓上司了解在各種假定情況下,合理預期的收益會介在什麼「範圍」。

1. 假定情況至少要設定三種版本

關於假定情況,至少要設定「一般情況」、「樂觀情況」和「悲觀情況」三種,每一種情況大致如下:

(1) 一般情況

維持過去的成長趨勢,不過於保守的預估。上市公司發表的業績預測,通常都是一般情況下的預測。

(2) 樂觀情況

比起一般情況,對收益的預估更積極樂觀。公司內部設定的營業目標,這類的目標數值,通常都是樂觀情況下的預測(多數情況下,公司內部設定的目標數值都高於對外發表的目標)。

(3) 悲觀情況

假定最壞結果的情況。負責管理公司資金的財務長,就經常會使用這個版本,例如要估算「企業收益減少多少,將無法支付員工薪水?」就會假定悲觀情況。

2. 汽車銷售門市的案例

在汽車銷售門市的案例中,這三種假定情況可以這麼設定:

(1) 一般情況

以往汽車銷售收入成長率為3%,未來也將持續這個水平。至於其他方面的數字(售後服務收入和各項費用等),由於沒有會導致大幅變動的可預期因素,因此維持現在的數字。

(2) 樂觀情況

受到近年來景氣復甦的影響,預期第二年和第三年汽車銷售量將會成長。根據這家門市過去的銷售紀錄,景氣好的時候成長率為5%,因此適用5%的成長率。此外,由於公司導入新的作業系統,未來將可減少作業人員。雖然每年的銷售管理費會增加1,000萬元,但是在新的作業系統下,不需聘用新進人員,因此每年會減少一名員工。

(3) 悲觀情況

近幾年來景氣確實有復甦的跡象,但是年輕人對汽車的依賴程度顯著降低,未來汽車銷售量可能下滑。根據某智庫的調查報告顯示,未來新車銷售量,一年最多可能減少5%,因此假定在悲觀情況下,汽車銷售收入為一年5%的負成長。為因應銷售量下滑,將縮小業務人員編制,每年將減少兩名員工,以縮減人事費用。

思考假定情況。圖/思考假定情況。

一般情況。圖/一般情況。

樂觀情況。圖/樂觀情況。

悲觀情況。圖/悲觀情況。

3. 用圖表彙總各個情況

比較各種情況下的結果。圖/比較各種情況下的結果。

如上圖所示,把這三種情況的淨利彙總成一張表格,並畫出折線圖,就能夠立刻看出在不同情況下收益預測的差異。

這個案例的主角,也就是門市課長,先向區域經理說明這三種情況的假設條件,並出示收益計畫表和比較圖,再進一步說明:

「關於門市三年後的收益預測,在一般情況下,延續過去的成長趨勢,淨利將達到4,300萬元。如果因為景氣復甦,汽車銷售數量增加,最多有可能提高到6,000萬元,我們也會以這為目標,盡全力達成。相反的,營業收入也有可能減少,最近年輕人對汽車的依賴下降,也會對門市造成負面影響,在最糟糕的情況下,汽車銷售收入也有可能呈現5%的負成長。不過,屆時也會縮小業務人員的編制,淨利可望維持在2,400萬元,不會和現在落差太大。」

區域經理聽到這樣的分析,會給予門市課長高度的評價吧,「能夠根據各種情況,考量未來的收益計畫,還具有高度的風險管理意識,懂得在最惡劣的情況下,也要確保收益的道理。」

收益會在許多因素的影響下大幅變動,正因為如此,才要根據各種假定情況製作收益計畫,從多種角度預測收益。不管是樂觀的角度也好,悲觀的角度也好,讓團隊共享這些觀點,這是很重要的事。

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

數位專題
從青年培力到永續解方,你我都要修的企業倫理課
分享 Line分享分享 複製連結
職場企業經營與管理