目 錄
第一章 Excel 基礎(chǔ)知識(shí) ··························································································.1
第一節(jié) Excel 認(rèn)知·······························································································.2
一、數(shù)據(jù)輸入與編輯 ·······················································································.2
二、數(shù)據(jù)管理與分析 ·······················································································.6
第二節(jié) Excel 常用公式與函數(shù) ···············································································.14
一、Excel 常用公式 ·······················································································.14
二、數(shù)學(xué)與統(tǒng)計(jì)函數(shù) ······················································································.18
三、邏輯與文本函數(shù) ······················································································.19
四、日期與時(shí)間函數(shù) ······················································································.20
本章實(shí)訓(xùn) ···········································································································.21
第二章 Excel 在憑證與賬簿中的應(yīng)用 ····································································.23
第一節(jié) 記賬憑證的編制 ······················································································.24
一、創(chuàng)建記賬憑證·························································································.27
二、編輯記賬憑證·························································································.32
三、優(yōu)化憑證號(hào)····························································································.35
四、輸入后續(xù)憑證數(shù)據(jù) ···················································································.37
第二節(jié) 日記賬的生成 ·························································································.38
一、設(shè)置現(xiàn)金日記賬格式 ················································································.38
二、登記現(xiàn)金日記賬 ······················································································.39
第三節(jié) 科目匯總表的生成 ···················································································.42
一、設(shè)置科目匯總表格式 ················································································.42
二、獲取本期發(fā)生額 ······················································································.42
第四節(jié) 總分類賬的生成 ······················································································.48
一、設(shè)置總分類賬格式 ···················································································.48
二、錄入期初數(shù)····························································································.50
三、計(jì)算總賬科目本期發(fā)生額 ··········································································.50
四、計(jì)算期末數(shù)····························································································.51
本章實(shí)訓(xùn) ···········································································································.52
第三章 Excel 在財(cái)務(wù)報(bào)表中的應(yīng)用········································································.53
第一節(jié) 資產(chǎn)負(fù)債表的編制 ···················································································.54
一、設(shè)置資產(chǎn)負(fù)債表格式 ················································································.54
二、獲取一級(jí)科目期初數(shù) ················································································.56
三、計(jì)算資產(chǎn)負(fù)債表期初數(shù) ·············································································.59
四、獲取發(fā)生額····························································································.61
五、計(jì)算資產(chǎn)負(fù)債表期末數(shù) ·············································································.64
第二節(jié) 利潤(rùn)表的編制 ·························································································.66
一、設(shè)置利潤(rùn)表格式 ······················································································.66
二、獲取本期發(fā)生額 ······················································································.66
三、計(jì)算利潤(rùn)表本期金額 ················································································.69
本章實(shí)訓(xùn) ···········································································································.71
第四章 Excel 在薪酬管理中的應(yīng)用········································································.74
第一節(jié) 員工信息表的編制 ···················································································.75
一、創(chuàng)建參數(shù)表····························································································.76
二、創(chuàng)建員工信息表 ······················································································.77
三、添加員工信息·························································································.84
第二節(jié) 考勤表與業(yè)績(jī)表的編制··············································································.84
一、編制考勤表····························································································.86
二、編制業(yè)績(jī)表····························································································.88
第三節(jié) 員工工資表的編制 ···················································································.91
一、創(chuàng)建員工工資表 ······················································································.91
二、計(jì)算“五險(xiǎn)一金” ···················································································.95
三、計(jì)算個(gè)人所得稅 ······················································································.99
四、計(jì)算實(shí)發(fā)數(shù)····························································································100
第四節(jié) 工資條的編制 ·························································································101
一、生成工資條····························································································101
二、打印工資條····························································································101
本章實(shí)訓(xùn) ···········································································································103
第五章 Excel 在應(yīng)收賬款管理中的應(yīng)用·································································105
第一節(jié) 應(yīng)收賬款的分析 ······················································································106
一、編制應(yīng)收賬款統(tǒng)計(jì)表 ················································································106
二、分類匯總應(yīng)收賬款 ···················································································109
三、繪制應(yīng)收賬款三維餅圖 ·············································································113
第二節(jié) 逾期應(yīng)收賬款的分析·················································································117
一、編制逾期應(yīng)收賬款表 ················································································117
二、繪制逾期應(yīng)收賬款簇狀柱形圖 ····································································120
三、編制應(yīng)收賬款催款函 ················································································123
第三節(jié) 應(yīng)收賬款的賬齡分析·················································································129
一、編制應(yīng)收賬款賬齡分析表 ··········································································129
二、計(jì)算應(yīng)收賬款壞賬準(zhǔn)備金額 ·······································································130
本章實(shí)訓(xùn) ···········································································································131
第六章 Excel 在存貨管理中的應(yīng)用········································································133
第一節(jié) 進(jìn)貨的統(tǒng)計(jì)分析 ······················································································134
一、創(chuàng)建存貨參數(shù)表 ······················································································134
二、創(chuàng)建入庫(kù)單界面 ······················································································135
三、編制入庫(kù)統(tǒng)計(jì)表 ······················································································137
第二節(jié) 銷貨的統(tǒng)計(jì)分析 ······················································································141
一、創(chuàng)建出庫(kù)單界面 ······················································································141
二、編制出庫(kù)統(tǒng)計(jì)表 ······················································································143
第三節(jié) 存貨的分類匯總 ······················································································146
一、創(chuàng)建存貨匯總表 ······················································································146
二、計(jì)算總金額和總數(shù)量 ················································································147
本章實(shí)訓(xùn) ···········································································································150
第七章 Excel 在固定資產(chǎn)管理中的應(yīng)用·································································151
第一節(jié) 固定資產(chǎn)的登記與變更··············································································152
一、登記固定資產(chǎn)·························································································152
二、變更固定資產(chǎn)·························································································159
第二節(jié) 固定資產(chǎn)的折舊處理·················································································163
一、創(chuàng)建固定資產(chǎn)折舊表 ················································································164
二、年限平均法計(jì)提折舊 ················································································165
三、雙倍余額遞減法計(jì)提折舊 ··········································································166
四、年數(shù)總和法計(jì)提折舊 ················································································167
五、分析折舊費(fèi)用·························································································168
本章實(shí)訓(xùn) ···········································································································171
第八章 Excel 在成本費(fèi)用管理中的應(yīng)用·································································172
第一節(jié) 產(chǎn)品成本的統(tǒng)計(jì)分析·················································································173
一、創(chuàng)建產(chǎn)品成本計(jì)算表 ················································································174
二、計(jì)算產(chǎn)品成本·························································································174
三、分析產(chǎn)品成本差異 ···················································································182
第二節(jié) 日常費(fèi)用的統(tǒng)計(jì)分析·················································································185
一、創(chuàng)建參數(shù)表····························································································185
二、創(chuàng)建費(fèi)用匯總報(bào)銷單 ················································································187
三、編制日常費(fèi)用統(tǒng)計(jì)表 ················································································188
四、按費(fèi)用類別分析日常費(fèi)用 ··········································································191
五、繪制日常費(fèi)用分析圖 ················································································193
本章實(shí)訓(xùn) ···········································································································196
第九章 Excel 在本量利分析中的應(yīng)用 ····································································199
第一節(jié) 分解混合成本的方法·················································································200
一、利用圖表工具分解混合成本 ·······································································200
二、利用相關(guān)函數(shù)分解混合成本 ·······································································203
第二節(jié) 本量利關(guān)系的敏感分析··············································································203
一、計(jì)算單品種盈虧平衡點(diǎn) ·············································································204
二、分析單品種敏感性 ···················································································205
三、繪制單品種本量利分析動(dòng)態(tài)圖表 ·································································206
四、計(jì)算多品種盈虧平衡點(diǎn) ·············································································210
本章實(shí)訓(xùn) ···········································································································212
第十章 Excel 在投資決策中的應(yīng)用········································································213
第一節(jié) 貨幣時(shí)間價(jià)值的認(rèn)識(shí)·················································································214
一、計(jì)算現(xiàn)值·······························································································214
二、計(jì)算終值·······························································································216
三、計(jì)算利率·······························································································217
第二節(jié) 投資決策指標(biāo)的計(jì)算·················································································218
一、靜態(tài)評(píng)價(jià)指標(biāo)·························································································218
二、動(dòng)態(tài)評(píng)價(jià)指標(biāo)·························································································221
本章實(shí)訓(xùn) ···········································································································224
第十一章 Excel 在籌資決策中的應(yīng)用 ····································································226
第一節(jié) 資金需要量的預(yù)測(cè) ···················································································227
一、資金習(xí)性法預(yù)測(cè) ······················································································227
二、銷售百分比法預(yù)測(cè) ···················································································230
第二節(jié) 資本成本的測(cè)算 ······················································································234
一、創(chuàng)建資本成本計(jì)算界面 ·············································································234
二、計(jì)算個(gè)別資本成本 ···················································································235
三、計(jì)算加權(quán)資本成本 ···················································································236
第三節(jié) 長(zhǎng)期借款籌資的決策分析···········································································238
一、編制等額本息法貸款還本付息計(jì)劃表 ···························································238
二、編制等額本金法貸款還本付息計(jì)劃表 ···························································241
本章實(shí)訓(xùn) ···········································································································242
第十二章 Excel 在財(cái)務(wù)分析中的應(yīng)用 ····································································244
第一節(jié) 財(cái)務(wù)結(jié)構(gòu)分析 ·························································································245
一、分析資產(chǎn)負(fù)債表結(jié)構(gòu) ················································································247
二、分析利潤(rùn)表結(jié)構(gòu) ······················································································249
第二節(jié) 財(cái)務(wù)趨勢(shì)分析 ·························································································251
一、繪制資產(chǎn)趨勢(shì)線 ······················································································251
二、計(jì)算增長(zhǎng)率····························································································252
第三節(jié) 財(cái)務(wù)指標(biāo)分析 ·························································································252
一、分析償債能力·························································································252
二、分析營(yíng)運(yùn)能力·························································································254
三、分析盈利能力·························································································255
四、分析成長(zhǎng)能力·························································································256
本章實(shí)訓(xùn) ···········································································································256