Excel在財務(wù)中的應(yīng)用 第3版 課件 項目三 Excel在財務(wù)報表中的應(yīng)用

Excel在財務(wù)報表中的應(yīng)用項目三任務(wù)一編制資產(chǎn)負(fù)債表任務(wù)二編制利潤表和損益表任務(wù)三搭建財務(wù)報表分析框架編制資產(chǎn)負(fù)債表o1任務(wù)一編制資產(chǎn)負(fù)債表（1）表頭，包括報表的標(biāo)題、編制單位、編制日期及計量單位等；（2）表體，一般為賬戶式，即左邊為資產(chǎn)類項目，右邊為負(fù)債類及所有者權(quán)益類項目；（3）表尾，一般為編制報表的相關(guān)責(zé)任人。1.設(shè)置資產(chǎn)負(fù)債表的基本格式(1)表頭選中E3:G3區(qū)域，合并單元格后輸入日期公式“=IF(OR(封面!F6=4,封面!F6=6,封面!F6=9,封面!F6=11),

DATE(封面!D6,封面!F6,30),IF(封面!F6=2,DATE(封面!D6,2,28),DATE(封面!D6,封面!F6,31)))”公式可以跨工作簿引用，其引用格式為“=[SUMIF(S).XLSX]工作表名!單元格地址”，即工作簿名稱需要放在方括號中間。

如果被引用的工作簿已關(guān)閉，還需要在工作簿前增加路徑，且需在半角模式下的單引號將文件全名及路徑、工作表名引起來。例如該文件存在于D盤，則引用公式為“=‘D:\[工作簿名]工作表名’!單元格地址”。知識技能3-1：跨表引用技巧(2)表體由于字體較小，具體請參考教材(3)表尾選中B46：D46，合并單元格，輸入“="單位負(fù)責(zé)人："&封面!D8”，選中E46：F46，合并單元格，輸入“="財務(wù)負(fù)責(zé)人："&封面!D10”，選中G46：I46，合并單元格，輸入“="編表人："&封面!D12”2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)

資產(chǎn)負(fù)債表數(shù)據(jù)包括資產(chǎn)類、負(fù)債類和所有者權(quán)益類三大項數(shù)據(jù)，每項又包括年初數(shù)和期末數(shù)。

一般來說，年初數(shù)就是上一期的期末數(shù)，可以直接從上一期的資產(chǎn)負(fù)債表中復(fù)制過來即可，也可以從稅務(wù)系統(tǒng)中將自動生成的年初數(shù)復(fù)制過來。對于期末數(shù)，則需要通過SUMIF函數(shù)來實現(xiàn)。（1）輸入貨幣資金的期末數(shù)=SUMIF(總賬匯總表!J:J,"<1100",總賬匯總表!G:G)其中，第二參數(shù)"<1100"是一個邏輯關(guān)系式，意思是從科目編碼中找到"<1100"這個條件的所有科目，將它們的期末借方余額相加。2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)知識技能3-2：快速將文本格式轉(zhuǎn)換為數(shù)值格式在“總賬匯總表”工作表中，復(fù)制A列文本格式的科目編碼，鼠標(biāo)右鍵在J列上點擊“選擇性粘貼”。（2）輸入交易性金融資產(chǎn)的期末數(shù)=SUMIF(總賬匯總表!B:B,B7,總賬匯總表!G:G)

找到和該資產(chǎn)負(fù)債表B7單元格的名稱相符的科目后，計算出這個科目的借方余額也可寫成：=SUMIF(總賬匯總表!B:B,"交易性金融資產(chǎn)",總賬匯總表!G:G)2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)（3）輸入應(yīng)收票據(jù)的期末數(shù)暫不考慮壞賬準(zhǔn)備，公式簡化為:=SUMIF(總賬匯總表!B:B,"應(yīng)收票據(jù)",總賬匯總表!G:G)同上題理，也可直接取單元格內(nèi)容：=SUMIF(總賬匯總表!B:B,B9,總賬匯總表!G:G)2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)（4）輸入應(yīng)收賬款的期末數(shù)=SUMIF(總賬匯總表!B:B,"應(yīng)收賬款",總賬匯總表!G:G)+SUMIF(總賬匯總表!B:B,"預(yù)收賬款",總賬匯總表!G:G)-SUMIF(總賬匯總表!B:B,"壞賬準(zhǔn)備",總賬匯總表!H:H)其中，壞賬準(zhǔn)備是備抵科目，所以要從H列取數(shù)扣除。2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)（5）輸入預(yù)付款項的期末數(shù)=SUMIF(總賬匯總表!B:B,"預(yù)付賬款",總賬匯總表!G:G)+SUMIF(總賬匯總表!B:B,"應(yīng)付賬款",總賬匯總表!G:G)2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)（6）輸入其他應(yīng)收款的期末數(shù)暫不考慮壞賬準(zhǔn)備，公式簡化為:=SUMIF(總賬匯總表!B:B,"應(yīng)收利息",總賬匯總表!G:G)+SUMIF(總賬匯總表!B:B,"應(yīng)收股利",總賬匯總表!G:G)+SUMIF(總賬匯總表!B:B,B13,總賬匯總表!G:G)

注：B13為“其他應(yīng)收款”2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)（7）輸入存貨項目的期末數(shù)存貨項目是由多個科目匯總而成的，當(dāng)需要同時滿足多個條件時，需要用到SUMIFS函數(shù)。=SUMIFS(總賬匯總表!G:G,總賬匯總表!J:J,"<1500",總賬匯總表!J:J,">1400")+SUMIF(總賬匯總表!B:B,"生產(chǎn)成本",總賬匯總表!G:G)

2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)SUMIFS函數(shù)表示將J列中所有介于1400和1500之間的科目找出來，并將這些科目對應(yīng)的G列的金額相加。知識技能3-3：SUMIFS函數(shù)含義：SUMIFS函數(shù)可以根據(jù)多個指定條件對若干單元格進(jìn)行求和。

語法格式：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,

criteria2],

...)。其中：sum_range為實際求和區(qū)域，可以包括數(shù)字或包含數(shù)字的名稱、區(qū)域或單元格引用，在計算過程中會忽略空白值和文本值。criteria_range1為計算關(guān)聯(lián)條件的第一個區(qū)域。criteria1為條件1，條件的形式為數(shù)字、表達(dá)式、單元格引用或者文本，可用來定義對criteria_range1參數(shù)中的哪些單元格求和。此外，SUMIFS函數(shù)和SUMIF函數(shù)一樣，其條件可包含通配符，即進(jìn)行模糊查找時可以用“?”代表單個字符，用“*”代表任何文字。知識技能3-3：SUMIFS函數(shù)

廣東地區(qū)3月份共生產(chǎn)了多少A產(chǎn)品？=SUMIFS(D3:D21,B3:B21,3,A3:A21,”A產(chǎn)品”,E3:E21,”廣東*”)（8）計算固定資產(chǎn)的期末數(shù)“=SUMIF(總賬匯總表!B:B,B32,總賬匯總表!G:G)-SUMIF(總賬匯總表!B:B,"累計折舊",總賬匯總表!H:H)-SUMIF(總賬匯總表!B:B,"固定資產(chǎn)減值準(zhǔn)備",總賬匯總表!H:H)”+SUMIF(總賬匯總表!B:B,"固定資產(chǎn)清理",總賬匯總表!G:G)。2.設(shè)置資產(chǎn)類科目的數(shù)據(jù)（9）計算各個合計數(shù)流動資產(chǎn)合計=SUM(D6:D14)非流動資產(chǎn)合計=SUM(D25:D41)資產(chǎn)合計=D23+D422.設(shè)置資產(chǎn)類科目的數(shù)據(jù)（1）一般項目的取數(shù)與資產(chǎn)類項目的用意基本一致，只不過負(fù)債類和所有者權(quán)益類賬戶是從H列中進(jìn)行取數(shù)。

選擇單元格，輸入公式“=SUMIF(總賬匯總表!B:B,單元格,總賬匯總表!H:H)”3.設(shè)置負(fù)債類及所有者權(quán)益類項目的數(shù)據(jù)（2）應(yīng)付賬款的期末取數(shù)選擇H10單元格，輸入公式“=SUMIF(總賬匯總表!B:B

,

"應(yīng)付賬款",總賬匯總表!H:H)+SUMIF(總賬匯總表!B:B,"預(yù)付賬款",總賬匯總表!H:H)”3.設(shè)置負(fù)債類及所有者權(quán)益類項目的數(shù)據(jù)（3）預(yù)收款項的期末取數(shù)選擇H11單元格，輸入公式“=SUMIF(總賬匯總表!B:B

,

"預(yù)收賬款",總賬匯總表!H:H)+SUMIF(總賬匯總表!B:B,"應(yīng)收賬款",總賬匯總表!H:H)”3.設(shè)置負(fù)債類及所有者權(quán)益類項目的數(shù)據(jù)（4）其他應(yīng)付款的期末取數(shù)選擇H15單元格，輸入公式“=SUMIF(總賬匯總表!B:B,"應(yīng)付利息",總賬匯總表!H:H)+SUMIF(總賬匯總表!B:B,"應(yīng)付股利",總賬匯總表!H:H)+SUMIF(總賬匯總表!B:B,F15,總賬匯總表!H:H)”3.設(shè)置負(fù)債類及所有者權(quán)益類項目的數(shù)據(jù)（4）流動負(fù)債合計的計算“=SUM(H6:H18)”（5）應(yīng)付債券（其中）的計算“=H23+H24”（6）非流動負(fù)債合計的計算“=SUM(H21:H22,H25:H29)”（7）負(fù)債合計的計算（8）其他權(quán)益工具（其中）的計算“=SUM(H19,H30)”“=H35+H36”3.設(shè)置負(fù)債類及所有者權(quán)益類項目的數(shù)據(jù)（9）未分配利潤的期末取數(shù)“=SUMIF(總賬匯總表!B:B,"本年利潤",總賬匯總表!H:H)+SUMIF(總賬匯總表!B:B,

"利潤分配",總賬匯總表!H:H)-SUMIF(總賬匯總表!B:B,"本年利潤",總賬匯總表!G:G)-SUMIF(總賬匯總表!B:B,"利潤分配",總賬匯總表!G:G)”。3.設(shè)置負(fù)債類及所有者權(quán)益類項目的數(shù)據(jù)（10）所有者權(quán)益（或股東權(quán)益）合計的計算“=H33+H34+H37-H38+SUM(H39:H42)”（11）負(fù)債和所有者權(quán)益（或股東權(quán)益）合計的計算“=SUM(H31,H43)”3.設(shè)置負(fù)債類及所有者權(quán)益類項目的數(shù)據(jù)編制利潤表和損益類科目匯總表o2任務(wù)二編制利潤表和損益類科目匯總表1.設(shè)置利潤表基本格式（1）創(chuàng)建工作表（2）設(shè)置利潤表表體（3）設(shè)置利潤表表體這個利潤表是為了公司自用的，所以不要采用“一、二、三”“加、減”這些多余的符號，不利于函數(shù)取數(shù)。1.設(shè)置利潤表基本格式2.設(shè)置利潤表的本期金額（1）營業(yè)收入的期末取數(shù)

“=SUMIF(總賬匯總表!B:B,"主營業(yè)務(wù)收入",總賬匯總表!E:E)+SUMIF(總賬匯總表!B:B,"其他業(yè)務(wù)收入",總賬匯總表!E:E)”

從借方發(fā)生額中取“主營業(yè)務(wù)收入”和“其他業(yè)務(wù)收入”的本期發(fā)生額。（2）營業(yè)成本的期末取數(shù)

“=SUMIF(總賬匯總表!B:B,”主營業(yè)務(wù)成本“,總賬匯總表!E:E)+SUMIF(總賬匯總表!B:B,”其他業(yè)務(wù)成本",總賬匯總表!E:E)”

從借方發(fā)生額中取“主營業(yè)務(wù)成本”和“其他業(yè)務(wù)成本”的本期發(fā)生額。2.設(shè)置利潤表的本期金額（3）其它損益類項目的期末取數(shù)

“=SUMIF(總賬匯總表!B:B,單元格,總賬匯總表!E:E)”，表示從借方發(fā)生額中單元格的本期發(fā)生額。

D16、D19和D21單元格是關(guān)于相關(guān)利潤的計算，所以不適用前面的公式2.設(shè)置利潤表的本期金額（4）設(shè)置相關(guān)利潤的金額營業(yè)利潤=D6-SUM(D7:D13)+SUM(D14:D15)利潤總額=營業(yè)利潤+營業(yè)外收入-營業(yè)外支出=D16+D17-D18凈利潤=利潤總額-所得稅費用=D19-D202.設(shè)置利潤表的本期金額3.編制損益類科目匯總表（1）設(shè)置損益類科目匯總表的基本格式（2）輸入1-11月份金額3.編制損益類科目匯總表（3）對12月份金額進(jìn)行取數(shù)選擇O5單元格（營業(yè)收入），輸入=VLOOKUP("主營業(yè)務(wù)收入",總賬匯總表!$B$1:$H$50,4,0)+VLOOKUP("其他業(yè)務(wù)收入",總賬匯總表!$B$1:$H$50,4,0)3.編制損益類科目匯總表選擇O6單元格（營業(yè)成本），輸入=VLOOKUP("主營業(yè)務(wù)成本",總賬匯總表!$B$1:$H$50,4,0)+VLOOKUP("其他業(yè)務(wù)成本",總賬匯總表!$B$1:$H$50,4,0)其余單元格，輸入“=IFERROR(VLOOKUP(單元格,總賬匯總表!$B$1:$H$50,4,0),0)”回到C5單元格，輸入“=SUM(D5:O5)”。取數(shù)后將鼠標(biāo)拖曳向下填充。3.編制損益類科目匯總表（3）對12月份金額進(jìn)行取數(shù)3.編制損益類科目匯總表（3）對12月份金額進(jìn)行取數(shù)知識技能3-4：IFERROR函數(shù)含義：IFERROR函數(shù)可以捕獲和處理公式中的錯誤。如果公式的計算結(jié)果為錯誤，則返回您指定的值；否則將返回公式的結(jié)果。

語法格式：IFERROR(value,value_if_error)。其中：value就是檢查是否錯誤的參數(shù)，如果正確則返回該值的結(jié)果；如果錯誤則返回value_if_error的值。4.編制利潤表累計數(shù)“利潤表”的累計數(shù)需要從“損益類科目累計匯總表”中取數(shù)，因此只需在“利潤表”工作表中利用VLOOKUP函數(shù)，查找并返回每項在“損益類科目累計匯總表”工作表中的累計金額即可。

以E6單元格為例，輸入公式“=VLOOKUP(B6,損益類科目匯總表!$B$5:$C$17,2,0)”E16、E19、E21這3個單元格會呈現(xiàn)出“#N/A”無法找到的字樣，因為這3個項目都是利潤的計算公式。簡單的處理是直接將D16、D19、D21這3個單元格的公式分別往右拖動。4.編制利潤表累計數(shù)搭建財務(wù)報表分析框架o31.資產(chǎn)負(fù)債表分析模型（1）建立分析框架將表格右側(cè)列示負(fù)債和所有者權(quán)益項目剪切到左邊資產(chǎn)項目的下方，確?！靶写巍表棓?shù)據(jù)順序排列。1.資產(chǎn)負(fù)債表分析模型（2）結(jié)構(gòu)分析

結(jié)構(gòu)分析是以會計報表中的某個總體指標(biāo)作為100%，再計算出其各組成部分占該總體指標(biāo)的百分比，通過百分比的增減變動以了解有關(guān)財務(wù)活動的變化趨勢。例如，對資產(chǎn)負(fù)債表進(jìn)行數(shù)據(jù)結(jié)構(gòu)分析時，以資產(chǎn)為100%設(shè)置權(quán)重；對利潤表進(jìn)行數(shù)據(jù)結(jié)構(gòu)分析時，以營業(yè)收入作為100%基礎(chǔ)。這種比較方法可以用于發(fā)現(xiàn)存在顯著變化的項目。（2）結(jié)構(gòu)分析（2）結(jié)構(gòu)分析點擊“貨幣資金”所對應(yīng)的E6單元格，輸入“=D6/$D$83”，其中D6就是貨幣資金的本年數(shù)，D83是“負(fù)債和所有者權(quán)益(或股東權(quán)益)總計”數(shù)據(jù)（當(dāng)然也可以取D43單元格的“資產(chǎn)合計”數(shù)），因為是固定的總體指標(biāo)，所以需要絕對引用。取數(shù)后將鼠標(biāo)移到E6單元格右下角，雙擊填充柄，確保該列數(shù)據(jù)都已計算完畢。選中E整列，修改數(shù)據(jù)格式為百分比，并調(diào)整小數(shù)位數(shù)為兩位。1.資產(chǎn)負(fù)債表分析模型（2）結(jié)構(gòu)分析用同樣的方法設(shè)置G列數(shù)據(jù)（3）數(shù)據(jù)比較分析會計報表的比較分析是將連續(xù)數(shù)期的會計報表金額并列起來，比較其相同指標(biāo)的增減變動金額和幅度，據(jù)此判斷企業(yè)財務(wù)狀況和經(jīng)營成果發(fā)展變化的一種方法。繼續(xù)新增列H“增減變動金額”、列I“橫向變動比例”、列J“縱向變動比例”，并在第4行對應(yīng)位置附上相關(guān)計算公式“⑤=①-③”“⑥=⑤/③”“⑦=(②-④)/④”。