Business Functions Library for Excel

      1. Getting Started
      2. Using The Help File
      3. Top Dozen Functions
      4. Golden Rules
      5. Excel 2007
      1. Go To
      2. Function Selector
      3. CalculateFull
      4. Calculate WorkSheet
      5. Trace Facility
      6. Quick Paste Example
      7. Tutorials
      8. Function Finder
      9. Examples
      10. Help
      11. Excel"s Function Wizard
      12. Access Internet
      13. Usage of Functions (Audit)
      14. Uninstall
      1. Time Periods
      2. Inclusive and Exclusive Dates
      3. Using Daycount
      4. Examples of DayCount
      5. Annual Date Sequences
      6. ProjMode and Inclusive Dates
      7. Date Rolling Convention
      1. Rate Projections Functions Walkthrough
      2. Accruals and Cash
      3. Repeating Formulae
      4. Range Names and References
      5. Optional Parameters
      6. Using PmtsPerYear
      7. Modelling Seasonality
      8. Calculating Interest
      9. Using Business Functions in VBA (Visual Basic for Applications)
      10. Array Functions
      11. Volatility
      12. Annual Equivalent Rates
      13. Array Function
      14. Auto Multi Functions
      15. Variable Plurality
      16. GoalSeek
      1. Introduction To the Worked Examples
      2. Daycount
      3. General Projections
      4. Business Plans
      5. Cashbasis And Periods
      6. Using Timebases
      7. Using Dates
      1. How To Generate a time scale for a structured financing
      2. How To Project Rents off a Rental Forecast
      3. How To Run a model on different time bases
      4. How To Isolate The Cause of a Errors in Cells using Trace
      1. Introduction to the Utilities
      2. Audit
      3. Synchronized Range Insert/Delete
      4. Database Edit
      5. Insert Macro Button
      6. Link Analyser
      7. Range Describer
      8. PrintScript (beta)
      9. Create Local Range Name
      1. Number Formats
      2. Apply BF"s Color Palette
      3. Bullets
      4. Color Cells
      5. Conditional Formats
      1. Validation DropDowms
      2. Validation Standards
      1. Select Excel Function
      2. Array Function Tools
      3. Sort Sheets
      4. Range Value
      5. Named Range Manager
      6. Enforce WorkBook Settings
      7. Monte-Carlo
      8. TimeChart
      1. The ".ini" file
      1. Forum
      2. Online Help
      3. Templates
      1. Conversion of Input Strings to Values
      2. List of Holidays
      3. Acknowledgements and Trademarks
      4. Published Editions Changes in Behaviour
      5. Range Handling And Constraints
      6. Dates in Excel and Business Functions
      7. Old Composite DayCount Format
      8. DayCount in Excels"s Functions
      1. NPV of Annual To Periodic CashFlows - CorrectionM
      2. Interest - Simple, Annual, Continous and Discount Factors
      1. New Functions
      2. Obsolete Functions
      3. Discontinued Functions
      4. Deprecated Functions
    Modelling Seasonality
    Forecasting revenues that vary seasonally
    Seasonality in Business Functions is where you want an amount disbursed pretty evenly, but you want to be able to specify certain times of the year where the rate is higher. In particular:
    • You want to specify a yearly rate.
    • You want that rate to vary seasonally throughout the year.

    Business Functions facilitates the modelling of seasonality with functions that have the "?S" suffix, like ConS and UniSpreadS, derived from Con and UniSpread respectively.

    Two additional variables are used to model seasonality, SeasonFactors and SeasonSeq, although you only need to use SeasonFactors.

    There are basically two methods of modelling seasonality:

    1. Evenly spaced seasonality just using SeasonFactors

    Here, you just input a list of factors, or weights (that needn"t add up to one or any particular number), and the rate is adjusted accordingly. If you input 12 factors, these are treated as calendar monthly factors starting at January. If you input 4 factors, they are treated as quarters. You can even specify 3 factors and have them treated as "thirds" of a year if you want. This method is easy and great if you want your factors applied to evenly time-spaced weighting periods. If you want to use this method, either stop your inputs after the SeasonFactors argument, or if you need to specify DayCount or Periods, just leave a blank between the commas in the functions argument list. See Optional Parameters.

    2. Specified, or unevenly spaced seasonality using both SeasonFactors and SeasonSeq

    Here you specify both a list of factors and a list of annual dates in the SeasonSeq variable. The latter dates are annual sequence dates in the form mm.dd, e.g. {1.01,7.01}, such as are used in the Periods and CashBasis variables. The seasonal factors apply from the sequence date. So the first sequence date must be "1.01" (1st January) otherwise the function will return an error.

    Using both variables allows you to so things like "150% of the rate between 15 May and 21 August", which would be expressed as SeasonFactors={1,1.5,1} and SeasonSeq={1.01,5.15,8.21}.

    Alternative: Using WeightSpread Functions

    There is a further way of modelling seasonality, although it is not really seasonality that is modelled, rather, a time-dependent factor weighting applied to what is basically a Time Spread function. The functions concerned are FWeightSpread and TWeightSpread.

    These allow you to distribute a total amount over time according to weighting factors and a time scale specified either by FromDates or ToDates. What the weighting factors do is decide the fraction of the total that is distributed in each mini-time-window specified by FromDates or ToDates.

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure