Cash and revolver.

When we finish building the cash flow statement, we've calculated cash and cash equivalents net to the revolver at the end of each forecast year but we need to use this single number to forecast both cash and the revolver in our balance sheet. So how can we do this? Well, if the ending cash balance is negative, we can assume that this is just a liability on the balance sheet. So we assume that it all relates to the revolver and that the cash balance is zero. So in year one shown here, we would show a revolver balance of 50 and a cash balance of zero. Effectively, we're saying that the company has had to dip into its revolver because it didn't have enough cash for its other activities. If the ending balance is positive, we can assume that this is just an asset balance on the balance sheet. So we assume that it all relates to a cash balance and that the revolver balance is zero. So in year three shown here, we would show a cash balance of 20 and a revolver balance of zero. Effectively, we're saying that the company has been able to pay down its revolver because it had surplus cash but how can we make our model do the work for us without us having to manually allocate positive and negative balances to cash and the revolver? Well, we can use the MAX and MIN functions in Excel to do this. For the cash balance, we use the MAX function. The formula takes the maximum of zero and the ending amount on the cash flow statement. The MAX function takes the higher of the two numbers in brackets. So in year one, Excel takes the higher of zero and minus 50, which is zero and our model therefore shows a cash balance of zero. However, in year three, Excel has to take the higher of zero and 20, which is 20, and therefore, this time our model shows a cash balance of 20.

For the revolver balance, we use minus the MIN function. The formula takes minus the MIN of zero and the ending amount on the cash flow statement. The MIN function takes the lower of the two numbers in brackets. So in year one, Excel takes the lower of zero and minus 50, which is minus 50. However, because there's a negative sign before our MIN function, this converts it into a positive number and our model therefore shows a revolver balance of 50. In year three, Excel has to take the lower of zero and 20, which is zero and therefore, our model shows a revolver balance of zero. So using the MAX and MIN function, we can plug our cash and revolver balances into our balance sheet using the balance at the bottom of our cash flow statement. Because we're using our cash flow statement to solve for both cash and the revolver, our balance sheet should absolutely balance once we've done this. So don't forget to check that your balance sheet balances once you've popped cash and your revolver into your model.