QUESTION 1
1.	In Tab “Question 1” name the ranges containing the COGS for 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 the Salary for 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 following two mutually exclusive projects. The required rate of return is 10.50 percent for project A and 12.75 percent for project B. 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 for Planner 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 the Store Name of each Store that was opened in the Year 2010.
b)      Highlight in GREEN those COGS over $20,000.
1.	Attach File
Attach Local File
10 points   
QUESTION 6
1.	Answer this question in tab Question 6. 
Using Filters find all stores that are in CENTRO NORTE and were opened in 2003.
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 a data validation setting that ensures that each date entered occurs in July 2012.
1.	Attach File
Attach Local File
10 points   
QUESTION 8
1.	Insert a Pivot Table in the Tab for Question 8 that shows the Units Sold by Planner Code.  What is the total Qty Sold in Quarter 1 (Jan+Feb+Mar)  for planner KIANDERS?
Hint: you must group by quarter.
1.	Attach File
Attach Local File
10 points   
QUESTION 9
1.	In tab Question 9 Record a Macro to record the following actions:
1-    Call the Macro: question9
2-    Sort the table by Customer Name , then by Department
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 the Subtotal command for the following computation:  Find the total Units Sold for the Month of Sept 2012 
1.	Attach File