Excel技巧|「未來三年會賺錢嗎?」老闆突襲的大問題,只要一張Excel就能算出來!

611

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

如何建立模擬模型

Excel技巧

▍汽車銷售門市的案例研究
‧主角:汽車銷售門市的課長,負責門市的營運和收支管理等,過去沒有製作收益預測資料的經驗
‧課題:區域經理要求:「希望各門市主管製作門市未來三年的收益預測資料。」
‧基本數值設定:
營業收入包含銷售汽車的收入和提供售後服務的收入
銷售汽車的收入──第一年3億元,第二年以後每年成長3%
售後服務的收入──預期為穩定的收入,每年維持在2億元
費用包括薪資支出和銷售管理費
薪資支出──員工人數35人×每人平均年薪700萬元
銷售管理費──每年維持在2.3億元

◤先思考收益結構

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

Excel技巧
圖片來源:時報出版提供

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

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

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

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

在此稍微複習一下第一章的內容。以下是希望各位在製作表格時的注意事項:
用顏色區分數字,手動輸入的數字為「藍色」,計算的結果為「黑色」。因此,價值動因的數字為手動輸入的「藍色」,只要調整藍色的數字,就可以模擬營業淨利的變動。

Excel技巧
圖片來源:時報出版提供

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

◤建立收益結構的訣竅

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

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

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

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

思考假定情況

Excel技巧

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

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

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

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

▍一般情況
維持過去的成長趨勢,不過於保守的預估。上市公司發表的業績預測,通常都是一般情況下的預測。
▍樂觀情況
比起一般情況,對收益的預估更積極樂觀。公司內部設定的營業目標,這類的目標數值,通常都是樂觀情況下的預測(多數情況下,公司內部設定的目標數值都高於對外發表的目標)。
▍悲觀情況
假定最壞結果的情況。負責管理公司資金的財務長,就經常會使用這個版本,例如要估算「企業收益減少多少,將無法支付員工薪水?」就會假定悲觀情況。

◤汽車銷售門市的案例

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

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

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

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

Excel技巧
圖片來源:時報出版提供
Excel技巧
圖片來源:時報出版提供
Excel技巧
圖片來源:時報出版提供

用圖表彙總各個情況

Excel技巧
圖片來源:時報出版提供

如圖4-11所示,把這三種情況的淨利彙總成一張表格,並畫出折線圖,就能夠立刻看出在不同情況下收益預測的差異。
這個案例的主角,也就是門市課長,先向區域經理說明這三種情況的假設條件,並出示收益計畫表和比較圖,再進一步說明:

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

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

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

本文節錄自:【外商投資銀行超強Excel製作術】一書

Excel技巧
圖片來源:時報出版提供

【作者簡介】

熊野整

波士頓大學畢業後,進入摩根士丹利證券投資銀行本部,負責領導大型企業併購案或資金調度計畫。離職後在GLOBIS經營研究所取得MBA。之後進入大型上市網際網路企業,負責事業計畫的立案與戰略的推行。

工作上常有同事向他請教Excel問題,基於「想讓人輕鬆學會國際投資銀行Excel技術」的理念,從2013年10月開始,每週末舉辦以個人為對象的Excel講座──「投資銀行教你用Excel 學商業模擬!」,一年內就發展成為超人氣講座,參加人數超過3,000人。目前,除了以個人為對象的講座,還承辦許多企業訓練活動,指導各界商務人士製作收益計畫。

他將課程精華整理成本書《外商投資銀行超強Excel製作術》,歸納出「應該要知道、很多人卻不曉得的Excel重點」,一出版就獲得各界好評。

【譯者簡介】

劉格安

政治大學畢業,現為專職譯者,譯作類型包含商管、醫學、旅遊、生活、歷史和小說等。

 

 

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

本文由時報出版授權,請勿任意轉載
【延伸閱讀】

【誠徵專欄作家】你喜歡寫作嗎?你擅長用文字觸動人心嗎?《董事長碎碎念》邀請對寫作充滿熱情,正經營個人粉絲團或部落格的你,加入專欄作家行列!歡迎來信投稿職場行銷、成功守則、名人勵志主題文章,並於文末附上100字內的作者自介。文章經編輯部潤飾後更有機會刊登在《董事長碎碎念》FB專頁,快點把握機會點我投稿吧!