贊贊小屋

【Excel YEAR函數】整理公司固定資產財產清冊,財務預測計算折舊到期日

ERP系統報表往往不足,需要Excel函數公式組合補充。本文以公司固定資產的財產目錄表計算折舊為範例,一次學會ROUND、VALUE、LEFT、YEAR、MONTH、CONCATENATE、IF等函數應用。

事務所工作的時候,接觸過很多家企業的帳冊和ERP系統,臺灣大型的集團企業蠻多使用SAP系統,稍具規模的中小型企業很多是被鼎新拿下,例如Workflow系統和Tip-top系統。像這些成熟完整的ERP系統,會依照功能分成好幾個模組,其中總是會有個固資模組,裡面很多制式化報表,包括不可缺少的「財產目錄」或者「固定資產清冊」。

固定資產清冊核心欄位有:資產編號、資產名稱、規格、數量、原始成本、取得成本、本期折舊、累計折舊,這些歷史資料可以滿足事務所種種查核程序。然而,如果公司財會部門想要做財務分析,例如未來五年十年的預測模型,評估帳上固資未來幾年折舊對於損益的影響,此項分析除了已發生的歷史,更需要的是未來資訊,也就是折舊到期日。

比較少在ERP系統報表看到這個欄位,事務所不需要,但其實它是公司財務分析模擬未來狀況的關鍵資訊。以下介紹如何在現有固定資產清冊的基礎上,運用Excel函數公式計算出折舊到期日:

一、固定資產清冊

簡單扼要的「固定資產清冊」,如同文章前言所述,欄位有「資產編號」、「名稱」、「取得日期」等,目的是利用這些資料算出折舊到期的月份。

【Excel YEAR函數】整理公司固定資產財產清冊,財務預測計算折舊到期日 日期函數 第1張

二、每月折舊計算

首先是計算出每月折舊的公式:「=ROUND(G5/D5/12,0)」,亦即「取得成本」除以「耐用年限」、再除「12」(月份)。通常系統報表會有當期折舊,建議還是另外架個公式,剛好也是驗算。

【Excel YEAR函數】整理公司固定資產財產清冊,財務預測計算折舊到期日 日期函數 第2張

三、最後一年折舊

計算出折舊的「最後一年」:「=VALUE(RIGHT(YEAR(C5)+D5,2))」,這裡利用「Year」函數將「取得日期」轉換成年度,再加「耐用年限」,最後再以「Value」函數強制把計算結果設定為數值型態, 方便再進一步處理。

【Excel YEAR函數】整理公司固定資產財產清冊,財務預測計算折舊到期日 日期函數 第3張

四、最後折舊月份

考慮到月份有一位數和兩位數的差異,為符合一般年月為整齊四位數的表達方式,設計了「IF」函數作為邏輯判斷:「=IF(VALUE(MONTH(C5))<10,CONCATENATE(“0”,MONTH(C5)),MONTH(C5))」,計算結果即為「最後折舊月份」,這裡是假設取得固資後的次月開始提列折舊。

【Excel YEAR函數】整理公司固定資產財產清冊,財務預測計算折舊到期日 日期函數 第4張

五、固定資產折舊到期日

簡單將年月合併:「=I5&J5」,得到一目瞭然的「折舊到期」所屬年月。

【Excel YEAR函數】整理公司固定資產財產清冊,財務預測計算折舊到期日 日期函數 第5張

六、特殊情況IF函數調整

由於最後一年的折舊通常不會剛好是12個月,必須精心設計計算公式:「=IF($I5<18,0,IF($I5<19,$H5*$J5,$H5*12))」,如果是17年以前到期,於18年當然折舊費用為零,再來如果是小於19年,加上前面已經篩選掉17年以前,判斷結果便是當年18年到期,折舊計算為「每月折舊」乘以「最後折舊月份」,前兩個條件皆不成立的話,表示在當年折舊不會到期,所以是折12個月。

【Excel YEAR函數】整理公司固定資產財產清冊,財務預測計算折舊到期日 日期函數 第6張

七、各年度折舊費用

上一步驟將欄位的部份前面都加了個「$」,作用為固定住公式中的欄,到了這個要進一步計算「2019」年及「2020」年,公式拖曳複製過來之後,只要修改其中關於年份的部份即可。如此,完美計算出未來三年折舊費用預測。

【Excel YEAR函數】整理公司固定資產財產清冊,財務預測計算折舊到期日 日期函數 第7張

兩點心得補充

第一,ERP系統雖然很多報表可以使用,但畢竟是制式化軟體,不一定符合每個企業狀況和需求,很多時候原始報表必須運用Excel進行後加工處理。公式設計時應一併考量延伸性,如同此篇文章範例,想再增加未來預測年度、過後新的結帳期間要再更新資料,都是很容易的。

第二,後加工的過程愉悅或者痛苦,決定於日積月累的Excel功力。這篇文章有些函數之前我用過了,有些函數之前從沒用過,只是知道現在處理跟日期還有文字有關的問題,而我大致瞭解這兩個類型有些什麼函數可以使用,所以解題的過程沒遇到太大困難。在這裡建議各位讀者,既然工作上會一直用到Excel,那麼就在每一次遇到難題的時候,把它當作是挑戰和磨練,假以時日,每個人都可以達到贊贊小屋見招拆招、無招剛好練功的境界。

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

https://www.books.com.tw/products/0010817518

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1