Please download this file : https://drive.google.com/file/d/1tMYafnf_j6TtAvBaGCARwVpI1lPQe0MQ/view?usp=sharing

And complete the following tasks as demonstrated by the videos at the end of this post:

Task	Task Instructions
1	Apply a range name to the table used in the vlookup. Name this range “PriceList”
2	Update the Vlookup function to use the “PriceList” Name range
3	Use a range name for the percentage and update the formula which calculates the vat
4	Insert a new row for “Cinnamon rolls” which sold 2000 items
5	Insert  a new column “Hot Seller?”. This column will contain a value of “Yes” if more than 1500 Items were sold for the month and “NO” otherwise. Implement this using an “if” function.
6	Apply a custom sort, First sort by “Hot Seller” Descending then by “revenue per item” descending
7	Apply an advanced filter using to show items that weren’t hot sellers but sold more than 1000 items
8	Create a pie chart showing  the net revenue per item
9	Create a barchart showing both the revenue per item and the vat per item
10	Move the tables and constants to a new sheet, “Constants”. i.e the vat value and price list. Observe how the formula for the vlookup has changed. Add a new column, “Discounted price” to the sales table .  Add a new constant called Discount to the Constants sheet with a value of 6%.  Use this value to calculate the discounted price per item in the sheet.

See the video demonstrations below for the tasks outlined above.

The above video was continued in another class session:

© 2020  Vedesh Kungebeharry. All rights reserved.