Spreadsheet: 327754d1403679754-sales-forecasting-help-with-autofil-using-dates-forecast-6-23-14-v99
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 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21
 |  |  | 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 | 95 | 2016-06-01 00:00:00 |  |  |  | 1 | 265000 | 0.486
 |  |  |  | Shield |  |  | 0 | 2002-03-18 00:00:00 | 95 | 2016-06-01 00:00:00 |  |  |  | 1 | 265000 | 0.383
 |  |  | Dodge Caravan | Grille - LH/RH |  |  | 0,3.5,3,3 | 2008-07-01 00:00:00 | 95 | 2016-06-01 00:00:00 |  |  |  | 2 | 714000 | 0.981
 |  |  |  | Bezel |  |  | 0 | 2008-07-01 00:00:00 | 95 | 2016-06-01 00:00:00 |  |  |  | 1 | 520000 | 0.7634
 |  |  | [NAME] | Lens |  |  | 0,1,1,1 | 2010-09-02 00:00:00 |  | 2017-06-01 00:00:00 |  |  |  | 1 | 222000 | 1
 |  |  |  |  |  |  | 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)
 |  |  | Q1.  Yes, but it would be easier just to change the figures in H.  That way the formulae don't mess up.
 |  |  | Q2.  Makes no difference.  But it is still better to make the adjustment in H...  I have reset the equations.  No mucking around!! OK??
 |  |  | Lingering programmes.  Bullet 1.  Sorted... |  | But... Re-road test extensively. |  | I have added a lot of columns and this may have introduced the odd error.
 |  |  | Lingering programmes.  Bullet 2.  That's because you overwrote the equations in AC11 To AE 11.  Again, the best wasy is to accurately re-calculate the discount & change the entry at H.  No more mucking around!! OK??
 |  |  | Q3.  Just hide everything you don't need to see.  Don't delete things, 'cos that will cause a major, major F-up.
 |  |  | One complaint.  You over-wrote my equations in column K.  Don't.  Put in the start date and the contract months.  You may wind up with funny results for contracts that start on January 1st if you manually enter the EOP date.
 |  |  | I have not (YET...) put the equations back.  Do you really have contrat that end on dates other than the month end??
 |  |  | For Q2:  You say it "makes no difference" but later "no mucking around";  I understand the importance of keeping the formula, but could a manual override in the 'Unit Price per Contract Year' be the EXCEPTION here on some
 |  |  | rows (Puchase Order pricing from the customer does not always match exactly the LTA reduction amount, and I like to keep the LTA column as the original agreement and the pricing column as the PO matching amount)
 |  |  | The problem with mucking with the equations is that you may accidentally copy the "exception" cells into new contracts.  If you like,put another (!!) column in somewhere with OLD LTA terms, but keep H for the LTA being applied.
 |  |  | Complaint:  Sorry for that, these manual override examples would be exceptions.  Yes, contract end on dates other than months end.  Similar question to above: as the EXCEPTION, could I do a manual overridde to column K?
 |  |  | Forget about column J and stick with manual entry in [NAME]'ve convinced me!!
 |  |  | New Question:  column CA to CB - skips from 2002 to 2011? |  |  | Decorative error.  Fixed.
 |  |  | I will road test after your feedback on these items.  Thanks! |  |  | Great !