Spreadsheet: 326633d1403208197-sales-forecasting-help-with-autofil-using-dates-forecast-6-19-14-glenn-question
Sheet: Forecast

Contract data |  |  |  |  |  |  |  | Project Start (SOP) | Contract Months | Project End (EOP) | Year Fraction (SOP) | Mid Years Fraction | Year Fraction (EOP) | Pieces per unit sales | Quoted Volume | Piece Price [NAME] | LTA terms |  |  |  |  |  |  |  |  |  | Unit price per contract year |  |  |  |  |  |  |  |  |  | Contract Activity |  |  |  |  |  |  |  |  |  |  |  |  | Calendar Years Active |  |  |  |  |  |  |  |  |  |  |  |  | Price by Active Years |  |  |  |  |  |  |  |  |  |  |  |  | Sales per year
# | Customer | Program Code | [NAME] | Component | TS # | Part # | LTA |  |  |  |  |  |  |  |  |  | % Discount applied to costs in each year. |  |  |  |  |  |  |  |  |  | Price per unit in each year of the contract |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13
 |  |  | Ford Focus | Bezel 40 |  |  | 0 | 2002-03-18 00:00:00 | 95 | 2016-06-01 00:00:00 |  |  |  | 1 | 265000 | 0.457
 |  |  |  | Bezel 60 |  |  | 0 | 2002-03-18 00:00:00 |  | 2016-06-01 00:00:00 |  |  |  | 1 | 265000 | 0.486
 |  |  |  | Shield |  |  | 0 | 2002-03-18 00:00:00 |  | 2016-06-01 00:00:00 |  |  |  | 1 | 265000 | 0.383
 |  |  | Dodge Caravan | Grille - LH/RH |  |  | 0,3.5,3 | 2008-07-01 00:00:00 | 95 | 2016-06-01 00:00:00 |  |  |  | 2 | 714000 | 0.981 |  |  |  |  |  |  |  |  |  |  |  | 0.95 | 0.922 | 0.746
 |  |  |  | Bezel |  |  | 0 | 2008-07-01 00:00:00 | 95 | 2016-06-01 00:00:00 |  |  |  | 1 | 520000 | 0.7634
 |  |  | [NAME] | Lens |  |  | 0 | 2010-09-02 00:00:00 |  | 2017-06-01 00:00:00 |  |  |  | 1 | 222000 | 1.21
 |  |  |  |  |  |  | Manual Entries - questions:
 |  |  |  |  |  |  | Q1.  There may be exceptions to the LTA and corresponding Unit price calculations - in these cases, can I make a manual entry into the Unit Price section?  Note entry in cell AC11, AD11, AE11
 |  |  |  |  |  |  | Q2.  If OK for manual entry above, should I manually change AF11-AK11 to match or is a formula better here to accomplish this?
 |  |  |  |  |  |  | Lingering Programs - question
 |  |  |  |  |  |  | * The above shows lingering programs - SOP in 2002 / 2008 and still running! This SOP date of 2002 is my longest running program.  Sorry……
 |  |  |  |  |  |  | * Using row 11 as an example, this LTA is calculating for years 2011-2013 (cells BM11, BN11, BO11) instead of populating  the accurate model year price (.746) in 2010
 |  |  |  |  |  |  | Q3:  So….I don't necessarily need to see 2002 - 2009 data (rather not), but I would like 2010 to start with the right price on these examples, if possible?
 |  |  |  |  |  |  | (f this creates a problem, it is not a big deal as these old programs are the exception - I could do a manual entry)