Posted: September 13th, 2017

Spreedsheets creating spreedsheets

Spreedsheets

creating spreedsheets

Assessment Description
This assessment task is divided into two parts:
•    Part 1 – Written Tasks
•    Part 2 – Printing Reports
For this assessment task you are required demonstrate knowledge and skills in
•    Developing a linked spreadsheet solution including using formulae and functions, and testing formulae to confirm output meets tasks requirements
•    Automating and standardising the spreadsheet operation by using templates and macros
•    Using the spreadsheets including entering/checking data to be error free, using manuals and documentation, previewing and printing tasks and finally naming and

storing spreadsheets
Assessment Task 3 of 3
Develop and use complex spreadsheets – Short answer questions and Portfolio of Evidence
Instructions
Part 1 – Please use a Word document to answer the questions. Although there is no word limit, sufficient explanations must be present.
Part 2 – Please scan your printouts into a pdf file and submit the pdf file. We need to determine you can print these files.
Part 1 – Written Questions
1    Explain how you have checked formulas in the workbook template created in Assessment 2, Part 1.

2    Write down the formulas used to calculate the following in the Branch template created in Assessment 2, Part 1, question 5 and 12 respectively.
% commission determined for each sales person for the first month

The average sales for a month rounded to the nearest $100

3    Explain the steps you would take to edit the print range macro created in Assessment 2, Part 1, question 18 ie to change the data range selected.

4    Fill in the table below indicating the file name and folder location you used to store spreadsheet files for this assessment.

File Name    Folder Location
Assessment 2, Part 1
Assessment 2, Part 2

Assessment 2, Part 3
Assessment 2, Part 4
5    Use manuals, user documentation and online help to look up the following topics.  Record your findings in the spaces provided.
Explain what the PMT function is and when you would use it.

How would you split the screen so you could view the top and lower part of a worksheet?

Part 2 – Printing Reports
These questions relate to templates and workbooks created in Assessment 2.
1    A sales person was omitted from the data for Brisbane; his details are shown below.  Add the sales person’s data to the Brisbane workbook (the Head Office

Sales Workbook should automatically update).  Sort the sales people into alphabetical for the Brisbane workbook. The below prints out will show new figures due to the

added sales person.
Brisbane Branch
Jan 14    Feb 14    Mar 14
Elliott, James    5,870.00    6,030.00    5,968.00
2    Print preview and print the three worksheets that detail the sales for each branch for the quarter Jan – Mar 14.  Ensure all data is printed.
3    Print the template that each branch uses, and then print another copy showing the formulas.
4    Print the template that is used by Head Office, and then print another copy showing the formulas.
5    Print the entire workbook that Head Office has produced that displays the sales from all branches for the quarter Jan – Mar 14.
6    Print preview and print the chart –total sales for each branch – found in the Head Office workbook (Part 3, question 8) for the quarter Jan – Mar 14.
7    Scan and upload all printed pages to OpenSpace.

Common Business Unit
BSBITU402A Develop and use complex spreadsheets (Excel 2013)
Assessment Task (2 of 3)

Student Name: XXXXXX
Student Number: XXXXXX
Assessment Number:  31251/02

© Millbank Investments Ltd, NZ, 2014
Publishers – Software Publications Pty Ltd (ABN 75 078 026150)
Disclaimer
All rights reserved. No part of this publication may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying,

scanning, recording, or any information storage and retrieval system, without permission in writing from the
publisher, Software Publications Pty Ltd. No patent liability is assumed with respect to the use of the information contained herein. While every precaution has been

taken in the preparation of this book, the publisher and the authors assume no responsibility for errors or omissions. Neither is any liability assumed for damages

resulting from the use of the information contained herein.
These learning materials have been prepared for use as part of a structured vocational education and training course and should only be used within that context. The

information contained herein was correct at the time of preparation. Legislation and case law is subject to change and readers need to inform themselves of the current

law at the time of reading.
This edition published with amendments by Open Colleges 2014.

Getting Started
Once you feel confident that you have covered the learning materials for this unit, you are ready to attempt this assessment.
To complete this assessment, please type your answers to each question or task in this document or submit the task in the appropriate format as advised by the

assessment instructions.
To help Open Colleges manage your assessment file/s, please use an appropriate file naming convention that is clear and easy to store.
Assessment Submission
When you are ready to submit your assessment, upload the file in OpenSpace using the Assessment Upload links in the relevant Study Period of your course. The Student

Lounge provides a ‘Quick Guide to Uploading Assessments’ if you need further assistance. Uploading assessments in OpenSpace will enable Open Colleges to provide you

with the fastest feedback and grade on your assessment.
Alternatively, you can print and post your assessment to Open Colleges, PO Box 1568, Strawberry Hills, NSW 2012. Please ensure that you use the Open Colleges

Assessment Cover Sheet (available in the Student Lounge in OpenSpace). Where assessments are submitted by post, grades and feedback will be released in OpenSpace.

Please note that assessments submitted by post may take up to 21 days from the date received by Open Colleges to grade and are reliant on the efficiency of the postal

service.
It is important that you keep a copy of all electronic and hardcopy assessments submitted to Open Colleges.
Competency and Grading
To achieve a Pass (PA) grade for this assessment, you must at a minimum address all the requirements specified in the assessment instructions in order to demonstrate

your competency. To achieve a higher grade such as a Credit (CR), Distinction (DN) or High Distinction (HD), your assessor will be looking for additional details,

research, and analysis and referencing where it is not specified. This will demonstrate your in-depth understanding and application beyond the assessment requirements

but within the context of the unit being delivered.
See further information on grading located in your Student Handbook.

Assessment Description
This assessment is divided into four (4) parts:
•    Part 1 – Branch Template
•    Part 2 – Branch Workbooks
•    Part 3 – Head Office Template
•    Part 4 – Head Office Workbook
For this assessment task you are required demonstrate knowledge and skills in:
•    Preparing to develop spreadsheets by analysing tasks to determine specifications and organising task requirements in relation to data entry, storage, output,

reporting and presentation requirements
•    Developing a linked spreadsheet solution including using formulae and functions, formatting cells, using data attributes with absolute and/or relative cell

references and testing formulae to confirm output meets tasks requirements
•    Automating and standardising the spreadsheet operation by using templates and macros
•    Using the spreadsheets including entering/checking data to be error free, importing/exporting data, using manuals and documentation, previewing and printing

tasks and finally naming and storing spreadsheets
•    Representing numerical data in graphic form.
Assessment Task 2 of 3
Develop and use complex spreadsheets – Portfolio of Evidence
Instructions
You are required to use Microsoft Excel to create workbooks or templates as instructed below. Please follow instructions and submit all required workbooks, templates

and printouts.
Scenario
Breeze Appliances have three branches across Australia: Melbourne, Sydney and Brisbane.  Every quarter, each branch manager is required to calculate the sales

commission each sales person achieves each month and send this information to head office.  At head office the sales information is collated into one spreadsheet for

analysis.
A template will be required for the recording and calculation of sales and commission for each branch.  Head office also requires a template to analyse the sales data

received from each branch.

Part 1 – Branch Template
A workbook template will be created that will record and calculate the sales data for each branch shown in appendix 1.  (The data for each branch must be recorded in a

separate workbook.)
1.    Spend some time planning and designing your template. You may wish to note down the plan on a piece of paper.
2.    Import the text file called Commission Rates.txt into a new workbook.  Name the worksheet Commission Rates.
3.    Create a named range for the commission rates data.  The Commission Rates data is used to look up the % commission each sales person will receive.  This

percentage will then be used to calculate the commission for each month, based on monthly sales.
4.    Insert a new sheet before the Commission Rates worksheet.  Name the new sheet Sales.
5.    On the Sales worksheet, calculate the commission each sales person will receive each month.
6.    Each month if a sales person equals or exceeds a 12% commission target, they receive an additional bonus of $1,250.  Insert a column for each month to

determine if the sales person will receive the bonus.  (This can be achieved by combining an IF and a VLOOKUP statement.)  The commission target and the amount of

bonus on offer will vary every quarter.  Ensure that this is taken into account when designing the spreadsheet by positioning the bonus amount and the % commission

target in separate cells at the top of the worksheet data. You will need to think of using absolute referencing in your formula as well.
7.    Columns must be wide enough to display data properly.
8.    Include a header containing the file name of the workbook.
9.    Today’s date and a Branch name area must appear on the worksheet.  (The branch name will be filled in when the template is used.)
10.    Calculate the total sales for each month.
11.    Save the workbook file as a template with an appropriate file name in the correct templates folder.  Note that macros are required in the template.
12.    A summary of the sales for each of the three months recorded must appear on the worksheet.  This summary must include average, maximum and minimum calculations

for each month.  All figures must be rounded to the nearest $100.
13.    Format the spreadsheet so it is clear and easy to read.  Use Calibri font in keeping with the organisation’s house style.  Format currency columns to two

decimal places.
14.    Check all that formulae work correctly.
15.    Ensure that the workbook template is set up to automate all calculations so the user only needs to enter the sales data for each month.
16.    Ensure users cannot enter data in formula cells by protecting these cells. Please use ‘password’ for your password when protecting the sheet as the marker

needs to be able to access the formulae.
17.    Develop a macro that will sort all sales persons into alphabetical order.
18.    Develop a macro that will print only the sales summary information.
19.    Assign these two macros to buttons at the top of the worksheet.

Part 2 – Branch Workbooks
1.    Using the template created in Part 1 to produce a workbook for each branch using the sales data in appendix 1 for all three branches.
2.    Ensure all data is checked to ensure accurate input.
3.    Each workbook should be saved with an appropriate file name in your working folder.
4.    Ensure the sales person data is sorted into alphabetical order and the summary information is printed for each workbook. Scan the reports into a pdf file to

submit.
Part 3 – Head Office Template
Create a workbook template that will display the total sales for each month from each branch to be used at Head Office.
1.    Spend some time planning and designing your template. You may wish tonote down the plan on a piece of paper.
2.    Columns must be wide enough to display data properly.
3.    Include a header containing the date (to the right) and a footer with the file name in the lower right corner.
4.    Use Calibri font in keeping with the organisation’s house style.
5.    Save the workbook file as a template with an appropriate file name in the correct templates folder.
6.    Total each column and row to determine the total sales for each month and each branch.
7.    Create a chart that displays the sales for each branch over the three month period. Use an appropriate chart type and add a title.  Insert the chart on a new

worksheet and rename the sheet appropriately.
8.    Create a chart that displays the total sales for each branch. Use an appropriate chart type that will show which branch has the larger percentage of sales

against all other branches.  Use appropriate data labels and add a chart title.  Explode the largest segment.  Insert the chart on a new worksheet and rename the sheet

appropriately.
Part 4 – Head Office Workbook
1.    Using the Head Office template created in Part 3 produce a Head Office workbook using the data from the Branch workbooks created in Part 2.
2.    You will do this by linking the Branch workbooks to the Head Office workbook. Any changes in the data in the Branch should automatically update in the Head

Office workbook. You will need to think about using relative referencing in your formulas to be able to copy the link across your months.
3.    The workbook should be saved with an appropriate file name in your working folder.

Checklist
You will need to complete the following:
0
Please make sure you have followed all formatting requirements
0
Checked that all formulas and functions work properly
0
Part 1:
?    Created and submitted a Branch macro enabled template.
?    Please ensure worksheet protection password is ‘password’ for marker to access
0
Part 2:
?    Created and submitted 3 Branch macro enabled workbooks
?    Printed three sales report (one for each branch) submitted as pdf file
0
Part 3:
?    Created and submitted a Head Office template
0
Part 4:
?    Created and submitted Head Office workbook
0
Upload all documents to OpenSpace
Note: The field boxes above are an optional self-checklist. Double click each check box to mark as complete.

Appendix 1 – Sales Data
Melbourne Branch
Sales person    Jan -14    Feb -14    Mar 14
Gibbons, Barry    2,450.00    3,680.00    3,360.00
Pascoe, Ian    8,890.00    9,770.00    7,988.00
Walters, Denise    4,765.00    6,750.00    7,420.00
Stringer, Lee    7,680.00    6,995.00    5,600.00
Knott, Delia    5,790.00    4,987.00    4,325.00
Thompson, James    4,730.00    5,450.00    6,740.00
Ritchie, Ross    7,988.00    8,664.00    6,950.00
Gordon, Marie    6,750.00    7,889.00    8,745.00
Brisbane Branch
Sales person    Jan -14     Feb- 14    Mar -14
Carter, Richard    3,990.00    2,770.00    2,980.00
Tait, Lee    6,980.00    5,670.00    6,120.00
Penfold, Alison    8,915.00    7,830.00    7,680.00
Warner, Scott    4,880.00    5,990.00    6,350.00
Mahoney, Andrew    6,235.00    5,050.00    5,620.00
Leonard, Jill    7,320.00    5,990.00    5,415.00
Cotton, Ian    5,425.00    3,785.00    4,060.00

Sydney Branch
Sales person    Jan -14    Feb -14    Mar -14
Shields, David    9,850.00    8,360.00    7,670.00
Fisher, Harry    7,550.00    5,990.00    5,685.00
Kennedy, Frank    6,665.00    7,150.00    6,360.00
Irvine, Kim    4,380.00    4,890.00    5,120.00
Taylor, Pam    6,550.00    5,695.00    4,360.00
Dickson, John    5,670.00    6,755.00    6,430.00
Sinclair, Mark    6,558.00    5,830.00    5,325.00
Henderson, Tina    7,330.00    6,990.00    7,120.00

Appendix 1 – Sales Data
Melbourne Branch
Sales person    Jan -14    Feb -14    Mar 14
Gibbons, Barry    2,450.00    3,680.00    3,360.00
Pascoe, Ian    8,890.00    9,770.00    7,988.00
Walters, Denise    4,765.00    6,750.00    7,420.00
Stringer, Lee    7,680.00    6,995.00    5,600.00
Knott, Delia    5,790.00    4,987.00    4,325.00
Thompson, James    4,730.00    5,450.00    6,740.00
Ritchie, Ross    7,988.00    8,664.00    6,950.00
Gordon, Marie    6,750.00    7,889.00    8,745.00
Brisbane Branch
Sales person    Jan -14     Feb- 14    Mar -14
Carter, Richard    3,990.00    2,770.00    2,980.00
Tait, Lee    6,980.00    5,670.00    6,120.00
Penfold, Alison    8,915.00    7,830.00    7,680.00
Warner, Scott    4,880.00    5,990.00    6,350.00
Mahoney, Andrew    6,235.00    5,050.00    5,620.00
Leonard, Jill    7,320.00    5,990.00    5,415.00
Cotton, Ian    5,425.00    3,785.00    4,060.00

Sydney Branch
Sales person    Jan -14    Feb -14    Mar -14
Shields, David    9,850.00    8,360.00    7,670.00
Fisher, Harry    7,550.00    5,990.00    5,685.00
Kennedy, Frank    6,665.00    7,150.00    6,360.00
Irvine, Kim    4,380.00    4,890.00    5,120.00
Taylor, Pam    6,550.00    5,695.00    4,360.00
Dickson, John    5,670.00    6,755.00    6,430.00
Sinclair, Mark    6,558.00    5,830.00    5,325.00
Henderson, Tina    7,330.00    6,990.00    7,120.00

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂