第 9章 計算業績獎金 著作權所有 © 旗標出版股份有限公司.

Presentation on theme: "第 9章 計算業績獎金 著作權所有 © 旗標出版股份有限公司."— Presentation transcript:

1 第 9章 計算業績獎金 著作權所有 © 旗標出版股份有限公司

2 應用實例說明 精明能幹的業務員是一家公司不可或缺的重要角色, 儘管公司生產了品質優良的產品, 但若缺乏業務員將產品的特色與優點推廣給眾多客戶採用, 那麼再優良的產品也只能關在公司的倉庫中涼快。因此, 為了刺激業務員的士氣, 公司通常會訂定一套業績獎金發放標準, 以鼓舞表現傑出的業務員。

3 應用實例說明 發放業績獎金的方式有很多種, 譬如從業績金額當中提撥固定的百分比當作獎金, 或者規定每達到一個業績水準, 就可領到對應該水準的獎金, 另外也有論件計酬的方式, 也就是每成交一筆, 就固定可得到一個獎金數目…。

4 應用實例說明 不同的行業類別, 所制訂的業績獎金發放標準也不盡相同, 本章將探討兩種業績獎金發放的案例, 一種是「依產品銷售業績核算獎金」、一種是「依業績表現分成二部份計算業績獎金」, 看完之後, 您便可以將這些技巧運用到實際的情況中了。

5 應用實例說明

6 應用實例說明

7 本章提要 依銷售業績分段核算獎金 依業績表現分二部份計算獎金

8 依銷售業績分段核算獎金 公司行號為了有效激勵業務員衝刺業績, 時常會採取高業績伴隨高比例獎金的制度, 也就是說業績愈高, 就可以獲得愈高比例的獎金。不過也因為如此, 業績獎金的計算工作會顯得繁瑣多變。 本節要為您介紹的是依照銷售業績高低, 分段給予不同比例獎金的計算技巧。

9 依銷售業績分段核算獎金 現在, 請您開啟範例檔案 Ch09-01, 我們已經將宏瞻公司的獎金發放規則建立在獎金標準工作表當中：

10 依銷售業績分段核算獎金 在本例中, 業績獎金採用分段計算的方式, 當銷售業績在 10 萬元以下, 只可獲得業績的 10% 做為獎金；而若是業績介於 10 萬 ~ 15 萬之間,則 10 萬以下的部分可得 10% 的獎金, 超過 10 萬, 未滿 15 萬的部分則是可以得到 12% 的獎金, 依此類推…。

11 依銷售業績分段核算獎金 舉例來說, 如果某甲業務員的業績是 220,000 元, 那麼他可獲得的獎金就是：

12 依銷售業績分段核算獎金 累進差額的計算 計算獎金比例 計算實得業績獎金 查詢累進差額 改變獎金計算方式 命名的原則 定義名稱
HLOOKUP 函數的用法 求出獎金比例 查詢累進差額 何謂「名稱」 命名的原則 定義名稱 建立累進差額查詢公式 計算實得業績獎金 改變獎金計算方式

13 累進差額的計算 為了簡化獎金的計算工作, 我們必須在工作表中建立好計算的相關公式, 然後讓 Excel 自動根據銷售業績來計算應得的獎金。由於本例採用分段計算獎金的方式, 因此我們還需先將各階段獎金的累進差額計算出來。

14 累進差額的計算 這裡先為您說明「累進差額」的概念：當我們在申報綜合所得稅的時候, 就是採用累進稅率的方式, 依照您的所得淨額乘上應扣稅率, 然後再減去累進差額就是您所要繳納的稅額。分段計算業績獎金就跟計算所得稅相當類似, 只要將 "銷售業績 * 獎金比例 – 累進差額", 就是業務員可得到的獎金了。

15 累進差額的計算 請您選取 Ch09-01 獎金標準工作表的 D6 儲存格, 我們以計算第三段的累進差額為例來說明累進差額的計算方式：

16 累進差額的計算 其餘各段的累進差額計算公式, 請參考 B6：F6 儲存格當中的公式,這裡就不再贅述了。

17 計算獎金比例 請切換到 Ch09-01 的獎金計算工作表, 宏瞻公司每個業務員的銷售業績資料已經輸入在 B 欄當中, 現在我們要參照獎金標準工作表, 將對應的獎金比例填入獎金計算工作表的 C 欄。在此, 我們要使用到一個水平查表函數：HLOOKUP 。

18 HLOOKUP 函數的用法 HLOOKUP 函數可讓我們在表格的第一列中尋找含有某個值的欄位, 然後再傳回同一欄中所指定某一列儲存格的值。HLOOKUP 的格式為：

19 HLOOKUP 函數的用法 Lookup_value：就是搜尋範圍第一列中所要搜尋的值, 可以為數字、位址、或是文字。
Table_array：指定要搜尋的資料範圍, 也可以是一個定義好的儲存格範圍名稱 (有關名稱的使用, 稍後會有介紹)。 Row_index_num：是一個數值, 表示要傳回第幾列的值。

20 HLOOKUP 函數的用法 Range_lookup：為一個邏輯值, 可以用來決定是否要尋找完全符合的值。當此引數值為 TRUE 或是被省略的時候, 若找不到完全符合的值, 就傳回僅次於 Lookup_value 的值；而當此引數值為 FALSE 時, 則表示要尋找完全相符的值, 若找不到, 就會傳回錯誤值 #N/A。另外,當 Range_lookup 的值為 TRUE 時, Table_array 第一列中的數值必須按照遞增次序排列, 這樣搜尋結果才會正確。

21 HLOOKUP 函數的用法 以下面的例子來說, 在儲存格 B7 輸入公式 "=HLOOKUP(B6, B1：F4, 4, FALSE)" 的計算結果為 20：

22 HLOOKUP 函數的用法 由於 B6 的值為 "檸檬", 在 B1：F4 範圍中找到 "檸檬" 後, 在 "檸檬" 所屬的那一欄中, 第 4 列的值為 "100", 故 B7 公式的運算結果為 "100"。

23 求出獎金比例 了解 HLOOKUP 函數的用法之後, 我們回到獎金計算工作表, 開始依據 B 欄的 "銷售業績" 數字來查詢應得的 "獎金比例"。我們以在 C3儲存格求出第一個業務員的獎金比例來說明, 請在C 3 中輸入公式"=HLOOKUP (B3, 獎金標準!B4：F6, 2)"：

24 求出獎金比例

25 求出獎金比例 由於每個業務員的獎金比例求算方式都相同, 因此我們可以將 C3的公式複製到 C4：C30 當中, 請如下操作：

26 求出獎金比例

27 求出獎金比例 接著, 您可以按下格式工具列上的百分比樣式鈕, 讓 C3：C30 的數值改以百分比的樣式呈現。

28 查詢累進差額 累進差額的查詢方式與獎金比例相同, 一樣是使用 HLOOKUP 函數到獎金標準工作表中進行搜尋。不過, 這裡我們還要告訴您一個可以讓公式看起來更一目了然的技巧, 那就是在公式中使用名稱。

29 何謂「名稱」 截至目前為止, 我們都是使用儲存格位址來當作公式的運算元或函數的引數, 雖然可以直接指出計算的範圍, 但卻無法一目了然公式的用途。 所以, 假如我們為儲存格取一個好記且具有意義的名稱, 以後就可以直接用名稱代替儲存格位址, 使公式更易閱讀。以下圖為例：

30 何謂「名稱」

31 命名的原則 當我們為儲存格定義名稱時, 必須遵守下列的命名規則：
名稱的第一個字元必須是中文、英文、或底線 ( _ ) 字元。其餘字元則可以是英文、中文、數字、底線、句點 (.) 和問號 (?)。 名稱最多可達 255 個字元。但是請記住一個中文字就佔兩個字元。

32 命名的原則 名稱不能類似儲存格的位址, 如 B5、$A$3。 名稱不區分大小寫字母, 所以 MONEY 和 money 視為同一個名稱。

33 定義名稱 現在, 請切換到獎金標準工作表。B4：F6 是我們進行查表的儲存格範圍, 我們來練習為它取個好懂、容易記住的名稱吧！請選取 B4：F6, 然後按一下名稱方塊, 輸入 "查表範圍" 來做為它的名稱, 完成後請按下 [Enter] 鍵：

34 定義名稱

35 建立累進差額查詢公式 接著, 切換回獎金計算工作表, 選取 D3 儲存格開始輸入公式來查詢累進差額：

36 建立累進差額查詢公式 同一份活頁簿檔案中, 即使是不同的工作表,也不能定義相同的名稱。因此, 當我們在公式中使用名稱時, 可省略名稱所在的工作表, 因為事實上它是根據整份活頁簿所有定義的名稱來找尋參照來源。 拉曳 D3 的填滿控點至 D30, 則每個人的獎金累進差額就通通都查出來了：

37 建立累進差額查詢公式

38 「貼上名稱」與「刪除名稱」 若擔心在建立公式時不慎輸入錯誤的名稱, 可以執行『插入/名稱/貼上』命令, 由交談窗中選取欲插入的名稱：

39 「貼上名稱」與「刪除名稱」 假如定義的名稱用不到了, 想要將它刪除掉, 可執行『插入/名稱/定義』命令, 在定義名稱交談窗中選取欲刪除的名稱, 再按下刪除鈕即可。

40 「貼上名稱」與「刪除名稱」

41 計算實得業績獎金 知道各業務員可得的獎金比例及累進差額之後, 計算實得獎金就不是一樁難事了。我們先在 E3 儲存格算出業務員 "陳艾齡" 的業績獎金,即 "銷售業績"× "獎金比例"－"累進差額" (=B3*C3－D3), 然後將 E3的公式複製到 E4：E30, 則業績獎金的計算便大功告成了：

42 計算實得業績獎金

43 計算實得業績獎金 完成這張工作表之後, 請另存一份含有公式及獎金標準的空白檔案,以便未來每個月 (或固定的週期) 將業務員的銷售業績填入獎金計算工作表的 B 欄當中, 就會自動將可得的獎金比例、累進差額、業績獎金通通計算出來, 相當省事。而如果業績發放的標準有所變動, 只要到獎金標準工作表中修改數值, 便可按照新標準重新計算個員的業績獎金了。

44 改變獎金計算方式 由於銷售業績直接關係到個人的業績獎金, 所以業務員都會賣力的衝刺業績。
但有時候業務員難免會有投機或鬆懈的心理, 譬如說, 這個月的業績特別好, 抽了高比例的獎金, 到了下個月, 業務員可能就會鬆懈下來；或者, 業務員會蓄意將業績集中在某個月, 以衝高業績、得到較高的獎金比率…。

45 改變獎金計算方式 為了避免發生諸如此類的情況, 宏瞻公司決定依照實際狀況來變動部份遊戲規則。修改為將獎金的計算方式改成以 2 個月的平均銷售業績來做計算。 例如 2 月份的獎金就是根據 1、2 月平均業績來計算、3月份獎金則根據 2、3 月平均業績來計算…依此類推, 以督促業務員持續努力衝刺業績。

46 改變獎金計算方式 現在, 計算規則變了, 我們的工作表當然也要有所更動。其實並不困難, 只要在獎金計算工作表當中加上兩欄, 分別存放 "上月" 和 "本月" 業績, 然後算出這兩欄的平均之後, 再依照平均業績來查詢可得的獎金比例, 就可算出業務員的獎金啦！請您開啟範例檔案 Ch09-02, 然後跟著下面的說明來做修改：

47 改變獎金計算方式 選取獎金計算工作表的 B、C 兩欄, 並執行『插入/欄』命令：

48 改變獎金計算方式 分別在插入的兩欄中輸入 "上月" 及 "本月" 的業績, 然後將 D 欄改成 "兩月平均", 並建立公式計算 B、C 兩欄的平均：

49 改變獎金計算方式 假如在計算業績的時候, 想讓 "上月" 與 "本月" 的業績比重為 3：7 (也就是以本月表現為重, 但參考上月的表現), 則我們可將 D3 的公式改成“=B3*0.3+C3*0.7”, 再複製到 D4：D30。

50 改變獎金計算方式 等到下個月要來計算獎金的時候, 只要將 C 欄的 "本月" 業績複製到 B 欄的 "上月" 業績, 然後在 C 欄輸入新月份的業績數字, 就又可輕鬆完成獎金的計算工作了。您可開啟範例檔案 Ch09-03 來查看成果。

51 依業績表現分二部份計算獎金 本節的範例要來計算推廣健身房會員的業務員獎金, 此外還要運用計算出來的結果, 做進一步的分析工作, 譬如找出那些業務員具有潛力,值得公司好好栽培等等…。

52 依業績表現分二部份計算獎金 請開啟範例檔案 Ch09-04 , 業績標準工作表存放招幕會員業績獎金的發放標準, 一共分成兩階段來計算業績獎金, 第一階段採取論件計酬的方式, 也就是說只要成功推廣一人成為終身會員, 可得 500 元獎金、推廣一人成為 5 年期會員則得 100 元獎金, 之後再按照第一階段的結果核發第二階段的累進獎金。

53 依業績表現分二部份計算獎金 請切換到計算獎金工作表, 此工作表已建立好各區業務員的人名資料, 待會兒我們便要在此完成業績獎金的計算工作：

54 依業績表現分二部份計算獎金

55 依業績表現分二部份計算獎金 輸入到職日期 計算年資 計算第一階段獎金 計算第二階段獎金 更改日期顯示方式 四捨五入 LOOKUP 函數的用法
輸入第二階段獎金公式

56 依業績表現分二部份計算獎金 IF 函數與 VALUE 函數 修正計算公式 合計獎金 找出超級業務員 分區排序業績獎金 調整欄位寬度 後記

57 輸入到職日期 計算獎金工作表的 C 欄是用來存放業務員的到職日期, 我們先來看看如何在工作表中輸入日期資料。當您在儲存格中輸入日期 (或時間)資料時, 必須以 Excel 能接受的格式輸入才會被當作是日期 (或時間), 否則會被當成文字資料。 底下列舉 Excel 所能接受的日期格式：

58 輸入到職日期

59 輸入到職日期 由於 Excel 預設使用西元年份,因此輸入日期時, 記得年份必須使用西元年。
格中輸入"1998/9/10", 並按下 [Enter] 鍵：

60 輸入到職日期 接著請你仿照相同的方法, 將所有業務員的到職日期輸入進去：

61 輸入到職日期 若想要直接輸入民國年份, 例如 "93/9/18",而不會被 Excel 判斷成 1993/9/18, 那麼請在輸入日期的最前面加上 "R" (文字與數字間不能空格), 例如 "R93/9/18" 即表示民國93 年 9 月 18 日。

62 更改日期顯示方式 輸入完成後, 您可以依照自己的需要更改日期的顯示方式, 例如要將西元年改成以民國方式顯示, 則請選取 C3：C13 然後執行『格式/儲存格』命令, 並切換到數值頁次：

63 更改日期顯示方式

64 更改日期顯示方式

65 計算年資 年資的計算就是將目前的日期減去到職日期, 表示這段期間的天數再除以一年 365 天, 即可求出。以計算 "劉珮珊" 的年資為例, 請在 D3 儲存格中輸入公式 "=(TODAY( )-C3)/365"：

66 計算年資 接著將 D3 的公式拉曳複製到 D13, 則每個人的年資就都計算出來了。

67 四捨五入 算出年資之後, 我們再利用 ROUND 函數做四捨五入, 以增加報表的美觀與易讀性：

68 ROUND 函數的用法 ROUND 函數可按照您指定的位數, 將數字四捨五入。其格式如下：
當 Num_digits 大於 0 時, 數字會被四捨五入到指定的小數位數, 例如：ROUND (25.34,1) = 25.3。

69 ROUND 函數的用法 當 Num_digits 等於 0 時, 數字會被四捨五入到整數, 例如：ROUND (55.76,0) = 56。

70 計算第一階段獎金 現在, 我們要開始計算業績獎金囉！首先是將每個業務員推動了幾位終身會員、幾位 5 年期會員的資料輸入到計算獎金的 E、F 欄當中,然後就可以來計算第一階段的獎金了。請開啟範例檔案 Ch09-05：

71 計算第一階段獎金

72 計算第一階段獎金 第一階段的獎金必須參照到業績標準工作表中的內容, 以計算 "劉珮珊" 第一階段的獎金為例, 請在 G3 儲存格中輸入公式 "=E3*業績標準!D3+F3*業績標準!D4"：

73 計算第一階段獎金

74 計算第一階段獎金 接著, 請將公式中的 D3、D4 改成混合參照位址 "D$3"、"D$4", 然後把公式複製到 G4：G13, 即可完成第一階段獎金的計算工作。

75 計算第二階段獎金 剛才已根據業務員的終身會員、5 年期會員人數算出第一階段的獎金, 接下來, 還要依據第一階段的獎金來加發第二階段的累進獎金。 請開啟範例檔案 Ch09-06：

76 計算第二階段獎金

77 計算第二階段獎金 在計算之前, 我們必須先來認識一下 LOOKUP 函數, 因為待會兒就是要用此函數來幫我們查詢出每個業務員可得到多少第二階段的獎金。

78 LOOKUP 函數的用法 LOOKUP 函數會在單一欄 (或單一列) 的範圍中尋找指定的搜尋值,然後傳回另一個單一欄 (或單一列) 範圍中同一個位置的值。LOOKUP函數的格式如下：

79 LOOKUP 函數的用法 Lookup_value：即為所要尋找的值。Lookup_value 可以是文字、數字、邏輯值等。
Lookup_vector：是個單一列或單一欄的儲存格範圍。若 Lookup_vector 中的值是數字, 則須以遞增的次序排列, 否則結果會不正確。 Res ult _ vec tor：是個單一列或單一欄的範圍。它的大小要與Lookup_vector 相同。

80 LOOKUP 函數的用法 以下圖而言, 在儲存格 C2 中輸入公式 "=LOOKUP(B2, A$9：A$12,B$9：B$12)" 的計算結果為 "甲"：

81 LOOKUP 函數的用法

82 輸入第二階段獎金公式 明白 LOOKUP 函數的用法後, 我們開始在 Ch09-06 計算獎金工作表的 H3 輸入計算公式吧！

83 輸入第二階段獎金公式

84 輸入第二階段獎金公式 建議您養成習慣, 在輸入公式時便將儲存格參照的方式設定好, 以避免在複製公式時出錯。
當 LOOKUP 函數無法在查詢範圍中找到完全符合的值時, 會找出最接近但不超過的值。例如：業務員 "劉珮珊" 第一階段的獎金為13,500, 介於 12,000~20,000 之間, 因此會採用 12,000 而找出對應 12,000的第二階段獎金為 8,000。

85 輸入第二階段獎金公式 接著, 請將 H3 的公式複製到 H4：H13 當中：

86 輸入第二階段獎金公式 發生 "#N/A" 錯誤的原因是因為當搜尋值小於搜尋範圍中的最小值時, LOOKUP 函數就會傳回錯誤值。例如 "王勝良" 第一階段的獎金為3,700 元, 少於第二階段最低業績標準 5,000 (即業績標準工作表的 C7 儲存格), 因此會傳回 "#N/A" 錯誤訊息。

87 IF 函數與 VALUE 函數 為了不讓工作表出現錯誤訊息, 我們可以在第二階段獎金的計算公式中加入使用 IF 函數與 VALUE 函數。底下先簡介 IF 函數和 VALUE 函數的用法。 IF 函數可用來判斷測試條件是否成立, 如果傳回的值為 TRUE 時, 就執行條件成立時的作業, 反之則執行條件不成立時的作業。IF 函數的格式為：

88 IF 函數與 VALUE 函數 VALUE 函數可將文字資料轉換成數字資料。VALUE 函數的格式為：

89 修正計算公式 現在, 請您將 H3 的公式修改為 "=IF(G3<5000, VALUE(0), LOOKUP(G3, 業績標準!C$7：C$11, 業績標準!D$7：D$11))", 然後拉曳複製公式至 H13, 這樣就可以消除 "#N/A" 的錯誤訊息了：

90 修正計算公式

91 合計獎金 既然兩個階段的獎金都算出來了, 接著就可以來計算 I 欄的 "獎金合計" 囉！要將第一、二階段的獎金相加, 相信這對你來說應該很容易吧！ 您可以在 I3 儲存格中輸入公式 "=G3+H3", 然後複製公式至 I13, 則整個業績獎金的計算作業就完成了：

92 合計獎金

93 合計獎金 在這份工作表中, 您也可以依各公司敘薪方式做調整, 例如, 增加 "本薪"、"加給" 的欄位 (如果加給是以年資為標準, 可另外再建立一個年資加給的工作表以供查表…), 則此工作表不但可以計算業績獎金, 還可以直接將當月應付薪資都計算出來哦！

94 找出超級業務員 業績獎金計算出來就滿足了嗎？其實我們還可以藉由這些計算結果做更進一步的分析、獲得更多寶貴的資訊喔！譬如, 我們可以找出資歷很淺, 但是業績表現卻很出色的業務員, 日後可多加栽培訓練。 請您開啟範例檔案 Ch09-07, 然後在計算獎金工作表執行『資料/篩選/自動篩選』命令,現在我們要來找出年資小於 5 年, 但領到 20,000元以上獎金的優秀業務員：

95 找出超級業務員 拉下 "年資" 欄的自動篩選鈕, 選擇自訂：

96 找出超級業務員 在開啟的自訂自動篩選交談窗中做如下的設定：

97 找出超級業務員 從清單中篩選出年資小於 5 年的記錄：

98 找出超級業務員 接著使用相同的方法, 拉下 "獎金合計" 欄的自動篩選鈕, 選擇自訂項目, 然後在自訂自動篩選交談窗中設定條件為大於、20000, 則我們所要的結果便篩選出來了：

99 找出超級業務員 另外, 您也可以利用篩選功能, 分區將業務員的資料顯示出來, 或者是篩選出終身會員人數大於某數量的業務員…等等, 就看你的需求來靈活應用囉！

100 分區排序業績獎金 假設, 現在我們又想將同一地區的業務員資料排在一塊, 並且按照獎金高低由大排到小, 那麼該怎麼做呢？
請您在計算獎金工作表中執行『資料/篩選/自動篩選』命令, 移除自動篩選鈕, 然後選取清單中的任一個儲存格, 執行『資料/排序』命令, 開啟排序交談窗：

101 分區排序業績獎金

102 分區排序業績獎金

103 調整欄位寬度 這張工作表中的部分欄位名稱較長, 除了可以分成兩列來顯示外, 還可以使用縮小字型以適合欄寬功能, 讓儲存格內容隨著欄寬而自動改變字型大小。 請選取計算獎金工作表中的 E2：H2, 然後按右鈕執行快顯功能表中的『儲存格格式』命令, 開啟儲存格格式交談窗：

104 調整欄位寬度

105 調整欄位寬度 接著, 請您將 E2、H2 的欄寬調小, 您將會發現字型大小會隨著欄寬而自動調整：