Proper Actual versus Projection Data Layout

Building a report is the work of a day, or maybe a week.

Laying out a table is the work of a day, max. It might be spread across a while because you work on it and come back, but in the end, it's like 8 hours of work.

But filling in your table? That's the part that happens over thousands of hours spread across twenty years.

So in my view, laying out the data in a way that's robust and easy to use is the critical part. If your data is set up well, then slapping a new report on top of it is easy. Adding new data is pretty straightforward and intuitive. But if it is not set up well... well.

So I think you'd be well served to follow the classic rules of building tables. This is more like an SQL or Access question, but it applies a lot to Excel as well.

I think generally you're on the right track. Rather than trying to anticipate what everyone will need to do, just make your table good and useful.

Broadly speaking, a table has:
1) Headers across the first row, naming what's in each column
2) One Object / entity per row
3) One piece of data in each cell, that is a property of that Object

The critical thing is deciding what each "Object" is. Is it the part #?
Usually each Object should have a unique Key that is data value that is one-and-only-one, where there is one Key per Object, and it never has another Key, and no other Object has the same Key. If you have a data value per Object that is already like that, it's usually a good bet to use that.

Every column should have the same type of data. Whether it's a date/time, an integer, a text description, a Boolean, whatever; everything in the column should be the same type of data for each Object.

Generally speaking, the table should record only data records; try to avoid sticking "helper columns" in that transform and process data. If you want something really robust, your data should flow like:
Records Table => Processing Table => Report
(That's where having unique one-and-only-one Keys on your data records helps; it makes lookups really easy to implement, because they're a chicken-and-an-egg relationship; lookup functions were written assuming your data has unique Keys, so pulling data into your Processing Table becomes a trivially easy activity).

Once set up, the "design" of the Records Table should be frozen; adding new record rows is something anybody can do, removing old records is something to double-check, and adding/removing columns requires a boring 30-minute review meeting. That kind of logic.

Then people can pull the Records Table (using an External Data Link, say?) to set up their own Processing Tables to create whatever the hell kind of report they want.

I am going to suggest starting by making a small sample table, using Ben's instructions about setting up records. The table should have just enough data that you could:
1) Identify a part that failed to launch and one that didn't,
2) A part that went obsolete quickly and one that didn't
3) A customer that over-estimates how much they will buy and one that didn't
Then use that data to populate a basic report that answers the three questions from post #1, if not by way of formulas/pivot tables then manually so that we can at get an idea of how you envision the final product will look. If you hit a wall then upload the file, as opposed to just a screen shot.
To upload a file click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.