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

作者: moodyblue   2017-12-04 00:13:13
Excel如何設計函數公式整理固定資產清冊
網誌圖文版:
http://www.b88104069.com/archives/4267
事務所工作的時候,接觸過很多家企業的帳冊和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整理術 - 入門密技+進階實作 台北場淡江大學台北校區
https://goo.gl/y6QKVG
高效率 Excel VBA 工作術 台北場淡江大學台北校區
https://goo.gl/kYqdUc

Links booklink

Contact Us: admin [ a t ] ucptt.com