Skip to main content
Hi Ryan - there's not one on the Modano website but we could mock something up for you pretty quickly.
I've always done these with a VBA macro - i.e. cash sweep/payback period analysis. Is this what you're expecting?
Also, what time series are you using - e.g. annual, monthly, annual historical & forecast, etc?
My model is monthly. That's exactly what I'm working on, cash sweep to either draw down or repay revolver using a cash flow waterfall.
This business is seasonal so working capital levels fluctuate quite a bit leading to revolver drawdowns.
Ok Ryan - I've built a cash sweep (i.e. revolving debt facilities functionality) into the debt module within a monthly forecast-only financial model and attached it to this comment.
The approach I've used is one that I regularly use when building financing models, and I've always liked it because it doesn't require the use of a goal seek. It also includes the ability to:
The approach uses a combination of calculating the required cash drawdowns and repayments in each period and VBA looping to very efficiently calculate the drawdowns and repayments required in each period to achieve a target closing cash balance. It's only limitations are:
Let me know if this is what you were after, or if I've missed anything.
Inserting this module into your own modular workbook
To insert the debt module containing this cash sweep in this example model into your own modular workbook:
Let me know if you have any issues.