Scenario

You work for RSM Manufacturing Ltd and have been asked to plan and create a wages template that will be used each week to enter hours worked by employees.

You have been provided with the following data to help with the creation of the template:

Template requirements

1. Leave a space under the worksheet heading to insert the week ending date

2. List the pay rates and the tax rates at the top of the worksheet.

3. Type the list of Employees with family name first, then given name, e.g. Eaton, Andrew.

Then sort by family name.

4. Insert a column for the position held by each employee.

5. Insert a column for each employee's hourly rate and use a vlookup formula to determine the correct rate.

6. Insert a column for the hours worked.

7. Insert columns and formulas for the following:

• Gross pay

• Tax rate

• Tax to pay

• Net pay

8. Insert formulas for a Totals row.

9. Format the worksheet appropriately.

10. Create a macro and assign it to a macro button at the top of the worksheet (with text Enter hours). This macro will select the cells in the Hours column which will allow the user to type in hours, pressing Enter after each figure,

11. Save the template to the default template folder with the file name Wages

12. Add spreadsheet documentation on how to use the template and the macro.

Part 1

Open the file called Spreadsheet plan 402 from the 978-1-925291-30-8 BSBITU402 exercise files folder and use it to plan and sketch the worksheet. Show your sketch to your trainer and discuss the design and selection of formulas.

Part 2

1. Create the Wages template as shown in your spreadsheet plan.

2. Print the template then close.

Part 3

1. Use the Wages template to create a workbook for the week ending 11 November 2016.

Click on the Enter hours macro button and type:

2. Save the worksheet as a macro-enabled workbook with the file name Wages 11 Nov in your working folder.

3. Print and close the workbook.

Revision

These questions can be answered in writing or as a group discussion. Follow the instructions of your trainer.

1. What is a macro?

2. Where can macros be stored? Explain each option.

3. List the three different ways a macro can be assigned.

4. Explain the advantage of using templates.

5. What is data validation and why would it be used?

6. Why would you protect a worksheet?

7. Briefly describe how a workbook can be saved as a template.