View the step-by-step solution to:

# Imagine that you are the manager of the sales division at your firm. Create a spreadsheet which will relay salary information to the accounting...

Financial engineer

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) List ten employees, each with a base annual salary of \$35,000. 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 they’d be fired): Products Sold Bonus (per # of products sold) 1-49 \$50 50-99 \$90 100+ \$120 Hint: This involves nesting an IF statement inside another IF statement. You might start by checking if the number of products sold is < 50 then the bonus is \$50 for each product sold, otherwise check if products sold is < 100, etc. Part (b) Next, determine what each employee’s gross and net (take-home) salary will be in terms of bi-weekly (26) 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 15% \$35,000-\$59,999 28% \$60,000+ 34% Part (c) Determine how the net salary will change when employees contribute to the company’s 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 employee’s 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 6%.

1) Employee ID
1
2
3
4
5
6
7
8
9
10 Base salary Product sold Bonus per product
35000
45
35000
60
35000
70
35000
80
35000
90
35000
100
35000
110
35000
120
35000
40
35000
30 50
90
90
90
90
120
120...

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents