Starting a beta – particularly a beta for a rapidly developing technology such as AI is always going to show you things you didn’t expect. Oh, so people don’t actually care about that feature we worked hard on? And people want to do what?! And actually most of their time is spent… you get the idea.
And of course with an early beta there are always bugs. One big one was that for some types of model not all the required files were in the installation package. That’s now been fixed.
The main learning however, is that customisation is essential. The usual approach to building a model with Openbox has three stages:
1. Decide the type of model (e.g. project finance)
2. Add standard components
3. Customise, because every project and business is unique
The current Openbox AI helps with the first two of these. But until now, you were on your own for customisation. That’s why we are introducing the first version of our Openbox AI formula expert. It helps you write Openbox formulas to do common tasks, letting you customise the model the AI produces to fit your needs more closely.
“Isn’t that the same as Copilot?”, I can hear someone asking. It is certainly similar, in that Copilot is designed to help you with common tasks in Excel. But Copilot is designed to help you analyse data. Financial models certainly use data, and particularly if you’re including lots of historic or actuals in a model, that analysis can be useful. But what you really want is to forecast, and often that forecast needs your understanding of how the future will work, which is different to the past. To take an extreme example, for a greenfield project, there’s no historic data to analyse.
The Openbox formula expert can help here. What it does is draw on its “general knowledge” (in reality, what the underlying LLM learnt from the Web) to come up with a plausible formula. Here’s what the AI said when asked to calculate Loan Life Cover Ratio (LLCR):
Certainly, the Loan Life Cover Ratio (LLCR) is a financial metric used to evaluate the ability of a project to repay its outstanding debt over the loan’s remaining life. The LLCR is calculated by dividing the present value of cash flows available for debt service by the remaining debt balance. Here’s how you can calculate LLCR in Openbox step by step:
### Step 1: Create New Calculation Blocks
1. [Net Cash Flow for Debt Service]: This represents the cash flows available for debt service (often EBITDA – taxes + depreciation – capital expenditures – change in working capital).
2. [Discount Rate]: The discount rate used to calculate the present value of the cash flows.
3. [Remaining Loan Balance]: The outstanding loan balance at a particular period.
4. [Present Value of Cash Flows]: This is the present value of [Net Cash Flow for Debt Service] over the remaining loan period.
5. [Loan Life Cover Ratio]: This is the LLCR, which we’ll calculate.
### Step 2: Set Values and Formulas
1. Set values for [Net Cash Flow for Debt Service], [Discount Rate], and [Remaining Loan Balance] based on your financial model’s assumptions and calculations.
2. Calculate [Present Value of Cash Flows] using the formula:
NPV([Discount Rate], FUTUREVALUES([Net Cash Flow for Debt Service], 1))
This formula calculates the present value of future net cash flows available for debt service, starting from the current period and extending for the life of the loan.
3. Calculate [Loan Life Cover Ratio] using the formula:
[Loan Life Cover Ratio] = [Present Value of Cash Flows] / [Remaining Loan Balance]
This formula divides the present value of future cash flows by the remaining loan balance to get the LLCR.
By following these steps, you will have calculated the LLCR, which will provide insights into the project’s ability to cover its remaining debt obligations.
Not bad, and it even correctly uses the FUTUREVALUES function.
So how do you get access to this? Well in a spirit of openness we’re making it available to everyone for free, as a prompt for ChatGPT. Just insert the prompt then ask away. If you’d like to get this please just email us. We’ll be making updated versions of the prompt available over time.
(Small print: this is an AI not a professional financial modeller so treat its responses as suggestions rather than following them blindly).
With the Openbox AI, and this prompt, you can now create, modify and customise your Openbox models quickly and easily. Happy modelling!
