Results 1 to 6 of 6

Thread: Conditional formatting for 2011 forecast

  1. #1
    franztupaz
    Guest franztupaz's Avatar

    Conditional formatting for 2011 forecast



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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)))
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    franztupaz
    Guest franztupaz's Avatar
    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

  4. #4
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  5. #5
    franztupaz
    Guest franztupaz's Avatar
    Hi Sir,

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

    Francis

    Quote Originally Posted by Roger Govier View Post
    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.

  6. #6
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •