BSBITU402_AE_Pro_2of2_LMS
docx
keyboard_arrow_up
School
TAFE NSW - Sydney Institute *
*We aren’t endorsed by this school
Course
306B
Subject
Information Systems
Date
Jan 9, 2024
Type
docx
Pages
29
Uploaded by PrivateIce11519
Project Assessment 2 of 2
Criteria
Unit code, name and release number
BSBITU402 Develop and use complex spreadsheets (1)
Qualification/Course code, name and release number
FNS40217 Certificate IV in Accounting and Bookkeeping (1)
BSB40515 Certificate IV in Business Administration (3)
Student details
Student number
880966107
Student name
Clarissa Langebeck-Muller
Assessment Declaration
If you are submitting this assessment online, complete the assessment declaration on the TAFE NSW learning platform. If you are submitting this assessment on paper, complete the following declaration:
This assessment is my original work and no part of it has been copied from any other source except where due acknowledgement is made.
No part of this assessment has been written for me by any other person except where such collaboration has been authorised by the assessor concerned.
No part of this assessment is plagiarised. I understand that plagiarism is the presentation of the work, idea or creation of another person as though it is your own.
Plagiarism occurs when the origin of the material used is not appropriately cited.
Student signature and date
CLMuller
9/11/2022
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 1 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Version:
20201102
Date created:
3 November 2020
Date modified:
2 November 2020
For queries, please contact:
Technology and Business Services SkillsPoint
Ultimo
© 2019 TAFE NSW, Sydney
RTO Provider Number 90003 | CRICOS Provider Code: 00591E
This assessment can be found in the: Learning Bank
The contents in this document is copyright © TAFE NSW 2019, and should not be reproduced without the permission of the TAFE NSW. Information contained in this document is correct at time of printing: 1 January 2024. For current information please refer to our website or your teacher as appropriate.
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 2 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Assessment instructions
Table 1 Assessment instructions
Assessment details
Instructions
Assessment overview
The objective of this assessment is to assess your knowledge and performance as would be required to develop and use complex spreadsheets
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 3 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Assessment details
Instructions
Assessment Event number
2 of 2
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 4 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Assessment details
Instructions
Instructions for this assessment
This is a project-based assessment and will be assessing you on your knowledge and performance of the unit.
This assessment is in two parts and includes Checklists and Assessment Feedback form:
1.
Practical Tasks
2.
Role Play
Appendix 1: Assessment Checklist for Part 1
Appendix 2: Observation Checklist for Part 2
Assessment Feedback
Your assessor will provide assessment results and feedback online, or
via the Assessment Feedback form included in this document.
Submission instructions Required assessment files to be submitted are:
1.
Practical tasks
2.
Role play
For online students, complete and upload this assessment online via the TAFE NSW online learning platform. Upload all required Document title: BSBITU402_AE_Pro_2of2_LMS
Page 5 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Assessment details
Instructions
assessment files in the assessment area on the online learning platform. Submit your completed assessment and printouts online as one PDF. Online students are required to print to PDF format where reference to print or printing is made.
The participants for the role play can include colleagues, other students completing this unit/course or industry expert/representative.
For all other students, complete and upload the practical tasks. The completed assessment and printouts can be submitted as one PDF. Alternatively, hand all required assessment files to your assessor for marking. The role play may be completed in class or by using online tools such as Microsoft Teams, Skype or Adobe Connect that can be recorded and uploaded onto the learning platform for marking. Other options may be available at the discretion of your assessor. If recording your role play, please refer to Video Recording Instructions for Role Play Assessments
for guidelines and Unit Assessment Guide (UAG) or Individual Training Plan for online students.
Ensure you have your name at the bottom of each page of this assessment.
It is important that you keep a copy of all electronic and hardcopy assessments submitted to TAFE and complete the assessment declaration when submitting the assessment.
What do I need to do to achieve a satisfactory result?
To successfully complete this assessment the student is required to correctly undertake all the assessment criteria as outlined in the assessment instructions and submit for marking by the due date.
All parts of the observable tasks must be performed to a satisfactory level as indicated in the criteria section of the Observation Checklists.
All questions must be answered correctly to be deemed satisfactory in
this assessment task; however, Assessor may ask questions to clarify understanding.
What do I need to Access to computers/laptops with appropriate software, internet Document title: BSBITU402_AE_Pro_2of2_LMS
Page 6 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Assessment details
Instructions
provide?
access and printer.
Completed assessment and submit as per instructions.
Online students will need to have access to colleagues, other enrolled students completing this unit/course or industry expert/representative to complete the role play and will need to upload a digital recording of the role play.
What the assessor will provide?
Access and electronic copy of
This assessment
BSBITU402_AE_Pro_2of2_SR1.xlsx
BSBITU402_AE_Pro_2of2_SR2.txt
BSBITU402_AE_Pro_2of2_SR3.docx
Instructions to access Learning platforms (e.g. Moodles) and or Microsoft teams or Adobe Connect if required and video recording instructions Video Recording Instructions for Role Play Assessments
.
Due date and time allowed
Refer to Unit Assessment Guide or your Individual Training Plan for online students for due date.
This assessment should take approximately 4 to 5 hours to complete
Assessment location
For online students, this assessment is to be completed online. The role play is to be recorded and uploaded.
For other students, completed out of class and role play options will be at the discretion of your Assessor.
Supervision
This is an unsupervised assessment.
Your assessor may ask for additional evidence to verify the authenticity of your submission and confirm that the assessment task was completed by you.
Assessment feedback, review or appeals
In accordance with the TAFE NSW policy Manage Assessment Appeals, all students have the right to appeal an assessment decision in relation to how the assessment was conducted and the outcome of Document title: BSBITU402_AE_Pro_2of2_LMS
Page 7 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Assessment details
Instructions
the assessment. Appeals must be lodged within 14 working days
of the formal notification of the result of the assessment.
If you would like to request a review of your results or if you have any concerns about your results, contact your Teacher or Head Teacher. If they are unavailable, contact the Student Administration Officer.
Contact your Head Teacher for the assessment appeals procedures. Appeals are addressed in accordance with Every Students Guide to Assessment. Assessment Guidelines for TAFE NSW
.
Specific task instructions
The instructions and the criteria in the tasks and activities below will be used by the assessor to determine if you have satisfactorily completed this assessment event. Use these instructions as a guide to ensure you demonstrate the required knowledge and skills.
You are required to:
Read the following instructions carefully
Plan, design and create a series of spread sheet that fulfil the requirements of the tasks.
Plan for a role play
Participate in a role play.
Hand (upload) your completed tasks into your teacher by the due date
You will be assessed:
Part 1: Your assessor will mark your performance against the assessment criteria of your submitted/presented project. Use Appendix 1
: Assessment Checklist to understand what skills and/or knowledge you need to demonstrate in your submission/presentation. The assessment checklist also includes a column for students to know what needs to be submitted
for each criteria.
Part 2: Appendix 2
: Observation Checklist will be used by your assessor to mark your performance of the role play. Use this Checklist to understand what skills you need to demonstrate in the role play scenario.
Part 1: Project
Instructions
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 8 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
i.
You will need access to a computer with internet access, a printer and Microsoft Excel or an alternative spread sheet software.
ii.
You will require the files BSBITU402_AE_Pro_2of2_SR1.xlsx
and BSBITU402_AE_Pro_2of2_SR2.txt
for Task 6. These files will be located on the TAFE NSW online learning platform or provided by the Assessor
iii.
Complete all tasks in this assessment paper. You are required to use spread sheet software such as Microsoft Excel
.
iv.
Check all instructions have been followed before printing or print to PDF form if uploading your tasks.
v.
You are required to provide usable
printouts or print to PDF form of each assessment task and present them according to designated timeline
as would be required in a workplace. vi.
Where possible, ensure your printouts or print to PDF format fit on one page using the appropriate software function.
vii.
You may access appropriate textbooks, user manuals and online help if required.
viii.
For 20XX, use the current year.
ix.
You are encouraged to be proactive in clarifying with any instructions or tasks that you do not fully understand. You can send/show your teacher a draft of the template you created in Task 1 and discuss the suitability/correctness of your draft with your teacher before performing Tasks 2 to 6.
x.
Refer to the Appendix 1 Assessment Checklist
for the assessment criteria your assessor will be assessing your Project work on. The checklist also includes reference of what needs to be submitted with this assessment.
Wherever possible your spread sheets should use the most efficient formulas and/or functions, which must demonstrate
intermediate functions such as:
AutoSum Functions
Average Functions
Sort Function
Absolute Cell Reference
IF Function
Round Function
VLOOKUP
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 9 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Background to task
You are employed as the Accounts Clerk in the administration department for The Technology Network (TTN). Part of your role is to produce complex spreadsheets for your team. You are required to complete a number of tasks starting on the following page of this assessment. All tasks will need to comply with TTN’s style guide. Tasks are simulated workplace scenarios. The spreadsheets will be completed within designated timelines. The template will be created prior to the end of the first quarter. After the end of the Quarter the template will be edited for sales data to be entered. One month after the end of the quarter.
The Technology Network’s Policies and Procedures Manual contains their style guide. You will have access to a copy of The Technology Network’s Policies and Procedures Manual (BSBITU402_AE_Pro_2of2_SR3.docx) on the online learning platform or provided by the Assessor.
Task 1 – Plan and design a spreadsheet
Analyse the following task requirements and determine the specifications for your spreadsheet. Once you are clear on what is required by the task create a spreadsheet template to meet the task requirements. Read all of the task instructions before you commence the task.
The Technology Network operates in all states of Australia. They have a team of 8 sales staff in each state. The sales staff are paid commissions based on the amount of sales they make and if they make sales in excess of $20,000 a quarter they are paid a bonus. Commissions and bonuses are paid quarterly. Your manager has asked you to prepare a spreadsheet template that can be used for each state at the end of each Quarter, commencing with March 20XX. The spreadsheet will show:
The name of the state the spreadsheet is for.
Each state will have a separate spreadsheet (but you only need to create 1 template)
The name of each of the sales staff for that particular state.
The $ amount of sales each sales person made in each month.
The spreadsheet will cover the months of January, February and March.
The total $ amount of sales for the quarter for each sales person.
The commission earned by each sales person for the quarter.
If the sales person earned a bonus during the quarter and the amount of the bonus.
The total payment due to the sales person for the quarter (commission plus bonus).
The spreadsheet must show the:
o
Commission percentage (5%).
o
Sales target for the bonus ($20,000). That means that if a sales person makes
sales in excess of $20,000 in the quarter they receive a bonus of $1,500. Use a complex function to calculate the bonus. Hint: Absolute Referencing is required in the formula
o
Bonus amount ($1,500).
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 10 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Wherever possible your template should use the most efficient formulas and must demonstrate
advanced functions such as:
o
Round
o
IF
o
Absolute (or named) Cell Reference
The spreadsheet must fit on one landscape page when printed or printed to PDF format showing data. You will also be required to print or print to PDF format the spreadsheet in formula view, this does not need fit on one page. Note: Column widths may need to be adjusted when printing or print to PDF form formulas so that the entire formula can be viewed and printed or print to PDF form.
Each sheet must have the state name in the heading
Each state has a team of 8 sales people
Add an appropriate label/heading for the totals calculated.
In addition to the spreadsheet specifications listed above, your manager has requested that you include some additional calculations on the spreadsheet template, including:
A summary of the minimum, maximum and average Total Sales for the Quarter (3 months) must be included on the worksheet under the data. All figures in this summary must be rounded
to the nearest $100
– use an appropriate formula.
Required:
1.
Open a new excel workbook. 2.
Open a new worksheet. Rename the worksheet QTR No
.
3.
Read the above task instructions and analyse the requirements for the spreadsheet. Rename a worksheet Theory then include a list of specifications on the Theory Worksheet of this spreadsheet. (your answer should be between 10 and 30 words).
4.
Evaluate tasks where automation can be included in the template to increase efficiency. On Theory Worksheet of this spreadsheet, include a list of tasks can be automated to increase efficiency.
5.
Create a spreadsheet template for The Technology Network. Include all formulas.
6.
Columns must be wide enough to display data properly. Ensure you have set your page up to fit to one landscape page when printing or print to PDF format.
7.
Save the spreadsheet as an Excel Macro-Enabled
TEMPLATE
using the naming and location convention instructed in the TTN Style Guide.
8.
Format all headings and totals appropriately as instructed in the TTN Style Guide (excluding Footers at this point). Centre on the page horizontally and vertically.
9.
Spellcheck, proofread and test your formulas. 10. Print a copy or print to PDF your spreadsheet in data view ensuring the spreadsheet fits to one page in landscape orientation.
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 11 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Task 2 – Macro
1.
Ensure your template from Task 1 is still open.
2.
Record a macro that is called InsertFooter, enter an appropriate description.
3.
Start recording the macro:
a.
Create a custom footer containing the workbook File Path and Name in the left section, and the Sheet Name in the right section. (HINT: When you add the File Path it will automatically add the File Name too).
b.
Stop recording the macro.
c.
Print a copy of the macro code and label it “Macro code before edit”.
4.
Print a copy or print to PDF your template showing formula view. Requires gridlines and row/column headings to be evident.
5.
View Macros, select InsertFooter and click “Edit”. Go to RightFooter in the code and change “&A” to “Your Name”. 6.
Save and print or print to PDF the macro code and label it “Macro code after edit”.
7.
You will need to store your spreadsheet by saving it to safe location so you do not lose or damage your data. When you save your file ensure it is still saved as an Excel Macro-Enabled
TEMPLATE
with the same file name from Task 1.
8.
Once the file is safely saved exit Excel.
NOTE: You are about to use your template to create a separate workbook for each of your three (3) separate states i.e. NSW, VIC & SA. If you discover there is an issue with a formula as you populate your spreadsheets in this next task, go back to the original template and ensure you fix it before moving on to the next city. You will also need to reprint the template in data and formula view to replace your incorrect ones.
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 12 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Task 3 – Use Templates and Enter Data
1.
Open a copy of the template from the previous task. Using the data below populate the workbook for NSW. Be sure to double-check the clerical accuracy of all figures you have entered
.
State: New South Wales Salesperson
January February March Edwards, Michael
$ 3,960.00 $ 2,690.00 $ 2,680.00 Avard, Renae
$ 7,060.00 $ 5,970.00 $ 6,145.00 Fuller, Rhianna
$ 8,615.00 $ 7,805.00 $ 7,620.00 Jermyn, Katie
$ 7,910.00 $ 6,070.00 $ 6,650.00 Pohlner, Amanda
$ 9,205.00 $ 8,970.00 $ 7,320.00 Enright, Melissa
$ 7,400.00 $ 8,290.00 $ 5,440.00 Smith, Kurt
$ 5,125.00 $ 3,760.00 $ 4,000.00 Craine, Maurie
$ 6,980.00 $ 7,120.00 $ 7,590.00 2.
Add the name of the state at the top of the worksheet.
3.
Sort the data by salesperson in alphabetical order. 4.
Rename the worksheet Qtr No 1.
5.
Repeat these steps for the other states; Victoria and Queensland. Their data appears below. You should have three (3) workbooks.
6.
Each workbook should be stored by saving it as per the TTN Style Guide. You will need to save them as Excel Macro-Enabled Workbooks
to be able to use your Macro from Task 2. Save using the naming and location naming conventions from the TTN Guide.
7.
Run your Macro to update your footers.
8.
Preview and adjust your spreadsheet to ensure all information displays on one landscape page.
9.
Print to PDF form or print a copy of each state in data view.
10. Exit excel
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 13 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
State: Victoria
Salesperson
January February March Alexander, James
$ 3,640.00
$ 2,570.00
$ 2,880.00
May, Ivy
$ 3,800.00
$ 4,890.00
$ 3,180.00
Giles, Harriet
$ 8,615.00
$ 7,350.00
$ 7,630.00
Williams, Oliver
$ 5,030.00
$ 6,010.00
$ 6,450.00
Timothy, Zack
$ 5,985.00
$ 5,030.00
$ 5,520.00
Jones, Magnus
$ 7,140.00
$ 5,890.00
$ 7,475.00
Ryan, Claudia
$ 5,135.00
$ 3,575.00
$ 4,010.00
Sharrock, Vanessa
$ 6,050.00
$ 7,520.00
$ 7,150.00
State: Queensland
Salesperson
January February March Shepherd, Alice
$ 3,980.00 $ 4,370.00
$ 2,955.00 Little, Rachel
$ 7,380.00 $ 5,895.00
$ 6,220.00 Ross, Michelle
$ 8,890.00 $ 7,850.00
$ 7,590.00 McLeod, Philippa
$ 4,890.00 $ 7,390.00
$ 6,375.00 Taylor, Benny
$ 6,225.00 $ 4,550.00
$ 5,595.00 Specter, Rosie
$ 7,720.00 $ 6,215.00
$ 5,515.00 Pearson, Wally
$ 5,400.00 $ 3,885.00
$ 3,970.00 Paulson, Gretchen
$ 6,780.00 $ 5,990.00
$ 7,550.00 Document title: BSBITU402_AE_Pro_2of2_LMS
Page 14 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Task 4 – Link Workbooks
In a new workbook create a summary to show the Total Sales, the Commission Earned and the Bonus for each State. Total Sales
Commission
Earned
Bonus
NSW
VIC
QLD
Quarterly Totals
1.
Use appropriate formula to link and insert data from your state sheets workbook.
2.
Columns must be wide enough to display data properly.
3.
Total each column to determine the total sales, commission and bonus for the quarter.
4.
Add a main heading at the top of the worksheet.
5.
Rename the sheet to Summary
.
6.
Insert a header as per the TTN Style Guide. 7.
Insert a footer: Left – Path and Filename, Right – Your name
8.
Print or Print in PDF form a formula copy of the Summary worksheet.
9.
Store the file to avoid damage or loss by saving the file to a secure location
10. Save your workbook as per the TTN Style Guide. 11. Take a screen shot of the file name and folder and paste into Theory Worksheet. 12. Exit Excel
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 15 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Task 5 – Charts and Graphs
1.
Open your Summary Workbook from Task 4.
2.
Determine which style of chart you should use to display the total sales for each state
at a designated timeline - after the end of the Quarter. Use an appropriate chart type
that will show which state has the larger percentage of sales against the other states.
In Theory worksheet determine styles of graph that would be suitable to meet the specified requirements and advise if manipulation of the spreadsheet table will be required.
3.
Insert your chosen chart type. Use appropriate data labels and titles. Ensure the chart
sits under the data on the worksheet.
4.
Change the page orientation to Landscape
5.
Print or Print to PDF a new copy of your summary and chart after the end of the Quarter. Remember, the spreadsheet and chart must fit on one landscape page when printed.
6.
Determine which style of chart would best display the Commission Earned and Bonus
for each state. 7.
Insert your chosen chart type for this data. Manipulate the chart so the data labels and titles are clear and suit the chart requirements.
8.
Move this chart to a separate sheet. Rename the sheet: Salespeople Payments
.
9.
Add an appropriate chart title.
10. Insert a footer: Left – Path and Filename, Right – Your name
11. View the chart and adjust the chart so it meets the requirements of the style guide.
12. Store the file to avoid damage or loss by saving the file to a secure location
13. Print or Print to PDF format the chart sheet showing Commission earned and Bonus after the end of the quarter.
14. Exit excel 15.
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 16 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Task 6 – VLOOKUP and Import/export
1.
Open the file BSBITU402_AE_Pro_2of2_SR1.xlsx
2.
Use the software help guide to determine how to import and export files. On Theory
worksheet write the search procedure asked to obtain the relevant help to import/export files (write the exact words used)
3.
Follow procedures to access data from BSBITU402_AE_Pro_2of2_SR2.txt
then import/export data into the Items
tabbed worksheet of the BSBITU402_AE_Pro_2of2_SR1.xlsx
file
4.
Take a Print screen or use snipping tool to capture evidence of the screen showing the imported file data in the excel file, and submit to your assessor when submitting this assessment. 5.
Go back to the Invoice
worksheet.
6.
In Cell G5
insert a formula
to automatically enter today’s date.
NOTE: When dragging your formulas down to other rows, ensure you Fill Without Formatting, so that you do not impact on the formatting of the invoice.
7.
In the Discount Column set up a formula using an IF function
that calculates 15% discount on the unit price when the quantity for an item is 20 or more.
8.
In the Price Column calculate the quantity x (price – discount).
9.
In G19
enter an appropriate formula to for the Subtotal.
10. In G21
enter an appropriate formula to subtotal the subtotal and freight.
11. In G22
enter an appropriate formula to add 10% GST onto your invoice (
NOTE
: GST is NOT included in the unit prices so we are adding on the GST).
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 17 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
12. In G24
enter an appropriate formula to add the Invoice Subtotal and GST together.
13. In C4 create a VLOOKUP formula to populate the Customer Name once we have entered the Customer ID.
14. In C5
create a VLOOKUP formula to populate the Customer Address once we have entered the Customer ID.
15. In the Description
column create a VLOOKUP formula to populate the Description once you have entered the Item Number.
16. In the Unit Price
column create a VLOOKUP formula to populate the Unit Price once you have entered the Item Number.
17. Insert your footer as per the style guide.
18. Header is not required, as you already have the date on the invoice.
19. Print to PDF form or Print, two copies of your invoice, one in formula view and one in data view, before you prepare the invoices below. Check that the invoice is correct.
20. Prepare and print to PDF form or print the following two invoices to check that your formulas work:
Customer 205, Invoice No. 210:
20 x Item 11
4 x Item 14
30 x Item 18
Freight = $24.00
Customer 203, Invoice No. 211:
15 x Item 10
5 x Item 12
25 x Item 19
6 x Item 13
Freight = $29.00
You will need to delete your formulas out of any rows that are not being used so that your calculations at the bottom of your invoice work.
21. Amend data as per Style Guide documentation, then save the change you have made to this excel file
22. Exit excel
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 18 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Part 2: Role play You are required to participate in a role play. You will need to request the assistance of another participant in this process and they will be playing the part of your manager.
For online students, you are required to record and upload this role play. You will need to request the assistance of another participant in this process and they will be playing the part
of your manager.
For other students, the role play options to the role play will be at the discretion of your Assessor.
Role play participants and responsibilities:
You – you are participating in a conversation with your manager.
Manager – online students will need to arrange either a colleague, a student completing this unit/course or industry expert/representative
to play this part. Ensure you provide them with a script to assist them. For other students, other options may be available at the discretion of your assessor
During the role play you will be participating in a conversation with your manager. The conversation will be to analyse and determine the specifications for the spreadsheet you created in Part 1 Task 1 above. Your assessor will be looking for you to demonstrate the following:
Active listening skills
Questioning skills
Good communications skills
Confirm and clarify the specifications of the spreadsheet.
Your demonstration will be used as part of the overall evidence requirements of the unit. You should refer to the list of criteria provided in Appendix 2
: Observation Checklist to understand what skills you need to demonstrate in this section of the assessment. This Checklist outlines the Performance Criteria, Performance Evidence and Assessment Conditions your assessor will be marking you on. Document title: BSBITU402_AE_Pro_2of2_LMS
Page 19 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Role play scenario:
You have received an email from you’re your manager setting out what is to be included in a spreadsheet that you will create. The instructions in the email are the same as the instructions found in Part 1 Task 1 above. In summary the spreadsheet will show:
sales figures
Sales persons commissions
Bonuses
Quarterly payments due to sales persons.
This meeting is taking place so you can clarify and confirm the specifications of the spreadsheet you will create.
REQUIRED: Sit down with the other participant (playing your manager) and analyse the spreadsheet task, your discussion could include:
The aim of the spreadsheet.
What particular information it needs to show.
How the information should be displayed.
Any other specifications that the spreadsheet should have.
Enter into a conversation with your manager, use questioning and listening skills to clarify and confirm the above points. The role play should run for around 5 minutes.
NOTE
:
If required instructions to access Learning platforms (e.g. Moodles) and or Microsoft teams or Adobe Connect will be provided and video recording instructions to assist in recording and submitting is provided by this link Video Recording Instructions for Role Play Assessments
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 20 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Appendix 1: Assessment checklist for Part 1
The following checklist will be used by your assessor to mark your performance against the assessment criteria of your submitted/presented project. Use this checklist to understand what skills and/or knowledge you need to demonstrate in your submission/presentation. All the criteria described in the Assessment Checklist must be met. The assessor may ask questions while the submission/presentation is taking place or if appropriate directly after the task/activity has been submitted/completed.
AC#
Marking Criteria
Task
#
Submitte
d?
S
US
Assessor Comments
Task 1 Plan and design
AC1a
Analyse and determine Specifications for spreadsheets.
Evaluate tasks to identify those where automation would increase efficiency.
1.3
1.4
AC1b
Utilised spreadsheet functions
to meet identified requirements.
1.10
AC1c
Macro-enabled Template saved by student
1.7
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 21 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
AC#
Marking Criteria
Task
#
Submitte
d?
S
US
Assessor Comments
Task 2 Formulae & 7Macro
AC2a
Utilised spreadsheet formulae
to meet identified requirements.
Formatted cells in accordance with task specifications.
Used data attributes with relative and/or
absolute cell reference in accordance with task specifications.
Use manual and documentation to overcome problems
with spreadsheet design and production.
2.4
AC2b
Create macros to automate operation
2.3
AC2c
Edit and use macros to
automate operation
2.6
AC2d
Develop appropriate templates in accordance with organisational requirements
2.7
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 22 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
AC#
Marking Criteria
Task
#
Submitte
d?
S
US
Assessor Comments
Task 3 Use template & enter data
AC3a
Edit and Use templates in accordance with organisational requirements.
3.5
AC3b
Preview, adjust and print or print to PDF
spreadsheet in accordance with organisational and task requirements
3.9
Task 4 Link Workbooks
AC4a
Linked workbooks in accordance with software procedures
4.8
AC4b
Name and store excel file in accordance with organisational requirements.
4.11
AC4c
Excel file saved and application exited without loss or damage
4.10
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 23 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
AC#
Marking Criteria
Task
#
Submitte
d?
S
US
Assessor Comments
Task 5 Charts and Graphs
AC5a
Determine style of graph to meet specified requirements and manipulate spreadsheet data if necessary, to suit graph requirements.
5.2
AC5b
Create graphs with labels and titles from numerical data
contained in a spreadsheet
(2 graphs to be created)
5.5
AC5c
Save, view and print or print to PDF graph within designated timelines
5.12
5.13
Task 6 VLookup /Import/Export
AC6a
Online help to overcome problems
with spreadsheet 6.2
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 24 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
AC#
Marking Criteria
Task
#
Submitte
d?
S
US
Assessor Comments
design and production AC6b
Access data, then Import and export data between compatible spreadsheets.
Perform complex work
tasks (task 1 import/export)
6.4
AC6c
Test formulae to confirm output meets task requirements.
Perform routine work task.
6.19
AC6d
Use manual and documentation to overcome problems
with spreadsheet design and production
Perform Complex work tasks ( Task 2 VLOOKUP)
6.21
Appendix 2: Observation Checklist The Observation Checklist will be used by your assessor to mark your performance of the role play. Use this Checklist to understand what skills you need to demonstrate in the role play scenario. The Checklist lists the assessment criteria used to determine whether you have successfully completed this assessment event. All the criteria must be met. Your demonstration will be used as part of the overall evidence requirements of the unit. Document title: BSBITU402_AE_Pro_2of2_LMS
Page 25 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
OC # Task/Activity Performed S U/S Assessor Comments (Describe the student’s ability in demonstrating the required skills and knowledge) DATE OF OBSERVATION
OC1
Communicates effectively with the manager. Assessors are to record their observations in sufficient detail to demonstrate their judgement of the student’s performance against the criteria required.
OC2
Uses questioning skills to clarify and confirm specifications and requirements
OC3
Uses listening skills to clarify and confirm specifications and requirements
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 26 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
NOTE:
For online students, complete and upload this assessment online via the TAFE NSW online learning platform. Upload all required assessment files in the assessment area on the online learning platform. Submit your completed assessment and printouts online as one PDF.
For all other students, complete and upload the practical tasks. The completed assessment and printouts can be submitted as one PDF. Alternatively, if submitting by hand to your assessor all printed copies of spreadsheets, invoices and any other documents you have been instructed to print, ensuring documentation is securely attached to the assessment.
Role play submission
For online students, you are required to record and upload this role play to the online learning platform as per submission instructions. For other students, the role play options to the role play will be at the discretion of your Assessor and will be advised.
Before submitting for marking ensure that the appendices - Assessment Checklist and Observation Checklist (role play) have been check and assessment has met criteria that has been advised. Document title: BSBITU402_AE_Pro_2of2_LMS
Page 27 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Assessment Feedback
NOTE: If you are submitting through the TAFE NSW online learning platform
, the assessor will give you feedback via the platform
NOTE: For manual feedback, this section must
have the assessor signature and student signature to complete the feedback.
This section is to be completed by the assessor. Once feedback has been provided by the assessor you will be given the opportunity to respond.
Additional evidence for verification of assessments
Additional questions asked by assessor
Assessors may ask additional questions to clarify student understanding. List here any additional questions that were asked during this assessment event.
Student responses to additional questions
Record the student responses to any additional questions that were asked during this assessment event.
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 28 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Assessment outcome
☐
Satisfactory
☐
Unsatisfactory
Assessor Feedback
☐
Has the Assessment Declaration been completed and confirmed by the student?
☐
Are you assured that the evidence presented for assessment is the student’s own work? ☐
Was the assessment event successfully completed?
☐
If no, was the resubmission/re-assessment successfully completed?
☐
Was reasonable adjustment in place for this assessment event?
If yes, ensure it is detailed on the assessment document.
Comments:
Assessor name, signature and date:
Student acknowledgement of assessment outcome
Would you like to make any comments about this assessment?
Student name, signature and date
NOTE: Make sure your name is at the bottom of each page of your submission before attaching the cover sheet and submitting to your assessor for marking
Document title: BSBITU402_AE_Pro_2of2_LMS
Page 29 of 29
Resource ID: TBS_18_007_BSBITU402_AE_Pro_2of2_LMS STUDENT NAME: CLARISSA LANGEBECK-MULLER
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help