Task 1
Assessment Instructions
This is an individual assessment. There are two parts in this task. If you need help understanding any questions, ask your assessor to explain.
Part A
You are required to prepare to develop Excel spreadsheets to manage the business requirements for Covers Books (see Appendix 1). You are required to setup or adjust your workstation in accordance with ergonomic considerations, practical layout of necessary documents and resource conservation requirements. You are also required to answer questions on legislative knowledge, organisational and task requirements.
Procedure
1. Setup or adjust your workstation, desk or work area in accordance with ergonomic considerations, the practical layout of necessary documents and Work Health and Safety requirements. Use the checklist in Appendix 2 to setup or adjust your work area.
2. List five potential health and safety hazards that could occur during the use of a computer.
3. Describe two exercises and one other strategy (not exercise) that you can undertake to reduce the risk of injury when using a computer. Explain how often you would recommend the exercises are done
4. Setup or adjust the energy conservation settings on your computer using the following settings. Take a screen shot of the settings screen showing that you have made the requested adjustments.
a. Set the monitor to turn off after 15 minutes of inactivity.
b. Set the computer to go into standby mode after 1 hour of inactivity.
5. Using the information provided in Appendix 1, describe two additional ways that DJ could conserve energy or resources at Covers Books.
6. Using the information provided in Appendix 1, identify the task requirements by undertaking the following tasks.
a. Identify at least three spreadsheets Covers Books are likely to need.
b. Briefly describe the purpose or use of each spreadsheet.
7. Where and how would you suggest that DJ store the business records?
Ensure that you include references to both hard (paper) and soft (electronic) storage suggestions and data back-up options in your answer
8. Explain how each of the following may affect business operations at Covers Books:
a. Ethical principles
b. Privacy laws
c. Work Health and Safety laws and regulations.
To be deemed competent you will need to successfully demonstrate the following:
You must:
- submit a completed copy of the ergonomic and environmental checklist (Appendix 2)
- submit answers to the questions asked in 2 to 8 of the procedure above.
Part B
You are required to use Microsoft Excel to create spreadsheets for Covers Books that will manage payroll information and calculate fortnightly payroll amounts.
Covers Books
Covers Books is a small bookstore located in a local shopping precinct owned by DJ Mathis. Operating for just over two years, Covers Books specialises in fiction books and has built up a steady business with many repeat customers from the local area. DJ (the owner) has set up a table containing the details of the four staff members currently employed by Covers Books. You are required to automate the classification of the employee depending on their age, and then calculate the correct pay rate, based on the classification. You will then need to create a template for the fortnightly pay and enter data for the current fortnight.
Procedure
1. Create the spreadsheet titled Covers Staff as below.
|
Staff
|
|
|
Current Date
|
|
|
|
|
Staff ID
|
First Name
|
Last Name
|
Date of Birth
|
Age
|
Classification
|
Pay Rate
|
|
N00011
|
Sally
|
Jenkins
|
5/06/1975
|
|
|
|
|
N00012
|
David
|
Nugyen
|
24/04/1971
|
|
|
|
|
N00013
|
Natalie
|
Dinatale
|
19/03/1998
|
|
|
|
|
N00014
|
Cameron
|
Johnson
|
21/08/1991
|
|
|
|
|
N00015
|
Justine
|
Lee
|
15/01/1984
|
|
|
|
|
Staff Classifications
|
|
Age
|
Classification
|
Description
|
Ordinary Payrate
|
|
16
|
Junior 1
|
Junior wage - 16 yo
|
$ 8.23
|
|
17
|
Junior 2
|
Junior wage - 17 yo
|
$ 9.88
|
|
18
|
Junior 3
|
Junior wage - 18 yo
|
$ 11.52
|
|
19
|
Junior 4
|
Junior wage - 19 yo
|
$ 13.17
|
|
20
|
Junior 5
|
Junior wage - 20 yo
|
$ 14.82
|
|
21
|
Adult
|
Full adult wage
|
$ 16.47
|
Make the following changes to the spreadsheet to enable the correct salary classification to be determined.
a. Add a formula in E1 to calculate the current date.
b. Add a formula to cells E3:E7 that calculates the age of the staff member as at the current date.
c. Add a formula to cells F3:F7 that displays the salary classification.
d. Add a formula to cells G3:G7 that displays the correct pay rate that applies to each staff member.
e. A new staff member started last week and needs to be added to the staff list as follows:
i. Name: Grace Jacobs
ii. Date of Birth: 11 October 1997
f. Save the file using the following format: [Your Student Number] Covers Staff ddmmyy. For example: 20160XXX Covers Staff.
2. Create the spreadsheet title ‘Covers Fortnightly Payroll' as below.
Fortnightly Ending:
|
Staff ID
|
Staff Member Name
|
Hrly Rate
|
No. Hrs.
|
Gross Pay
|
Tax
|
Net Pay
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Totals
|
|
|
|
|
|
|
|
Tax Table (PFN)
|
|
$ -
|
0%
|
|
$ 250.00
|
5%
|
|
$ 500.00
|
8%
|
|
$ 1,000.00
|
10%
|
|
$ 1,500.00
|
12%
|
|
$ 2,500.00
|
14%
|
|
$ 5,000.00
|
16%
|
Make the following changes to the spreadsheet that will be used calculate each fortnight's payroll.
a. Format cell F3 to be short date
b. Merge, bold and right justify cells A18:C18
c. Place individual lines around each cell and a single bold border from A10:G18
d. Place a single bold border from A10:G10, A11:G17 and A18:G18
e. Add formulas that look up the staff code in the spreadsheet you created in the previous step of this procedure and return the first name of the staff member and the appropriate hourly rate, ensuring that an hourly rate of $0 is returned when no Staff ID is input.
f. Add a formula to cells E11:E17 that will calculate the gross pay
g. Add a formula to cells F11:F17 that will look up and calculate the tax amount payable
h. Add a formula to cells G11:G17 that will calculate the net pay amount
i. Add a formula that calculates the total hours, gross pay, tax and net pay
Save the file as a template using the following format: [Your Student Number] Covers Fortnightly Payroll Template. For example: 20160XXX Fortnightly Payroll Template.
3. Use the template you created in step 2 of this procedure to open a new payroll sheet and enter the information in Appendix 1.
a. It is policy of Covers Books that both a hard and soft copy of the fortnightly payroll is kept.
b. Print a copy of this fortnight's payroll ensuring that its fits to a single sided A4 page.
c. Save the file using the following format: [Your Student Number] Covers Payroll PE ddmmyy. For example: 20160XXX Covers Payroll PE.
To be deemed competent you will need to successfully demonstrate the following:
You must submit:
- a hard copy of the following files:
o the staff details file named [Your Student Number] Covers Staff ddmmyy
o the payroll template named [Your Student Number] Covers Fortnightly Payroll Template
o the current fortnights payroll file named [Your Student Number] Covers Payroll PE ddmmyy.
- a hard copy of the formulas used in all Excel worksheet
Task 2
Assessment Instructions
You are required to use Microsoft Excel to create spreadsheets for Covers Books that will manage the store's investments.
Covers Books
Covers Books is a small bookstore located in a local shopping precinct owned by DJ Mathis. Operating for just over two years, Covers Books specialises in fiction books and has built up a steady business with many repeat customers from the local area. DJ (the owner) has set up a small investment portfolio in the name of the store to better utilise excess cash.
Procedure
1. Create the text file Covers Investments as below using Notepad and save as Covers Investments.
Shares prices
Date,TeleCom (TC),State Bank (SB),United Retail (UR) 30/06/2015;7.62;27.59;14.47;
31/07/2015;7.74;27.85;14.28;
31/08/2015;6.82;21.17;15.45;
30/09/2015;7.23;24.73;14.86;
31/10/2015;7.41;22.16;14.71;
30/11/2015;7.67;23.92;14.93;
31/12/2015;6.94;28.17;15.03;
31/01/2016;6.84;25.68;14.98;
28/02/2016;7.01;26.12;15.22;
31/03/2016;7.34;26.86;15.36;
30/04/2016;7.22;25.99;15.67;
31/05/2016;7.38;26.45;15.46;
30/06/2016;7.55;26.62;15.51;
2. Import the text file Covers Investments into Excel and make the following changes:
a. format the information into a table of figures
b. create a chart titled ‘Investment Prices June 2015 - June 2016' showing the price of each share over the 12 months covered by the data
c. name the worksheet ‘Investment Prices'.
3. In a new worksheet titled ‘Investment Details' create a table that calculates the value of each share holding at the end of each month and then totals the share values for each corresponding period. You will need to include and refer to the additional information in Appendix 1 for this calculation
4. Create a chart titled ‘Total Investment Values' showing the value of the total investment over 12 months.
5. From the figures in the ‘Investment Details' table, use formulas to determine the average value of each investment over the 12 months.
6. Create macros as follows:
a. to print the 'Investment Prices' chart
b. to print the ‘Total Investment Value' chart
c. create buttons for each of the macros, ensuring you link the buttons to the macros.
7. Save the file using the following format: [Your student number] Covers Investments with Macros ddmmyy.
For example: 20160XXX Covers Investment with Macros.
8. Export the table of each share for 12 months and the related chart to a document:
a. Format the document by adding the logo (copy from the Covers Fortnight payroll file provided for Assessment Task 1)
b. Add an appropriate title
c. Ensure that the graph and table will print to a single A4 page
d. Save the report using the following format: [Your student number] Covers Investments Report ddmmyy.
For example: 20160XXX Covers Investments Report
e. Print a copy of the report file.
To be deemed competent you will need to successfully demonstrate the following:
You must:
- submit a hard copy of the following files:
? the investment file named [Your student number] Covers Investments with Macros ddmmyy.
? the investments report file named [Your student number] Covers Investments Report ddmmyy.
- a hard copy of the formulas used in all Excel worksheet
Appendix 1 - Additional investment information
|
Shares
|
Date of Purchase
|
No of Shares
|
|
TeleCom (TC)
|
01/06/2015
|
1000
|
|
State Bank (SB)
|
29/08/2015
|
1200
|
|
United Retail (UR)
|
01/09/2015
|
1500
|
Attachment:- Appendix task1.rar