Conditional formatting for 2011 forecast

F

franztupaz

Guest
Dear Everyone,

I am a newbie in the excel and basically has close to "0" knowledge except for basic functions, but have seen the benefit of having a working sheets/templates which cuts work time in half.

If I may kindly request for your assistance as I am working on a 2011 forecast and I am stuck and cannot move on, please see below parameters and likewise the attached excel sheet as an example.

Conditions:
Base Number - by branch 2010 monthly sales
Growth Factors - grow 2010 Monthly Sales by branch
"GDP"=+4% growth
"Same"=+6% growth
"New"=+10% growth

If monthly 2010 sales is greater than 200,000, then just add growth factors as 2011 monthly forecast
If monthly forecast is less than 200,000 base number should be average 2010 sales + growth factors
If monthly forecast is greater than 150% of average 2010 sales, then forecast should take average 2010 sales as its forecast + growth factors.
Round final numbers

Please refer to the attached under "Tarlac" row, but please note that this was computed manually and without any "nested formula".

Hope someone helps me.

Thank you.

best regards,

Francis
 

Attachments

  • sample no 2.xls
    52 KB · Views: 19

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
Hi Francis

I think the attached workbook does what you want.
In cells X1, Y1, Z1 I entered the values 104%, 106% and 110%.
I then name these cells using Insert>Name>Define as GDP, Same and New

I also set a name called test as 200000 (only to make it easier if you change your base value, you only have to alter this value, not all your formulae.

The formula in cell AL32 then becomes

Code:
=IF(D37>Test*1.5,$R37*INDIRECT(W37),IF(D37>Test,D37*INDIRECT(W37),$R37*INDIRECT(W37)))
 
F

franztupaz

Guest
Hi Sir Roger,

I don't understand your reply am very sorry, this is the closest formula I have but it still will not consider the last parameter which is "if 2011 Monthly forecast is greater than the 2010 average sales by 150%, then 2011 monthly forecast will take 2010 average sales as base number and add growth factors whether 4,6,10%.

=ROUND(IF(IF(W6="GDP",(1.04*D6),IF(W6="Same",(1.06*D6),IF(W6="New",1.1*D6)))<200000,R6,IF(W6="GDP",(1.04*D6),IF(W6="Same",(1.06*D6),IF(W6="New",1.1*D6)))),0)

Thanks!

Francis
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
Hi
I misread the 150% part. I had read is at 150% of 200,000, not 150% of Average sales.

In which case, try
Code:
=IF(ISNUMBER($R37),MIN(IF(D37>Test,D37*INDIRECT(W37),$R37*INDIRECT(W37)),1.5*$R37*INDIRECT(W37)),"")

The first part is testing whether there is an average value in column R, if there ism, then make the calculation, otherwise leave blank.

IF(D37>Test,D37*INDIRECT(W37)
Test is 200000 so if D37 >200000 multiply D37 by Indirect(W37)
W37 will contain either GDP, Same or New so Indirect (W37) will give the values of 104%, 106% or 110% as has been set up as the named ranges containing those values.

If the value in D37< Test than it takes the Average sales in R37 and multiplies by Indirect(W37)

Whatever result that comes up with is then compared with the value which is 150% time the Average value uplifted by the relevant percentage, and uses the lower of the 2.
 

Attachments

  • sample no 2.xls
    79.5 KB · Views: 27
F

franztupaz

Guest
Hi Sir,

I still get an error of #name?, which I dont know why and what it means. Thanks.

Francis

Hi
I misread the 150% part. I had read is at 150% of 200,000, not 150% of Average sales.

In which case, try
Code:
=IF(ISNUMBER($R37),MIN(IF(D37>Test,D37*INDIRECT(W37),$R37*INDIRECT(W37)),1.5*$R37*INDIRECT(W37)),"")
The first part is testing whether there is an average value in column R, if there ism, then make the calculation, otherwise leave blank.

IF(D37>Test,D37*INDIRECT(W37)
Test is 200000 so if D37 >200000 multiply D37 by Indirect(W37)
W37 will contain either GDP, Same or New so Indirect (W37) will give the values of 104%, 106% or 110% as has been set up as the named ranges containing those values.

If the value in D37< Test than it takes the Average sales in R37 and multiplies by Indirect(W37)

Whatever result that comes up with is then compared with the value which is 150% time the Average value uplifted by the relevant percentage, and uses the lower of the 2.
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
Hi

Just download the file I attached.
The named values have been created in there.

To create names
Insert>Name>Define
Name GDP
Refers to =X1

Cell X 1 contains the value 104%

Similar for each of the other 3 named ranges.
 
Top