Forecast your ability to borrow on your line of credit with accuracy and confidence
In a recent blog entry I tried to convey how important it is to be able to accurately and completely forecast a company’s balance sheet and the many benefits associated with it. For many Budget Maestro customers who also use Analytics Maestro, this is an everyday reality.
We looked at one important benefit, the forecasting of meeting or breaching loan covenants and how properly planning and continuously analyzing (with Analytics Maestro) can show us in advance how the company is going to perform and what changes need to be made (hopefully well in advance) in order to eliminate or at least reduce negative results that can have severe consequences to the company.
Here is another great benefit of balance sheet forecasting:
Most small and medium size companies have a line of credit (LOC) established with their local bank or a financial institution. In addition to LOC covenants that are in the LOC agreement, all banks have some restrictions imposed on the use of the credit line.
For example: The bank establishes a LOC limit (e.g., 13.5 MM). Then, if the LOC is secured by the company’s accounts receivable and inventory, the lender will almost always have restrictions on these two assets, which means that less than the full asset value can be used to secure the outstanding LOC balance. In our example let’s assume that only 80% of all eligible accounts receivable can be used to calculate the maximum amount that can be borrowed and remain outstanding on the LOC. We will further assume that only 50% of the inventory valuation (at each period end) can be used in the calculation.
In addition, the lender may stipulate in the LOC agreement that the maximum inventory value that can secure the LOC is $7.5 MM, regardless of the actual valuation.
Normally, without proper forecasting and analysis tools company managements are faced with the challenge of manually forecasting how much of the credit line amounts may be available to them during the plan or budget period (e.g., 12 month, 24 months, etc.). In reality, many smaller companies are unable to do that or prefer to ignore it due to lack of proper technology tools.
This is in addition to the difficult task of forecasting cash flow and the cash account balance at each budget period end, unless the company’s balance sheet is accurately and completely forecasted. In previous posts we saw that balance sheet forecasting is not only possible with Budget Maestro, it is a reality and is routinely used by the companies who use this application.
How would our LOC example work using Budget Maestro with Analytic Maestro?
First, we must complete our company financial plan and budget. This is done in Budget Maestro and does not require programming or entering any formulas, links or macros. We rely on using built in business rules and logic and can depend on drivers we enable, as well as other entered forecasted data and business specific assumptions.
The budget entered will automatically generate forecasted financial statements that cover every period included in the budget. The obtained forecasted balance sheet will show us the forecasted cash, A/R and inventory balances (and all other balance sheet account balances).
Using Analytics Maestro, any data available in Budget Maestro can be displayed in any format we desire. Since Analytics Maestro uses MS-Excel’s formatting and display capabilities we can customize reporting templates that will automatically pull data from Budget Maestro and display them exactly the way we want them to look.
A custom display showing the available LOC balance is set up using the Budget Maestro forecasted AR and Inventory account balances, in conjunction with the bank’s restrictions on these two accounts.
Using our above example (13.5MM LOC limit, 80% of AR eligible, 50% of Inventory eligible to a maximum of $7.5 MM) and with the following five ending balances:
Jan 2015 Feb 2015 Mar 2015 Apr 2015 May 2015
AR Bal(MM) 6.4 7.3 7.1 7.5 8.1
Inv Bal (MM) 13.6 14.4 15.1 15.7 16.2
we can see that in these forecasted periods the available LOC balances (how much we will be able to borrow on the LOC) are:
Jan 2015 Feb 2015 Mar 2015 Apr 2015 May 2015
LOC Avail. 11.9 13.0 13.2 13.5 13.5
A graphic representation of the forecasted available cash from our line of credit can be quickly set up and will display the above data as follows:
In this example we can clearly see from the display output that the company is limited in the first two months by the inventory credit limit component of the LOC and in the last two months of this example by the LOC total limit. This data can be made visible for every period of the budget, for example 12 months, 24 months or longer. It can easily be compared to current actual data as well as historical data.
As budget data and assumptions are changed, all financial statements are updated in real time. The data used in our example will also be updated, showing the new results. If you have more than one version of the budget set up in Budget Maestro, as well as actual and historical data, you can see all that as well. With multiple LOC’s (in multiple entities) you can see each LOC data individually, globally or in any combination of entities.
With a forecasted balance sheet, using this example, not only can you project your cash balance and other balance sheet key account balances at each budget period-end, you can also clearly see the available cash from your line of credit, using the exact terms and restrictions imposed by the lender.
You can plan ahead to make this available LOC cash work better for the business and can also be better prepared to request changes (e.g., increases) to the LOC well in advance before you run into a cash crunch. On the other hand, you can plan on reducing the LOC balance over time by better planning your inventory or production demand, better managing your vendors or supply chain, etc.
As is always the case with balance sheet forecasting, Budget Maestro with Analytics Maestro, takes the guess out of your forecasting through a clear and up to date presentation of key balance sheet numbers, financial ratios and other automatically calculated key performance indicators as well as provides a full set of forecasted financial statements just like your actual accounting system, except projected into future periods.