Subscribe to PSTV 'Views and News'

Our monthly 'PSTV Views and News' gives extra tidbits on guest interviews and upcoming guests..

First Name *
Last Name *
Email *
May
29 2012

Shortcuts for your spreadsheets

Syndicated from: Canadian Youth Business Foundation

Simon Selkrig, Strategize Financial Modelling, simon@strategizefm.com, Montreal, QC Here are some quick and simple shortcuts to help save you time with your financial spreadsheets. Expanding existing formula range Do you need to insert additional line items such as expenses and achieve an updated sum of the expense schedule? Insert rows into the second last row of the array (see image below) – this will ensure your existing formulae updates automatically. Avoid retyping certain words or names Does your spreadsheet contain numerous worksheets? Why not use the Defined Name feature for certain items, which will need to be entered repeatedly across the financial spreadsheets. Not only will it save time in manual entry, but it will ensure your spreadsheet is more uniform, consistent and error-free. Restrict certain cell inputs to specific parameters with drop-down and dependent drop-down boxes Are you using drop-down boxes in your spreadsheet? Drop-down menus and dependent drop-down boxes force users to select specified inputs, such as country and dependent states or provinces. In the spreadsheet below, this will dictate the tax rate of a business unit based on location and prevent incorrect tax rates being computed.  Avoid repetitive charts across numerous worksheets Different users of financial spreadsheets have different preferences and end reporting requirements. Often it is better to place numerous charts on one worksheet, or give the user the option to customise a graph’s time series and specific area to graph, i.e. comparing different insurance products’ death benefits rather than their cash surrender values or annual premiums. Adding tick-box functionality can further enhance the chart’s user-friendliness and flexibility. Users can add or remove specific items such the different products to compare. Using drop-down boxes to construct dynamic charts will permit the user to customise the chart for their own needs. It will also save time having to recreate a chart for each area of analysis, which would increase the number of worksheets in your financial spreadsheet. Conclusion There are many shortcuts to implement with your financial spreadsheet which will not only save time but also reduce error. There are ways to expand an existing formula range in order to account for additional line items, for example, in an expense report. You can define cell names in order to avoid the hassle of constant retyping words or names. And the use of drop-down boxes will restrict input parameters or enable one chart to be used for different data sources or data arrays.

Previous post:

Next post: