BombCenter
New Member
- Joined
- Aug 31, 2011
- Messages
- 19
Hi All,
As the most excel-proficient person at my workplace, I've somehow been tasked with creating an accounting and labor-hours reporting dashboard, even though I'm neither an accountant nor project manager (I'm IT). Ideally, my boss wants to track actual labor hours expended in the field vs. labor hours budgeted during job estimation, and eventually a budget vs. actual report for costs. We use an estimating spreadsheet in-house (which I also wrote), and extracting budgeted hours is a trivial task. For actual hours expended, we use web-based time tracking software. Each employee clocks-in against a project, each project can have up to 5 tiers of subprojects/tasks, resulting in hundreds of different subproject/task combinations for each parent project. Time-sheet data can be exported from the platform as CSV and is exported in a per-entry format (every time an employee clocks in/out, or switches tasks, it counts as an entry). This data is easy enough to filter via pivot-table/charts.
However, my boss would like to see a chart (e.g., stacked bar) showing budgeted hours (high-level) vs. actual hours expended (detail-level) on a per-subproject basis. I'm unsure of how to combine the two data sets into a single pivot table (or if that's even the right approach) to generate a chart outlining this data. I figure this is a relatively common real-world use case, so there's probably a novel, in-built solution to do this -- I saw something about Data Models for Excel 2013/2016, but unfortunately our office uses a mix of Excel 2007/2010x64.
I'm relatively new to pivot tables, though am finding them easy enough to understand. I'm comfy with macros and coding in general, if required, but would prefer an in-built solution. Could anyone point me in the right direction or toward a tutorial?
Regards.
As the most excel-proficient person at my workplace, I've somehow been tasked with creating an accounting and labor-hours reporting dashboard, even though I'm neither an accountant nor project manager (I'm IT). Ideally, my boss wants to track actual labor hours expended in the field vs. labor hours budgeted during job estimation, and eventually a budget vs. actual report for costs. We use an estimating spreadsheet in-house (which I also wrote), and extracting budgeted hours is a trivial task. For actual hours expended, we use web-based time tracking software. Each employee clocks-in against a project, each project can have up to 5 tiers of subprojects/tasks, resulting in hundreds of different subproject/task combinations for each parent project. Time-sheet data can be exported from the platform as CSV and is exported in a per-entry format (every time an employee clocks in/out, or switches tasks, it counts as an entry). This data is easy enough to filter via pivot-table/charts.
However, my boss would like to see a chart (e.g., stacked bar) showing budgeted hours (high-level) vs. actual hours expended (detail-level) on a per-subproject basis. I'm unsure of how to combine the two data sets into a single pivot table (or if that's even the right approach) to generate a chart outlining this data. I figure this is a relatively common real-world use case, so there's probably a novel, in-built solution to do this -- I saw something about Data Models for Excel 2013/2016, but unfortunately our office uses a mix of Excel 2007/2010x64.
I'm relatively new to pivot tables, though am finding them easy enough to understand. I'm comfy with macros and coding in general, if required, but would prefer an in-built solution. Could anyone point me in the right direction or toward a tutorial?
Regards.
