Hi All,
I recently had an Excel test (which I failed) and which is still puzzling me... I was basically asked to reformat row data from 2 different tabs into a single sheet WITHOUT USING PIVOT TABLES.
The data is as follows:
- Tab 1 provides the Sales Representative's targets by quarter (along with additional info like Sales Rep's Manager and Region)
- Tab 2 provides the Sales Representative's bookings by month.
The question was simple: can you take something out of this raw data and present it in a single tab. I of course figured I was supposed to present the Sales Rep's bookings vs their targets by quarter (and year) along with the % achievement. Ideally their would be a way to also show a summary by Region and Manager.
Using a pivot would be quite straightforward I guess. However, without pivot, I'm not sure how to present this data:
- in a nice format,
- in which a user can easily retrieve the info he wants to analyse
- from which I could ideally generate charts to present in a ppt
- finally, which would automatically get refreshed if the data from tab1 and 2 get changed (e.g. if a new Rep is added at the bottom of the booking sheet). This last one was not required but I think it would make sense.
The file is attached. I have also provided my methodology in Step 1, 2 and 3 tabs (although I'm not really happy about it!).
Many, Many thanks in advance for your help. And let me know if I need to clarify anything.
Seb
Bookmarks