Cashflow - Direct basis - Funding requirement and Cash balance at the end of period Identification

4 posts / 0 new
Log in or register to post comments.
Last post
1 year ago
#1
Sajid Mafahir X 1
SM
Cashflow - Direct basis - Funding requirement and Cash balance at the end of period Identification

Hello All,

Could any member assist me in finding cashflow deficits throughout the forecasted periods in a more coherent manner (or maybe it is already available in Modano but I'm not looking at the right area)? 

What I could currently see is "negative cash" as an error and in the financials but if there's a way where I could populate the financials to a freeform sheet and then place a row below the Cashflow which links with the "debt" model it would then be modelled in a much intuitive manner rather than crossing through sheets for that purpose as its something which had to be done with the board and for them to see in a single sheet. We need this row to show the Cash flow at the end of the period matching with the Cash in the Balance sheet for the period.

I tried creating a freeform sheet but i could not figure out to pull the information required from other reports except the time series.

Thank You.

Sajid 

1 year ago
Michael Hutchens A+ 190

Hi Sajid,

I'm a little confused by this post. The direct cash flow statement should very clearly show you the cash flows from operations, investing, and financing which collectively determine your change in cash held.

And to the extend that you're looking to prevent negative cash at bank using a debt facility you really just need to insert a cash sweep debt facility, which you can do as per below using the Change Driver tool.

This type of debt facility allows you to specify a target closing cash balance and will then draw down or repay debt in each forecast period with the aim of closing cash equating to this target.

Freeform sheets are always an absolute last resort and we generally recommend against using them unless there's a very good reason for doing so.

I hope this helps. Michael

1 year ago
Sajid Mafahir X 1
SM

Hello Michael,

 

Thank you for your feedback.

I have 3 questions based on your feedback.

1 - I'm not sure if Modano is providing me with a direct cashflow based on the imports from Xero. 

  • What I'm seeing is the P&L and BS are imported on accrual basis and based on assumptions in the "working capital" sheet and other cash/credit selections the cashflow report is calculated even for the historical.
  • I was surprised to see some extra information such as dividend and other financing cashflow populate info out of nowhere while we do not have such historical records in Xero or anywhere. i had to break the formula in capital & financial sheets to avoid this assumption in the forecasted periods. attached screenshot and highlight in red after breaking the formula. 
  • Either way, we should have to portray a check on cashflow statement (opening cash + net cashflow = closing cash balance) and balance sheet cash balance to say its actually the same. Perhaps, I'm not looking at the right area yet.

 

2 - Cash sweep debt facility

  • Yes, this is the driver I have been using to prevent negative cashflow and since this is a sheet which I have to cross quite often is why I tried to create an annual summary in a freeform sheet and link the cash sweep debt facility to a new row in the freeform sheet so it helps the model recalculate simpler while in presentation mode.
  • Another point here is that the interest is assumed to be paid monthly. While this should generally be the case but the interest is accumulated and paid at a later stage than paid periodically. is there a way to manipulate this assumption? i had to break formula here too to avoid cashflows which are not real.
  • the expense should be calculated monthly and works fine but the payable portion should be allowed to manipulate.

3 - Freeform sheets for presentation/kpi's etc.

We would like to create freeform sheets but using the information from Modano's outputs/inputs but just as a reference link. This will help us present the model in the way the recipient feels comfortable to take decisions in a way they understand. this is required as we need to add few management accounting information on top of the finance model to make informed decisions. 

Appreciate all your support and amazing updates and expecting feedback on the above.

 

Looking forward.

Thanks.

Attachments: 
Modano - CF Investing auto populating of info.png
1 year ago
Sajid Mafahir X 1
SM

Hello,

Could i expect any feedback on the above?