Financial Projection Software:
FAQs for Users

Tips:

1. This is a long page - allow time for it to fully load.
2. Use your browser's search or find facility to locate key words.
3. Save this page on your PC in your Exl-Plan folder (EXL**) using File | Save as ...
4. This page is regularly expanded - Bookmark and revisit.
5. Suggestions for additional FAQs are very welcome via this Contact Form.
6. For more FAQs, see Frequently Asked Questions for Newcomers.
7. Exl-Plan contains extensive online help (130+ topics) along with a 90-page manual (Word or PDF format) and a series of Flash-based tutorials (running for 19 minutes) covering setting up Excel, getting started, the basics and using Quik-Plan. Manuals and tutorials are included in the download files for all versions of Exl-Plan.
8. If you still have a query or problem, see Support for Users.
9. This FAQ is mainly for the Micro, Lite, Pro, Super, Super Plus, Ultra and Ultra Plus versions and the answers may not be fully applicable to the Exl-Plan Basic and Free versions.

1. Getting Started

These FAQs augment the extensive guidance on using Exl-Plan contained in the online help, the 90-page manual (Word or PDF format) and a series of Flash-based tutorials (running for 19 minutes) covering setting up Excel, getting started, the basics and using Quik-Plan which are supplied with all version of Exl-Plan.

These FAQs are based on the menu system used by Excel 5, 7, 8, 95, 97, 2000, XP and 2003. If using Excel 2007, 2010, 2013, 2016 with its new Ribbon interface, you may wish to consult the interactive Excel 2003 to Excel 2007 and 2010 command reference guide (within Excel 2007, 2010, 2013, 2016's help) or get it on-line (or as a download) from Microsoft here.

Q. How do I configure Exl-Plan to handle a service or distribution business?
There is no need to configure anything. As supplied, Exl-Plan can accommodate a manufacturing, service or distribution business. To handle the latter, you simply ignore (i.e. set values to zero or empty after clearing assumption reports) the assumption variables which are irrelevant to your business type (e.g. finished inventory). For more detailed guidance, see the topic/appendix "Adapting Exl-Plan to Different Business Types" in help/manual.

To automatically suppress the printing of empty rows in output reports, select *Setup | No empty rows printed. If desired, you can manually hide rows in an assumption report which are never used by unprotecting the sheet (press the "U" toolbar button), displaying row headers (Tools | Options | View), selecting the rows to hide and using Format | Rows | Hide. Note: Never delete unused rows or formulae.

Q. How do I decide on the best approach to using Exl-Plan?
Have a look at the sections of the manual or help topics dealing with Structuring a Model and Adapting Exl-Plan to Different Business Types. Start by entering basic, high-level assumptions to get an insight into how Exl-Plan works and then progressively add detail and/or make simple changes to Exl-Plan - see Making Simple Changes in the manual or online help. If you need further guidance, use this Contact Form.

Q. When should I create separate models (files) for each business unit within a single company?
If the business units are relatively small and there is extensive internal trading or shared resources, the best approach might be to treat all the business units as a single entity and use one of the more powerful versions of Exl-Plan such as Super, Super Plus, Ultra or Ultra Plus which handle multiple revenue/cost streams. If the business units are relatively independent with their own balance sheets, it would make sense to create separate models (i.e. Exl-Plan files) for each unit and then consolidate the key results to create a single corporate plan within a separate Excel workbook. See reply further down to the question "How can I consolidate Exl-Plan projections for several subsidiary companies?" for a detailed guide to this procedure.

Q. How do I use Exl-Plan to forecast external funding needs?
Build your projections (sales, costs etc.) but, initially, exclude any assumptions about external funding. Run Exl-Plan and review the rows in the monthly, quarterly and annual balance sheets relating to Line of credit (Overdraft). The peak values in these rows will indicate the external funding need. The next question is whether the funding peak can be lowered and what is the optimum type, terms etc. of funding - use Exl-Plan to explore alternative scenarios. Check out our White Papers about Managing Working Capital, Making Cashflow Forecasts and Preparing Financial Projections.

Q. Can I use Exl-Plan to produce more than one set of projections?
Yes, provided that the licensed copies of Exl-Plan are all used on the same PC. There is no limit to the number of models that can be created - simply use new file names.

Q. What changes does Exl-Plan make to Excel and Windows?
When installed, Exl-Plan creates an install log and sets up a facility for uninstalling Exl-Plan (using Control Panel | Add/Remove Programs). When Exl-Plan runs within Excel, it makes some minor changes to the operation of Excel. These include hiding row/column headers, setting the calculation method to manual, and showing only the standard toolbar along with Exl-Plan's own toolbar. Most changes are removed automatically when an Exl-Plan file is closed and the balance disappear once Excel is re-started.

Q. Any problems if I use a comma (",") instead of a point (".") as the decimal symbol?
You may encounter a divide by zero message whenever you recalculate even though Exl-Plan contains no such errors. Better to switch to using a point as the symbol via Control Panel, Regional Settings.

Q. Can I use Exl-Plan as an accounting system?
In theory, yes. In practice, no. Not recommended!

Q. Can I overwrite Exl-Plan's projections with actuals?
In theory, yes. In practice, with difficulty. Refer to Updating Projections with Actuals in the manual or online help. A much better use of Exl-Plan in this context is to do analysis of variances by placing year-to-date actuals alongside the original projections - refer to Incorporating Actuals with Projections in the manual or online help. An extension of this approach would be to create reports which combine actuals for the year-to-date with projections for the remainder of the year. Alternatively, have a look at the Super, Super Plus, Ultra or Ultra Plus versions of Exl-Plan which has a facility to produce updated first-year projections of sales and profitability by combining year-to-date actuals with projections for remaining months of the year.

Q. Can I change variable descriptions?
Any variable descriptions shown in blue (i.e. unlocked) can be directly overwritten and the change will be replicated by formulae in all other worksheets. For many other variables, minor changes in wording are possible provided the sense of the variable remains unchanged (e.g. change Long-Term Loans to LT Debt).

Q. How do I change references to VAT (Value Added Tax) to GST (Goods & Services Tax) ?
For all US/Canadian editions of Exl-Plan, you can globally change references to VAT, Sales Taxes, GST etc. by entering an appropriate text string in the designated cell within Monthly Assumption Report No. 8. If you are using release 2.5 (or higher) of any version of the UK/International edition of Exl-Plan, you can change the references via a designated cell within Monthly Assumption Report No. 1. If you have an earlier release of the UK/International edition, use the following procedure to make a global change:

1. Go to the M_S worksheet (Monthly Assumption No.1) and unprotect all worksheets via the *Protection menu (ignore the request for a password).
2. Right click on the M_S tab and choose the option to Select All Sheets.
3. From the Edit menu, select Replace ..., enter VAT and GST in the appropriate text boxes and choose Replace All.
4. Right click the M_S tab and choose Ungroup Sheets.
5. Restore protection to all worksheets via *Protection.

Q. How do I change the start date for projections, business name or main sales groups?
This facility may not available in all trial versions of Exl-Plan. In the fully-operational version, use the *Tools/Setup | Basic Info ... menu options to change start dates, business name and type, and descriptions of the main sales groups (two for Lite, four for Pro, six for Super & Ultra and ten for Super Plus & Ultra Plus).

Q. How do I change the start year for projections to show a year later than 2004?
This issue may only arise if you are using a release of Exl-Plan earlier than 2.5. See Notes on Extending Start Year Beyond 2004 for guidance on several solutions - use the back button on your browser to return to this page.

Q. Why does Exl-Plan fail to update values whenever I enter an assumption?
By design, Excel's automatic recalculation facility is turned off in all versions of Exl-Plan. To recalculate a model, select the menu option *Tools/Setup | Calculate All & Check or click the button with the "tick" within the red/green circle. To recalculate the current worksheet, select the menu option *Tools/Setup | Calculate Sheetor click the button with the "X" within the circle.

Q. Can I change Exl-Plan's print orientation to landscape?
Yes, you can do this by changing the Page Setup. We originally thought of offering users the option to specify the orientation for each report but opted against this because many of Exl-Plan's reports would spill over into several landscape pages. This gets very messy when collating for presentations and reports. Our recommendation to users who need different layouts etc. is to photocopy Exl-Plan's reports to secure the desired orientations, paper sizes, changed left margins for binding etc. etc. For example, where the text on some reports is too small, the best solution is to photocopy onto A3 paper (equivalent to double letter size) and then fold these pages into reports etc. as pull outs. However, if desired, File | Page Setup can be used to change almost any feature of a printed report including orientation, header/footer, margin widths etc. These are best done on a worksheet or report-by-report basis rather than by selecting several worksheets and making grouped changes.

Q. Should I always run Exl-Plan by clicking on its menu option or icon within the Start menu or Program Manager?
This option/icon is set up during installation of Exl-Plan and can be used to load Excel plus the supplied EXLPLAN.XLS file. It is really intended only for first-time use. Once Exl-Plan has been fully installed, you should access Exl-Plan by running Excel and then use its File | Open dialog to load Exl-Plan files, usually in the C:\EXL* folder or directory. This suggestion is consistent with our advice that users should make regular copies of their Exl-Plan files, to ensure minimal reworking in the event of system/software problems, using file names (e.g. PLAN-1.XLS, PLAN-2.XLS and so on).

Q. You supply Exl-Plan as a 5.0/7.0 Excel file, can I convert it to the Excel 97 or 2000/XP/2003 file format?
Yes. We supply the file in the 5.0/7.0 to suit all versions of Excel from 5 upwards. If you are only using Excel 97 or 2000/XP/2003, it is a good idea to convert it to the latest format (i.e. using File | Save As ...). This will help eliminate the problem of losing the Exl-Plan toolbar (see below) and will give you access to additional features included with the latest versions of Excel.

Q. How can I show row/column headings & grid lines?
When Exl-Plan loads, it turns off these items to maximize the display of reports etc. To turn them on, select Tools | Options | View and then tick the appropriate check boxes. Exl-Plan releases 2.0 or higher incorporate a menu option (within *Tools/Setup) to perform these functions.

Q. How can I see the formulae inside Exl-Plan?
Select View | Formula Bar and move the cursor to the relevant cells. You may also wish to turn on the row & column headings (see answer to previous question).

Q. Can I replace my very old copy of Exl-Plan with the latest release?
If you are a paid-up registered user of Exl-Plan, you are welcome to secure a copy of the latest release. To do this, send us an email or use the Contact Form giving the following details about your original purchase of Exl-Plan:
  (a) your registered user name and/or organization.
  (b) your current version (i.e. Basic, Micro, Lite etc.) and edition (US/C or UK/I) of Exl-Plan.
  (c) approximate date (or serial number) of the invoice/delivery note or confirmation email sent
       by us when you originally purchased Exl-Plan. Note that the Upgrade Code from the
       FRONT worksheet of your existing version of Exl-Plan is not suitable as confirmation of
       purchase.

Alternatively (or in addition) you may wish to consider trading up to a more powerful version of Exl-Plan at a specially reduced price (US$20 plus difference in price between existing and proposed version. VAT and other sales taxes may apply). Review the detailed description of the Exl-Plan range to identify the most appropriate version to meet your changing needs. Get full details on trading up here. Once you have traded up, you can also continue using your original version.

Top of Page

Buy & Use Now

Download

2. Specific Issues

These FAQs augment the extensive guidance on using Exl-Plan contained in the online help, the 90-page manual (Word or PDF format) and a series of Flash-based tutorials (running for 19 minutes) covering setting up Excel, getting started, the basics and using Quik-Plan which are supplied with all version of Exl-Plan.

These FAQs are based on the menu system used by Excel 5, 7, 8, 95, 97, 2000, XP and 2003. If using Excel 2007, 2010, 2013, 2016 with its new Ribbon interface, you may wish to consult the interactive Excel 2003 to Excel 2007 and 2010 command reference guide (within Excel 2007, 2010, 2013, 2016's help) or get it on-line (or as a download) from Microsoft here.

Q. How do I use the euro symbol "€" within Exl-Plan?
The display of the euro symbol "€" by Exl-Plan is an operating system, printer and Excel issue. Assuming that you have the proper operating system, updated printer fonts and a compliant version of Office there should be no problems. If this is not the case, you may wish to refer to following Microsoft pages:

https://www.microsoft.com/windows/euro.asp
https://www.microsoft.com/technet/treeview/
default.asp?url=/euro/Default.asp
https://www.microsoft.com/typography/faq/faq1

If the euro symbol has been installed but you have no dedicated key for it on your keyboard, you can use either of the following keystroke combinations:

CTRL+ALT+4
ALT and enter 0128 on the numeric keypad

If these keystrokes do not display the euro symbol, then it has not been fully installed on your system. If you cannot upgrade your system or printer to display the symbol, the fall back is to use the abbreviation EUR.

To enter the euro symbol as the base currency for Exl-Plan, select *Setup | Enter Basic Model Info and use one of the above key combinations to enter the symbol. Note that the symbol may display as a thin vertical line on some worksheets due to their zoom settings. However, it will display correctly when printed and/or the zoom setting is increased.

If you insert a formula like "=$333" into a cell, this will be displayed by Excel as "$333". However, if you insert a formula like "=€333" into a cell, you may get an error message. The work around is to create a custom format via Format, Cells, Number as follows:
For Excel 95: From within the "Category" window, select "Custom" or "Currency", choose an appropriate format code, replace its currency symbol by the "€" symbol and press OK.
For Excel 97 and higher: From within the "Category" window, select "Currency", choose the "€" symbol from the pull down list and press OK.

Q. How do I run two Exl-Plan models at the same time?
In theory, you could run Excel and then simply load the two Exl-Plan files. However, this can lead to Exl-Plan's toolbar getting confused !! A much better solution is to load the two Exl-Plan files into separate copies of Excel (i.e. run Excel twice).

Q. How do I produce monthly projections extending beyond one year?
If you need monthly projections for two or three years, you should consider upgrading to Exl-Plan Ultra or Ultra Plus which create monthly projections for three years, quarterly projections for the following two years and,optional, annual projections for a further two years. Alternatively, you can use any other version of Exl-Plan to generate two-year monthly projections by creating the first-year projections using one Exl-Plan file and using a second file to handle the second-year and subsequent year projections. You will need to either file link or directly insert the first-year's P&L and closing balance sheet data from the first file into the second file.

Q. I'm using Exl-Plan Pro, Super or Super Plus and cannot generate projections for the fourth & fifth years?
If you have entered assumptions for all five years, Pro, Super and Super Plus will display the fourth-fifth year projections provided annual sales are projected to increase/decrease for the fourth-fifth years. By design, they assume that no projections are being made for the fourth-fifth years if there are no changes in sales for these years. If projections are required and there are no planned changes in sales, the work around is to enter very small values (e.g. 0.0001) as the annual increases on the 45_A worksheet. Similar rules apply to the Ultra and Ultra Plus versions for sixth/seven years.

Q. Could you explain in more detail about the Seasonal Index and the facility for allocating assumptions?
The Seasonal Index appears in certain Monthly Assumption Reports (M_S, M_M, M_C and M_O worksheets). It is used in conjunction with the menu option *Assumptions | Seasonal Assumptions to allocate an annual assumption value across months in accordance with a seasonal (monthly) profile defined by the user in the Seasonal Index. Let us illustrate its use with an example: The first row in the table below sets out a Seasonal Index as entered by a user. This index could relate to any seasonal factor and its monthly total need not equal 100%. It could be derived from historic data or reflect expected trends. The second row shows how annual sales of 1,000 are automatically allocated using the Seasonal Assumptions facility based on the values specified for the Seasonal Index. This was done by placing the cursor in the cell for Jan's sales, selecting the *Assumptions | Seasonal Assumptions menu option and entering the value 1000.

Once the Seasonal Index has been defined, it can be used to allocate any monthly assumptions (sales, labor, inventory, costs, energy usage etc.) within a specific report.

When using Ultra and Ultra Plus, the Seasonal Index extends over 36 months. For further information, check its manual or online help.

Q. How can I tweak Exl-Plan to handle cashflows which are exceptional or out-of-synch with expenses?
If using the Pro, Super, Super Plus, Ultra or Ultra Plus versions, you can defer payment of most expense items for up to four months by inserting integers (1, 2, 3 or 4) in the boxed single cells adjacent to the variable descriptions in the M_C and M_O sheets. This means, for example, that you can show an item as an expense in January but defer payment until May. If you are using Micro or Lite (or need to handle exceptional cashflows relating to capital or operating expense items in Pro, Super, Super Plus, Ultra or Ultra Plus), use the variables Changes (+/-) in Prepayments or Changes (+/-) in Accruals in the M_F sheet to either accelerate or defer the related cash payments. To see how this works, enter a single easily recognizable value as a change in prepayments or accruals in M_F and then trace its impact in the Monthly Cashflows and Balance Sheets. Instead of entering simple numeric values in M_F, you could write complex formulae to link the changes in prepayments or accruals to variations in other variables.

Q. How can I make copies of selected reports in Exl-Plan for non-users?
We suggest four methods of copying Exl-Plan's reports for non-users. See also the answer to the next question which deals with incorporating Exl-Plan reports into a Word document.

Method 1 - Create an Adobe PDF file
This is probably the ideal method as it can create a very attractive, compact, easy-to-access version of an Exl-Plan file. Excel 2007, 2010, 2013, 2016 have built in utilties to facilitate this. Basically, when saving an Exl-Plan file, just select *.pdf as the fie type.

Also, if you temporarily set Excel's default printer to Adobe PDF (via File, Print, Printer), you will be able to use Exl-Plan's Print menu to produce PDFs for individual reports or batches e.g. all monthly assumption reports.

Method 2 - Copy & Paste Sheets
Only use this method if it is essential to create a values-only file. See below for an alternative (and much better) method. Bear in mind that it is not possible to supply a copy of an Exl-Plan model file to a unlicensed user for technical and legal reasons. To make values-only copies of selected worksheets/reports for distribution to non-users as Excel files, proceed as follows:

1. Load the relevant Exl-Plan file into Excel.
2. Create a new workbook file using Excel (File | New ...).
3. Within Exl-Plan, unprotect and select a report to be copied and then copy it to the clipboard using Edit | Copy.
4. Switch to the new workbook file and use Edit | Paste Special ... Values to paste in the selected values (do not use Edit | Paste as this will paste links).Without changing the selected range, use Edit | Paste Special .... Formats to paste in the formats from Exl-Plan (again, do not use Edit | Paste as this will paste links).
5. If using Excel 2000 (or higher versions), use Edit | Paste Special .... Column Widths to paste in the column widths used in Exl-Plan (do not use Edit | Paste as this will paste links). Otherwise, set the column widths manually (for most reports, columns C-E & S should be hidden).
6. Repeat steps 3-5 for other reports.

This procedure will not work for the charts within Exl-Plan as the charts will want to maintain their links back to Exl-Plan. Instead, select a chart within Exl-Plan and copy it to the clipboard, paste it into any Word document as a picture (using Edit | Paste Special ...) and then re-copy this picture from Word into Excel

Method 3 - Copy Worksheets
For technical and license reasons, it is not possible to simply email an Exl-Plan XLS file to an unlicensed user. However, there are two workarounds. Obviously, the first option is for the unlicensed user to acquire their own licensed copy of the exact same version/edition of Exl-Plan. The second option is to create a static copy of some/all of the Exl-Plan workbook. We confirm that this would not infringe the Exl-Plan license agreement. The procedure is as follows (for Excel 97/2000/XP/2003):

1. Select all relevant worksheets in the Exl-Plan file as a single group. Do this by selecting the tab for the first worksheet (e.g. FRONT), pressing SHIFT key and then selecting the tabs corresponding to the worksheets to be copied. All the selected tabs will now appear in white (instead of the normal dark color).
2. Right click on any of the whitened tab and select Move or Copy...
3. In the Move or Copy dialog, select the (new book) option within the pulldown menu for To book:; tick the Create a copy box; and then press OK. This process creates a new workbook called BOOK*.XLS. incorporating all the selected worksheets with their values and formatting intact and with some (but not all) of the underlying formulae.
4. From within the original file, right click on any whitened tab and select the option to Ungroup Sheets.
5. Save the new workbook file with a more appropriate file name. It is now ready for emailing. The following critical instructions should be given to the recipient:
   • When loading the file a message will appear indicating that "The workbook you opened contains automatic links ....". Press the No or Don't Update button in order to to keep the existing information.
   • Do not change any values or formulae within the file.
   • Do not press F9 to recalculate, or change the workbook from manual to automatic calculation.

Method 4 - Convert to Web Pages
With Excel 2000 (or higher versions) you can convert an entire Exl-Plan file into a series of web pages which can be uploaded to the Internet or Intranet as explained below.

1. Unprotect all worksheets within the Exl-Plan file via *Protection | Unprotect All Worksheets. Ignore the request for a password and click OK.
2. Select all worksheets within the Exl-Plan file as a single group. Do this by selecting the tab for the first worksheet (e.g. FRONT), pressing SHIFT key and then selecting the tabs corresponding to the worksheets to be copied. All the selected tabs will now appear in white (instead of the normal dark color).
3. Right click on any of the whitened tab and select Move or Copy...
4. In the Move or Copy dialog, select the (new book) option within the pulldown menu for To book:; tick the Create a copy box; and then press OK. This process creates a new workbook called BOOK*.XLS. incorporating all the selected worksheets with their values and formatting intact and with some (but not all) of the underlying formulae.
5. From within the original file, right click on any whitened tab and select the option to Ungroup Sheets.
6. Switch to the newly created file and delete the FRONT worksheet by right clicking on its tab and selecting Delete from the pop-up menu.
7. Finally, save this file as a series of web pages via File | Save as Web Page .... Be sure that Entire Workbook is checked and that Add Interactivity is unchecked. Give the web pages an appropriate title. Once saved as web pages, the Exl-Plan results (within a newly created *.htm file and folder of the same name, for example, automatically called BOOK*.HTM and BOOK*_FILES respectively by Excel) can be published on the web.

To convert a single Exl-Plan report into a web page the procedure is as follows:

1. Go the report and unprotect it using the "U" toolbar button.
2. Select File | Save as Web Page .... Be sure that Selection:Sheet is checked and that Add Interactivity is unchecked. Give the web page an appropriate title and file name. Once saved as a web page, the Exl-Plan report (within a newly created *.htm file and folder of the same name, for example, automatically called PAGE*.HTM and PAGE*_FILES respectively by Excel) can be published on the web.
3. Repeat #1 and 2 to convert further reports into web pages.

See also the next question about pasting the Textual Summary Report into Word.

Q. How do I paste the Textual Summary Report into Word?
The Textual Summary Report contains self-modifying text, mini-tables and charts. It was designed to serve as a stand alone document that could be easily printed from within Exl-Plan. It runs to about 12 pages when printed in portrait.

Here are two alternative ways to export it into Word once projections have been finalised for incorporation in a written business plan etc.:

1. Select and copy the entire Textual Summary Report and then paste it into Word. Change the page orientation in Word from portrait to landscape to accommodate the wide tables and charts.
2. Select and copy the entire Report and use Paste Special - RFT format to paste it into Word using the portrait orientation. Then, copy each of the report's charts and paste them using Paste Special - Picture into the appropriate blank slots within the Word report.

Q. How can I insert reports and charts from Exl-Plan into a business plan being prepared with Word?
You can copy and paste any reports or charts from Exl-Plan into a Word document by creating links. As a result, any changes to assumptions in Exl-Plan will be automatically reflected in the report or chart inserted into the business plan. You will need to watch out for unlinked values which you quote as text in Word and which are not automatically updated. The only drawback is that you will break the links as a result of changing the Exl-Plan file name when creating incremental backup files of your Exl-Plan model. (Note: This is recommended as a safeguard in the event of errors creeping into the Exl-Plan model.)

Alternatively, you might prefer to paste reports and charts into the Word document as static pictures. Obviously, this should be done once Exl-Plan's projections are absolutely final. To paste, copy the required report or chart in Exl-Plan and then use Word's Edit | Paste Special … | Paste | Picture (depending on your version of Word, make sure that 'float over text' is not ticked) to insert the item into the Word document at the appropriate location. Some tips:

1. You will be able to resize the pasted picture by clicking on it and moving its corner handles.
2. Generally, you will want the pasted in picture to wrap in line with text. To do this, right click the pasted table and select Format Object | Layout.
3. Many of the tables and charts in Exl-Plan's Textual Summary Report are ideally sized for pasting directly into a Word document.
4. To paste any of Exl-Plan's wider tables (e.g. for 12 month projections) into a Word document, you will need to re-orientate it so that fits vertically within an A4- or Letter-size page. To do this, copy the table, paste it into Microsoft Paint, select Image | Flip/Rotate and rotate by 270 degrees and then paste the rotated table into Word. This approach works extremely well and is very professional looking. The only drawback is that it may result a very large Word file which could be a problem if the file needs to be emailed without being compressed. Each rotated picture may consume about three megs before compression.
5. Before editing text or hiding/unhiding rows/columns within an Exl-Plan report to prepare it for copying in a Word document, save the file with a new name in case any unrecoverable mistakes occur. You must then unprotect the sheet that is being revised (press the "U" toolbar button). Finally, display column/row headers via Tools | Options | View to simplify the selection of rows and columns to be hidden.

Q. Could you explain the cash flow calculations for capital expenditure for the fourth/fifth years within Exl-Plan Pro, Super and Super Plus, and for sixth/seventh years in Ultra and Ultra Plus?
These comments do not apply to Release 2.1 or higher (see below). In the fourth year, the cashflow for capital expenditure comprises any residual payments relating to capital expenditure incurred in the third year plus 50% of the projected capital expenditure for the fourth year. For the fifth year, the cash flow for capital expenditure comprises 50% of the assumed capital expenditure for the fourth year plus 50% of the assumed capital expenditure for the fifth year. For both years, the closing balance sheets show the amounts of capital expenditure outstanding. As these allocations of cash flow may not suit all users, the procedure for changing the relevant formulae is as follows:

1. Go to Q_A2 and unprotect it using the "U" toolbar button.
2. Display the row and column headers using Tools | Options | View.
3. Select column S and the narrow space to the right of it and unhide all hidden columns using Format | Column | Unhide. Select columns S-T and use Format | Column | Width ... to set their widths to 9.
4. Remove "/2" (ignore the quotes) within the formulae in columns T and U in the row for Capital Expend Payments. This will ensure that all capital expenditure planned for the fourth and fifth years are fully paid within their respective years. If you seek to have only 80% of projected Capital Expend Payments paid within each year, replace the "/2" by"*0.8" (without the quotes).
5. Select column S and change its width to 1. Select column T and change its width to 0.1.
6. Select column U and all columns to its right and hide them using Format | Column | Hide.
7. Protect Q_A2 using the "P" toolbar button.

For Release 2.1 (or higher) of Pro, Super and Super Plus, 85% of fourth-year capex is paid within that year and only 15% is carried over as a liability into the fifth year. Likewise, 85% of the fifth-year capex is paid within that year and the outstanding 15% is shown as a liability in the fifth year's balance sheet.

In the case of Ultra and Ultra Plus, 85% of sixth-year capex is paid within that year and only 15% is carried over as a liability into the seventh year. Likewise, 85% of the sixth-year capex is paid within that year and the outstanding 15% is shown as a liability in the seventh year's balance sheet.

If you wish to change the timing of these capex payments, proceed as follows:

1. Go to Q_A2 and unprotect it using the "U" toolbar button.
2. Display the row and column headers using Tools | Options | View.
3. Select column S and the narrow space to the right of it and unhide all hidden columns using Format | Column | Unhide. Select columns S-T and use Format | Column | Width ... to set their widths to 9.
4. Modify the timing of Capital expenditure payments by changing the value (0.85) within the formulae in columns T and U. For example, change the 0.85 to 1.0 to make capital expenditure and related payments both occur in the same year.
5. Select column S and change its width to 1. Select column T and change its width to 0.1.
6. Select column U and all columns to its right and hide them using Format | Column | Hide.
7. Protect Q_A2 using the "P" toolbar button.

Q. Could you explain the cash flow calculations for dividends and grants for the fourth/fifth years within Exl-Plan Pro, Super and Super Plus, and for sixth/seventh years in Ultra and Ultra Plus?
These comments do not apply to Release 2.1 or higher (see below). Any dividends outstanding from the third year are paid in the fourth year. Dividends declared in the fourth year are paid in the fifth year and dividends declared in the fifth year are treated as a liability in the fifth year's balance sheet. The phasing of grant payments is identical to that for capital expenditure (see previous question).

For Release 2.1 (or higher) of Pro, Super and Super Plus, one-third of fourth-year dividends are paid within that year and two-thirds are carried over as a liability into the fifth year. Likewise, one-third of fifth-year dividends are paid within that year and the outstanding two-thirds are shown as a liability in the fifth year's balance sheet. In the case of grants (applicable to UK/International editions only), 85% of fourth-year grants are received within that year and just 15% is carried over as an asset into the fifth year. Likewise, 85% of fifth-year grants are received within the year and the remaining 15% is shown as an asset in the fifth year's balance sheet.

For Release 2.1 (or higher) of Ultra and Ultra Plus, one-third of sixth-year dividends are paid within that year and the outstanding two-thirds are shown as a liability in the sixth year's balance sheet. In the case of grants (applicable to UK/International editions only), 85% of sixth-year grants are received within that year and just 15% is carried over as an asset into the sixth year. Likewise, 85% of seventh-year grants are received within the year and the remaining 15% is shown as an asset in the seventh year's balance sheet.

If you wish to change the timing of these dividend (or grant) payments, proceed as follows:

1. Go to Q_A2 and unprotect it using the "U" toolbar button.
2. Display the row and column headers using Tools | Options | View.
3. Select column S and the narrow space to the right of it and unhide all hidden columns using Format | Column | Unhide. Select columns S-T and use Format | Column | Width ... to set their widths to 9.
4. Modify the timing by changing the value (0.33) within the formulae in columns T and U for Dividend payments. For example, change 0.33 to 1.0 to make dividend declarations and payments both occur in the same year. If using UK/International edition, apply a similar treatment to grants and their payments.
5. Select column S and change its width to 1. Select column T and change its width to 0.1.
6. Select column U and all columns to its right and hide them using Format | Column | Hide.
7. Protect Q_A2 using the "P" toolbar button.

Q. What is the significance of the VAT or taxes adjustment for the fifth quarter in the Quarterly Cashflow Statements (Q_CF)?
This adjustment refers to VAT, GST or other input/output taxes. It is related to the transition from monthly cashflow projections for the first year to quarterly projections for the second and subsequent years. In the first year, a user specifies the payment frequency whereas this interval is fixed by design at quarterly for the second and subsequent years. No adjustment is made if first-year payments are made in the first, fourth, seventh and tenth months.

Q. What is the easiest way to create annual projections for the first year with Exl-Plan?
First-year projections are usually generated on a monthly basis. If you simply need annual projections for the first year, use the Monthly Assumption Reports and utilize the data-entry facilities for allocating annual values (over the twelve months) and for entering constant monthly values where pro-rata is inappropriate (e.g. for interest rates). These facilities are accessible via the Toolbar and *Assumptions menu. Of course, it would also be possible to use Quik-Plan to create high-level, first-year projections.

Q. How do I change the timing of corporate tax payments relating to profits?
Because Exl-Plan was designed to accommodate projections starting at any month of the year, it cannot anticipate the timing of corporate tax payments relating to profits as calculated in its income statements (or profit and loss accounts). Instead, Exl-Plan contains built-in assumptions about tax payment dates as explained below. It is not difficult for anyone with minimal Excel expertise to modify these these assumptions. Before doing so, make a back-up copy of any work in progress.

Notes:

1. The US/Canadian and UK/International editions of Exl-Plan Ultra and Ultra Plus (release 2.5 or higher) allow users to set the timing of tax payments. For more information, see the supplied README.DOC file for more information.
2. For other versions/editions and earlier releases of Ultra and Ultra Plus, the procedure for changing formulae is explained below. Note that the relevant sheet must first be unprotected (via the "U" toolbar button).

For Micro, Lite, Pro, Super and Super Plus:
As supplied, these versions of Exl-Plan handles corporate taxes as follows: Tax liabilities in the opening balance sheet can be paid in any month during the first year by entering the appropriate values within the first table in the M_B sheet. Tax liabilities incurred during the first year are assumed to accumulate in the balance sheet during that year and to be paid in the second quarter of the second year. Likewise, tax liabilities incurred during the second year are assumed to accumulate in the balance sheet and to be paid in the second quarter of the third year.

To change the timing of corporate tax payments relating to trading in the first year, it is necessary to modify one (or more) formulae used in the Monthly Cashflow Projections (M_CF) for "taxes paid" (fourth item down in the Cash Payments section). To do this, change the formula for the appropriate month(s) by adding a specific value or a cell reference (incorporating a formula* which links into the corporate tax calculations in the M_IS or M_PL) to the very end of this formula to reflect the amount of tax to be paid in the specified month. For example, if the formula =IF(Calc!$X$XXX>1,M_B!XXX+M_F!XXX,M_B!XXX) for a certain month is changed to =IF(Calc!$X$XXX>1,M_B!XXX+M_F!XXX,M_B!XXX+100), a tax payment of 100 will be made in that month. Note that the XXXs in the formulae above are simply intended to reflect the format of the formulae. There is no need to make any changes in the Monthly Balance Sheets as tax liabilities will be automatically reduced by other formulae to reflect the newly specified payments.

* If you create formulae to make advance tax payments, you may inadvertently also create a circular calculation which could have effect of locking up calculations within Exl-Plan. The solution is simply to allow Excel to do calculation iterations via Tools | Options | Calculation. The reason for the circular calculation is that an advance payment of taxes reduces cash balances (or increases borrowings) during the year. This reduces interest earned (or increases interest paid) for the year and, in turn, this reduces profitability and taxes due.

To change the timings for the second and third years, the approach is to insert the amounts of tax to be paid into the second Quarterly Assumptions Report (Q_A2) at the row designated "tax payments" (located near the top of the worksheet). To do this, simply overwrite any formulae or dash sign (" - ") appearing for the last eight quarters by a cell reference(s) or value(s) reflecting the projected tax payment(s). There is no need to make any changes in the Quarterly Balance Sheets as tax liabilities will be automatically reduced to reflect the newly specified payment(s).

For Ultra and Ultra Plus (before release 2.5):
As supplied, these versions of Exl-Plan handles corporate taxes as follows: Tax liabilities in the opening balance sheet can be paid in any month during the first three years of projections by entering the appropriate values within the first table in the M_B sheet. Tax liabilities incurred during the first year are assumed to accumulate in the balance sheet during that year and to be paid in the sixth month of the second year. Tax liabilities incurred during the second year are assumed to accumulate in the balance sheet during that year and to be paid in the sixth month of the third year. Tax liabilities incurred during the third year are assumed to accumulate in the balance sheet during that year and to be paid in the second quarter of the fourth year. Tax liabilities incurred during the fourth year are assumed to accumulate in the balance sheet and to be paid in the second quarter of the fifth year.

To change the timing of tax payments within the first three years, it is necessary to modify one (or more) formulae used in the Monthly Cashflow Projections (M_CF) for "taxes paid" (fourth item down in the Cash Payments section). To do this, change the formula for the appropriate month(s) by replacing the very last cell reference in this formula by either a specific value or a cell reference (incorporating a formula* which links into the corporate tax calculations in the M_IS or M_PL) to reflect the amount of tax expected to be paid in the specified month. For example, if the formula =IF(Calc!$X$XXX>1,M_B!XXX+M_F!XXX,M_B!XXX+Calc!XXX) for a specific month is changed to =IF(Calc!$X$XXX>1,M_B!XXX+M_F!XXX,M_B!XXX+100), a tax payment of 100 will be made in that month**. Note that the XXXs in the formulae above are simply intended to reflect the format of the formulae. There is no need to make any changes in the Monthly Balance Sheets as tax liabilities will be automatically reduced by other formulae to reflect the newly specified payments.

* If you create formulae to make advance tax payments, you may inadvertently also create a circular calculation which could have effect of locking up calculations within Exl-Plan. The solution is simply to allow Excel to do calculation iterations via Tools | Options | Calculation. The reason for the circular calculation is that an advance payment of taxes reduces cash balances (or increases borrowings) during the year. This reduces interest earned (or increases interest paid) for the year and, in turn, this reduces profitability and taxes due.

** The preset formulae used to calculate tax payments for the sixth month of the second and third years will also need to be revised (to avoid double payments of taxes). Do this by deleting the cell references +Calc!XXX which appear at the end of the formulae =IF(Calc!$X$XXX>1,M_B!XXX+M_F!XXX,M_B!XXX+Calc!XXX) for the sixth months of both these years.

To change the timings for the fourth and fifth years, the approach is to insert the amounts of tax to be paid within the second Quarterly Assumptions Report (Q_A2) at the row designated "tax payments" (located near the top of the worksheet). To do this, simply overwrite any formulae or dash sign (" - ") appearing for the last eight quarters by a cell reference(s) or value(s) reflecting the projected tax payment(s). There is no need to make any changes in the Quarterly Balance Sheets as tax liabilities will be automatically reduced to reflect the newly specified payment(s).

Q. How can I use Exl-Plan to produce regularly updated cashflow projections?
In theory, you could advance the start date for Exl-Plan's projections and then revise all its projections by shifting assumptions forward individually and in small batches. In practice, this could be very time-consuming and error-prone. Instead, have a look at Cashflow Plan, our range of fully-integrated specialist cashflow planners which generate projections for 12 months ahead and incorporate a roll-forward facility to simplify regular updating of projections. Details and trial-version downloads. The following table shows the equivalent versions of Cashflow Plan and Exl-Plan:

Q. Could you explain more about "negative" purchases?
Negative purchases can appear in the Monthly Assumptions Report No 2 (worksheet M_M) within its schedule for Total Purchases. The following table illustrates how they can arise as a consequence of the interactions between a user's assumptions and Exl-Plan's calculations.

In Case 1, there is no change between opening and closing inventory targets (100) and consequently purchases and requirements are identical (75). In Case 2, there is a planned reduction in inventory (100-60=40) and there is a resultant reduction in purchases (35) to meet the projected requirements (75). In Case 3, the desired reduction in inventory (100-10=90) exceeds projected requirements (75). The only way that this can be handled mathematically by Exl-Plan is to create negative purchases (-15). The solution, illustrated in Case 4, is to increase the closing inventory to 25 based on the difference between the desired opening inventory (100) and the projected requirements (75). Resultant purchases are zero.

Negative purchases can be prevented by ensuring that planned reductions in each month's materials/goods inventories (in worksheet M_M) never exceed that month's projected requirements. This is logical when you think about it!

To eliminate negative purchases, review the desired closing inventory targets for materials/goods (in worksheet M_M) for each month (working from left to right) for each main sales group and check that the desired monthly reductions do not exceed the monthly requirements of goods/materials.

It is feasible to use formulae to automate the calculation of these inventory targets. For example, the inventory target for the first month could be set at, say, 50% of that month's Cost of materials/packaging or goods required. This formula could be replicated across the remaining months. The only drawback is that this 'one size fits all' formula could be inappropriate for months with big fluctuations in demand and it could generate negative purchases. Another approach would be to use a conditional formula to help eliminate negative purchase and, at the same time, gain better control over projected inventory levels. For example:

= if (current month's requirements - previous month's closing inventory >=0, 60, 20).
Note: Two conditional values (60 and 20) are shown here for illustrative purposes only. The appropriate conditional values can be derived from trial and error. The simplest approach is to enter best guesses for both values to address the terms of the conditional statement and recalcuate.

If negative purchases are encountered, increase one (or other) of the guessed value by the maximum value of negative purchases. As either (or both) conditions can cause negative purchases, the guessed value to be adjusted depends on the applicable condition. If, for the month which causes the greatest negative purchases, the month's requirements are greater than the previous month's closing inventory then add the value of the month's negative purchases to the first conditional value (e.g. 60 in above formula). Replicate this revised formula across all months. If any negative purchases remain, increase the second conditional value (e.g. 20) by the maximum value of negative purchases and replicate the revised formula.

If no negative purchases are encountered then then one or other of the conditional values could be reduced.

The formula works as follows: If the current month's requirements exceed the previous month's closing inventory, then the formula sets the month's closing inventory to the first (usually higher) conditional value (e.g. 60). If the current month's requirements are less than the previous month's closing inventory, the formula sets the closing inventory level to the second (lower) conditional value (e.g. 20).

Q. Can I change the method used to calculate depreciation in the Monthly Assumption Report for Fixed Assets (No. 5)?
More powerful versions of Exl-Plan allow a user to choose (within the Monthly Assumption Report for Fixed Assets) whether to depreciate fixed assets (a) by applying a specified depreciation rate to the original cost of the assets or (b) by applying a doubled depreciation rate to the declining fixed asset balances. This note explains how to depreciate fixed assets by applying the depreciation rate to the declining balance (rather than to original cost). To do this, make changes to the depreciation calculation formulae as follows:

1. Make a backup copy of the XLS file and then unprotect the M_A worksheet.
2. For one of the main asset groups, change the formula for calculating depreciation within the cell for the first month (i.e. column G only). To do this, replace *2/100/12 within this formula by /100/12 (i.e. remove the *2). Note that 2/100/12 appears twice inside the formula.
3. Once this change has been made in the first month's cells, copy the changed formula across the remaining 11 (or 35) months.
4. If desired, repeat (2) and (3) for another main assets group.
5. Enter "1" in the designated cells for basing depreciation on cost or double declining balance. Of course, removal of the *2 from the original deprecation formulae makes them work on a single declining balance basis!
6. Do a scan of the changed row(s) to make sure that everything works OK.

An alternative approach would be to halve the planned depreciation rate and use the double declining balance method - the existing formulae will automatically double the specified interest rate and apply this to the declining asset balances.

Q. How can I get Exl-Plan to handle factoring or invoice discounting?
To handle factoring or invoice discounting, the approach is to (a) set the debtor (receivables) credit period in the second table in worksheet M_B for the relevant sales group(s) to "under one month" and (b) enter assumption values (or simple formulae) to calculate the interest or service costs for the facility within an existing assumptions' row in worksheets M_C or M_O. If unlocked, this row description may be retitled as "Discounting charges" or the assumption values may be added to other related assumptions within an assumptions row.

Q. How can I consolidate Exl-Plan projections for several subsidiary companies?
Two solutions are offered. First, if you need consolidated projections for just one year (12 months), you should consider acquiring a related product, Cashflow Plan Super or Ultra, which contains a comprehensive tool for consolidating projections (i.e. cash flow and income statements plus forecast balance sheets) for subsidiary businesses using a special Report for Consolidation. This report is linked to an additional workbook (Cashflow Plan Consolidator) which facilitates the consolidation process and includes tools for changing currencies/ denominations and eliminating inter-company transactions. A Cashflow Plan Consolidator Guide is included in the download files for Cashflow Plan Super and Ultra.

The second solution is to work with Exl-Plan and to build a new workbook and populate it with relevant data taken from Exl-Plan files. The procedure which does not require any exceptional Excel expertise is described below. For simplicity, it assumes that results for just two subsidiary companies are being consolidated.

1. Use Exl-Plan to create separate projections (i.e. Exl-Plan files) for each company e.g. Company1.xls and Company2.xls. Ensure that the layout of the reports to be consolidated (e.g. Five-Year Income Statement) are identical for each file. Tip: If you wish to have more than one Exl-Plan file open at the same time, we recommend you open them in separate copies of Excel (i.e. run Excel and load one file, run another copy of Excel and load the second file into it) to prevent Exl-Plan's toolbar from getting "confused" as to which Exl-Plan file is being referenced.
2. Run Excel a third time with a new blank workbook (the "consolidation workbook") and insert and name three worksheets - Consolidator, Company1, Company2 - in that order when reading from left to right.
3. Copy and paste all the reports (include variable descriptions in left hand columns and titles at top) that you need to consolidate from Exl-Plan into their respective Company1 and Company2 worksheets created in #2. We recommend you use simple pastes rather than create links which would be broken whenever you update your Exl-Plan file names (in accordance with our recommendation that you use incrementing file names as an insurance against any possible loss of data when using Exl-Plan). If multiple reports are being consolidated, paste in the reports below each other in the same order for both Company1 and Company2 and with identical row spacing between the reports.
4. In order to create the final "consolidation report" within the Consolidator worksheet, copy and paste copies of all the reports that you need to consolidate from one or other of the Exl-Plan files (it does not matter which as the reports should be identical) into Consolidator. Use the same order and row spacings as for Company1 and Company2.
5. Within Consolidator, go to the top left cell containing a projected value and replace this value by a SUM formula which aggregates the corresponding values for the Company1 and Company2 worksheets. Copy and paste this formula into ALL blocks of values within reports in Consolidator using Paste Special ... Formulae. Change column widths to display variable descriptions and hide unneeded columns. At this stage, you may need to selectively adjust some of the SUM formulae to take account of inter company loans and trading, currency conversions and other factors dictated by accounting conventions and standards.
6. Save the consolidation workbook with an appropriate name. Any time, you update the Exl-Plan projections for Company1 or Company2, you can use copy and paste to insert their updated reports into the appropriate worksheet within the consolidation workbook. The SUM formulae in the Consolidator will automatically generate your updated consolidated results.

Q. How do I phase out the receipt of an opening tax credit?
Exl-Plan is set up to handle the payment of taxes (e.g. corporation taxes) which are shown as liabilities in the opening balance sheet. The phasing of these payments is done in the first table within Monthly Assumptions Report No. 7 (M_B worksheet) which will also handle the receipt of a tax credit. However, if you are using a version of Exl-Plan prior to release 2.6 and try to phase in the receipt of a tax credit then an error message (Error in the phasing out of opening balance sheet items !) is reported. The workaround to handle the receipt of an opening tax credit is as follows:

1. Enter the opening tax credit as a negative value in the cell for Taxes in the Current Liabilities part of the opening balance sheet.
2. Go to the M_B worksheet, unprotect it (press the "U" toolbar button) and display its row and column headers (via Tools | Options | View tab and select Row & column headers within the Window options).
3. Unhide the hidden column S and go to the cell in this column opposite Taxes due.
4. Edit the formula there by adding ABS with parenthesis i.e. ( ) around all four cell references as per the following example:
   =IF(OR(ABS(T13)>ABS(F13)*1.05,ABS(T13)<ABS(F13)*0.95),1,0).
5. Rehide column S and enter the phased receipts of the opening tax credit as negative values within the Taxes due row.

Top of Page

Buy & Use Now

Download

3. Extending Exl-Plan

Important: The online help and manual supplied with all versions/editions of Exl-Plan contain much more extensive guidance on changing and extending Exl-Plan.

These FAQs are based on the menu system used by Excel 5, 7, 8, 95, 97, 2000, XP and 2003. If using Excel 2007, 2010, 2013, 2016 with its new Ribbon interface, you may wish to consult the interactive Excel 2003 to Excel 2007 and 2010 command reference guide (within Excel 2007, 2010, 2013, 2016's help) or get it on-line (or as a download) from Microsoft here.

Q. How can I trade up to a more powerful version of Exl-Plan?
If you have already purchased a copy of Exl-Plan, you can trade up to a more powerful version at a specially reduced price (US$20 plus difference in price between existing and proposed version. VAT and other sales taxes may apply). Review the detailed description of the Exl-Plan range to identify the most appropriate version to meet your changing needs. Get full details on trading up here. Once you have traded up, you can also continue using your original version.

Q. Is it easy to expand Exl-Plan?
Yes. The upgraded commercial version can be easily customized by users. The manual and online help (Changing Exl-Plan) both explain many ways for making simple and complex changes. It proposes a technique called plugging whereby a user's special requirements and additional projections can be plugged into an appropriate predefined row within Exl-Plan. This is very simple and extremely powerful. Some specific examples:

• Within the Monthly Sales Assumptions Report (M_S), you can insert very detailed schedules of sales (by region, product, customer etc.) in the empty rows below the Report and then use simple formulae to plug the results into the boxed-in, cream-colored, assumption areas within the Report.
• In the Monthly Material Costs Report (M_M), the cost of materials (or purchases for resale) could be expanded to include a bill of materials and component costs with exchange rates etc.
• In the Monthly Cost of Sales Report (M_C) and Overhead Report (M_O), there is provision for specify the number of people engaged in specific categories and their average monthly payroll costs. More detail could be generated in accompanying schedules showing the names of individuals and costs. All that is required in order to plug these details into the Exl-Plan assumptions would be the head count and weighted average payroll cost.
• In other reports, it would be straightforward to draw up detailed schedules and plug the overall results into Exl-Plan assumption rows. For example, these could cover fixed assets, capital expenditure, long-term loans etc.

See other answers in this section for more specific advice on expanding Exl-Plan.

Q. Can I use formulae instead of values within Exl-Plan's assumption areas?
Yes. Plugging-in as described in the previous question is an example of this. Another example would be to use formulae to calculate head counts for operations based on forecast sales (or production requirements) and productivity indices (e.g. units produced per person per month). These formulae are then inserted (instead of values) into the appropriate assumption areas within Exl-Plan.

Q. How can I restructure Exl-Plan's reports to include additional variables?
Let us assume that you need to add new variables which will impact on all monthly, quarterly and annual reports. One solution would be to work through the various reports making changes to formulae as required. Obviously, this has to be done with great care to ensure that the integrity of Exl-Plan is not disrupted. Another approach would be to (i) copy the contents of output reports into empty rows on their respective worksheets using simple cell references (like "=A16"), (ii) copy and paste (use Paste Special ... Formats) the original reports' formatting into the new reports and (iii) make all the desired changes within the copied reports. Although, it requires setting up, this approach may be more expedient in the long run as the underlying Exl-Plan model is not disrupted and changes should be much easier to apply and be more evident.

Q. How do I generate financial projection reports in different languages and formats?
This is straightforward. First, select one version/edition of Exl-Plan as the primary planning system. Second, build the projections using this version/edition. Third, use simple cell linking within Exl-Plan to create the desired additional reports - see Changing Report Layouts in the manual or online help. Essentially, you create a new report below an existing English report with all the description items translated into the new language and with simple cell references to link values back into the calculated values in the English report. This approach eliminates to need to make changes to the supplied descriptions within an English report and ensures that the new language report is automatically updated as soon as Exl-Plan recalculates. This methodology can also be used to create customized reports in English.

Q. How do I extend Exl-Plan to handle purchases of intangible assets?
Exl-Plan Micro, Lite and Pro allow a user to amortize intangible assets which have been included in the opening balance sheet. However, these version do not permit a user to increase the investment in intangibles. The notes below explain how Exl-Plan can be easily modified to handle such an investment. Note: Exl-Plan Super, Super Plus, Ultra and Ultra Plus (release 2.5 or higher) include facilities within worksheet M_F that allow a user to handle purchases of intangible assets.

1. Go to the M_F worksheet and unprotect it. Use Tools | Options | View to display its row/column headers.
2. Select the entire row for "Intangible asset amortization" (or "Intangible asset writedowns" if using the UK/I edition) and insert a new row. It should appear above the existing row. Use the "Unlock range" button to unlock the new row from Columns G to R (Columns G to AP if using Ultra or Ultra Plus). Enter a description in Column A of the new row e.g. "Investment in intangible assets".
3. Go to the M_CF worksheet and unprotect it. Use Tools | Options | View to display its row/column headers.
4. Select the entire row "Operating lease payments" and insert a new row above it. Enter "Investment in intangible assets" in Column A of this new row. In Columns G to R (Columns G to AP if using Ultra or Ultra Plus), enter formulae which refer back to the corresponding cells in the recently inserted row in the M_F worksheet.
5. Enter summing formulae in Column T (or Columns AR to AT if using Ultra or Ultra Plus) within the M_CF worksheet by copying down the formula from the row immediately above into the new row. There is no need to change the monthly summing formulae as the values in the new row will be picked up automatically by the existing formulae.
6. Go to the M_BS worksheet and unprotect it. Extend the formula for "Net intangible assets" in Column G to include the value of "Investment in intangible assets" shown in Column G of the worksheet M_F. Replicate this formulae across the remaining months to Column R (Column AP if using Ultra or Ultra Plus).

To handle the investment, enter its value in the new row of the worksheet M_F at the appropriate month and then provide for the cost of acquisition by issuing shares, increasing loans, or using cash/overdraft resources. The value of intangible assets can be amortized over the subsequent months, quarters and years.

Q. How can I expand Exl-Plan to incorporate detailed schedules for sales, labor, materials, expenses etc.?
Do this by creating these schedules within the empty rows below assumption reports and plugging their monthly totals into Exl-Plan's designated assumption cells.

To create a basic schedule, e.g. to expand the detail for an expense item within the Monthly Assumption Report for Overhead Expenses (No. 4), proceed as follows:

1. Unprotect the worksheet (e.g. M_O) using the "U" toolbar button and scroll down.
2. Working in empty rows below the text "Supplementary Data & Calculations" , create the desired schedule with descriptions in column A and assumption values in the corresponding monthly columns (G, H etc.).
3. Unlock the block of cells containing the monthly assumption values by using the "Unlock range" toolbar button. This will change their font color to blue and the cell backgrounds to creamish.
4. Immediately below, insert SUM formulae for each monthly column to embrace the values in the unlocked cells.
5. Scroll back up to the relevant expense item within the Assumption Report and insert cell references (e.g. =G87) across the boxed-in, cream-colored row in order to plug-in the schedule's SUM formulae.

For some variables, it may be necessary to create more extensive schedules to include quantities (e.g. headcounts or units of consumption) and rates (e.g. payroll costs or unit prices) and to calculate weighted average rates. The following example shows how this is done in the case of staffing variables (headcounts and payroll cost/person) within the Monthly Assumption Report for Overhead Expenses (No. 4):

1. Unprotect the worksheet (e.g. M_O) using the "U" toolbar button and scroll down.
2. Working in empty rows below the text "Supplementary Data & Calculations" , create three simple schedules with descriptions in column A and a mix of assumption values and formulae in the corresponding monthly columns (G, H etc.) along the following lines:
   
   

   	Row	A	G
   First schedule	90	Staff headcount:
   	91	- Grade A	2
   	92	- Grade B	3
   	93	- Grade C	4
   	94	Total staff headcount	=SUM(G90:G93)
   	95
   Second schedule	96	Staff payroll/benefit cost (US$000/person/month):
   	97	- Grade A	3.0
   	98	- Grade B	2.5
   	99	- Grade C	7.2
   	100
   Third schedule	101	Total staff costs (US$000/month):
   	102	- Grade A	=G91*G97
   	103	- Grade B	=G92*G98
   	104	- Grade C	=G93*G99
   	105	Total staff costs	=SUM(G101:G104)
   	106
   	107	Weighted average payroll/benefit cost (US$000/person/month)	=G105/G94

3. Unlock all the cells within these schedules which contain assumption values by using the "Unlock range" toolbar button. This will change their font color to blue and the cell backgrounds to creamish.
4. Scroll back up to the relevant assumption variables within the Assumption Report and insert cell references for headcount (e.g. =G94) and payroll/benefit costs (e.g. =G107) across the designated boxed-in, cream-colored rows.

These two approaches can be readily adapted to suit a wide range of other situations, for example, detailed sales volume/price schedules or material cost analyses. Where practicable, it is always preferable to create or include detailed schedules within Exl-Plan by either using empty rows below assumption reports or inserting extra worksheets. This avoids the need to create file links and manage assumptions dispersed across several files.

Top of Page

Buy & Use Now

Download

4. Common & Unusual Problems

Note that the supplied online help, README.DOC file and manual are the primary sources of assistance for dealing with common problems. These FAQs are based on the menu system used by Excel 5, 7, 8, 95, 97, 2000, XP and 2003. If using Excel 2007, 2010, 2013, 2016 with its new Ribbon interface, you may wish to consult the interactive Excel 2003 to Excel 2007 and 2007 command reference guide (within Excel 2007, 2010, 2013, 2016's help) or get it on-line (or as a download) from Microsoft here.

Q. After I enter monthly sales assumptions in Exl-Plan, why are these correctly reflected in the Monthly Income Statements (P&Ls) but no corresponding cash inflows appear in the Monthly Cashflow Projections?
This can arise where (a) all values have been cleared from an Exl-Plan model; and (b) sales assumptions have been entered into the Monthly Assumptions Report No. 1 - Sales; and (c) no further assumption have been entered into other monthly assumption reports. This condition gives rise to the following calculation error message when the model re-calculates: "The four sub-models are not producing identical results !".
In addition to entering the sales assumptions, you need to specify customer credit terms by completing the lower table in Monthly Assumptions Report No. 7 - Opening Balance Sheet Items, Receivables & Payables. Once this has been done and the model updated, the cashflows relating to sales (and any associated output taxes e.g. VAT, GST) will appear in the monthly cashflows. The cashflows will be calculated in accordance with the credit terms specified in Report No. 7.

Q. Why can't I change the formatting within a report?
First, you must unprotect the worksheet that you wish to modify. To do this, click the "U" button in Exl-Plan's toolbar.

Q. Why do I see an error message "This command only operates when Exl-Plan is the active workbook." ?
Either, you are trying to access an Exl-Plan menus from a non-Exl-Plan workbook (solution: make the Exl-Plan workbook active) or you have added a worksheet in front (to left) of the supplied FRONT worksheet (solution: move the new worksheet to behind FRONT).

Q. Why does a second copy of Exl-Plan load when I click on Exl-Plan's toolbar?
This arises because the second copy has the same file name as the first copy but is located in different folder. To resolve, close the second copy and then reset the toolbar for the first copy by *Tools/Setup | Reset Toolbar Buttons. To prevent this recurring, don't use two different Exl-Plan files with the same file names at the same time.

Q. Why does my copy of Excel cease to calculate immediately after I have finished using Exl-Plan?
This arises because Exl-Plan switches Excel from automatic to manual calculation (to speed up data entry and facilitate its error-trapping). To restore automatic calculation, restart Excel or simply use Tools | Options | Calculation | Automatic.

Q. How can I get rid of ############?
#### signifies that values cannot be displayed inside a cell. There are several simple solutions depending on whether the ####### appear on the screen or in a printed report. Tip: Print a report to see if the ###### appear there as well as on the screen.

If ####### only appear on the screen, try changing the zoom setting for the report using Exl-Plan's *Tools/Setup menu options, or Excel's View | Zoom, or click the "+" button to the right of Exl-Plan's toolbar.

If this does not work or the problem arises in printed reports, you can change the column widths. To do this, you will need to unprotect the worksheet (using the "U" toolbar button). Then use Tools | Options View to turn on the row/column headings and change the column widths as required. You can also change widths via Format | Columns.

If you don't want to widen columns too much because of possible page width problems when printing using portrait orientation, another solution is to change the number format for the page - remove the coma separator for thousands (e.g. change 1,000 to 1000); eliminate any bold fonts; and/or reduce the decimal places displayed. Changing format is often the easiest and best solution.

A further solution is to widen columns and change the print orientation for individual "problem" reports from portrait to landscape (via File | Page Setup | Page). The only drawback with this solution is that reports may be spread over two-three landscaped pages instead of one for portrait.

If the ##### are very extensive, you may need to consider changing the basic currency denomination (e.g. from 000s to Mlns) used by Exl-Plan (via *Tools/Setup | Basic Model Info). If you do this, you will need to revise all the assumption values.

Final comment: 000s is the most widely used currency denomination by Exl-Plan users rather than single units e.g US$000 rather than US$. The latter is only really applicable if you have a micro business with annual sales of less than, say, US$100,000.  If you use US$000, you can still enter detailed values to the nearest dollar e.g. enter 147,567 as 147.567  (note decimal point). Exl-Plan will display this as 147.6 but it will use 147.567 in calculations. This makes the presentation of reports much clearer and avoids spurious accuracy issues. It also avoids the need to widen columns which could lead to very small font sizes being used in reports when printed with portrait orientation.

Q. When using the Profitability Planner in Exl-Plan Super, I see ####### instead of results?
Not applicable to release 2.1 or higher of Super, Super Plus, Ultra or Ultra Plus - to check release, select *Exl-Plan Help | About Exl-Plan ....
This arises when all the sales groups are not being used for first-year projections. The simplest solution is to remove the "n/a" value which appears in one or more of the yellow-colored cells relating to prices and then recalculate (press F9). A better solution is to unprotect the Profit Planner worksheet and change all the formulae in the white-colored, price-related cells by replacing "n/a" by the number 0 (i.e. zero without any quote marks).

Q. When planning a start-up, what's wrong when I get a calculation error message saying that "the sub-models are not producing identical results"?
Not applicable to Exl-Plan releases 1.2 or higher - to check release, select *Exl-Plan Help | About Exl-Plan ...
This may happen when you project monthly direct costs in Monthly Assumption Report No. 3 but have zero monthly sales corresponding to these costs. This becomes very apparent in the quarterly reports and Exl-Plan's error trapping and CHECK report will signify a problem. The simple solution is to transfer any monthly direct costs for which there are no corresponding monthly sales into monthly overhead costs in Monthly Assumption Report No. 4. In other words, only show monthly costs as being "direct" for months in which sales are being projected.

Q. Why did I get a message "Unable to clear all assumption values."?
This message may appear when Quik-Plan is being run or when assumption reports are being cleared. It usually indicates that Exl-Plan encountered formulae within boxed-in assumption areas in an assumption report or the opening balance sheet (M_BS). By design, Exl-Plan will not automatically clear these formulae in case they are important. Review each assumption report and the opening balance sheet (M_BS) to find these formulae and then either delete them or lock them using *Protection | Lock Range. Bear in mind that the cell entry "=305" (ignore the "") is treated as a formula whereas the entry "305" (without the = sign) is not a formula and will be automatically cleared.

Q. How do I get Exl-Plan's special toolbar to appear?
Exl-Plan's special toolbar is attached to Exl-Plan's XLS files and should appear automatically whenever an Exl-Plan XLS file loads. Unfortunately, Excel's binding of toolbars to XLS files is weak and, in some circumstances, the Exl-Plan toolbar may get "lost". This usually only happens if a user renames an Exl-Plan XLS file using Explorer. To get around this renaming problem, load the XLS file into Excel and then save it with a new name. The following measures should restore Exl-Plan's special toolbar:

1. Load Exl-Plan and use View | Toolbars to display a list of available toolbars. If Exl-Plan is listed as a toolbar but not selected, tick it.
2. If the Exl-Plan toolbar is not listed or does not appear after ticking, select the menu option "Reset Toolbar Buttons" within the *Setup (or *Tools/Setup) menu. Allow a few moments for this resetting to take place.
3. If the Exl-Plan toolbar still does not appear, try #1 above again. If the Exl-Plan toolbar is still not listed, try reattaching it to Excel using the supplied ADDTOOL.XLS file. This file will be found in the folder to which you first installed Exl-Plan - you can also download it here (ADDTOOL.ZIP - 9k). To use ADDTOOL.XLS, close any open copy of Exl-Plan and then load ADDTOOL.XLS into Excel. This process will automatically reattach the toolbar to Excel. Close Excel, reload it and then rerun Exl-Plan.
4. If the Exl-Plan toolbar still does not appear, load Exl-Plan and use View | Toolbars to display a list of available toolbars. If Exl-Plan is listed as a toolbar but not selected, tick it. You may also need to select the menu option "Reset Toolbar Buttons" within the *Setup (or *Tools/Setup) menu. Allow a few moments for this resetting to take place.

If needs be, see also the response to the question immediately below and the discussion on "Exl-Plan loses its Customized EXL-PLAN Toolbar" further down in 5. Fixes for Exl-Plan.

Q. How should I respond to the message "Exl-Plan could not find its customized toolbar. Refer to accompanying README file for further info."?
In certain exceptional circumstances, Exl-Plan may display this message when loading. We supply a simple fix to reattach the Exl-Plan toolbar to Excel and overcome this problem which is related to changes in the file formats for Excel 5/95 and 97/2000/XP/2003. You can download an Excel file here (ADDTOOL.ZIP - 9k) containing the fix with instructions. This file (ADDTOOL.XLS) is automatically supplied with Exl-Plan release 2.0+ and will be found in the folder to which Exl-Plan was first installed. To use ADDTOOL.XLS, load it into Excel and it will automatically reattach the toolbar. Alternatively, use this Contact Form to advise the nature of your problem. If using the fix, you might wish to refer to the notes further down about 'hanging' for explanations and to prevent recurrences of the problem.

Q. Why do I encounter an 'out of memory' message when my PC has plenty of RAM ?
Even with 64 Mb (or more) of RAM, it is possible to run out of memory due to memory leakage and/or running too many applications simultaneously. The main solutions/fixes to this problem are listed below:

1. Close down and reboot - this usually solves the problem.
2. Quit non-essential programs when starting Windows and run Excel as the first program after loading Windows. If not restarting Windows, close all non-essential programs.
3. Remove Excel add-ins which you don't use - select Tools | Add-ins and deselect the add-ins you don't need.
4. Try setting the zoom to 100% before you use *Tools/Setup | Basic Info (Apparently Excel does not like zoom settings other than 100% !!!!). Once changed, restore the zoom setting to the default using the Exl-Plan *Tools/Setup menu.
5. If you have added charts to Exl-Plan, you may have hit the infamous Excel 97 resource-gobbling charts problem. Don't add additional charts to Exl-Plan as these can be very memory intensive. You may also be able to work around this limit by moving some of your additional charts from your Exl-Plan workbook into a separate workbook. Note that charts moved to another workbook remain linked to the data in the original workbook.
6. Turn off virus protection. Does that change anything? Turn it back on. If it makes a difference some people have turned off McAfee Virus Shield from scan "all files" to scan "program files only" to resolve memory problems. If that helps, it would be a good idea to check McAfee site to see if you can find more information.
7. Screen Savers and backgrounds consume resources. Try turning off your screen saver.
8. HP printers can cause memory problems because their drivers do not release system resources. Even if you just do a Page Setup and do not print, this will cause problems. HP692 drivers and some other HP drivers according to MS will cause OS resource memory loss. It is possible that the latest version of the printer driver will correct the problem. More info at the Microsoft Knowledge Base at http://support.Microsoft.com/support/kb/articles/Q165/9/85.ASP
9. Reduce the number of colors the video driver is set to (256 is enough for most spreadsheets, although the desktop won't look too pretty). Consider disabling any unnecessary video features/acceleration or updating your video drivers.
10. Make sure Virtual Memory is on and consider increasing Virtual Memory (take note of the initial settings if you wish to restore them).
    Windows 95/98: Right-click the My Computer icon on the desktop, and then click Properties on the shortcut menu. Click the Performance tab. Click Virtual Memory. Click to select "Let Windows manage my virtual memory settings (recommended)." Click OK, and then click Close.
    Windows NT: Right-click the My Computer icon on the desktop. Click Properties on the shortcut menu. Click the Performance tab. Click Change. In the Virtual Memory dialog box, you can change the parameters for your paging file (initial size, maximum size, and so on). After you change the settings, click Set, and then click OK. Click Close. More info at the Microsoft Knowledge Base at http://support.Microsoft.com/support/kb/articles/Q199/3/40.ASP.
11. More info about Methods for Conserving GDI Resources at the Microsoft Knowledge Base at http://support.Microsoft.com/support/kb/articles/Q102/4/38.ASP.

Q. What can I do if a GPF occurs when using Exl-Plan and I can only reopen my file as 'read-only'?
If you encounter an Excel General Protection Fault ("Illegal operation" stuff) then the file in used is not marked "closed" by Excel/Windows. Restarting Excel will only permit read-only access to this file. Restarting Windows usually sorts out the problem. If it doesn't, open the file as read-only and save it with another name. If you can write after that, delete the original file and rename new file back to the original file name.

Q. Why can't I copy a chart or selected ranges from Exl-Plan into Word?
Before using the copy function in Exl-Plan, you must unprotect the worksheet that you wish to copy from. To do this, click the "U" button in Exl-Plan's toolbar.

Q. One of my Exl-Plan XLS files contains links to another file but I cannot find the links?
Links in an Excel file are not evident. One solution is to search the linked file for any formulae containing "[" (left square bracket without the ""). This will display links in the form [xxx.xls!xxxx] one at a time. If desired, replace the linked cells by their underlying values (i.e. Edit | Copy and Edit | Paste Special .. Values)

Q. When trying to print reports, I get an error message "Cannot shift objects off sheet" or Exl-Plan fails to hide columns in certain reports?
This is an Excel problem which can arise after you have inserted Comments within the body of a monthly or quarterly report. It will manifest itself when Exl-Plan's macros fail to correctly hide certain columns to produce a compacted report, for example, to hide all monthly columns when printing a full-year output report or, in the case of Ultra and Ultra Plus, to hide certain monthly columns when printing monthly or full-year assumption and output reports. To confirm the source of the problem, go to the relevant sheet and hide a series of columns in order to provoke the message "Cannot shift objects off sheet". From our experimentation, the problem seems to almost occur at random.

The simplest solutions are to (a) place all new comments relating to a particular row into a comment inserted into column A for that row and/or (b) relocate existing comments within columns G-R etc. into a new comment inserted in column A. See also Microsoft's solution at http://support.Microsoft.com/default.aspx?scid=kb;EN-US;q170081.

Top of Page

Buy & Use Now

Download

5. Fixes for Exl-Plan

The existence of this item about bug fixes should not alarm users. We test Exl-Plan very extensively before release but inevitably the odd minor problems may escape our very best endeavors. It is our policy to disclose any identified or reported problems/errors.

No fixes needed or problems reported as all versions and editions are very stable and running satisfactorily (thank goodness!). If you are using Excel 2007, 2010, 2013, 2016 and need guidance on its new interface and procedures, see the FAQ and Checklist for Excel 2007, 2010, 2013, 2016 and Windows 7.

Top of Page

Buy & Use Now

Download