The Super and Ultra versions of Cashflow Plan incorporate a facility to help consolidate monthly and full-year pro-forma financial projections generated by Cashflow Plan.
Cashflow Plan Consolidator is powerful and flexible and very easy and speedy to use on a regular basis. It uses the acquisition method of consolidation and is aimed at holding companies with wholly- or majority-owned subsidiaries. The main components are as follows:
1. Cashflow Plan contains a Report for Consolidation which is automatically updated whenever a subsidiary’s projections are recalculated. This report comprises the monthly Income Statements (Profit & Loss Accounts), Cashflow Projections (with notes) and Balance Sheets generated by Cashflow Plan.
2. Cashflow Plan Consolidator is a supplementary Excel workbook for consolidating a series of Reports for Consolidation generated by Cashflow Plan. It includes facilities for changing exchange rates and denominations and adjusting for inter-company trading and balances.
Consolidation starts with the completion of a subsidiary’s projections using Cashflow Plan. Its Report for Consolidation is then copied to the clipboard and pasted into a Subsidiary Report within the Consolidator workbook. This process is repeated for further subsidiaries. Then, the Consolidator’s Currency Converter can be used to make currency conversions and the Adjustment Report can be used to exclude inter-company trading and balances from the consolidated Group Report.
Note that projections within the Report for Consolidation are linked to projections elsewhere in Cashflow Plan and automatically updated whenever Cashflow Plan recalculates or rolls forward. This would facilitate production of consolidated rolling projections.
When using Cashflow Plan for consolidation the main options are as follows:
1. Develop projections using Cashflow Plan for all subsidiaries on a single PC and consolidate them there. This requires a single-user license for Cashflow Plan.
2. Develop projections at subsidiary level using multiple copies of Cashflow Plan and submit either Cashflow Plan files containing complete projections or, more simply, the Reports for Consolidation by email to the consolidation co-ordinator. This option requires multi-user licenses for Cashflow Plan - contact PlanWare at <firstname.lastname@example.org> for details of multi-user pricing.
This document serves as a detailed user guide on using Consolidator with Cashflow Plan. It describes the basics of consolidation and explains how currency conversions and inter-company adjustments are made.
You will find two Excel template files for Consolidator (CASH*US.XLT and CASH*UK.XLT) within a folder called Consol below the main folder created when Cashflow Plan was installed (i.e. at C:\CASH*). The * refers to “S” or “U” depending on whether Cashflow Plan Super or Ultra applies.
These templates correspond to the US/Canadian and UK/International accounting format settings within Cashflow Plan. If you use the former in Cashflow Plan, select the CASH*US.XLT file and select CASH*UK.XLT if you use the UK/International format and immediately save the file with an appropriate incrementing name e.g. "Con-1.xls".
Consolidator is already setup to handle two Subsidiary Reports within the worksheets designated "Sub-1" and "Sub-2". You can rename these worksheets to the names of your subsidiaries. To add a further subsidiary, click the button “Insert Sheet for Another Subsidiary”. This will insert a copy of an existing worksheet between the "Adjuster" and "LastSheet" worksheets. Consolidator will automatically aggregate all these worksheets in the Group Report within the “Group” workbook.
Resave Consolidator with a new incrementing file name as your primary working copy.
When using Consolidator, please note the following:
1. When consolidating, you should use Excel to load both Cashflow Plan and Consolidator. They will appear in separate windows within Excel.
2. Cashflow Plan’s special menus and toolbar buttons will not work from within Consolidator. The only exception to this is the “Calculate with check” button which will work within Consolidator.
3. In common with Cashflow Plan, Consolidator uses Manual Recalculation when it is being used at the same time as Cashflow Plan. Therefore, to recalculate press the F9 function key, or a "Calculate Now" button, or the “Calculate with check” toolbar button. However, when Cashflow Plan has been closed, Consolidator will revert to Excel’s automatic calculation setting.
4. As Consolidator makes extensive use of synchronised reports, do not change the structure of Cashflow Plan’s output reports or its Report for Consolidation. Likewise, do not insert, delete or move any rows within Consolidator’s worksheets. Do not modify or delete formulae within the “Group”, “Currency” or "Adjuster" worksheets and do not rename the “Introduction”, "Adjuster" or "LastSheet" worksheets.
5. Unlike Cashflow Plan, Consolidator does not contain any automated tests for unbalanced balance sheets. These have to be checked manually.
6. If the message “File is not valid” appears when attempting to use Cashflow Plan’s toolbar button, it signifies that Cashflow Plan has “lost” its toolbar. To regain it, use the Reset Toolbar Buttons option within the *Setup menu.
To start, load Cashflow Plan with the subsidiary to be consolidated and then load your working copy of Consolidator. It will appear in a second window within Excel. To switch between Cashflow Plan and Consolidator, use Excel’s Window menu. If consolidating projections for several subsidiaries, it is best to open only one copy of Cashflow Plan at a time so as to prevent Cashflow Plan’s menus and toolbar buttons from getting confused as to which copy of Cashflow Plan should be linked to.
There is no limit to the number of subsidiaries that can be consolidated. If use of Cashflow Plan is physically dispersed (and assuming appropriate license have been acquired), complete files containing a subsidiary’s detailed projections can be copied to the co-ordinator of consolidation or, more simply, the subsidiary’s Report for Consolidation can be pasted into a blank workbook and just this file passed to the co-ordinator.
The basics of using Consolidator are as follows:
1. Having finalised projections within Cashflow Plan, go to the Report for Consolidation within the "Con_" worksheet and click the "Copy Report to Clipboard" button. This will activate a macro which will copy the entire report to the clipboard.
2. Immediately, switch to the relevant subsidiary's worksheet within Consolidator and click the "Insert Projections" button. This will run a macro which pastes the report’s values and formats into the worksheet and overwrites any existing projections. Observe that the default option for this task is “Cancel” and that a positive assertion is needed for completion.
When inserting projections, note that the Group Report in Consolidator always uses the projection dates, currency symbol and denomination, and variable descriptions used by the first Subsidiary Report, i.e. that contained in the original "Sub-1" worksheet. Accordingly, use this worksheet for a subsidiary which uses the desired home currency and denomination.
3. Click the "Calculate Now" button to update the Group Report within the “Group” worksheet.
4. Repeat this process for each subsidiary. Open only one copy of Cashflow Plan at a time so as to prevent Cashflow Plan’s menus and toolbar buttons from getting confused as to which copy of Cashflow Plan should be linked to.
If a holding company is to be consolidated, you must create a separate set of projections for it using Cashflow Plan and then consolidate it as per a subsidiary. This will ensure that it is embraced by the Group Report after appropriate inter-company adjustments are made.
If you wish to change the layout (or language) of the Group Report, do not change the supplied report. Instead, insert a new worksheet immediately after “Introduction” and use a combination of copying/pasting text and formats together with simple cell references linking into the consolidated values within the Group Report to construct your bespoke report. This approach will ensure that the new report is updated whenever Consolidator is updated. It can also be used were the Group Report needs to be extended to consider goodwill, minority interests and associated companies in greater detail.
Use the Currency Converter within the "Currency" worksheet to set a conversion factor for exchange rates and currency denominations to convert a foreign subsidiary’s currency and denomination to that for the home currency and denomination (as determined by the currency and denomination used by the subsidiary in the original "Sub-1" worksheet).
Conversion should be undertaken after the foreign subsidiary’s projections have been inserted into Consolidator and before using the Adjustment Report. The procedure is as follows:
1. Insert a currency rate and/or denomination conversion factor into the white cell near the top of the Currency Converter. Any combination of currency rate and/or denomination converter can be used. For example:
· 1000 converts a subsidiary’s denomination from millions to thousands and 0.001 converts from thousands to millions.
· 1.70 converts a foreign currency to the home currency where 1.70 is the exchange rate. This rate could be the latest actual rate, or a forecast average rate for the period covered by the projections, or a forecast rate applicable to the date of the final projected balance sheet. A further alternative would be to unprotect the “Currency” worksheet and overwrite the formulae in the row for Monthly Currency Converter by forecast rates on a monthly basis. Note that, for simplicity, the specified exchange rate(s) will be applied by the Currency Converter throughout the foreign subsidiary’s projected accounts.
· 1700 converts a subsidiary’s denomination from millions to thousands and its foreign currency to the home currency where 1.70 is the exchange rate.
2. When ready, press the “Copy Converter to the Clipboard” button and switch to the Subsidiary Report requiring conversion. Immediately, press the “Apply Currency Conversion” button. This will run a macro which multiples the subsidiary’s projections (i.e. all values appearing within the Subsidiary Report) by the designated conversion factor. Note that the default option for this task is “Cancel” and that a positive assertion is needed for completion.
3. Go the “Group” worksheet and press “Calculate Now” to include the foreign subsidiary’s projections using the home currency and denomination in the Group Report.
Repeat these steps for any further foreign subsidiaries before making any adjustments using the Adjustment Report.
Use the Adjustment Report within the "Adjuster" worksheet to eliminate inter-company transactions and balances within the Group Report.
Be consistent and comprehensive when making adjustments and consider all the knock-on consequences. If desired, explain adjustments in blank cells to right of the report. The process of consolidating can be complicated and may require considerable accounting know-how and experience. If unsure on how to proceed or unfamiliar with relevant accounting requirements and regulations such as IAS GAAP and IFRS then seek professional assistance.
When completing the Adjustment Report only insert values within the cream unlocked blocks - enter increases as positive values and decreases as negative values. It is permissible to also insert formulae linked to other rows within the Adjustment Report or to variables within Subsidiary Reports. The use of formulae instead of values can greatly simplify adjustments and updating of consolidated projections especially if Cashflow Plan’s roll-forward facility is used.
Adjustments should always be compensatory so that the consolidated balance sheets remain balanced (see bottom row of the Group Report). They must also be applied consistently throughout, for example, the same exchange rates used for foreign subsidiaries should also be used when determining home currency values of their inter-company balances and transactions so as to prevent the creation of exchange profits/losses.
Here is a simple example to explain how to eliminate inter-trading between two wholly-owned subsidiaries:
1. Enter minus values in a sales row of the income statement (profit & loss account) within the Adjustment Report to reflect the projected monthly sales between the two subsidiaries.
Better still, enter formulae which link to the subsidiary’s sales e.g. enter “ - SalesGroup * 20%” (note the minus sign at the start of the formula) where 20% is the proportion of sales for a sales group made to the other subsidiary.
2. As sales by one subsidiary are purchase for the other, enter formulae which link to the sales values in (1) above within the cost of goods sold rows to reflect the monthly purchases.
3. In the balance sheet within the Adjustment Report, enter minus values across the receivables (debtors) row (including those for the opening period) within Current Assets to reflect historic and projected receivables (debts) due from one subsidiary to the other.
Instead of entering values, a simple formula could be used to express these receivables as a proportion of the monthly sales adjustments in (1) above. For example, you could enter “ - SalesGroup * 200% * 1.15 “ (note the minus sign at the start of the formula) where 200% signifies that the relevant projected credit balance is typically equivalent to two months’ sales for the sales group and 1.15 is an adjustment which applies sales taxes at 15% (or VAT, GST etc.) to these net sales.
4. As receivables for one subsidiary are payables for the other, enter formulae which link to the projected receivables in (2) above within the payables (creditors) row in Current Liabilities.
5. Further adjustments might be needed to take account of goods- or cash-in-transit; transactions not yet invoiced; inter-company expenses; unrealised profits on unsold inventories arising from inter-company purchases; and unrealised profits on inter-company sales of fixed assets.
If desired, you could replace the factors used in this example (i.e. 20%, 200%, 1.15) by variables located in a row below the Adjustment Report.
If there had been cross shareholdings or loans between the subsidiaries, then other adjustments would have been needed to take account of inter-company dividends, loan interest, investment values, loan balances, minority interests, goodwill, post-acquisition reserves, group taxation and so on. Similar adjustments would have been needed if one of the companies had been the holding company rather than a subsidiary.
Generally speaking, no adjustments will be needed to the cashflow reports provided that all significant cash-related transactions and their timing have been projected on a consistent basis when Cashflow Plan was used to make projections for all the related companies. For example, if two subsidiaries are inter-trading, the projected receivable (debtor) terms (specified in the second table within the RecPay worksheet within Cashflow Plan) relating to this trade for one subsidiary should be similar to those used for the other subsidiary’s payables (creditors). Likewise, the timing and amounts of loan repayments and interest by a subsidiary should match those used in the lender’s projections.
Additional copies of the “Adjuster” worksheet can be created and used provided that they are located after the “Adjuster” and before "LastSheet". These would be most useful where several subsidiaries are inter-trading or where extensive adjustments are needed for the holding company. Alternatively, the “Adjuster” worksheet could be extended below the Adjustment Report to include a series of schedules corresponding to rows in the Adjustment Report and containing detailed workings for subsidiaries with the schedule totals linked back into the appropriate rows within the Adjustment Report.
Consolidator is an integral part of Cashflow Plan and licensed for distribution and use with Cashflow Plan under the terms and conditions set out in the Agreement for Commercial/Registered Version, Additional License Terms for Shareware Version and Additional License Terms for Distribution of the Shareware Version contained in the accompanying LICENSE.TXT file.
Note that derivatives of the Consolidation file can be freely copied and distributed provided they are populated using Cashflow Plan. However, this does not exempt the need for different users of Cashflow Plan to have their own licensed copies of Cashflow Plan.
V1.3 – First releases of Consolidator to suit release 1.3 of Cashflow Plan Super and Ultra.
Contact details for the author of Cashflow Plan Consolidator are as follows:
27 Ardmeen Park
Tel: +353-1-283 4083
Fax: +353-1-278 2391