P.S Open the link below with excel data file. since course Hero system would not allow me to excel file upload directly. Thank you

Question 7 (18 points):

https://drive.google.com/file/d/1ijmAEy1pO-6VzfpK9UrWfVm7F74M-JSc/view?usp=sharing

Data used: "Real Estate" sheet

This table shows the purchased and sold price for a portfolio of properties

7a. Make( Create) a new column called Profit_Loss.

Profit_Loss = Sold Price - Purchase Price

Then make( create )a Level column

If a property results in a loss, then give value -10 (negative 10) in column Level.

If not, if a property results in a profit, give value 20 (positive 20) in column Level.

Find the sum of Level column over the whole portfolio. (6 points)

7b. Calculate the average Purchase Price for the whole portfolio. So you have only 1 average number.

Make (Create) a new column named "Purchased Price Level".

If purchased price of a property is more than the average number above, then give "Purchased Price Level" value 50.

If not, give the Purchase Price Level value 60.

Report the sum of the "Purchase Price Level" for the whole portfolio. (6 points)

7c. Make( Create) a new column named "Both Price Level".

For a given property, if purchased price is greater than 500 and sold price is also greater than 500 then give "Both Price Level" value 2. If not give value 0.

Report the sum of this "Both Price Level" column. (6 points)

Question 8 (27 points):

Data used: "Sales" sheet

This table report the sales for each group over a number of weeks.

You will have to create new tables that contain salary information for each group based on the below rules.

8a.Each week, salary for each group is based on the schedule below:

If Sales in a given week is less than 300 then salary for this week is 250

If Sales in a given week is more than or equal to 300 then salary for this week is 350

Report the total dollar salary for all groups over the whole period. (6 points)

8b.Each week, calculate the average Sales across 8 groups.