Assessment BSBITU402

Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 1 of 12
Assessment Task 1 – (Research and Demonstration)Prepare to
develop spreadsheet
Assessment description
You are required to prepare to develop Excel spreadsheets to manage the business requirements for
Covers Books (see Appendix 1). Candidates are required to setup or adjust their workstation in
accordance with ergonomic considerations, practical layout of necessary documents and resource
conservation requirements. Candidates are also required to answer questions on legislative knowledge,
organisational and task requirements.
Procedure/Instructions
1. Setup or adjust your workstation, desk or work area in accordance with ergonomic considerations,
the practical layout of necessary documents and occupational health and safety requirements.
a. Use the checklist in Appendix 2 to setup or adjust your work area.
Additional information on the correct set up of your workstation can be found on the
Ergonomics in Australia website at
<http://www.ergonomics.com.au/pages/400_useful_info/420_how_to_sit.htm>.
b. Obtain the assistance of a friend, family member or colleague to take up to three photos of
you seated at your work area. The photos are required to show that the height of the chair
has been correctly adjusted for you and that the monitor and keyboard (and any document
holder) are correctly positioned for you when you are seated at your work area.
c. Insert the photos into a document or email and add text beneath each photo explaining the
key points of ergonomic compliance and the date the photo was taken.
Liaise with your facilitator/assessor as to the most appropriate format for this step to ensure
that there are no compatibility issues.
2. 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
3. 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.
Hints:
For Windows users – Hold down the ‘Alt’ key and then press the ‘Print Screen’ key to take a
screen shot of the active screen. This can then be pasted into a document.
Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 2 of 12
For Mac users – Hold down the ‘Command’ and ‘Shift’ keys then press ‘4’. Select the ‘open
window’ option from the screen shot choices. This can then be pasted into a document.
4. Using the information provided in Appendix 1, describe two additional ways that DJ could
conserve energy or resources at Covers Books.
5. 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.
6. 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
7. Explain how each of the following may affect business operations at Covers Books:
a. ethical principles
b. privacy laws
c. occupational health and Safety laws and regulations.
Submission specifications
You must submit:
● a completed copy of the ergonomic and environmental checklist (Appendix 2)
● photos (a maximum of three) of the student at their computer work area clearly showing that the
chair height, monitor and keyboard are correctly positioned and adjusted
● a screen shot showing the energy settings on the computer have been adjusted or set as per the
instructions in step 2 of the procedure
● answers (in writing unless otherwise directed by your assessor) to the questions asked in steps 4, 5,
6 and 7 of the procedure above.
You must submit both printed copy and soft copy of your answers in a word document.
Submit the printed copy of required evidences (your answers) to your Trainer with the “Assessment
Cover Sheet” (Filled out and signed appropriately) attached on top of your documents.
Upload the softcopy on the link provide in the eLearning site.
Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 3 of 12
Appendix 1 – Covers Books
Covers Books is a small bookstore located in a local shopping precinct owned by DJ Mathis. The store
does not hold a lot of stock but does offer a special order service for stock not kept on hand.
Stock details
The store carries approximately 5000 titles covering the following categories:
● children’s books
● teenage fiction
● general fiction
● science fiction and fantasy
● romance
● true crime and thriller
● classics.
Staffing
In addition to the owner, DJ, who is at the store on a full-time basis, there is one part-time and two
casual sales staff and one part-time admin/bookkeeping staff member.
Suppliers
Covers have eight main suppliers for the books plus an office supplier to assist with general office needs
such as stationery. Most suppliers are happy to offer 30 day payment terms.
Customer purchasing and payment options
Covers accept cash and major credit cards for customer purchasers.
An eight-week lay-buy option is available for orders of $20 or more. This arrangement requires a 10%
deposit to be paid.
Special orders are available on request. Orders less than $50 require no pre-payment, although special
requests for items totalling $50 or more require a 10% deposit to be paid prior to the order being placed.
IT Usage
All computers and software provided by Covers Books are to be used primarily for work related
purposes only. Limited personal use is allowed; however this privilege may be withdrawn if usage is
found to be too high. All computer usage will be monitored to ensure proper use.
User IDs and passwords must not be shared.
Records and files must not be saved to the ‘C’ drive. All work related files and records should be saved
to the shared drive which is backed up on a weekly basis. Any personal records or files should be saved
to an external device, such as a memory stick.
Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 4 of 12
Finances
The finances for the first three years are shown below.

Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 5 of 12
Appendix 2 – Ergonomic and environmental checklist

Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 6 of 12

Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 7 of 12
Assessment Task 2 – (Demonstration Portfolio of Work) Payroll
records
Assessment description
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/Instructions
Open the spreadsheet provided by your assessor titled Covers Staff. 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 1992
f. Save the file using the following format: [Your initials] Covers Staff ddmmyy. For example:
DP Covers Staff 221210.
2. Open the spreadsheet title ‘Covers Fortnightly Payroll’. 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
Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 8 of 12
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
j. Save the file as a template using the following format: [Your initials] Covers Fortnightly
Payroll Template. For example: DP Fortnightly Payroll Template.
Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 9 of 12
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 initials] Covers Payroll PE ddmmyy. For
example: DP Covers Payroll PE 221210.
Submission specifications
Submission specifications
You must submit:
1. A soft copy of the following files:
a. the staff details file named [Your initials] Covers Staff ddmmyy
b. the payroll template named [Your initials] Covers Fortnightly Payroll Template
c. the current fortnights payroll file named [Your initials] Covers Payroll PE ddmmyy.
2. A print out of the current fortnight’s payroll on a single A4 page.
● named and stored spreadsheets in accordance with organisational requirements and exited the
application without data loss or damage
● developed complex spreadsheets.
You must submit both printed copy and soft copy of your answers in a word document.
Submit the printed copy of required evidences (your answers) to your Trainer with the “Assessment
Cover Sheet” (Filled out and signed appropriately) attached on top of your documents.
Upload the softcopy on the link provide in the eLearning site.
Appendix 1 – Pay details
Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 10 of 12
Fortnight ended
Enter the current date, as used in step 1 of the procedure.
Hours worked for the fortnight

Assessment Task 3 – (Demonstration Portfolio of Work)
Investment
Assessment description
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.
Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 11 of 12
Procedure/Instructions
1. Import the text file Covers Investments provided to you by your assessor into Excel and make the
following changes:
a. format the information into a table of figures
b. create a chart titled ‘Investment Prices June 2009 – June 2010’ showing the price of each share
over the 12 months covered by the data
c. name the worksheet ‘Investment Prices’.
2. 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
3. Create a chart titled ‘Total Investment Values’ showing the value of the total investment over 12
months.
4. From the figures in the ‘Investment Details’ table, use formulas to determine the average value of
each investment over the 12 months.
5. 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.
6. Save the file using the following format: [Your initials] Covers Investments with Macros ddmmyy.
For example: DP Covers Investment with Macros 221210.
7. 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 2)
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 initials] Covers Investments Report
ddmmyy.
For example: DP Covers Investments Report 221210
e. Print a copy of the report file.
Assessment
BSBITU402
Last updated October 2019, Version No. 1.0 Page 12 of 12
Submission specifications
You must submit:
● a soft copy of the following files:
○ the investment file named [Your initials] Covers Investments with Macros ddmmyy
○ the investments report file named [Your initials] Covers Investments Report ddmmyy.
● a print out of the investments report.
You must submit both printed copy and soft copy of your answers in a word document.
Submit the printed copy of required evidences (your answers) to your Trainer with the “Assessment
Cover Sheet” (Filled out and signed appropriately) attached on top of your documents.
Upload the softcopy on the link provide in the eLearning site.
Appendix 1 – Additional investment information

The post Assessment BSBITU402 appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms