Budget vs. Actual Detail

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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
hi

consider a really simple , sample dataset: subproject, datatype, value
with entries in datatype of either "budget" or "actual"
and perhaps half a dozen records

now make a pivot chart. For example, select a cell in the data, ALT-D-P to start the wizard & pick pivot CHART not the default pivot table. Drag some fields around & you can arrive at a bar chart like what you're after

can you achieve that? And then expand/develop it for what is wanted?
 

Forum statistics

Threads
1,136,327
Messages
5,675,128
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top