In the “Data” tab, you have been given a data set for a product that has information on month, week, sales, and margin. In this assessment, summarize the data by following the instructions below. You can add worksheets, insert columns or rows into the data as you see necessary. You can also use any Excel tools/formulas/tricks to complete the assessment. Please save all of your work and be prepared to discuss the reasoning behind your work. As you only have 30 minutes, please work to find the answers to all questions first. If you have remaining time, you can adjust formatting and present the data in a more executive friendly format.
When complete, do a “save as” with the following naming convention: “FirstName_LastName_Assessment.xls” on the desktop.
Someone from Employee Services will help you when you are finished.
Exercise 1 Data tab
Using the information on the ‘data’ tab, create a pivot table illustrating total Sales $ and Margin $ by month for 2014.
Exercise 2 Data tab
Calculate the Sales $ difference from the prior week in column G
Exercise 3 Data tab
Insert a column before Column A and add a formula to combine year, month, week
Exercise 4 Data3 tab
On the Data3 tab, write a VLOOKUP to pull over volume grades for the stores in Column A. Reference the master list on the Data2 tab.
using the information on the ‘data’ tab, create a pivot table illustrating total Sales $ and Margin $ by month for 2014