5. Again, use the Copy/Move command to create a new tab under the new name BonusAnalysis 3. Complete the following steps. Modify the formula to allocate the bonuses so that the allocation is based on their base salaries. (Example: if all employees' salaries total $100,000 and employee#1's salary is $8000, then his allocation is 8000/100000 times the bonus amount for that bonus). Modify your conclusions as needed regarding which employee level benefits the most from this scheme based on job levels. AutoSave On Lab 2 Excel Sign in File Draw Page Layout Formulas Data Review View Help O Search Share Comments v Home Insert X Arial [ BIU. Wrap Text TI Percentage 10 AA === AY Insert DR Delete Format 2ro 4 Paste E Merge & Center $ %, 48_99 Ideas Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Ideas J K L L M H6 fo =C6/G6 A B c D E F G H 1 1 Bonus Allocation worksheet 2 Base Benefit Costs $ 2,500 3 Additional Benefit Cost per $1000 of salary $ 6.00 4 $ 50,000 $ 75,000 Current yr. Current yr. 5 Employee# Job Level salaries Bonus-Volume Bonus -Profit Benefits Total Compensation % of Base 6 1 3 $ 25,000 $ 9,375 $ 14,063 S 2,850 $ 51,088 48.94% 7 2 6 $ 150 000 $ 18,750 $ 28,125 S 3,400 $ 200,275 74.90% 8 3 3 4 $ 85,000 $ 12,500 $ 18,750 S 3,010 $ 119,260 71.27% 9 41 1 $ 76 000 $ 3.125 $ 4.688 S 2,956 S 86,769 87.59% 10 51 2 $ 20,000 $ 6,250 $ 9,375 S 2,620 $ 38.245 52.29% 11 16 $ 356,000 $ 50.000 $ 75,000 S 14,636 S 495,636 12 13 14 15 According to my findings on the percent of base salary calculate, the job level that are most benefited is level 1 16 17 18 19 20 21 22 Bonus Analysis 1 Bonus Analysis 2 Bonus Analysis 3 computer costs 100% HE Type here to search O N X ? 12:10 PM 9/19/2020 41 5. Again, use the Copy/Move command to create a new tab under the new name BonusAnalysis 3. Complete the following steps. Modify the formula to allocate the bonuses so that the allocation is based on their base salaries. (Example: if all employees' salaries total $100,000 and employee#1's salary is $8000, then his allocation is 8000/100000 times the bonus amount for that bonus). Modify your conclusions as needed regarding which employee level benefits the most from this scheme based on job levels. AutoSave On Lab 2 Excel Sign in File Draw Page Layout Formulas Data Review View Help O Search Share Comments v Home Insert X Arial [ BIU. Wrap Text TI Percentage 10 AA === AY Insert DR Delete Format 2ro 4 Paste E Merge & Center $ %, 48_99 Ideas Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Ideas J K L L M H6 fo =C6/G6 A B c D E F G H 1 1 Bonus Allocation worksheet 2 Base Benefit Costs $ 2,500 3 Additional Benefit Cost per $1000 of salary $ 6.00 4 $ 50,000 $ 75,000 Current yr. Current yr. 5 Employee# Job Level salaries Bonus-Volume Bonus -Profit Benefits Total Compensation % of Base 6 1 3 $ 25,000 $ 9,375 $ 14,063 S 2,850 $ 51,088 48.94% 7 2 6 $ 150 000 $ 18,750 $ 28,125 S 3,400 $ 200,275 74.90% 8 3 3 4 $ 85,000 $ 12,500 $ 18,750 S 3,010 $ 119,260 71.27% 9 41 1 $ 76 000 $ 3.125 $ 4.688 S 2,956 S 86,769 87.59% 10 51 2 $ 20,000 $ 6,250 $ 9,375 S 2,620 $ 38.245 52.29% 11 16 $ 356,000 $ 50.000 $ 75,000 S 14,636 S 495,636 12 13 14 15 According to my findings on the percent of base salary calculate, the job level that are most benefited is level 1 16 17 18 19 20 21 22 Bonus Analysis 1 Bonus Analysis 2 Bonus Analysis 3 computer costs 100% HE Type here to search O N X ? 12:10 PM 9/19/2020 41
