We are trying to build our cash flow statement in Prophix. Currently, we have our Income Statement and Balance Sheet in Prophix and have had it since we implemented. The Income Statement and Balance Sheet have the Time Perspective dimension in the pages so users can switch between MTD and YTD. We would like to do the same with the Cash Flow Statement.
A member of the Product Adoption Team helped me build what we have so far but it only reports MTD, YTD is not accurate. We used an MDX formula to create a new version called “Prior Month”. Then we created a second version called “Actual Minus Prior Month” which calculated the change. In my data view, I have a unique combination of Accounts and Versions since some accounts (like Net Income) would use Actual instead of the new version, Actual Minus Prior Month.
Since the Cash Flow Statement is a standard financial report, I’m certain other users are utilizing Prophix to report this information from a MTD and YTD perspective. I’m hoping to find some insight on how other users have their reports set up.
We build a cash flow statement but it only works on a YTD basis. I also look forward to any more input on how MTD would work.
Hi Brittany,
I’m Darren, one of the Solution Architects at Prophix.
Unfortunately it’s not easy to just switch between MTD and YTD on a cash flow statement like you can on a balance sheet or income statement. The reason is that you need a different MDX version for Prior Year-End balances instead of Prior Period balances. Switching your Time Perspective selection on your report will not automatically switch your calculated versions.
This is why I typically create two separate cash flow reports, one for MTD and another for YTD.
if you want to build a YTD Cash Flow statement, the MDX code for a Prior Year-End version would be:
(ParallelPeriod([Time].Default.[Year],1,Ancestor([Time].Default.Currentmember, [Time].Default.[Year])), [Version].Default.&[ACT])
I hope this helps.
Take care,
Darren
4 Likes
I have a cash flow but only for YTD. I agree with Darren that 2 separate reports would work best for MTD and YTD. Good luck
Brittany,
It looks like this only works in single currency. Is that the environment you operate in?
Mike
Hi Brittany,
I’m surprised by many of the answers, we have a cash flow statement that can switch between MTD and YTD in the pages. It was built by our implementation consultant from Prophix the first week we had Prophix along with our BS and IS.
In pages we have: Classification, Department, Time Perspective, and Time
Rows: Account
Columns: Company, Version
I believe the key for this setup is in the Account dimension. Each line of the cash flow statement is its own account. The balance sheet items that change (either from month to month, or from the current month compared to the end of the prior year) use a LAG formula.
The cash flow formula for “receivables” is this: LAG([119.0],1) -[119.0]
119.0 represents the account key for our “receivables” account on the balance sheet.
I hope all that helps!
3 Likes
Yes, we only use US currency.
Does anyone forecast the cash flow? Do you have to forecast the balance sheet or just at the cash flow level? Thanks for any input!
1 Like
Hey @brittany.hess,
You could also try this to combine a MTD & YTD report.
- In the Account dimension, for the Cash Beginning of Period account, change the Time Conversion under Member Properties (right-panel) to a formula.
- Check the MDX box and add this formula
IIF([Time Perspective].[Default].CurrentMember IS [Time Perspective].[Default].&[YTD] , ([Accounts].[Default].&[CF04],[Time Perspective].[Default].&[BASE], OpeningPeriod([Time].[Default].[Month],Ancestor([Time].[Default].CurrentMember,[Time].[Default].[Year]))) , ([Accounts].[Default].&[CF04],[Time Perspective].[Default].&[BASE], OpeningPeriod([Time].[Default].[Month],Ancestor([Time].[Default].CurrentMember,[Time].[Default].[Quarter]))))
NOTE: check that the dimension names in the square boxes match the names of your dimensions. For example, the formula uses [Accounts] but if your Account dimension is spelled [Account], then you will need to modify the formula.
This formula basically says if the Time Perspective = MTD, then the cash beginning of period should use the the regular formula: LAG([cash account],1). If Time Perspective = YTD, then the cash beginning of period should be prior year December’s ending balance.
Have the Time Perspective dimension in the Pages to flip between MTD and YTD and use Version: Actual for both.
The Cash End of Period account should also have a formula for the Time Conversion. The formula should be Cash Beginning of Period + Net Change in Cash.
Once you’ve made these changes, validate the values for both MTD & YTD to make sure they’re correct.
Hope this helps!
Lilian
4 Likes
Good summary Austin. This set up is what we later built so that we could budget and forecast the cash flow statement. Because each line is a separate account using the Lag formula, you can switch between versions and times much easier.
Our original build used the time perspective/version in columns and the balance sheet and income statement accounts in the rows.
2 Likes
Typically they are built at the YTD level, but I’ve seen instances where a separate MTD is built. Or if needed, we can built them into one by using MDX formulas to allow for a cash flow on both perspectives.
Brittany,
We only do a few lines of YTD in our cash flow and we just built them into our cash flow statement.
We just use a lag reset to get the YTD balance.
This gets me the YTD amount of our Monthy Net cash amount.
Statistical Account: YNCOP - YTD Net cash
Formula: [MNCO]+Lagreset([YNCOP],1)
You could probably use this concept for all your cash flow lines, hide the monthly rows and just use the YTD rows to display. But this would probably require a new report.
Hope this helps
1 Like
Our organization spends a lot of time on cash flow forecasting. At the weekly level. I have an Excel model right now that does this for us, and I was about to throw it into one of my cubes, but then we added a couple major acquisitions, so I’m waiting to get their data online and figure out a forecast for them, and then I’ll transition it into Prophix. I’ll probably use my Weekly Sales cube, since I use that as the driver on the receipts side.
1 Like
The use of LAG calculations in accounts is the key to creating the YTD/MTD reports you need.
2 Likes