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.
*Life-long learning opportunity: see p. xxii in preface.
We need at least 10 more requests to produce the solution.
0 / 10 have requested this problem solution
The more requests, the faster the answer.