9.5.1 Data Spreading
Posted: Fri Sep 17, 2010 11:55 am
I'm having a problem with data spreading that I hope someone out there can help with.
I have two products that have the same rules being applied, with revenue being calculated as price (Average Selling Price or ASP) * units. At a consolidated level the ASP is a calculation of revenue/units. When I try to use this year's ASPs (at a consolidated level) as a base for next year forecast, using Relative Proportional Spread, it works for one product but with the other I get a 'Spreading failed due to conflict with rules' message. There are no rules are specifically for either product. However, for this product the consolidations of quarter, half year and full year have revenue values and ASPs. At the N level, I have revenue but no ASPs. If I Trace Calculation on revenue at the N level it shows me that revenue is calculated as ASP * units, showing the ASP as zero but somehow calculating a value for revenue.
If I subsequently put a value in for Nov-10 ASP, all the figures that had been there for revenue in 2011 disappear and Nov-10 is the only month with revenue.
I attach some screenshots highlighting the problem and the rules being applied.
This is on a new project on 9.5, Excel 2003.
I have two products that have the same rules being applied, with revenue being calculated as price (Average Selling Price or ASP) * units. At a consolidated level the ASP is a calculation of revenue/units. When I try to use this year's ASPs (at a consolidated level) as a base for next year forecast, using Relative Proportional Spread, it works for one product but with the other I get a 'Spreading failed due to conflict with rules' message. There are no rules are specifically for either product. However, for this product the consolidations of quarter, half year and full year have revenue values and ASPs. At the N level, I have revenue but no ASPs. If I Trace Calculation on revenue at the N level it shows me that revenue is calculated as ASP * units, showing the ASP as zero but somehow calculating a value for revenue.
If I subsequently put a value in for Nov-10 ASP, all the figures that had been there for revenue in 2011 disappear and Nov-10 is the only month with revenue.
I attach some screenshots highlighting the problem and the rules being applied.
This is on a new project on 9.5, Excel 2003.