(２)再次單擊“插入”選項卡“表格”組中的“數據透視表”選項,在工作表中創建一個“數據透視表２”模板.
１５０Excel在財務管理中的應用圖４Ｇ１９創建數據透視表１將“客戶名稱”字段添加到“行標簽”區域,將“數量”和“銷售額”字段添加到“數值”區域.
見圖４Ｇ２０.
圖４Ｇ２０插入數據透視表２模塊四:Excel在銷售會計崗位中的應用１５１(３)用同樣的方法再次創建一個數據透視表,將“銷售人員”添加到“行標簽”區域,將“數量”和“銷售額”字段添加到“數值”區域.見圖４Ｇ２１.
圖４Ｇ２１創建數據透視表３(４)單擊數據透視表中的“行標簽”單元格,分別將３個數據透視表的行標簽標題更改為“按商品名稱分析”、“按客戶分析”和“按銷售人員分析”.見圖４Ｇ２２.
圖４Ｇ２２更改行標簽標題１５２Excel在財務管理中的應用(５)單擊選中數據透視表１中的任意單元格.在“插入”選項卡中的“篩選器”組中單擊“切片器”按鈕.在“插入切片器”對話框中勾選“商品名稱”字段.單擊“確定”按鈕.隨後,“商品名稱”字段作為切片器插入到工作表中.見圖４Ｇ２３.
圖４Ｇ２３插入的切片器(６)在“切片器工具—選項”選項卡中的“大小”組中的“列”框中輸入“５”.拖動切片器邊框,調整長寬比例.見圖４Ｇ２４.
圖４Ｇ２４設置切片器列數及長寬(７)單擊“數據透視表連接”按鈕.在“數據透視表連接(商品名稱)”對話框中勾選要建立連接的數據透視表.單擊“確定”按鈕.
在切片器中單擊“冰箱”按鈕,此時所有的數據透視表中將隻顯示商品名稱為“冰箱”的數據統計結果.
“按商品名稱分析”數據透視表中顯示“冰箱”的分析結果,“按客戶分析”數據透視表中顯示“冰箱”的分析結果,同樣地,“按銷售人員分析”數據透視表中也顯示“冰箱”的統計結果.此時,它們的總計值都相同.見圖４Ｇ２５.
模塊四:Excel在銷售會計崗位中的應用１５３圖４Ｇ２５使用切片器控製數據透視表(８)單擊選中切片器.在“切片器樣式”組中單擊選擇一種切片器樣式.應用樣式後的切片器效果如圖４Ｇ２６.當前選中的按鈕顯示為藍色,當前未選中的按鈕顯示為灰色.
圖４Ｇ２６應用樣式後的切片器效果１５４Excel在財務管理中的應用(９)最後得到的數據透視表樣式效果表.見圖４Ｇ２７.
圖４Ｇ２７數據透視表樣式效果五、技能訓練以所學知識運用任務４Ｇ１訓練結果表,創建月銷售收入綜合分析透視表.
模塊四:Excel在銷售會計崗位中的應用１５５任務４Ｇ５銷售利潤變動趨勢分析一、任務描述本任務以天真電器公司為例,分析銷售利潤變動趨勢.通過創建數據點折線圖可以反映該年中銷售利潤的變動趨勢.
二、任務分析本任務主要根據全年銷售收入與銷售成本、銷售費用數據,建立銷售收入與成本、銷售費用和銷售稅金所需的基本信息表格及圖表.通過分析企業的銷售收入與銷售成本、銷售費用和銷售稅金,可以探尋銷售收入與銷售成本之間是否存在一定程度的相關性.通過圖表比較銷售收入與銷售成本、銷售費用和銷售稅金可以初步掌握企業的毛利潤.(建議課時:２課時)三、相關知識企業的銷售費用是指企業在銷售商品過程中發生的各項費用以及為銷售本企業商品而專設的銷售機構的經營費用.商品流通企業在購買商品過程中發生的進貨費用也包括在銷售費用之中.
銷售稅金是指由銷售商品、提供勞務等負擔的銷售稅金和教育附加費,包括營業稅、消費稅、城市維護建設稅等.
四、實際操作(１)打開“銷售收入與成本、費用的分析”表.①選擇單元格區域F２:F１４.②在“插入”選項卡中單擊“折線圖”下三角按鈕.③從子圖表類型中單擊“數據點折線圖”,見圖４Ｇ２８.
１５６Excel在財務管理中的應用圖４Ｇ２８選擇圖表類型(２)創建的默認的銷售利潤折線效果圖如圖４Ｇ２９.
圖４Ｇ２９創建默認效果折線圖(３)①打開“選擇數據源”對話框,單擊“添加”按鈕.②設置“係列名稱”為單元格B２,設置“係列值”為單元格B２:B１４.單擊“確定”按鈕.見圖４Ｇ３０.
模塊四:Excel在銷售會計崗位中的應用１５７圖４Ｇ３０添加“銷售收入”數據係列(４)再次打開“選擇數據源”對話框,單擊“添加”按鈕.設置“係列名稱”為單元格C２,設置“係列值”為單元格C３:C１４.單擊“確定”按鈕.見圖４Ｇ３１.
圖４Ｇ３１添加“銷售成本”數據係列(５)添加“銷售收入”和“銷售成本”兩個數據係列後的圖表效果見圖４Ｇ３２.
圖４Ｇ３２添加係列後的圖表效果１５８Excel在財務管理中的應用(６)單擊選擇“銷售利潤”係列.在“圖標工具—布局”選項卡的“分析”組單擊“趨勢線”下三角按鈕.在下拉列表中單擊“線性趨勢線”選項.見圖４Ｇ３３.
圖４Ｇ３３添加線性趨勢線效果(７)右擊圖表中的趨勢線.從快捷菜單中單擊“設置趨勢線格式”命令.在“趨勢預測”框中設置前推“２”個周期.勾選“顯示公式”複選框.見圖４Ｇ３４.
圖４Ｇ３４最終圖表效果模塊四:Excel在銷售會計崗位中的應用１５９五、技能訓練結合所學知識,以任意公司為例錄入銷售收入與銷售成本、銷售費用數據,建立銷售收入與成本、銷售費用和銷售稅金所需的基本信息表格及圖表.
１６０Excel在財務管理中的應用任務４Ｇ６銷售利潤相關性分析表一、任務描述本任務以天真電器公司為例,分析銷售利潤相關性.需要創建一個銷售利潤因素分析表.
二、任務分析本任務主要根據全年銷售收入與銷售成本、銷售費用數據,建立銷售收入與成本、銷售費用和銷售稅金所需的基本信息表格及圖表.使用Excel中的回歸函數以及相關係數計算函數,計算出利潤與各個因素之間的回歸方程,使用公式和數據來說話.(建議課時:１課時)三、相關知識通過銷售毛利潤等於銷售收入減去銷售成本,再減去銷售費用和銷售稅金後的餘額.
四、實際操作(１)打開“銷售收入與成本、費用分析表”,插入工作簿,命名為“銷售利潤相關性分析表”,並在該表下方創建銷售利潤因素分析表格.見圖４Ｇ３５.
圖４Ｇ３５創建表格(２)選中單元格區域B１９:C１９,輸入公式“＝LINEST(F３:F１４,B３:B１４)”,按下【Ctrl＋Shift＋Enter】組合鍵,返回利潤和收入的線性回歸方程參數a和b.見圖４Ｇ３６.
模塊四:Excel在銷售會計崗位中的應用１６１圖４Ｇ３６設置公式計算回歸係數a和b(３)選中單元格區域B２０:C２０,輸入公式“＝LINEST(F３:F１４,C３:C１４)”,按下【Ctrl＋Shift＋Enter】組合鍵,返回利潤和成本的線性回歸方程參數a和b.用同樣的方法操作“利潤與費用相關性分析”和“利潤與稅金相關性分析”.見圖４Ｇ３７.
圖４Ｇ３７設置公式計算回歸係數a和b(４)在單元格D１９中輸入公式“＝CONCATENATE(＂Y＝＂,TEXT(B１９,＂０．００＂),＂X＋＂,TEXT(C１９,＂０．００＂))”,按下【Enter】鍵後,向下複製公式至單元格D２２,生成各個不同組合模塊的線性回歸方程.見圖４Ｇ３８.
圖４Ｇ３８設置公式生成回歸方程１６２Excel在財務管理中的應用(５)在單元格E１９中輸入公式“＝CORREL(F３:F１４,B３:B１４)”,按下【Enter】鍵後,從結果可以得出當前數據所顯示的銷售利潤與銷售收入具有顯著相關性.見圖４Ｇ３９.
圖４Ｇ３９設置公式計算相關係數(６)在單元格E２０、E２１、E２２中分別輸入公式“＝CORREL(F３:F１４,C３:C１４)”、“＝CORREL(F３:F１４,D３:D１４)”、“＝CORREL(F３:F１４,E３:E１４)”,計算各自的相關係數.見圖４Ｇ４０.
圖４Ｇ４０設置公式計算相關係數(７)在單元格F１９中輸入公式“＝IF(ABS(E１９)＜０．５,＂異常＂,＂正常＂)”,按下【Enter】鍵後,向下複製公式至單元格F２２,根據相關係數值判斷當前數據表現是否正常.見圖４Ｇ４１.
圖４Ｇ４１銷售利潤變動趨勢分析表模塊四:Excel在銷售會計崗位中的應用１６３五、技能訓練結合所學知識,以任務４Ｇ４訓練原始數據為依據,建立銷售收入與成本、銷售費用和銷售稅金所需的基本信息表格及圖表.使用Excel中的回歸函數以及相關係數計算函數,計算出利潤與各個因素之間的回歸方程,繪製銷售利潤變動趨勢分析表.
１６４Excel在財務管理中的應用任務４Ｇ７銷售預測一、任務描述本任務以天真電器公司為例,采用周期性的預測模型比較方法,對下年銷售預測.
二、任務分析本任務主要根據２０１４年全年銷售額的序列數據預測２０１５年上半年各月的銷售額.銷售預測是指借助於曆史銷售資料及其他相關信息,采用適當的方法,對未來一定時期產品的銷售數量和銷售狀態的變化趨勢做出預計.做好銷售預測對於改善企業的生產經營、提高經濟效益具有十分重要的意義.(建議課時:２課時)三、相關知識首先繪製所有商品銷售額的時間變化曲線(繪製XY散點圖)以便觀察銷售額的變化趨勢,確定采用哪種預測模型.采用周期性的預測模型,利用函數進行預測、利用圖表進行預測.
四、實際操作(１)打開“銷售預測”插入工作簿,命名為“利用函數進行預測”,並在該表下方創建銷售預測分析表格.見圖４Ｇ４２.
模塊四:Excel在銷售會計崗位中的應用１６５圖４Ｇ４２２０１４年全年銷售彙總表根據任務４Ｇ５知識點製作XY散點圖,如圖４Ｇ４３.
圖４Ｇ４３２０１４年月銷售散點圖根據２０１４年月銷售額預測２０１５年上半年月銷售額.在單元格B１７輸入公式“＝FORECAST(１２＋＄A１７,B＄３:B＄１４,＄A＄３:＄A＄１４)”,然後向右向下複製,就得到各個商品以及全部商品在２０１５年上半年的銷售額預測值.見圖４Ｇ４４.
圖４Ｇ４４利用函數預測銷售額效果圖１６６Excel在財務管理中的應用(２)打開“銷售預測”插入工作簿,命名為“利用圖表進行預測”,並在該表下方創建銷售預測分析表格.
利用圖表預測就是先繪製XY散點圖,然後為數據係列添加趨勢線,並顯示趨勢線方程和R平方值,根據R平方值判斷預測方程的準確度,通過趨勢線的變化趨勢來判斷未來的銷售變化情況.
根據任務４Ｇ５知識點製作XY散點圖,如圖４Ｇ４５.
圖４Ｇ４５製作XY散點圖根據２０１４年銷售數據變化曲線,然後為該數據係列添加趨勢線如圖４Ｇ４６.
圖４Ｇ４６添加趨勢線模塊四:Excel在銷售會計崗位中的應用１６７添加趨勢線相關設置如圖４Ｇ４７.
圖４Ｇ４７設備趨勢線圖結果如圖４Ｇ４８.
圖４Ｇ４８圖表法預測銷售額如果一種預測模型誤差很大,不能滿足要求,可以重新設置趨勢線的類型,方法是:單擊趨勢線,再單擊右鍵快捷菜單中的“趨勢格式”命令,打開“趨勢格式”對話框,在“類型”選項卡中重新選擇預測模型.見圖４Ｇ４９.
１６８Excel在財務管理中的應用圖４Ｇ４９重新選擇趨勢線的預測模型圖４Ｇ５０是把趨勢線的多項式模型的順序設置為６的情況,此時的R平方值為０．７３６９,要比２階多項式的R平方值大,因此預測精度也更高一些.
圖４Ｇ５０改變趨勢線類型後的預測模型及其R平方值注意:特別注意的是:利用圖表進行預測是非常直觀的,但在有些情況下我們不能使用趨勢線的預測方程來計算預測值,趨勢線隻是給出一個銷售額的變化趨勢,通過這個趨勢我們可以了解未來的銷售變化情況,從而提前做準備.
模塊四:Excel在銷售會計崗位中的應用１６９綜合實訓四根據上表資料做如下要求:按商品統計分析銷售收入、分析銷售收入變動趨勢、分析月銷售收入綜合、分析銷售利潤變動趨勢、分析銷售利潤相關性,並將各結果分別保存.
１７０Excel在財務管理中的應用模塊五:Excel在成本管理崗位中的應用學習目標通過本章學習,掌握財務人員在成本管理工作中應具備的基本知識和操作技能,主要包括以下幾點:１．建立成本分析基礎信息表;２．建立甲產品成本分析圖表;３．根據銷售預算建立生產預算表;４．完成直接材料、直接人工預算;５．完成成本核算;６．掌握折線圖、柱形圖及餅圖等圖表的用法;７．綜合技能:依據銷售、生產、直接材料、直接人工預算,進行現金預算.
本模塊建議課時:８課時任務５Ｇ１建立成本分析基礎信息表一、任務描述本任務以××公司為例,建立成本管理所需的成本分析表格和公式.
二、任務分析本任務主要以××公司生產的甲產品為例,建立成本管理所需的分析表格和公式(建議課時:２課時)三、相關知識具體數據輸入及表格設置等相關知識可以參閱模塊一.具體使用方法參見下例實際操作部分.
模塊五:Excel在成本管理崗位中的應用１７１四、實際操作(１)建立“成本分析”工作簿,將工作表Sheet１命名為“甲產品成本分析表”,並保存,如圖５Ｇ１所示.
圖５Ｇ１甲產品成本分析表(２)設計並輸入“××公司產品成本分析表”,並根據單位實際情況直接輸入直接材料、直接人工等信息,如圖５Ｇ２所示.
圖５Ｇ２××公司產品成本分析表１７２Excel在財務管理中的應用(３)選中B８單元格,輸入公式“＝SUM(B４:B７)”,將其填充複製到M８單元格,如圖５Ｇ３所示.
圖５Ｇ３××公司產品成本分析表(４)選中N４單元格,輸入公式“＝SUM(B４:M４)”,將其填充複製到N９單元格,如圖５Ｇ４所示.
圖５Ｇ４××公司產品成本分析表(５)選中B１０單元格,輸入公式“＝IF(B９＝０,＂＂,B８\/B９)”,將其填充複製到N１０單元格,如圖５Ｇ５所示.
模塊五:Excel在成本管理崗位中的應用１７３圖５Ｇ５××公司產品成本分析表(６)選中B１１單元格,輸入公式“＝IF(＄B＄９＝０,０,B４\/＄B＄８)”,並設置單元格格式為百分比,小數位數為２位.將其填充複製到N１４單元格,如圖５Ｇ６所示.
圖５Ｇ６××公司產品成本分析表注意:由於對單元格B８的引用為絕對引用,複製公式後,需將B８改為對應列的單元格(如C８、D８等),才可得出正確的計算結果.
(７)選中B１５單元格,輸入公式“＝SUM(B１１:B１４)”,將其填充複製到N９單元格,如圖１７４Excel在財務管理中的應用５Ｇ７所示.
圖５Ｇ７××公司產品成本分析表五、技能訓練根據所學知識,以任意產品為例,建立產品成本分析表.
模塊五:Excel在成本管理崗位中的應用１７５任務５Ｇ２建立產品成本分析圖表一、任務描述本任務以××公司甲產品為例,建立成本管理所需的成本分析圖表.
二、任務分析本任務主要以××公司生產的甲產品為例,建立成本管理所需的分析圖表(建議課時:２課時)三、相關知識建立分析圖表,具體使用方法參見下例實際操作部分.
四、實際操作(一)完成成本結構比率對比分析(１)選中A１７單元格,輸入標題“成本結構比率圖”.
(２)選中B１１:M１４單元格區域,選擇【插入】→【折線圖】→【帶數據標記的折線圖】命令,創建如圖５Ｇ８所示的折線圖,並移動至合適的位置.
１７６Excel在財務管理中的應用圖５Ｇ８成本結構比率圖(３)選擇【圖表工具】→【設計】命令,在“圖表布局”選項中選擇“布局１２”,如圖５Ｇ９所示.
圖５Ｇ９成本結構比率圖模塊五:Excel在成本管理崗位中的應用１７７(４)右擊圖表區內藍色線條,彈出菜單選擇→【設置數據係列格式】命令,打開對話框,切換到“線條”選項卡中,修改寬度“１磅”,如圖５Ｇ１０所示.同樣修改餘下的數據線條,如圖５Ｇ１１所示.
圖５Ｇ１０設置數據係列格式圖５Ｇ１１成本結構比率圖１７８Excel在財務管理中的應用(５)右擊選擇圖表區,彈出菜單選擇【設置圖表區域格式】→【發光和弱化邊緣】,選擇顏色為第５列第３行之後,單擊【關閉】按鈕,得到如圖５Ｇ１２所示效果.
圖５Ｇ１２成本結構比率圖(二)進行總成本與產量變動分析(１)選中A３６單元格,輸入標題“總成本與產量變動圖”.
(２)選中A８:M９單元格區域,選擇【插入】→【折線圖】→【帶數據標記的折線圖】命令,創建如圖５Ｇ１３所示折線圖,並移動至合適的位置.
圖５Ｇ１３總成本與產量變動圖模塊五:Excel在成本管理崗位中的應用１７９(３)選擇【圖表工具】→【設計】命令,在“圖表布局”選項中選擇“布局１２”,如圖５Ｇ１４所示.
圖５Ｇ１４總成本與產量變動圖(４)右擊圖表區內藍色線條,彈出菜單選擇【設置數據係列格式】命令,打開對話框,切換到“線條”選項卡中,修改寬度“１磅”,如圖５Ｇ１５所示.同樣修改餘下的數據線條,如圖５Ｇ１６所示.
圖５Ｇ１５設置數據係列格式１８０Excel在財務管理中的應用圖５Ｇ１６總成本與產量變動圖(５)右擊選擇圖表區,彈出菜單選擇【設置圖表區域格式】→【發光和弱化邊緣】,選擇顏色為第５列第３行之後,單擊【關閉】按鈕,得到如圖５Ｇ１７所示效果.
圖５Ｇ１７總成本與產量變動圖模塊五:Excel在成本管理崗位中的應用１８１(三)單位成本變動分析(１)選中A５５單元格,輸入標題“單位成本變動圖”.
(２)選中A１０:M１０單元格區域,選擇【插入】→【圖表】→【柱形圖】→【圓柱圖１】命令,創建如圖５Ｇ１８所示柱形圖,並移動至合適的位置.
圖５Ｇ１８單位成本變動圖(３)右擊選擇圖表區,彈出菜單選擇【設置圖表區域格式】→【發光和弱化邊緣】,選擇顏色為第５列第３行之後,單擊【關閉】按鈕,得到如圖５Ｇ１９所示效果.
圖５Ｇ１９單位成本變動圖１８２Excel在財務管理中的應用(四)年度成本結構圖(１)單擊單元格A７４,輸入標題“年度成本結構圖”.
(２)選中A１１:A１４和N１１:N１４區域,選擇【插入】→【圖表】→【餅圖】→【三維餅圖１】命令,創建如圖５Ｇ２０所示餅圖,並移動至合適的位置.
圖５Ｇ２０年度成本結構圖(３)選擇圖表後,點擊選擇【圖表工具】→【布局】,選擇【數據標簽】→【最佳匹配】,得到如圖５Ｇ２１所示效果.
圖５Ｇ２１年度成本結構圖模塊五:Excel在成本管理崗位中的應用１８３(４)右擊選擇圖表區,彈出菜單選擇【設置圖表區域格式】→【發光和弱化邊緣】,選擇顏色為第５列第３行之後,單擊【關閉】按鈕,得到如圖５Ｇ２２所示效果.
圖５Ｇ２２年度成本結構圖１８４Excel在財務管理中的應用任務５Ｇ３建立生產預算表一、任務描述根據××公司銷售預算表,建立生產預算表.
二、任務分析本任務主要根據××公司銷售預算表,建立生產預算表(建議課時:１課時).
三、相關知識數據輸入:具體數據輸入及表格設置等相關知識可以參閱模塊一.具體使用方法參見下例實際操作部分.
四、實際操作(１)根據銷售預算表,製作生產預算表.
(２)選中B２３單元格,輸入:＝B２０＋B２１－B２２;即:預計生產量＝預計銷售量＋預計期末庫存－預計期初庫存.
(３)選中B２３單元格,將其複製到C２３:F２３,得到如圖５Ｇ２３所示.
圖５Ｇ２３生產預算模塊五:Excel在成本管理崗位中的應用１８５任務５Ｇ４直接材料、直接人工預算一、任務描述根據××公司銷售及生產預算表,建立直接材料、直接人工預算表.
二、任務分析根據××公司銷售及生產預算表,建立直接材料、直接人工預算表.(建議課時:１課時)三、相關知識數據輸入:具體數據輸入及表格設置等相關知識可以參閱模塊一.具體使用方法參見下例實際操作部分.
四、實際操作(一)建立直接材料預算表格(１)建立直接材料預算表格,如圖５Ｇ２４所示.
圖５Ｇ２４直接材料預算表１８６Excel在財務管理中的應用(２)選中相應單元格區域,輸入計算公式,得到如圖５Ｇ２５所示.其中:生產需耗材料量＝預計生產量×單位產品材料耗用量預計采購量＝生產需耗材料量＋預計期末存貨－預計期初存貨材料采購金額＝預計采購量×材料單價圖５Ｇ２５直接材料預算表(二)建立直接人工預算表格(１)建立直接人工預算表格,如圖５Ｇ２６所示.
圖５Ｇ２６直接人工預算表模塊五:Excel在成本管理崗位中的應用１８７(２)選中相應單元格區域,輸入計算公式,得到如圖５Ｇ２７所示.其中:需耗直接人工總工時＝預計生產量×單位產品直接人工小時需耗直接人工總成本＝需耗直接人工總工時×小時直接人工成本圖５Ｇ２７直接人工預算表１８８Excel在財務管理中的應用任務５Ｇ５作業成本管理一、任務描述根據產品成本資料,采用作業成本中心和成本動因、傳統成本計算法計算兩種產品的單位成本,分別計算作業成本動因分配率,利用作業成本法計算兩種產品的製造費用,按作業成本法計算兩種產品的單位成本,兩種產品計算法下的總成本與單位成本對比.
二、任務分析根據產品成本管理資料,運用作業成本法計算兩種產品的單位成本、計算兩種產品計算法下的總成本與單位成本差異.(建議課時:２課時)三、相關知識作業成本理論框架;數據輸入:具體數據輸入及表格設置等相關知識可以參閱模塊一,具體使用方法參見下例實際操作部分.
四、實際操作(１)建立產品成本資料,利用作業成本中心和成本動因、傳統成本計算法計算兩種產品的單位成本.
①產品成本資料表格,如圖５Ｇ２８所示.
圖５Ｇ２８產品成本資料模塊五:Excel在成本管理崗位中的應用１８９②作業成本中心和成本動因表格,如圖５Ｇ２９所示.
圖５Ｇ２９作業成本中心和成本動因③用傳統成本計算法計算兩種產品的單位成本表格,如圖５Ｇ３０所示.
圖５Ｇ３０傳統成本計算法計算兩種產品的單位成本１９０Excel在財務管理中的應用(２)建立並填製作業成本管理所需計算分析表格:作業成本動因分配率、利用作業成本法計算的兩種產品的製造費用、按作業成本法計算兩種產品的單位成本、兩種產品計算法下的總成本與單位成本對比四份表格.
①建立作業成本動因分配率表格,如圖５Ｇ３１所示.
計算並填列作業成本分配率,如圖５Ｇ３２所示,分配率＝各作業金額÷作業量合計.
圖５Ｇ３１作業成本動因分配率圖５Ｇ３２作業成本動因分配率模塊五:Excel在成本管理崗位中的應用１９１②建立利用作業成本法計算的兩種產品的製造費用表格,如圖５Ｇ３３所示.
計算並填列兩種產品製造費用分配,如圖５Ｇ３４所示,分配金額＝作業量×分配率.
圖５Ｇ３３利用作業成本法計算的兩種產品的製造費用圖５Ｇ３４利用作業成本法計算的兩種產品的製造費用③建立按作業成本法計算的兩種產品的單位成本表格,如圖５Ｇ３５所示.計算並填列作業成本法核算的兩種產品的單位成本表格,如圖５Ｇ３６所示.其中:製造費用作業法＝相應兩種產品圖５Ｇ３１合計欄製造費用單位成本＝製造費用作業法金額÷產量合計(作業法)＝直接材料和人工成本合計(行５８)＋製造費用合計(行６０)１９２Excel在財務管理中的應用產品單位成本＝合計作業法(行６２)÷產量圖５Ｇ３５按作業成本法計算兩種產品的單位成本圖５Ｇ３６按作業成本法計算兩種產品的單位成本④建立兩種產品計算法下的總成本與單位成本對比表格,如圖５Ｇ３７所示.
模塊五:Excel在成本管理崗位中的應用１９３計算並填列兩種產品計算法下的總成本與單位成本對比,如圖５Ｇ３８所示.
圖５Ｇ３７兩種產品計算法下的總成本與單位成本對比圖５Ｇ３８兩種產品計算法下的總成本與單位成本對比１９４Excel在財務管理中的應用綜合實訓五一、實訓要求根據任務五××公司銷售及生產預算表、直接材料、直接人工預算表,進行現金預算.
二、實訓步驟(１)該公司應收賬款收回為當季度銷售收入收回７０％,其餘的第二季度收回.要求填列銷售預算中預期現金收入金額,得到如圖５Ｇ３９所示.
圖５Ｇ３９銷售預算(２)該公司應付賬款支付為當期材料采購金額６０％當期支付,其餘第二季度支付.要求填列直接材料預算表中預期現金支出部分,得到如圖５Ｇ４０所示.
模塊五:Excel在成本管理崗位中的應用１９５圖５Ｇ４０直接材料預算表(３)建立現金收支預算表,如圖５Ｇ４１所示.
圖５Ｇ４１現金收支預算１９６Excel在財務管理中的應用(４)填列現金收支預算表中相應的數值,得到如圖５Ｇ４２所示.其中:次季度的期初現金餘額＝上一季度的期末現金餘額銷售現金收入＝銷售預算中的現金收入合計可供使用現金＝期初現金餘額＋銷售現金收入直接材料＝直接材料預算表中的現金支出合計直接人工＝直接人工預算表中的需耗直接人工總成本現金支出合計＝直接材料＋直接人工＋製造費用＋銷售費用和管理費用＋所得稅＋購買設備＋股利現金需求總額＝現金支出合計＋最小現金餘額現金多餘或不足＝可供使用現金－現金需求總額融資合計＝向銀行借款－償還銀行借款－償還銀行利息期末現金餘額＝現金多餘或不足＋最小現金餘額＋融資合計圖５Ｇ４２現金收支預算模塊六:Excel在總賬會計崗位中的應用１９７模塊六:Excel在總賬會計崗位中的應用學習目標總賬處理是會計業務的核心.總賬處理是指從歸集原始憑證、編製記賬憑證開始,通過登賬、對賬、結賬等一係列會計核算處理,最終編製出會計報表的過程.通過本章學習,掌握財務人員在總賬處理工作中應具備的基本知識和操作技能,主要包括以下幾點:１．設置企業會計科目;２．編製記賬憑證;３．編製期初餘額表;４．登記明細賬;５．登記總賬;６．編製資產負債表和利潤表;７．進行財務分析.
本模塊建議課時:１０課時任務６Ｇ１設置企業會計科目一、任務描述本任務是利用Excel來設置企業會計科目編碼和科目名稱.
二、任務分析會計科目根據公司經營性質不同,其具體編號和科目名稱也有所不同,因此不同的企業可以在財政部頒布的企業會計製度的會計科目基礎上結合各自的實際情況來設置會計科目.本任務以某企業為例,參照我國?企業會計製度?,在Excel工作表中設置企業會計科目.(建議課時:１課時)１９８Excel在財務管理中的應用三、相關知識會計科目是會計核算的基礎和紐帶,一般設到三級,多的可以設到五級.會計科目分為會計科目代碼與會計科目名稱兩部分.其中,會計科目代碼是指會計科目統一規定的代碼,如一級會計科目代碼由行業會計製度確定,二級及以下的代碼可以結合本企業的情況自己設置.會計科目名稱應按企業製度的規定設置,不應隨意簡化.
四、實際操作創建會計科目表是為了在錄入記賬憑證時,通過選擇對應的科目代碼來自動輸入該代碼對應的科目名稱.會計科目表的製作非常簡單,隻需在其中輸入相應的科目代碼和對應的科目名稱即可,這裏為了以後方便查閱和引用,可為相應的類別創建超鏈接.其具體操作如下:(１)新建一個工作簿,將其保存為“總賬處理．xlsx”,將工作表“Sheet１”重命名為“會計科目”,並建立表單基本標識,如圖６Ｇ１所示.
圖６Ｇ１設置會計科目表基本標識(２)在“會計科目”工作表中,選中A４:A８９單元格區域,點擊右鍵,選擇“設置單元格格式”,如圖６Ｇ２所示.
模塊六:Excel在總賬會計崗位中的應用１９９圖６Ｇ２右鍵單擊“設置單元格格式”按鈕(３)在“設置單元格格式”對話框中,單擊“數字”選項卡,選擇“文本”選項,最後點擊“確定”按鈕,如圖６Ｇ３所示.
圖６Ｇ３設置科目代碼格式(４)在A４:B８９單元格區域中依次輸入科目代碼和對應的科目名稱,如圖６Ｇ４所示.
圖６Ｇ４輸入科目代碼和科目名稱２００Excel在財務管理中的應用(５)在打開的“插入超鏈接”對話框的“請鍵入單元格引用”文本框中輸入需鏈接的單元格區域“A４:B４２”,然後單擊“確定”按鈕如圖６Ｇ５所示.
圖６Ｇ５插入資產類的超鏈接(６)在工作表中單擊A２單元格中的超鏈接,將選擇資產類的所有會計科目.(７)用相同的方法為B２單元格中的文本超鏈接到A４３:B６５單元格區域,為C２單元格中的文本創建超鏈接到A６６:B７０單元格區域,為D２單元格中的文本創建超鏈接到A７１:B７４單元格區域,為E２單元格中的文本創建超鏈接到A７５:B８９單元格區域,如圖６Ｇ６所示.
圖６Ｇ６創建更多超鏈接五、技能訓練根據所學知識,以任意企業為例,建立企業科目表.
模塊六:Excel在總賬會計崗位中的應用２０１任務６Ｇ２記錄記賬憑證一、任務描述完成會計科目表的創建後,下麵將根據××公司本期發生的經濟業務數據錄入記賬憑證.
二、任務分析由於在賬務處理中,記賬憑證既要能概括地反映經濟業務的基本情況,同時又要能滿足登記賬簿的需要,所以它需包含憑證的填製日期和編號、經濟業務內容、會計科目、金額、借貸方向等相關內容.在記錄記賬憑證過程中將涉及數據有效性的設置、查詢函數的使用、條件格式的設置等操作.(建議課時:１課時)三、相關知識(一)記賬憑證記賬憑證是根據審核無誤的原始憑證編製的,用來確定會計分錄、作為記賬依據的一種會計憑證.
記賬憑證按其適用的經濟業務,分為通用記賬憑證和專用記賬憑證兩種.在經濟業務比較簡單的單位,為了簡化憑證可以采用通用記賬憑證記錄所發生的各種經濟業務.專用記賬憑證則是用來專門記錄某一類經濟業務的記賬憑證.專用記賬憑證按其記錄的經濟業務與現金和銀行存款的收付有無關係,又分為收款憑證、付款憑證和轉賬憑證.
(二)函數的引用１．IF函數IF(logical_test,value_if_true,value_if_false)執行真假值判斷,根據邏輯計算的真假值,返回不同結果logical_test:邏輯判斷真價值的表達式value_if_true:邏輯判斷為真時返回的值value_if_false:邏輯判斷為假時返回的值２．LOOKUP函數Lookup(lookup_value,lookup_vector,result_vector)在查找範圍中查詢指定的值,並返回另一個範圍中對應位置的值２０２Excel在財務管理中的應用lookup_value:查找值,可使用單元格引用、常量數組和內存數組lookup_vector:查找範圍result_vector:結果範圍四、實際操作(１)打開“總賬處理．xlsx”工作簿,將“Sheet２”工作表重命名為“記賬憑證”,並建立表單基本標識,如圖６Ｇ７所示.
圖６Ｇ７建立記賬憑證的基本框架(２)設置自動選擇科目代碼為了防止無效科目代碼的輸入,可以使用數據有效性功能建立序列來自動選擇科目代碼,具體操作如下:①選中G３單元格,在“數據”選項卡的“數據工具”組中單擊“數據有效性”按鈕,如圖６Ｇ８所示.
模塊六:Excel在總賬會計崗位中的應用２０３圖６Ｇ８單擊“數據有效性”按鈕②彈出“數據有效性”對話框,在“允許”下拉列表框中選擇“序列”選項,在“來源”文本框中輸入“＝會計科目!＄A＄４:＄A＄８９”,單擊“確定”按鈕,如圖６Ｇ９所示.
圖６Ｇ９設置數據有效性③設置G３單元格的數據有效性後,將其複製到下方單元格即可,如圖６Ｇ１０所示.
２０４Excel在財務管理中的應用圖６Ｇ１０填充G列數據有效性(３)設置根據科目代碼返回科目名稱.