EXCEL在會(huì)計(jì)和財(cái)務(wù)管理中運(yùn)用第7章講義

1、固定資產(chǎn)是指企業(yè)使用期限超過1年的房屋、建筑物、機(jī)器、機(jī)械、運(yùn)輸工具以及其他與生產(chǎn)、經(jīng)營(yíng)有關(guān)的設(shè)備、器具和工具等。而一些不屬于生產(chǎn)經(jīng)營(yíng)主要設(shè)備的物品，但是其單位價(jià)值在2000元以上，并且使用年限超過2年的，也應(yīng)當(dāng)作為固定資產(chǎn)。固定資產(chǎn)是企業(yè)的勞動(dòng)手段，也是企業(yè)賴以生產(chǎn)經(jīng)營(yíng)的主要資產(chǎn)。本節(jié)從一些常見的固定資產(chǎn)管理表格入手，介紹固定資產(chǎn)的管理方法。下面先來制作固定資產(chǎn)卡片，用于登記固定資產(chǎn)的各種資料。創(chuàng)建固定資產(chǎn)卡片的操作步驟如下。步驟1 新建“固定資產(chǎn)管理.xlsx”工作簿，并重命名Sheet1為“固定資產(chǎn)卡片”，接著在工作表中輸入如圖7-1所示的數(shù)據(jù)。步驟2 選中B5:H15單元格區(qū)域，然后在

2、“開始”選項(xiàng)卡下的“字體”組中單擊“邊框”按鈕旁邊的三角按鈕，從彈出的菜單中選中“所有框線”命令，如圖7-2所示。步驟3 選中B1:F4單元格區(qū)域，然后在“開始”選項(xiàng)卡下的“對(duì)齊方式”組中單擊“合并后居中”按鈕，接著設(shè)置其字體格式為“華文琥珀 26”，效果如圖7-3所示。步驟4 按住Ctrl，依次選中G5:H5, G6:H6, G7:H7, G8:H8,B10:H10單元格區(qū)域，然后在“開始”選項(xiàng)卡下的“對(duì)齊方式”組中單擊“合并后居中”按鈕旁邊的三角按鈕，從彈出的菜單中選擇“跨越合并”命令，如圖7-4所示。圖7-1 創(chuàng)建“固定資產(chǎn)卡片”工作表圖7-4 選擇“跨越合并”命令圖7-3 設(shè)置表格標(biāo)題

3、的字體格式圖7-2 添加表格邊框線步驟5 選中文本內(nèi)容不能充滿單元格的單元格，然后打開“設(shè)置單元格格式”對(duì)話框，并切換到“對(duì)齊”選項(xiàng)卡，接著在“水平對(duì)齊”下拉列表框中選擇“分散對(duì)齊（縮進(jìn)）”選項(xiàng)，再單擊“確定”按鈕，如圖7-5所示。圖7-6 固定資產(chǎn)卡片的最終效果圖7-5 設(shè)置分散對(duì)齊方式步驟6 選中B9:H15單元格區(qū)域，然后在“開始”選項(xiàng)卡下的“對(duì)齊方式”組中單擊“居中”按鈕。步驟7 選中E7單元格，然后在“開始”選項(xiàng)卡下的“數(shù)字”組中單擊“數(shù)字格式”文本框右側(cè)的下拉按鈕，從彈出的菜單中選擇“短日期”選項(xiàng)。步驟8 設(shè)置C8,G8,C11:C15,E11:E15,G11:H15單元格區(qū)域的數(shù)

4、字格式為“數(shù)值”，“小數(shù)位數(shù)”為“2”；設(shè)置E8,D11:D15, F11:F15單元格區(qū)域的數(shù)字格式為“百分比”，“小數(shù)位數(shù)”為“2”，制作好的固定資產(chǎn)卡片如圖7-6所示。下面應(yīng)用表格樣式來快速美化表格，具體操作步驟如下。步驟1 選中B5:H15單元格區(qū)域，然后在“開始”選項(xiàng)卡下的“樣式”組中單擊“套用表格格式”按鈕，從彈出的菜單中單擊“表樣式中等深淺23”選項(xiàng)，如圖7-7所示。步驟2 彈出“套用表格式”對(duì)話框，在“表數(shù)據(jù)的來源”文本框中設(shè)置要應(yīng)用表格格式的單元格區(qū)域地址，并選中“表包含標(biāo)題”復(fù)選框，再單擊“確定”按鈕，如圖7-8所示。圖7-7 選擇要使用的表格格式步驟3 在“表格工具”下的

5、“設(shè)計(jì)”選項(xiàng)卡下，單擊“工具”組中的“轉(zhuǎn)換為區(qū)域”按鈕，如圖7-9所示。步驟4 彈出Microsoft Excel對(duì)話框，單擊“是”按鈕，確認(rèn)將表轉(zhuǎn)換為普通區(qū)域，如圖7-10所示。步驟5 調(diào)整B5:H5單元格區(qū)域的字體格式及內(nèi)容，接著合并B9:H9單元格區(qū)域并居中，最終效果如圖7-11所示。圖7-8 “套用表格式”對(duì)話框圖7-9 單擊“轉(zhuǎn)換為區(qū)域”按鈕圖7-10 確認(rèn)將表轉(zhuǎn)換為普通區(qū)域圖7-11 固定資產(chǎn)卡片的最終效果固定資產(chǎn)卡片制作完成后，接下來登記固定資產(chǎn)信息了，具體操作步驟如下。步驟1 在“固定資產(chǎn)卡片”工作表中錄入卡片編號(hào)、歸屬部門、參數(shù)、資產(chǎn)編號(hào)、資產(chǎn)名稱等基本信息，如圖7-12所示

6、。圖7-12 輸入固定資產(chǎn)基本信息步驟2 在G8單元格中輸入公式“=C8*E8”，按Enter鍵計(jì)算凈殘值，如圖7-13所示。步驟3 下面使用線性折舊法計(jì)算固定資產(chǎn)的年折舊額，方法是在C11單元格中輸入公式“=SLN($C$8,$G$8,$B$15)”，按Enter鍵計(jì)算年折舊額，再向下復(fù)制公式，結(jié)果如圖7-14所示。圖7-13 計(jì)算凈殘值步驟4 在D11單元格中輸入公式“=C11/$C$8”，按Enter鍵計(jì)算年折舊率，再向下復(fù)制公式。步驟5 在E11單元格中輸入公式“=C11/12”，按Enter鍵計(jì)算月折舊額，再向下復(fù)制公式。步驟6 在F11單元格中輸入公式“=E11/$C$8”，按En

7、ter鍵計(jì)算月折舊率，再向下復(fù)制公式，如圖7-15所示。步驟7 在G11單元格中輸入公式“=C11”，計(jì)算第1年累計(jì)折舊額；在G12單元格中輸入公式“=G11+C12”，計(jì)算第2年累計(jì)折舊額，再向下復(fù)制公式，結(jié)果如圖7-16所示。步驟8 在H11單元格中輸入公式“=C8-C11”，計(jì)算第1年后固定資產(chǎn)的折余價(jià)值；在H12單元格中輸入公式“=H11-C12”，計(jì)算第2年后固定資產(chǎn)的折余價(jià)值，再向下復(fù)制公式，結(jié)果如圖7-17所示。圖7-14 計(jì)算年折舊額圖7-15 計(jì)算年折舊率、月折舊額和月折舊率圖7-16 計(jì)算累計(jì)折舊額圖7-17 計(jì)算固定資產(chǎn)的折余價(jià)值圖7-18 選擇“移動(dòng)或復(fù)制”命令圖7-1

8、9 “移動(dòng)或復(fù)制工作表”對(duì)話框當(dāng)要登記其他固定資產(chǎn)時(shí)，可以在新的固定資產(chǎn)卡片上進(jìn)行填寫，但是這就需要用戶再次輸入凈殘值、年折舊額、年折舊率、折余價(jià)值等計(jì)算公式。為了避免再次輸入公式的麻煩，用戶可以在已登記固定資產(chǎn)的卡片中變更固定資產(chǎn)的基本信息，具體操作步驟如下。步驟1 右擊“固定資產(chǎn)卡片”工作表，從彈出的快捷菜單中選擇“移動(dòng)或復(fù)制”命令，如圖7-18所示。步驟2 彈出“移動(dòng)或復(fù)制工作表”對(duì)話框，在“工作簿”下拉列表框中選中“固定資產(chǎn)管理.xlsx”選項(xiàng)，接著在“下列選定工作表之前”列表框中選中Sheet2選項(xiàng)，并選中“建立副本”復(fù)選框，再單擊“確定”按鈕，如圖7-19所示。步驟3 這時(shí)將會(huì)出現(xiàn)

9、名稱為“固定資產(chǎn)卡片（2）”的工作表，修改工作表名稱為“變更登記”，然后在工作表中修改卡片編號(hào)、型號(hào)、資產(chǎn)名稱、原值等基本數(shù)據(jù)，結(jié)果如圖7-20所示。在前面制作固定資產(chǎn)卡片的過程中，我們使用了直線折舊法計(jì)算固定資產(chǎn)每年的折舊額，下面再為大家介紹其他折舊方法，包括雙倍余額遞減法、年數(shù)總和法、可變余額遞減法、固定余額遞減法等。雙倍余額遞減法是用直線法折舊率的兩倍作為固定的折舊率乘以逐年遞減的固定資產(chǎn)期初凈值，得出各年應(yīng)提折舊額的方法。步驟1 在“固定資產(chǎn)管理.xlsx”工作簿中重命名Sheet2工作表為“雙倍余額遞減法”，接著在工作表中創(chuàng)建“固定資產(chǎn)雙倍余額遞減法分析”表格，并輸入要分析固定資產(chǎn)的

10、基本信息，如圖7-21所示。圖7-20 變更信息后的固定資產(chǎn)卡片步驟2 選中C9單元格，然后單擊編輯欄左側(cè)的“插入函數(shù)”圖標(biāo)，打開“插入函數(shù)”對(duì)話框，接著設(shè)置函數(shù)類別為“財(cái)務(wù)”，并在“選擇函數(shù)”列表框中選中DDB選項(xiàng)，再單擊“確定”按鈕，如圖7-22所示。圖7-21 創(chuàng)建“雙倍余額遞減法”工作表圖7-22 “插入函數(shù)”對(duì)話框步驟3 彈出“函數(shù)參數(shù)”對(duì)話框，在Cost文本框中輸入“E4”；在Salvage文本框中輸入“D6”；在life文本框中輸入“E6”；在Period文本框中輸入“B9”，省略Factir參數(shù)，再單擊“確定”按鈕，如圖7-23所示。步驟4 由于固定資產(chǎn)的初始購置成本、資產(chǎn)殘值

11、和預(yù)計(jì)使用年限是固定不變的，因此要修改C9單元格中的公式為“=DDB($E$4,$D$6,$E$6,B9)”，并向下填充公式，再將公式復(fù)制到E9:E13單元格區(qū)域中，結(jié)果如圖7-24所示。圖7-23 設(shè)置DDB函數(shù)參數(shù)圖7-24 使用雙倍余額遞減法計(jì)算固定資產(chǎn)折舊額年數(shù)總和法又稱總和年限法、折舊年限積數(shù)法、年數(shù)比率法、級(jí)數(shù)遞減法或年限合計(jì)法，是固定資產(chǎn)加速折舊法的一種。它是將固定資產(chǎn)的原值減去殘值后的凈額乘以一個(gè)逐年遞減的分?jǐn)?shù)計(jì)算確定固定資產(chǎn)折舊額的一種方法。在年數(shù)總和法中，折舊率的分子是固定資產(chǎn)尚可使用的年數(shù)；分母是使用年數(shù)的逐年數(shù)字之總和。步驟1 復(fù)制“雙倍余額遞減法”工作表，并修改其名稱

12、為“年限總和法”，接著修改工作表中的表格，如圖7-25所示。步驟2 選中C9單元格，然后單擊編輯欄左側(cè)的“插入函數(shù)”圖標(biāo)，打開“插入函數(shù)”對(duì)話框，接著設(shè)置函數(shù)類別為“財(cái)務(wù)”，并在“選擇函數(shù)”列表框中選中SYD選項(xiàng)，再單擊“確定”按鈕，如圖7-26所示。步驟3 彈出“函數(shù)參數(shù)”對(duì)話框，設(shè)置SYD函數(shù)參數(shù)，再單擊“確定”按鈕，如圖7-27所示。步驟4 復(fù)制C9單元格中的公式至C10:C13,E9:E13單元格區(qū)域，結(jié)果如圖7-28所示。圖7-25 創(chuàng)建“年限總和法”工作表圖7-26 選中SYD函數(shù)余額遞減法也是加速折舊法的一種，它是將每期固定資產(chǎn)的期初帳面凈值乘以一個(gè)固定不變的百分率計(jì)算折舊額的一

13、種方法。余額遞減法分為可變余額遞減法和固定余額遞減法兩種，下面將逐一進(jìn)行介紹。圖7-27 設(shè)置SYD函數(shù)參數(shù)圖7-28 使用年數(shù)總和法計(jì)算固定資產(chǎn)折舊額圖7-29 創(chuàng)建“可變余額遞減法”工作表圖7-30 選中VDB函數(shù)步驟1 參考前面方法，創(chuàng)建“可變余額遞減法”工作表，并在工作表中創(chuàng)建“固定資產(chǎn)可變余額遞減法分析”表格，如圖7-29所示。步驟2 選中C10單元格，然后單擊編輯欄左側(cè)的“插入函數(shù)”圖標(biāo)，打開“插入函數(shù)”對(duì)話框，接著設(shè)置函數(shù)類別為“財(cái)務(wù)”，并在“選擇函數(shù)”列表框中選中VDB選項(xiàng)，再單擊“確定”按鈕，如圖7-30所示。步驟3 彈出“函數(shù)參數(shù)”對(duì)話框，設(shè)置VDB函數(shù)參數(shù)，再單擊“確定”

14、按鈕，如圖7-31所示。步驟4 復(fù)制C10單元格中的公式至C11:C14單元格區(qū)域，結(jié)果如圖7-32所示。步驟5 在E10單元格中輸入公式“=VDB($E$4,$D$6,$E$6,B14,D10)”，在E11單元格中輸入公式“=VDB($E$4,$D$6,$E$6,D10,D11)”，并向下填充公式，使用可變余額遞減法計(jì)算固定資產(chǎn)折舊額的最終結(jié)果如圖7-33所示。圖7-31 設(shè)置VDB函數(shù)參數(shù)圖7-32 復(fù)制可變余額遞減法計(jì)算公式步驟1 參考前面方法，創(chuàng)建“固定余額遞減法”工作表，并在工作表中創(chuàng)建“固定資產(chǎn)固定余額遞減法分析”表格，如圖7-34所示。步驟2 選中C9單元格，然后單擊編輯欄左側(cè)的

15、“插入函數(shù)”圖標(biāo)，打開“插入函數(shù)”對(duì)話框，接著設(shè)置函數(shù)類別為“財(cái)務(wù)”，并在“選擇函數(shù)”列表框中選中DB選項(xiàng)，再單擊“確定”按鈕，如圖7-35所示。圖7-33 固定資產(chǎn)折舊計(jì)算的最終結(jié)果圖7-34 創(chuàng)建“固定余額遞減法”工作表圖7-35 選中DB函數(shù)圖7-36 設(shè)置DB函數(shù)參數(shù)步驟3 彈出“函數(shù)參數(shù)”對(duì)話框，設(shè)置DB函數(shù)參數(shù)，再單擊“確定”按鈕，如圖7-36所示。步驟4 復(fù)制C9單元格中的公式至C10:C13,E9:E13單元格區(qū)域，如圖7-37所示。在前面介紹的固定資產(chǎn)折舊分析中，我們使用了5種不同的折舊計(jì)算方法來計(jì)算固定資產(chǎn)的折舊額，每一種方法得到的折舊金額都是不同的。而企業(yè)在選擇折舊方法時(shí)

16、，需要考慮每一種折舊方法對(duì)企業(yè)自身發(fā)展的影響，因此需要對(duì)不同折舊方法的折舊額進(jìn)行對(duì)比分析，從中選出最優(yōu)的折舊方法。步驟1 在“固定資產(chǎn)管理.xlsx”工作簿中插入新工作表，并將其重命名為“折舊方法對(duì)比”，接著在工作表中創(chuàng)建“固定資產(chǎn)折舊方法對(duì)比分析”表格，如圖7-39所示。步驟2 在C9單元格中輸入公式“=SLN($F$4,$E$6,$F$6)”，并按Enter鍵確認(rèn)，接著向下填充公式，得出使用直線折舊法計(jì)算的固定資產(chǎn)折舊額，如圖7-40所示。圖7-37 使用固定余額遞減法計(jì)算固定資產(chǎn)折舊額圖7-39 創(chuàng)建“折舊方法對(duì)比”工作表步驟3 在D9單元格中輸入公式“=IF(B9=5,VLOOKUP(

17、B9,雙倍余額遞減法!$B$9:$C$13,2,1),VLOOKUP(B9,雙倍余額遞減法!$D$9:$E$13,2,1)”，并按Enter鍵確認(rèn)，接著向下填充公式，填入使用雙倍余額遞減法計(jì)算的固定資產(chǎn)折舊額，如圖7-41所示。步驟4 在E9單元格中輸入公式“=IF(B9=5,VLOOKUP(B9,年數(shù)總和法!$B$9:$C$13,2,1),VLOOKUP(B9,年數(shù)總和法!$D$9:$E$13,2,1)”，并按Enter鍵確認(rèn)，接著向下填充公式，填入使用年數(shù)總和法計(jì)算的固定資產(chǎn)折舊額，如圖7-42所示。步驟5 在F9單元格中輸入公式“=IF(B9=5,VLOOKUP(B9,可變余額遞減法!$B$9:$C$14,2,1),VLOOKUP(B9,可變余額遞減法!$D