BSBITU402 – Develop and use complex spreadsheetsMGT603: Systems ThinkingBSBMGT616 Develop and implement strategic plansBSBMGT616 Develop and implement strategic plansBSBMGT616 Develop and implement strategic plansIndividual Research Proposal Assignment BISM7217

FIND A SOLUTION AT Academic Writers Bay

Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 1 of 14
Last Modification Date
March – 2020
Verified By: Training Manager
Proposed Review Date
March – 2021
Approved by: CEO
Version no.
OPIE- 001
Assessment Resource Summary
Unit Details
BSBITU402 – Develop and use complex spreadsheets
Assessment Type
This is a summative assessment, which requires each
student to have adequate practice prior to undertaking this
assessment
This assessment has been contextualised for Building and
Construction, and must be undertaken in a real or
simulated Building and Construction environment
Assessment
Projects
Assessment 1 – 4
Written assessment
Assessment 5
(Written Assessment attached)
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 2 of 14
Unit Summary
This unit describes the skills and knowledge required to use spreadsheet software to complete business
tasks and produce complex documents.
It applies to individuals employed in a range of work environments who require skills in creation of complex
spreadsheets to store and retrieve data. They may work as individuals providing administrative support
within an enterprise, or may be independently responsible for designing and working with spreadsheets
relevant to their own work roles.
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 3 of 14
ASSESSMENT SUMMARY / COVERSHEET
This form is to be completed by the assessor and used a final record of student competency.
All student submissions including any associated checklists (outlined below) are to be attached to this cover
sheet before placing on the students file.
Student results are not to be entered onto the Student Database unless all relevant paperwork is completed and
attached to this form.
Student Name:
Student ID No:
Final Completion Date:
Unit Code:
BSBITU402
Unit Title:
Develop and use complex spreadsheets
Please attach the following documentation to this form:
Result
S = Satisfactory
NS = Not Satisfactory
Assessment 1
 Explanatory memorandum for staff “explanatory
memorandum” format attached
S | NS
Assessment 2
 Template of expense sheet hard copy with
complete formatting attached
 Business letter to the sales manager on benefits
of automation attached
S | NS
Assessment 3
 Document how you created a test plan to check
the calculations attached
S | NS
Assessment 4
 Explanatory document on help option followed
S | NS
Assessment 5
 Print out of the chart/graph attached
 Document explaining why the particular chart
was selected attached
S | NS
Assessment 6
 Written assessment attached
S | NS
Final Assessment Result for this unit
C / NYC
Student Feedback:
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 4 of 14
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
Student Declaration: I declare that I have been
assessed in this unit, and I have been advised of my
result. I also am aware of my appeal rights.
Name: ________________________________
Signature: ____________________________
Date: ____/_____/_____
Assessor Declaration: I declare that I have
conducted a fair, valid, reliable and flexible
assessment with this student, and I have provided
appropriate feedback
Name: ________________________________
Signature: ____________________________
Date: ____/_____/_____
Administrative use only
Entered onto Student Management Database
________________
Date
Initials
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 5 of 14
Student Instructions
BSBITU402 – Develop and use complex spreadsheets
Metharom Construction (MC) plans to become a leading provider of housing construction and
renovation services in the South-East Melbourne area. The company’s overall strategy will be based
on a continuing improvement process of setting objectives, measuring results, and providing
feedback to facilitate further growth and progress. They have been involved in large scale
construction for 3 years and are now branching out to residential housing.
During this unit, you will need to take on the role of an administration officer. Your responsibilities
are to: provide supporting documentation for financial records, employee engagement, managing
employee expenses.
For more information regarding Metharom Construction, please see the appendices and company
policy documentation.
Assessment 1: Prepare an explanatory memorandum regarding ergonomics and energy
conservation. (Maximum of 250 words)
Metharom has been experiencing increased overhead costs over the past quarter. The administration
manager has investigated, and found that the increased costs are due to two factors: increased
energy usage, and an increased amount of sick days for the clerical staff.
You are required to create an explanatory memorandum for staff:
a) explaining a minimum of 5 ergonomic practices and
b) a minimum of 5 energy conservation techniques.
You will need to ensure that:
• you refer to Metharom’s Document style guide
• Your document can be easily read and understood by all employees of Metharom Construction
• The document that you create is visually appealing
• The content has been proofread and edited to suit the audience
This assignment should be no more than 250 words.
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 6 of 14
Assessment 2: Create a template for the sales team to record their quarterly expenses, and make a
recommendation to improve efficiency and accuracy of documents submitted to accounts.
(Maximum of 250 words)
Metharom’s sales team is required to submit an expense report on a quarterly basis. Recently, as the
number of sales has increased, the reports have had a higher rate of errors. In order to prevent this
from happening again, you as an administration officer will need to create an automated sheet to
assist the sales staff.
Your expense report template must include:
A) Name this sheet as ‘Expenses’ and save it in the required format. Calculate total for each
expense using formula
B) Apply conditional formatting to highlight all expense higher than 100 in red colour
C) Link the above expense sheet with a new sheet in the same workbook and name it as “new
expense” so that any changes made on expense will also reflect in new expense sheet.
D) Write a business letter to the sales manager that recommends a minimum of two other
documents that could be automated, and explains the benefits of automation.
You will need to submit both a print copy of the sheet and soft copy of the template as well as the test
document to your assessor.
Maximum of 250 words.
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 7 of 14
Assessment 3: Using macros and absolute reference
The data reflects payments to the subcontractors for last three quarters which were pending for
approvals.
A) Write the Formula to calculate tax for quarter 1 using an Absolute Reference and then
copy the formula to Quarter 2 and quarter 3 to get amount.
B) Create and record a macro and name the macro as tax
C) Create a test plan to ensure the calculations are correct.
Appendix B
This assessment must be submitted in both hard and soft copy format.
Assessment 4: Import and export data on spreadsheet (Maximum of 150 words)
Data has been provided in Appendices in text format.
You are required to refer to help option on excel and do the following tasks
A) Import the data to an excel file and format. Name and store spreadsheet as “customer” in
accordance with organisational requirements and exit application without data loss or damage,
sort the data in ascending order of names
Headings: bold
font: Arial
font size: 12
colour : black
B) Once the formatting has been completed export the data to text format. (Word document).
Ensure the spreadsheet and the word document is linked. Provide a detailed explanation step wise of
how you have used the online help option. Submit the print copy to your assessor.
Appendix C: Data
Tax rate: 10%
Quarter 1 Quarter 2 Quarter 3
Sub contractor 1 $2,500 $1,250 $3,052
Sub contractor 2 $3,500 $678 $1,689
Sub contractor 3 $1,890 $1,670 $567
Tax:
Copying a Formula Using an Absolute Reference
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 8 of 14
Assessment 5: Create a pie chart to represent costs of subcontractors.
The operations manager analysing subcontracting expenses to determine if hiring additional staff for
the work team, would be more cost effective than hiring subcontractors. In order to do this, she has
requested that you
A) Create a chart/graph with labels that represents the percentage of funds spent on subcontractors
for each trade.
B) Explain the use of pie charts
C) Save, view, adjust and print chart and submit to assessor
Refer to the appendices for additional data to assist you. As this is an official Metharom document,
ensure that you refer to and abide by the style guide in the company folder.
Note:
The work you submit must be your own. Studentshave the responsibility to maintain the
highest ethical standards of academic integrity in their work.Academic misconduct by
students in examinations or in other forms of assessment is unacceptable.
Prior to submitting an assessment, each student will be required to complete an
assessmentcover sheet which will ask them to sign a declaration to say they have not
plagiarised and thatthey have read and understood the OPIE Plagiarism policy.
All work submitted will be checked to ensure authenticity.
Students found to have plagiarised will be forced to re-submit the assessment and be required to pay
the re-submission fee of $350 per unit of competency and may be subject to disciplinary action.
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 9 of 14
ASSESSMENT 6 – WRITTEN ASSESSMENT
Student Name: ______________________________________________________________
Student ID No: ______________________________________________________________
Date: ______________________________________________________________
Please refer to the instructions below regarding completing this assessment
Written Assessment
This is a closed book written assessment. In the time allocated, you are to answer all of the following
questions. Make sure you:

Print Clearly



Answer all questions
Use a black pen. Assessments written in pencil will not be accepted.
Ask your assessor if you do not understand a question. Whist your assessor cannot tell you the
answer, he/she may be able to re-word the question for you
Do not talk to your classmates. If you are caught talking, you will be asked to leave and your
assessment will not be marked.
Do not cheat or plagiarise the work of others. Anyone caught cheating will automatically be marked
Not Yet Competent for this unit. There are NO EXCEPTIONS to this rule.


Questions
1
Explain any two benefits of a good design for spreadsheet?
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 10 of 14
2
2. Explain the following terms:
Cell Reference
Relative Reference
3
Name five strategies for conserving resources in an office environment
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 11 of 14
4
Name 3 data formats available in Excel and their purpose
5
What are the two benefits of ergonomics processes?
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 12 of 14
6
Explain three positive impacts of resource efficiency in Business
7
If you have a list of numbers in cells B2 through B20 and you wanted the average of these
numbers, what would be the formula?
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 13 of 14
8
What types of calculations can you perform using the SUM function?
9
Relevant to Microsoft excel, what is the purpose of a macro?
Address: 67 JEFFCOTT STREET, WEST MELBOURNE VIC 3003
Email: info@opie.vic.edu.au | Phone: 0396025555 | Website: opie.vic.edu.au
Page 14 of 14
Student Declaration: I declare that the work submitted is
my own, and has not been copied or plagiarised from any
person or source.
Name: ________________________
Signature: ________________________
Date: ____/_____/_____
END OF WRITTEN ASSESSMENT
Before you hand in your written assessment, make sure that you:
1. Re-check your answers and make sure you are happy with your responses
2. Have written your Name, Student ID Number, and Date on the first page
ASSESSOR USE ONLY
Comments
Assessment Method: WRITTEN / PROJECT
Result
(Please Circle)
Satisfactory
Not Satisfactory
Incomplete
Assessor: I declare that I have conducted a fair, valid,
reliable and flexible assessment with this student, and I
have provided appropriate feedback
Name: ________________________
Signature: ________________________
Date: ____/_____/_____
10
Name and explain any three types of charts or graphs that can be used for comparisons.

YOU MAY ALSO READ ...  significant role in marketing products
Order from Academic Writers Bay
Best Custom Essay Writing Services

QUALITY: 100% ORIGINAL PAPERNO PLAGIARISM – CUSTOM PAPER