Minimum Cash Balance Forecast

How to model a minimum cash balance in the forecast

To model a Minimum Cash balance in the forecast, you can set a threshold amount that triggers action when the cash falls below it. For instance, let's say you want to maintain a minimum cash balance of $10,000. If the projected cash balance were to fall below this threshold, you can establish a mechanism that automatically initiates the draw from the Line of Credit.

Follow these steps to model a minimum cash balance in the forecast:

1. Create a Minimum Cash Balance Assumption

   Start by creating an assumption that represents the minimum cash balance you want to maintain. Create the Assumption from Settings ⚙️> Assumptions.
   
   

2. Populate the Minimum Cash Balance Assumption

   Populate the assumption with the desired minimum cash balance from Plan > Assumption. In this example, we'll use $6,000,000:
   

   
   

3. Create a Section in a Custom Table

   Make a separate section in a custom table to calculate what needs to happen when your cash balance drops below the minimum amount.

   The section should look like the one below when complete.

   
4. Add a Balance Sheet Driver for the Line of Credit
   Cash is always the implied offset of every driver, so it is only necessary to add one driver linking the Line of Credit calculations to the Balance Sheet. 
   Add an Increase Driver from Plan > Drivers > Balance Sheet to the Line of Credit Account.
   
5. Add Drivers to the Custom Table
   Finally, add drivers to the Custom Table to determine when the cash balance dips below the minimum threshold and draw from the Line of Credit. 
   
   1. Forecasted Cash Balance
      • The purpose of this line is to display the cash forecast from the model for reference
      • Driver Type: Global
      • Input 1: KPI Library ⋄ Cash ⋄ Cash on Hand
      • fx: ✖️
      • Input 2: Constant ⋄ 1
      • Start Date: Min Date
      • End Date: Max Date
   2. Line of Credit Draw
      • The purpose of this line is to calculate the appropriate amount to maintain the minimum cash balance
      • Driver Type: Custom (Plan)
      • Input 1: Custom Tables ⋄ Balance Sheet ⋄ LOC Draw Flag (1=Draw) 
      • fx: ✖️
      • Input 2: Custom Tables ⋄ Balance Sheet ⋄ Balances Excluding Cash & LOC
      • Start Date: Cutover Date
      • End Date: Max Date
   3. Forecasted Line of Credit
      • The purpose of this line is to display the line of credit forecast from the model for reference - this should be the same account that a driver was added to in step 4
      • Driver Type: Global
      • Input 1: Liabilities $ ⋄ Line of Credit ⋄ Subtotal 
      • fx: ✖️
      • Input 2: Constant ⋄ 1
      • Start Date: Min Date
      • End Date: Max Date
   4. Balances Excluding Cash & LOC
      • The purpose of this line is to calculate when cash would go below the minimum balance assumption; a positive balance means the line of credit should be drawn upon whereas a negative balance implies there is no need to draw from the line of credit
      • This line will have multiple drivers associated with it:
        • Driver Types: Custom (Plan)
        • Add Drivers to sum all Assets EXCEPT the Cash account from Step 5a
        • Add Drivers that subtract all Liabilities & Equity EXCEPT the LOC account from Step 5c
        • Add Driver to reference the Minimum Cash Balance Assumption from Step 1
        • Add a Driver to reverse the Forecasted Line of Credit from the previous month
        • Start Dates: Cutover Date
        • End Dates: Max Date
      • When complete, it should look similar to this:
        
   5. LOC Draw Flag (1=Draw) 

• • • The purpose of this line is to return a 1 when the Line of Credit should be drawn upon per the Balances Excluding Cash & LOC line above; a 1 means  the line of credit should be drawn upon whereas a blank implies there is no need to draw from the line of credit
    • Driver Types: Custom (Plan)
    • Input 1: Custom Tables ⋄ Balance Sheet ⋄ Balances Excluding Cash & LOC 
    • fx: ➗
    • Input 2: Constant ⋄ 100,000,000
    • Rounding: Round Up 

• • • Start Date: Cutover Date
    • End Date: Max Date

By following these steps, you can effectively model a minimum cash balance in your forecast.