Reference no: EM132333273
Develop and Use Complex Spreadsheets Assignment - Portfolio of Activities
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 Organise personal work environment in accordance with ergonomic requirements.
1.2 Analyse task and determine specifications for spreadsheets.
1.3 Identify organisational and task requirements of data entry, storage, output, reporting and presentation requirements.
1.4 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 Link spreadsheets in accordance with software procedures.
2.3 Format cells and use data attributes assigned with relative and/or absolute cell references, in accordance with task specifications.
2.4 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 Create, use and edit macros to fulfil requirements of task and automate spreadsheet operation.
3.3 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 Import and export data between compatible spreadsheets and adjust host documents, in accordance with software and system procedures.
4.3 Use manuals, user documentation and online help to overcome problems with spreadsheet design and production.
4.4 Preview, adjust and print spreadsheet in accordance with organisational and task requirements.
4.5 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.
Task 1 -
Instruction for student: This is an individual task with written response. You need to prepare answers from the materials available at elearnings or you may use any web materials. Please answer all questions.
You are a new Accounts Clerk of Deaux's Consulting Company. Today is your first day. In your new office, you have your own desk, computers, telephone. You have been given the following task after your orientation at the company with a WHS representative (From Question 01 - 04)
Q1. List five potential WHS hazards that could occur during the use of a computer at work place according to Work Health Safety Regulation 2011
Q2. List five ways in which you can improve the overall layout of your work area to increase work effectiveness and decrease hazards.
Q3. List five good resource-conserving practices.
Q4. What is ergonomics? How would you start your daily work environment if you are an Accounts Clerk of XYL Ltd?
Q5. While working on the first day at your desk your manager wanted you to draw a simple plan for a working spreadsheets using pencil and the following information:
a. The spreadsheet records rainfall for a calendar year
b. Column A is for dates in the month
c. Use a column for each month
d. Include a total row for each month
Task 2 -
Instruction for student: This is an individual task.
You are a new Accounts Clerk of Deaux's Consulting Company. Your accounts manager has requested you to create and design a spreadsheet according to the requirements. You must consult your manager (your trainer) to understand and analyse this task and you must use online help if necessary to spreadsheet design.
Task instruction & Requirements:
1. Design and create a spreadsheet to record the results information (Provided in Appendix 1).
a. Enter the information into your spreadsheet.
b. Save the file using the following format: [Your initials] Covers Quarterly Results ddmmyy. For example, 'DP Covers Quarterly Results List 010610'.
c. Follow the style guide to complete Appendix 1 - Client List
2. In its current format, the data is difficult to understand and explain. Your manager has asked that you use some of the inbuilt functions to present the information in a more 'user-friendly' manner. In particular, he has asked that the following be easily identified:
a. Insert header "Task 02", and insert Footer "Your First Name Student Number".
b. Compare each category of the two quarter in Column D.
c. Create a percentage of the comparison of each category of the two quarter in Column E & F (e.g. Previous Quarter Children's Fiction/ Previous Quarter Total Sales).
d. Prepare Pie chart of Sales and Expenses of each quarter.
e. Save the changes to your file.
f. Use all these charts in MS Word with a file name "Task 02".
g. Preview your work and print accordingly (Plan before you print. e.g. 4 pie charts in 1 page).
h. Email your file to teacher's email with a subject "Task 02".
Task 3 -
Instruction for student: This is an individual task.
You are a new Accounts Clerk of Deaux's Consulting Company. Your accounts manager has requested you to create an automated Trial Balance and Profit & Loss Statement using journal entries. You must consult your manager (your trainer) to understand and analyse this task and you must use online help if necessary to spreadsheet design.
Task instruction & Requirements:
a. Insert header "Task 03", and insert Footer "Your First Name Student Number".
b. Design and create a spreadsheet of General Ledgers, Trial balance and Profit & Loss Statement using journal entries.
c. Use Cell A93 to start your heading "General Ledgers".
d. Use Cell A286 to start your heading "Trial Balance".
e. Use Cell A315 to start your heading "Income Statement".
f. Transfer journal entries to specific ledgers. Use formula for ledger balance.
g. Link cells of ledger balance to cells in Trial Balance. Use formula to balance Trial Balance.
h. Link cells of Trial balance to cells in Profit and Loss Statement. Use formula to prepare Profit and Loss Statement.
i. Save the changes to your file.
j. Preview your work and print accordingly (Plan before you print. e.g. 4 pie charts in 1 page).
k. Email your file to teacher's email with a subject "Task 03".
Task 4 -
Instruction for student: This is an individual task.
You are an Accounts Clerk of Deaux's Consulting Company. Your accounts manager has requested you to create spreadsheets documents using Pivot Table, "if" logical function, conditional formatting, sort, and other functions. You must consult your manager (your trainer) to understand and analyse this task.
Task instructions & Requirements:
a. Insert header "Task 04", and insert Footer "Your First Name Student Number".
b. Design and create a spreadsheet.
c. Use Cell A2 for heading "Cookie Sales by Region".
d. Fill Blank cell in "SalesRep" column using order data according to order number. (e.g. Order no "217" means "Bill", Order no "268" means "Frank".
e. Fill Blank cell in "Region" column using order data according to order number. (e.g. Order no "217" means "West", Order no "268" means "West".
f. Rename the worksheet to "Main Data", make the 'Order No' centre aligned.
g. Copy "Main Data" into a new worksheet.
h. Rename the worksheet to "Sorted Data" and sort data according to "Order No".
i. Copy "Main Data" into a new worksheet and rename it to "PIVOT".
j. Create a pivot table.
k. Click a new worksheet and rename it to "Best Sales man".
l. Create a query based on using pivot table, "SalesRep" and "Total Sales", copy information and use paste special to "Best Sales man" worksheet. Highlight the best 2 salesrep using conditional formatting (cell in white color and fill in blue color).
m. Click a new worksheet and rename it to "Area Sales".
n. Create a query based on using pivot table, "Region" and "Total Sales", copy information and use paste special to "Area Sales" worksheet. Create a Pie Chart. (Insert data level, chart title, legends).
o. Copy and move "Area Sales" worksheet and rename it to "Best Area".
p. Create a column after "Total Sales" Column. Type "Comment" as column heading.
q. Assess area with total sales. Total Sales more than $300000 is "Excellent", more than $ 200000 is "Good" and less than that is "Poor".
r. Save the changes to your file.
s. Preview your work and print accordingly (Plan before you print. e.g. 4 pie charts in 1 page).
t. Email your file to teacher's email with a subject "Task 04".
Task 5 -
Instruction for student: This is an individual task.
You are an Accounts Clerk of Deaux's Consulting Company. Your accounts manager has requested you to create spreadsheets documents using VLOOKUP," if " logical function, conditional formatting, sort, and other functions. You must consult your manager (your trainer) to understand and analyse this task and you must use online help if necessary to spreadsheet design.
Task instructions & Requirements:
a. Insert header "Task 05", and insert Footer "Your First Name Student Number".
b. Design and create a spreadsheet.
c. Use Cell A2 for heading "Cookie Sales by Region".
d. Fill Blank cell in "SalesRep" column using Vlookup.
e. Fill Blank cell in "Region" column using Vlookup.
f. Rename the worksheet to "Main Data".
g. Copy "Main Data" into a new worksheet.
h. Rename the worksheet to "Sorted Data" and sort data according to "Order No".
i. Copy "Main Data" into a new worksheet and rename it to "PIVOT".
j. Create a pivot table.
k. Click a new worksheet and rename it to "Best Sales man".
l. Create a query based on using pivot table, "SalesRep" and "Total Sales", copy information and use paste special to "Best Sales man" worksheet. Highlight the best 2 salesrep using conditional formatting (cell in white color and fill in blue color).
m. Click a new worksheet and rename it to "Area Sales".
n. Create a query based on using pivot table, "Region" and "Total Sales", copy information and use paste special to "Area Sales" worksheet. Create a Line Bar. (Insert data level, chart title, legends).
o. Copy and move "Area Sales" worksheet and rename it to "Best Order".
p. Create a column after "Total Sales" Column. Type "Comment" as column heading.
q. Assess order number with total sales. Total Sales more than $120000 is "Excellent", more than $ 60000 is "Good" and less than that is "Poor".
r. Save the changes to your file.
s. Preview your work and print accordingly (Plan before you print. e.g. 4 pie charts in 1 page).
t. Email your file to teacher's email with a subject "Task 05".
Task 6 -
Instruction for student: This is an individual task.
You are an Accounts Clerk of Deaux's Consulting Company. Your company have started working with "ACA College" in Sydney. ACA College has sent your manager a result draft in PDF file format. The college has 3 examinations ( Mid Term, Assignment and Final). ACA College has sent results for Mid Term, Assignment and Final examination for a subject, Business Activity Statement. Principal from ACA College advised that he will send results for other subjects as well and he will send all in PDF file format. Your accounts manager has requested you to create spreadsheets documents using VLOOKUP, "if" logical function, conditional formatting, sort, MACRO and other functions. You must consult your manager (your trainer) to understand and analyse this task and you must use online help if necessary to spreadsheet design.
Task instructions & Requirements:
a. Insert header "Task 06", and insert Footer "Your First Name Student Number".
b. Design and create a spreadsheet.
c. In this workbook, you have 4 worksheets.
d. Create worksheets naming "MAIN", "MID TERM", "ASSIGNMENT", and "FINAL".
e. Enter all data into specific worksheets and follow the template to create worksheets.
f. After entering data in "MID TERM" worksheet, create a macro to fix the cell width 30 pts (215 pixels) horizontally using CTRL+L. Rename this macro as "COLUMN FIT".
g. Create another macro to fix the cell width 30 pts (215 pixels) vertically using CTRL+H. Rename this macro as "ROW FIT".
h. Edit macro "COLUMN FIT" and change width to 35 pts.
i. Apply these macros while creating "ASSIGNMENT" and "FINAL" worksheets.
j. Enter Data in "MAIN" worksheet and fill midterm, assignment and final columns using Vlookup.
k. Sort "Total" according higher to lower.
l. Fill the "Grade", 81-100 HD, 75-80 D, 65-74 C, 50-64 P, 0-49 F.
m. Create a macro for conditional formatting below using CTRL+K.
n. Show HD in Blue Fill, white font and F in Red Fill, yellow font.
o. Comment: for F in grade, "Call and warn the student".
p. Save the changes to your file.
q. Preview your work and print accordingly (Plan before you print. e.g. 4 pie charts in 1 page).
r. Email your file to teacher's email with a subject "Task 06"
Attachment:- Portfolio of Activities Assignment File.rar