Eurocomp Ltd is a fictional European online seller of laptops. You are their data analyst charged with making certain revenue calculations from promotional sales and analyzing them.

Sales data are given in the laptop$\_$price$\_$cleaned tab. There are about $1300$ types of laptops. Here is description of some key fields

$-$ Each repesented by a unique laptop ID $($Col A$).$

$-$ Brand is in Col B

$-$ Size of screen in inches $($Col E$)$

$-$ Price in Euros $($Col M$)$

$-$ Weight in Kilograms $($KG$)($Col N$)$

$-$ Number of Units Sold $($Col O$)$

The company gave price discounts based on the Discount Policy based on brands:

$-$ For Apple or HP No Discount is offered

$-$ For Dell, a $5\%$ discount on laptops $>15$ inches only

$-$ All other brands and sizes get a $7\mathrm{.}5\%$ discount

Company also charges shipping fees per laptop based on the weight of the laptop. Shipping information is provided in the table below.

Your Tasks:

$1.$ Create a column for the discount $\%($Use Nested IF Functions and any other logical functions needed inside of the IF $).$ Using IFS will not get credit.

$2.$ Create a column with the correct shipping fees per laptop using the table below.

$3.$ Create a column for discounted price per laptop using the above discount rate

For each Laptop ID You need to Make $5$ different revenue calculations $($in separate columns$)$

$4.$ Calculate the FULL SALES REVENUE based on full price

$5.$ Calcualate Discounted Sales Revenue based on the Discounted Price you calculated

$6.$ Calculate SALES REVENUE FOREGONE $=$ Revenue without discount $-$ Revenue with discount

$7.$ Cacluate Shipping Revenue based on the shipping fees

$8.$ Calculate the BOOKED REVENUE $($inc shipping revenue and Discounted sales revenue$)$ for each laptop ID

$9.$Comany is interested examining in laptops that are $15$ inches or more in size $.$ Identify these laptop IDs using a Yes$/$No variable in the data.

$10$ Using Countif SUMIF$/$COUNTIF functions $($as appropriate$)$ get the number of laptop IDS that are $>15$

Shipping Charges Table

Kilograms Shipping fees $($in Euros$)$

$020$

$122$

$224$

$326$

$428$