Excel 2007 : Adding Multiplying Modifiers to Pivot Tables

Hello,

I'm hoping someone has a an idea on what I can do here because I've spent a few days trying to make this work to no avail.

I have a spreadsheet that is pulling from source data about products my firm produces, forecast demand, and on hand inventory. The source data is divided into weekly numbers on one sheet and monthly on another (irrelevant because if I can figure out how to make this work for weekly then monthly will be that much simplier). The items we produce are broken down to the individual item level but also one category level higer - ie. item A, B, and C are all part of segement group one.

The pivot table has the segments on the left, followed by the individual items. We show demand, production, on hand inventory, and days on hand and then also sum those values up all within the rows. The columns are used for dates, so weekly/monthly depending on the spreadsheet that is being viewed.

Above the pivots I have standard excel tables that are pulling the data from the pivots and representing it in a different format but lined up by date by column. What I am trying to do is build in scenario planning into the worksheet. I want to be able to add columns for modifiers in the above table AND the pivot table. Meaning, a drop down that I can select to reflect 100% accuracy, 95%, 90%, 110% etc. I thought I could just add columns, insert the values and rewrite the forumla of the corresponding cell with a "*A2" or whichever cell the modifier would be. Not working out, I have added a column to my pivot table called modifier but I'm being told that I can't edit any data in the pivot table. I can't add columns to the sheet at all because the pivot is imbedded.

I'm starting to think the pivot has to be on a different sheet than the tables but my objective was to build the scenario editting into the existing sheet, my co-workers rely heavily on seeing accurate data quickly without having to flip through pages of a workbook.

Any ideas? I'm not the best with pivots but I pick up quickly; I'm familiar with macros, basic/intermediate VBA etc.

Any help is appreciated.

As far as I am aware, you can't add columns to a pivot table, you can only add them to the source data to be included in the pivot.

As for the remainder of your problem, an example worksheet with expected output would be helpful.