Good evening if someone can help me with this. Thank you

1-You need to save the file asMacro Enabled Workbook.

2- Each question will ask you to submit a file.However, youonly need to submit the file once(in question 10).Make sure that you completeALL the questions in the file before you submit.

QUESTION 1

1.In Tab Question 1 name the ranges containing theCOGSfor Year 1, and Year 2 and calculate the Total COGS for those years using the formula:COGS_Yr1+COGS_Year2

1.Attach File

Attach Local File

10 points

QUESTION 2

1.In Tab Question 2 use the VLookup function to get theSalaryfor the following employees (copy the list below, paste it to the worksheet for question 2 and use Vlookup to get the Salary) :

Employee ID

39212594

6529727

62909768

21281615

79801882

4639128

66841312

1.Attach File

Attach Local File

10 points

QUESTION 3

1.Answer question 3 in tab Question 3.

You are considering the followingtwo mutually exclusive projects. The required rate of return is 10.50 percent for projectAand 12.75 percent for projectB.Which project should you accept and why?

Clearly demonstrate your knowledge of the NPV function in Excel in your answer.

Year	Project A	Project B
0	-$100,000	-$126,900
1	$50,400	$ 69,700
2	$75,300	$ 80,900
3	$11,700	$ 0

1.Attach File

Attach Local File

10 points

QUESTION 4

1.Answer this question in the tab Question 4.

Demonstrate your knowledge of the IF statement and calculate the total sales forPlanner BONEIL.

1.Attach File

Attach Local File

10 points

QUESTION 5

1.Using the data in tab Question 5, use conditional formatting in the following situations:

a) Highlight in RED theStore Nameof each Store that was opened in the Year2010.

b) Highlight in GREEN those COGSover$20,000.

1.Attach File

Attach Local File

10 points

QUESTION 6

1.Answer this question in tab Question 6.

UsingFiltersfind all stores that are inCENTRO NORTEand were opened in2003.

1.Attach File

Attach Local File

10 points

QUESTION 7

1.Answer question 7 in the appropriate tab.

You are entering in the cell range D5:D23 the dates of transactions that occurred during July 2012. Enter adata validationsetting that ensures that each date entered occurs inJuly 2012.

1.Attach File

Attach Local File

10 points

QUESTION 8

1.Insert aPivot Tablein the Tab for Question 8 that shows theUnits SoldbyPlanner Code.What is the total Qty Sold inQuarter 1(Jan+Feb+Mar)for plannerKIANDERS?

Hint: you must group by quarter.

1.Attach File

Attach Local File

10 points

QUESTION 9

1.In tab Question 9Record a Macroto record the following actions:

1- Call the Macro:question9

2- Sort the table byCustomer Name, then byDepartment

3- Apply Filters to all the columns

3- In Cell E3 type your name.

Grading Criteria:

- Getting the Macro to work - 100%

1.Attach File

Attach Local File

10 points

QUESTION 10

1.In tab Question 10 - Use theSubtotal command for the following computation: Find the totalUnits Sold for theMonth of Sept 2012

1.Attach File