Preparing the sales report - what you are required to do: Download the start file and rename it to your name and save it on desktop. Call your lecturer to check if you get the right file.

You must convert the basic data provided into a usable spread sheet report on performance for each centre of STAR Tutoring:

1.Format the report so that the data and information is clearly labelled and easy to understand. All cells should be in the appropriate format

2.Due to Covid-19 pandemic STAR tutoring has decided to give discount on the tuition fees.

As such you are required to find the fees after discount and then Add FIVE columns to the spread sheet that display the Sales Income earned after discount per centre for each category.

3.Add a column to the spread sheet that displays the Gross Sales Income earned for each centre. This is the total of the sales income for every category sold for each centre.

4.Add a column to the spread sheet that displays the commission payable to each centre - they receive 3.5% if the sales are greater than or equal to average total sales, otherwise they receive a 1.2% commission.

5.Show the Average GROSS sales over all centre

6.VLOOKUP - STAR tutoring to try out a revised commission scheme to reward centre that generate more income. They have decided to introduce a BONUS commission in ADDITION to existing commission above. For centre with a Gross Sales below $50,000 there will be no BONUS commission. For Gross Sales income higher than $50,000 but below $100,000 the Bonus rate will be 3%. For Gross Sales above $100,000 the BONUS rate will be 5.5%.The following table show the Bonus commission rates:

Table 3 - Bonus Commission

Gross Sales

Bonus pack

Bonus Commission Rate

$0 to less than $50,000

Basic

0%

$50,000 to less than $100,000

Intermediate

3% of sales

Greater than $100,000

Premium

5.5% of sales

Add the data presented in the table above to your spread sheet and use the VLOOKUP function to calculate the bonus commission rate. Add a column to show this bonus commission rate (in $).

7.Add another column next to Bonus Commission rate to show the Bonus pack for each centre. Use Vlookup to show the bonus pack.

8.Develop a column chart showing the performance of each centre for each of the category. Hint: Develop a chart showing the sales amount for each category for each centre.

9.Use Data Validation to allow the right entry of sales figures. Be sure to include appropriate input alert and error messages.

10. Automatically highlight the appropriate row/rows which have performed better than the overall average of Gross Sales. (Hint: You need to use New Rule in conditional formatting to ensure the entire row to be highlighted when the condition is fulfilled - 'conditional formatting rows based on cell value'. Merely highlighting one cell or cells will not yield any marks.

11. Transfer relevant summary data to the Summary sheet, using copy and paste link to make the data "LIVE".

12. STAR Tutoring is planning to open a new store in 'Taiping' to expand their business. They want to identify how much this new store total sales should be in order to meet their new total average sales target of $115,000.

Insert a row to add this new store. Using Whatif Goal Seek determine the sales required for Taiping to generate a total average sale of $115,000.

13. Protect the worksheet. All cells containing formulae or labels that are not expected to be used for input by the user must be protected against accidental change. Cells expected to accept user input should be unprotected. Do not use passwords for this exercise.

14. Document any add additional features you added to the worksheet, also document them on the worksheet.