Timesheet Business Intelligence design?

[RichRoy]

I'm trying to create a project management / business intelligence platform with Excel.
Our company provides professional services and currently has 50 employees and subcontractors submitting timesheets with approximately 40-60 active customers. Each employee is currently submitting a Excel timesheet at the end of the month.

I have written some VBA code that copies each timesheet into a WorkBook, with a WorkSheet for each employee.
Approximately 50 WorkSheets ( 1 per employee)
Timesheet Fields: Customer, Employee, Business Group, Billable - Y/N, Work category, Comments, Hours worked

My question is: what is the best approach to analyze this data?
The Pivot Tables and Charts I would like to create in a dashboard:
- View Customers and Employees with hours worked.
- View for each Customer / Employee working hours
- View for each Employee showing customer work activity and hours

1. Want to deliver a dashboard for management to use without getting involved with setting up the data.

2. I'm exploring Pivot Tables and linking all the WorkSheets, however, I'm concerned that I will have to update this all the time to setup the correct regions as the Tables expand every month.

3. Also, looking at PowerPivot.
Would linking the Employee WorkSheets together make sense?
Would all the users (i.e. Management) require the PowerPivot add-in or just me for designing the data-links? Most users do not have the Professional version of Excel.
Table format (Employee Timesheet)
/ Customer / Employee / Business Group / Work Category / Billable - Y/N / Comments / Hours Worked /

I'm starting to familiarize myself with Excel's advanced tools, so I'm finding it difficult to specify the best design.
I feel that I'm almost there, but I don't understand the best path forward.
Any help or comments would be appreciated.

Thanks

 

[Matt Allington]

No doubt in my mind. You want Power Query to combine all the data together and power pivot to report on it. You definitely need all the data combined. Excel versions will be an issue it sounds. You need Office Pro Plus 2013 to author PP workbooks, office pro to interact with them, in Ofice 2016)you only need pro for both. You could use power bi desktop which is free for everyone. You are no worse off than distributing excel workbooks, but everyone needs new software. Or you could publish the power bi report to the cloud. The free service will probably cover your needs.

If if you are going to do this, then you will need to do some learning. It is easy to learn - but learn you must.

 

[FDibbins]

Excel works best if all data is on the same sheet. If, instead of having a bunch of (similar formatted?)( sheets for each person, you could list them all 1 below the other, and run summaries on that. All calcs would be automatic, and you would have the added flexibility of creating tables the way you want them to look, not how PT allows you to make them look.

 

[RichRoy]

Thank you for the responses.
I was stuck trying to sort out which approach to take.
On the weekend I signed up for a free trial of the Office 365 Enterprise E3 which has the Excel ProPlus and the Business Intelligence platform.
After appending all the EmployeeTimesheets into 1 WorkSheet I was able to create all the tables and charts I was looking for!!!
One of my issues is that we have a lot of Mac users and PowerPivot is not compatible with even the latest Excel 2016...
I'm now looking at the BI service for publishing the reports.
Lots to learn.

Thanks again.
Rich