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 Task:

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.

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

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$ inches, their units sold, total booked revenue and total sales revenue foregone