Open Forum

Expand all | Collapse all

Management Reporter - 12 month Forecast with actual and budget

  • 1.  Management Reporter - 12 month Forecast with actual and budget

    Posted Apr 29, 2019 03:02 PM
    ​I'm trying to build a 12 month forecast in Management Reporter.  As months are complete, actual will be reflected; otherwise, budget will be reflected.  There will also be a total column showing what the Year-End forecasted amounts will be.

    This is what I have so far:
    Column Definition
    I have this formatting going out to Period 12.

    What I can't figure out is my total column.  How should it be setup?  Ultimately, it should sum what is shown on the report.  If I ran it through March, it should sum the Actuals for Jan-Mar and the Budgets for Apr-Dec.

    I appreciate any help you have to offer.

    ------------------------------
    Josh Case
    Great Lakes Forest Products Inc
    Elkhart IN
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: Management Reporter - 12 month Forecast with actual and budget

    GPUG ALL STAR
    Posted Apr 29, 2019 06:42 PM
    This situation is exactly why I love Jet and other Excel based reporting tools!

    I'll let some of the MR experts such as @Derek Krebs hopefully help you out as I have no idea, LOL​

    ------------------------------
    Jo deRuiter, MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    770-906-4504 (Cell)

    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: Management Reporter - 12 month Forecast with actual and budget

    TOP CONTRIBUTOR
    Posted Apr 29, 2019 07:08 PM
    Hi, simply do a range column calculation (e.g. B:Y) and it will only add printing columns.  Please see the attached for more details (could not get website attachment to work so email me and I can send).  I love purpose built report writing tools with re-usable and interchangeable rows/columns/trees, oh my!  :)​

    ------------------------------
    Derek Krebs
    Sr Consultant
    MSX Group
    derek.krebs@msxgroup.com
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: Management Reporter - 12 month Forecast with actual and budget

    SILVER CONTRIBUTOR
    Posted Apr 30, 2019 09:26 AM
    ​Your total column is a calc with the formula = the range of all columns.  For example, B:Y.  Columns with conditional options are not included with any calculations (if the calc uses a range).  So B:Y ignores the unprinted columns in the total, but B+C+D+E... includes the unprinted columns.

    We generate the same report you are asking about and it works great.


    ------------------------------
    Selena Breedlove
    SVP of Finance/Controller
    US Assure Inc
    Jacksonville FL
    ------------------------------

    Conference-GPUG_200x200


  • 5.  RE: Management Reporter - 12 month Forecast with actual and budget

    Posted Apr 30, 2019 09:53 AM
    ​Thanks everyone!  I got it to work.  I also figured out one other hang up I had with the report in wanting to have a conditional Header.  I wanted it to say Actual if actuals were reported and Budget if budgets were reported.  The Header would be each word only once spread over each associated month with a box and shaded.  It took a little bit to figure it out and quite a few videos and tutorials.

    I was able to make it work by having the 1st Header go from B to Base and the 2nd Header go from Base+1 to Y.

    If you all know of a comprehensive "How To" manual for management reporter, I'd love a copy.

    ------------------------------
    Josh Case
    Great Lakes Forest Products Inc
    Elkhart IN
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role