34 1 2 Bonus Allocation A 8 69 567 2345 B C Salary Analysis Employee Units...

Transcribed Image Text:

34 1 2 Bonus Allocation A 8 69 567 2345 B C Salary Analysis Employee Units Number Processed 1 B 3 7 5 529 22 $40,000.00 $64,000.00 Bonus By Bonus By Volume Profit D $5.454.55 $8.727.27 $12.727.27 $20,363.64 $9.090.91 $14.545.45 $9.090.91 $14.545.45 $5.818.18 $3.636.36 $40,000.00 $64.000.00 Salary Analysis DELL Scenario 1 18. Write an Excel formula in cell C4 to calculate the Volume Bonus for Employee #1. The Volume Bonus is based on the units processed and the bonus amount allocated to be distributed to all employees. For example, Employee #1 processed 3 units, thus his allocation would be: (Units processed/Total number of Units processed)* (Bonus allocation) Copy the formula down to calculate the volume bonus allocations for each of the other employees and then respective bonus allocations each employee. across to calculate the profit for Note: The bonus by volume allocation is $40,000 and the bonus by profit allocation is $64,000. Each employee will receive a percentage of the bonus based on management's decision to give bonuses based on volume or profit. 19. You should use ONE formula in C4 and copy it down to C8 and across to D8 to fill the chart. If the numbers are different, you may want to check your Absolute and mixed cell referencing. 20. Write an Excel formula in cell C9, which can be copied to D9 to calculate the total bonus amounts allocated. 34 1 2 Bonus Allocation A 8 69 567 2345 B C Salary Analysis Employee Units Number Processed 1 B 3 7 5 529 22 $40,000.00 $64,000.00 Bonus By Bonus By Volume Profit D $5.454.55 $8.727.27 $12.727.27 $20,363.64 $9.090.91 $14.545.45 $9.090.91 $14.545.45 $5.818.18 $3.636.36 $40,000.00 $64.000.00 Salary Analysis DELL Scenario 1 18. Write an Excel formula in cell C4 to calculate the Volume Bonus for Employee #1. The Volume Bonus is based on the units processed and the bonus amount allocated to be distributed to all employees. For example, Employee #1 processed 3 units, thus his allocation would be: (Units processed/Total number of Units processed)* (Bonus allocation) Copy the formula down to calculate the volume bonus allocations for each of the other employees and then respective bonus allocations each employee. across to calculate the profit for Note: The bonus by volume allocation is $40,000 and the bonus by profit allocation is $64,000. Each employee will receive a percentage of the bonus based on management's decision to give bonuses based on volume or profit. 19. You should use ONE formula in C4 and copy it down to C8 and across to D8 to fill the chart. If the numbers are different, you may want to check your Absolute and mixed cell referencing. 20. Write an Excel formula in cell C9, which can be copied to D9 to calculate the total bonus amounts allocated.

Answer rating: 100% (QA)

calculations 18 Excel Formula for Volume Bonus C2SUMC2C5B2 This formula takes the value of the number of units processed by Employee 1 C2 and divides ... View the full answer