Help

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 Loss, Balance 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.

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.

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.

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.

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.

One of the first steps in configuring the model is to select Model Financial Year Start Date & Base Trial Balance Date. For detailed instructions on how to configure the model please, Review Section 1.4 Model Setup and Configuration.

In our example, forecast period starts on April 1st, 2017. Therefore, we need to import account balances as of March 31st, 2017 into the model.

Base Trial Balance period for our model is March 2017. We need to enter account balances of that period in Enter TB Section from Opening Trial Balance group on the ribbon.

At configuration stage we added rows to each account category. All of those items are listed in this section in Column B. In Column G users can enter base period trial balance figures. This can be done either manually or by using the Mapping column. Mapping column allows the user to match each account with a unique identifier (possibly, account code internally used by the company). Then any lookup function can be used to import corresponding values of each identifier into column G.

After importing the trial balance figures for the base period, it is possible to review the overall results at the top of TB Input Section:

Model makes it very easy for the user to replace forecast figures with actual results after each period is completed.

In our example from Section 2.1 Opening Balance base trial balance period was March 2017. Forecast income for April 2017 was £12,844.

In order to override forecast figures with actual results, first it is necessary to change the Base Trial Balance Date from Admin Sheet to 30/04/2017.

Next, we enter new trial balance for updated period into the TB input section under corresponding month. We can use the same mapping system as during the initial trial balance input.

After the trial balance update for April, we can see that actual profit for the period – £221,750 is reported in the Statement of Profit & Loss and it has replaced the forecast figure. Other financial statements will be linked to Trial Balance input section as well for any periods up to the Base Trial Balance Date on Admin Sheet.

Short term receivables and payables that are included in the base Trial Balance need to be manually unwound over the forecast periods. Unwound amounts are entered in corresponding periods in the Unwind TB Section.

Asset categories that are manually unwound include Trade Receivables, Accrued Income & Prepayments.

Liability categories include Trade Payables, Deferred Income, Accrued overheads & Other short term creditors.

Latest trial balance figures are imported into the Base column in this Section. As we can see, receivables from Sales A at the end of April were in the amount of £337,000. We have entered £300,000 of this amount in the yellow cells according to our cash receipt projections. £37,000 of the receivables has not been unwound yet.

Please, note that Assets are reported as positive and liabilities as negative figures in this Section.

To review in details what balances remain to be unwound on each accounts and how unwound amounts have impacted the financial statements in each period, users can review TB Unwound Section from Opening Trial Balance Group. This section is for information purposes only and no user input is required.

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.

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)

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.

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:

• Direct Type employee salaries should be entered in the Direct Expenses screen (as explained at 3.2.1 Entering Direct Expenses):

• Admin Type employee salaries should be entered in the Admin Overheads screen (as explained at 3.3.1 Entering Standard Overheads)

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.

Both Current and Fixed Assets can be accessed from the Assets’ section on FD4CAST ribbon:

At the top of the Fixed Assets section you can find the VAT rates that apply to purchases and sales of assets. VAT rates can be managed from Taxation > VAT Assumptions on the ribbon. For more details on how to manage the tax rates, please, review Section 6.1 VAT & Currency Assumptions.

4.1.1 Fixed Asset Purchases and Depreciation

Fixed Assets are grouped into three categories – Investments, Intangible and Tangible Assets.

All fixed assets, with the exception of investments, are subject to depreciation. Keep in mind that both purchases and depreciation figures should be entered as positive numbers in yellow cells. Assets in this section are reported net of VAT as shown on the Balance Sheet.

Depreciation for assets brought forward from before the forecasting period should also be reported in this section. E.g. figures shown under ‘Tangibles – Depreciation’ section above relate to fixed asset balances from previous periods as shown below.

Instructions on how to manage initial balances can be reviewed in Section 2: Inputs Balance.

4.1.2 Fixed Asset Revaluations

Model allows users to perform revaluation of fixed assets. Both upward and downward revaluations are reported in section below:

Investment Revaluation gains/losses (+/-) are reported in P&L under the line item, ‘Investment Revaluations’:

Revaluation gains/losses (+/-) from tangible and intangible fixed assets are not reported in P&L. They are reflected directly on the Balance Sheet under ‘Revaluation Reserves’. Reversals of previous revaluations will offset this Equity item directly, bypassing the P&L.

4.1.3 Payments for Fixed Asset Purchases

In our model it is assumed that payments for Fixed Assets take place in the month of acquisition. In the example below, payments for £10,000 worth of investments and £20,000 worth of intangibles take place in May-17 and Jun-17, in the same period as they were purchased. In order to reschedule payments, user will need to unprotect the model and manually apply the new payments schedule. Please, review Section 8.6 – Unprotect Model to review the instructions.

Keep in mind, that applicable VAT rate is automatically added to the cost of tangible fixed assets in the above section.

Above transactions are reflected in the Statement of Cash Flows (both direct and indirect), under the item, ‘Purchase of Fixed Assets.’

4.1.4 Fixed Asset Disposals

In order to correctly reflect the sale of fixed assets, we need to have the following three pieces of information:

• Original Cost of an Asset;
• Net book Value (=Original Cost – Accumulated Depreciation);
• Proceeds from asset disposal.

As an example, let’s assume that in the beginning of Aug-17, we plan to sell Vehicles with an original cost of £15,000 and the accumulated depreciation of £7,200. Net book value is £7,800 – the difference between the above two figures. The sales price is £9,000 including VAT.

We enter the above information in corresponding sections under Fixed Assets:

Proceed of Asset Disposals:

Original Cost of Assets Disposed:

Net Book Value of Disposed Assets:

Disposal of fixed assets has an impact on all three financial statements. In the Statement of Cash Flows we can see inflow of £9,000 – proceeds from the sale of Vehicles including VAT:

Vehicles, after they have been sold, do not appear on the Balance Sheet starting from Aug-17:

The Statement of Profit & Loss shows net loss on sale of fixed assets in the amount of £300 – difference between Proceed from disposals net of VAT (£7,500) and Net book value of Vehicles (£7,800).

Stock section allows the user to plan inventory levels in accordance with the monthly income stream and supply chain requirements.

4.2.1 Input of Planned Inventory Levels

Users can access the inventory module by clicking on ‘Stock’ button under the ‘Assets – Fixed / Current’ group. At the top of the screen all the revenue streams are automatically imported into this section. This information simplifies planning of stock levels in line with incomes of the corresponding period.

In our example below, we have two types of stock: Stocks1 & Other. Let’s assume that £2,060 worth of Stocks1 is used to generate £41,200 of sales A income and £50 worth of stock is necessary to generate £1,000 of Other income in the month of May-17. In order to add other inventory types to the list, please review Section 1.4.2: Add / Remove Category Rows.

On the left hand side, under the target Inventory Level we can insert a number of months. In our example below we plan to hold 3 months’ worth of stock at each period end.

4.2.2 Inventory Purchases

In the Target Inventory Level Section below, model automatically calculates how much stock we need to purchase in each period in order to keep them at planned levels.

E.g. in the month of May-17, we have target inventory level in the amount of £6,558 and consumption need of £2,060. With no beginning inventory, model tells us that we need to purchase £8,618 worth of stock during the given period in order to maintain targeted stock level.

4.2.3 VAT on Stock & Credit Terms

We can select an applicable VAT rate on purchases for each type of stock by using the drop down list next to ‘Purchases (Including VAT)’ section. VAT rates can be managed from Section 6.1 VAT & Currency Assumptions.

In our example below, £10,342 is the gross value of purchases of Stock1 in the month of May-17.

Model allows the user to easily manage the credit terms for each type of stock. We can insert a number under ‘Credit Period’ heading to indicate the number of months by which we can delay the payments for the purchases made in each period. E.g. payment for £10,342 worth of stock purchased in May-17 will be made with one month’s delay as indicated by us in yellow cells under the heading ‘Credit Period’. Therefore, this amount will appear on our Balance Sheet as part of Inventory Account Payables at the end of May.

Cash deposits section under Assets group is a very important part of the model as it incorporates information from almost all other sections.

At the top of the page you can see the summary of current account turnover. Beginning cash balance – £50,000 comes from the trial balance input, discussed under Section 2: Inputs Balance. The rest of the information comes from within the Cash Deposits section. Below we will discuss each item separately.

4.3.1 Net Receipts and Payments

On the Net receipts and payments row you can see the difference between cash inflows and outflows on current account from almost all business transactions. The details for this line item can be reviewed in section, ‘Current Account Net Receipts and Payments’. It is linked to cash receipts/payments section of almost all other items in the model.

As an example, you can see that net inflow of £242,274 in May-17 consists of cash collections from standard income minus all the cash outflows of the month, including payments for inventory purchases, wages & fixed assets.

4.3.2 Transfers to / from current account

‘Transfers to / from current account’ shows cash movement between current account and the deposits. This information is manually entered in the Cash Deposits section below.

In our example, we have two deposits that earn 3% annual interest on a monthly basis. Interest rates on deposits can be modified by changing yellow cells under the heading ‘Interest Rate’.

Let’s assume that we added £10,000 to the Dollar deposit in July and £10,000 to the Euro deposit in September. Transfers to the deposits are entered as positive figures. Keep in mind that in this section we show GBP value of each deposit account irrespective of their original currency.

Deposits can be added to or removed from the list from the Admin Sheet, section ‘Cash Accounts’. Please, review 1.4.2 Add / Remove Category Rows for detailed instructions.

4.3.3 Transfers to / from holding company

Transfers of cash to and from the holding company are managed through the special section below.

If our company is not part of the holding company or does not engage in cash transfers with the holding company, we can select ‘FALSE’ from the drown down list next to ‘Transfer to and from Holding Company’.

In order to activate manual input section for target cash balance for current account, we should select ‘FALSE’ from the drop down list next to ‘Use Maximum and Minimum Cash Balance’:

After we select ‘False’, cells next to ‘Manual input of target cash balance’ become active. In our example above, monthly target cash balance for our company is £100,000. The difference between current cash balance and the above amount will be transferred to the holding company. E.g. cash balance at the end of May before holding company transfers was £297,774. If our target cash level is £100,000, we can transfer the difference – £197,774, to the holding company.

In case our company’s target is to hold cash levels within a specific range, we can select ‘True’ next to ‘Use Maximum and Minimum cash balance’ and indicate lower and upper limits of cash levels in the yellow cells below:

As we can see, we have set the desired cash level on current account between £15,000 and £50,000. Accordingly, the model calculated the amount that we will transfer to/from the holding company to maintain the cash balance between the given range for each period.

4.3.4 Interest on Current Account and Overdraft

We can manage interest receipts on current account balances or interest payments on overdraft by going back to the summary section of current account.

In yellow cells we insert annual interest rates. Model automatically calculates monthly interest income/expense based on closing net cash balance of the previous period.

Current Account and Deposit balances have an impact on the Balance Sheet. They are reported under ‘Cash at bank & in hand’ and ‘Cash Deposits’ respectively.

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:

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:

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.

Model allows the user to easily manage InterCompany transactions from the InterCompany Section in the Balance Sheet Group on the ribbon.

At the very top of the InterCompany section, opening balances of receivables/payables (+/-) with group companies are automatically imported from trial balance input.

In our example, the opening balance of £10,000 receivables is related to the holding company.

In order to add other group companies to the list, please review Section 1.4.2 Add/Remove Category Rows.

5.4.1 Adjustments to Group Company Receivables/Payables

Net cash inflows/outflows to and from group companies are managed in Cash Deposits section. Final net figure for each month is imported into the current, Inter Company section. For more details on how to manage target net cash flow with the holding company, please, review Section 4.3.3 Transfers to / from holding company.

In our example above, target net cash flow with the holding company in the month of May is -£247,774. In combination with the beginning balance of £10,000, we get £257,774 of receivables balance with the group companies at the end of the period. This figure is fixed as it is our target balance with the group companies for the end of May-17.

Yellow cells for receivables and payables enable the user to reflect any adjustments to the net cash flows with the holding company. This feature can be used for financial statement consolidation purposes. E.g. if £15,000 out of £247,774 transferred to the holding company was in transit by the end of May-17, we will enter -£15,000 as an adjustment to receivables.

As a result, net cash outflow to the holding company will be reported under two separate headings on the Statement of Cash Flows: Surplus cash paid to Group Company (this amount will be used for consolidation purposes) & Net Group Companies Adjustments that will be reported as cash outflow from financing activities for the period.

In another example, if the company received £10,000 from the group company we can enter this amount in Adjustments to Payables. Increase in payables to group companies is automatically offset by increase in receivables. This way our target net cash flow with the group companies remains unchanged.

Increase in payables will be reflected on the Balance Sheet under ‘Amount owed to Group Undertakings’:

All equity transactions of the company are managed through the Equity section from the Balance Sheet group on the ribbon.

5.5.1 Share Capital & Premium

At the very top of the Equity section user can reflect issuing of new shares, their face value and total proceeds from the transaction.

In the example above, face value per share issued is £1.00. Total number of shares is 1,000 as entered in yellow cells next to the heading, ‘Number of Shares Issued (Redeemed)’. (In case of redeeming old shares, their quantity and amount should be entered as negative figures.)

If total proceeds from share issuance is £500,000, £1,000 (face value per share x number of shares) will be reported under Share Capital on the Balance Sheet. The difference between the total proceeds and the face value – £499,000, will be reported as Share Premium.

In the same Equity Section we can see the reconciliation of opening and closing balances for both Share Capital and Share premium accounts.

In our example, opening balances of Share Capital and Share premium are £100 and £10, respectively. These amounts come from Opening Trial Balance input section. For detailed instructions on how to bring forward opening balances, please, review Section 2: Inputs: Balance.

5.5.2 Dividends

Declaration and payment of dividends are managed from the Dividends section.

Declared dividends are entered as positive figures in yellow cells above. It is assumed that dividend payouts take place in the same period as they are declared. In order to reschedule payments, user needs to unprotect workbook as explained in Section 8.6 Unprotect Model.

Declared dividends are reported in P&L below the Profit After Tax line item:

Payment of dividends are shown on the Statement of Cash Flows under Financing Activities:

5.5.3 Revaluations Reserve

Revaluation of fixed assets and goodwill are reflected in Equity section under Revaluation Reserves. Any subsequent adjustments to the initial revaluations are also reflected here.

Initial recognition of goodwill as well as subsequent revalued amount is entered into the below section. Keep in mind that in case of revaluating initial goodwill, user must enter the ending value for the period rather than the amount of adjustment.

In the example above, goodwill is initially recognized in the month of July. In September it is revalued downward to £1,500. We enter revalued amount in yellow cell in the corresponding period.

Fixed Asset revaluations are also reported under Revaluation Reserve section on the Balance Sheet. For detailed instructions on how to revalue fixed assets, please, review Section 4.1.2: Fixed Asset Revaluations.

5.5.4 Accumulated Profits/Losses

Accumulated profits/losses are reported under Profit and Loss Account in Equity Section. This is the same as retained earnings account.

Any receivables and liabilities not reflected in other sections can be shown in Adjust B/S section under Balance Sheet Group.

5.6.1 Other Receivables

In the upper part of the section, user can reflect increase/decrease (+/-) in Other Receivables. In our example above we increase receivables in May by paying out £1,000 to the debtor. In July debtor repays us £500.

In the Statement of Cash Flows under the line item, ‘Other Assets Collections’ we can see initial payout in May as well as receipt from the debtors in July.

On the Balance sheet we can see the increase of £1,000 in Other Current Assets in May. Subsequently, there’s decrease of £500 in July reflecting the cash receipt from other debtors.

5.6.1 Other Liabilities

In Other Liabilities section we can reflect any adjustments of other payables.

In the example above, we have the inflow of cash in the amount of £2,000 in May-17. Subsequently, company pays out £1,000, reducing the debt to other creditors.

Both transactions are reflected in the Statement of Cash Flows under the line item, ‘Other Current Liabilities Payments.’

On the Balance sheet, under Other Creditors section we can see the increase in Other Creditors in May. Subsequently, the payables are reduced to £1,000 reflecting the cash outflow in July.

VAT and Corporate Income Tax rates as well as currency assumptions are managed from the Taxation group on the ribbon.

6.1.1 VAT Assumptions

On the VAT Assumptions page user must first configure the VAT rates that could apply to different kinds of transactions throughout the model.

In the VAT Config table yellow cells are used to list all different VAT rates. User can list up to 5 different rates, one of them being Zero VAT Rate. In our example above, VAT Rate 1 is 20%, VAT Rate 2 – 10%, etc.

As you can see, after defining each VAT rate once, it automatically applies to all forecast periods at the top of the section.

After configuring the VAT rates, we can apply them one by one to each income, expense, overhead and fixed asset type separately using the drop down lists in yellow cells. E.g. let’s say VAT Rate 1 applies to Sales A. VAT Rate 1 is 20%, as configured earlier. That means, for each £120 of Sales A, £20 will be accrued as output VAT.

In order to review how different VAT rates effect the overall projections, please, review Section 3: Inputs: Income & Expenses & Section 4: Inputs: Assets.

6.1.2 VAT Payment Configuration

Users can easily configure VAT payments schedule from the VAT Assumptions section.

It is possible to assign fixed monthly payments for the VAT account. Model also allows the user to modify monthly payments for each period separately by unprotecting the model and manually entering the information in row ‘VAT Payments on Account (unprotect sheet to over-ride)’. For detailed instructions on how to unprotect the model, please review Section 8.6: Unprotect Model.

In the VAT Payments Configuration table we can apply general settlement and payment terms:

• Frequency of Account Settlements – the frequency at which our obligation of settling VAT accounts arises. User can select 1 for monthly settlement, 3 for quarterly settlement and 12 for annual settlement.
• First Payment Month of Calendar Year – In our example above, we have selected 1 for January, meaning that first VAT payment took place in that period. Therefore, the next payment will take place three months later, in April.
• Settlement Payment Lag (months) – The number of months it takes to pay the VAT after settling the accounts.
• Settlement Payment Lag (months) – The number of months it takes to receive the VAT refund after settling the accounts (in case the VAT account balance is negative).

We can see the effect of our configuration in Taxation >> VAT Calculations Section:

VAT accounts are settled once every three months. Payment is made one month after the settlement.

6.1.3 Currency Assumptions

Model allows the user to easily manage foreign currency exchange rates from the VAT Assumptions Section:

All foreign currency values must be expressed in terms of GBP. As shown in our example above, we enter the forecasted value of 1 GBP in EUR (1.1) and USD (1.3) in yellow cells in Currency Configuration table. These rates are automatically applied to all periods of our forecast. If we want to apply different rates to individual periods, we must first unprotect the model (as explained in Section 8.6: Unprotect Model) and then, manually enter exchange rates under corresponding months.

VAT Calculations section can be accessed from the Taxation Group on the ribbon:

In this section user is not required to enter any information. It is for information purposes only.

At the very top of the section we can review the Output VAT accrued on both regular and other income (e.g. sale of fixed assets).

Below we can see the Input VAT – paid above all types of direct and indirect costs and deductible from the Output VAT.

VAT accrued for the period is calculated as the difference between Output and Input VAT. E.g. In the month of July output VAT was £18,121 and the deductible VAT was in the amount of £2,461. Their difference – £15,660 is VAT payable for the period.

In order to calculate the outstanding VAT balance at the end of July, we take beginning balance, add VAT payable for the period and subtract any payments that took place during the month. The final amount – £817 is our closing balance for this account. Payment will take place at the end of August according to our initial configuration of VAT payments. For more details on this topic, please review Section 6.1.2 VAT Payment Configuration.

VAT payments are reported in the Statement of Cash Flows under VAT Payments. VAT refunds, if any, are shown under a separate line item VAT Refund Receipts.

VAT payable is shown on the Balance Sheet under Wages, Taxes and Social Security Costs:

Corporation Tax section from the Taxation group allows the user to plan tax rates for forecasted periods, make adjustments to taxable profits and schedule payments.

6.3.1 Corporation Tax Rates

At the top of Corporation Tax section user can assign rates to each forecast period. E.g. For the year ending on April 1st 2017 our forecast for corporation tax rate is 20%.

On the same page, user can enter number of months by which payment of tax expense for the year can be delayed. In our example, number of credit months is three.

6.3.2 Taxable Profit Adjustments

In the Statement of Profit & Loss we could have included some expenses that are not deductible for tax purposes. At the same time, certain types of income may be tax exempt.

In the above section user can adjust profit before tax by adding back non-deductible expenses or subtracting non-taxable income. There are specific lines that can be used for specific items (e.g. Income from Disposals). Yellow cells can be used for any Other adjustments not listed in above lines.

Corporation tax is calculated once a year from the total taxable profit.

In our model, tax expense is divided by the number of forecast months and the result is reported in P&L as tax provision in each period. In the example above our monthly tax provision is £12,541 (tax provision for the year / number of forecast months).

It is possible to adjust corporate tax provision in several different ways:

User can enter early payments towards corporate tax account. This will not change overall tax expense, but will reduce the Corporation Tax liability on the Balance Sheet

User can adjust tax expense of any period by entering adjusting figure in the line item, ‘Corporate Tax Refunds (Override)’. This will reduce/increase (+/-) company’s cumulative tax expense for the period:

By making the adjustment in ‘Refunds Received’ line item, user can increase tax payable amount without increasing tax provision in P&L:

Financial Statements of the company can be accessed from the View Outputs group on the ribbon. All throughout the previous sections we have reviewed how specific items, like income & expenses, fixed assets, loans, leases, etc. impact all three financial statements. In this section we will discuss their structure and content.

7.1.1 Profit & Loss Statement

At the very top of Financial Statements Section, we have a Statement of Profit & Loss which can be reviewed by expanding the corresponding group rows. P&L consists of several account groups: By subtracting Direct Costs from Income, we arrive at Gross Profit.

Cumulative Net Profit is calculated by subtracting Overheads, Depreciation, Extraordinary Items, Interest Expense, Corporation Tax and any Declared Dividends from Gross Profit. Each account and its calculation can be accessed by clicking on corresponding Source hyperlink.

7.1.2 Balance Sheet

Balance Sheet consists of Assets and Liabilities. Assets are divided into long term and current assets. Liabilities include long term and short term liabilities as well as the Equity. Total Assets and Total Liabilities balances must be equal for each period.

7.1.3 Statement of Cash Flows

Both Direct and Indirect Cash Flows are included in Financial Statements Section.

In Direct Cash Flow, total cash inflows are subtracted from total cash outflows to arrive at net cash flow for the period.

Total Cash Inflow:

Total Cash Outflow:

Indirect Cash Flow shows Profit/Loss Before Tax for the period. This amount is adjusted with non-cash items, non-operating expenses and working capital changes to arrive at Net Cash Flow from operating activities.

Cash flows from Investing and Financing activities are the same for Direct and Indirect Cash Flow formats.

Profit & Loss Report from the View Outputs group shows more detailed breakdown of account groups compared to the P&L in Section 7.1.1: Profit & Loss Statement. However, the overall structure and content are the same in both reports.

Any declared dividends are deducted from Profit After Tax to arrive at Retained Profit for the Year. Retained Earnings Carried Forward is calculated in this section and imported into the Balance Sheet under Capital & Reserves account group.

Profit & Loss Statement in this section is reported on a monthly basis. For Quarterly financial results, please, review P&L Sum worksheet.

Balance Sheet from the View Outputs group is more detailed than the Balance Sheet in Financial Statements section.

In this section Net Assets are calculated as the difference between Assets and Liabilities.

Net Assets are equal to Shareholders’ Equity for any given period.

For quarterly Statements of Financial Position, user must review BS Sum worksheet.

Statement of Cash Flows can be accessed from the View Outputs group on the ribbon. In this section cash flow is prepared using the direct method.

The difference between net cash flows calculated in this section and in Section 7.1.3 Statement of Cash Flows may come from intra group transactions. Group company transactions are not included in this section. Therefore, this report can be used for the purposes of group company financial statement consolidation.

Quarterly Cash Flow Statements can be reviewed from CF Sum worksheet:

Dynamic Charts from the View Outputs group allow the user to better visualize various business trends.

Standard bar charts present the trends of Cash Inflows and Outflows, Cash Balance, Cash Balances with Group Companies, Income, Costs and Gross & Net Profit.

User can easily modify the period covered in charts by changing Start date and End Date in yellow cells from the top of the section.

Frequency stands for the period represented by one bar in standard charts. It can be selected from the above drop down list.

Model enables the user to export final reports to a separate workbook. It is also possible to create multiple budgets and scenarios within the model.

As discussed in Section 7 Outputs: Standard Reports, all three financial statements can be reviewed by clicking on corresponding buttons in the View Outputs group:

Because the Statement of Profit and Loss, Balance Sheet and Cash Flow Statement are the final outputs of our model, we often need to review & communicate them separately without including the entire model.

We also may need to export Budgets of all three Financial Statements as well as the Scenarios to excel workbooks. For instructions on how to create budgets and scenarios, please, review Section 8.2 Creating Budget & Section 8.3 Scenario Analysis.

Outputs can be exported into a workbook by clicking on Reports Budgets > Export Reports.

In case there are multiple budgets and scenarios in the model, we will see them all listed under Budgets & Scenarios, respectively. User can select which reports to export by ticking the corresponding box.

The resulting output is the excel workbook with all the previously selected reports on separate worksheets.

FD4Cast Model comes with a built in budgeting tool for all three financial statements. Existing budgets can be accessed by clicking on corresponding worksheets (P&L_Budget, BS_Budget & CF_Budget).

For Year-To-Date periods, actual trial balance input is compared with initial forecast. For Year-To-Go periods, forecast is compared with the budget. In the built in model, budget figures equal to forecast figures and variances are zero.

User can modify budgeted figures within the existing worksheets. It is also possible to add new budget versions. Adding new versions can be a useful tool for creating dynamic budgets throughout the year – user can save old forecasts, but also create new ones on a regular basis.

New Budgets can be created by accessing Reports group on the ribbon: Reports Budgets > Create Budget.

After choosing to create a new budget, user has the option to either Erase and Replace existing budget, or Create a new one.

By choosing ‘Yes’, all Year-to-date budget figures in the existing budget are updated to actuals, so that the variances actual vs. budgeted equal to zero.

By choosing ‘No’, new budget worksheets are created, where existing variances are copied. User then has the possibility to reflect changes by entering new projections in Year-To-Go budgets without modifying older versions.

In forecasting it is often necessary to modify one or more assumptions and see the effect of change on the financial results of the company. Scenario Analysis tool enables the user to create different scenarios of Profit & Loss Statement, Balance Sheet and Cash Flow statement within the model.

Let’s assume that our initial forecast of Corporation Tax Rate for the year starting on April 1st,2017 is 20%:

Corresponding Profit After Tax shown in P&L is the following:

During forecasting exercise user may want to see what the profit for the year will be if the Corporation Tax Rate is 15% instead of 20%.

For this purpose, it is possible to extract current output reports into Scenario 1. Afterwards, change the corporate tax rate to 15% for the year starting on April 1st, 2017 & export the results into Scenario 2. (For instructions on how to manage corporation tax rates, please review Section 6.3: Corporation tax)

In order to extract current outputs into a scenario, user must go to Reports Budgets > Create Scenario:

As a result, three additional worksheets are added to the model (P&L_Scn_1, BS_Scn_1 & CF_Scn_1).

They show the financial results of the company under the assumption that corporation tax rate for the current year is 20%.

For creating Scenario 2, we change applicable tax rate to 15% for the current year from Taxation > Corporation Tax and extract the resulting financial reports into Scenario 2:

Scenario 2 shows £586,294 Profit After Tax vs. £551,806 in Scenario 1. The increase is due to the reduction in corporate tax rate assumptions from 20% to 15%.

It is possible to create multiple scenarios by changing multiple types of assumptions (income, expenses, fixed asset purchases & disposals, tax rates, etc.) in the model.

The resulting scenarios can be exported into a separate excel workbook as described in Section 8.1: Export Reports.

Model has a built in functionality for detecting errors throughout the model.

This functionality can be accessed from Check / Tools group > Audit CheckSheet.

On the left hand side, we have listed all the worksheets that are in the model. Next to each worksheet title TRUE indicates that there have been no errors detected.

On the right hand side, we have Custom checks. For example, Balance Sheet Check calculates the difference between Net Assets and Equity. If the result is zero, there haven’t been any errors detected in the model and we see TRUE next to the check.

If there is an error in the model, we will see FALSE next to the corresponding section. We will also see Top left cell’s fill color change to red in all sections throughout the model. User can investigate the error by clicking on a hyperlink next to the section in question.

Blank Sheet can be added to the model by clicking on Add Blank Sheet from Checks / Tools Group.

Blank sheet will be added to the right of Admin Section with the default name of Assumptions1. It is possible to rename the sheet.

Blank Sheet will come with default formatting and column names that are in line with all other sections in the model. However, user can easily modify default inputs as all excel functionalities are active for this worksheet.

By default, all formulas and calculations in the model are protected. This is helpful for preventing users from accidentally deleting or modifying the data, which might lead to errors in outputs.

When trying to access a specific formula, the following message comes up.

However, sometimes it is necessary to change formulas to accommodate the specific requirements of the user. This can be done by first unprotecting the model from Unprotect Model in Checks / Tools group.

After unprotecting the model, all Microsoft Excel functionalities are activated in the model. User can modify formulas, hide/unhide rows & columns or add/delete specific data.

COMING SOON…

COMING SOON…

COMING SOON…