Design and produce spreadsheets
Student Guide and Assessment
Competency-based assessment: Evidence will be gathered which is mapped to the requirements of the unit of competency.
The link between the unit and assessment tools can be found in the Assessment Outline & Mapping document.
All assessment is based on simulated businesses. Unless otherwise specified, for each assessment item student will be working as an administrative assistant for a small business, whose responsibility is create spreadsheets according to the business requirement. In student’s role as an administrative assistant, student is required to:
- Select and prepare resources
- Plan spreadsheet design
- Create spreadsheet
- Produce simple charts
- Finalise spreadsheets
Summary of Assessment Tasks
To successfully complete this unit of competency students are required to complete the following Assessment tasks:
- Assessment Task 1: Observation: Students must demonstrate that their workplace is ergonomically sound and that their work practices are healthy
- Assessment Task 2: Project: Students must plan, create, revise, and produce a final draft of a spreadsheet using Microsoft Excel and Google sheet.
Instructions to Student
- Each assessment item will include instructions on how to complete and submit the assessment
- Submit completed assessment items to your trainer by the agreed date.
- All assessment items submitted must include: student name; unit code/name;
- All assessment items submitted must be in Excel spreadsheet, unless otherwise stated, and clearly labelled and identify question, printout or task number
- Clarify any questions regarding this assessment with your trainer
Common office equipment, technology, software and consumables, including:
- You will need to ensure that the following items are available for students to complete their assessments:
- Access to textbooks and other learning materials.
- Computer and Microsoft Office.
- Access to the Internet.
- A chair and workstation (computer, monitor, keypad and mouse).
- Roleplay participant (assessor).
- Style Guide (provided as separate document).
- Spreadsheet Data (provided as separate document).
- Assessor versions of student documents for guidance with marking (provided as separate documents).
Refer to student course materials to assist student in completion of all assessment items
- Assessment will take place during class time and in your own time as required per each assessment task
- No marks or grades are allocated for the assessment. The task will be assessed as Satisfactory or Not Satisfactory
- Students will be assessed according to the criteria listed in the Marking Summary_Completion Record.
- The Marking Summary_Completion Record will also be used to provide feedback and record unit outcome/completion.
- Assessment Task 1: No submission required
- Assessment Task 2:
- Excel file: draft version
- Excel file: budget template final version
- Excel file: actual data final version
- A screenshot of your email to your assessor
Requirements to be deemed competent
- Students must satisfactorily demonstrate all assessment criteria for each assessment task to be deemed Satisfactory for each assessment task.
- All assessment tasks must be completed satisfactorily to be able to determine competent in this unit of competency.
- If any assessment task is deemed to have performed unsatisfactorily, students are required to be reassessed against the unsatisfactory assessment criteria only.
Trainer/assessor may make reasonable adjustments for students with LLN difficulties such as:
- verbal responses to written activities (such as questions and answers tasks and case studies)
- conducting assessor workplace observations via Skype or other video format (for example, in cases of students in remote areas)
- rescheduling assessor observations in the event that workplace conditions may not be suitable (for example, safety concerns, required resources not being available, lack of appropriate clients on the day, and so on)
Assessment Task 1: – ObservationSubmission details
The assessment task is due on the date specified by the assessor. Any variations to this arrangement must be approved in writing by the assessor.
You do not need to submit anything for this assessment task
Students will demonstrate that their workplace/works station is ergonomically sound and that their work practices are healthy.
- For this task you are required to demonstrate to your assessor that you understand and can apply the requirements for an ergonomically healthy and safe work station, as well as meeting work organisation and conservation requirements.
- 1. Carefully read the following resources:
- Demonstrate that you are able to set up an ergonomic/healthy and safe work station.
This will include:
- Adjusting your chair so that it is at the required height.
- Showing the required position of the wrists when typing.
- Showing the required position of the keyboard and mouse on the desk.
- Identifying the required distance of the screen from the eye and positioning the screen.
- Demonstrating exercises that can be performed during rest breaks. You are not required to perform athletics; merely show that you understand how exercise can help counteract the effects of sitting at a computer for long periods of time.
- Your assessor will also ask you some verbal questions at the end of the observation. The questions will relate to the following topics:
- The importance and frequency of regular work breaks
- Strategies to conserve resources.
Assessment Task 2: ProjectSubmission details
Students are required to submit on Moodle:
- 1st draft of your Excel spreadsheet with data under the name: filename_draft.
- Final version of your spreadsheet template (no data required)
- Final version of your spreadsheet as well as a screenshot of the folder where you saved your files. Ensure to save your final version as filename_final.
- The screenshot of your email to your assessor with the final version attached to your email.
- Carefully read the following:
- King Edward VII College is based in Melbourne, and has been operating for two years. The college is growing, has recently employed new staff, and is also seeking to improve operational practices.
- As part of the college’s growth, the operations manager has decided that an annual budget needs to be prepared each year. The operations manager wants you, as administration assistant to develop a report for the annual budget using excel spreadsheets.
- The budget spreadsheet needs to include the following worksheets:
- Annual budget summary: lists projected income and expenditure for July 2015 to June 2016.
- Projected versus actual quarterly budget for July to September 2015 and variation to budget.
- Produce 3 charts as following:
- Chart 1 – Bar chart – Monthly income: actual versus budget and variation
- Chart 2 – Column chart – Monthly expenditure: actual versus budget and variation
- Chart 3 – Line chart – Monthly profit: actual versus budget and variation
- Complete the following activities:
- Read through the information above, the style guide, and the spreadsheet data. When you are sure that you understand what is required of you, write an email to your assessor setting out, in your own words, the steps you will go through to create the spreadsheet.
If you have any doubts about the spreadsheet suiting its purpose and its audience, this is the opportunity to air these doubts.
Also, if you have any questions at all about this activity’s requirements, this email is the opportunity to ask them of your assessor.
- Develop a spreadsheet template, including all associated worksheets according to the requirements confirmed in your email, and as set out in the style guide.
- Enter the data provided in the spreadsheet data document into the spreadsheet.
- Save the spreadsheets according to the naming convention and in the required file.
- Send the screenshot, the spreadsheet template and the first draft of the spreadsheet with data and charts, as email attachments to your assessor.
- Meet with your assessor. Your assessor will answer the email with the date and time of a meeting to discuss the spreadsheets that you have developed.
The meeting will be held at your workstation. You will show your assessor the first drafts of the spreadsheets you have developed.
While at the workstation, you should keep in mind the ergonomic principles that you were asked about in Assessment Task 1. Take rest breaks away from the computer according to best practice guidelines.
At the meeting, you will also be asked to demonstrate the following:
- That the formulas you have entered into your spreadsheet are correct by calculating the totals for a data sample on a calculator. For example, calculate a period’s total income or expenditure, as well as checking the variation to budget totals.
- That you know how to use the help menu, as well as online help to access assistance in creating spreadsheets. Show your assessor on the help menu information about how to create charts, as well as online help that can be accessed to create charts.
- Show your assessor that you can change fonts and font size.
- Show your assessor that you can change the chart type and layout for any one of the charts you created.
- That you understand and will adhere to the company’s conservation guidelines.
- Show your electronic filing system stores your files in.
- Show your assessor how you close down the program(s) you have been using, and turn off the computer.
- Open Google sheet and copy your file to Google sheet. Show your assessor how to check your formular on Google sheet and how to access the help function on Google sheet for using the average function.
- When your meeting has been concluded, finalise the draft of your document, ensuring that it contains any changes that your assessor may have suggested. Proofread the documents that you have saved. You should find at least one change (perhaps wider columns or position more centred on the page or any formatting requirement) that you wish to make to each worksheet.
- Set print area for all worksheets for all worksheets to ensure they fit in one A4 page.
- Produce a final draft of the spreadsheet that incorporates the changes that your assessor suggested and those you selected in the previous activity. Save this version of the file as filename_final and take a screen shot of the final folder and files.
- Email the final version of the spreadsheet with relevant charts and the folder screenshot to your assessor as an attachment within the designated timeline. Ensure your email meets the requirements of the style guide.
- Submit the following on Moodle:
– Excel file: draft version
– Excel file: final version
– A screenshot of your email (from step 10 above)
Congratulations students have completed all assessment requirements for this unit of competency.
|BSBITU314 Student Guide and Assessment||Page 8 of 8||Ver.1.0 Version Date: 13/03/2021|
Global Business College of Australia Pty Ltd trading as Global Business College of Australia
ABN 96 600 373 859 RTO no. 41292 CRICOS Provider no. 03443D