|
|
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, 365 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:
- Go to the M_S worksheet (Monthly Assumption No.1) and unprotect all
worksheets via the *Protection menu (ignore the request for a
password).
- Right click on the M_S tab and choose the option to Select All
Sheets.
- From the Edit menu, select Replace ..., enter VAT and
GST in the appropriate text boxes and choose Replace All.
- Right click the M_S tab and choose Ungroup Sheets.
- 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.
|
|
|
|
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, 365 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.
Months |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
Total |
Seasonal Index |
5 |
5 |
6 |
7 |
8 |
10 |
12 |
9 |
6 |
4 |
2 |
2 |
|
Sales |
66 |
66 |
79 |
92 |
105 |
132 |
158 |
118 |
79 |
53 |
26 |
26 |
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 use Exl-Plan to deal with deviations from the normal credit
terms for receivables (debtors) or payables (creditors)?
If using Micro, Lite, Pro, Super or Super Plus you should use the solution
described in the previous answer to temporarily adjust the net cashflows.
If you are using Exl-Plan Ultra or Ultra Plus (release 2.5 or higher), there
is a special facility within the worksheet M_F which allows you to adjust
receivable (debtor) or payable (creditor) levels and their related cashflows
(which are automatically calculated based on the "normal" credit
intervals specified in the worksheet M_B) to take account of seasonal variations
or temporary factors - see the supplied README.DOC file for more information.
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, 365 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:
- Load the relevant Exl-Plan file into Excel.
- Create a new workbook file using Excel (File | New ...).
- Within Exl-Plan, unprotect and select a report to be copied and
then copy it to the clipboard using Edit | Copy.
- 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).
- 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).
- 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):
- 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).
- Right click on any of the whitened tab and select Move or Copy...
- 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.
- From within the original file, right click on any whitened tab and
select the option to Ungroup Sheets.
- 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.
- Unprotect all worksheets within the Exl-Plan file via *Protection
| Unprotect All Worksheets. Ignore the request for a password
and click OK.
- 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).
- Right click on any of the whitened tab and select Move or Copy...
- 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.
- From within the original file, right click on any whitened tab and
select the option to Ungroup Sheets.
- 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.
- 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:
- Go the report and unprotect it using the "U" toolbar
button.
- 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.
- 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.:
- 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.
- 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:
- You will be able to resize the pasted picture by clicking on it and
moving its corner handles.
- 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.
- Many of the tables and charts in Exl-Plan's Textual Summary Report
are ideally sized for pasting directly into a Word document.
- 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.
- 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:
- Go to Q_A2 and unprotect it using the "U" toolbar button.
- Display the row and column headers using Tools | Options | View.
- 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.
- 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).
- Select column S and change its width to 1. Select column T and change
its width to 0.1.
- Select column U and all columns to its right and hide them using
Format | Column | Hide.
- 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:
- Go to Q_A2 and unprotect it using the "U" toolbar button.
- Display the row and column headers using Tools | Options | View.
- 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.
- 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.
- Select column S and change its width to 1. Select column T and change
its width to 0.1.
- Select column U and all columns to its right and hide them using
Format | Column | Hide.
- 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:
- Go to Q_A2 and unprotect it using the "U" toolbar button.
- Display the row and column headers using Tools | Options | View.
- 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.
- 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.
- Select column S and change its width to 1. Select column T and change
its width to 0.1.
- Select column U and all columns to its right and hide them using
Format | Column | Hide.
- 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:
- 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.
- 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:
Cashflow
Plan
Version * |
Exl-Plan
Version |
Exl-Plan's Time Horizon |
Micro |
Micro |
One revenue/cost group - first year on monthly basis plus 2 years
on quarterly basis (3 years) |
Lite |
Lite |
Two revenue/cost groups - first year on monthly basis plus 2 years
on quarterly basis |
Plus |
Pro |
Four revenue/cost groups - first year on monthly basis, 2 years
on quarterly basis plus 2 years on annual basis |
Super |
Super |
Six revenue/cost groups - first year on monthly basis, 2 years
on quarterly basis plus 2 years on annual basis |
Super |
Super Plus |
Ten revenue/cost groups - first year on monthly basis, 2 years
on quarterly basis plus 2 years on annual basis |
Ultra |
Ultra |
Six revenue/cost groups - first three years on monthly basis,
2 years on quarterly basis plus optional 2 years on annual basis |
Ultra |
Ultra Plus |
Ten revenue/cost groups - first three years on monthly basis,
2 years on quarterly basis plus optional 2 years on annual basis |
* All version cover 12 months ahead with weekly breakdown
for initial three months.
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.
|
Case 1 |
Case 2 |
Case 3 |
Case 4 |
|
Projected materials/goods requirements |
75 |
75 |
75 |
75 |
Calculated by Exl-Plan |
Desired opening materials/goods inventory for month i.e. Desired
closing inventory for previous month |
100 |
100 |
100 |
100 |
Entered by user |
Desired closing materials/goods inventory for month |
100 |
60 |
10 |
25 |
Entered by user |
Purchases for month (Closing inventory + requirements - opening
inventory) |
75 |
35 |
-15 |
0 |
Calculated by Exl-Plan |
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:
- Make a backup copy of the XLS file and then unprotect the M_A worksheet.
- 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.
- Once this change has been made in the first month's cells, copy
the changed formula across the remaining 11 (or 35) months.
- If desired, repeat (2) and (3) for another main assets group.
- 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!
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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:
- Enter the opening tax credit
as a negative value in the cell for
Taxes in the Current Liabilities part of the opening balance sheet.
- 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).
- Unhide the hidden column
S and go to the cell in this column opposite Taxes
due.
- 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).
- Rehide column S and enter the phased receipts of the opening tax credit
as negative values within the Taxes due row.
|
|
|
|
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, 365 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.
- Go to the M_F worksheet and unprotect it. Use Tools | Options |
View to display its row/column headers.
- 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".
- Go to the M_CF worksheet and unprotect it. Use Tools | Options
| View to display its row/column headers.
- 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.
- 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.
- 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:
- Unprotect the worksheet (e.g. M_O) using the "U" toolbar button and
scroll down.
- 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.).
- 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.
- Immediately below, insert SUM formulae for each monthly column to
embrace the values in the unlocked cells.
- 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):
- Unprotect the worksheet (e.g. M_O) using the "U" toolbar button and
scroll down.
- 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 |
- 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.
- 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.
|
|
|
|
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, 365 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:
- 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.
- 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.
- 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.
- 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:
- Close down and reboot - this usually solves the problem.
- 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.
- Remove Excel add-ins which you don't use - select Tools | Add-ins
and deselect the add-ins you don't need.
- 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.
- 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.
- 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.
- Screen Savers and backgrounds consume resources. Try turning off
your screen saver.
- 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
- 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.
- 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.
- 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.
|
|