第 4 章 分析損益表 著作權所有 © 旗標出版股份有限公司

應用實例說明 追求利潤是企業經營的主要目標之一, 而評估經營成果的依據, 就是企業建立好的各類財務報表。雖然每家企業皆有其獨特且適合自己的會計系統, 但是所建立的財務報表內容卻相去不遠。 本章就要從會計的角度, 看看如何利用 Excel 建立公司行號常用的會計報表 － 損益表。

應用實例說明 旗旗公司最近成功地完成了全面電腦化的工作, 公司的高階主管希望財務單位能好好利用現有資源, 盡快將公司歷年來的財務資料建立成電腦檔案。這對於精通會計, 卻對 Excel 一竅不通的曾先生來說, 真可說是一大挑戰呢！ 我們就來看看如何幫曾先生完成如下的損益表和比較損益表吧！

應用實例說明

應用實例說明

本章提要 建立損益表 美化報表的外觀 為儲存格加上註解 建立年度費用支出圖表 簡單的圖表編輯 複製損益表 計算彙總結果

建立損益表 建立損益表資料 建立簡單的計算公式 銷貨收入淨額 銷貨成本淨額 銷貨毛利 營業費用總額 費用總額 本期損益

建立損益表資料 首先, 我們要在一張全新的工作表上輸入損益表的資料, 並將欄位調整到適當的寬度。 您也可以直接開啟已經輸入完成的範例檔案 Ch04-01：

建立損益表資料 緊接著, 將數字資料一一輸入到對應的儲存格中：

建立簡單的計算公式 輸入完損益表的文字和數字資料後, 我們就可以建立公式計算出銷貨收入淨額、銷貨成本淨額、銷貨毛利、營業費用總額、費用總額及本期損益了。 如果您沒有依上列步驟輸入損益表資料, 亦可開啟範例檔案 Ch04-02, 來接續進行以下的練習：

銷貨收入淨額 『銷貨收入淨額』是由『銷貨收入』減去『銷貨退回』而來, 所以請在儲存格 C5 輸入如下的公式, 完成後請按下資料編輯列的輸入鈕或 [Enter] 鍵：

銷貨成本淨額 『銷貨成本淨額』則是將各類銷貨方面的成本相加, 因此我們可以如下計算：

銷貨成本淨額

銷貨成本淨額

銷貨毛利 用『銷貨收入淨額』減去『銷貨成本淨額』 所得到的結果, 就是本年度的 『銷貨毛利』 , 我們可以這樣計算：

營業費用總額 再來我們要計算營業費用的總額, 只要將各類營業費用加總, 就可以計算出『營業費用總額』了。請選定儲存格 C19, 然後利用自動加總鈕輸入公式 "= SUM (B13：B18)"：

營業費用總額

費用總額 由於我們的支出費用只有營業費用一種, 所以『費用總額』就等於『營業費用總額』。 我們可以用儲存格參照的方式, 將 C19 與 C20 設定成參照, 請選定儲存格 C20：

費用總額

本期損益 『收入』減去『費用』就等於 本期損益』, 因此我們可以得到一個公式：本期損益 = 銷貨毛利 – 費用總額。請選取儲存格 C21, 並輸入公式 "=C10 – C20", 即可計算出本年度的淨利：

美化報表的外觀 加入報表標題 設定數字格式 調整欄寬 加上框線

加入報表標題 首先我們要為報表加上標題及相關資訊, 例如：公司名稱、報表名稱, 以及損益表的記錄期間, 以便讓公司內外的相關人士, 可以很快的瞭解這份損益表是哪段期間的營運狀況。請開啟範例檔案 Ch04-03： 請選取 1 到 3 列, 然後在選取範圍區中按下滑鼠右鈕, 選擇『插入』命令：

加入報表標題

加入報表標題 分別在新增的 1 到 3列中輸入如下的標題：

加入報表標題 輸入完成後, 請選定 A1：C1, 再按下跨欄置中鈕, 將第 1 個標題置中對齊：

加入報表標題 請利用同樣的方式, 分別將第 2 及第 3 個標題也置中對齊：

設定跨欄置中的注意事項 在將標題設定成跨欄置中時, 若要設定一個以上的標題, 請務必將標題分別設定。以上例來說, 如果我們直接選取 A1：C3 的儲存格範圍, 並按下跨欄置中鈕, 那麼會得到如下的結果：

設定跨欄置中的注意事項

設定跨欄置中的注意事項 那是因為跨欄置中只會保留最左上角的儲存格資料, 並在設定時出現如下的警告訊息：

設定數字格式 既然是財務報表, 報表內的數字就該設定成一般常用的會計表示方法。請選定 B6：C24 的儲存格範圍, 然後按滑鼠右鈕選擇『儲存格格式』命令：

設定數字格式

設定數字格式

利用『貨幣樣式』鈕 設定數字格式 可以在儲存格格式 / 數值交談窗設定格式外, 還有一個快速設定數字格式的方法要介紹給您, 請先選定要設定的儲存格範圍：

利用『貨幣樣式』鈕 設定數字格式 按下格式工具列的增加小數位數鈕則可增加小數位數。

調整欄寬 為了讓這張損益表閱讀起來更舒適, 我們可以將欄寬加大些, 列印起來也較不會有資料都擠在一起的感覺。 請開啟範例檔案 Ch04-04, 同時選定 A、B、C 欄, 然後按右鈕選擇『欄寬』命令, 將欄寬調整成 20：

調整欄寬

加上框線 接下來要進行美化工作表的最後一個步驟, 為損益表加上外框。請選定 A1：C24 的儲存格範圍, 按右鈕選擇『儲存格格式』命令, 並切換至外框頁次：

加上框線

加上框線

加上框線 另外, 若希望將損益表的每個類別加以區別, 可在收入、銷貨成本、銷貨毛利、費用、費用總額及本期損益列前分別插入一空白列, 讓損益表更容易閱讀：

加上框線

為儲存格加上註解 曾先生想為這份損益表加上一些輔助說明, 但又不想讓這些文字直接顯示在損益表上, 破壞表格的完整性, 這時, Excel 的註解功能就派上用場了。 加上註解 檢視註解 修改註解

加上註解 由於這份損益表在費用部份, 只有營業費用一項, 為了避免主管誤以為是會計部門漏計了其它的費用, 因此我們可以在費用的儲存格加上註解。 請開啟範例檔案 Ch04-05：

加上註解 選取要加上註解的儲存格 A18, 然後按右鈕選擇『插入註解』命令, 儲存格附近便會出現註解圖文框：

加上註解 在註解圖文框中輸入註解的內容：

加上註解 拉曳註解圖文框的控點可調整圖文框的大小；拉曳邊框則可移動註解的位置。 輸入完畢後, 請用滑鼠點選圖文框以外的地方即可完成, 且註解會自動被隱藏起來。

設定使用者資訊 加上註解時, 註解圖文框上會自動顯示目前使用者的名稱, 而此處的名稱是可以更改的, 您可以執行 『工具/選項』 命令, 並切換至一般頁次中來設定：

設定使用者資訊

檢視註解 雖然註解輸入完後會自動被隱藏起來, 但我們仍可以在插入註解的儲存格右上角, 看到一個紅色的註解指標, 提醒我們這個儲存格中有註解。 我們只要將指標移到有註解指標的儲存格上 (不需選取儲存格), 註解便會自動顯示出來：

檢視註解

修改註解 註解的內容似乎有點不太完善, 應該要再修改一下才行。 請選定註解指標所在的儲存格, 然後按下右鈕選擇『編輯註解』命令：

修改註解 在 "…支出" 之前加上 "相關" 二字： 修改完畢後, 用滑鼠點選圖文框以外的地方即可。

刪除註解 若要刪除註解, 只須選取欲刪除註解的儲存格, 再按右鈕選擇『刪除註解』命令, 就可以將之刪除了。 若只將註解圖文框中的文字刪除, 註解圖文框仍會存在, 只是變成一個空的註解而已, 所以請務必以執行命令的方式來刪除註解。

建立年度費用支出圖表 圖表有時可以充份表現出文字所無法展現的效果, 彌補數字的不足,因此報表中利用圖表來說明, 也是很常見的一種表達方式。損益表主要是用來表達企業在某一段時間收入與費用的真實狀況, 但是太多的數據資料對決策來說, 卻沒有太大的助益, 若能將數字轉換成分析圖表, 就能讓管理階層對於公司的營運狀況一目了然。

建立年度費用支出圖表 例如管理階層想知道營業費用到底用在何處？各項費用所佔的比例又是多少？ 這時, 我們就可以利用損益表的費用數據, 來建立一份能清楚表達的圖表。 快速建立圖表 變更圖表類型 自訂預設圖表類型

快速建立圖表 請開啟範例檔案 Ch04-06, 由於我們要繪製的是『各類費用使用情形』, 所以只要選定費用的範圍 (A20：B25) 即可：

快速建立圖表

快速建立圖表

變更圖表類型 建立好圖表後, 我們就可以從圖表中看出營業費用的使用情形了。如果覺得直條圖不能表現出費用的分配情形, 可利用圖表工具列的圖表類型鈕 來變更圖表：

變更圖表類型

自訂預設圖表類型 如果真的覺得圓形圖最能充分表達統計結果, 我們可以將圓形圖設定成預設的圖表類型, 下次再按 [F11] 鍵來快速產生圖表時, 建立的就會是圓形圖了。 設定時, 請先選取工作表中的圖表或切換至圖表文件, 然後執行『圖表/ 圖表類型』命令：

自訂預設圖表類型

自訂預設圖表類型

簡單的圖表編輯 圖表的樣子是大略完成了, 但是圖表上只有圖例文字和一個圓形圖,似乎還不能確實表達各項費用所佔的比例；且圖例的文字太小, 對照圖形時易造成閱讀的困擾。 因此, 接下來我們要對圖表進行一些簡單的編輯工作, 讓圖表更能傳達出閱讀者所想要的訊息。

簡單的圖表編輯 調整圖例文字 調整圖例位置及底色 加入資料標籤 調整資料標籤的字型大小

調整圖例文字 我們先將圖例文字調整到 18 點的大小。請開啟範例檔案 Ch04-07,並切換至 Chart1 工作表, 然後如下操作：

調整圖例文字

調整圖例文字

選取圖表物件 利用工具提示來選取：當指標移至圖表內, 指標旁會出現一方框, 顯示目前所指到的物件名稱, 只要將指標移至想要選取的圖表物件上, 再按一下滑鼠左鈕即可選取該物件。

選取圖表物件 利用圖表物件列示窗來選取：拉下圖表工具列上的圖表物件列示窗,亦可輕鬆選取想要的圖表物件。 若您沒有看到圖表工具列, 可執行『檢視/工具列/圖表』命令將其顯示出來。

調整圖例位置及底色 我們在前面有提到, 選定圖例物件後, 在範圍內直接拉曳可調整圖例的位置, 但是如果希望圖例能顯示在圖形的下方, 拉曳後將會產生如下的情形：

調整圖例位置及底色

調整圖例位置及底色 請按下復原鈕將圖例復原至右方的位置 (或開啟範例檔案 Ch04-08, 並切換至 Chart1 工作表), 進行以下調整圖例位置的練習：

調整圖例位置及底色

調整圖例位置及底色

調整圖例位置及底色

加入資料標籤 圖表裡光有圖例, 卻沒有一個明確的標題, 也沒有每個圖例項目所代表的比例, 這樣是不夠的。幸好 Excel 可以讓我們在圖表中加入資料標籤, 做為每個圖例的補充說明；也可以輕易地插入圖表標題, 讓我們一眼就看出這張圖表所要表達的重點。 現在請就接續上例, 為圖表加上標題及資料標籤吧！

加入資料標籤

加入資料標籤

加入資料標籤

加入資料標籤

調整資料標籤的字型大小 最後, 將資料標籤的字型調整一下：

調整資料標籤的字型大小

調整資料標籤的字型大小 您還可以自行調整不滿意的地方, 例如拉曳圖表的繪圖區, 將其調整至圖表區的中央, 再將圖表標題的字型調整成 36 點大小, 並拉曳到圖表的左上方, 就可以呈現出如下的成果了：

調整資料標籤的字型大小 拉曳繪圖區控點可改變大小；直接拉曳繪圖區可改變位置。

複製損益表 我們的目標是要建立公司 90 年至 92 年間的財務報表, 在完成 90 年的年度損益表後, 要製作往後幾年的損益表就簡單多了。因為公司的財務報表格式是相同的, 只是數據資料有差異而已, 所以我們可以用複製的方式, 將 90 年的損益表複製到其他工作表中, 然後再修改其中的相關資料, 以建立其他年度的損益表。

複製損益表 複製損益表 調整損益表的內容 新增工作表 調整工作表的位置 為工作表命名 變更頁次標籤色彩 刪除工作表

複製損益表 現在我們就把 90 年的損益表, 複製到另一個工作表中。請開啟範例檔案 Ch04-09, 並切換至 Sheet1 工作表, 然後如下操作： 選取 A1：C30 的儲存格範圍, 再按下一般工具列的複製鈕。 然後按一下 Sheet2 頁次標籤, 切換至 Sheet2 工作表。 選定 A1 儲存格, 再按下一般工具列的貼上鈕：

複製損益表

複製損益表

調整損益表的內容 複製好損益表後, 接著將 A3 儲存格的資料內容修改為 "91/1/1 至 91/12/31", 並依右表更新 91 年的損益表資料：

調整損益表的內容 當我們輸入資料時, C 欄的結果也會重新計算, 那是因為剛才複製的動作中, 將公式也一併複製過來了, 所以當我們將資料更新完畢, 91 年度的損益表也同步完成了。

調整損益表的內容

新增工作表 Excel 預設只有三張工作表, 當我們依序建立 90 年、91 年、92 年的損益表後, 就沒有多餘的工作表可以使用了。別擔心！Excel 允許我們自行新增工作表。請開啟範例檔案 Ch04-10, 並切換至 Sheet3 工作表：

調整工作表的位置 接著, 我們要將工作表調整到正確的位置。請直接拉曳 Sheet4 頁次標籤到 Sheet3 頁次標籤的右邊：

為工作表命名 Excel 預設會將工作表以 Sheet1、Sheet2…依序命名, 但這樣的名稱通常沒有什麼意義, 所以我們要將工作表取個實用又有意義的名稱。 我們希望可以從工作表的名稱, 清楚地分辨出該工作表是屬於哪一個年度, 因此將以報表的年度做為命名依據, 請雙按 Sheet1 頁次標籤, 讓它呈現反白狀態：

為工作表命名 請依序將 S h e e t 2 、Sheet3、Sheet4 工作表, 分別命名為 91 年、92 年、93 年：

變更頁次標籤色彩 若在頁次標籤上按右鈕, 選擇快顯功能表上的『索引標籤色彩』命令, 再選取想要的顏色, 則可更改頁次標籤的色彩。設定好後, 只要切換到其它工作表, 就可以看到設定完成的標籤色彩了。

刪除工作表 除了適時新增工作表、更新資料外, 對於不再需要的工作表, 也要將其刪除, 以免活頁簿存在太多不必要的資料而顯得雜亂。 例如要將尚未建立資料的 93 年工作表刪除, 只要在頁次標籤上按滑鼠右鈕, 並選擇『刪除』命令即可。

刪除已輸入資料的工作表 如果您要將已輸入資料的工作表刪除, 選擇『刪除』命令後則會出現如下的確認訊息：

計算彙總結果 整理完歷年來的損益表後, 這份財務報表似乎還欠缺了什麼。雖然我們建立了每一年的年度報表, 但除了個別的資料外, 應該要建立一些彙總的資料, 才能做為調整營運方針的參考。只要將歷年來的財務狀況做個彙總, 就可以從這些資料中了解公司的財務體系了, 因此我們要選擇損益表中幾個重要的項目, 做一份彙總性的比較報表 － 比較損益表。

計算彙總結果 由於比較損益表是屬於較高層的彙總性報表, 其資料是來自於各個年度損益表的計算結果, 所以我們必須將各期損益表和比較損益表之間做個連結, 把歷年的損益表資料『搬移』到比較損益表中。

計算彙總結果 建立比較損益表表格 儲存格參照 計算比例

建立比較損益表表格 在彙總資料之前, 我們必需先建立比較損益表的表格。 請執行『插入/工作表』命令, 新增一張工作表, 然後將其搬移到最後的位置,並命名為比較損益, 再如下圖建立損益表的表格：

建立比較損益表表格

儲存格參照 建立好表格後, 接下來的工作是將資料填入儲存格中。前面提過,由於比較損益表的資料來自於各年度的損益表, 因此, 我們要從各期損益表中, 將需要的資料『參照』過來。 如果您尚未依照以上步驟建立好比較損益表的表格, 亦可開啟範例檔案 Ch04-11, 並切換至比較損益工作表來練習。

儲存格參照

儲存格參照

儲存格參照

參照與複製的差別 進行了以上參照的步驟後, 我們發現參照的結果與 90 年 C9 儲存格的內容一模一樣, 那我們為什麼不直接用複製、貼上的方式就好了, 而要採用較複雜的參照動作呢？ 這可從兩個方面來說明：

參照與複製的差別 如果我們利用一般工具列上複製、貼上鈕, 將 90 年 C9 的資料複製到比較損益工作表的 B6, 那麼 Excel 並不會複製儲存格內的數值, 而是將公式複製到比較損益工作表中, 且還會因貼上的儲存格不同, 自動做對應的調整, 結果當然就和我們想要的不同了：

參照與複製的差別

參照與複製的差別

參照與複製的差別 另一個原因, 如果是執行『編輯/選擇性貼上』命令, 只選擇將 90 年 C9儲存格的值貼到比較損益的 B6, 雖然複製的結果是正確的, 不過當 90 年 C9 的資料改變時, 比較損益的 B6 並不會同時更新, 同樣會造成資料錯誤的困擾。

參照與複製的差別 使用『參照』的方式, 就可以避免以上可能發生的錯誤了。 因為設定參照是對應到某一個工作表的某個儲存格, 相當於 『分身』 的效果, 無論對原始儲存格做任何的改變, 參照的儲存格也會出現相同的結果。

儲存格參照 接續上例, 請用同樣的方式, 將三個會計年度的收入、銷貨成本、銷貨毛利、費用及比較損益建立起來：

儲存格參照

計算比例 建立比較損益表的目的, 是希望能夠了解公司的經營狀況, 好藉此調整營運方針。現在我們已經計算出各項目的總額, 也能快速得知這些數據的增減情形, 但對於決策者說, 若能計算出 "各項目與總收入的相對比例", 來做為提列下一年度預算時的參考數值, 將會更有意義。

計算比例 接著我們就來完成比較損益表中, 關係到營運成果的各項百分比吧！ 請開啟範例檔案 Ch04-12, 並切換至比較損益工作表：

計算比例

計算比例

計算比例 輸入公式後按下 [Enter] 鍵即可求得結果。接著, 請分別在 E10、E12儲存格輸入公式"=B10/B6"、"=B12/B6", 求算出費用、損益兩個項目所佔的百分比, 再用相同的方法, 算出 91、92 兩個年度各項目所佔的百分比：

計算比例

設定百分比格式 最後, 我們要再將數字設定為百分比格式。請選取欲設定的範圍 E7：G12, 然後執行『格式/儲存格』命令, 開啟儲存格格式交談窗：

設定百分比格式

設定百分比格式