Posted: February 12th, 2015

Spreedsheets

Paper, Order, or Assignment Requirements

 

 

 

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:

	Please make sure you have followed all formatting requirements
	Checked that all formulas and functions work properly
	Part 1: Created and submitted a Branch macro enabled template. Please ensure worksheet protection password is ‘password’ for marker to access
	Part 2: Created and submitted 3 Branch macro enabled workbooks Printed three sales report (one for each branch) submitted as pdf file
	Part 3: Created and submitted a Head Office template
	Part 4: Created and submitted Head Office workbook
	Upload all documents to OpenSpace

Note: The field boxes above are an optional self-checklist. Double click each check box to mark as complete.

 

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

• Explain how you have checked formulas in the workbook template created in Assessment 2, Part 1.

 

 

• 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

                                                                                                                                                                                                     

 

 

• 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.

……………………………………………………………………………………………………………………………

……………………………………………………………………………………………………………………………

……………………………………………………………………………………………………………………………

• 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

• 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.

• 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

• 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.
• Print the template that each branch uses, and then print another copy showing the formulas.
• Print the template that is used by Head Office, and then print another copy showing the formulas.
• Print the entire workbook that Head Office has produced that displays the sales from all branches for the quarter Jan – Mar 14.
• 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.
• Scan and upload all printed pages to OpenSpace.

Checklist

You will need to complete the following:

	Submit questions for Part 1 as a Word document
	Submit scanned printed reports as a PDF file for Part 2
	Upload all documents to OpenSpace

Note: The field boxes above are an optional self-checklist. Double click each check box to mark as complete.