Open Forum

Managment reporter formula

  • 1.  Managment reporter formula

    BRONZE CONTRIBUTOR
    Posted 06-11-2018 05:21 PM
    I am trying to create a column formula that takes the % of Sales from Budget Column E - the % of Sales from Actual Column C times the BASEROW  for Column B but am not sure how to specify Column B BASEROW

    Columns are
     COL A                 COL B           COL C               COL D         COL E                  COL F
    DESCRIPTION -  ACTUAL    -   % OF SALES -  BUDGET  - % OF SALES  -      RATE VARIANCE

    FORUMULA IN COLUMN F is -    (e-c)*Baserow     (but not sure how or If I can specify the formula to use the BASEROW for Column B)

    Can someone provided any suggestions.

    Thank you

    Bill


    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------


  • 2.  RE: Managment reporter formula

    BRONZE CONTRIBUTOR
    Posted 06-12-2018 01:14 AM
    ​Hi Bill, try this formula assuming the base row is row code 500:

    (E-C)*B.500

    B.500 is the column_letter (B).row_code (500)

    Feel free to email if any other questions and I could also provide some further TOT and CAL examples and documentation.

    ------------------------------
    Derek Krebs
    Derek.Krebs@msxgroup.com
    Sr Consultant
    MSX Group
    Carlsbad CA
    ------------------------------



  • 3.  RE: Managment reporter formula

    BRONZE CONTRIBUTOR
    Posted 06-12-2018 03:17 PM
    ​I tried this then remembered the base row changes throughout the Row Format using the CBR command, so there is not just one row I can use in the formula
    (E-C)*B.RowNumber

    Any other suggestions?

    Thanks
    Bill

    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------



  • 4.  RE: Managment reporter formula

    BRONZE CONTRIBUTOR
    Posted 06-13-2018 12:56 AM
    ​Hi, you can email me some screen shots of the row and column and Excel output of what exact figures you are trying to get.  I'm thinking to simplify formula that I see most have for "variance rate" is Var$/Budget.  Else may have to resort to lots of row calculations and change calc priority to columns first then rows 2nd.

    ------------------------------
    Derek Krebs
    Sr Consultant
    Derek.Krebs@msxgroup.com
    MSX Group
    Carlsbad CA
    ------------------------------



  • 5.  RE: Managment reporter formula

    BRONZE CONTRIBUTOR
    Posted 06-19-2018 03:54 PM

    There are a few options you have.  One to start for visibility is to have a calc column and the formulas simply is BASEROW all by itself.  That way you can see and confirm the changing rows and values for your formula.  You may make this BASEROW calc column no print or use similar in another calc formula but be sure to use parenthesis to clearly define order and intent.  The formula you inquired about, as I read and see it, is a very small number so you are not seeing the value unless you add a format/currency override (like 0.00000000) so look to use variations of the formula as needed (like multiple by 100 or most show Var% =Var$/Bud$.

    You have my email, and other DSLUG users as well. so open door policy for any FRx, Forecaster and Mgt Reporter reporting & budgeting questions (100% of our focus).

    There is a MgtRpt webinar I am doing tomorrow on Mgt Rpt for DSLUG and ALL: Management Reporter – 7 Cool Report Examples for Income Statements ( Wednesday, June 20, 2018 (11:00 AM) to Wednesday, June 20, 2018 (12:00 PM) (Eastern Time)

     



    ------------------------------
    Derek Krebs
    Derek.Krebs@msxgroup.com
    Sr Consultant
    MSX Group
    Carlsbad CA
    ------------------------------