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