Home
ExcelCalcs
Welcome, Guest
Please Login or Register.    Lost Password?
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.
Go to bottom Favoured: 0
TOPIC: 2011 Forecast formula configuration
#2465
franztupaz (User)
Tadpole (Junior Boarder)
Posts: 4
graphgraph
User Offline Click here to see the profile of this user
2011 Forecast formula configuration 6 Years, 4 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.xls
File Size: 101888
 
Logged Logged  
  The administrator has disabled public write access.
#2466
JohnDoyle[Admin] (Admin)
Mechanical Engineer.
Moderator
Posts: 1738
graph
User Online Now Click here to see the profile of this user
Gender: Male MoreVision Location: LEEDS, WEST YORKSHIRE, UK Birthdate: 1965-02-24
Re:2011 Forecast formula configuration 6 Years, 4 Months ago Karma: 492  
I think all you need to use is IF() and AND() functions.

IF(AND(S2010<AS2010,AS2010>200000),AS2010,IF(AND(S2010<AS2010,AS2010<200000),200000,if(S2110>AS2010,S2010*G))

where AS2010 = Average Sales in 2010
and S2010= Sales 2010
 
Logged Logged  
 
  The administrator has disabled public write access.
#2467
franztupaz (User)
Tadpole (Junior Boarder)
Posts: 4
graphgraph
User Offline Click here to see the profile of this user
Re:2011 Forecast formula configuration 6 Years, 4 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 Logged  
  The administrator has disabled public write access.
#2470
franztupaz (User)
Tadpole (Junior Boarder)
Posts: 4
graphgraph
User Offline Click here to see the profile of this user
Re:2011 Forecast formula configuration 6 Years, 4 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.xls
File Size: 48128
 
Logged Logged  
  The administrator has disabled public write access.
#2471
franztupaz (User)
Tadpole (Junior Boarder)
Posts: 4
graphgraph
User Offline Click here to see the profile of this user
Re:2011 Forecast formula configuration 6 Years, 4 Months ago Karma: 0  
thanks for helping me figure things out, even if it still is unresolved. appreciate the effort
 
Logged Logged  
  The administrator has disabled public write access.
#2472
JohnDoyle[Admin] (Admin)
Mechanical Engineer.
Moderator
Posts: 1738
graph
User Online Now Click here to see the profile of this user
Gender: Male MoreVision Location: LEEDS, WEST YORKSHIRE, UK Birthdate: 1965-02-24
Re:2011 Forecast formula configuration 6 Years, 4 Months ago Karma: 492  
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 Logged  
 
Last Edit: 2011/04/04 07:58 By JohnDoyle[Admin].
 
  The administrator has disabled public write access.
Go to top
We have 3 guests and 38 members online
Contact Us
post/emailEmail (preferred method of contact)
telephone US +1 617 5008224
telephone EU +44 113 8152220
Our Feeds
Repository RSS. Forum RSS. User Comment RSS. News RSS.