How can I calculate some averages about product life cycles?
What I need to know is how quickly a part ramps, peaks, declines, service (how long it may stick around at a low flat level of sales), and when it typically goes obsolete. I am looking for a way to show that with color (conditional formatting) and by a "dashboard" summary type page. But I'm at a loss as to how I should go about doing this.
What I am currently thinking - peak is the MAX revenue (or within 20% of that), ramp is anything occurring before that peak and that is less than 80% of peak, and decline is anything after the peak period and less than 80% of peak. Service is a low level of flat sales. Obsolete is when sales completely die. But I have no idea how to put this all together.
Please see my attached sample file.
View attachment Part Data.xlsx
My original data is all in an excel table and pulled together via a pivot table. I just couldn't figure out how to take data setup as "Year (of sales)\Part\Launch Year (First Year of Sales)\Revenue" and transform that into a PT that listed the number of the year since a part's launch year (1,2,3,4,5...), rather than a huge PT with a column for each year with a row for each part (my data stretches back over 15 years). The sample data sheet is only a few parts, but I have thousands of parts to analyze. If you have any ideas on how to setup a PT to resemble my attached data that would also be appreciated.
Thanks!
What I need to know is how quickly a part ramps, peaks, declines, service (how long it may stick around at a low flat level of sales), and when it typically goes obsolete. I am looking for a way to show that with color (conditional formatting) and by a "dashboard" summary type page. But I'm at a loss as to how I should go about doing this.
What I am currently thinking - peak is the MAX revenue (or within 20% of that), ramp is anything occurring before that peak and that is less than 80% of peak, and decline is anything after the peak period and less than 80% of peak. Service is a low level of flat sales. Obsolete is when sales completely die. But I have no idea how to put this all together.
Please see my attached sample file.
View attachment Part Data.xlsx
My original data is all in an excel table and pulled together via a pivot table. I just couldn't figure out how to take data setup as "Year (of sales)\Part\Launch Year (First Year of Sales)\Revenue" and transform that into a PT that listed the number of the year since a part's launch year (1,2,3,4,5...), rather than a huge PT with a column for each year with a row for each part (my data stretches back over 15 years). The sample data sheet is only a few parts, but I have thousands of parts to analyze. If you have any ideas on how to setup a PT to resemble my attached data that would also be appreciated.
Thanks!
Last edited: