Imagine that you are the manager of the sales division at your firm. Create a spreadsheet which will relay salary information to the accounting department. The salary of your employees will be composed of a base plus a bonus. Assume that the bonus is based on sales last year and is now being spread out over the pay periods in this coming year. Part (a) (25 points) List ten employees, each with a base annual salary of $30,000. Select any number of products sold for each employee. Use IF statements to provide the following various bonuses based on the quantity of products that they sold last year (all salespeople must sell at least 1 product or else theyd be fired): Products Sold Bonus (per # of products sold) 1-39 $40 40-99 $55 100+ $70 Hint: This involves nesting an IF statement inside another IF statement. You might start by checking if the number of products sold is < 40 then the bonus is $40 for each product sold, otherwise check if products sold is < 100, etc. Part (b) (25 points) Next, determine what each employees gross and net (take-home) salary will be in terms of weekly (52) paychecks. Use IF statements and the following federal flat tax rates (assume no additional state taxes or other deductions): Income Tax Rate $0-$34,999 17% $35,000-$59,999 28% $60,000+ 33% Part (c) (50 points) Determine how the net salary will change when employees contribute to the companys 401k plan. Note: taxable income = gross income minus 401k contribution. In general, contributing to a 401k plan reduces your tax liability. You should consider several scenarios for each employee (this will result in many columns): -What if each employee contributes nothing to the 401k plan? -What if each employee contributes 5% of their gross salary? What about 10%? What about 15%? Further, determine what the future value of each employees monthly 401k contribution payments would be if they are all 22 and will all retire at age 67. Assume the payments stay the same throughout the 45 years and use an inflation-adjusted interest rate of 5%.

Imagine that you are the manager of the sales division at your firm. Create a spreadsheet which will relay salary information to the accounting department. The salary of your employees will be composed of a base plus a bonus. Assume that the bonus is based on sales last year and is now being spread out over the pay periods in this coming year. Part (a) (25 points) List ten employees, each with a base annual salary of $30,000. Select any number of products sold for each employee. Use IF statements to provide the following various bonuses based on the quantity of products that they sold last year (all salespeople must sell at least 1 product or else theyd be fired): Products Sold Bonus (per # of products sold) 1-39 $40 40-99 $55 100+ $70 Hint: This involves nesting an IF statement inside another IF statement. You might start by checking if the number of products sold is < 40 then the bonus is $40 for each product sold, otherwise check if products sold is < 100, etc. Part (b) (25 points) Next, determine what each employees gross and net (take-home) salary will be in terms of weekly (52) paychecks. Use IF statements and the following federal flat tax rates (assume no additional state taxes or other deductions): Income Tax Rate $0-$34,999 17% $35,000-$59,999 28% $60,000+ 33% Part (c) (50 points) Determine how the net salary will change when employees contribute to the companys 401k plan. Note: taxable income = gross income minus 401k contribution. In general, contributing to a 401k plan reduces your tax liability. You should consider several scenarios for each employee (this will result in many columns): -What if each employee contributes nothing to the 401k plan? -What if each employee contributes 5% of their gross salary? What about 10%? What about 15%? Further, determine what the future value of each employees monthly 401k contribution payments would be if they are all 22 and will all retire at age 67. Assume the payments stay the same throughout the 45 years and use an inflation-adjusted interest rate of 5%.