Exl-Plan: Notes on Extending Start Year Beyond 2004

Introduction

For Exl-Plan releases prior to 2.5, it is not possible to use the Enter Basic Mode Info dialog to set start dates for projections after 2004 or after 2004-5. To surmount this, you have two main options as follows:

      1. Download the latest release of your existing version/edition of Exl-Plan and upgrade it to fully-operational. Details below.
      2. Make changes on an “as required” basis to an existing Exl-Plan file(s). Details are given below for manual and macro-based solutions.

1. Download the Latest Release

Downloading the latest release of your current version/edition of Exl-Plan is the best option if you wish to use Exl-Plan to create completely new projections on an ongoing basis in the future.

To do this, use this Contact Form to get instructions - be sure to include some reference to confirm that you previously purchased a copy of Exl-Plan e.g. invoice, receipt or transaction number or an approximate purchase date. On this basis, no fee is payable to update to the latest release of your existing version/edition of Exl-Plan.

Instead of (or in addition to) updating your existing version of Exl-Plan, you may be interested in trading up to a more powerful version. You can get more info on the full range at <www.planware.org/exldesc.htm> or download a trial version from <www.planware.org/exldown.htm>. We offer special rates to registered users who trade-up to a more powerful version. The fee is calculated as US$ 20 plus the price difference between your current and desired version. Get full details about trading up here.

2. Extending Start Years - Manual Solution

Use this option if you wish to extend the dates for any existing Exl-Plan files (including your master copies). If you wish to regularly change the dates of these files, you may prefer the macro-based solution described below.

Extending dates beyond 2004 or 04-05 is a simple task and only takes a few minutes for each file that needs to be changed. As a safety precaution, you should apply the changes to a backup copy of your file(s).

If your new start year runs from January to December, use the *Setup/Tools | Enter Basic Model Info to set the start month to January (no need to change any other setting in the dialog). Then, go to the M_S worksheet and unprotect it, overwrite the formulae for each of the 12 months in the upper date row by entering the new start year as a four-figure year number e.g. 2005. Recalculate. That’s it! Save your work and read no further.

If your new start year is a split year e.g. from February 2005 to January 2006, you will need to make simple changes to several worksheets as explained below - be sure to unprotect each sheet before attempting changes or use the *Protection | Unprotect All Worksheets option (ignore the request for a password).

IMPORTANT: When inserting a split year, you should use an AA-BB format (e.g. 05-06 for 2005-6 and so on). Note: You must include an apostrophe (the symbol below the @ symbol on most keyboards) immediately before AA-BB. Otherwise, Excel will interpret your entry as a formula and enter -1 in the cell !!

Worksheet M_S:
Overwrite the formulae for year numbers in the upper date row for each of the 12 months by entering years as four-figure year number e.g. as 2005 etc. For the full-year total(s), overwrite the formulae for full years using the AA-BB format for split year(s) (e.g. enter ‘05-06 for 2005-6 and so on). Calculate and the changes will flow through related worksheets.

Worksheet M_PL:
For the historic and projected full-year total(s) only, overwrite their formula using the AA-BB format for the split year(s) (e.g. enter ‘05-06 etc.). Calculate and the changes will flow through related worksheets

Worksheet Q_A1:
Overwrite the formulae for year numbers in the upper date row for each of the 12 quarters by entering using the AA-BB format for split year(s) (e.g. enter ‘05-06 etc.). Calculate and the changes will flow through related worksheets.

Worksheet Q_A2:
Copy the changes made to Q_A1 into Q_A2. Calculate and the changes will flow through related worksheets.

Worksheet A_45 (not applicable to all versions):
Overwrite the formulae for year numbers for the last two years using the AA-BB format for split year(s) (e.g. enter ‘05-06 etc.). Calculate and the changes will flow through related worksheets.

Worksheet Q_PL (Q_IS for US/Canadian edition):
Overwrite the formulae for any remaining incorrect year numbers within full-year columns using the AA-BB format for split year(s) (e.g. enter ‘05-06 etc.). Calculate and the changes will flow through related worksheets.

That completes the task. Reprotect all worksheets via the *Protection menu and save your work !

3. Extending Start Years - Macro-based Solution

Use this method if you need to regularly change the start year for a particular file and have some (very basic) knowledge of Excel macros and Visual Basic.

The solution is to insert a simple macro which overwrites one of the variables used within Exl-Plan. To do this, proceed as follows:

  1. Open the Exl-Plan file and give it an appropriate new name.
  2. Access "Enter Basic Model Info" to set up the basic info for the projections - ignore the Start Year variable.
  3. Add the following macro to an existing Visual Basic module within this file:

    Sub newyear()
    Dim year
    Let Range ("year") = 10
    Calculate
    End Sub

To insert this macro:

  1. Select the menu option Tools|Macros|Visual Basic Editor.
  2. Switch to the Visual Basic Editor.
  3. Display its Project Explorer via the menu option View|Project Explorer.
  4. Within the Project Explorer, click "Modules" to display all the modules within Exl-Plan.
  5. Select the MAIN module by clicking it, scroll down to the VERY bottom of it and then click there to place the cursor on a new empty row.
  6. Paste the above macro into the MAIN module at the cursor's location.
  7. Relocate the cursor anywhere inside this macro and press the F5 function key to run the macro.
  8. Switch back to the Exl-Plan workbook, recalculate and check the new start date in one of the monthly assumption reports. See note below.
  9. Close the Visual Basic Editor and re-save the Exl-Plan file.
  10. To change the start year for projections generated by this file, change the “10” in the macro as needed.

Note: This macro will extend Exl-Plan's base year (this was 1997 for many older versions) by 10 to create a new start year of 2007 (based on the 1997 base year) which will be replicated throughout the Exl-Plan file (after you recalculate). You can change the value “10” in this macro to secure a different start year. Also, as the base year used by Exl-Plan has changed over time, you may need to experiment with other values to get the correct start year for your projections.

That completes the task. Save your work !