Leases IFRS model.

Here we are given a part complete model for JD Sports Fashion, a UK-based sports clothing and equipment retailer.

They have a large lease portfolio, which primarily reflects their retail stores.

We are going to complete the model by including the appropriate income statement, balance sheet, and cash flow statement entries in respect of these leases.

Let's start by taking a quick look at the information provided in the assumptions.

We're given an assumption for the lease CapEx as a percentage of revenue, so that's the present value of new leases signed in each year as a percentage of revenue, and this figure is held constant with the prior year, so we're assuming that new store openings are growing in line with revenue.

We also have an assumption for lease depreciation as a percentage of the opening balance on the lease asset, which is very similar to the depreciation assumption for purchased assets.

Finally, towards the bottom of our assumptions, we also have an assumption for the effective interest rate on the lease liabilities.

In the calculation section, we have empty based calculations for both the right of use assets and the lease liabilities.

The income statement is almost complete, but is missing the depreciation on leased assets and the interest expense on lease liabilities.

The balance sheet is also nearly complete, but is missing the right of use assets and the lease liabilities.

The cash flow statement is also nearly complete, but is missing the cash flow statement entries for leases within operating cash flows and also financing cash flows as a result of all of this.

The balance sheet in this model doesn't yet balance, so let's create the lease entries needed to complete this model.

We're going to start with the base calculations for the right of use assets, and the first thing we need to do is take the ending balance from the last reported year.

This then becomes our beginning balance in the first forecast year.

We then add to this the lease CapEx.

Remember that this isn't an actual cashflow, it's just the present value of new leases signed each year.

We'll grab our assumption for this from the top of the model and then multiply this by our forecast revenue and this gives us lease CapEx.

Next, we need to subtract the depreciation on the lease assets, and again, we'll grab our assumption for this from the top of the model and then multiply this by the beginning balance.

On our right of use assets, we need to multiply this by minus one to make it a Negative figure.

We can then sum our base calculation to provide the ending balance on our right of use assets in the first forecast year.

Now, let's build our base calculation for the lease liability.

As before we take the ending balance from the prior year and that becomes our beginning balance in the first forecast year.

We then add to this lease CapEx note that this is the same lease CapEx that we added to the right of use assets because new leases create an equal and opposite new assets and liability in the balance sheet.

The next step is to add the lease interest.

Remember that the lease liability reflects the present value of future lease payments, so as the discount on each lease liability unwinds, the interest cost is added to the liability.

We calculate this by taking the interest rate assumption from near the top of the model and multiplying this by the beginning balance on the lease liability.

The final step is to subtract the lease repayments.

We're going to assume that lease repayments are equal to the total lease expense in the income statement.

That's the interest and depreciation above.

So we subtract both of these in this line.

Note that we need to multiply the interest by minus one to make it negative.

We can then sum our base calculation to provide the ending balance on our lease liability.

Now that we have completed our base calculations for the first forecast year, we can copy them all across to the right for subsequent forecast years.

The next step is to include these items in our model, starting with the income statement in the income statement, we're going to grab the depreciation on our lease assets and include these in the same way as other depreciation.

We are then going to grab the interest on our lease liabilities and include those in the same way as other interest costs.

Two things to flag here.

Firstly, we need to multiply the lease interest by minus one to show it has an expense in the income statement.

Secondly, the interest is calculated on the opening lease liability, so we don't need to worry about creating any circular references here.

Now let's look at the balance sheet here.

We need to take the ending balance on our base calculations and include them in the balance sheet.

We start with the right of use asset first and then finish with a lease liability.

The final step is to make the adjustments needed in the cash flow statement.

First, we need to add back the depreciation on our leased asset in operating cash flows as this is a non-cash item, which is included with net income.

Note that we haven't added back the interest on the lease liability as interest payments are usually included within operating cash flows.

We now need to include the lease repayments in our financing cash flows.

However, because we've already included the interest component in our operating cash flows, there's a bit of complexity here.

We start by subtracting the whole lease repayment, which we grab from our base calculations, and then we add back the lease interest because this is already implicitly included in our operating cash flows.

Now that we've completed our cash flow statement, our model is complete.

We can check that our model has integrity by confirming that our balance sheet now balances, which we can see is the case.