Excel Problem*

Objective: Learn how to use the VLOOKUP function for payroll calculations.

REQUIRED

a. Read the article “Make Excel a Little Smarter” by Lois S. Mahoney and Charles Kelliher in the Journal of Accountancy (July 2003). You can find a copy at www.

aicpa.org.

b. Read the section titled “Data in Different Places,” and create the spreadsheet illustrated in Exhibit 6.

Print a screen shot of your work, and save your spreadsheet.

c. Create a formula that calculates total bonuses. Also create a cell entry that indicates what that number represents. Print a screen shot of your work, and save it.

d. Add the following data validation controls to your spreadsheet, including explanatory error messages. Save your work.

• Sales must be positive.

• Sales cannot exceed 125.

• Amount of bonus must be nonnegative.

• Amount of bonus cannot exceed 20% of unit sales.

e. Modify your worksheet by placing the sales data and resulting bonus on a different worksheet from the bonus table. Name your table array, and modify the VLOOKUP function accordingly. Then add another employee: Johnson, who sold 115 units.

Print a screen shot of your new worksheet showing the bonuses for each employee, including Johnson. Save your work.