Use the data in the sheet titled "Raw data" to calculate the profit for each product

category and product sub category $($ability to drill down$).(16$ points: $2$ points each$)$

a$.$ Create a new worksheet titled $-"$Q$4-$ Profit by Category" and insert a new

pivot table for the data in the sheet $-$ "Raw Data". Move the worksheet to

the $4^{th}$ position.

b$.$ List the total profit for product categories and subcategories in this table

c$.$ Format the numbers to reflect the $"$ $ $"$ symbol before the figures and $2$

decimal places.

d$.$ Change the column headings to be meaningful.

e$.$ Add a meaningful title to the worksheet at the top and center it across the

pivot table.

f$.$ Use conditional bars to highlight gains and losses.

g$.$ Add another column to the pivot table display the profit or loss as a percent of

the total.

h$.$ Use conditional formatting to display the top $3$ products that had the highest

percentage.

Management would like to know when their busiest month for sales is$.(16$ points: $2$

points each$)$

a$.$ Create a new worksheet titled $-"$Q$5-$Sales by Month" and insert a new pivot

table for the data in the sheet $-$ "Raw Data". Move the worksheet after Q$4$

worksheet.

b$.$ List only the months in the $1^{st}$ column and the sales in the $2^{nd}$ column. To do

this click on one of the dates. Then do a right mouse click to display a menu.

Click the Group option. Another menu will display. Click on Months and

Quarters.

c$.$ Sort the sales in Descending order.

d$.$ Format the numbers to reflect the $"$$$"$ symbol before the figures and $2$

decimal places.

e$.$ Change the column headings to be meaningful.

f$.$ Add a meaningful title to the worksheet at the top and center it across the

pivot table.

g$.$ Use to conditional formatting to highlight the top month for sales in green,

the bottom month for sales in red.

h$.$ Next, add a filter at the top so that you can filter out product sub categories.

Management would like to know $-$ what is the split in terms of profit, across the

Product Categories, for each quarter. Use a Line Chart to show the numbers. Rank