Excel如何設計函數公式整理固定資產清冊

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

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

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

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

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

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

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

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

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

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

文章結語兩點補充:

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

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


《會計人的Excel小教室》高雄場 文創訓練中心高雄世貿館
2017-12-23(星期六) 09:00~16:00

https://uknowiknow.com/courses/33b3c40c-c79e-412f-8ef8-2bebcc9d4ef2

《會計人的Excel小教室》:每位會計人都應該具備的武功秘笈!

http://www.books.com.tw/products/0010734874

《會計人的Excel VBA小教室》:會計人Excel強化並且VBA入門!

http://www.books.com.tw/products/0010753074