Sales Forecasting - Help with Autofil using Dates

[Hippo7]

Hello Forum. Can you please help me with the attached? Highlighted box with questions should explain.

Thanks-

[Glenn Kennedy]

Hi there. i think I've got this sorted. There is one and may be two outstanding problems.

1. Regional settings for dates. Where are you? Are you USA style (05-17-2014) or UK style (17-05-2014).

Pricing policy in subsequent years. In your first post on thsi subject Yr 1 was 1.00 contract cost and subsequent years were 0.99 of the previous year's cost. PLEASE tell me that that is standard (I have a horrible feeling that it won't be, however).

[Hippo7]

Hi Glenn. From your note: I am in the US. Unfortunately.....the subsequent year pricing structure can vary, but I don't mind making that a hard entry if need be.

[Glenn Kennedy]

OK, so firstly check out the dates to make sure they work OK for you.

[Hippo7]

OK, I checked it out. Thank you. Here is what I see from your attachment:

1. Calculating Projected End (EOP) from Contract Years: For Contract Years (column G), this entry could be a partial year (ie. 2.25), but the date seems to round to the nearest year. Could this work for partial year?
2. Year Fraction EOP: Seems to work fine
3. Sales Per Year calculations: Being a novice, I don't understand the formulas here, but it appears that your #'s are too high. If I calculate the maximum possible sales as a reference point, the totals would be:
a. Program A - Overhead Lens: .702 x 2 x 130,000 = $182,520
b. Program A - Lamp Lens: .661 x 1 x 600,000 = $396,600
c. Program B - Overhead Lens: 1.74 x 1 x 114,800 = $199,752
d. Program B - Lamp Lens: .675 x 1 x 600,000 = $405,000

You can see that your totals show much higher.

Thanks-

[Glenn Kennedy]

Oķ. Leave it with me. It's time for feet up and a beer here!

[Glenn Kennedy]

I forgot to multiply one of the components of the total of the full year by the year fraction. I'll fix it in morning. The beer beckons...

[Glenn Kennedy]

Patience... We'll get there. Try this version.

[Hippo7]

Looks good - that beer did not affect your intellect one bit! Thank you again for all your help. Questions / comments:
1. Partial Contract Year: Can we make the formula work with partial Contract Year (ie. 2.25, ect..)?
2. Sales Calculations: Curious - is cell Z11 not calculating 100,000 exactly due to the formula calculating days verses and exact full year?
3. Further improvement possibility: I'm scared to ask this but....could rows N-Y automatically populate considering the entry in column E (LTA Terms)? LTA is the yearly productivity reductions effective 1 year from the SOP; typical LTA is 1% for 3 years, but could be something else (3% x 3 years, 2% x 2 years, etc..).

Thanks!

[Glenn Kennedy]

Once again, it might be tomorrow pm before I get back. Leaving now, out this evening & a nice audit to suffer in the morning...

[Glenn Kennedy]

A bit of time...

1. Partial Contract Year: Can we make the formula work with partial Contract Year (ie. 2.25, ect..)?
Done. Very easy, too. Just modified/simplified column H & require contract length to be entered in months.

2. Sales Calculations: Curious - is cell Z11 not calculating 100,000 exactly due to the formula calculating days verses and exact full year?
I had copied the equation off this from the www. However, you're right. It is counter-intuitive. there may be a wee error in leap years, but in other years, sorted.



3. Further improvement possibility: I'm scared to ask this but....could rows N-Y automatically populate considering the entry in column E (LTA Terms)? LTA is the yearly productivity reductions effective 1 year from the SOP; typical LTA is 1% for 3 years, but could be something else (3% x 3 years, 2% x 2 years, etc..).
I need some help from you here. At the moment, the first year is set at the contract price. Thereafter each year is 0.99 of the previous year (in line with your first example). Can you explain a) the 0.99 multiplier, b) the LTA and c) whether one or other or potentially both could apply. Take your time doing this, as I don't want to spend time on this and get it wrong!!

in the meantime, here's the latest iteration.

[Hippo7]

Thanks. From your questions:
LTA (Long Term Agreement) is the agreed upon yearly productivity reduction agreement negotiated with the customer prior to getting a new business award in US Automotive. There will be some exceptions, but I will give you a couple examples:
1. LTA = 0,1,1,1: This means 0% sell price reduction for 1st year, than 1% sell price reduction each year for 3 years following; therefore the .99 multiplier
2. LTA = 0,3,3,3: This means 0% sell price reduction for 1st year, than 3% sell price reduction each year for 3 years following; Multiplier would be .97 in this case

I hope this answers your question. THE 'LTA Term' column cell could be inputted differently than how I currently have it listed in just one column as '1% x 3' in a general format. I like less columns, so ideally, some magic formula can populate pricing from ONE 'LTA Term' column that reads 0,1,1,1 or 0,3,3,3 for the respective LTA, but I suppose you could break out the LTA Term into multiple columns if need be (o, 1, 1, 1 each in it's own column?).

I defer to you as I am the knucklehead here and you are the excel genius.

Thanks-

JTF
JTF

[Glenn Kennedy]

Hi, I haven't forgotten about you. Far from it. I've just been taking a bit of a break from LTAs .

So, maybe one more Q before I tear away at it again. If the LTA is 0,1,1,1 and the contract is for longer than 4 years, is it safe for me to assume that for subsequent years the multiplier is zero (ie no further discounts) - or is there a level of complication here that I've missed?

[Hippo7]

Good Question. Yes, after the LTA is complete, the multiplier for the subsequent years would be zero.

Thanks!

[Hippo7]

Hi Glenn. Have I scared you away with nightmares about LTA reductions?!

[Glenn Kennedy]

Not at all. However, I was in London for a few days. Back now, I'll try to get to it tomorrow.

[Glenn Kennedy]

Hi. nearly there. However, what is the MAXIMUM number of years that the LTA discount terms can apply (including the first year)?

The reason I ask is that I can only come up with a truly horrible equation, which you'll see shortly. I've got it working out to a mximum of 6 years. Is that enough to cover all eventualities?

[Glenn Kennedy]

Have a CAREFUL look at htis. i think it's what you wanted. i had to completely re-do the LTA calculations as (for some strange reason) my sums didn't work with decimal discounts. they do now. I suggest you play with some easy dat ranges and cost ranges to see if everything matches. I think it does, but - you can never be too sure.

[Hippo7]

Took it for a test run. It seemed to function OK until I changed the dates. If I change the SOP date in F7 to 7/1/14: the LTA Terms still populate, but the Annual Pricing and Sales Per Year no longer updates according to the LTA. Also:
1. Maximum # of yrs that LTA applies is 5 (question from your previous note that I didn't see)
2. Forecast Document / # of columns / calculations: When using this eventual forecast document, I would likely only utilize / show a 5 year forecast at one time (so as of now, I would show 2014-2018). Not sure if that helps any, if not, I don't mind hiding columns that I am not using.

I will be able to respond better, now that I know how to get to next page in the thread! Thanks.

[Glenn Kennedy]

On way to pub. Will check it out ASAP.

[Glenn Kennedy]

Hey ho. Busy busy busy. Not enough time. Anyhow. Those problems are now fixed. Road test this version TO DEATH...

Not on a plane for a week ahead - so response will be faster.

[Hippo7]

OK...from my message to you a moment ago - I have road tested and I have a couple questions regarding forecast exceptions. See attached - questions are listed on the document.

Thanks!

[Glenn Kennedy]

OK. It's evening here & the pizza is sizzling in the oven. I don't think that there's anything serious here. Leave it with me... At least I'm not on a plane again until Wednesday...

[Glenn Kennedy]

Been to London and back (again). There again next week (so I thought I'd better shunt this on a bit...).

I nearly had a blonde moment, closing Excel without saving. Disaster averted...

[Hippo7]

Hi Glenn. Thanks. Responded to your comments and added some questions. See attached.

[Hippo7]

Thinking about this....maybe we eliminate the "Contract Months" column and I just manually enter the EOP date?

[Glenn Kennedy]

Here you are. Good luck

[Hippo7]

Thank you! I am running out of questions....but I do have 1 more, hopefully easy question. On an handful of rows, when comparing this latest fix to the previous, the calculations in 'Unit Price Per Contract Year' column are slightly different causing the 'Sales Per Year' to be slightly different. It is not much, but I am curious. I have cut and paste the comparative items in two attachments. Outside of the 1st one which is a 2012 SOP, all the items in question have a 2013 SOP. Please advise.

[Glenn Kennedy]

Guess what? I'm in London again with limited IT until tomorrow.

[Glenn Kennedy]

SORRY. I thought that I had already replied to this.

The reason is because of my interpretation of an answer to a Q back around Post 12. There are two ways to apply the discount.

1. Unit Price is $4.00. 10% discount next year = $3.60. 10% discount the following year is 10% of $3.60, i.e. 36c = 3.24.
This is the way I had (incorrectly, I think) been doing this at the start.

2. Unit Price is $4.00. 10% discount next year = $3.60. 10% discount the following year is 10% of $4.00, i.e. 40c = 3.20.
This is the way I am now (correctly, I think) doing this.

[Hippo7]

Hi Glenn. From your note - your item 1 would actually be the correct interpretation. The discount is always applied to the prior year price. This means we need to correct the formulas?

Thanks-

[Glenn Kennedy]

Yep
At the cinema now. I'll correct it when I get home.

[Glenn Kennedy]

Have another go at this...

[Hippo7]

So far so good! I will road test and let you know in the next couple days.