Hi,
I'm trying to design a worksheet that does several comparisons of actual to budget, but I just can't seem to come up with workable design.
Here's the general layout:
The raw data consists of data blocks organized like so:
The rows comprise several revenue and cost categories:
Revenue-Cust A
Revenue-Cust B
Revenue-Cust C
Material Costs Plant 1
Material Costs Plant 2
Material Costs Plant 3
Labor Costs
Admin Costs
The columns are Jan-Dec, Total, Month, Quarter, and Year to Date. So I basically have a number of these 17-column data blocks for different types of data, e.g. current year$, prior year$, budget$, forecast$,current year stats, prior year stats, budget stats, forecast stats.
I also have a few intermediate tables that do things like calculate price-volume variances and sum the data in different ways.
I'd like to create a final report that pulls the data together so that I can input a date (e.g. Sept 30), comparative columns (e,g, current year actual to budget or current year to prior year), and a time period (month, quarter, year-to-date).
My initial design strategy was to have a separate, but identical worksheet for each data block (Current year worksheet, budget worksheet, etc) but it's been maddeningly difficult to keep the worksheets synced up (we get new customers or lose old ones for example). Linking all of formulas is also very tedious. I'm wondering if I'd be better off stacking several data blocks in the same worksheet.
This must be a common problem, and there's got to be a better than the way I'm going about it! I'd appreciate any feedback that you can give.
David
I'm trying to design a worksheet that does several comparisons of actual to budget, but I just can't seem to come up with workable design.
Here's the general layout:
The raw data consists of data blocks organized like so:
The rows comprise several revenue and cost categories:
Revenue-Cust A
Revenue-Cust B
Revenue-Cust C
Material Costs Plant 1
Material Costs Plant 2
Material Costs Plant 3
Labor Costs
Admin Costs
The columns are Jan-Dec, Total, Month, Quarter, and Year to Date. So I basically have a number of these 17-column data blocks for different types of data, e.g. current year$, prior year$, budget$, forecast$,current year stats, prior year stats, budget stats, forecast stats.
I also have a few intermediate tables that do things like calculate price-volume variances and sum the data in different ways.
I'd like to create a final report that pulls the data together so that I can input a date (e.g. Sept 30), comparative columns (e,g, current year actual to budget or current year to prior year), and a time period (month, quarter, year-to-date).
My initial design strategy was to have a separate, but identical worksheet for each data block (Current year worksheet, budget worksheet, etc) but it's been maddeningly difficult to keep the worksheets synced up (we get new customers or lose old ones for example). Linking all of formulas is also very tedious. I'm wondering if I'd be better off stacking several data blocks in the same worksheet.
This must be a common problem, and there's got to be a better than the way I'm going about it! I'd appreciate any feedback that you can give.
David