PCMA精選:如何用Excel建立預測模型,進行財務預測

2020-12-09 騰訊網

引言

很多企業做經營計劃、財務預算都是「三拍」：

年初拍腦袋：不懂科學的預測方法，也沒有使用預測工具的意識，對來年的經營指標全靠領導拍腦袋決定。

年中拍胸脯：半年度對預算執行情況進行檢視匯報時，哪怕經營指標完成得很不好，但還是拍胸脯「保證完成年度預算」。

年底拍屁股：年度預算嚴重脫離實際，根本不可能完成，到了年底完成不了既定預算，只好拍屁股走人。

那怎樣才能預測工具進行科學地預測呢？《管理會計應用指引第802號--管理會計信息系統》第二十一條告訴了我們相關方法：

企業應藉助適用的預測方法（如：趨勢預測、平滑預測、回歸預測等）建立預測模型，輔助企業制定預算目標，依據預算管理體系，自動分解預算目標，輔助預算的審批流程，自動匯總預算。最終輸出結果應為各個責任中心的預算方案等。

本文介紹如何利用Excel相關功能來科學地預測經營指標。

一、按時間序列進行趨勢預測

企業的經營指標都是按時間的先後順序排列組成的序列。該序列體現了該經營指標的發展方向和發展趨勢，只要經營情況沒有發生重大變化，該指標仍將保持原來的的發展趨勢和速度，這樣我們就可以利用歷史數據來估算該指標的未來某時間段的值。

案例1：

逸凡諮詢有限公司2011年到2019年的營業收入如下表：

假定其他條件不變，公司保持現有的增長速度，請根據這些歷史數據，估計2020-2022年的收入，以做為確定三年經營計劃的參考數據。

在Excel偷懶的技術微信公眾號2月3日的文章中已經介紹過使用折線圖的趨勢線來確定：

好消息，新型肺炎疫情確診人數這一天開始將逐漸減少！！

按照文章中介紹的方法，可以得到趨勢線公式。

其趨勢線的公式為：

y=17.333x+372.78

利用此公式可以計算未來三年的營業收入。

注意：

1、公式中的X是時間序列1、2、3……，而不能使用年份數字2011、2012、2013。

2、之所以使用折線圖來取得趨勢線公式，是因為折線圖默認將年份的2011、2012、2013分別視同為1、2、3。更規範的做法是使用散點圖，並添加趨勢線公式。

插入圖表並添加趨勢線，以取得趨勢線公式，這種方法相對比較麻煩一點。我們可以直接使用TREND函數來計算預測值，公式：

=TREND(D3:D11,B3:B11,[@時間序列])

上圖中D12單元格的公式相當於

=TREND(D3:D11,B3:B11,B12)

TREND函數語法：

=TREND（已知的因變量Y值列表，已知的自變量X值列表，新的自變量X值，是否強制截距等於0）

肯定有朋友會問趨勢線公式中的17.333和372.78是怎麼計算得來的呢？

y=17.333x+372.78

實際上，該趨勢線就是回歸直線，用回歸方程來計算的

如果用上面的公式計算過程很複雜很麻煩，但在Excel中一切都變得很簡單，用一個公式就搞定了，比如計算回歸係數b：

=INDEX(LINEST(D3:D11,B3:B11,,0),1)

LINEST函數的詳細用法，我們下篇文章再介紹。敬請關注。

二、根據相關指標來預測（因果分析法）

在日常財務管理中，除了根據經營指標的歷史數據來預測未來的值，有時候還需要研究幾個指標的關聯關係，這樣就可以根據已知指標的值來預測另一個指標的估算值。比如，車間設備的修理費用與機器工作時間存在定的關聯關係，使用時間越多，修理費越多。那麼，我們在編制預算時就可以根據機器的開機時間來預測機器的修理費。

案例2：

逸凡精工設備公司2019年車間的機器工時及修理費如下表如示，根據2020年的經營計劃，2020年4月、5月、6月機器生產工時預計為450、420、512小時，請使用線性回歸法估算各月的修理費。

與時間序列一樣，我們使用散點圖添加趨勢線，得到趨勢線公式：

當然，也可以使用折線圖添加趨勢線，顯示趨勢公式，來得到趨勢線的公式，只是需要注意的是，前面已經介紹過，折線圖默認將橫坐標的數值視同為文本標籤，等同於1、2、3，所以，這裡的圖表需要將橫坐標設置為日期坐標軸。否則，圖表默認將橫軸的分類標籤視為文本，計算出的趨勢線公式是錯的。

一樣的，也可以TREND函數以及LINEST來計算回歸係數。

前面介紹的二個案例都是使用的一元線性回歸模式，在實際工作中還有更複雜的情況，比如一個指標跟多個指標相關聯，要根據多個指標的已知值來估算另一指標的值。

案例3：

比如一個產品的銷量主要受兩個因素影響：居民的購買力、廣告費。已知近五年的居民的購買力、廣告費、銷量情況，2020年預計要投入廣告費和居民購買力為已知數，現要估算2020年的銷量。

可以使用TREND函數編制下面的公式：

=TREND(E3:E7,C3:D7,C8:D8)

從上面的公式可以看出，TREND函數第二個參數「已知的自變量X值列表」可以是多列數據。

TREND函數還可以用於更複雜的預測和估算。

案例4：

房產的價值受多個因素影響，假設某地的房產主要受附近的商業配套、學校、使用年限等因素影響，現收集了該市類似樓盤的一些數據，如下圖表A2：F12單元格區域，B15：E30為本樓盤已知的數據，現要根據這些數據來測算某樓盤的價值：

在F15單元格輸入公式：

=TREND(F2:F12,B2:E12,B15:E15)

相關焦點

  • 星巴克財務預測估值模型-2020.4
    科創板估值知識庫涵蓋估值模型（帶連結的excel）、估值培訓課程（視頻+課件）、經典估值書籍、估值報告案例、估值標準等六大子庫，我們致力搜集各行業估值測算模型，做你的估值好助手。>美團財務預測估值模型資產評估機構收益法評估模型東方財富財務預測估值模型順豐控股財務預測估值模型德勤諮詢-企業併購財務預測估值模版寧德時代財務預測估值模型
  • 持續教育 | Excel工具創建季節性銷售的滾動預測模型
    對於銷售基本穩定的行業，銷售預測可以採用回歸分析、趨勢分析、移動加權和平滑指數等方面進行預測；但對於銷售波動比較大的行業，銷售預測的要求比較高，有一定的難度。如果對本企業銷售淡旺季的預測準確度比較高，可以更科學地指導公司下個年度經營活動的開展，提前做好人員配置、收支的安排等等……本次課程通過「創建季節性銷售的滾動預測模型」，提供兩種預測方法，模擬 「季節性銷售指數」（以下簡稱「季節指數」），對存在一年中不同時段銷售波動比較大的行業進行更準確地銷售預測，為企業的經營決策提供依據。
  • excel數據分析技巧:預測未來,為圖表添加趨勢線
    對於exceler而言，預測數據走勢，如銷量趨勢預測、股指走勢預測等是日常處理、分析數據工作中的一部分。面對這類問題，我們可以使用Excel圖表中的趨勢線對數據未來走勢進行預測，幫助我們更加直觀地了解數據變化的趨勢。下面我們就和春風一起來學習excel中的趨勢線吧！學習更多技巧，請收藏部落窩教育excel圖文教程。
  • 卷積神經網絡預測模型:提前24個月預測ENSO
    但目前業務部門一般的ENSO 預測時效只有6~9個月，最長為1年左右，這樣的預報時效無法滿足現實需求。較長時間的ENSO預測也是國際上的難點，如何延長ENSO的預測時效，成為眾多科學家迫切想要解決的問題。羅京佳及其研究團隊目前正在攻克這個難題。2016年3月，羅京佳被一個新聞熱點所吸引——「人工智慧擊敗世界圍棋冠軍」。
  • 手把手:用Python搭建機器學習模型預測黃金價格
    新年第一天，讓我們嘗試用python搭建一個機器學習線性回歸模型，預測金價！自古以來，黃金一直作為貨幣而存在，就是在今天，黃金也具有非常高的儲藏價值，那麼有沒有可能預測出黃金價格的變化趨勢呢？答案是肯定的，讓我們使用機器學習中的回歸算法來預測世界上貴重金屬之一,黃金的價格吧。
  • Nature深度用數據模型預測可怕結果
    ▲英國首相鮑裡斯·詹森同樣的模型表明，如果不採取行動，美國可能面臨220萬人死亡。美國政府也迅速對人們行動採取嚴格的新管控措施。新冠肺炎疫情模型如何建立？它們需要儲備哪些數據和假設？這些預測數字究竟靠不靠譜？
  • Excel裡的這個工具,做財務分析一定要學會
    有過編制預算經驗的朋友，應該會很熟悉，在預測技術裡，回歸分析應用非常廣泛，是用來進行定量數據分析的方法。線性回歸分析是一種統計方法，用於確定某個變量（或一組變量）對另一個變量的影響。
  • 老年人口預測模型與中國應用
    目前多數機構和學者，包括聯合國，均利用歷次全國人口普查的資料和近年《中國統計年鑑》公布的人口資料，根據不同的人口政策目標參數假設，即今後可能的人口政策取向，對未來我國人口老齡化的變化趨勢進行預測。由於使用的方法或選取的生育率、死亡率、遷移率的參數不同，人口預測結果往往並不一致。採用的方法主要有灰色預測模型、VAR模型、微分方程人口控制模型、多元回歸方程模型、隊列要素法等。
  • 基於ARIMA與Elman神經網絡的風速組合預測模型
    為提高風速數據中非線性部分的預測精度，本文提出了一種基於ARIMA和改進Elman神經網絡組合模型對某地區風速進行預測的新方法。ARIMA模型用於描述歷史數據的線性關係，改進的神經網絡模擬數據的非線性規律。本文採用2009年9月的720個風速數據建立組合預測模型，並利用該模型預測10月1日到6日內144個風速，取得了比較滿意的預測效果。
  • 用Excel軟體做銷售預測的三種方法
    營長說在編寫業務經營計劃和預算時，重點是進行銷售的預測。預測往往要以歷史數據為依據，分析銷售業務的未來趨勢。使用Excel軟體可以進行初步預測，這裡介紹函數預測、圖表預測和預測工作表三種方法。01函數預測法在做預測時最常用的方法就是線性預測法，即將歷史數據擬合成一條直線，根據直線的走向來預測未來數值。可以根據歷史數據對未來銷售額、庫存需求或消費趨勢進行預測。
  • 北京大學王亞沙:新冠肺炎傳播預測模型
    報告內容：首先介紹建立新冠病毒疫情分析預測的意義、數據來源和數據預處理方法，然後介紹報告人及團隊建立的多種疫情數據分析方法和疫情預測模型，接下來分湖北、湖北域外地區對模型預測結果進行了對比和討論，最後探討了相關方法和模型的可擴展性及其在其它領域的應用潛力。
  • 預測精準度明顯優於國外預測模型
    深圳特區報廣州11月28日電（記者 劉良龍）11月27日晚，廣東省南山醫藥創新研究院發布消息稱，由國家呼吸醫學中心、廣州呼吸健康研究院、國家呼吸系統疾病臨床醫學研究中心開發的全球新冠疫情科學預測系統（公測版）正式上線。
  • 機器學習預測房價?靠譜嗎?
    從數據集中移除「 median_house_value」列，然後將餘下的分配為X，如下所示：第四步：構建模型使用scikit-learn庫創建模型。該庫在代碼中以sklearn形式編寫。當用存儲在DataFrames中的數據類型進行建模時，最受歡迎的庫就是Scikit-learn。
  • 通過Python 代碼實現時間序列數據的統計學預測模型
    如何明確時間序列數據是否具有平穩性？可以從兩個特徵進行判斷。(1) 趨勢，即均值隨時間變化；(2) 季節性，即方差隨時間變化、自協方差隨時間變化。案例：通過 SARIMA 預測廣告支出首先，我們建立test_stationarity來檢查時間序列數據的平穩性。
  • 10個Excel財務表格模板!省時省力又省心
    對於大多數會計人員來說，最讓人頭疼的可能就是每天都要統計各式各樣的數據，也就需要用excel做各種不同的表格，要是Excel操作再不熟練，就只能天天加班熬夜製作，費時費力！今天小編給大家分享幾個財務工作中常用到的表格模板，可直接套用！非常方便！
  • 當模型預測控制遇見機器人學習
    本文將不使用任何讓人敬而遠之的公式，試圖通過自然語言深入淺出的討論，當模型預測控制遇見機器學習，它們會擦出怎樣的思想火花。我從工程系統的角度理解，用一句話解釋，那就是ML是利用數學（例如概率論）將工程系統的歷史數據（Data）轉換為數字（Numbers），並從中找出系統的行為模式（Patterns），以對其未來的行為進行預測（Prediction）。如果只用一個詞解釋，那就是預測（Prediction）。
  • MagicHand雲平臺|物種與功能網絡與模型預測分析
    今天給大家介紹雲平臺宏基因組個性化分析之一：物種與功能網絡與模型預測分析。物種與功能網絡與模型預測分析物種與功能網絡與模型預測分析是針對特定的研究對象和目的，對特定的樣品及其數據集進行關聯與模型預測分析，進一步挖掘數據中的有效信息。
  • 讓谷歌折戟的AI流行病預測,如何被創業公司攻佔?
    今天我們就從傳染病預測出發，看看AI是如何一步步走向「神機妙算」的。谷歌GFT頻喊「狼來了」：流感大數據的狂想曲用AI預測傳染病顯然不是Bluedot的專利，其實早在2008年，今天的AI「強手」谷歌，就曾進行過一次不太成功的嘗試。
  • 需求預測模型精準「預報」
    中國石油網消息（特約記者王雲濤）截至11月25日，中國石油規劃總院共完成天然氣市場預測分析及建議報告10餘份，及時提交天然氣銷售周度、月度預測報告30餘期、預測數據4000餘條，持續加強冬季天然氣生產運行支撐保障工作。
  • 光伏組件積塵量的影響因素及預測模型研究
    4 積塵量預測模型1) 為判斷光伏組件表面積塵量受氣象因素的影響程度，通過實驗收集同一時間內的光伏組件積塵量，以及環境溫度、相對溼度、風速、氣壓等相關氣象數據，並建立它們之間的相互關係模型。由於樣本較少，自變量之間的關係較為模糊，因此採用支持向量回歸(SVR) 預測方法建立SVR 模型，利用Matlab 的Libsvm 工具箱完成模型建立。按照Libsvm 軟體包所要求的格式收集數據集，對數據進行歸一化處理，採用網格尋優、遺傳算法等尋求最佳的懲罰係數參數C 與寬度係數γ，利用得到的最佳(C，γ) 對訓練集進行訓練，從而得到SVR 模型，最後利用獲取的模型進行測試驗證。