高級財務建模最佳實踐：用於智能、無錯誤建模的技巧

執行摘要

簡介：財務模型

財務模型是每個公司財務工具包中不可或缺的一部分。 它們是詳細說明給定企業的歷史財務數據、預測其未來財務業績並評估其風險和回報狀況的電子表格。 財務模型通常圍繞三個會計財務報表構建，即：損益表、資產負債表和現金流量表。 大多數公司的管理至少部分依賴於財務模型的細節、假設和輸出，所有這些對上述公司的戰略和資本決策過程都至關重要。

本文為希望在構建財務模型時遵循專家最佳實踐的新手和中級財務專業人士提供分步指南。 對於高級財務建模者，本文還將展示一系列專家級技巧和技巧，以優化時間、輸出和建模效率。 讓我們開始。

規劃你的模型

與所有復雜的事物一樣，構建財務模型（“模型”）的第一步是仔細佈局藍圖。 在建模過程中進行計劃外、未預料到的結構更改可能會非常耗時、令人困惑且容易出錯，尤其是在模型的適配器與其作者不同的情況下。 這些挑戰很容易在演習開始時通過一些專門的計劃時間來解決。 我建議您的計劃階段如下：

1. 定義模型的最終目標。

明確定義模型的目的是確定其最佳佈局、結構和最終輸出的關鍵。 作為此過程的一部分，請花時間確保您的模型的主要利益相關者在開始構建之前簽署您的藍圖和流程設計。 這使他們有機會表達任何最終的偏好或意圖，從而避免任何“範圍蔓延”（行業用語）或痛苦的重定向。

2. 了解構建模型及其使用壽命的時間表。

雖然次要於模型的最終目標，但了解構建模型的時間表以及模型將使用多長時間也是確定建模練習方法的重要輸入。 長期和長期（使用壽命）模型通常是從頭開始定制的，包括大量的操作細節、靈活性和靈敏度能力。 對於更直接、更短持續時間的運營或資本項目模型，建模人員通常會使用預製模板來最大限度地提高施工速度，同時最大限度地減少錯誤。 此外，模型模板也往往更熟悉，因此更容易被組織內的不同利益相關者使用/操作。

3. 確定“細節”與“可重用性”之間的最佳權衡。

在決定所需的詳細程度和模型可重用性之間的最佳權衡時（即，模型是打算為多種事務類型/目的重新設計，還是僅僅為這種一次性練習而設計），在我職業生涯的大部分時間裡，用於決定一個人的模型選擇/方法的有用框架如下：

隨著藍圖/規劃階段的完成和關鍵決策的確定，我們現在可以進入下一階段的建模。

構建模型

在這個時刻，我們準備打開 Excel 並開始考慮結構化。 在可能的最高級別上，每個模型都可以/應該分為三個部分：(a) 輸入/驅動因素，(b) 計算（預計財務報表），和 (c) 輸出。 將這些部分分離得越好，就越容易審核和修改模型，同時最大限度地減少錯誤並及時優化。

對於我構建的幾乎每個模型，我都遵循相同的結構方法； 我和我各自的利益相關者一直認為這種方法實用、易於理解且最終有用。 其部分如下：

1. 封面（選項卡）：項目代號、模型意圖描述、作者聯繫信息以及任何適用的免責聲明。
2. 驅動因素選項卡：輸入和假設。
3. 模型選項卡：計算（即三個財務報表的預測和計算）。
4. 輸出選項卡：對模型最重要亮點的簡潔、簡潔的總結。
5. 敏感度選項卡：管理層在過渡到決策過程時​​將依賴的場景、敏感度和數據結果的範圍。

我會為你分解這些部分，一次一個。 如下：

封面

封面是與您的作品的第一個接觸點。 雖然它是最簡單的構建，但如果做得好，它會給人留下很好的第一印象，並清楚地解釋即將發生的事情。 一個簡單的指導性封面通常是最好的方法，通常包括以下部分：

1. 模型名稱：不言自明。
2. 模型的目的：描述其預期用途的段落
3. 模型索引：一個簡要表，詳細說明每個選項卡的描述和用途。 本節對非金融運營商特別有幫助，通過突出顯示他們需要使用哪些選項卡進行輸入、在決策過程中需要關注哪些輸出以及他們應該使用哪些複雜的計算選項卡，幫助他們“消化”模型的結構和流程保持不變。
4. 模型版本歷史記錄：花幾秒鐘時間按日期鍵入對模型所做的關鍵更改始終可以節省時間，尤其是在您需要回溯和反轉/修改更改時。 對於您在未來時期可能用作模板的複雜模型和模型尤其如此。
5. 作者的聯繫方式：不言自明
6. 適用的法律免責聲明（如有，由您的法律顧問提供） ：不言自明

請注意：我建議始終將封面鎖定給任何未經作者明確授權進行更改的所有人。

駕駛員選項卡：輸入和假設

緊接著模型的封面，必須來到驅動程序（輸入）選項卡。 您必須確保此選項卡清晰、簡潔且易於理解，因為這是非金融運營商最常操作的選項卡。 我通常建議在輸入選項卡中實現兩個輸入部分，一個用於靜態輸入，另一個用於動態輸入。 我所說的靜態輸入是指不隨時間變化的輸入，例如假設的“發電廠規模”或“公司的初始債務餘額”； 我所說的動態輸入是指隨時間變化的輸入（例如，逐月或逐年），例如“通貨膨脹”假設、“債務成本”或“收入增長”假設。

示例驅動程序和假設選項卡示例

在上述靜態與動態輸入部分中，我建議您還清楚地將數據分為兩種：（1）硬編碼的數字，無論假設情況如何都不會改變，以及（b）敏感參數，將驅動不同的假設場景，並最終驅動您的敏感度表。 但是請注意，您永遠不會完全知道哪些參數將構成靈敏度參數，而您直到項目的最後階段才知道哪些參數。 有關靈敏度建模的更多信息，請參閱以下文章。

模型選項卡：詳細計算和操作構建

此選項卡代表模型的核心，所有輸入、假設和情景共同作用，將公司的財務業績預測到其外部年份。 在此選項卡之外，還將運行各種假設驅動的情景以及將在最終戰略決策之前進行的評估部分。

示例模型選項卡示例

場景和敏感度選項卡

授權的第三方模型運營商將經常使用“場景和敏感性”選項卡，即使只是為了選擇他們選擇的預編程場景。 出於這個原因，您應該直觀地構建場景，保護實際場景免受外部編輯，並構建足夠多的敏感度，以便少數預先編程的場景足以在敏感度表（下面的示例）後產生可能的結果的廣泛視圖也建。

供您參考，我在整個職業生涯中所依賴的場景格式結構如下，僅作為一種示例：

關於上圖的幾點說明：

1. 模型用戶應該只能編輯它，因為他們將在其中選擇場景編號。 該數字是指電子表格右側顯示的場景之一。 然後，用戶將在第一列中呈現所選場景（在本例中為第 6 號）。 這是模型中引用的場景和敏感性電子表格的唯一列。
2. 在此處添加幾個描述字段，以有效總結所選場景所代表的內容。
3. 我總是覺得它非常有用，特別是如果其他人將使用該模型，添加一個指定每個輸入單元的列。
4. 此列拉入所選場景（在本例中為第 6 號）的槓桿統計/字段，它們都顯示在右側（藍色）。 驅動它所需的公式是一個偏移函數，即“=OFFSET（在上面以紅色突出顯示的第一個場景的左邊插入空單元格，選擇/突出顯示場景的單元格）。” 請注意，兩個單元格之間有一個空格，因此兩個逗號 (,,) 不是拼寫錯誤。
5. 將您的假設分組到宏觀類別和子類別中。 這將幫助您（建模者）和您的用戶清楚地了解模型選擇的場景。

樣品靈敏度表示例

輸出選項卡

輸出選項卡是模型操作員最常使用的選項卡。 多年來，我發現自己傾向於至少三個用於中復雜模型的輸出選項卡：

1. 財務輸出選項卡：這是模型選項卡中詳細財務數據的簡短摘要。 它們通常以年度為基礎（即使模型可能是季度）。 此輸出應介於 50 到 150 行之間，並應顯示計算選項卡中的所有關鍵行項目。 請務必提供足夠的詳細信息，以確保用戶不會在此選項卡和各種計算選項卡之間切換。 另請注意，作為最佳實踐，任何輸出選項卡都不應重新執行任何計算，並且此信息應僅包含直接鏈接。
2. 執行摘要選項卡：此選項卡非常標準，通常顯示圖形、圖表和表格的組合，盡可能簡單易懂地說明高管和董事會成員導航其所需的各種趨勢、分析和關鍵摘要統計數據關鍵決策。
3. 特定輸出選項卡：此選項卡包含特定輸出，通常由投資備忘錄模板、投資委員會演示文稿或高管和董事會成員提出的要求決定，以達到他們的決策點。

模型輸出選項卡示例，包括表格、圖表和圖形

至此，模型的構建階段正式完成。 我們可能會將注意力轉向我在文章開頭提到的一些專家級建模最佳實踐。 讓我們從格式化開始。

格式化你的模型

首先，重要的是要注意每個公司/集團可能有自己的偏好或內部做法。 因此，在構建時，重要的是首先檢查並遵守您各自公司規定的任何格式。 然而，在缺乏公司特定實踐的情況下，以下內容詳細介紹了華爾街用於格式化模型的通用語言。

財務建模的第一個也是最簡單的格式化方法是使用一致且可識別的配色方案來表示不同類型的單元格和數據。 如下：

藍色= 輸入或任何硬編碼數據，例如歷史值、假設和驅動因素。

黑色= 源自同一工作表的公式、計算或參考。

綠色= 公式、計算和對其他工作表的引用（請注意，儘管某些模型完全跳過此步驟並為這些單元格使用黑色）。

紫色= 到其他 Excel 文件的鏈接、輸入、公式、引用或計算（再次注意，某些模型完全跳過此步驟，並且這些單元格也使用黑色）。

紅色= 要修復的錯誤。

格式正確（顏色編碼）的財務摘要示例

請注意，沒有內置的自動化功能可以根據上述通用顏色編碼標準對您的 Excel 電子表格進行顏色編碼。 相反，您可以設計自己的宏來實現這些結果，然後創建快捷方式組合來自動對您的工作進行顏色編碼。

在我最近的某個時候，我從一位同事（今天我感謝他）那裡收到了以下宏（包括詳細說明），這為我節省了幾個小時的體力勞動。 如果可以的話，我想分享它們。

宏創建說明（適用於 Mac 和 PC 版本的 Excel）：

1. 同時按 Alt + W + M + R 來命名並開始錄製您的宏。
2. 同時按 F5（“跳到單元格”）然後按 Alt + S 以到達“轉到特殊”菜單。
3. 點擊“O”選擇常量，點擊“X”取消選中文本。
   
4. 現在同時點擊 Alt + H + FC（或 Ctrl + 1），並為這些常量選擇藍色字體顏色。
5. 按 Esc。
6. 現在做同樣的事情，從 F5 開始，但選擇公式 (F) 而不是常量，然後按“X”取消選中文本。
7. 現在點擊 Alt + H + FC（或 Ctrl + 1）並為這些常量選擇黑色字體顏色。
8. 使用 Alt + W + M + R 或 Alt + T + M + R 停止錄製宏。

其他工作簿的鏈接

查找其他工作簿和工作表的鏈接很棘手，您很可能必須使用 VBA 才能使其正常工作。 這是基本思想：搜索符號“！”的存在。 在工作簿中包含公式的每個單元格中，然後將字體顏色更改為綠色。 您將需要在 VBA 編輯器中對其進行修改，並使其成為遍歷所有“！”實例的for each循環。 您找到，然後更改其中每一個的字體顏色。

請注意，此快捷方式仍然無法 100% 工作，因為某些公式會引用其他工作表中的單元格，而不會直接鏈接到它們。 幸運的是，綠色單元格比黑色或藍色單元格少，因此上述方法在大多數模型中都相當有效（您可以在出現或遇到其他工作表時手動將其餘鏈接有機地格式化）。

審計模型的最佳實踐

在建模時，我鼓勵您始終牢記這個問題：“我是否讓這個模型易於審核？” 因為對於執行的每項任務、創建的公式和建立的鏈接，總會有一種更快、更“骯髒”（用行業術語來說）的方式來完成這項工作。 這樣的黑客和技巧，無論當時看起來多麼聰明，尤其是在一段時間之後，總是會被遺忘，並會導致難以追踪的錯誤。 牢記第三方審閱者將指導您完成整個過程，並幫助您在關鍵時刻做出正確的決定。

以下是有關如何以審核員心態進行構建的一系列最佳實踐。 如下：

1. 一排一式

每行應該只有一個公式，這意味著在任何給定行的第一個單元格中使用的任何公式都應該是在整個行中統一應用的相同公式。 用戶應該通過查看每行的第一個單元格來了解模型的結構，因為他們垂直地沿著模型向下移動。

雖然這在原則上很簡單，但它經常被違反以進一步強調。 當電子表格在“歷史財務”組列和“外部年度預測”之間拆分時，通常會發生一個常見的情況（參見上圖，標題為“格式良好（顏色編碼）財務摘要的示例”，作為參考）。

解決這些實例的一種簡單方法是使用位於電子表格頂部的標誌（例如，1/0、 TRUE / FALSE ），然後使用IF語句通過模型的主體進行引用。 這在工作中的一個簡單說明如下：

Excel 建模中“標誌的使用”示例

2. 公式中沒有嵌入硬編碼的數字

永遠不要使用嵌入在公式中的硬編碼數字，因為如果用戶對模型不太熟悉，就很難發現它們。 相反，要清楚地突出顯示輸入/硬代碼並將其與公式分開； 更好的是，收集所有輸入/硬代碼（視情況而定）並將它們聚合在同一個選項卡中。 隨後讓您的公式根據需要從所需的單元格和相應的選項卡中提取/引用它們。

3. 簡單總是更好

避免複雜的公式總是更好。 相反，將您的配方分解成易於消化的步驟。 這種方法通常會創建更多行，而不是一個看似整齊的行，從而導致更大的電子表格； 但第三方更容易跟踪和審計。

4.始終遵守您的標誌約定

您應該在零時決定您的簽名約定/密鑰是什麼。 舉例來說，在模型的設計階段問自己：“成本、費用、扣減、折舊、資本支出等將呈現為負數還是正數？” 我個人的偏好是始終將成本表示為負數，原因有兩個：（a）總數將始終是直和，您將最大限度地減少用戶錯誤，以及（b）僅使用符號更容易發現錯誤。

5.避免命名你的單元格，而是依賴Excel的網格邏輯

在可能的情況下，我強烈建議避免命名您的單元格，因為以後很難找到所述命名單元格的源輸入（例如，“Inflation”）。 相反，我建議您在公式中依賴 Excel 的網格約定（例如，如果引用位於不同的選項卡或工作簿中，則只需鏈接到單元格 C4 或位置[Tab Name]l'!G21 ）。

6. 永遠不要在多個位置使用相同的輸入

簡單而透明地組織您的輸入。 我建議您將所有輸入合併到幾個驅動程序選項卡中，並在整個電子表格中從它們的奇異原點引用它們。

7.避免鏈接文件

避免鏈接到其他文件。 最好從不同的文件輸入您需要的相關數據作為硬編碼輸入，然後根據需要手動更新。 眾所周知，交叉鏈接會使較大的 Excel 模型崩潰或更新不一致，從而產生難以跟踪的錯誤。

8. 不要隱藏表格或行

在較長的電子表格中，“分組”行/列而不是“隱藏”它們。

9. 更少、更大的標籤比多個更小的標籤更好

這種做法是 100% 以經驗為基礎的。 與跨多個選項卡或更糟糕的是，跨鏈接的多個電子表格相比，跟踪和審計跨一個大型連續電子表格的數據更容易。

10. 通過位於一個選項卡中的“聚合錯誤檢查”在整個模型中創建檢查

檢查是快速檢查模型完整性的最簡單方法。 “檢查”涵蓋了從確保應該與實際掛鉤的總計到確保一個人的資產負債表實際平衡的所有內容。 我通常在每個電子表格的頂部或底部建立一些檢查，然後將它們合併到一個單獨的“檢查選項卡”中。 這確保了很容易在模型中找到錯誤，然後跟踪錯誤的來源。

資產負債表“支票”樣本

請注意，僅僅依靠檢查來驗證模型的完整性絕不是一個好主意，因為檢查通常是非常高級的。 但這是一個很好的起點。

開拓 - 高級用戶：Excel 提示

本節為我們的高級用戶介紹了一些非常有效的 Excel 最佳實踐。 這些可能需要一些調整，但以後應該可以節省幾個小時的工作並且實施起來相對簡單。 簡而言之，它們如下，簡明扼要的要點：

1. 盡可能多地使用鍵盤快捷鍵。 互聯網上有幾個適合不同需求的 Excel 鍵盤快捷鍵文件。 我將在這裡引用一對：
   • 建模關鍵備忘單
   • 綜合 Excel 提示列表
2. 使用 F5（“轉到特殊”）快速找到所有硬編碼的數字或公式。
3. 使用 Trace Precedents 和 Trace Dependents 來審核模型。
4. 使用XNPV和XIRR允許將自定義日期應用於現金流，在進行回報分析的途中； 這與 Excel 的NPV和IRR函數相反，後者隱含地假設計算的時間間隔是等距的。
5. 在VLOOKUP函數上使用INDEX MATCH函數來查找大型電子表格中的信息。
6. VLOOKUP幾乎總是優於IF語句； 適應它。
7. 養成在公式語法中包含IFERROR的習慣。
8. 結合使用日期函數、 EOMONTH和IF語句來使日期動態化。
9. 在展示或分享財務模型時移除網格線； 它使輸出文檔更乾淨、更優美。

愛它還是恨它…

無論喜歡還是討厭，Excel 在企業財務、分析和數據驅動的決策制定方面都是無所不知、無所不在、無所不能的。 不管你信不信，它不一定是令人生畏或痛苦的，即使對於新手或外行也是如此。 就像生活中的大多數事情一樣，練習、一致性和對細節的關注（在 Excel 的情況下，快捷方式）將使您獲得最大的成功。

一旦你熟悉了這個應用程序，你會發現它是一個強大的生產力和數字故事講述工具，即使在你的個人生活中，你也很少能夠在沒有它的情況下使用它。 隨著您在 Excel 流利程度的各個階段取得進展，我祝您一切順利，並鼓勵您將本文作為您經常參考的實用指南。