Name______
Production Test Unit 7
Background
The company is developing a new template for use in all shops. It will help each owner calculate wages for all employees. The template will include formatting, lookup tables, data validation, and a macro. It will also include a hyperlink.
Business Purpose
Create a template that will calculate new wages for employees at a Klassy Kow Ice Cream Shop using lookup tables. Use a formula to calculate the new wage based on a percentage. Include a macro to apply fill to alternate rows after data is entered.
Directions
1. Open Prod. Test Unit 7 data file.xlsx and save it as macro-enabled template as Last name-Prod Test Unit 7 template.xltm (you do not need to key the extension)
2. Name the two ranges (A1:B11 and D1:E11) on the LookupTables sheet so they can be used in lookup functions.
3. On the WageWorksheet sheet, format the Hourly Wage, Annual Bonus, and New Wage columns as Currency, dollar sign, with 2 decimals from rows 8-24.
4. Format the Years of Service column as Number with 1 decimal for rows 8-24.
5. Format The Pay Increase column as a Percentage with 1 decimal for rows 8-24.
6. Using the named ranges created in #2 above, create two lookup formulas. In the Pay Increase column, create a lookup formula which will determine the percent of increase based on the years of service. In the Annual Bonus column, create a lookup formula which will determine the bonus amount based on the hourly wage. Use dummy numbers if necessary to see if the formulas are working correctly. Copy to row 24.
7. In the New Wage column, create a formula that calculates the new wage based on the percentage increase. Copy to row 24.
8. Display a data validation message in the Hourly Wage and Years of Service columns. Give the input messages a title.
9. Display the data validation message in the Hourly Wage column. The message should explain what the data entry clerk may key in based on your settings. The setting should permit only keying in a minimum hourly wage of $6.55 or greater (you can key $6.55 or greater). Include a Warning error message for these settings that prohibits entry of data that does not meet the requirements.
10. Display the data validation message in the Years of Service column. The message should explain what the data entry clerk may key in based on your settings. The setting should permit keying in 1 or greater years of service, (that is you can key 1 or greater.) Include a Warning error message for these settings that prohibits entry of data that does not meet the requirements.
11. Insert a hyperlink in A26. Use http://www.mhhe.com as the URL, but show Klassy Kow Ice Cream, Inc. on screen.
12. In this workbook, create a macro named AddFill with a shortcut of [Ctrl]+[Shift]+[F]. This macro should apply a light fill color to a cell. Hint: Do not select a range of cells in the row as part of the macro. Also do not select the entire row by clicking on the row header. The user should select however many cells needed in a row and then run the macro.
13. Paste a list (paste names) of range names, (showing the range names and cell references), in the lookup table in A13 on the Lookup Tables worksheet.
14. All the sheets should fit to one page, so make adjustments if needed.
15. Add a header on all sheets, Left side: Your Name; Middle, Filename; Right Side, worksheet name.
16. Add a footer on all sheets, Left side: Date; Right side: Page ? of ?'s.
17. Unlock cell C6 (for the store location) and A8:D24 for data entry. Protect the sheet without a password. In the Protect Sheet dialog box, Allow all users of this worksheet to: Select locked cells, Select unlocked cells, Format cells, Format columns, and Format rows.
18. Test your template. Key in a wage to see if the lookup is done correctly. Key in Years of Service to also check the lookup. If you have applied formatting for dollar signs and 2 places, key in amounts to see if it appears with the correct formatting. Test your data validations by entering an incorrect amount, then a correct amount. Is the user of the worksheet going to know what amounts are correct to enter into the worksheet? Test the macro. Apply protection and test the locked and unlock cells. Leave your testing data in the template.
Everything you have done on that worksheet should be tested using made up data before saving it and using it as a workbook. Realize if you find an error when adding data to the workbook, you will need to make that correction in the template which is wasting time. Find what is not working now.
Save your template as Last name-Prod Test Unit 7 template.xltm
19. Using Last name-Prod Test Unit 7 template.xltm create a macro enabled workbook. Save the file as Last name-Prod Test Unit 7 Salinas.xlsm
20. In the Salinas workbook enter the following data. If you receive a warning regarding a cell entry, leave the cell blank. Enter the store location in C6.
Employee Hourly Wage Years of Service
Mabel Smith $7.25 1
Jim Washington $8.00 3
Enrique Perez $6.50 7
Tim Longfellow $10.50 4
Martha Cunningham $8.00 6
Juan Valdez $9.25 4
Mary Stout $6.25 .5
21. Run the macro on the first employee row and every other row with data after that (Columns A:G)
22. Save the macro-enabled workbook Last name-Prod Test Unit 7 Salinas.xlsm
23. Prepare and submit your work as instructed. Two files will be submitted—the template .xltm and the macro enabled workbook .xlsm
Production Test Unit 7Page 1 of 2