Reference no: EM132333290

Develop and use complex spreadsheets Assignment - Demonstration/Written Response

Purpose of the Assessment - The purpose of this assessment is to assess the student in the following learning outcomes:

Element 1: Prepare to develop spreadsheet

1.1 Analyse task and determine specifications for spreadsheets.

1.2 Identify organisational and task requirements of data entry, storage, output, reporting and presentation requirements.

1.3 Apply work organisation strategies and energy and resource conservation techniques to plan work activities.

Element 2: Develop a linked spreadsheet solution

2.1 Utilise spreadsheet design software functions and formulae to meet identified requirements.

2.2 Format cells and use data attributes assigned with relative and/or absolute cell references, in accordance with task specifications.

2.3 Test formulae to confirm output meets task requirements.

Element 3: Automate and standardise spreadsheet operation

3.1 Evaluate tasks to identify those where automation would increase efficiency.

3.2 Develop, edit and use templates to ensure consistency of design and layout for forms and reports, in accordance with organisational requirements.

Element 4: Use spreadsheets

4.1 Enter, check and amend data in accordance with organisational and task requirements

4.2 Use manuals, user documentation and online help to overcome problems with spreadsheet design and production.

4.3 Preview, adjust and print spreadsheet in accordance with organisational and task requirements

4.4 Name and store spreadsheet in accordance with organisational requirements and exit application without data loss or damage.

Element 5: Represent numerical data in graphic form

5.1 Determine style of graph to meet specified requirements and manipulate spreadsheet data if necessary to suit graph requirements.

5.2 Create graphs with labels and titles from numerical data contained in a spreadsheet file.

5.3 Save, view and print graph within designated timelines.

Part A - Practical

Question No 01 -

Instruction for student: This is an individual task.

You are a new Accounts Clerk of ABC Ltd. Your accounts manager has requested you to create an automated (Cell Referencing) payroll using timesheets. You must consult your manager (your trainer) to understand and analyse this task.

Task instruction & Requirements:

a. Insert header "Final Question 1", and insert Footer "Your First Name Student Number".

b. Design and create a spreadsheet of consisting 2 worksheets.

c. Rename first worksheet to "Weekly Payroll".

d. Rename second worksheet to "Weekly Timesheets".

e. Use heading "Bold, Font- Arial, 12, Centre Alignment.

f. Normal font Arial 12.

g. Hourly Rate $40.00 excluding tax (Please use Cell A13 for "Hourly Rate" and Cell B13 "$40.00". Use B13 as absolute cell reference).

h. Tax Rate 19% (Please use Cell A12 for "Tax Rate" and Cell B12 "19%. Use B12 as absolute cell reference).

i. Save the file as "Q1 Final".

j. Print spreadsheet showing formulas.

k. Preview your work, print accordingly (Plan before you print) and attach.

l. Email your file to teacher's email with a subject "Final Q1".

Company policy with payroll timesheets:

a. Ordinary Hour means 8 hours.

b. After 8 hours, employees work overtime rate at 1.5 times for 4 hours. Any employee works more than these hours in a day will be paid double.

Question No 02 -

Instruction for student: This is an individual task.

You are an Accounts Clerk of ABC Ltd. Your accounts manager has requested you to create spreadsheets documents using Pivot Table, logical function, conditional formatting, sort, and other functions.

Task instructions & Requirements:

a. Insert header "Final Question 2", and insert Footer "Your First Name Student Number".

b. Design and create a spreadsheet.

c. Use Cell A2 for heading "Sales for the month ended July 2013".

d. Rename the worksheet to""Main Data".

e. Highlight the best 2 "Total Goods Sold" using conditional formatting (cell in white color and fill in blue color).

f. Copy "Main Data" into a new worksheet and rename it to "PIVOT".

g. Create a pivot table.

h. Create 6 products Pie Chart. (Insert data level, chart title, legends) and rename the worksheet to "PIE".

i. Create a 3D Line Bar based on "Store" and "Total Goods Sold". (Insert data level, chart title, legends) and rename the worksheet to "LINE".

j. Save the changes to your file as "Q2 Final".

k. Preview your work and print accordingly (Plan before you print).

l. Email your file to teacher's email with a subject "Final Q2".

Question No 03 -

Instruction for student: This is an individual task.

You are an Accounts Clerk of ABC Ltd (ABN: 11 111 111 111 Address : 333 Kent Street Sydney 2000).You usually notify short payment or invoice discrepancy to your suppliers filling a paper. Your accounts manager (your trainer) has requested you to create a template in spreadsheets that will minimise your time to prepare the document. He also advised that the new template should meet the ATO Tax Invoice Guidelines. You must consult your manager (your trainer) to understand and analyse this task.

Task instructions & Requirements:

a. Insert header "Final Question 3", and insert Footer "Your First Name Student Number".

b. Describe your plan, review and sketch your plan to create this template at the given space below.

c. Design and create template in MS Excel.

d. Use Cell A2 for heading "Tax Invoice Discrepancy Notice".

e. It should contain a column for item description, Item Code, Quantity ordered, Item Price, Total Price on Order, Invoice Quantity, Invoice Price, Invoice total, Quantity Variance and Price Variance, Price Variance Comment, Quantity Variance Comment, Grand Total.

f. Use border for each column.

g. Save the changes to your file as "Final Q3".

h. Preview your work and print accordingly (Plan before you print).

i. Email your file to teacher's email with a subject "Final Q3".

Part B - Written Response

Question No 04 -

Instruction for student: This is an individual written task.

You are an Accounts Clerk of ABC Ltd. Your accounts manager has requested you to prepare a short paragraph on the following issues,

a. How all employees can minimise five energy and resource cost at workplace?

b. Write 2 differences between Macro and Template.

c. Discuss Macro? How to edit and review a macro?

d. How to use Microsoft Excel Help? Write procedures for "countif" function.

Attachment:- Demonstration Written Response Assignment File.rar