Develop and use complex complex spreadsheets excel 2016 / BSBITU402

The scenario:

An exclusive car sales company, High End Cars, has branch offices in various suburbs in Victoria and wishes to keep a record of agents sales for each month, commission earned by each sales person, each branch and have a summary for all branches. The company has provided the raw data on Brightspace in session 14.

Create workbook to meet this request.

Find an appropriate picture for a logo (internet or on locally available pictures) for this company and place it on the left hand corner of each sheet.

The company requires a sheet for each branch and a summary sheet. Each sales persons' total for the 6 months needs to be shown as well as the totals for each month. The commission for each sales person needs to be calculated.

All sheets need to be formatted appropriately, demonstrating a wide variety of formatting features.

• A sales person's commission rate is 3% of sales.
• If a sales person's total sales for 6 months are over 1.5 million dollars they receive a bonus of $5,000, if not, they do not receive a bonus. In the column next to Commission put the heading 'Bonus Amount' and using an If statement/function put the $5000 for those salespeople that meet the condition.

In the next column put the heading 'Total due to Salesperson' and then calculate this by adding the commission and bonus together.

• A summary sheet of the branches totals for each month Jan-June, a 6 month total for each branch and the commission totals for each branch for this time needs to be created.
• Create 3D pie chart to show each branches total for the 6 months from the summary sheet as shown below. Give the chart an appropriate heading.