Common Business Unit: Sales and Commission
Common Business Unit BSBITU402A Develop and use complex spreadsheets(Excel 2013)
Assessment 2
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.
Requirements
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.)
Completed
1. Spend some time planning and designing your template. You may wish to jot 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
Assessment 2
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.
Requirements
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.)
Completed
1. Spend some time planning and designing your template. You may wish to jot 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