web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Management Reporter Ro...
Microsoft Dynamics GP (Archived)

Management Reporter Rolling Cash Forecast Problem

(0) ShareShare
ReportReport
Posted on by dnodo

We are trying to create a rolling cash forecast using the YTD Ending Cash as a starting point.   When we try to bring Ending Cash from the last Actual column to Beginning Cash in the first and subsequent Budget Columns the Beginning Cash row returns no results.  If I change C to reference B.60 I can get the Ending Cash for the first budget period but the next column will not work.   MR doesn't let me use CAL totals in the next column.   Is there any way around this?

10 NP  Cash /BB                              Account Modifier=[/BB]

20 Beginning Cash Balance CAL B=B.10,C=B.50,D=C.50

30 Total Sources              TOT

40 Total Applications              TOT

50 Ending Cash Balance CAL @20+@30+@40

60 NP  Ending Cash Balance/Y   Account Modifier=[/Y]

Has anyone been able to create a rolling forecast cash budget off of actuals?

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    It sounds like the problem may be the calculation priority. Basically, MR calculates either the rows or the columns first. Your row calculation is likely referencing a column calculation that hasn't happened yet. You could try toggling the "Calculation priority" in the Report Definition under the Settings tab to "Perform column calculation first and then row". Note that this may throw off any column calculations that reference row calculations so hopefully you don't have any of these.

  • Kimberley Stevens Profile Picture
    Kimberley Stevens 720 on at

    Were you able to get your rolling cash forecast to work?  If so, how did you accomplish it?

    I was able to get it to work by adding my actual beginning balance plus the net activity since then in my advanced cell placement formula rather than referencing my ending cash balance.  However, the result is a HUGE formula as I am consolidating 5 companies that all have budget and actual balances for a 12-month period.  I've actually only done it for six months so far, and I'm kind of waiting for MR to tell me that I can't add any more arguments to the CAL formula.

    I also got it to work by adding extra columns to calculate the beginning balance for each period, and referencing them instead.  However, this creates dozens of extra columns that I can't fully hide because I have to use the P<=B & P>B print controls in order to calculate whether to add in the net activity from the budget or actual ledger.  Sadly, I can't add another print control on top of an existing print control, and the row formatting is overriding the column formatting so I can't hide it by making the font white.

    I think the alternative is to link to a Forecasted Balance Sheet using a Financial Dimensions + Worksheet dimension in your row format.  I think I might try that and see if I can get something a bit more manageable that way.

    It's definitely over-complicated by having the separate row/column structure in MR!

  • Suggested answer
    Kimberley Stevens Profile Picture
    Kimberley Stevens 720 on at

    Following up to say that I did run out of space for arguments with the first method that I suggested.  The character limit in MR advanced cell placement calculations is 1024.

    I ended up using a combination of my first suggestion and my second suggestion.  Utilizing my second suggestion reduced the number of arguments that I needed in my advanced placement formula enough to fit the remaining calculations in (note that I also renumbered my rows to reduce the number of characters in the formula).  I was able to make some of the extra columns non-printing columns in my column layout, and I was eventually able to utilize a white font to hide the remaining columns as well.

    I believe that the third alternative that I suggested should also work.  I got it to work on a smaller test report, but there is some level of complication that I have in my live report that is preventing it from working there.  

    In the end, my report is enormous, and seemingly pushing every limit of MR.  I hope none of you have to write this complicated of a report, but feel free to reach out to me if you do!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans