Return to Projected Balance Sheet Model Overview
The projection is a data processing procedure to populate parts of planned balance sheet statements. The projection uses amounts planned in the Profit and Loss cube. The projection configuration defines for all PnL Account elements how they affect the balance sheet. The configuration can be set up as well as executed using the Projected Balance Sheet Projection Configuration report. The projection procedure creates Posting Journal entries in the Projected Balance Sheet ledger and posts the totals to the Balance Sheet Cube.
The Projected Balance Sheet model covers the following cases:
- Instant mapping of income or expenses without tax from an account in the profit and loss statement map to an account in the balance sheet statement (showing an increase or decrease in a transaction).
- Instant mapping of income or expenses with tax from an account in the profit and loss statement are the same as without tax plus mapping the computed tax in income or expenses to a tax account in the balance sheet statement (showing an increase or decrease in a transaction).
- Deferred proceeds (alias inbound payments) and payments (alias outbound payments) from current receivables or accounts payables to a cash or cash equivalents account.
- Deferred proceeds and payments from tax assets and tax liabilities to a cash (or cash equivalents) account. Document limits when due tax assets proceeds exceed tax payments.
1. Instant mapping of income or expenses without tax
Legal entity 17 (Best Bicycle USA LLC) has planned a total revenue of $ 12,928,730 for December 2017. In the planned balance sheet this amount is booked as an increase of Total trade and other current receivables and an increase in Retained earnings. The booking takes instant effect – in the same period as the revenue.
The following diagram illustrates how the transaction in profit and loss causes changes in the two balance sheet accounts:
The configuration for the PnL Account mapping is defined in the projection configuration:
The retained earnings account is defined in Administration > Settings > Projected Balance Sheet.Configuration.bs_account_retained_earnings.
The journal entry showing a debit in Total trade and other current receivables as well as a credit in Retained earnings within the BS Account is shown below:
|
BS Account (Transaction Type) |
Debit |
Credit |
|
Total trade and other current receivables (Increase) |
12,928,730 |
|
|
Retained earnings (Increase) |
12,928,730 |
2. Instant mapping of income or expenses with tax
The Jedox model features the projection of taxes on income and expenses. This may or may not be relevant for planning and control purposes, depending on the industry and customer preferences. The usage of tax projection is optional. It can be turned on per individual PnL Account by setting up a tax account and a tax rate.
The following diagram illustrates how the transaction in profit and loss causes changes in the two balance sheet accounts when a tax rate of 19% is applied to the net amount:
The configuration for the PnL Account mapping is defined in the projection configuration:
The journal entry showing a debit in Total trade and other current receivables as well as a credit in Retained earnings and Current tax liabilities within the BS Account is shown below:
|
BS Account (Transaction Type) |
Debit |
Credit |
|
Total trade and other current receivables (Increase) |
15,385,188.34 |
|
|
Retained earnings (Increase) |
12,928,729.70 |
|
|
Current tax liabilities, current |
2,456,458.64 |
3. Deferred proceeds and payments
Not all invoiced revenue is paid immediately but typically with some delay. Based on experience a payment schedule of payments can be defined. The model supports payment schedules with an arbitrary delay (in periods) and split in up to four portions. The payment schedule for this example is defined as follows:
|
Revenue Percentage Paid |
Payment Schedule |
|
60% of revenue |
Paid within a delay of one month after invoice |
|
25% of revenue |
Paid within a delay of two months after invoice |
|
10% of revenue |
Paid within a delay of three months after invoice |
|
Remaining 5 % of revenue |
Paid within a delay of six months after invoice |
The configuration for the PnL Account mapping is defined in the projection configuration:
For the December revenue $ 12,928,730 this means 60% of this amount will be paid by January 2018, additional 25% in February, another 10% by March and the remaining 5% not later than May 2018. But wait – all these figures are not relevant to create the projected balance sheet for December 2017. To project the proceeds and payments happening in December one must look up the invoices raised in the months before December. 60% of the November revenue is projected as payment in December:
But that’s not all. There is also 25% of the October revenue coming in December. And 10% of September and finally 5% of June:
The journal entries showing a debit in Current Payments, as well as a credit in Total trade and other current receivables within the BS Account, is shown below:
|
BS Account (Transaction Type) |
Debit |
Credit |
|
Current Payments (Increase) |
554,076.13 |
|
|
Total trade and other current receivables (Decrease) |
554,076.13 |
|
BS Account (Transaction Type) |
Debit |
Credit |
|
Current Payments (Increase) |
1,200,541.28 |
|
|
Total trade and other current receivables (Decrease) |
1,200,541.28 |
|
BS Account (Transaction Type) |
Debit |
Credit |
|
Current Payments (Increase) |
2,620,649.78 |
|
|
Total trade and other current receivables (Decrease) |
2,620,649.78 |
|
BS Account (Transaction Type) |
Debit |
Credit |
|
Current Payments (Increase) |
6,918,515.28 |
|
|
Total trade and other current receivables (Decrease) |
6,918,515.28 |
If the delay requires data older than the first period of the current version, data from the previous version (given by the Previous Version attribute of the Version dimension) is used.
4. Deferred proceeds and payments from tax assets and tax liabilities
The immediate projection of the additional tax is done together with the instant mapping of income or expenses. The actual payments of the tax liabilities are due one month after the revenue (this schedule is independent of customer payments). The tax payments of December result from the revenue of November:
The journal entry showing a debit in Current tax liabilities, current, as well as a credit in Current Payments within the BS Account, is shown below:
|
BS Account (Transaction Type) |
Debit |
Credit |
|
Current tax liabilities, current (Decrease) |
2,190,863.17 |
|
|
Current Payments (Decrease) |
2,190,863.17 |
The Projected Balance Sheet model creates the journal entries for payments of tax liabilities and proceeds from tax assets from the revenue and expenses in the past. In practice, the proceeds are deducted from the payments. However, if tax assets exceed the liabilities the net tax assets would remain until becoming deductible from future tax liabilities.
For the sake of simplicity and transparency, this is not modeled in the projection. The Projected Balance Sheet model would just assume a proceed from net tax assets.
The configuration for the PnL Account mapping is defined in the projection configuration:
Updated August 1, 2024