Student Name Student Number Unit Code/s & Name/s BSBTEC402 Design and produce complex spreadsheets
Student Name Student Number Unit Code/s & Name/s BSBTEC402 Design and produce complex spreadsheets
Cluster Name If applicable N/A
Assessment Name Design and produce a complex spreadsheet Assessment Task No. 3 of 3
Assessment Due Date Date submitted / /
Assessor Name Student Declaration: I declare that this assessment is my own work. Any ideas and comments made by other people have been acknowledged as references. I understand that if this statement is found to be false, it will be regarded as misconduct and will be subject to disciplinary action as outlined in the TAFE Queensland Student Rules. I understand that by emailing or submitting this assessment electronically, I agree to this Declaration in lieu of a written signature.
Student Signature Date / /
Instructions to Student General Instructions:
In this assessment you will be required to work on spreadsheets created in both Project One and Project Two from assessment 2.
This assessment task requires you to demonstrate the skills and knowledge requirements to:
Use a template to enter data
Edit a macro
Create charts
Export data
Plan the requirements of a template
Create the template meeting both organisational and task requirements
Test the template to ensure all elements work correctly.
Using the Oliver Trading Simulation, you will continue with the two projects started in Assessment 2. In this assessment the following will be required:
Project One - In the previously created template from Oliver Trading simulated workplace you will now enter data as given, create a chart and a summary spreadsheet and print completed spreadsheet.
Project Two - In the previously created workbook from Oliver Trading simulated workplace you will now create a new template and test this template for accuracy and usability.
Assessment Conditions:
Time Allowed:
This task is to be submitted by the due dates advised in the Unit Study Guide. If a student requires an extension, then they must negotiate this with their assessor prior to the due date.
Level of Assistance Permitted (If any):
This is an open book assessment task. All work must be your own.
Students may:
refer to learning resources, workplace and/or research further information
ask the Assessor clarifying questions
access the services of Studiosity to assist in interpreting questions or proof-reading text
be referred to learning support for additional assistance
Students must seek any required assistance, reasonable adjustment or negotiate extensions with Assessor prior to due date.
Location:
This assessment is to be completed in a simulated workplace including the classroom or home office environment.
Assessment Criteria:
To achieve a satisfactory result, your assessor will be looking for your ability to demonstrate the following key skills/tasks/knowledge to an acceptable industry standard:
Adhere to organisational requirements
Adhere to identified or task requirements when producing documents
Use appropriate data storage options
Meet the timeline of the task which is the due date given above.
Apply knowledge of functions and features of Excel application. These may include use of templates, macros, advanced formula/functions, linking and charts
Materials required:
Computer equipment and Excel software.
An ergonomically safe work environment.
Assessment files graded satisfactory from assessment 2.
Materials supplied:
Expense claim summary test data.docx
Oliver Trading organisational procedures Appendix 2
Work, Health and Safety:
follow TAFE Queensland Student Rules
access TAFE Queensland Student Services for any additional support
conduct risk assessments prior to any assessment task to ensure the safety of all participants and the environment
ensure an ergonomically safe work environment to complete all assessment tasks.
Submission details(if relevant) Students must produce Microsoft Excel documents for their portfolio of evidence as requested and print copies as pdf (online students) as requested.
Assessment to be submitted via:
TAFE Queensland Learning Management System: Connect url: https://connect.tafeqld.edu.au/d2l/loginUsername; 9 digit student number
For Password: Reset password go to: https://passwordreset.tafeqld.edu.au/default.aspxFiles to Submit - Seven (7)
Cashbook Qtr 1 file - macro enabled workbook - appropriately named as per procedures (excel file) (Project 1)
Cashbook Expenses Total Table (pdf file) (Project 1)
Cashbook Qtr 1 file saved as .csv file (Project 1)
Summary Expense Claims Template file saved as a macro enabled template file (Project 2)
Summary Expense Claims Test file (Project 2)
Summary Expense Claims Test printed as a pdf document (Project 2)
Screen shot of storage location showing files stored and named for this assessment (Projects 1 and 2)
Number of Attempts:
You will receive up to two (2) attempts at this assessment task. Should your 1st attempt be unsatisfactory (U), your teacher will provide feedback and discuss the relevant questions with you and will arrange a date for your 2nd attempt. If your 2nd attempt is unsatisfactory (U), or you fail to attend the scheduled date for a 2nd attempt, you will receive an overall unsatisfactory result for this assessment task. Only one re-assessment attempt may be granted for each assessment task.
For more information, refer to the Student Rules.
Instructions to Assessor Assessors are to:
confirm expectations, as detailed in Instructions to Student, have been followed and met
provide feedback on all assessment attempts and identify additional learning and/or practice required by the student before their second attempt.
use the Marking Criteria to record their assessment and feedback. Please mark work for consistency against the supplied benchmark solutions and use the marking criteria sheet provided for feedback to students.
ask for further clarification either in a face-to-face manner, by phone or written correspondence where they feel more information is needed. They can negotiate to complete assessment tasks and/or resubmissions verbally, where the opportunity presents:
scribe the students response verbatim on the assessment task/marking criteria, and note the questions verbally addressed on the coversheet.
initial and date additional comments.
Reasonable Adjustment:
Assessors are able to use discretion if any reasonable adjustment is to be made in regards use of equipment or production of spreadsheet as long as the requirements as given are met.
Work Health and Safety:
A work health and safety check of the assessment environment is to be conducted prior to the assessment as addressed in the points under Instructions to Students.
Note to Student An overview of all Assessment Tasks relevant to this unit is located in the Unit Study Guide.
Oliver Trading Scenario
You work as an administration assistant in the Accounts Department of Oliver Trading. Your supervisor, Ethan Venner, has given you two projects (see descriptions below) to complete over the next two weeks. Your deadline for completion of these tasks will be the due date for this assessment as outline in your study guide.
The Spreadsheet Procedures Guidelines document has been supplied to you in Appendix 2. It provides the organisational requirements in regards creating, modifying and storing spreadsheet files. Please ensure you follow these guidelines unless otherwise instructed.
The guidelines include energy and resource conservation. You are to apply paper saving techniques by making all edits on screen and emailing/publishing completed files, rather than printing and posting.
Storage
Continue to use the created folder from assessment 2, called Account Dept_initials. Where initials will be your initials eg Accounts Dept_SK. Save all your work, correctly named according to organisational naming requirements, to this folder. You will be required to present a screen shot of your assessment folder upon completion of all tasks. On completion of the tasks, take a screen shot of your Account Dept_initials assessment folder and upload it with your assessment files.
3574415123825 TAFE Queensland, 2021
0 TAFE Queensland, 2021
Project One
Task Description Review
You were to create a cash book that could be used by staff for recording of expenses that have occurred during each month. This cash book will give the expense description and costs and then also break each expense down into one of four expense categories. Ethan completed a very rough sketch.
There will be one spreadsheet workbook for each quarter. The workbook for the quarter will contain 4 worksheets, one for each of the months and the 4th for the summary sheet which will include a graph to display summary data visually.
In assessment 2 you analysed what was required and created the template and test data files. In this assessment you will now use the approved template to enter this quarters data. (This is the Cashbook macro-enabled template file that was marked as Satisfactory by your assessor in Assessment 2.) Remember to continue to follow the organisational guidelines and to adjust your workstation to meet own personal ergonomic needs.
Project One - PART TWO continuing on from assessment 2
Task Six - Use the cashbook template
6.1Create a new workbook based on your approved cashbook template. Save and name this spreadsheet to show that it contains Qtr 1 data ensure the spreadsheet is saved as a macro-enabled document.
6.2Name sheet 1, January; Sheet 2, February; Sheet 3, March; Sheet 4, Qtrly Totals (insert extra sheets as required)
6.3Copy your cashbook on the January sheet across to February and again across to March
6.4Enter correct month names on each sheet in the cell provided for Month.
Task seven - Enter Data
Note: Wages have NO GST so ensure your formula for wages indicates a zero. Do NOT override your formula to just type 0.
7.1Insert the following transactions for January, February and March.
January
Date Cheque No Code Amount GST
Jan 3 101 W 125.50 0
Jan 9 102 R 300.00 ?
Jan 14 103 V 200.00 ?
Jan 20 104 E 236.00 ?
Jan 27 105 W 130.00 0
Jan 28 106 V 350.00 ?
February
Date Cheque No Code Amount GST
Feb 9 107 W 130.00 0
Feb 10 108 R 300.00 ?
Feb 12 109 V 187.50 ?
Feb 23 110 E 212.50 ?
Feb 25 111 W 141.00 0
Feb 27 112 V 359.00 ?
March
Date Cheque No Code Amount GST
Mar 9 113 W 125.00 0
Mar 11 114 R 300.00 ?
Mar 12 115 V 47.50 ?
Mar 18 116 E 350.50 ?
Mar 26 117 W 130.00 0
Mar 27 118 V 129.00 ?
Check your data for accuracy and resave.
Task Eight - Link and Print
8.1On Sheet 4 Qtrly Totals - use your edited macro that was resaved to your template, to add the Business name to the top of the sheet. Add an appropriate main heading and subheading to describe the table.
Prepare a table, which will give the totals of each expense category for each month. Prepare formulas in this sheet, which will link the totals for each expense from sheets 1 - 3. (January-March).
Format the Total row to currency, 2 decimals and the other figures to number and 2 decimals.
Month Wages Electricity Rent Vehicle expenses
January February March Totals $ $ $ $
8.2Preview the spreadsheet. Ensure all your sheets have the appropriate footer. Amend if necessary.
8.3Recheck organisational procedures for document layout for printing. Eg orientation, horizontal/vertical centring and data alignment. Adjust your spreadsheet to ensure it meets all organisational requirements. Resave. Remember to maintain version control.
8.4Print a copy of Sheet 4 Qtrly Totals. If you are an online student, print this as a pdf document.
Task Nine - Charts
9.1Prepare a chart (on a separate sheet - Chart 1), which will give a comparison of the total expenses for each expense for Qtr 1. Choose the most appropriate chart type that would be suited to just one series of data.
9.2Give the chart appropriate Title(s).
9.3Include a separate legend and label each section with the value amount. Check you have included the required footer.
9.4Upon rechecking the data figures, you have found an error and cheque number 115 should have been 475.00. Amend this figure and resave.
9.5As your supervisor Ethan, wishes to share this file with other managers who may have different versions of spreadsheet software, resave your Cashbook Qtr 1 file again but this time as a .csv document. You will just save the Qtrly Totals worksheet to csv format.
Project Two
Task Description
In assessment 2, you were asked to prepare a new spreadsheet to show a Summary of Staff Expense Claims for early September. Ethan, your supervisor, would like to have this information available for review at future Managers meetings. He would like you to create a template that can be placed on the companys intranet and used for future summary reports of expense claims. Ethan would also like the template to highlight when any expense exceeds the limit for the period. All files are to be stored in the previously created folder from assessment 2 - Account Dept_initials. Where initials will be your initials eg Accounts Dept_SK. Remember to continue to follow the organisational guidelines and to adjust your workstation to meet own personal ergonomic needs. Ensure you take a break and move around before commencing Project Two.
Task One - Create a Template
1.1 Open the spreadsheet for Summary Expense Claims _date, created in assessment 2.
1.2Resave the file as a macro-enabled template and rename this spreadsheet to add the word template to your file name instead of a date eg if the filename was previously Summary Expense Claims_date, as a template it will now be called Summary Expense Claims _Template_v1.
1.3Clear all data from the data entry sections (blue sections). Ensure you retain the blue formatting.
1.4Remove the SUMIF Help and Macro screen sheets. (Make sure not to delete the hidden Lookup Data sheet
1.5Amend both chart types to a horizontal graph.
1.6Unhide the Lookup Data sheet and copy the table given here onto this sheet in an appropriate space
Conditional Limit
Meals 350
Kms 450
Accom 400
Fares 1200
1.7On the Summary Expense Claims sheet, create conditional formatting in the Breakdown Summary table to highlight if the amount is greater than these limits.
Have the conditional format show red font on a coloured background. Below the table, add the words in red amount will show red if exceeds the set limit
1.8Re-hide the Lookup Data Sheet
1.9Resave as a macro-enabled template and close the template.
Task Two - Testing the template
2.1Use the template and run some test data to ensure all components perform correctly. Test data is supplied in Summary Expense Claims _Test Data.docx.
2.2Resave the test spreadsheet as a macro enabled workbook titled Summary Expense Claims _Test
2.3Check your formatting, removing any unnecessary blank lines and check dates entered for time period and the charts.
Task Three - Publishing and Printing the Template
3.1Simulate publishing a copy of the template to Oliver Trading Intranet by uploading your completed template to your assessment folder in Connect.
3.2Print the test workbook. Online students - if you are submitting electronically then print as a pdf.
Final Task - Storage
Recheck your storage folder and filenames and submit a screen shot of your storage folder for this scenario. Check that the following files have been included.
Cashbook Qtr 1 file - macro enabled workbook appropriately named as per procedures (excel file) (Project 1)
Cashbook Expenses Total Table (pdf file) (Project 1)
Cashbook Qtr 1 file saved as .csv file (Project 1)
Summary Expense Claims Template file saved as a macro enabled template file (Project 2)
Summary Expense Claims Test file (Project 2)
Summary Expense Claims Test printed as a pdf document (Project 2)
Screen shot of storage location showing files stored and named for this assessment (Projects 1 and 2)
Date of Expense Employee ID Expense Code Working Amount
24/08/2020 S1547 EM 125
18/08/2020 C1724 EM 55
23/08/2020 S1547 EF 250
22/08/2020 P1824 EA 280
19/08/2020 B4879 EA 150
29/08/2020 S1547 EM 65
19/08/2020 P1824 EK 130
Student Name Student Number Unit Code/s & Name/s BSBTEC402 Design and produce complex spreadsheets
Cluster Name If applicable N/A
Assessment Type Assignment Project Case Study Portfolio Third Party Report (Workplace) Third Party Report (Peer) Other
Assessment Name Design and produce a complex spreadsheet Assessment Task No. 3 of 3
Assessment Due Date Date Submitted / /
Assessor Feedback:
Attempt 1 Satisfactory Unsatisfactory Date / /
Assessor Name Assessor Signature Student provided with feedback and reassessment arrangements (check box when completed) Date scheduled for reassessment / /
Attempt 2 Satisfactory Unsatisfactory Date / /
Assessor Name Assessor Signature Note to Assessor: Please record below any reasonable adjustment that has occurred during this assessment e.g. written assessment given orally.
Assessment Criteria / Benchmarks
The evidence submitted demonstrates that the student has satisfactorily: Attempt 1 Attempt 2
Date
__/__/__ Date
__/__/__
Y N Y N
Workstation adjusted to meet individual needs (project scenario) Micro breaks taken between projects Used the approved template (Project 1: task 6) Entered data through importing data and data entry and then (Project 1: tasks 6 &7) Checked data for accuracy and amended where required according to organisational requirements(Project 1: tasks7) Created a linked tabled between multiple worksheets (Project 1: task 8) Printed worksheets as per task requirements (Project 1: task 8) Analysed and created an appropriate graph to meet task requirements (Project 1: task 9) Format graph according to organisational and task requirements including labels and titles. (Project 1: task 9) Exported data by saving between compatible spreadsheet modes. (Project 1: task 9) Files named and stored as per requirements (storage screen shot) (Project 1 Final storage tasks) Created a template that met organisational and task requirements and used design features to enhance readability and presentation (Project 2: task 1) Entered data and then checked data for accuracy (Project 2: task1) Amend the template to display charts as bar charts and add conditional formatting (Project 2: task 1) Amended template to include conditional formatting (Project 2: task 1) Rehid the Lookup Data sheet (Project 2: task 1) Resaved the template as a macro-enabled template. (Project 2: task 1) Used the approved template (Project 2: task 2) Tested the template by running sample data (Project 2: task 2) Entered data through importing data and data entry and then checked data for accuracy and amended where required according to organisational requirements (Project 2: task 2) Printed worksheets as per task requirements (Project 2: Task 3) Files named and stored as per requirements (storage screen shot) (Project 2 Final storage tasks)
Student Name Student Number Unit Code/s & Name/s BSBTEC402 Design and produce complex spreadsheets
Cluster Name If applicable N/A
Assessment Type Assignment Project Case Study Portfolio Third Party Report (Workplace) Third Party Report (Peer) Other
Assessment Name Design and produce a complex spreadsheet Assessment Task No. 2 of 3
Assessment Due Date Date Submitted / /
Assessor Feedback:
Attempt 1 Satisfactory Unsatisfactory Date / /
Assessor Name Assessor Signature Student provided with feedback and reassessment arrangements (check box when completed) Date scheduled for reassessment / /
Attempt 2 Satisfactory Unsatisfactory Date / /
Assessor Name Assessor Signature Note to Assessor: Please record below any reasonable adjustment that has occurred during this assessment e.g. written assessment given orally.
Assessment Criteria / Benchmarks
The evidence submitted demonstrates that the student has satisfactorily: Attempt 1 Attempt 2
Date
__/__/__ Date
__/__/__
Y N Y N
Workstation adjusted to meet individual needs (project scenario) Micro breaks taken between projects Named and stored files according to organisational procedures (Final storage task) Completed analysis and planning document (Project 1: task 1) Created a template that met organisational and task requirements and used design features to enhance readability and presentation (Project 1: task 2) Imported data from the supplied text file (Project 1: task 2) Created a macro that met task requirements and automated tasks (Project 1: task 2) Created a macro that met task requirements and automated tasks (Project 2: task 4) Analysed and used appropriate formulas and functions to perform required tasks including formulas that required absolute referencing (Project 1: task 3) Edited the macro to meet changed task requirements (Project 1: task 4) Tested the formulas to confirm correct outputs from data (Project 1: task 5) Analysed the designed spreadsheet template for efficiency (Project 1: task 5) Analysed and created macro enabled workbook meeting task requirements and organisational procedures (Project 2: task 1) Used help to research task and summarised findings (Project 2: task 2) Audio or video supplied demonstrating oral communication in clarifying tasks required. (Project 2: task 2) Analysed and used appropriate formulas and functions to perform required tasks including formulas that required absolute referencing (Project 2: task 3) Create a macro to meet task requirments (Project 2: task 4) Test the macro to meet task requirments by running on the SUMIF help sheet (Project 2: task 4) Created chart(s) to meet task requirements and organisational procedures (Project 2: task 5) Edited the macro to meet changed task requirements (Project 2: Task 6) Format data entry cells to pale blue (Project 2: Task 7) Format page orientation to landscape (Project 2: Task 7) Format Summary Expense Claims worksheet to fit to one page (Project 2: Task 7) File saved as macro-enabled workbook (Project 2: Task 7) Any blank rows removed in worksheet table (Project 2: Task 8) Worksheets are labelled (Project 2: Task 8) Written sentence provided on method used to test formulas (Project 2: Task 8) Printed worksheets and graphs as per task requirements (Project 2: Task 8)
As part of your analysis, planning and preparation for this document, complete the following.
You will need to carefully analyse the scenario and task requirements given in assessment 2.
Purpose of document(s): Target Audience: Scenario Task requirements: Organisational requirements (Oliver Trading): Analysis of design - include here:
what formulas/functions you may use;
what formats you may use e.g. titles, column headings, layout, logo;
any chart requirements; what type of chart you may choose to use. Files needed to complete task: State how you will review and perform final tasks check: Timeframe - Required by: Contingency Management - A common occurrence in the workplace at present is that once a lot of work has occurred on the production of a document/spreadsheet, someone then changes their mind about what is required or determines more needs to be added. To prevent the stress of working through all the task requirements as given and then at the last minute having these task requirements changed what contingency management plans could you put in place to help ease this situation from occurring? Further Contingency what plans do you have in place in case anything should go wrong with your plan e.g. cannot meet the deadline?
Note contingency plans are what you put in place BEFORE things go wrong.
Student Name Student Number Unit Code/s & Name/s BSBTEC402 Design and produce complex spreadsheets
Cluster Name If applicable N/A
Assessment Name Design and produce a complex spreadsheet Assessment Task No. 2 of 3
Assessment Due Date Date submitted / /
Assessor Name Student Declaration: I declare that this assessment is my own work. Any ideas and comments made by other people have been acknowledged as references. I understand that if this statement is found to be false, it will be regarded as misconduct and will be subject to disciplinary action as outlined in the TAFE Queensland Student Rules. I understand that by emailing or submitting this assessment electronically, I agree to this Declaration in lieu of a written signature.
Student Signature Date / /
Instructions to Student General Instructions:
You are required to commence two spreadsheet projects in Excel using the Oliver Trading simulated workplace scenario provided on page 5.
This assessment task requires you to demonstrate the skills and knowledge requirements to:
Plan the requirements of a template
Create the template meeting organisational and task requirements
Test the template to ensure all elements work correctly
Create a spreadsheet that meets user requirements and organisational specifications.
Import supplied data
Accurately enter data supplied.
Project One will cover the production of a template. Planning and development will occur in Part One - this assessment.
You will then be required to use the approved (by your assessor) template in Part Two - Assessment 3. Therefore, when planning for the scenario task please consider the whole scenario as given on page 5.
Project Two will require preparation, development and use of a spreadsheet to summarise staff expense claims for the first two weeks in September. Details are given on page 8. In assessment 3, you will be required to create a template from this spreadsheet workbook.
Assessment Conditions:
Time Allowed:
This task is to be submitted by the due dates advised in the Unit Study Guide. If a student requires an extension, then they must negotiate this with their assessor prior to the due date.
Level of Assistance Permitted (If any):
This is an open book assessment task. All work must be your own.
Students may:
refer to learning resources, workplace and/or research further information
ask the Assessor clarifying questions
access the services of Studiosity to assist in interpreting questions or proof-reading text
be referred to learning support for additional assistance
Students must seek any required assistance, reasonable adjustment or negotiate extensions with Assessor prior to due date.
Location:
This assessment is to be completed in a workplace or a simulated workplace including the classroom or home office environment.
This task includes activities which require you to demonstrate practical and communication skills which need to be observed by your Assessor.
These skills and activities can be demonstrated and observed in any one of three ways:
in class
online
video recording.
(NB! Options available at the assessors discretion.)
If recording please confirm your identity as the student being assessed with name and student number, plus record consent from all participants at the beginning of the task.
You may use this comment, I am (insert student name and number). This activity is for the purpose of assessment only and will be recorded, viewed and stored following TAFE Queensland procedures. All participants consent to being recorded for these assessment purposes.
If recording please record and upload in maximum 5 minute segments.
Assessment Criteria:
To achieve a satisfactory result, your assessor will be looking for your ability to demonstrate the following key skills/tasks/knowledge to an acceptable industry standard:
Adhere to organisational requirements
Adhere to identified or task requirements when producing documents
Resolve issues by referring to user documentation and online help
Use appropriate data storage options
Apply knowledge of functions and features of Excel application. These may include use of templates, macros, advanced formula/functions, linking and charts
Meet the timeline of the task which is the due date given above.
Materials required:
Computer equipment and Excel software
An ergonomically safe work environment.
Materials supplied:
Organisational procedures for Oliver Trading Appendix 2
Oliver Trading logo file
Cashbook.txt
CashbookPlanningDocument.doc
Lookup Data.xlsx
1-14 Septemberexpense claim data.docx
Work, Health and Safety:
follow TAFE Queensland Student Rules
access TAFE Queensland Student Services for any additional support
conduct risk assessments prior to any assessment task to ensure the safety of all participants and the environment
ensure an ergonomically safe work environment to complete all assessment tasks.
Submission details(if relevant) Students must produce Microsoft Excel (.xls) documents and print copies as pdf (online students), as requested for their portfolio of evidence.
Assessment to be submitted via:
TAFE Queensland Learning Management System: Connect url: https://connect.tafeqld.edu.au/d2l/loginUsername; 9 digit student number
For Password: Reset password go to: https://passwordreset.tafeqld.edu.au/default.aspxFiles to Submit - Seven (7)
Project 1
Planning document
Cashbook - macro enabled template - appropriately named as per procedures
Cashbook Test 1
Project 2
Summary Expense Claims _date_v1 - Upload the final version only
Summary Expense Claims _date_v1 - printed as a pdf
Video recording seeking clarification on Project 2 task 1.
Screen shot of storage location showing files stored and named for this assessment (Project 1 and 2).
Number of Attempts:
You will receive up to two (2) attempts at this assessment task. Should your 1st attempt be unsatisfactory (U), your teacher will provide feedback and discuss the relevant questions with you and will arrange a date for your 2nd attempt. If your 2nd attempt is unsatisfactory (U), or you fail to attend the scheduled date for a 2nd attempt, you will receive an overall unsatisfactory result for this assessment task. Only one re-assessment attempt may be granted for each assessment task.
For more information, refer to the Student Rules.
Instructions to Assessor Assessors are to:
confirm expectations as detailed in Instructions to Student have been followed and met
provide feedback on all assessment attempts and identify additional learning and/or practice required by the student before their second attempt.
use the Marking Criteria to record their assessment and feedback. Please mark work for consistency against the supplied benchmark solutions and use the marking criteria sheet provided for feedback to students.
ask for further clarification either in a face-to-face manner, by phone or written correspondence where they feel more information is needed. They can negotiate to complete assessment tasks and/or resubmissions verbally, where the opportunity presents:
scribe the students response verbatim on the assessment task/marking criteria, and note the questions verbally addressed on the coversheet.
initial and date additional comments
Reasonable Adjustment:
Assessors are able to use discretion if any reasonable adjustment is to be made in regards to use of equipment or production of spreadsheet as long as the requirements as given are met.
Work Health and Safety:
A work health and safety check of the assessment environment is to be conducted prior to the assessment as addressed in the points under Instructions to Students.
Note to Student An overview of all Assessment Tasks relevant to this unit is located in the Unit Study Guide.
Oliver Trading Scenario
You work as an administration assistant in the Accounts Department of Oliver Trading. Your supervisor, Ethan Venner, has given you two project tasks (see descriptions below) to complete over the next two weeks. Your deadline for completion of these tasks will be the due date for this assessment (refer to study guide).
The Spreadsheet Procedures Guidelines document has been supplied to you in Appendix 2. It provides the organisational requirements in regards creating, modifying and storing spreadsheet files. Please ensure you follow these guidelines unless otherwise instructed.
The guidelines include energy and resource conservation. You are to apply paper saving techniques by making all edits on screen and emailing/publishing completed files, rather than printing and posting.
Storage
Create a folder in the working storage area on your computer called Account Dept_initials. Replace initials with your initials eg Accounts Dept_SK. Save all your work, correctly named according to organisational naming requirements, to this folder. You will be required to present a screen shot of your assessment folder upon completion of all tasks. On completion of the tasks, take a screen shot of your Account Dept_initials assessment folder and upload it with your assessment files.
Project One
330771524574500Task Description
You are to create a cash book that can be used by staff for recording of expenses that have occurred during each month. This cash book will give the expense description and costs and then also break each expense down into one of four expense categories. Ethan has done a very rough sketch for you.
3317240858520 TAFE Queensland
0 TAFE Queensland
There will be one spreadsheet workbook for each quarter, a separate worksheet in the spreadsheet workbook for each month. Then at the end of each quarter, a summary sheet will be added to total expenses for all three months and these totals also shown in a visual graph format.
Analyse what you are required to do by reading through the following tasks and determining the best layout, format, functions and charts to use to achieve the most efficient completion of this task. Remember to also follow the organisational guidelines. A Cashbook Planning Document is supplied which is required to be completed as part of this analysis. Remember to continue to follow the organisational guidelines and to adjust your workstation to meet own personal ergonomic needs.
PART ONE
Task One - Analyse and Plan
Analyse your task requirements by completing the supplied Cashbook Planning Document (supplied with your Assessment 2 files). This document should contain an explanation of your plan, considering all the requirements of the task (as given below), as well as the Scenario and Task Description above.
Task Two - Create a macro-enabled template
You are required to create a Cashbook template, which can be used each month by Oliver Trading Staff.
Steps:
2.1Your supervisor has given you an old template in an old delimited txt file Cashbook.txt. (Supplied with your Assessment 2 files.) You are to import this file into a blank Excel worksheet. This file will give you 11 column headings for your cashbook. Ensure that you save your file to your Account Dept_initials folder and give it appropriate name.
2.2In this same Excel worksheet, prepare a macro, which will insert the name of the business Oliver Trading at the top of the sheet. It must be formatted in Calibri 18pt bold and italics as shown. Place the name at the top of your spreadsheet to ensure consistency of design.
Assign the shortcut key - Ctrl+e.
Ensure this macro is stored with the template and not your personal workbook.
2.3Underneath the heading Oliver Trading, give your spreadsheet another appropriate main heading. To the right, add the side-heading Month: and allow an adjacent cell for the current month name to be added. Also include the provided logo.
2.4Above the imported column headings, insert a row.
Merge the cells above the first 7 column headings and type heading Expense Description into the merged cell.
Apply merging and text wrap features where applicable to all your column headings.
2.5Repeat for the process for the last four items (Wages, Electricity, Rent and Vehicle Expenses). Type Expense Categories into the merged cell as the heading.
Apply merging and text wrap features where applicable to your column headings. (refer to sketch on page 2)
2.6Allow room for at last 10 entries to be made before inserting the necessary totals row.
2.7Use appropriate borders and shadings to present your spreadsheet. Shade the data entry only areas a pale blue.
2.8Ensure columns are an appropriate width for readability and name sheet tab 1 - Cashbook
2.9Below the cashbook you have just created, add the following table. Align the table so that the description column in both tables is in the same column.
Code Description
E Electricity
R Rent
V Vehicle Expenses
W Wages
Task Three - Entering formulas
3.1Insert a lookup formula, which will look up the small table below the cashbook and return the expense description for each code into the Description expense column of spreadsheet. Consider which lookup function would be the best to use.
3.2Prepare a logical formula (IF) in each of the expense category columns. This formula must return the amount from the Amount column into the appropriate Expense Category column.
3.3Insert formula to calculate the GST amount. GST is 1/10 of the Amount.
NOTE: There is no GST on Wages, so you will need a formula that will determine if the code is W and if so, enter 0, if not then calculate for GST.
3.4Insert formula to calculate Payment Amount. (Amount +GST)
Insert formulas for totals at the bottom.
3.5Format the Amount / GST / Payment Amount / Wages / Electricity / Rent Vehicle Expenses to number, 2 decimals. Format the Total row to currency and 2 decimals.
3.6Save your spreadsheet as a macro-enabled template stored in your Accounts Dept folder. Name the file using the correct file naming procedure, as indicated in the Spreadsheet Procedures Document. It is vital that you follow the guidelines for naming as you will end up with many versions of your spreadsheets and version control is essential. You need to avoid getting your files mixed up and uploading, or working on, an incorrect version.
Ensure you have followed the guidelines for presentation, page set-up and printing. Save and close the template.
Task Four - Edit the macro
4.1Create a new workbook based on your cashbook template.
4.2Open the macro instructions (Developer Ribbon/Macro/Edit) and take a screen shot. Paste this onto sheet 2 of your cashbook.
4.3Edit your macro instructions and change the font size to 14. Re-open the macro instructions (Developer Ribbon/Macro/Edit) and take another screen shot. Paste this onto sheet 3 of your cashbook.
4.4Resave your spreadsheet as macro enabled and name it Cashbook Test 1. Leave the spreadsheet open.
Again, you will end up with many versions of your spreadsheets and version control is essential. You need to avoid getting your files mixed up and uploading, or working on, an incorrect version.
Task Five - Test your formulas and evaluate
5.1Use the open Cashbook Test 1 to check your formulas by entering three different codes and three different amounts into the Code and Amount columns to check that your formulas are working correctly. Ensure one code is for Wages. Leave your test data in for marking purposes.
5.2Reread the scenario given on page 5. On a separate worksheet in your Cashbook Test 1 spreadsheet, write an evaluation of how efficient you believe the format of this template would be. Compare it to the originally supplied cashbook.txt file and consider access by the staff who will use it, efficiency of data entry, etc. You are to justify your responses. Rename this worksheet Evaluation of Efficiency
5.3Resave your Cashbook Test 1 - ensure it is saved to your Accounts Dept folder.
Again, you will end up with many versions of your spreadsheets and version control is essential.
Ensure you take a micro break before commencing Project 2 given over the page.
Project Two Task Description
Oliver Trading management would like to track staff expense claims for the first two weeks in September, so Ethan Venner, your supervisor, has asked you to take the supplied data and create a macro-enabled workbook to track staff expense claims for this period. During Ethans briefing to you, to convey his requirements, you have made the following notes and drawn this rough sketch. Remember to continue to follow the organisational guidelines and to adjust your workstation to meet your own personal ergonomic needs. Ensure you take a break and move around before commencing Project Two.
Rough Notes:
Staff expense details will be supplied in a Word format. The workbook will access two data tables (also supplied)
The spreadsheet workbook is to breakdown the expenses into expense types.
Two graphs are required. One to show breakdown summary by expense type and one to show breakdown summary by department. A macro is required to add a footer.
628015473498 TAFE Queensland
00 TAFE Queensland
Task One - analyse and create
Taking the rough notes and sketch supplied above, prepare and develop a spreadsheet workbook. Analyse what is required and determine the best layout, format, functions and charts to use to achieve the most efficient completion of this task. Remember to also follow the organisational guidelines supplied in Appendix 2
1.1Create a table as per the sketch and column headings below. Note: include 20 blank rows below column headings. This will be your working table.
1.2Name this working sheet Summary Expense Claims
369680966300
TAFE Queensland
1.3Save your spreadsheet and store it in your Accounts Dept folder. Name the file using the correct file naming procedure, as indicated in the Spreadsheet Procedures Document.
Task Two - Researching Help / Role-play
2.1As part of the template, Ethan, would like summary breakdowns as per Department but he is not sure how this would work. Using the Help option:
Research how you would use SUMIF
Add a sheet to the template. On this sheet, summarise your research into simple steps
Name this sheet SUMIF Help.
2.2Role-play: You will need to ask a family member, colleague or friend to play the role of Ethan, your supervisor. Record a 1 to 2 minute video of your conversation with Ethan where you are requesting clarification on task one (1) in Project 2. You may wish to write out your questions for Ethan first and give your role-play participant some time to consider responses before recording. The conversation must be submitted as a video file (MP4).
Ensure you:
Include a greeting
Use listening and questioning skills to clarify understanding.
Task Three - Enter Formulae
3.1As it is standard practice in Oliver Trading to keep initial data separate to working data, the expense variable data will be supplied in a Word document. September data is given in September Expense Claim Data.docx. This data will be copied into the Summary Expense Claims sheet - into the data entry columns of: Date, Employee ID, Code and Amount.
3.2The workbook will access two data tables using the VLookup Function. These two data tables are supplied in a spreadsheet file Lookup Data. To ensure this data stays with the workbook, copy the tables in to the workbook on a separate sheet. Name this sheet Lookup Data.
3.3A number of formulas are required within the worksheet.
VLOOKUP function to supply Employee Name and Expense Type based on the supplied data tables.
IF Function to break amount down into separate expense types
SUM Function to total columns. Refer back to sketch plan.
SUMIF Function to create the department summary table
Simple formula to link the expense summary amounts. Refer back to sketch plan.
On the Summary Expense Claims sheet, create and copy these formulas down the 20 blank rows.
3.4Hide the Lookup Data sheet.
Task four - Create a Macro to add a footer
4.1A footer should be included on all sheets. To save repeating this, create a macro that will insert a footer on a sheet that includes your name on the left (note this is your actual name), the date code field in the centre and the filename code field on the right. Ensure this macro is stored with this workbook and not your personal workbook. Assign the shortcut key - Ctrl+f.
4.2TEST MACRO: Use the previously created SUMIF help sheet within your spreadsheet to run the macro.
Open the macro editor and copy the macro steps to a separate sheet in your spreadsheet.
Name this sheet Macro Image
4.3Resave the spreadsheet.
Task five - Create Charts
5.1In Summary Expense Claims worksheet, prepare two charts which will sit side by side, below the working table. The first chart will show breakdown of expenses by expense type. The second chart will show breakdown of expenses by department. Refer back to sketch plan.
5.2Give each chart an appropriate title.
5.3Adjust the size of the charts so that they fit within the table length.
Task Six - Edit Macro
Ethan has now decided that the footer should not contain just the date in the centre but also the words Printed date &D in front of the date.
6.1 Edit your macro to make this change.
6.2 Resave. (It is good practice to save a new version of the worksheet, keeping previous versions at hand. Version control is useful if you need to resubmit your assessment and do not want to start from the beginning, but instead work from a particular point in its development.)
6.3 Test the macro on a new sheet.
Task seven - Format, Name and Store the File
7.1In the Summary Expense Claims sheet, shade all data entry cells a pale blue.
7.2Orientation is to be landscape and Fit to Page is to be applied so that the spreadsheet plus graphs all fit on one page.
7.3Check that you have followed the organisational guidelines for formatting.
7.3Check the format of the document and include an appropriate sheet name(s).
7.5Save the file as a macro-enabled workbook in the previously created storage folder.
Task Eight - Final Check and Print
8.1Check all formatting against organisational requirements.
8.2Remove any blank rows without data
8.3Ensure all four tabs are appropriately labelled. (Lookup Data sheet will remain hidden.)
8.4Final check to ensure all tasks requirements have been completed.
8.5Ensure that you have created all the formulas. Using one of the formula testing processes described in the Oliver Trading Procedures, check that there are no errors in the formulas. On the SUMIF sheet, write a sentence at the end on how you tested your formulas.
8.6Resave the document as a macro-enabled workbook.
8.7Print the Summary Expense Claims sheet (if submitting electronically print as a pdf.)
Final task - Storage
Recheck your storage folder for files and filenames required in both Project 1 and Project 2 and submit a screen shot of your storage folder for this scenario.
Files to Submit - Seven (7)
Project 1
Planning document
Cashbook - macro enabled template - appropriately named as per procedures
Cashbook Test 1
Project 2
Summary Expense Claims _date_v1 - Upload the final version only
Summary Expense Claims _date_v1 - printed as a pdf
Video recording seeking clarification on Project 2 task 1.
Screen shot of storage location showing files stored and named for this assessment (Project 1 and 2)
ProcedureSPREADSHEET GUIDELINES
Policy
Established company rules and guidelines in presentation and layout of documents ensures all written communications whether in-house or external, reflects its professionalism, image and gives an appropriate and consistent impression to all recipients. There are also benefits of cost effectiveness, increasing readability and time-saving.
Safe Work Practices
Oliver Trading endeavours to provide a working environment that is safe and free of risk to its employees health and welfare. The following links provide information for safe work practices. This includes recent current practices of working from home.
Ergonomic guide to computer-based workstations
Ergonomic guide to computer based workstations (worksafe.qld.gov.au)Guide to office workstations
Office workstations (worksafe.qld.gov.au)How do I set up a workstation at homeWorking from home - Workstation Setup Guide - COVID-19 | Safe Work AustraliaUse of Logo
Unless otherwise specified, logos are best placed top right corner of the spreadsheet.
The logo may be resized to best fit but MUST retain the same proportions so resizing is to be done only from corner markers.
The logo should be included on ALL sheets except raw data sheets within the same spreadsheet and optional on charts
Text Fonts and Sizes:
Normal default text Calibri (body) 11 pt.
Main heading Calibri 16 pt., bold (Merge & Centre across columns)
Sub heading Calibri 14 pt., bold (Merge & Centre across columns)
Column headings Calibri 12 pt., bold
Columns which contain text have headings that are left aligned. Columns which contain numbers have headings that are right aligned
Cell Format
Currency: Any data that is dollars/cents value must have the currency format and decimal points set to 0 or 2, right aligned. If there are cents then you must use the 2 decimals to show them. The $ sign is only to be used in any totals. Note format feature may need to be
Date Format: dd/mm/yyyy, e.g. 20/01/2021
Text Wrapping: Text must be wrapped in cells that contain column headings, use the Wrap Text Button. This will allow you to narrow the column width and heading. Hint: you may need to increase or decrease
Heading that require two lines use Alt Enter to break lines where required
Note: Use Australian spelling and ensure all spreadsheets are spell checked and proof read for errors.
Borders and Shading (format for readability and presentation when printed)
Format the spreadsheet with borders to improve readability and presentation when the document is printed, this may require ALL borders not just an outside border. May also add any other formatting to the worksheet to assist with readability, e.g. shading, bolding, font colour etc.
Charts (charts can be inserted as an object or chart sheet)
Charts must be inserted on a separate chart sheet (unless location is specified) and set up for full colour printing. You will have to add the appropriate footers to the chart sheet.If a chart has been inserted incorrectly, select the chart, go to the Move Chart Location Button to either move to a:
New Sheet: Chart1 (this will move it to its own chart sheet) OR
Object in: Sheet1 (this will put it in as an object in a sheet under your data).
Testing Formulas
When inserting formulas into spreadsheet, it is an Oliver Trading requirement that testing of these formulas is completed.
Use one or more of the following processes to test any new formulas.
Use test data use small numbers to test the formulas are correct
Perform the equation on a calculator and compare result with spreadsheet result
Use the formula auditing tool options in the Formula Ribbon in Excel to check for errors
Note any comment markers that appear designating changes to formulas in ranges
Check all required arguments parts have been included in the FUNCTION
Turn on formulas so that are visible and proofread check arguments and parts of formula are in the correct order
Double click on the formula cell and note which cells are highlighted as used in the formula. Check these are correct.
Document Layout for worksheet presentation/printing and page setup
Orientation
If the spreadsheet is wider that it is long use Landscape orientation. Unless otherwise instructed.
If the spreadsheet is longer that it is wide use Portrait orientation. Unless otherwise instructed.
Margins
Margin settings should be Normal default setting of 1.9 cm top/bottom 1.8 cm left/right, unless you need to adjust to fit document to one page, then the use of Narrows setting would be applicable.
Centring horizontally and vertically on the page
All single page spreadsheets must be centred both Horizontally and VerticallyUse the Page setup dialog box > Margins Tab> click both check boxes in bottom left corner under Center on Page
Spreadsheets that span at least 2 pages centre Vertically only
Header/Footer - Custom Footer only
In the Left section, type your name
In the Center section, click on the Insert Date button
In the Right Section, click on the Insert file name button (this will put the sheet tab name and the filename underneath).
Headers/Footers should be two sizes smaller than default font ie 10 ptPrinting
All worksheets must be print previewed to ensure readability and presentation as per above company guidelines before printing.
Print preview document and check the following:
Main and sub headings merged and centred across columns
Column headings are formatted and right aligned if over numeric data
Orientation and fits to one page if applicable
Centred Horizontally and Vertically on the page
Customer Footers are correct
Borders and Formatting for readability when printed
Any charts are correctly embedded or on separate chart sheet with footer.
Protection
When specified, documents must be password protected and draft copies shredded and recycled in the secure bins provided.
Documents that require data entry should have formula cells protected (locked) and data entry cells unlocked to allow for entry.
Energy Conservation Principles
Oliver Trading is committed to energy conservation principles. The following should be considered and followed where ever possible.
Using power-save options for equipment and computers
Turn off lights, air conditioners, heaters at end of day
Power off computers, copiers, printers at end of day
Send an email instead of printing
Edit on screen rather than print and edit
Print/photocopy on both sides double sided use
Recycling used and shredded paper
Re-using paper for rough drafts (observing confidentiality requirements)
Shredding of all confidential printouts
When purchasing new equipment, consider the energy rating of the equipment
File Saving and File Naming
Saving files
Files that need access by other staff should be stored on the shared drive. They should be saved in the department folder for which you work and then a new folder for the specific content should be created if a folder does not already exist.
For example:
Meeting agendas and minutes would already have a created folder so saved these documents in this folder, correctly named.
Newly created work or new clients would need a new folder created.
Naming files
When naming a file the filename MUST contain name relevant to content_date_version number.
Minutes taken on 10 November 2018 would be saved as: Minutes_10112020_v1
Client Jim Smith cash records for 10/11/20 could be saved as: Smith J Cash Records_10112020_v1
Example of a completed spreadsheet using the guidelines is shown on the next page.
Example of a simple Oliver Trading spreadsheet showing:
Main and sub heading centred over the columns of data Sales Figures and First Quarter
Column headings of numeric columns right aligned Jan Feb Mar and Total
Dollars and cents data formatted with $ sign and 2 decimals
Borders and formatting added for readability
Custom Footer added
Spreadsheet has been centred vertically and horizontally on the page.
Page orientation in Landscape
Date of Expense Employee ID Expense Code Amount (less GST)
3/09/2021 T1896 EM 65
13/09/2021 B2659 EM 65
3/09/2021 B4879 EF 450
12/09/2021 S1547 EA 130
9/09/2021 P1824 EA 150
9/09/2021 P1824 EM 65
9/09/2021 P1824 EK 152
8/09/2021 B4879 EF 35
8/09/2021 B4879 EF 235
8/09/2021 B4879 EF 35
1/09/2021 P1824 EK 53
5/09/2021 S1547 EA 165
5/09/2021 S1547 EM 130
14/09/2021 J1457 EF 35
13/09/2021 C1724 EK 234
12/09/2021 S1547 EF 450
