Â
Assessment Task 1 - Unit Knowledge Test (U1KT) Instructions: ● ● ● ● ● ●
● ● ●
This is an individual assessment. The purpose of this assessment task is to assess the students’ knowledge essential to develop and use complex spreadsheets in a range of contexts and industry settings. To make full and satisfactory responses you should consult a range of learning resources, other information such as handouts and textbooks, learners’ resources and slides. All questions must be answered in order to gain competency for this assessment. You may attach a separate sheet if required. You must include the following particulars in the footer section of each page of the attached sheets: o Student ID or Student Name o Unit ID or Unit Code o Course ID or Course Code o Trainer and assessor name o Page numbers You must staple the loose sheets together along with the cover page. You must attach the loose sheets chronologically as per the page numbers. Correction fluid and tape are not permitted. Please do any corrections by striking through the incorrect words with one or two lines and rewriting the correct words.
Resources required to complete the assessment task: ● , ● ● ● ● ● ● ●
Internet MS Word Printer or e-printer Adobe acrobat/reader Learning management system Access to office equipment and software Access to samples of data for inclusion in spreadsheets.
Questions: Question 1: In order to attain a comfortable, productive and safe work environment, what organisational requirements would you recommend each for the following: a. Posture- Activity- Exercise (Ergonomics) b. Lightning, a ir and noise conservation techniques c. Work style, p eriods and breaks
Explain the requirements for each of the following in 100-200 words.
Question 2: “Sustainability in the office helps to reduce the expenses, making (you)feel good and look good”. Write down some tips which could make this statement clearer and more understandable in 50-100 words
Question 3: Explain ways to conserve energy for the following areas in 50-100 words each. a) Transportation b) Thermostat management c) Food d) Computer usage Question 4: Why is consistency so important in design/layout? List three (3) points about in support of consistency in design/layout. Question 5: Discuss the importance of a smooth documentation work flow process in 50-100 words. Question 6: What is a macro? What function can you perform with the help of a macro. Write your answer in 100-150 words.
Question 7: Define the term template and explain why templates are important? Give an example of a template. (Explain in 100-200 words)
Question 8: The following is the report of a company’s turn over per year.
Illustrate this through pictorial view In MS-Excel with the heading of annual turnover per year and the sum of all revenue (turnover) from 2000 to 2012. Question 9: Why are charts useful when displayed alongside the data set and why it is important to choose the correct type of chart to display different data sets? Explanation of reasonable adjustments strategy used (If required)
Question 10: How can you create a macro? Explain ion 250-300 words and use the images where required.
Question 11: As per the organisational policy, company A wants their financial data to be processed in MS Excel
You are required to prepare the following reports in MS-Excel: Cash receipts journal Cash Payment journal Following are the given transactions: Customer A sends a check for a prior sale, paying $10,357.55 and taking a $102.55 sales discount; You make a cash sale for $452; Customer B sends a check for a prior sale, paying $6,120; You are paid $3,000 in principal and $155 in interest on a note. Paid $455 for office equipment; Paid $1,200 for purchases; Paid $2,000 to Supplier A against accounts payable, taking a purchases discount of $225; Paid $1,125 for purchases; Paid $765.50 to Supplier B against accounts payable. Complete the attached templates, make sure that the structure and format is as per the given format and organisational requirements. You must use Excel formulas to calculate the given outputs of data. Question12: Below given is an Excel sheet with scattered data in it. Examine the data meticulously and explain how to use the MsgBox function with this data set. Write answer in 150-200 words.
Question13: Below are the sales record of the music company. Create two different spreadsheets. Explain the procedure in 200-250 words that you used and provide a screenshot of MS Excel to do the following: a) Change the names of both sheets to east, west b) Consolidate the data of both sheets on 3rd sheet c) Link both the worksheets d) Save the workbook on a removable drive e) Print the consolidated sheet
Question 14: A spreadsheet is a mixture of text, graphics and mathematics. What impact does the formatting and design of a spreadsheet have on demonstrability and readability of data? Write your answer in 100-150 words. •
•
•
•
Please note, academic standards of the unit/course will not be lowered to accommodate the needs of any student, but there is a requirement to be flexible about the way in which it is delivered or assessed. The Disability Standards for Education requires institutions to take reasonable steps to enable the student with a disability to participate in education on the same basis as a student without a disability. Trainer/Assessor must complete the section below “Reasonable Adjustment Strategies Matrix” to ensure the explanation and correct strategy have been recorded and implemented. Trainer/Assessor must notify the administration/compliance and quality assurance department for any reasonable adjustments made.
General Instructions for attempting the skills test:
● ● ● ●
● ● ●
You are required to develop complex spreadsheets to prepare financial statement based on given income statement. Your assessor will assess your work according to the given performance criteria/ performance checklist. You may attach a separate sheet if required. You must include the following particulars in the footer section of each page of the attached sheets: o Student ID or Student Name o Unit ID or Unit Code o Course ID or Course Code o Trainer and assessor name o Page numbers You must staple the loose sheets together along with the cover page. You must attach the loose sheets chronologically as per the page numbers. Correction fluid and tape are not permitted. Please do any corrections by striking through the incorrect words with one or two lines and rewriting the correct words.
Resources required to complete the assessment task: ● ● ● ● ● ● ●
Computer Internet MS Word Printer or e-printer Adobe acrobat/reader Learning management system MS Excel
Assessment task Instructions ● ● ● ● ●
This assessment task requires you to take on the role of accountant to prepare financial reports as per given income statement. You must develop complex spreadsheets to prepare financial report based on given income statement. You must prepare financial report based on the given income statement using provided templates. You must create graph and pivotal table for the given income statement. Assessor must assess the performance as per the performance criteria provided.
Task 1 : Test1: You are working as an accountant in the company. You have to prepare a financial report for the last year by assuming the following data Your spreadsheet (Excel sheet) must include all the financial statements included in accounting cycle. Months Ended May April March Revenues: Net Sales $18,875 Cost of Goods Sold (6,500) Gross Profit $12,375 Operating Expenses: Advertising $1,500 Bank Service Charges 120 Insurance Expenses 100 Interest Expense 125 Legal & Accounting Fees 300 Office Expenses 250 Payroll Taxes Expenses 350 Postage Expenses 75 Rent Expenses 800 Salaries 3,500 Supplies 300 Telephone Expenses 200 Utilities 255 Total Operating Expenses $7,875 Net Income $4,500 You must create a graph on sheet 1 and pivot table for this data on sheet 2, in such a way that if you change any value in sheet 1 data sheet 2 also changed.
Assessment Task 3 - Unit Project (UP) – Create spreadsheet Instructions to complete this assessment task: ● ● ●
● ● ● ● ● ● ● ●
● ●
Please create spreadsheet using VBA editor. You may attach a separate sheet if required. You must include the following particulars in the footer section of each page of the attached sheets: o Student ID or Student Name o Unit ID or Unit Code o Course ID or Course Code o Trainer and assessor name o Page numbers You must staple the loose sheets together along with the cover page. You must attach the loose sheets chronologically as per the page numbers. Correction fluid and tape are not permitted. Please do any corrections by striking through the incorrect words with one or two lines and rewriting the correct words. The premise of the project must be closely related to the previous assessment task. This submission must be well presented and follow the guidelines and instructions provided. Please follow the format as indicated in the template section below. One of the most important steps that you can take: proofread your project. Spreadsheet created in the project must be based on provided data, using 11-point font, double-spaced, and must include a cover page, table of contents, introduction, body, summary or conclusion, and works cited. Appropriate citations are required. All RTO policies are in effect, including the plagiarism policy.
Resources required to complete the assessment task: ● ● ● ● ● ● ●
Computer Internet MS Word Printer or e-printer Adobe acrobat/reader Learning management system VBA editor
Assessment task Instructions ● This assessment task requires you to create a spreadsheet using VBA editor. ● You must create a spreadsheet for an organisation of your choice. ● You must use the user form toolbox to create the required labels and fields. ● You must select the organisation as per following criteria and assessor must check the proof to confirm the same: ● Organisation must be real and have a website/online presence. ● Organisation must provide list of internal and external stakeholders. ● Your organisation can belong to any Industry relevant to your work practices or preferences.
● ●
Your trainer/assessor must approve the organisation before you start working on this assessment task. Assessor must assess the performance as per the performance criteria provided.
Project: Choose any company and create the following spread sheet for the attendance by assuming the blow mention conditions mandatory Company Name Address 1:
Enter Address
Phone:
Address 2:
Fax:
City/Town:
E-mail:
Zip/Postal:
Name's Time Sheet for the Week Employee Name:
Name
Approving Manager: Employee phone: Employee email: Tax ID#: Week ending: Sunday, 6 November 2016
Day Total
in Out
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
9
12
In
Out
Regular Hours Overtime HoursSick
1
7
7.0
Vacation
2.0
9.0
7.5
7.5
Total
7.00
2.00
Rate
$10
$15.00
16.50
Employee signature: Date:
Total
Manager signature:
$70.00 $30.00
$100.00
Date:
●
Use VBA editor and create a form using user form function
●
Use the user form toolbox to create the required labels and fields
●
The form is aesthetically designed with appropriate proportion and placement of form objects
●
Form fields are selected appropriate to the data type
●
Manipulate form properties to change name/caption, font, colour and other attributes
●
Save and print a copy of the form
●
Used labels, text boxes, drop-down list, option buttons and command buttons as needed
●
Attached the “show” form code to the “Enter Employee Details” button on the worksheet
●
The button is functional and brings up the form for input when clicked
●
Create a recording sheet to capture output from the form
●
Write initialisation/execution code to execute the commands – return the desired results Assigned a macro to the “Clear” button Assigned a macro to the “Cancel” button Assigned a macro to the “Submit” button Assign a macro to the “Spin” Button
●
Added form initialisation/execution code to the user form
●
The form is fully functional and error free
●
Populated the Employee Records with sample data
●
Use VLOOKUP and HLOOKUP functions
●
Use AND or OR functions to create a conditional formula that results in a logical value
●
Use IF and AND or OR functions to create a formula that results in another calculation
●
Use AVERAGE function to return average hours of work, except zeros
●
Use COUNT function to count number of employees who commenced on the same day
Saved the file as instructed for future use and marking And then add a new work sheet and do the following for same data
●
Use VLOOKUP and HLOOKUP functions to look up values vertically and horizontally in the list by using an exact match (any value)
●
Create a conditional formula that results in a logical value (TRUE or FALSE); Example: Is 18 hour of work per week equivalent to part time
●
Create a conditional formula that results in another calculation or in values other than TRUE or FALSE, using IF and AND or OR functions; Example: If under 18 years of age, and if working more than 3 hours a week, then return, “Guardian Approval Required”
●
Use AVERAGE function to return average hours of work, except zeros, of all employees per week
Use COUNT function to count number of employees who commenced on the same day (or any other similar count) Create these formulas in appropriate cells and test them for accuracy.