Section A1: All staff members receive an annual bonus of $200 plus an additional

Question

Section A1: All staff members receive an annual bonus of $200 plus an additional percentage of their Annual Income. Each staff member has been allocated their own percentage bonus rate (column C). In D6:D16 calculate each staff members Bonus

Section A2: Using the % Superannuation given to all staff. Calculate the annual Super amount paid to each staff member using the value in 9.5%. Copy the formula down to the last cell (to get full marks, a named range or an absolute cell reference must be used).

Section A3: In 'total package' calculate the total package for each staff member (Annual Salary, Bonus and Super). Adjust the spreadsheet so that the “#####” problem is addressed.

Section A4: Find total labour cost

Section A5: Inserest a row under the total labour cost and find the average total package

Section A6: use a formula to calculate the highest Total Package paid to an individual staff member (i.e. the biggest total package)

PLEASE PROVIDE AN EXPLAINATIONS during each step

Table is provided below.

Employee Number Annual Bonus Rate Bonus Amount Annual Super Total Package 10026 $ 49,283.00 1% 10027 $ 33,968.00 2% 10030 $ 32,158.00 2% 10032 $ 45,435.00 0% 10033 $ 51,722.00 0% 10034 $ 42,040.00 2% 10035 $ 44,161.00 1% 10036 $ 41,368.00 3% 10037 $ 57,029.00 2% 10038 $ 33,193.00 1% 10039 $ 37,410.00 0% Total Super% 9.50% Highest

Explanation / Answer

Column A Column B Column C Column D Column E Column F Employee Number Annual Bonus Rate Bonus Amount ($200 + (Annual*Bonus Rate)) Annual Super (Annual + Bonus Amount)*9.50% Total Package (Annual + Bonus Amount + Annual Super) 10026 $49,283 1% $692.83 $4,747.70 $54,723.53 10027 $33,968 2% $879.36 $3,310.50 $38,157.86 10030 $32,158 2% $843.16 $3,135.11 $36,136.27 10032 $45,435 0% $200.00 $4,335.33 $49,970.33 10033 $51,722 0% $200.00 $4,932.59 $56,854.59 10034 $42,040 2% $1,040.80 $4,092.68 $47,173.48 10035 $44,161 1% $641.61 $4,256.25 $49,058.86 10036 $41,368 3% $1,441.04 $4,066.86 $46,875.90 10037 $57,029 2% $1,340.58 $5,545.11 $63,914.69 10038 $33,193 1% $531.93 $3,203.87 $36,928.80 10039 $37,410 0% $200.00 $3,572.95 $41,182.95 Total $520,977.25 Average AVERAGE(F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13) $47,361.57 Highest Package MAX(F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13) $63,914.69 Annual Super calculation is calculated on ordinary time earnings which includes normal package plus additional performance bonus paid to employees

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.