Home
ExcelCalcs
2011 Forecast formula configuration (1 viewing) (1) Guest
If none of the forum headings seem to fit your question or comment then post it here.
 Favoured: 0
TOPIC: 2011 Forecast formula configuration
#2465
(User)
Posts: 4

 2011 Forecast formula configuration 7 Years, 6 Months ago Karma: 0 May I please request for your assistance in figuring out the proper equation? Logic:[/b] If 2010 Sales is lower than 2010 ave.sales, 2011 forecast is equal to ave.sales provided that its greater than 200,000 If 2010 Sales is lower than 2010 ave.sales, 2011 forecast is equal to ave.sales provided that its greater than 200,000, if the average sales is lower than 200,000 by default it will take the minimum forecast of 200,000 If 2010 Sales is greater than 2010 ave.sales, 2011 forecast is equal to 2010 sales + growth factors Growth Factors: 2010 + 4% (GDP Growth) 2010 + 3% (Marketing Program Growth - "Same" 2010 + 10% (New Program + GDP Growth - "New" Total Probable forecast growth is 10% Hoping to hear from you and thank you very much. Best regards, Francis File Attachment: File Name: sample_no_2.xlsFile Size: 101888

Logged
The administrator has disabled public write access.
#2466
Mechanical Engineer.
Moderator
Posts: 1773

 Re:2011 Forecast formula configuration 7 Years, 6 Months ago Karma: 494 I think all you need to use is IF() and AND() functions. IF(AND(S2010200000),AS2010,IF(AND(S2010AS2010,S2010*G)) where AS2010 = Average Sales in 2010 and S2010= Sales 2010

Logged

The administrator has disabled public write access.
#2467
(User)
Posts: 4

 Re:2011 Forecast formula configuration 7 Years, 6 Months ago Karma: 0 Hi Sir, To clarify, please see below looking forward to your help sir. 1st Condition Jan 2011 forecast = Jan 2010 sales if ("GDP" will equal to +4% growth) if ("Same" will equal to +6% growth) if ("New" will equal to 10% growth). 2nd Condition Jan 2011 forecast is greater than average sales then Jan 2011 forecast is = (equal to) Jan 2011+growth 3rd Condition Jan 2011 forecast is lower than 200,000 then it will take or be equal to average sales of 2010 (Jan-December Sales) Note: Minimum monthly forecast will always be the average 2010 sales as default forecast+growth factors But is the actual monthly forecast is greater than the average 2010 sales then monthly forecast will equal to 2010 monthly sales+growth factors. Growth Factors: 2010 + 4% (GDP Growth) 2010 + 6% (Marketing Program Growth - "Same" 2010 + 10% (New Program + GDP Growth - "New" Hope you can help me turn the logic into a working equation or formula, thank you very much. Sincerely, Francis

Logged
The administrator has disabled public write access.
#2470
(User)
Posts: 4

 Re:2011 Forecast formula configuration 7 Years, 6 Months ago Karma: 0 HI sir, AL6=Grow D6 by 4% if W6 is "GDP", 6% if W6 is "Same" and 10% if W6 is "New",IF (Grow D6 by 4% if W6 is "GDP", 6% if W6 is "Same" and 10% if W6 is "New" is less then 200,000 then use R6 as 2011 forecast, then round numbers. This means all Jan 2010 actual sales(D6) will either grow by 4%, or 6% and max of 10% (W6) and minimum forecast will be the average 2010 sales (R6). I have attached the worksheet I am trying to use as reference, hope someone can help me. Thank you. Regards, Francis File Attachment: File Name: sample_no_2-7bbaa95a17a67495e2ccf054705fa772.xlsFile Size: 48128

Logged
The administrator has disabled public write access.
#2471
(User)
Posts: 4

 Re:2011 Forecast formula configuration 7 Years, 6 Months ago Karma: 0 thanks for helping me figure things out, even if it still is unresolved. appreciate the effort

Logged
The administrator has disabled public write access.
#2472
Mechanical Engineer.
Moderator
Posts: 1773

Re:2011 Forecast formula configuration 7 Years, 6 Months ago Karma: 494
When worksheet functions become very complicates it is worth using a custom VBA function. Her is a link to help you create your first function.

Here is a start on a custom function but I'm afraid I cannot understand all your logic. Hopefully you can complete it yourself

 Code: ```Function Forecast(LastYear As Double, AverageLastYear As Double) As Double If LastYear < AverageLastYear Then     If AverageLastYear > 200000 Then         Forecast = AverageLastYear     ElseIf AverageLastYear < 200000 Then         Forecast = 200000     End If ElseIf LastYear > AverageLastYear Then ' I'm afraid I don't understand your logic for applying the rules ' You can modify the code below using more IF statements     Forecast = LastYear End If End Function```

Logged

Last Edit: 2011/04/04 07:58 By JohnDoyle[Admin].

The administrator has disabled public write access.
We have 3 guests and 36 members online