Required to use a spreadsheet and format it appropriately for the communication of financial details

ASSIGNMENT DETAILS In this assignment you are required to use a spreadsheet and format it appropriately for the communication of financial details of a firm while carrying out some financial functions. Please see the instructions below. The template for the spreadsheet can be found as an attachement Excelassignment You are an employee of a company selling cars and you have been sent a spreadsheet by another employee who has never used excel before. She is requesting your assistance in formatting this spreadsheet and also on showing this person how you can use excel to find out specific calculations. The spreadsheet can be found entitled “ExcelAssignment2018” and the following are the necessary features you need to use to help your fellow employee: 1. The spreadsheet should be formatted in a user friendly manner so that it is easy to read and analyse the existing data. It should be clear what each figure represents. We want to impress your work colleague by making the spreadsheet look professional and attractive to analyse. 2. Each salesperson must have a total but what we need to show the other employee is total sales for each product, the average sales for each product, the lowest sale per product, the highest sale per product and also the number of salespeople selling each product using the countnumbers function. Illustrate these calculations on the spreadsheet ensuring that the values are presented professionally. This must be done using formulae. 3. You have been asked if there is a function that we can use which will automatically categorise the total sales for each product into: Bonus Sales (if total sales are greater than 3,000000 per product) Good Sales (if total sales are greater than 2,000000 per product) Poor Sales (if total sales are less than 1,500000 per product) So we should be able to create a function to calculate this for the first product and use autofill to apply it to the rest. Again presentation of this is important. 4. The spreadsheet has four worksheets. In the second worksheet entitled “LinkstotalSales” create a linked formula which only shows the total of the sales for all the salespeople. 5. The company also has an insurance division and corresponding salespeople plus their sales figures for the year are on the next worksheet entitled “Commission”. Working with the other worksheet entitled” rates” devise a function which will work out the commission for each salesperson. Also, sort the names in the “Commission” worksheet in descending order as this was requested. 6. Create a chart of your choice to show each total product sale from the first worksheet.Make the chart as user friendly as possible showing enough information to make it clear to everyone. In other words, design it well. Place this chart where it is most presentable. Marking Scheme: Formatting (20) Borders (3) Shading (3) Merge Cells (3) Bold & Large Font for headings (3) Currency Formatting (3) 2 Decimal Places (3) Alignment of Text and Numbers (2) Formulae (60) SUM (5) IF Statement (10) Lookup Statement (15) COUNT (5) MAX (5) MIN (5) Average (5) Link to a second worksheet (5) Sort (5) Overview/Presentation (20) Appropriate Graph or Chart (10) Printout Presentation(Layout / Minimum Pages / Header/Footer) (10)