UNDERSTANDING THE MODEL

Introduction

FD4Cast is a corporate financial modelling system designed to enable financial and planning professionals to plan their business future by exercising their judgement in forecasting (the perceptive process) as well as performing trending from historic inputs (the learnt process).

FD4Cast models provide the user with the ability to quickly construct robust financial forecasting systems for businesses to begin using immediately. These systems are designed and constructed from a global template using advanced VBA development, creating user‐friendly and robust Microsoft Excel 2007 spreadsheet models based on a formal definition language and methodology. Complete construction and configuration is estimated to take around 25‐30 minutes per model.

Financial models can be developed to include all required forecasting elements, from a sales forecast, operating expense budget, working capital modelling, capital expenditure and depreciation analysis; to loans, leases, trade debtor financing, etc. ‐ all being summarised in traditional cash flow analysis, profit and loss and balance sheet format. In addition, users have the ability to conduct interactive forecasting and decision making sessions such as budget creation and real‐time scenario planning.

Unlike traditional ‘black box’ modelling applications, the workbooks created can be extended by the user by using Microsoft Excel in the normal way. The model has two significant parts: the main part which is protected so that the user cannot accidentally corrupt the formulae generated, and includes a selection of Standard Summary and Detailed Reports for Profit and LossBalance Sheet, and Cash Flow; the second part comprises of unprotected blank sheets on which the user can, for example, create their own custom reports and draw data through formulae links from the standard reports. Furthermore, this feature simultaneously provides the opportunity to append additional business drivers and editable analysis sheets.

This User Guide has been written as four separate sections:

  • UNDERSTANDING THE MODEL: The first section deals with the construction and configuration aspects of FD4Cast models
  • INPUTS: The second covers the inputs of historical and forecast data required to create a forecast
  • OUTPUTS: The third details the standard output reports, scenario and budget planning tools that FD4Cast models deliver
  • INFO & FAQ: Final section covers additional info and frequently asked questions.

Technical Support within the hour is provided by the FD4Cast’s team during normal UK office hours and a telephone hotline number is provided to all customers. Alternatively, existing and trial customers can also email support@fd4cast.com with a maximum response time of 24 hours.


Back To Top

Process Flow

Before going into details about how to use the model and its features, it will be a good idea to understand the general logic flow of the model. Basically the model works with this simple logic:

Set your initial Balance > Input Revenues & Expenses > Define your Assets > Define how do you Finance your company > Take the Output Reports

The model may be configured depending on many factors such as your company type, services/products you offer, financing methods and so on. Below you will see more detailed process flow of the FD4Cast tool:

  • It starts with deciding whether you have an existing balance sheet or not. If you have an existing company with past financial figures then you should first set up your Opening Balance Sheet and start unwinding trial balance. If you are a start-up or a new company without historical finances, then you can skip setting up opening balances and directly go to configuring your model’s categories.
  • Then comes the Income and Expenses stage. At this stage, you start to define your revenue items, expenses, overheads and payroll. This is the main skeleton of your model.
  • After setting up the income and expenses, you will define your assets as shown on the flow. This is closely followed by setting up the financing parameters such as loans, leasing decisions, investments and so on. These together will comprise the balance sheet.
  • Once you have input the figures in the previous stages, your financial reports will be ready. Profit and Loss, Balance Sheet, Cash Flow reports will be ready automatically according to the parameters defined. At this stage, you can also run scenarios and consolidate your reports to create some custom outputs for stakeholders.

After completing the flow you may then start to fine-tune  your model. When you review the outputs you may want to go back and change some parameters to come up with more reasonable scenarios or reflect foreseen changes.


Back To Top

Ribbon

FD4Cast model comes with a pre-built Excel ribbon menu. No installation necessary. When you open the model, you will see a custom menu at the top menu of Microsoft Excel. This menu can be used to easily navigate between different sections of FD4Cast model.

Below you can see how it looks like. The ribbon sections are number with short descriptions and links to the relevant sections below.

  1. INPUTS: BALANCE: In this section the user can input opening trial balance info (for existing companies). They can also unwind TB and related settings.
  2. INPUTS: INCOME & EXPENSES: In the second section the user can input values for Income and Expense items. Forecasted income, revenue distribution, debtor profiles, variable or fixed costs are all defined at this stage. Here the user also manages the payroll details, salaries and so on.
  3. INPUTS: ASSETS: The third section is for assets. Your stock, inventory, equipment and all fixed assets are managed here. Also the user can input cash deposit information if needed.
  4. INPUTS: FINANCE & BS: The fourth section is used to manage how  the company is financed. Loans, Leases and Factoring details can be input here. Here is also where you manage Balance Sheet details like InterCo, Equity and other adjustments.
  5. INPUTS: TAXES: The fifth section is used to define taxes and currency ratios. Any foreseen changes in these parameters can be input until the end of the model year. Note the Model Start Date is  set with the Configure Modelbutton on the far right of the ribbon.
  6. OUTPUTS: STANDARD REPORTS: After populating the input fields, this section gives you the standard financial reports such as P&L, Cash Flow and Balance Sheet. You can also create custom dynamic reports to meet your needs.
  7. OUTPUTS: MODEL TOOLS: The seventh section contains other tools such as scenario planning, budget creation and tool adjustment features.

Before going in detail on each of these items, we next explain how to use the Admin Sheet and Model Configuration Menu.


Back To Top

Model Setup & Configuration

On the FD4Cast Ribbon, you will see the button “Configure Model” which has these options:

1.4.1 Configure from Blank Template

This enables the user to configure the model from scratch. This can be useful especially if you are not happy with the settings you made and instead of changing one by one, prefer to set up the model again.

When selected, a dialog is displayed for defining the categories and other settings in order.

1.4.2 Add / Remove Category Rows

This is one of the most crucial settings of FD4Cast model. At this menu you can add or remove categories. But first, let’s define what a “category” is in the model.

What is “Category”?

A Category is a separate item of revenue, cost, asset or liability for which the FD4Cast model requires input information in order to construct a forecast. Conceptually, these categories replicate the lines of information that you would see on the detailed Profit and Loss and Balance Sheet statements that your organisation currently uses.

Your nominal level trial balance may include many more categories of information that you might wish to use when producing a forecast. As a result, before you begin to use FD4Cast it may be necessary to undertake a mapping exercise to match and aggregate your nominal accounts/account codes to the summary categories of your forecast – as you would typically do when producing month‐end reports.

Managing Categories

When you hover over this menu item in the Configure Model menu, you will see these two options:

When you click on Add Rows to Categories, you will be prompted with a popup window:

You select which category you want to insert into a new row > If there are existing rows at that category, select after which one to insert > Number of Rows to insert.

This makes more sense when looking at the Admin Sheet. In our demo, we have defined the following rows for each category as you can see below:

For example, if we decided that we need two new expense items to be placed after Expenses B item, under the Direct Expenses category, our selection will be like this:

After adding your rows, you can rename new expense items according to your needs.

Similarly, when you want to remove any category items, just select “Remove Rows from Categories” and select the item to be removed.

FD4Cast models only use the exact number of categories the user requires. Users are not limited to a specific number of lines in each category and in theory can accommodate as many lines as they wish. Users are reminded, however, that the more lines per category, the bigger the model size and the longer it will take to calculate the outputs.

1.4.3 Change Model Start Date

In this menu, users can simply change the start date of the Financial Year for the model they are building:

1.4.4 Show / Hide Categories

As explained in the section 1.4.2, categories are important when managing FD4Cast model. You can add/remove and rename category items as you wish. In addition to that, if the user does not require certain categories of income, costs or balance sheet items according to the type of industry their business operates in or the format they report their results in, the possibility exists to hide these from view throughout the model. This makes reports, summary tables and input cells, easier to analyse by concealing empty rows from view and from printouts.

When clicked, this feature brings up a selection window in which the user can manage which categories to hide/unhide. For example, below we have hidden some Financing Categories:

This procedure can take some time to complete as there are many rows in the model and the VBA macro searches for multiple matching criteria in each sheet.

1.4.5 Length of Forecast Outlook

In FD4Cast models, the user can specify and change at any time the length of forecast outlook. The minimum length of outlook is 24 months, with the maximum being 120 months. The length of outlook is changed by the user by simply navigating to Configure Model > Length of Forecast Outlook, then selecting the outlook required as well as the first forecast period.

1.4.6 Total Model Reset

This option, as is evident from its name, clears everything, all inputs, selections and settings the user made:

Please note that, this action is irreversible, so use with caution!

1.4.7 Enter Unlock Key

Registered users can enter their license keys here to be able to use the full version of FD4Cast models.


Back To Top

Admin Sheet

The Admin Sheet is used to manage categories, category items and renaming these items, with all changes reflected throughout the model. As already mentioned in the section 1.4.2 Model Setup / Category Rows, categories are an important part of FD4Cast models.

At the very beginning, a default Admin Sheet will look like this:

As you can see, we have default category tables to the right. These categories are listed as:

  • Income
  • Direct Expenses
  • Overheads
  • Investments
  • Intangibles
  • Tangibles
  • Stocks
  • Cash Accounts
  • Loans
  • Finance Lease
  • InterCo
  • Other Debtors
  • Other Creditors
  • Other Long Term Liabilities
  • ExtraOrdinary Items (Income Statement)

 

Under each category table, we have category items. For example, “Sales” is an item under Income category. There may be other income items according to your business model (such as commissions, royalties etc.) During the initial model setup, you may add as many items as you wish for each of the categories (see: 1.4.2 ADD / REMOVE CATEGORY ROWS) and rename them according to your business dynamics.

After adding some items, our demo model’s Admin Sheet looks like this:

Managing the FD4Cast model is a recursive process, meaning you can tweak the configuration until you find a good balance which reflects your business dynamics. In this process, you will use Admin Sheet extensively to add/remove and rename category items.


Back To Top

INPUTS: BALANCE

Unwinding Trial Balance

 

COMING SOON…

Back To Top

INPUTS: INCOME & EXPENSES

Income / Revenues

Revenue stream directly effects almost every other section in the model such as profits, cash flow and so on. Therefore, entering forecast income correctly is crucial for managing the model. In this section we are going to explain these sub-topics:

  • How to enter Standard Forecast Income?
  • What are Debtor Profiles and how to use them?
  • How to enter and reverse Accrued Income assumptions?
  • How to enter and reverse Deferred Income assumptions?
  • Bad Debt provision feature?
  • Changing Sales Tax and Currency Rates for forecasted income items?

After entering income items, we will be able to see the outputs in the relevant financial reports.

3.1.1 ENTERING STANDARD FORECAST INCOME

On FD4Cast Ribbon, click on the Forecast Income button. You will be forwarded to the screen below (we have added some demo data to illustrate how to fill in the sections. In your clean copy, you will have blank sections)

Currencies: At the top of the screen (blue region) you will see the currency ratios for each month. This section is managed from Taxation > VAT Assumptions in the model ribbon. Different currency ratios can be input for the following months for more accurate forecasts. (For more details, see: 6.1 VAT & CURRENCY ASSUMPTIONS)

Income Items: On the left hand side of the screen (orange region) we see different income items. These belong to Income Category which can be added as shown at 1.4.2 Add/Remove Category Rows and renamed as shown at 1.5 Admin Sheet.

Debtor Profiles: At this section in the middle (yellow region) we can manage the relation between payments and time delays. This will be explained below in more detail.

Standard Income Input: This is the section (green region) where income for each category and each month can be entered manually. Income forecasts should be entered net of VAT and in the month that the income is invoiced. Any timing differences for accrued/deferred income are dealt with separately (any income that was deferred or accrued on the opening balance sheet should not be entered on the forecast, as these will be unwound as explained in Sections 3.2 to 3.4).

The income forecast area also has a section for ‘Other Income’. This should only be used for income that is not subject to Corporation Tax. There is a further section for Other Financial Income, and this section is included in the Corporation Tax calculation.

Once you have input your income forecasts, you will see various tables below your forecasts. These tables are for audit purposes only, and cannot be edited. They allow you to see the figures flowing correctly through your model and the effect they have on other parts of the FD4Cast model. Other tables here will be dependent on your debtor profiles and timing assumptions (Accrued/Deferred Incomes).

3.1.2 DEBTOR PROFILES

Different revenue items may have different cash collection profiles. Simply enter these profiles as a percentage of debt collected each period after the revenue is invoiced (the default setting is 100% cash collected in the month of invoice).

For example, you may have retailer customers as profile 1 and wholesaler customers as profile 2. Below you can see the effect of debtor delay:

This is a simplified example of debtor delays:

  • 40k sales is being invoiced in April-17 (Total sales including VAT is 48k)
  • 50% is being recorded with 30 days delay in May-17
  • Rest 50% is recorded with 60 days delay in Jun-17

Of course, in real use, more revenue items, different profiles and upcoming months are consolidated all together automatically by the model and reflected upon the reports.

3.1.3 REVENUE ACCRUALS AND DEFERRALS

To be clear on the terminology used in the model: Accrued income is earned by the company for providing a service or selling a product that has yet to be received. Deferred income is the portion of a company’s revenue that has not been earned, but cash has been collected from customers in the form of a prepayment.

Similar to the debtor invoice payment profiles (section 3.2), accrued/deferred revenues also make it easy to manage the time difference between earning the income and actually recording it. Below the standard income input fields, you will see the input regions for Accrued Income and Deferred Income. For each of these, we reverse the values to keep in balance.

A comprehensive example can be seen below:

Accrued Income: In the example above, company has 10k accrued income from April to October. This could be something like a bonus payment which will be released when a quota is reached. In November, income is reversed as 70k but not recorded into cash flow immediately due to debtor profile settings. As you can see in debtor profile settings, income item is split 50% for 30 days / 60 days. So, in the Outputs section (blue region), Cash Flow shows 42k in Dec-17 and 42k in Jan-18 (including VAT)

Deferred Income: Also in the example, this company has a deferred income of 50k in Feb-18 and reversed it in Apr-18. Note it would directly go to cash flow as 60k (incl. VAT). The same debtor profiles are applied and they are recorded as 30k in Mar-18 and 30k in Apr-18.

On the bottom left of the example, you can see Related Reports indicating to which financial report that record is sent to.

**Please note that in our examples, we simplify the view (hide some items, change the colours etc.) to make it easier to understand. Your view in FD4Cast model may be different.


Back To Top

Direct Expenses

In FD4Cast models, expenses are input in two separate sections: Direct Expenses and Overheads. To confirm the methodology, Direct Expense is an expense incurred that varies directly with changes in the volume of your business (more sales – more material purchases – more labor etc.). Note that Overheads are not directly proportional with sales volume.

3.2.1 ENTERING DIRECT EXPENSES

In FD4Cast model, we can input direct expenses in their own menu, as shown below:

Currencies: At the top of the screen (blue region) you will see the currency ratios for each month. This section is managed from Taxation > VAT Assumptions from the model ribbon. Different currency ratios can be input for the following months for more accurate forecasts. (For more details, see: 6.1 VAT & CURRENCY ASSUMPTIONS)

Expense Items: On the left hand side of the screen (orange region) we see different expense items. These belong to the Direct Expenses category which can be added as shown at 1.4.2 Add/Remove Category Rows and renamed as shown at 1.5 Admin Sheet.

Creditor Delay: In this section in the middle (yellow region) we can manage the relation between payments and time delays. This will be explained below in more detail.

Direct Labour Input: This input area (green region) is being used for direct labour expenses. (This is another way of inputting employee expenses if users don’t want to use the Payroll Input sheet to enter employees individually. This is explained in more detail in section 3.4 Payroll Management)

Direct Expenses Input: This is the section (purple region) where expense for each category and each month can be entered manually. Expense forecasts should be entered net of VAT and in the month that the income is invoiced. Any timing differences for receiving the invoice from supplier and making the payment is managed with creditor delay, which is explained below.

Once you have input your expense forecasts, you will see various tables below your forecasts. These tables are for audit purposes only, and cannot be edited. They allow you to see the figures flowing correctly through your model and the effect they have on other parts of the FD4Cast model.

3.2.2 HOW TO USE CREDITOR DELAY SECTION?

Similar to the 3.1.2 Debtor Profiles (debtor delay) in the Income section, there can also be time differences between receiving the invoice from suppliers and actually pay them. This section makes it easy to manage these delays.

In the example below, under Direct Expenses: Standard (Excl. VAT) the company has 14 days to pay 100% of its expenses relating to “Raw Material”. Whereas for “Packages” types of expenses the company has 30 days to pay 50%, however 50% of expenses are payable without any delay.

The model calculates a number of financial lines, including VAT and the impact on Direct Expenses (Cashflow) (Incl. VAT) to see how the inputs are reflected throughout the reports. (On the left hand side, you can see the relevant reports with abbreviations in red)


Back To Top

Admin Overheads

In FD4Cast models, expenses are input in two separate sections: Direct Expenses and Overheads. To be clear on the terminology, Overheads are those costs required to run a business, but which cannot be directly attributed to any specific business activity, product, or service (such as rent, office utility bills, advertisement etc.)

3.3.1 ENTERING STANDARD OVERHEADS

Overheads can be added into the model by clicking on ‘Admin Overheads’ on the FD4Cast ribbon, as shown below:

Currencies: At the top of the screen (blue region) you will see the currency ratios for each month. This section is managed from Taxation > VAT Assumptions from the model ribbon. Different currency ratios can be input for the following months for more accurate forecasts. (For more details, see: 6.1 VAT & CURRENCY ASSUMPTIONS)

Admin Salaries: This input area (just below currencies region) is being used for admin salaries. (This is another way of inputting admin salaries if users don’t want to use the Payroll Input sheet to enter employees individually. This is explained in more detail in section 3.4 Payroll Management)

Overheads Items: On the left hand side of the screen (orange region) we see different overheads items. These belong to the Overheads category which can be added as shown at 1.4.2 Add/Remove Category Rows and renamed as shown at 1.5 Admin Sheet.

Creditor Delay: In this section in the middle (yellow region) we can manage the relation between payments and time delays. This is explained under direct expenses, 3.2.2 How to Use Creditor Delay Section?

Standard Overheads Input: This is the section (green region) where overheads for each category and each month can be entered manually. Overheads forecasts should be entered net of VAT and in the month that the income is invoiced. Any timing differences for prepaid/accrued overheads are dealt separately and explained in detail below.

Once you have input your overheads forecasts, you will see various tables below your forecasts. These tables are for audit purposes only, and cannot be edited. They allow you to see the figures flowing correctly through your model and the effect they have on other parts of the FD4Cast model. Other tables here will be dependent on your creditor profiles and timing assumptions (Prepaid/Accrued Overheads).

3.3.2 PREPAID OVERHEADS

Some overheads are not paid monthly but paid upfront for a certain period. For instance, if a company had paid for a whole year’s worth of insurance upfront, the amount paid would be an asset of company that can be recorded as a prepaid overhead. The company can then reverse the associated monthly cost.

In the example below ‘Insurance’ has a prepaid overhead of £12,000 entered in April 2016, representing the upfront payment of insurance for the year ahead. The reversal of the prepaid overhead is then entered over the period that it relates to, in this case it is -£1,000 over the 12 months from April 2016 to March 2017.

The model calculates the VAT (20% = £2,400) on the £12,000 prepayment for ‘Insurance’ and enters £14,400 in Invoiced Overheads (Incl. VAT) and Trade Creditors: Overheads Balance (Incl VAT) in April 2016.

As Insurance has a payment delay of 30 days, the model shows the £14,400 being paid in May 2016.

Prepaid Overheads – Impact on Direct Cash Flow

On the Financial Statements worksheet the model reflects the impact on the company’s cash flow. The Total Cash Flow for May 2016 is -£14,400 reflecting the settling of the invoice for the Insurance prepayment. The Opening Balance is £0, Closing Balance -£14,400 and Bank Overdraft of £14,400.

In June 2016 the company’s VAT Refund Receipts means it has a Total Cash Inflow and Net Cash Flow of £2,400. The Opening Cash Balance is £14,400, Closing Cash Balance is £12,000 and Bank Overdraft now £12,000.

Prepaid Overheads – Impact on Indirect Cash Flow

The prepaid reversal of £1,000 for the 12 months that the prepayment covers is shown as a negative figure in Profit/Loss Before Tax and Operating cash flow before Working Capital Changes.

The Prepaid Overheads of £11,000 in April 2016 shows the difference between the -£12,000 Insurance prepayment and the £1,000 reversal reflecting the Insurance cover for April 2016 only. In the months April 2016 to June 2016 there is £1,000 reversal reflecting the Insurance cover for each month.

VAT Receivables shows the VAT of -£2,400 included on the invoice and then the £2,400 VAT Refund Receipts received in June 2016.

The Trade Creditor: Overheads in April 2016 shows the £14,400 relating to the unpaid invoice and then in May 2016 -£14,400 as the invoice is paid in the month.

The Net Change in Working Capital for April 2016 is £1,000 showing the difference between the Prepaid Overheads -£11,000, VAT Receivables -£2,400 and Trade Creditors: Overheads £14,400.

The Net Change in Working Capital for May 2016 is -£13,400 showing the difference between the Prepaid Overheads £1,000 and Trade Creditors: Overheads -£14,400.

Prepaid Overheads – Impact on Profit and Loss

The reversal of the prepaid overhead is shown under Expenses over the period that it relates to, in this case it is £1,000 over the 12 months from April 2016 to March 2017.

The model calculates the EBITDA (Earnings Before Interest, Taxes, Depreciation and Amortisation) by subtracting total Expenses in row 181 from Gross Profit in row 175. EBIT (Earnings Before Tax and Interest) is calculated by adding EBITDA and Tax Depreciation Charged.

EBIT after Extraordinary Items, Tax Profit/ Loss Before Tax (PBT) Net Profit (PAT) each reflect the -£1,000 prepayment reversal each month.

The Cumulative Net Profit increases each month by -£1,000 until it reaches the end of the prepayment period, in this case it would be March 2017 with a figure of -£12,000 as that is the last month paid for upfront by the insurance period.

Prepaid Overheads – Impact on Balance Sheet

The Insurance Prepaid Overheads is captured under the Company’s Current Assets, and is decreasing by £1,000 per month. The VAT Receivable is also captured as a Current Asset and Total Assets sums all the assets, in this case only Prepaid Overheads and VAT Receivables.

The model calculates the VAT (20% = £2,400) on the £12,000 prepayment for ‘Insurance’ and enters £14,400 in Trade Creditors: Overheads as a Liability of the company in April 2016.

As Insurance has a payment delay of 30 days, Trade Creditors: Overheads reduces to £0 in May 2016, whilst Bank Overdraft increases to show the £14,400 being paid in May 2016. In June 2016 the company’s VAT Refund Receipts receives £2,400, reducing the Bank Overdraft to £12,000.

The Accumulated Reserves increases each month by -£1,000 reflecting the prepayment reversal each month.

Total Liabilities sums all of company’s liabilities, in this case for April 2016 £14,400 relating to Trade Creditors: Overheads and -£1,000 Accumulated Reserves. As the Accumulated Reserves increase the company’s Total Liabilities decrease. In this example by March 2017 Accumulated Reserves is -£12,000 and Total Liabilities is £0.

3.3.3 ACCRUED OVERHEADS

When Overheads are paid in arrears, the anticipated costs can be entered over the period that the overhead relates to and then reversed out when the invoice is received.

In the example below, ‘Other’ has an accrued overhead of £500 in the months April, May and June 2016. This could relate to the company’s electrical bill that is paid 3 months in arrears. The company knows it has incurred a cost which it estimates will be £1,500 when it receives the invoice from the electricity company. The company can reverse this amount when it receives the invoice in June 2016.

The model calculates the VAT (20% = £300) on the £1,500 invoice and records it in Invoiced Overheads (Incl VAT) and Trade Creditors: Overheads Balance (Incl VAT).

As this overhead is listed as Other, that has a payment delay of 30 days, the model shows the £1,800 being paid in June 2016.

Input VAT on Overheads shows the £300 on the invoice received in June 2016.

The Accrued Overheads Balance (Excl VAT), reflects the accrual of £500 in April and then the accumulation of the April and May accrual resulting in a total of £1,000 in May. In June 2016 the amount is zero as the accrual is reversed.

The Report Figures also reflect the figures associated with the accrual.

The Trade Creditors: Overheads shows the £1,800 amount owed to the electrical company in June 2016 and then the Payment to Creditors in July 2016, due to the 30 days payment terms.

Accrued Overheads – Impact on Direct Cash Flow

On the Financial Statements worksheet the model reflects the impact on the company’s cash flow. The Total Cash Inflow for Aug 2016 is £300 reflecting the VAT Refund received.

The Total Cash Outflow for Jul 2016 is -£1,800 reflecting the payment of the invoice relating to the electricity cost.

In July 2016 the company’s Opening Cash Balance is £0, the Net Cash flow and Closing Cash Balance are -£1,800, this represents the payment of the invoice. The Bank Overdraft is £1,800.

In August 2016 the company’s Opening Cash Balance is -£1,800, the Net Cash flow is £300 (VAT refund) resulting in a Closing Cash Balance of -£1,500. The Bank Overdraft is now £1,500.

The VAT Refund Receipts means it has a Total Cash Inflow and Net Cash Flow of £2,400. The Opening Cash Balance is £14,400, Closing Cash Balance is £12,000 and Bank Overdraft now £12,000

Accrued Overheads – Impact on Indirect Cash Flow

The accrued overhead -£500 for the 3 months is shown as a negative figure in Profit/Loss Before Tax and Operating cash flow before Working Capital Changes.

The Working Capital Changes shows the VAT receivable of £300 due to be paid in Jun 2016 and then the refund in Aug 2016. It also shows Trade Creditors: Overheads having a balance of £1,800 in June 2016, reflecting the receipt of the invoice and then -£1,800 in July 2016 reflecting the payment of the invoice.

The Net Cashflow from Operating Activities shows the accrual of -£500 in the months April and May 2016. In June 2016 the amount is £1,000, reflecting the -£500 accrual, -£300 VAT Receivable and Trade Creditor: Overheads £1,800. In July 2016 the Net Cashflow from Operating Activities is -£1,800 showing the invoice being paid and in August 2016 £300 showing the refund of the VAT.

The Net Cashflow reflects the same movements as the Net Cashflow from Operating Activities.

The Opening Cash Balance for April 2016 is £0 with a Closing Cash Balance of -£500, representing the accrual.

In May 2016 the Opening Cash Balance is -£500 and Closing Cash Balance is £1,000, again representing the accrual.

In June 2016 the Opening Cash Balance is -£1,000 with a Closing Cash Balance of £0. This represents the total accrual of -£1,500, VAT Receivable -£300 and Trade Creditors: Overheads of £1,800.

In July the Opening Cash Balance is £0 with a Closing Cash Balance of £1,800, representing the payment of the invoice.

In August the Opening Cash Balance is £1,800 with a Closing Cash Balance of £1,500, representing the £300 refund of the VAT.

Accrued Overheads – Impact on Profit and Loss

The accrued overhead is shown under Expenses over the period that it relates to, in this case £500 over 3 months from April 2016 to June 2016.

The model calculates the EBITDA (Earnings Before Interest, Taxes, Depreciation and Amortisation) by subtracting total Expenses in row 181 from Gross Profit in row 175.

EBIT (Earnings Before Tax and Interest) is calculated by adding EBITDA and Tax Depreciation Charged.

EBIT after Extraordinary Items reflect the £500 accrual each month.

The Cumulative Net Profit increases each month by -£500 until it reaches the end of the accrual period, in this case June 2016 with a figure of -£1,500 as this is the month that the invoice is received.

Accrued Overheads – Impact on Balance Sheet

In this example the only impact on the Company’s Total Assets relates to an entry in VAT Receivable for £300 in June 2016 and July 2016. When the VAT refund is received in August it reduces the VAT Receivable to £0.

The model reflects the impact on the Company’s Liabilities, showing the Bank Overdraft of £1,800 in July 2016 and £1,500 in August 2016.

The model also calculates the VAT (20% = £300) on the £1,500 accrual for the Electricity overhead and enters £1,800 in Trade Creditors: Overheads as a Liability of the company in June 2016. The accrual of £500 is reflected in April and the £500 accrual is added to it in May 2016. The accrual for June 2016 is also added, however the accrued overhead reversal is entered in June 2016 resulting in a £0 for June 2016.

The Accumulated Reserves increases each month by -£500 reflecting the accrued overhead for the electricity cost each month.

Total Liabilities sums all of company’s liabilities, in this case for June 2016 £1,800 relating to Current Liabilities and Accumulated Reserves of -£1,500. When the VAT refund is received in August the Current Liabilities of the Company reduces to £1,500 resulting in Total Liabilities of £0.


Back To Top

Payroll Management

FD4Cast models offers a comprehensive and flexible management interface for payroll records, salaries, employee expenses and so on. For users who don’t want to micro-manage specific employee expenses, the model also offers a general input sheet which is explained below.

3.4.1 PAYROLL MANAGEMENT SCREENS

Payroll details can be input into the model by click on ‘Payroll’ on the FD4Cast ribbon, as shown below:

After clicking on the Payroll button, 2 options  are presented:

General: This is for users who don’t want to micro-manage the employee salaries. Sum of direct labour and admin overheads can be input per month. Report output figures are generated at the bottom of this section.

Specific: This is where user can manage the employee expenses, salaries and taxes in detail.

3.4.2 PAYROLL – SPECIFIC

When Specific option is selected from the Payroll menu, the user is sent to the PayrollEntry sheet.

Please find below detailed explanations of each sub-section:

3.4.2.1 Taxes & Contribution Calculations

This section (red area, top of the screen) is being used to determine three main payroll tax factors:

  • PAYE Rates
  • National Insurance Rates
  • Pension Contributions

The user is able to define thresholds, tax rates to be applied after reaching each threshold, and pension contributions to be applied for each employee. These values are used to calculate the total employee cost to the company besides salaries.

The user is able to add more assumptions for the upcoming years by clicking on the little “+” buttons on the left side of each table:

3.4.2.2 Adding / Removing Employees

In this section (yellow area, middle of the screen) there are two separate buttons for adding and removing employees. When clicked, user is prompted with a popup asking to input the number of employees to be created or deleted:

These new employees will then be added to many tables like salary, headcount, tax calculations, reports and so on.

** An idea for adding employees is to “group” them. You don’t have to add all employees individually. You can create a new employee and name it as “Sales Team” and another employee as “Sales Managers”. After creating employees as groups, the headcount in each employee/group can be defined from the headcounts table. Grouping employees with same characteristics will save the user time compared to separate employees.

3.4.2.3 Employee Type & Info Inputs

In the employee info section (green area, bottom left) the user can define the employee type from the dropdown selection (this affects the tax calculations). Also, employees can be renamed simply from the Name column. Department info is optional and will be used for reference purposes only.

In this section, users can also select when a specific employee left (or is planning to leave) the company. This will affect the total salary calculations.

3.4.2.4 Salaries and Headcounts Table

After adding/removing employees, defining their info we can now add salaries and headcounts for employees. Note that, salaries are added per individual.

Below is an example with grouping logic mentioned previously:

In this example we have added 15 Sales Specialists, 4 Sales Managers and one person (J.Smith) as Director. Specialists and Managers have the same salaries, so we have grouped them together. You can change the employee type, department and leave dates as shown above.

With information populated, all the reports are created automatically:

3.4.3 PAYROLL – GENERAL

When you open this menu, the first thing you will notice is the selection at the top of the screen:

The user can either use the PayrollEntry Sheet to enter precise figures (as explained at 3.4.2 Payroll – Specific) or use this sheet to enter estimated figures. If you don’t want to micro-manageyour payroll, then you can use general estimated figures by selecting “No” from the dropdown.

Please note that, using Payroll-Specific will obviously lead to more accurate results. How much accuracy is needed is up to the user. This is a trade-off after all.

3.4.3.1 General Salary Input

If “No” is selected, then user will input the Net Salaries to the Expense screens:

3.4.3.2 General Employee Tax & Contribution Distribution

After total net salaries are input for the employee groups, the user should define the tax and contribution distribution. This can either be input as general percentages or absolute figures per month:

3.4.3.3 Payment Lag

The last option for general payroll management is: Payment Lag. This simply postpones the salary payments for each month entered in the Payment Lag cell as shown below:

After all inputs are complete (salaries, taxes and payment lag [optional]), the output reports will be generated at the bottom of the Payroll – General screen.

** In theory, if same values are input on both the specific or general payroll management screens, they should give very close figures as output. However, there may be small differences due to the calculation details at Payroll-Specific. That trade-off should be considered and decided by the user.


Back To Top

INPUTS: ASSETS

INPUTS: FINANCE & BS

Loans

FD4Cast calculates interest and repayments on loans, based on the information you enter into the model.

5.1.1 ENTERING LOANS

To enter loan information, go to Finance > Loans.

Loan Items: On the left hand side of the screen (orange region) we see different loan items. These belong to the Loans category which can be added as shown at  1.4.2 Add/Remove Category Rows and renamed as shown at 1.5 Admin Sheet. (You cannot model a new loan on the same line as an existing loan which appears on your Opening Balance Sheet, and therefore will need to create a new line in the category of Loans)

New Loan Inputs: At this section on the top (green region) we can input new loans in the month that loan is drawn. Please note that, this section is for new loans only. All existing loans drawn before the financial period start should be entered in the opening balance (See: 2.1 Opening Balance)

Loan Repayment Inputs: This section on the bottom of the screenshot (blue region) is used to indicate monthly repayments of loans.

Repayments of new and existing loans should be entered here to be taken into account for cash flow and other financial statements. In the example below, you can see both the repayment of new and existing loans:

After you input loans and repayments, the outputs will be automatically reflected in the reports below:

5.1.2 ENTERING LOAN INTERESTS

Loan interests can be input either  as a percentage or an absolute value as shown below:

Interest rate percentages can be input per month to the second table in the screenshot above.

The common method is to use percentages for loan interest calculations. So, the default value for Manual (absolute) loans is FALSE. However, in some cases the user may want to input absolute values per month for interest calculation. In this case:

  • Select “TRUE” for the loan you want to enable manual interest input
  • Enter absolute interest amounts per each month in the table on the right

After making the inputs, these interest payments are reflected on the reports which affect both P&L and Cash Flow Statements as seen below:

5.1.3 Amounts Falling Due Within a Year

You also have the option to define months for distributing loans effect to balance sheet. Using the same figures from previous examples, you can see below the effect of changes for two different scenarios:


Back To Top

Leases

FD4Cast calculates interest and repayments on finance leases, based on the information you enter into the model. Finance Leases work quite similar to the Loans.

5.2.1 ENTERING LEASES

To enter lease information, go to Finance > Leases:

 

Lease Items: On the left hand side of the screen (orange region) we see different lease items. These belong to Finance Lease category which can be added as shown at 1.4.2 Add/Remove Category Rows and renamed as shown at 1.5 Admin Sheet. (You cannot model a new lease on the same line as an existing lease which appears on your Opening Balance Sheet. Instead you need to create a new line in the category of Finance Lease)

New Leasing Inputs: In this section on the top (green region) we can input new leases in the month that lease is drawn. Please note that this section is for new leasings only. All existing leasings drawn before the financial period start should be entered in the opening balance (See: 2.1 Opening Balance)

Lease Repayment Inputs: This section on the bottom of the screenshot (blue region) is used to input monthly repayments of leasings.

Repayments of new and existing leases should be entered here to be taken into account for cash flow and other financial statements. In the example below, you can see both the repayment of new and existing leasings, and the output reports being created accordingly:

 

5.2.2 ENTERING LEASE INTERESTS

Lease interests can be input either as a percentage or an absolute value as shown below:

Interest rate percentages can be input per month to the second table in the screenshot above.

The common method is to use the percentages for lease interest calculations. So, the default value for Manual (absolute) lease interests is FALSE. However, in some cases the user may want to input absolute values per month for interest calculation. In this case:

  • Select “TRUE” for the lease you want to enable manual interest input
  • Enter absolute interest amounts per each month in the table on the right

After making the inputs, these interest payments are reflected on reports which affect both P&L and Cash Flow Statements as seen below:

5.2.3 Amounts Falling Due Within a Year

You also have the option to define the months for distributing lease effect to the balance sheet. Using the same figures from previous examples, you can see below the effect of change for two different scenarios:

 


Back To Top

Factoring

FD4Cast allows the user to model a Factoring arrangement where amounts owed on sales invoices are passed to a factoring company in order to improve cash flow. The Factoring interface can be reached at the FD4Cast ribbon via Finance > Factoring:

In the FD4Cast model, Factoring can be managed in two different sections:

  • Invoice Discounting
  • Working Capital Loan

5.3.1 Invoice Discounting

The first section under Factoring interface is invoice discounting as shown below:

Discount Rate Selection: In top section (orange area) you enter the amount and choose the type of discount to be applied on invoiced sales. Single or Compound discount can be selected.

Income Category Discount Rates: The middle region (green area) is used to determine the ratio of invoices to be discounted. Sales items on the left belong to the Income Category which can be added as shown at 1.4.2 Add/Remove Category Rows and renamed as shown at 1.5 Admin Sheet.

On the right hand side of this section, the income amounts are shown. These values are input/edited in the Income section as explained at 3.1.1 Entering Standard Forecast Income.

Debtor Profiles: On the left side of this region (blue area) the debtor profiles are shown. Debtor profiles are input/edited via the Income sheet as explained in 3.1.2 Debtor Profiles. On the right side of this section we see the discounted invoice values.

After making the inputs, the monthly flow is reflected below:

Also note the Unwinding section on the bottom. The interest rate can be selected for changing monthly unwound amounts.

Presentation Options

There are 2 different presentation options to display the effects of factoring agreements. Using the same values from previous examples, the outputs for two different presentation options are as follows:

5.3.2 Working Capital Loan

This section is located down below the Invoice Discounting section and can be reached by clicking on its link at the top (or simply scrolling down):

In this section, we have income category items coming from the Income sheet. Simply enter ratio of invoices to be discounted and the figures will be generated:

You can also change the annual interest rate as shown above.


Back To Top

INPUTS: TAXES

VAT & Currency Assumptions

 

COMING SOON…

Back To Top

OUTPUTS: STANDARD REPORTS

OUTPUTS: MODEL TOOLS

INFO & FAQ