Contents

Document Sample
Contents Powered By Docstoc
					Best Practice Modelling
SMA 15. Visual Basic Programming - Best Practice Model Example
Go to Table of Contents




Primary Developer: BPM

Cover Notes:
 - Provides a basic example of best practice VBA programming.
 - Use of this model is subject to the training model terms and conditions on the Best Practice Modelling website.
  Table of Contents
  SMA 15. Visual Basic Programming - Best Practice Model Example
  Go to Cover Sheet



 Section & Sheet Titles                                           Page

   1. Assumptions                                                   3
            a. Time Series Assumptions                              4
            b. Revenue Assumptions                                  5
   2. Outputs                                                       6
            a. Revenue Outputs                                      7
   3. Appendices                                                    8
      3.1. Lookup Tables                                            9
            a. Time Series Lookup Tables                            10
      3.2. Checks                                                   13
            a. Checks                                               14
               - Error Checks                                        -
               - Sensitivity Checks                                  -
               - Alert Checks                                        -


  Total Pages:                                                      16
                            Assumptions
                            Section 1.
                            SMA 15. Visual Basic Programming - Best Practice Model Example
                           Go to Table of Contents
                             



                           Section Cover Notes:
                           [Insert section cover note 1]
                           [Insert section cover note 2]
                           [Insert section cover note 3]




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Assumptions_SC
Printed: 3:48 AM on 2/23/2012                                    Page 3 of 16
     Time Series Assumptions
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
    x h O


     Time Series Assumptions

         Core Time Series Assumptions

              Title                                                                             Primary
              Periodicity                                                                       Annual
              Financial Year End                                                     31   31    December 12
              Start Date                                                                        1-Jan-10
              Periods                                                                               5
              Denomination                                                          $Millions      2


         Historical & Forecast Period Titles

              Include in Period Titles?                                                          TRUE
              Actual Periods                                                                       3
              Budget Periods                                                                       -

         Data & Projections - Timing Assumptions

              Data Term Basis                                                                    1
                                                                                    Active Data Periods
              Data - Active Periods                                                                 3
              Projections - Start Date                                                          1-Jan-13

         Notes:
           - A Financial Year End assumption of 28th of February is assumed to be a month end financial year end, even in a leap year.
           - The "Model Denomination" assumption will not necessarily automatically change the denomination of the outputs of this model.
           - A "Budget Period" refers to either a period in the current financial year or periods containing combined actual and forecast data.
           - "Data & Projections - Timing Assumptions" are used as the basis for related data and projections time series sheets.
           - Use the bpmToolbox "Update Time Series Columns" tool to hide inactive data and projections time series periods.




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
TS_BA
Printed: 3:48 AM on 2/23/2012                                                                              Page 4 of 16
     Revenue Assumptions
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
    x h O


     Year Ending 31 December                          2010 (A)   2011 (A)     2012 (A)      2013 (F)   2014 (F)



     Revenue Assumptions ($Millions)

         Category
         Apples                                          100.0      101.0           102.0      103.0      104.0
         Oranges                                         101.0      102.0           103.0      104.0      105.0
         Bananas                                         102.0      103.0           104.0      105.0      106.0
         Peaches                                         103.0      104.0           105.0      106.0      107.0
         Pears                                           104.0      105.0           106.0      107.0      108.0
         Plums                                           105.0      106.0           107.0      108.0      109.0
         Total                                          615.0      621.0           627.0      633.0      639.0


              Reset Revenue Assumptions
                                           Populate
                    (Best Practice)




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Rev_TA
Printed: 3:48 AM on 2/23/2012                                       Page 5 of 16
                            Outputs
                            Section 2.
                            SMA 15. Visual Basic Programming - Best Practice Model Example
                           Go to Table of Contents
                             



                           Section Cover Notes:
                           [Insert section cover note 1]
                           [Insert section cover note 2]
                           [Insert section cover note 3]




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Outputs_SC
Printed: 3:48 AM on 2/23/2012                                    Page 6 of 16
     Revenue Outputs
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
    x h O


     Year Ending 31 December                      2010 (A)   2011 (A)     2012 (A)      2013 (F)   2014 (F)



     Revenue Outputs ($Millions)

         Category
         Apples                                      100.0      101.0           102.0      103.0      104.0
         Oranges                                     101.0      102.0           103.0      104.0      105.0
         Bananas                                     102.0      103.0           104.0      105.0      106.0
         Peaches                                     103.0      104.0           105.0      106.0      107.0
         Pears                                       104.0      105.0           106.0      107.0      108.0
         Plums                                       105.0      106.0           107.0      108.0      109.0
         Total                                      615.0      621.0           627.0      633.0      639.0




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Rev_TO
Printed: 3:48 AM on 2/23/2012                                   Page 7 of 16
                            Appendices
                            Section 3.
                            SMA 15. Visual Basic Programming - Best Practice Model Example
                           Go to Table of Contents
                             



                           Section Cover Notes:
                           [Insert section cover note 1]
                           [Insert section cover note 2]
                           [Insert section cover note 3]




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Appendices_SC
Printed: 3:48 AM on 2/23/2012                                    Page 8 of 16
                            Lookup Tables
                            Sub-Section 3.1.
                            SMA 15. Visual Basic Programming - Best Practice Model Example
                           Go to Table of Contents
                             



                           Sub-Section Cover Notes:
                           [Insert sub-section cover note 1]
                           [Insert sub-section cover note 2]
                           [Insert sub-section cover note 3]




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Lookup_Tables_SSC
Printed: 3:48 AM on 2/23/2012                                    Page 9 of 16
     Time Series Lookup Tables
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
   


     Time Series Lookup Tables

         Month Days                        Names

                            Month Days     LU_Mth_Days
                                1
                                2
                                3
                                4
                                5
                                6
                                7
                                8
                                9
                                10
                                11
                                12
                                13
                                14
                                15
                                16
                                17
                                18
                                19
                                20
                                21
                                22
                                23
                                24
                                25
                                26
                                27
                                28
                                29
                                30
                                31




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
TS_LU
Printed: 3:48 AM on 2/23/2012                               Page 10 of 16
     Time Series Lookup Tables
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
   


         Month Names                           Names

                           Month Names         LU_Mth_Names
                              January
                             February
                               March
                                April
                                May
                                June
                                July
                              August
                            September
                              October
                             November
                             December

         Denomination                          Names

                           Denomination        LU_Denom
                              $Billions        Billions
                             $Millions         Millions
                               $'000           Thousands
                                  $            Currency

         Data Term Basis                       Names

                         Data Term Basis       LU_Data_Term_Basis
                         Active Data Periods
                          Projections Start

         Periodicity                           Names

                             Periodicity       LU_Periodicity
                               Annual          Annual
                             Semi-Annual       Semi_Annual
                              Quarterly        Qtrly
                               Monthly         Mthly




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
TS_LU
Printed: 3:48 AM on 2/23/2012                                       Page 11 of 16
     Time Series Lookup Tables
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
   



         Period Type Names                   Names

                        Period Type Names    LU_Period_Type_Names
                                Year         Yr_Name
                             Half Year       Half_Yr_Name
                              Quarter        Qtr_Name
                               Month         Mth_Name

         Periods In Year                     Names

                          Periods In Year    LU_Pers_In_Yr
                                 1           Yrs_In_Yr
                                 2           Halves_In_Yr
                                 4           Qtrs_In_Yr
                                12           Mths_In_Yr

         Conversion Factors                  Names

                        Conversion Factors
                                 10          Ten
                                100          Hundred
                               1,000         Thousand
                             1,000,000       Million
                           1,000,000,000     Billion




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
TS_LU
Printed: 3:48 AM on 2/23/2012                                       Page 12 of 16
                            Checks
                            Sub-Section 3.2.
                            SMA 15. Visual Basic Programming - Best Practice Model Example
                           Go to Table of Contents
                             



                           Sub-Section Cover Notes:
                           [Insert sub-section cover note 1]
                           [Insert sub-section cover note 2]
                           [Insert sub-section cover note 3]




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Checks_SSC
Printed: 3:48 AM on 2/23/2012                                   Page 13 of 16
     Checks
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
            x h O


     Error Checks

          ## Include summary in model name?


         Errors Detected - Summary

              Total Errors:                    -
              Error Message (Empty if None):

         Error Checks

              Error Checks                                Check      Include?   Flag

              Total Errors:                                                      -




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Checks_BO
Printed: 3:48 AM on 2/23/2012                               Page 14 of 16
     Checks
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
            x h O


     Sensitivity Checks

          ## Include summary in model name?


         Sensitivities Detected - Summary

              Total Sensitivities:                   -
              Sensitivity Message (Empty if None):

         Sensitivity Checks

              Sensitivity Checks                          Check      Include?   Flag

              Total Sensitivities:                                               -




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Checks_BO
Printed: 3:48 AM on 2/23/2012                               Page 15 of 16
     Checks
     SMA 15. Visual Basic Programming - Best Practice Model Example
     Go to Table of Contents
            x h O


     Alert Checks

          ## Include summary in model name?


         Alerts Detected - Summary

              Total Alerts:                    -
              Alert Message (Empty if None):

         Alert Checks

              Alert Checks                                Check      Include?   Flag

              Total Alerts:                                                      -




afd7fb96-fc50-4b63-bf74-50082ca6c7c6.xls
Checks_BO
Printed: 3:48 AM on 2/23/2012                               Page 16 of 16

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:2/22/2012
language:
pages:16