![minimum cash balance forecasting minimum cash balance forecasting](https://www.solutions360.com/wp-content/blogs.dir/13/files/2020/05/Cash-Flow-3.png)
If the statement of cash flows is driven entirely by formulas connecting it to the IS/ BS, you will never have an issue where things don't balance. In general you need to get rid of anything hard coded, everything should be driven by formulas in the forecast years. So your balance sheet doesn't balance for the historical period if you use the numbers you actually have there - so that is def causing problems going forward. But this is a hardcoded number actually adding up your "total liabilities" and "total equity" accounts for 2013 gives $1,066,797 million. You have total liabilities + equity = $959M in 2013, equal to total assets. Then this is actually your biggest issue (as to why things don't balance) I think: I don't know whats going on with the historical data, but something is up. For unearned revenue, the item on the SCF is not considering the change in non-current unearned revenue. Then with the capital leases I'm not sure how the $10,000 number under financing cash flows connects with what is going on on the balance sheet, seems like it doesn't. Then you have "Amortization of deferred costs" on the CF statement every year as if you are amortizing on a SL basis over 10-years, but the balance sheet items stay constant over the whole time period. Although this isn't actually affecting articulation because your BS and SCF agree on the revolver going down by $215M to -$107M. The several things I noticed: biggest issue is the revolver which you have flip flopping between positive/negative values. More logic needed if you want to incorporate the max draw on the revolver, make sure the revolver doesn't draw after you pay off your 1st Lien, etc. Less: Repayments = if(cash flow available for revolver)>0, min(cash flow available for revolver,(beginning balance + draws)Įnding Balance = Beginning balance + Draws - Repayments Plus: Draws = if(cash flow available for revolver) Your revolver math should something along these lines:īeginning balance = prior period ending balance You can layer other/different steps in as needed obviously Less: Mandatory Amortization of your TL, etc Take your CFADS (however you get to it from Net income / EBITDA) Check out our 15% off discount to Wall Street Prep's Financial Modeling Courses.īreak it out into steps for your waterfall, and adjust the waterfall based on seniority based on your deal (you mention CWIP, etc):
![minimum cash balance forecasting minimum cash balance forecasting](https://d2vlcm61l7u1fs.cloudfront.net/media/286/286327a3-4ad8-4f01-a9c0-eb80393a058c/phpooV5Gu.png)
Sign up for our financial modeling training course to learn all this and more. To get to the menu option: Alt + T + O then go to Formulas, check iterative calculations and set to 100 If you are encountering circular references when building out your model - you should go to options and make sure that iterative calculations are turned on. You can also view a separate thread on this topic. Try it and you'll see you cash balance will always equal that min cash balance plug.
![minimum cash balance forecasting minimum cash balance forecasting](https://image.slidesharecdn.com/cash-flowforecasting-120109130454-phpapp02/95/cash-flow-forecasting-9-728.jpg)
This will ensure you have your minimum cash balance and the revolver draws and pays down around this balance.
![minimum cash balance forecasting minimum cash balance forecasting](https://pas-wordpress-media.s3.amazonaws.com/content/uploads/2015/05/cycle-cash-flow-left.jpeg)
If it's a surplus, you can sweep it or let it accumulate as a cash balance. (-) Min cash balance (plug here or in your assumptions page)įrom here, if this is negative, you get your revolver to auto draw the shortfall. (-) Mandatory Repayments (revolver isn't mandatory, only Term Loan amortizations / maturities)