Financial modelling productivity pack release notes


Download the latest version from the Productivity Pack download page.

Version 020a - Updated April 1st 2022

This update was  "under the hood" code review & tidy up, and changes to ensure all shortcuts work on Excel for Mac.

New keystrokes for Mac users.

All Ctrl+alt keystrokes on Windows are now Shift+Ctrl+Option on Mac.

Windows keystroke change

Adding variance tabs was previously activated using Ctrl+Shift+3. This keystroke is not usable on Mac. We have therefore moved to Ctrl+Shift+9 on both Windows and Mac. Guidance on using this macro to follow as it's not yet been added to the handbook.

There is a known bug on the changelog macro Ctrl+shift+1. This will be addressed in the next version.  


Version 18o - Updated March 16th 2022

New: Add a Sparkline

Ctrl+shift+s

See this guide on adding a sparkline to a calculation.

Version 18n - Updated March 15th 2022

Bug fixes & minor updates:

  • Reset end cell macro didn't clear out formatting.
  • Used Range report didn't correctly report last column in extreme cases
  • Adding to the capitalisation exceptions list now checks for duplicates and reports
  • Changing the sheet exceptions for import / export marking now autosave  

Version 18m - Updated March 12th 2022

Managing exceptions for import/export marking and capitalisation macro

Improvement to the interface to make it clearer which sheets were excepted for import marking and export marking.

Bug fix: Macros don't work on protected worksheets

Certain macros were not working where some sheets in the model are protected with a password.


Version 18j - Updated March 2nd 2022

Updated: Reset end cell

Ctrl+alt+c

This was not working consistently on all files. Works consistently now but further testing and user feedback across more file types will be useful.

Updated: Sum of the row above

Shift+control+n

When the new is used in the constants column to give sum of the series values in the row above, it now also copies down the units from the row above.

Update: Input relocation macro

Ctrl+alt+r

Now contains a dropdown for the sheet that you want to send the inputs to. Only asks once for constants, and once for series, in each session then defaults to those options for future relocation

Input relocation macro adds constants to the active case. Range name CASE_ACTIVE is required to tell the macro which case is active.

Update: Add to exceptions list

Ctrl+alt+e

Previously to add a sheet to the exceptions list, you had type the sheet name. There is now a dropdown to select the sheet name.

Update: Label capitalisation check - "spell checker" type behaviour added

Previously the macro to check label capitalisation would make corrections all over the sheet without any user control. The macro will now make suggestions and allow the user to decide on a case by case basis, like a spell checker dialogue would.


Version 18d - Updated 22nd February 2022

New: Unhide and hide the macros

Ctrl+shift+7

Previously when we wanted to hide and unhide the macros, we had to do it through the menus. We can now do this with Ctrl+Shift+7. This keystroke toggles the macro workbook between hidden and unhidden.

New: Delete a worksheet

Ctrl+alt+-

Using F11 regularly to chart parts of your model leaves you with the need to delete sheets regularly. We have therefore added a shortcut for this. You will get the same "are you sure you want to do this" warning as would get using the menu commands.

New: Reset the end cell

Ctrl+alt+c

The behaviour of this shortcut depends on where you are in the sheet:

  • If you are in column J or later, the shortcut will set the end column
  • If you are in column A through I, the shortcut will set the end row
With a cell in column EA selected (at the end of the timeline) the macro will confirm that you want to clear all data from column EB onwards, setting column EA as the end column
Running the same macro while in Call A56, the macro will confirm that you want to clear all date from row 57 onwards. 

New: Add to output sheet

Ctrl+alt+o

You can only run this shortcut on constants or row totals. The macro will create a link to the selected item on the Output sheet.

You need to maintain a template row on your Output sheet, named "OutputRowTemplate". The Handbook start file has the correct range names for reference.

See also this summary of the range names needed in your model for the macros to work.

Maintain a template row on your output sheet, named "OutputRowTemplate"

Updated: Row total

Ctrl+shift+n

This keystroke has a new behaviour. When run in the Row Total column, it will add a row total as usual. If we run it in the Constants column, it will add a total of the line above as a constant and add "Sum of " & [above row label" as the row label. It will copy the units from the row above.

Ctrl+shift+n in the constants column adds the sum of the series line above

We have added this because we regularly need to bring the total of a series row into the constants column to be able to perform further calculations using that total.

Updated: Utilities dialogue

Ctrl+shift+6

This dialogue has been renamed from "Prepare for release" to "Utilities".

Changes to the dialogue

  • Select all button added for when we want to run a utility on all sheets.
  • Shift+down arrow works to select ranges of sheets to apply the utilities.

The utilities have been grouped into three types:

  • Quick build. These utilities help you get small build tasks done quickly,  such as adding a new corkscrew or a new template sheet.
  • Prepare model for release. These are a series of "tidy up" operations that can be run on selected sheets.
  • Model information. These run on all sheets and return information about the model.

New utilities have been added:

  • Collapse/expand all grouping. This will toggle the grouping state on all selected sheets.
  • Change header. This will change the print header across all selected sheets. Helpful in updating, for example, a Project Name across all sheets

Other changes

  • The utility macro to reapply grouping no longer hides the rows after the "END" marker at the bottom of the sheet.
  • Changelog. The spacing settings have changed on the control log (Ctrl+Shift+1). Spacer rows are only inserted between change log sections.
  • We've made changing the keystrokes for macros easier by adding modifier keys to a dropdown menu. They previously had to be typed, which was error-prone.


Comments

Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.

Subscribe to Financial Modelling Handbook

Don’t miss out on the latest financial modelling guides. Sign up now to get access to the library of members-only guides.
jamie@example.com
Subscribe