Help with complex worksheet design

[DavidH56]

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

 

[Justin Hardaway]

have you tried building a pivot table??

If its just losing or gaining customers that creates a need for a new place to enter data you can use one workbook with a worksheet for each customer, one for each plant, one for labor, and one for admin. Then add a date as a tracked field for each data entry. Then from an empty worksheet just organize the relavent data as you want for one report or get a total summary as detailed or general as you want.

 

[DavidH56]

I thought about a pivot table but that won't really work for this application because the data needs to be balanced and verified, which is why I have the separate sections. Also the data formats are all slightly different.