Docstoc

Chart

Document Sample
Chart Powered By Docstoc
					               ARInc Reserves Analysis Spreadsheet!
                               Analysis File V3.6, 3/10




This File will enable you to:
  1) Work with Reserve Data
  2) Compute your Percent Funded
  3) Test various Funding Plans

This file is provided and intended only for same-year adjustments of Reserve Study information,
not multi-year updating of Reserve Studies. Multi-year use is not intended or supported.

Think of the Main sheet as your control panel, where you enter information about
your association and test your Funding Plan ideas.

The left-most portion of the Data Sheet is where your Reserve Component data goes.

Only make entries in cells that are colored orange, as shown below:
           25.55

Macros are provided to print key pages, add rows, and delete rows.

Any time you make a change to the data, press F9 to recalculate.
       (trust us, leave automatic recalculation off)
                                              Analysis File V3.6, 3/10




                                                                                                       Monthly Contributions
                                          Starting Information:
                              Client #: 22972-0
                                Name: The Meadows HOA
                            Location: Mill Creek, WA
                               # Units:                        129
                              Base Yr:                        2012
                         Period Start:                    1/1/2012
                          Period End:                   12/31/2012          date problem?
                Site Inspection Date:                    11/4/2011
                    Total Assessmts.                     $4,386.00        Per Unit:   $34.00
                        Res Contribs.                        $0.00        Per Unit:    $0.00
                Starting Reserve Bal:                      $37,412
                              Interest:                     0.26%
                             Inflation:                     3.00%

                                                      Status:
                    Proportional FFB:                           $66,519
                     Percent Funded:                             56.2%
                       Swain Factor:                            2.592%

                                               Recommendation:
                       Res Contrib.                        $1,250         Per Unit:    $9.69
                    Annual Increase:                        3.00%
                         # of Years:                            30
                 Secondary Increase:                        0.00%
                         # of Years:                             0
                         1st Yr S.A.:                           $0        Per Unit:    $0.00
                        2nd Yr S.A.:                            $0        Per Unit:    $0.00
                        3rd Yr S.A.:                            $0        Per Unit:    $0.00
                         4th Yr S.A.:                           $0        Per Unit:    $0.00
                         5th Yr S.A.:                           $0        Per Unit:    $0.00

                     Minimum Balance:                    $37,412.00
                           Min Margin:                        29.8%
                                                System Defaults:
             Current Annual Increase:                         3.00%
              Budget Cycles Per Year:                              12
                  Text Budget Cycles:                        Monthly (Monthly/Annual/Quarterly)
                  Initial Year Fraction:                         1.00
                        Deficit per unit:                        226
                       Solver Success:      Success
                          Solver Cycle1:                           20
                          Solver Cycle2:                           26
                          Solver Cycle3:                            2




Association Reserves, Inc.                                                                        8/28/2012
                                                                              Funding Plan

                                        $3,500

               Monthly Contributions    $3,000

                                        $2,500

                                        $2,000

                                        $1,500

                                        $1,000

                                         $500

                                              $0
                                                   2012          2017         2022     2027     2032    2037    2042
                                                                                       Years


                                                                       Annual Reserve Expenses

                                          $250,000


                                          $200,000


                                          $150,000


                                          $100,000


                                           $50,000


                                                   $0
                                                        2012          2017      2022     2027    2032   2037    2042

                                                                                        Years



                                                                             Percent Funded

                                       120%

                                       100%

                                       80%

                                       60%

                                       40%

                                       20%

                                        0%
                                              2012             2017          2022      2027      2032    2037     2042
                                                                                       Years




Association Reserves, Inc.                                                                                               8/28/2012
                                                   Raw Data for Reserve Analysis goes below double line


      Additional
         Sort 1    Category   #         Component Title                       Description
                                  100   Concrete - Repair/Replace             ~24,300 GSF sdwlks/curbs
                                  120   Asphalt Roads/Parking - Resurface     Approx 70,000 square ft
                                  121   Asphalt Pond Access - Resurface       ~1,260 SF, asphalt
                                  170   Landscape - Refurbish/Replenish       Extensive landscaping
                                  182   Detention Facilities - Maintain       Pond, catch basins, etc.
                                  200   Entry Sign/Monument - Replace         (1) metal sign/trellis
                                  205   Mailboxes - Replace                   (9) metal cluster stands
                                  340   Play Equipment - Replace              (2) assorted
                                  346   Benches, Trash, etc- Replace/Repair   (16) assorted




Association Reserves, Inc.                       8/28/2012                               Page 4 of 46
elow double line                                                                    Table 1: Executive Summary

                      Remaining              Current
             Useful      Useful | ----- Replacement Cost ----- |
               Life        Life             Low           High           Avg Cost       #
                  5           5          $3,000        $5,000             $4,000      100
                25           18       $112,000       $140,000           $126,000      120
                35           28          $2,020        $2,520             $2,270      121
                  3           1          $4,000        $6,000             $5,000      170
                15            8          $7,500       $12,500            $10,000      182
                20           13          $3,000        $5,000             $4,000      200
                20           13         $10,800       $14,400            $12,600      205
                15            8         $22,500       $37,500            $30,000      340
                20           13          $7,500        $9,500             $8,500      346
                                                                                        9




         Association Reserves, Inc.                         8/28/2012                                     Page 5 of 46
Table 1: Executive Summary                                                      22972-0 Table 2: Reserve Co

                                             Useful         Rem.      Current     Future
                                               Life        Useful    Average    Average
      Component                               (yrs)     Life (yrs)      Cost       Cost      #
      Concrete - Repair/Replace                   5              5     $4,000     $4,637    100
      Asphalt Roads/Parking - Resurface         25             18    $126,000   $214,507    120
      Asphalt Pond Access - Resurface           35             28      $2,270     $5,194    121
      Landscape - Refurbish/Replenish             3              1     $5,000     $5,150    170
      Detention Facilities - Maintain           15               8    $10,000    $12,668    182
      Entry Sign/Monument - Replace             20             13      $4,000     $5,874    200
      Mailboxes - Replace                       20             13     $12,600    $18,504    205
      Play Equipment - Replace                  15               8    $30,000    $38,003    340
      Benches, Trash, etc- Replace/Repair       20             13      $8,500    $12,483    346
      Total Funded Components                                                                9




     Association Reserves, Inc.             8/28/2012                                Page 6 of 46
Table 2: Reserve Component List Detail                                                                           22972-0 Table 3: Contributio

                                                                                 Rem.     |--------------Current-------------|
                                                                        Useful   Useful          Best                 Worst
      Component                             Quantity                      Life     Life          Cost                   Cost      #
      Concrete - Repair/Replace             ~24,300 GSF sdwlks/curbs        5        5       $3,000                 $5,000       100
      Asphalt Roads/Parking - Resurface     Approx 70,000 square ft        25       18    $112,000               $140,000        120
      Asphalt Pond Access - Resurface       ~1,260 SF, asphalt             35       28       $2,020                 $2,520       121
      Landscape - Refurbish/Replenish       Extensive landscaping           3        1       $4,000                 $6,000       170
      Detention Facilities - Maintain       Pond, catch basins, etc.       15        8       $7,500               $12,500        182
      Entry Sign/Monument - Replace         (1) metal sign/trellis         20       13       $3,000                 $5,000       200
      Mailboxes - Replace                   (9) metal cluster stands       20       13      $10,800               $14,400        205
      Play Equipment - Replace              (2) assorted                   15        8      $22,500               $37,500        340
      Benches, Trash, etc- Replace/Repair   (16) assorted                  20       13       $7,500                 $9,500       346
      Total Funded Components                                                                                                     9




      Association Reserves, Inc.                            8/28/2012                                                    Page 7 of 46
            Table 3: Contribution and Fund Breakdown                                                               22972-0

--------|                                                        Rem.                       Fully      Current
                                                        Useful   Useful         Current   Funded         Fund        Reserve
                  Component                               Life     Life      (Avg) Cost   Balance     Balance    Contributions
                  Concrete - Repair/Replace                 5        5          $4,000        $0        $0.00           $0.00
                  Asphalt Roads/Parking - Resurface        25       18        $126,000    $35,280   $35,280.00        $589.16
                  Asphalt Pond Access - Resurface          35       28          $2,270      $454      $454.00           $7.58
                  Landscape - Refurbish/Replenish           3        1          $5,000     $3,333    $1,678.00        $194.83
                  Detention Facilities - Maintain          15        8         $10,000     $4,667       $0.00          $77.93
                  Entry Sign/Monument - Replace            20       13          $4,000     $1,400       $0.00          $23.38
                  Mailboxes - Replace                      20       13         $12,600     $4,410       $0.00          $73.64
                  Play Equipment - Replace                 15        8         $30,000    $14,000       $0.00         $233.79
                  Benches, Trash, etc- Replace/Repair      20       13          $8,500     $2,975       $0.00          $49.68
                  Total Funded Components                                                 $66,519     $37,412          $1,250




                 Association Reserves, Inc.                               8/28/2012                                   Page 8 of 46
                                   Significance



    Contribution   Fully Funded                                                       Current
          Calcs    Running Total       # Component                             UL   (Avg) Cost         Cost/Yr
          $0.00              $0      100 Concrete - Repair/Replace              5     $4,000             $800
      $5,040.00         $35,280      120 Asphalt Roads/Parking - Resurface     25   $126,000           $5,040
         $64.86         $35,734      121 Asphalt Pond Access - Resurface       35     $2,270              $65
      $1,666.67         $39,067      170 Landscape - Refurbish/Replenish        3     $5,000           $1,667
       $666.67          $43,734      182 Detention Facilities - Maintain       15    $10,000             $667
       $200.00          $45,134      200 Entry Sign/Monument - Replace         20     $4,000             $200
       $630.00          $49,544      205 Mailboxes - Replace                   20    $12,600             $630
      $2,000.00         $63,544      340 Play Equipment - Replace              15    $30,000           $2,000
       $425.00          $66,519      346 Benches, Trash, etc- Replace/Repair   20     $8,500             $425
       $10,693                         9 Total Funded Components                                       $11,493




Association Reserves, Inc.                                   8/28/2012                         Page 9 of 46
                         Year Increment                                      0          1          2          3
                         Inflation                                       1.000      1.030      1.061      1.093
                         Current Contribution rate (period)                 $0         $0         $0         $0
                         Current Contribution rate (total)                  $0         $0         $0         $0
                         Current Interest Earnings                         $97        $91        $84        $85
                         Current Starting Balance (for charts)         $37,412    $37,509    $32,450    $32,535
                         Current Starting Balance (for calcs)          $37,412    $37,509    $32,450    $32,535
                         Current Percent Funded                           56%        47%        36%        31%
                         Recommended Annual Avg. Balance                $44,912    $57,679    $70,936        $87,273
                         Recommended Periodic Reserve Contribs           $1,250     $1,288     $1,326         $1,366
                         Recommended Percent Funded                      56.2%      65.4%      70.2%          75.4%
                         Reference Point                                  100%       100%       100%           100%


                  Table 5: 30-Year Income/Expense Detail (yrs 0 through 4)

                         Fiscal Year                                      2012       2013       2014           2015
                         Starting Reserve Balance                       $37,412    $52,529    $62,979        $79,077
                         Annual Reserve Contribution                    $15,000    $15,450    $15,914        $16,391
     22972-0             Planned Special Assessments                        $0         $0         $0             $0
                         Interest Earnings                                $117       $150       $185           $227
                         Total Income                                   $52,529    $68,129    $79,077        $95,695


   Significance        # Component
         7.0%        100 Concrete - Repair/Replace                          $0         $0         $0             $0
        43.9%        120 Asphalt Roads/Parking - Resurface                  $0         $0         $0             $0
         0.6%        121 Asphalt Pond Access - Resurface                    $0         $0         $0             $0
        14.5%        170 Landscape - Refurbish/Replenish                    $0      $5,150        $0             $0
         5.8%        182 Detention Facilities - Maintain                    $0         $0         $0             $0
         1.7%        200 Entry Sign/Monument - Replace                      $0         $0         $0             $0
         5.5%        205 Mailboxes - Replace                                $0         $0         $0             $0
        17.4%        340 Play Equipment - Replace                           $0         $0         $0             $0
         3.7%        346 Benches, Trash, etc- Replace/Repair                $0         $0         $0             $0
       100.0%            Total Expenses                                     $0      $5,150        $0             $0


                         Ending Reserve Balance:                        $52,529    $62,979    $79,077        $95,695




Association Reserves, Inc.                                 8/28/2012                         Page 10 of 46
          4
      1.126
         $0
         $0
        $78
    $32,619
    $32,619
       27%
     $101,323
       $1,407
       79.1%
        100%


    22972-0

        2016
      $95,695
      $16,883
          $0
        $264
     $112,842



          $0
          $0
          $0
       $5,628
          $0
          $0
          $0
          $0
          $0
       $5,628


     $107,214




Association Reserves, Inc.   8/28/2012   Page 11 of 46
       Year Increment                                 5          6          7          8           9
       Inflation                                  1.159      1.194      1.230      1.267       1.305
       Current Contribution rate (period)            $0         $0         $0         $0          $0
       Current Contribution rate (total)             $0         $0         $0         $0          $0
       Current Interest Earnings                    $64        $59        $51         $0          $0
       Current Starting Balance (for charts)    $27,070    $22,497    $22,555    $16,457          $0
       Current Starting Balance (for calcs)     $27,070    $22,497    $22,555    $16,457    -$34,214
       Current Percent Funded                      20%        16%        14%         9%          0%
       Recommended Annual Avg. Balance          $113,590   $129,217   $144,658   $135,350     $129,653
       Recommended Periodic Reserve Contribs      $1,449     $1,493     $1,537     $1,583       $1,631
       Recommended Percent Funded                 81.1%      82.9%      84.7%      85.6%        82.8%
       Reference Point                             100%       100%       100%       100%         100%


Table 5: 30-Year Income/Expense Detail (yrs 5 through 9)                                     22972-0

       Fiscal Year                                 2017       2018       2019       2020         2021
       Starting Reserve Balance                 $107,214   $120,262   $138,509   $151,184     $119,867
       Annual Reserve Contribution               $17,389    $17,911    $18,448    $19,002      $19,572
       Planned Special Assessments                   $0         $0         $0         $0           $0
       Interest Earnings                           $296       $336       $377       $352         $338
       Total Income                             $124,899   $138,509   $157,334   $170,538     $139,776


     # Component
   100 Concrete - Repair/Replace                  $4,637        $0         $0         $0           $0
   120 Asphalt Roads/Parking - Resurface             $0         $0         $0         $0           $0
   121 Asphalt Pond Access - Resurface               $0         $0         $0         $0           $0
   170 Landscape - Refurbish/Replenish               $0         $0      $6,149        $0           $0
   182 Detention Facilities - Maintain               $0         $0         $0     $12,668          $0
   200 Entry Sign/Monument - Replace                 $0         $0         $0         $0           $0
   205 Mailboxes - Replace                           $0         $0         $0         $0           $0
   340 Play Equipment - Replace                      $0         $0         $0     $38,003          $0
   346 Benches, Trash, etc- Replace/Repair           $0         $0         $0         $0           $0
       Total Expenses                             $4,637        $0      $6,149    $50,671          $0


       Ending Reserve Balance:                  $120,262   $138,509   $151,184   $119,867     $139,776




Association Reserves, Inc.                     8/28/2012                               Page 12 of 46
       Year Increment                                10            11          12          13          14
       Inflation                                  1.344         1.384       1.426       1.469       1.513
       Current Contribution rate (period)            $0            $0          $0          $0          $0
       Current Contribution rate (total)             $0            $0          $0          $0          $0
       Current Interest Earnings                     $0            $0          $0          $0          $0
       Current Starting Balance (for charts)         $0            $0          $0          $0          $0
       Current Starting Balance (for calcs)    -$34,214      -$46,309    -$46,309    -$46,309    -$90,512
       Current Percent Funded                       0%            0%          0%          0%          0%
       Recommended Annual Avg. Balance          $143,808      $158,596    $180,084    $180,158     $180,884
       Recommended Periodic Reserve Contribs      $1,680        $1,730      $1,782      $1,836       $1,891
       Recommended Percent Funded                 84.9%         85.7%       87.1%       88.0%        86.7%
       Reference Point                             100%          100%        100%        100%         100%


Table 5: 30-Year Income/Expense Detail (yrs 10 through 14)                                        22972-0

       Fiscal Year                                 2022          2023        2024        2025         2026
       Starting Reserve Balance                 $139,776      $148,214    $169,391    $191,246     $169,540
       Annual Reserve Contribution               $20,159       $20,764     $21,386     $22,028      $22,689
       Planned Special Assessments                   $0            $0          $0          $0           $0
       Interest Earnings                           $374          $413        $469        $469         $471
       Total Income                             $160,310      $169,391    $191,246    $213,743     $192,700


     # Component
   100 Concrete - Repair/Replace                  $5,376           $0          $0          $0           $0
   120 Asphalt Roads/Parking - Resurface             $0            $0          $0          $0           $0
   121 Asphalt Pond Access - Resurface               $0            $0          $0          $0           $0
   170 Landscape - Refurbish/Replenish            $6,720           $0          $0       $7,343          $0
   182 Detention Facilities - Maintain               $0            $0          $0          $0           $0
   200 Entry Sign/Monument - Replace                 $0            $0          $0       $5,874          $0
   205 Mailboxes - Replace                           $0            $0          $0      $18,504          $0
   340 Play Equipment - Replace                      $0            $0          $0          $0           $0
   346 Benches, Trash, etc- Replace/Repair           $0            $0          $0      $12,483          $0
       Total Expenses                            $12,095           $0          $0      $44,203          $0


       Ending Reserve Balance:                  $148,214      $169,391    $191,246    $169,540     $192,700




Association Reserves, Inc.                     8/28/2012                                    Page 13 of 46
       Year Increment                                15            16           17           18           19
       Inflation                                  1.558         1.605        1.653        1.702        1.754
       Current Contribution rate (period)            $0            $0           $0           $0           $0
       Current Contribution rate (total)             $0            $0           $0           $0           $0
       Current Interest Earnings                     $0            $0           $0           $0           $0
       Current Starting Balance (for charts)         $0            $0           $0           $0           $0
       Current Starting Balance (for calcs)    -$90,512      -$96,744    -$104,768    -$104,768    -$319,274
       Current Percent Funded                       0%            0%           0%           0%           0%
       Recommended Annual Avg. Balance          $201,268      $218,385     $239,373     $157,908      $72,601
       Recommended Periodic Reserve Contribs      $1,947        $2,006       $2,066       $2,128       $2,192
       Recommended Percent Funded                 87.8%         88.4%        88.7%        89.2%        70.3%
       Reference Point                             100%          100%         100%         100%         100%


Table 5: 30-Year Income/Expense Detail (yrs 15 through 19)                                          22972-0

       Fiscal Year                                 2027          2028         2029         2030         2031
       Starting Reserve Balance                 $192,700      $210,361     $226,977     $252,393      $63,834
       Annual Reserve Contribution               $23,370       $24,071      $24,793      $25,536      $26,303
       Planned Special Assessments                   $0            $0           $0           $0           $0
       Interest Earnings                           $524          $568         $623         $411         $189
       Total Income                             $216,593      $235,000     $252,393     $278,340      $90,325


     # Component
   100 Concrete - Repair/Replace                  $6,232           $0           $0           $0           $0
   120 Asphalt Roads/Parking - Resurface             $0            $0           $0      $214,507          $0
   121 Asphalt Pond Access - Resurface               $0            $0           $0           $0           $0
   170 Landscape - Refurbish/Replenish               $0         $8,024          $0           $0        $8,768
   182 Detention Facilities - Maintain               $0            $0           $0           $0           $0
   200 Entry Sign/Monument - Replace                 $0            $0           $0           $0           $0
   205 Mailboxes - Replace                           $0            $0           $0           $0           $0
   340 Play Equipment - Replace                      $0            $0           $0           $0           $0
   346 Benches, Trash, etc- Replace/Repair           $0            $0           $0           $0           $0
       Total Expenses                             $6,232        $8,024          $0      $214,507       $8,768


       Ending Reserve Balance:                  $210,361      $226,977     $252,393      $63,834      $81,558




Association Reserves, Inc.                     8/28/2012                                      Page 14 of 46
       Year Increment                                 20           21           22           23           24
       Inflation                                   1.806        1.860        1.916        1.974        2.033
       Current Contribution rate (period)             $0           $0           $0           $0           $0
       Current Contribution rate (total)              $0           $0           $0           $0           $0
       Current Interest Earnings                      $0           $0           $0           $0           $0
       Current Starting Balance (for charts)          $0           $0           $0           $0           $0
       Current Starting Balance (for calcs)    -$328,042    -$335,266    -$335,266    -$344,847    -$423,790
       Current Percent Funded                        0%           0%           0%           0%           0%
       Recommended Annual Avg. Balance            $91,491     $115,615     $139,449     $124,723     $115,623
       Recommended Periodic Reserve Contribs       $2,258       $2,325       $2,395       $2,467       $2,541
       Recommended Percent Funded                  77.5%        83.1%        87.7%        90.4%        89.4%
       Reference Point                              100%         100%         100%         100%         100%


Table 5: 30-Year Income/Expense Detail (yrs 20 through 24)                                          22972-0

       Fiscal Year                                  2032         2033         2034         2035         2036
       Starting Reserve Balance                   $81,558     $101,663     $129,868     $149,392     $100,377
       Annual Reserve Contribution                $27,092      $27,904      $28,742      $29,604      $30,492
       Planned Special Assessments                    $0           $0           $0           $0           $0
       Interest Earnings                            $238         $301         $363         $325         $301
       Total Income                              $108,887     $129,868     $158,973     $179,321     $131,170


     # Component
   100 Concrete - Repair/Replace                   $7,224          $0           $0           $0           $0
   120 Asphalt Roads/Parking - Resurface              $0           $0           $0           $0           $0
   121 Asphalt Pond Access - Resurface                $0           $0           $0           $0           $0
   170 Landscape - Refurbish/Replenish                $0           $0        $9,581          $0           $0
   182 Detention Facilities - Maintain                $0           $0           $0       $19,736          $0
   200 Entry Sign/Monument - Replace                  $0           $0           $0           $0           $0
   205 Mailboxes - Replace                            $0           $0           $0           $0           $0
   340 Play Equipment - Replace                       $0           $0           $0       $59,208          $0
   346 Benches, Trash, etc- Replace/Repair            $0           $0           $0           $0           $0
       Total Expenses                              $7,224          $0        $9,581      $78,943          $0


       Ending Reserve Balance:                   $101,663     $129,868     $149,392     $100,377     $131,170




Association Reserves, Inc.                     8/28/2012                                      Page 15 of 46
       Year Increment                                 25           26           27           28           29
       Inflation                                   2.094        2.157        2.221        2.288        2.357
       Current Contribution rate (period)             $0           $0           $0           $0           $0
       Current Contribution rate (total)              $0           $0           $0           $0           $0
       Current Interest Earnings                      $0           $0           $0           $0           $0
       Current Starting Balance (for charts)          $0           $0           $0           $0           $0
       Current Starting Balance (for calcs)    -$423,790    -$442,634    -$442,634    -$442,634    -$459,267
       Current Percent Funded                        0%           0%           0%           0%           0%
       Recommended Annual Avg. Balance           $137,452     $160,265     $193,517     $219,523     $246,611
       Recommended Periodic Reserve Contribs       $2,617       $2,696       $2,777       $2,860       $2,946
       Recommended Percent Funded                  93.9%        96.5%        98.6%        99.8%       100.7%
       Reference Point                              100%         100%         100%         100%         100%


Table 5: 30-Year Income/Expense Detail (yrs 25 through 29)                                          22972-0

       Fiscal Year                                  2037         2038         2039         2040         2041
       Starting Reserve Balance                  $131,170     $144,091     $176,857     $210,680     $228,937
       Annual Reserve Contribution                $31,407      $32,349      $33,319      $34,319      $35,348
       Planned Special Assessments                    $0           $0           $0           $0           $0
       Interest Earnings                            $358         $417         $504         $571         $642
       Total Income                              $162,935     $176,857     $210,680     $245,570     $264,927


     # Component
   100 Concrete - Repair/Replace                   $8,375          $0           $0           $0           $0
   120 Asphalt Roads/Parking - Resurface              $0           $0           $0           $0           $0
   121 Asphalt Pond Access - Resurface                $0           $0           $0        $5,194          $0
   170 Landscape - Refurbish/Replenish            $10,469          $0           $0       $11,440          $0
   182 Detention Facilities - Maintain                $0           $0           $0           $0           $0
   200 Entry Sign/Monument - Replace                  $0           $0           $0           $0           $0
   205 Mailboxes - Replace                            $0           $0           $0           $0           $0
   340 Play Equipment - Replace                       $0           $0           $0           $0           $0
   346 Benches, Trash, etc- Replace/Repair            $0           $0           $0           $0           $0
       Total Expenses                             $18,844          $0           $0       $16,633          $0


       Ending Reserve Balance:                   $144,091     $176,857     $210,680     $228,937     $264,927




Association Reserves, Inc.                     8/28/2012                                      Page 16 of 46
         30
      2.427
         $0
         $0
         $0
         $0
  -$459,267
        0%
     $278,277
       $3,034
      101.1%
        100%




        2042
     $264,927
      $36,409
          $0    RUL Calculations
        $724
     $302,061           0          1     2         3        4      5      6       7            8

                     2012      2013    2014    2015       2016   2017   2018   2019      2020
       $9,709           5         4       3       2          1      0      4      3         2
          $0           18        17      16      15         14     13     12     11        10
          $0           28        27      26      25         24     23     22     21        20
          $0            1         0       2       1          0      2      1      0         2
          $0            8         7       6       5          4      3      2      1         0
          $0           13        12      11      10          9      8      7      6         5
          $0           13        12      11      10          9      8      7      6         5
          $0            8         7       6       5          4      3      2      1         0
          $0           13        12      11      10          9      8      7      6         5
       $9,709


     $292,352




Association Reserves, Inc.                    8/28/2012                        Page 17 of 46
         9         10         11     12      13         14     15     16     17         18

     2021       2022         2023   2024   2025        2026   2027   2028   2029     2030
        1          0            4      3      2           1      0      4      3        2
        9          8            7      6      5           4      3      2      1        0
       19         18           17     16     15          14     13     12     11       10
        1          0            2      1      0           2      1      0      2        1
       14         13           12     11     10           9      8      7      6        5
        4          3            2      1      0          19     18     17     16       15
        4          3            2      1      0          19     18     17     16       15
       14         13           12     11     10           9      8      7      6        5
        4          3            2      1      0          19     18     17     16       15




Association Reserves, Inc.                 8/28/2012                         Page 18 of 46
        19         20         21     22      23         24     25     26     27         28

     2031       2032         2033   2034   2035        2036   2037   2038   2039     2040
        1          0            4      3      2           1      0      4      3        2
       24         23           22     21     20          19     18     17     16       15
        9          8            7      6      5           4      3      2      1        0
        0          2            1      0      2           1      0      2      1        0
        4          3            2      1      0          14     13     12     11       10
       14         13           12     11     10           9      8      7      6        5
       14         13           12     11     10           9      8      7      6        5
        4          3            2      1      0          14     13     12     11       10
       14         13           12     11     10           9      8      7      6        5




Association Reserves, Inc.                 8/28/2012                         Page 19 of 46
                        FFB Calculations
                                  0           1        2        3        4        5        6        7
        29         30             1        1.03   1.0609 1.092727 1.125509 1.159274 1.194052 1.229874

     2041       2042               0         1          2        3        4        5        6        7
        1          0             0.0     824.0     1697.4   2622.5   3601.6   4637.1    955.2   1967.8
       14         13         35280.0   41529.6    48122.4 55073.4 62398.2 70112.9 78234.3 86779.9
       34         33           454.0     534.4      619.3    708.7    803.0    902.2   1006.8   1116.7
        2          1          3333.3    5150.0     1768.2   3642.4   5627.5   1932.1   3980.2   6149.4
        9          8          4666.7    5493.3     6365.4   7284.8   8253.7   9274.2 10348.5 11478.8
        4          3          1400.0    1648.0     1909.6   2185.5   2476.1   2782.3   3104.5   3443.6
        4          3          4410.0    5191.2     6015.3   6884.2   7799.8   8764.1   9779.3 10847.5
        9          8         14000.0   16480.0    19096.2 21854.5 24761.2 27822.6 31045.4 34436.5
        4          3          2975.0    3502.0     4057.9   4644.1   5261.8   5912.3   6597.1   7317.7
                             66519.0   80352.6    89651.8 104900.2 120982.9 132139.8 145051.3 163538.0




Association Reserves, Inc.                         8/28/2012                             Page 20 of 46
        8        9       10       11       12       13        14       15       16       17
  1.26677 1.304773 1.343916 1.384234 1.425761 1.468534   1.51259 1.557967 1.604706 1.652848

        8        9       10       11       12       13       14       15       16       17
   3040.2   4175.3   5375.7   1107.4   2281.2   3524.5   4840.3   6231.9   1283.8   2644.6
  95767.8 105216.9 115146.8 125577.7 136530.9 148028.2 160092.5 172747.4 186017.6 199928.4
   1232.4   1354.0   1481.8   1616.0   1756.9   1904.9   2060.1   2223.0   2393.8   2572.8
   2111.3   4349.2   6719.6   2307.1   4752.5   7342.7   2521.0   5193.2   8023.5   2754.7
  12667.7    869.8   1791.9   2768.5   3802.0   4895.1   6050.4   7270.5   8558.4   9917.1
   3800.3   4175.3   4569.3   4983.2   5417.9   5874.1    302.5    623.2    962.8   1322.3
  11971.0 13152.1 14393.3 15697.2 17066.4 18503.5         952.9   1963.0   3032.9   4165.2
  38003.1   2609.5   5375.7   8305.4 11406.1 14685.3 18151.1 21811.5 25675.3 29751.3
   8075.7   8872.5   9709.8 10589.4 11513.0 12482.5       642.9   1324.3   2046.0   2809.8
 176669.5 144774.7 164563.8 172951.8 194526.9 217240.9 195613.6 219388.1 237994.1 255866.2




Association Reserves, Inc.              8/28/2012                              Page 21 of 46
       18       19       20       21       22       23       24       25       26       27
 1.702433 1.753506 1.806111 1.860295 1.916103 1.973587 2.032794 2.093778 2.156591 2.221289

       18         19       20       21       22       23       24       25       26       27
   4085.8     5611.2   7224.4   1488.2   3065.8   4736.6   6504.9   8375.1   1725.3   3554.1
 214506.6     8837.7 18205.6 28127.7 38628.6 49734.4 61471.7 73868.5 86953.8 100757.7
   2760.4     2956.9   3162.8   3378.3   3603.9   3840.0   4087.1   4345.5   4615.7   4898.3
   5674.8     8767.5   3010.2   6201.0   9580.5   3289.3   6776.0 10468.9    3594.3   7404.3
  11349.6    12859.0 14448.9 16122.6 17883.6 19735.9       1355.2   2791.7   4313.2   5923.4
   1702.4     2104.2   2528.6   2976.5   3449.0   3947.2   4472.1   5025.1   5607.1   6219.6
   5362.7     6628.3   7965.0   9375.9 10864.3 12433.6 14087.3 15829.0 17662.5 19591.8
  34048.7    38577.1 43346.7 48367.7 53650.9 59207.6       4065.6   8375.1 12939.5 17770.3
   3617.7     4471.4   5373.2   6325.0   7329.1   8387.7   9503.3 10678.3 11915.2 13216.7
 283108.5    90813.4 105265.2 122362.7 148055.8 165312.3 112323.2 139757.1 149326.6 179336.1




Association Reserves, Inc.                8/28/2012                             Page 22 of 46
       28       29       30
 2.287928 2.356566 2.427262

       28       29       30
   5491.0   7541.0   9709.0
 115311.6 130648.0 146800.8
   5193.6    152.8    314.9
  11439.6   3927.6   8090.9
   7626.4   9426.3 11327.2
   6863.8   7541.0   8252.7
  21620.9 23754.2 25996.0
  22879.3 28278.8 33981.7
  14585.5 16024.6 17537.0
 211011.8 227294.3 262010.2




Association Reserves, Inc.    8/28/2012   Page 23 of 46
      Table 4: 30-Year Reserve Plan Summary




                   Fiscal Year Beginning:    01/01/12             Interest:      0.3%    Inflation:



                   Starting         Fully                          Annual     Loans or
                   Reserve       Funded     Percent               Reserve      Special     Interest
        Year       Balance       Balance    Funded       Rating   Contribs.    Assmts      Income
       2012        $37,412       $66,519     56.2%        Fair    $15,000          $0        $117
       2013        $52,529       $80,353     65.4%        Fair    $15,450          $0        $150
       2014        $62,979       $89,652     70.2%       Strong   $15,914          $0        $185
       2015        $79,077      $104,900     75.4%       Strong   $16,391          $0        $227
       2016        $95,695      $120,983     79.1%       Strong   $16,883          $0        $264
       2017       $107,214      $132,140     81.1%       Strong   $17,389          $0        $296
       2018       $120,262      $145,051     82.9%       Strong   $17,911          $0        $336
       2019       $138,509      $163,538     84.7%       Strong   $18,448          $0        $377
       2020       $151,184      $176,669     85.6%       Strong   $19,002          $0        $352
       2021       $119,867      $144,775     82.8%       Strong   $19,572          $0        $338
       2022       $139,776      $164,564     84.9%       Strong   $20,159          $0        $374
       2023       $148,214      $172,952     85.7%       Strong   $20,764          $0        $413
       2024       $169,391      $194,527     87.1%       Strong   $21,386          $0        $469
       2025       $191,246      $217,241     88.0%       Strong   $22,028          $0        $469
       2026       $169,540      $195,614     86.7%       Strong   $22,689          $0        $471
       2027       $192,700      $219,388     87.8%       Strong   $23,370          $0        $524
       2028       $210,361      $237,994     88.4%       Strong   $24,071          $0        $568
       2029       $226,977      $255,866     88.7%       Strong   $24,793          $0        $623
       2030       $252,393      $283,109     89.2%       Strong   $25,536          $0        $411
       2031        $63,834       $90,813     70.3%       Strong   $26,303          $0        $189
       2032        $81,558      $105,265     77.5%       Strong   $27,092          $0        $238
       2033       $101,663      $122,363     83.1%       Strong   $27,904          $0        $301
       2034       $129,868      $148,056     87.7%       Strong   $28,742          $0        $363
       2035       $149,392      $165,312     90.4%       Strong   $29,604          $0        $325
       2036       $100,377      $112,323     89.4%       Strong   $30,492          $0        $301
       2037       $131,170      $139,757     93.9%       Strong   $31,407          $0        $358
       2038       $144,091      $149,327     96.5%       Strong   $32,349          $0        $417
       2039       $176,857      $179,336     98.6%       Strong   $33,319          $0        $504
       2040       $210,680      $211,012     99.8%       Strong   $34,319          $0        $571
       2041       $228,937      $227,294    100.7%       Strong   $35,348          $0        $642




      Top of Weak Range:             0.3 Weak

Association Reserves, Inc.                        8/28/2012                              Page 24 of 46
       Top of Fair Range:    0.7 Fair
     Top of Strong Range:    1.3 Strong
          Named Surplus:         Surplus




Association Reserves, Inc.                 8/28/2012   Page 25 of 46
     22972-0




         3.0%



     Projected
      Reserve
     Expenses
           $0
       $5,150
           $0
           $0
       $5,628
       $4,637
           $0
       $6,149
      $50,671
           $0
      $12,095
           $0
           $0
      $44,203
           $0
       $6,232
       $8,024
           $0
     $214,507
       $8,768
       $7,224
           $0
       $9,581
      $78,943
           $0
      $18,844
           $0
           $0
      $16,633
           $0




Association Reserves, Inc.   8/28/2012   Page 26 of 46
                                                                Reserve Budget Funding Summary
           22972-0          The Meadows HOA


                                                                      2012                Estimated                            01/01/12          01/01/12
                                              Useful Life        Rem. Useful Life      Replacement             2012        Current Fund     Fully Funded        Remaining Bal.
Code                                           Min        Max       Min        Max      Cost in 2012    Expenditures            Balance          Balance         to be Funded
I          Interiors                             0          0          0          0   $          -     $        -      $            -     $          -      $             -
R          Roofing                               0          0          0          0   $          -     $        -      $            -     $          -      $             -
C          Capital Assets                        0          0          0          0   $          -     $        -      $            -     $          -      $             -
P          Paint                                 0          0          0          0   $          -     $        -      $            -     $          -      $             -
H          Hardscape                             0          0          0          0   $          -     $        -      $            -     $          -      $             -
                                                                                      $          -     $        -      $            -     $          -      $             -

                                                                                                                       Percent Funded:             0.0%




   Association Reserves, Inc.                                                 8/28/2012                                                                              Page 27 of 46
         2012
 Contributions
$         -
$         -
$         -
$         -
$         -
$         -




   Association Reserves, Inc.   8/28/2012   Page 28 of 46
                               Annual Reserve Expenses

   $250,000



   $200,000



   $150,000



   $100,000



    $50,000



            $0
                 2012        2017   2022       2027    2032   2037   2042
                                               Years


Association Reserves, Inc.                 9999-0                      8/28/2012
                                                  Funding Plan               Recommended
                                                                             Current

                           $3,500

                           $3,000
   Monthly Contributions




                           $2,500

                           $2,000

                           $1,500

                           $1,000

                            $500

                              $0
                                    2012   2017    2022      2027     2032      2037       2042
                                                              Years


Association Reserves, Inc.                                9999-0                              8/28/2012
                                                         Target Fully Funded Balance
                             30-Yr Cash Flow             Recommended Funding Plan
                                                         Current Funding Plan
$300,000


$250,000


$200,000


$150,000


$100,000


  $50,000


          $0
               2012           2017   2022        2027        2032          2037        2042
                                                 Years


Association Reserves, Inc.                  9999-0                                     8/28/2012
                                                          Recommended Funding Plan
                             Percent Funded
                                                          Current Funding Plan

120%


100%


 80%


 60%


 40%


 20%


   0%
         2012                2017   2022           2027        2032              2037   2042
                                           Years


Association Reserves, Inc.                    9999-0                                    8/28/2012
 1                           Initial Settings:                                                                                     = administrator initial settings
 2   Initial Contrib:                                    $957.77 InitialContribution                                               = computed diagnostics used by solver
 3   # of units                                              129 NumUnits
 4   1AI & 2AI                                            3.00% AnnualIncreasePct
 5   1AIY & 2AIY                                              30 AnnualIncreaseYears
 6   Initial Cycle Contrib Change                         1.00% ContributionChangeCycle1                  Counters
 7   2nd Cycle Contrib Change                             0.50% ContributionChangeCycle2                  Pass #               1
 8   Min Expense Remainder                               10.00% MinExpRemainder                           Cycle1              20
 9   SA Rounder (per unit)                                   $10 RoundFactor                              Cycle2              26
10   SA Change                                           10.00% SAChangePct                               Cycle3               2
11   Early Special Assmt Range max yr                          4 MaxEarlyYears
12   Contribution rounder                                    $10 ContribRound                                  $1,250   Main!d30   Contrib Rate
13   Change in 2AI                                        0.50% ChangeIn2AI                                        30   Main!d32   # of Initial Years
14   2nd Cycle Contrib Boost                              4.00% Cycle2ContribBoost                             0.00%    Main!d33   % of Secondary Increase
15   1AI Change Increment                                      5 YearsIncrement                                    $0   Main!d35   Special Assmt
16   1AI minimum yr limit                                      5 YearsMinimum                              0.0276536               Std Dev last 5 yrs
17   2AI Lower limit                                      0.50% AnnualIncreaseLowerLimit2                           0              #downslope yrs, last 5 yrs
18   2AI upper limit (underfunded)                        6.00% AnnualIncreaseUpperLimit2                      97.9%               Avg Pct Funded, last 5 yrs
19   Surplus Threshold                                  200.00% SurplusThreshold                               2.27%               Avg Slope, last 5 yrs
20   Final Cycle Contrib Change                           0.50% ContributionChangeCycle3
21   Surplus Pct Funded Flatness                         15.00% SurplusPctFlatness
22   Percent Funded Flatness Criteria                     2.00% FlatnessCriteria
23   Baseline Funding Minimum                                 $0 BaselineFundingMin
24                                          Year               0             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
25                                  Expenditure               $0       $5,150             $0          $0       $5,628     $4,637           $0       $6,149       $50,671         $0    $12,095         $0         $0    $44,203         $0     $6,232     $8,024         $0   $214,507    $8,768     $7,224         $0     $9,581    $78,943         $0    $18,844         $0         $0    $16,633         $0     $9,709
26                             Ending Balance            $52,529      $62,979       $79,077     $95,695     $107,214    $120,262     $138,509     $151,184      $119,867   $139,776   $148,214   $169,391   $191,246   $169,540   $192,700   $210,361   $226,977   $252,393    $63,834   $81,558   $101,663   $129,868   $149,392   $100,377   $131,170   $144,091   $176,857   $210,680   $228,937   $264,927   $292,352
27                 Early Ending Bal Minimum              $52,529 MinEndBalance
28         Ending Balance to expense ratio               100.0%      1222.9%         100.0%     100.0%       1905.2%    2593.5%        100.0%      2458.5%       236.6%     100.0%    1225.4%     100.0%     100.0%     383.5%     100.0%    3375.6%    2828.9%     100.0%      29.8%    930.2%    1407.2%     100.0%    1559.3%     127.2%     100.0%     764.7%     100.0%     100.0%    1376.4%     100.0%    3011.1%
29                        $ to make margin ok                 $0            $0            $0          $0           $0         $0            $0           $0           $0         $0         $0         $0         $0         $0         $0         $0         $0         $0         $0        $0         $0         $0         $0         $0         $0         $0         $0         $0         $0         $0         $0
30         Minimum Ending Balance Margin                  29.8% MinEndBalanceMargin
31               Early Min Ending Bal Margin             100.0% MinEndBalanceMarginEarly
32               Early Min Ending Bal Needs                   $0 MinEndBalanceNeeds
33                                   Pct Funded           56.2%         65.4%         70.2%      75.4%         79.1%       81.1%        82.9%        84.7%        85.6%      82.8%      84.9%      85.7%      87.1%      88.0%      86.7%      87.8%      88.4%      88.7%      89.2%     70.3%      77.5%      83.1%      87.7%      90.4%      89.4%      93.9%      96.5%      98.6%      99.8%     100.7%     101.1%
34                         Pct Funded Change                             9.1%          4.9%        5.1%         3.7%        2.0%         1.8%         1.8%         0.9%      -2.8%       2.1%       0.8%       1.4%       1.0%      -1.4%       1.2%       0.6%       0.3%       0.4%    -18.9%       7.2%       5.6%       4.6%       2.7%      -1.0%       4.5%       2.6%       2.1%       1.2%       0.9%       0.4%
35              Avg % Funded Change 25-29                 2.27% AvgPctFundedChange25_29                   ac-ag
36                 Pct Funded Std Dev 25-29         0.027653551                                           ac-ag
37                      Avg Pct Funded 25-29              97.9% AvgPctFunded25_29                         ac-ag
38                   Yrs 25-29 with neg slope                  0 NumMonthLessThanZero
39                   Ending Balance Minimum              $52,529 EndingBalanceMin
40                                   Constraints Minimum         Maximum
41                      1st cycle underfunded               70%                              ConstraintCycle1UnderMax
                                                                         120% ConstraintCycle1UnderMin
42                                     2nd cycle            90%                              ConstraintCycle2Max
                                                                         110% ConstraintCycle2Min
43                                    Final Year            98%                              ConstraintFinalYearMax
                                                                         102% ConstraintFinalYearMin
44           # of allowable Cycle 1 iterations               500 MaxCycle1
45           # of allowable Cycle 2 iterations              1000 MaxCycle2
46           # of allowable Cycle 3 iterations                50 MaxCycle3
47              # of times through the solver                  1 MaxPass
Note: all cell references refer to "Solver" worksheet unless otherwise designated
Full Funding Solver

Start                    set annual increases
                                  Set 1AI = d4            (1st ramp annual increase)
                                  Set 1AIY = d5           (1st ramp annual increase years)
                                  Set 2AI = d4            (2nd ramp annual increase)
                                  Set 2AIY = d5           (2nd ramp annual increase years)
                         set pass counter (integers)
                                  set pass = 0
                         set cycle counters (integers)
                                  set cycle1 = 0
                                  set cycle2 = 0
                                  set cycle3 = 0
                         set initial contribution rate
                                  Set contribution (Main!RecommendedContrib) to d2
                         set flatness check
                                  set flat = 0
                         set 2nd cycle boost check
                                  set 2boost = 0
                         set initial special assessment
                                  set special assessment yr 1 (Main!SpecialAssessment) = $0.00
                         if crash in first few (as defined by 0-d11) yrs, then special assessment
                                  if d27<0, goto Special Assess
                                  Goto Cycle1

Restart                  set cycle counters (integers)
                                  set cycle1 = 0
                                  set cycle2 = 0
                                  set cycle3 = 0
                                  set pass = pass + 1
                         set initial contribution rate
                                  Set contribution (Main!RecommendedContrib) to d2
                         set initial special assessment
                                  set special assessment yr 1 (Main!SpecialAssessment) = $0.00
                         if crash in first few (as defined by 0-d11) yrs, then special assessment
                                  if d27<0, goto Special Assess
                                  Goto Cycle1

Special Assess           set special assmt amount needed to keep from going negative
                                Set special assessment yr 1 (Main!SpecialAssessment) = +d27
                                divide by d3 to get individual owner special assessment
                                round above up to nearest d9.
                                set special assmt yr1 = rounded up individual unit owner special assessment x d3
                                Goto-Cycle1

Special Assess Small     small nudges to increase special assessment
                                if d32>0, set special assmt yr1 = special assessment yr 1 + d32, otherwise…
                                If special assmt yr1 = 0, set special assmt to d2, otherwise…
                                SA = SA x (1+d10)
                                divide by d3 to get individual owner special assessment
                                round above up to nearest d9.
                                set special assmt yr1 = rounded up individual unit owner special assessment x d3
                                goto-Cycle1

Cycle1                   begin adjusting
                                set cycle1 = cycle1 + 1
                     test to see if too many cycles (looping broken)
                             if cycle1>d44 goto Cycle2
                     test to see if early min margin is not met and small special assessment is needed
                             if d31<d8, goto Special Assess Small
                     test to see if min margin is not met and max constraints are exceeded
                             if d30<d8 and d37>e41, go to Special Assess Small
                     test to see if min margin is not met and contrib needs increasing
                             if d30<d8 goto-Cycle1-low
                     test to see if max constraints are exceeded and contrib needs reducing
                             if d37>e41, goto-Cycle1-high
                     test to see if min constraints not met and contrib needs increasing
                             if d37<d41, goto-Cycle1-low
                     to get here, must be good, so move on to Cycle2
                             goto-Cycle2

Cycle1-low           contrib needs to be increased
                             increase Main!D30 by d6
                     try again to see if it passes the minimum ending balance criteria
                             goto-Cycle1

Cycle1-high          contrib needs to be decreased
                             decrease Main!D30 by d6
                     try again to see if it has dropped down enough to pass along
                             goto-Cycle1

Cycle2               tighten up constraints and work on leveling
                     set cycle counter
                 A           set Cycle2=Cycle2 + 1
                     Set/Reset 2AI (Main!d33)
                 B           set Main!d33=d4
                     test to see if too many cycles (looping broken)
                 C           if cycle2>maxcycle2 , goto SolverFailure
                     check # of declining years in final yrs & send to appropriate corrective measures.
                 D           if NumMonthLessThanZero >2, goto Cycle2-low
                             if NumMonthLessThanZero <=2, goto Cycle2-high

Cycle2-High          due to increasing trend, try to lower the final slope
                     first... set initial settings
                 E             set Main!D32=d5
                     lower secondary increase a notch, if not too low already
                 F             if(Main!D33-d13>d17, set Main!d33=Main!D33-d13, otherwise set Main!d33=d17)
Cycle2-High-yr       Now cycle # yrs of secondary increases by dropping years of first increase
                 G             if(Main!D32-d15>d16, set Main!D32=Main!D32-d15, otherwise set Main!D32=d16)
                     test to see if it flattened the slope enough
                               if 2Boost=0, then
                               if (ABS)d35<=d22, goto Cycle2-constraint
                               else, it has already been boosted and needs to just hunt for a solution,
                 H             if (ABS)d35<=d22 and d37<=e42 and d37 >= d42, goto Cycle2-constraint
                     test to see if flattened slope drops below minimum margin
                 I             if d30<d8, set 2Boost=1 and goto Cycle2-Boost
                     test to see if flattened profile falls below ending constraints
                 J             if flat=0, then if d37<d42, goto Cycle2-Boost
                     still not flat enough, so try more years of lowered secondary ramp
                 K             if Main!D32>d16, goto Cycle2-High-yr
                     Since 1AI-yrs are at minimum, drop secondary ramp value & retry
                 L             if Main!d33>d17, goto Cycle2-High
                     Since 1AI-yrs are at minimum and 2AI is at minimum, go to SolverFailure
                    M          goto SolverFailure

Cycle2-Low              due to decreasing trend, try to increase the final slope
                        first… set initial settings
                    E            set Main!D32=d5
                        increase secondary increase a notch, if not too high already
                    F            if(Main!D33+d13<d18, set Main!d33=Main!D33+d13, otherwise set Main!d33=d18)
Cycle2-Low-yr           Now test # yrs of secondary increases
                    G            if(Main!D32-d15>d16, set Main!D32=Main!D32-d15, otherwise set Main!D32=d16)
                        test to see if it flattened the slope enough
                                 if main!Percent Funded => d19, then
                                 if (ABS)d35<=d21, goto Cycle2-Constraint
                                 else, it not a surplus funded situation,
                    H            if (ABS)d35<=d22, goto Cycle2-constraint
                        test to see if flattened profile is too high for ending constraints
                    I            if flat=0, if d37>e42, goto Cycle2-Trim
                        still not flat enough, so try more years of raised secondary ramp
                    J            if Main!D32>d16, goto Cycle2-Low-yr
                        Since1AI-yrs are at minimum, increase secondary ramp value and retry
                    K            if Main!d33<d18, goto Cycle2-Low
                        Since, 1AI yrs are at minimum and 2AI is at maximum, go to SolverFailure
                    L            goto SolverFailure

Cycle2-Constraint       test to see if flattened profile meets ending criteria
                        But first, document that it got here (was flat enough)...for use in surplus funded situations,
                        to avoid ever trimming something that needs to be boosted)
                    M           set flat = 1
                        test to see if minimum margin met
                    N           if d30<d8, set 2Boost=1 and goto Cycle2-Boost
                        test to see if flattened profile falls below ending constraints
                    O           if d35<d42, goto Cycle2-Boost
                        test to see if flattened profile is too high for ending constraints
                    P           if 2boost=0, if d35>e42, goto Cycle2-Trim
                        test to see if too many passes
                    Q           if pass < d71, go to restart
                        otherwise, must be within acceptable range
                                goto Cycle3

Cycle2-Boost            flattened profile now too low, so boost contrib a bit
                                if 2boost=0, then
                                Increase Main!D30 (contribution) by d7
                                otherwise increase Main!d30 (contribution) by d14
                        try again to flatten it out and pass the min ending balance criteria
                                goto-Cycle2

Cycle2-Trim             flattened profile now too high, so trim contrib a bit
                                Decrease Main!D30 (contribution) by d7
                        try again to see if it has dropped down enough to pass along
                                goto-Cycle2

Cycle3                  Now make minor adjustments to polish contrib & tune in to 100% zone at end
                        Assume no more slope changes necessary. Contrib tweak only
                        set Cycle3 counter
                                set cycle3=cycle3+1
                        test to see if it is looping almost endlessly
                                if cycle3=>d46, goto solverfailure
                        test to see if too low, & if so, boost contribution a bit
                        if d37<d43, goto Cycle3-Boost
                test to see if too high, & if so, drop contribution a bit
                        if d37>e43, goto Cycle3-Trim
                Now create a rounded "final candidate" contribution
                        Round contribution to nearest d12
                otherwise, must be good. Finished
                        goto SolverDone

Cycle3-Boost    Final year slightly too low, so boost contrib a bit
                       increase contribution by d20
                       goto Cycle3

Cycle3-Trim     Final year slightly too high, so trim contrib a bit
                       decrease contribution by d20
                       goto Cycle3

SolverFailure   Indicate failure
                       set Main!d55="Failure"
                       goto Display

SolverDone      Indicate Success
                       set Main!d55="Success"
                       goto Display

Display         Show # of cycles
                      set Main!d56=Cycle1
                      set Main!d57=Cycle2
                      set Main!d58=Cycle3
Note: all cell references refer to "Solver" worksheet unless otherwise designated
Baseline Solver

Start                      Set Cycle Counters
                                    set cycle1 = 0
                                    set cycle2 = 0
                           Set Annual Increases
                                    set 1AI = d4
                                    set 1AIY=d5
                           Note: only one ramp that goes 30 yrs. No need for 2AI or 2AIY
                           Set initial contribution rate
                                    Set contribution (Main!RecommendedContrib) to d2
                           check to see if min margin met
                                    set margin1 = N
                                    set margin2 = N
                           set initial special assessment
                                    set special assessment yr 1 (Main!SpecialAssessment) = $0.00
                           if crash in first few (as defined by 0-d11) yrs, then special assessment
                                    if d27<0, goto Special Assess
                                    Goto Cycle1


Special Assess             set special assmt amount needed to keep from going negative
                                  Set special assessment yr 1 (Main!SpecialAssessment) = +d27
                                  divide by d3 to get individual owner special assessment
                                  round above up to nearest d9.
                                  set special assmt yr1 = rounded up individual unit owner special assessment x d3
                                  Goto-Cycle1

Special Assess Small       small nudges to increase special assessment
                                  if d32>0, set special assmt yr1 = special assessment yr 1 + d32, otherwise…
                                  If special assmt yr1 = 0, set special assmt to d2, otherwise…
                                  SA = SA x (1+d10)
                                  divide by d3 to get individual owner special assessment
                                  round above up to nearest d9.
                                  set special assmt yr1 = rounded up individual unit owner special assessment x d3
                                  goto-Cycle1

Cycle1                     begin adjusting
                       A            set cycle1 = cycle1 + 1
                           test to see if too many cycles (looping broken)
                       B            if cycle1>d44 goto SolverFailure
                           test to see if early min bal is not met and small special assessment is needed
                       C            if d27<d23, goto Special Assess Small
                           test to see if min bal is not met and ending is too high
                       D            if d39<d23 and d37>e41, go to Special Assess Small
                           test to see if min bal is not met and contrib needs increasing
                       E            if d39<d23 goto-Cycle1-low
                           test to see if min bal is met and contrib needs reducing
                       F            if d39>=d23 goto-Cycle1-high
                           if here, must be a problem
                       G            goto SolverFailure

Cycle1-low                 first - check if there was prior success & only dipped low this first time
                                    if margin1 = Y, goto-Cycle2
                           otherwise, confirm unsuccessful Marginmet setting
                                    set margin1 = N
               contrib needs to be increased
                       increase Main!D30 by d6
               return to Cycle1 adjustments
                       goto-Cycle1

Cycle1-high    contrib needs to be decreased
                       decrease Main!D30 by d6
               confirm successful Marginmet setting
                       set margin1 = Y
               return to Cycle1 adjustments
                       goto-Cycle1

Cycle2         Now make minor adjustments to polish contrib
               set Cycle2 counter
                       set cycle2=cycle2+1
               test to see if it is looping almost endlessly
                       if cycle2=>d46, goto solverfailure
               test to see if successful margin
                       if d39<d23 goto-Cycle2-Boost
               test to see if too high, & if so, drop contribution a bit
                       if d39>=d23 goto Cycle2-Trim


Cycle2-Boost   Contrib slightly too low, so boost contrib a bit
                      increase contribution by d20
               check to see if that was enough to provide a solution
                      if d39>=d23 and Margin2=N, goto Cycle3
               otherwise, confirm unsuccessful Marginmet setting
                      set margin2 = N
                      goto Cycle2

Cycle2-Trim    Final year slightly too high, so trim contrib a bit
                      decrease contribution by d20
                      goto Cycle2

Cycle3         Trim any overfunding
                       Set Main!d32=d5
               lower secondary increase a substantial notch
                       if(Main!D33-(2*d13)>d17, set Main!d33=Main!D33-(2*d13), otherwise set Main!d33=d17)
Cycle3yrs      Now cycle # yrs of secondary increases by dropping years of first increase
                       if(Main!D32-d15>d16, set Main!D32=Main!D32-d15, otherwise set Main!D32=d16)
               test to see if margin is still ok & we can drop further
                       if d39>=d23 and Main!d32>d16, goto Cycle3yrs
               test to see if we went too far and we need to back up away from sensitive yr.
                       if d39<d23, set Main!d32=Main!d32+d15.
Cycle3AI       If here, must still be ok. So try a lower 2AI
                       if(Main!D33-d13>d17, set Main!d33=Main!D33-d13, otherwise set Main!d33=d17)
               test to see if margin is still ok & we can drop further
                       if d39>=d23 and Main!d33>d17, goto Cycle3AI
               test to see if we went too far and we need to back up
                       if d39<d23, set Main!d33=Main!d33+d13.
               If here, must be ok. Time to wrap it up.
                       Goto Finisher

Finisher       Now create a rounded "final candidate" contribution
                     Roundup contribution to nearest d12
               Must be good. Finished
                       goto SolverDone

SolverFailure   Indicate failure
                       set Main!d55="Failure"
                       goto Display

SolverDone      Indicate Success
                       set Main!d55="Success"
                       goto Display

Display         Show # of cycles
                      set Main!d56=Cycle1
                      set Main!d57=Cycle2
Do While d39>=d23 and Main!d32>d16
          This is the only action inside the loop
Loop

After exiting the loop, perform this check one time and move on regardless of the outcome.


This works exactly the same way as Cycle3yrs
Cycle 2 #   Contrib   1AI Yrs   2AI %   Slope   % Funded   At the End of Branch
             $1,157        30   3.00%   4.75%     71.25%   Cycle 2
       1     $1,163        25   2.50%   4.52%     72.76%   Cycle 2 High
             $1,163        25   3.00%   4.60%     72.90%   Cycle 2
       2     $1,169        25   2.50%   4.37%     74.41%   Cycle 2 High
             $1,169        25   3.00%   4.45%     74.55%   Cycle 2
       3     $1,175        25   2.50%   4.21%     76.07%   Cycle 2 High
             $1,175        25   3.00%   4.30%     76.21%   Cycle 2
       4     $1,180        25   2.50%   4.06%     77.74%   Cycle 2 High
             $1,180        25   3.00%   4.14%     77.88%   Cycle 2
       5     $1,186        25   2.50%   3.90%     79.41%   Cycle 2 High
             $1,186        25   3.00%   3.99%     79.55%   Cycle 2
       6     $1,192        25   2.50%   3.75%     81.10%   Cycle 2 High
             $1,192        25   3.00%   3.83%     81.24%   Cycle 2
       7     $1,198        25   2.50%   3.59%     82.79%   Cycle 2 High
             $1,198        25   3.00%   3.67%     82.93%   Cycle 2
       8     $1,204        25   2.50%   3.43%     84.49%   Cycle 2 High
             $1,204        25   3.00%   3.52%     84.64%   Cycle 2
       9     $1,210        25   2.50%   3.27%     86.20%   Cycle 2 High
             $1,210        25   3.00%   3.36%     86.35%   Cycle 2
      10     $1,216        25   2.50%   3.11%     87.92%   Cycle 2 High
             $1,216        25   3.00%   3.20%     88.07%   Cycle 2
      11     $1,222        25   2.50%   2.95%     89.65%   Cycle 2 High
             $1,222        25   3.00%   3.04%     89.80%   Cycle 2
      12     $1,228        25   2.50%   2.79%     91.39%   Cycle 2 High
             $1,228        25   3.00%   2.88%     91.53%   Cycle 2
      13     $1,228        25   2.50%   2.79%     91.39%   Cycle 2 High Yr
      14     $1,235        20   2.50%   2.38%     91.56%   Cycle 2 High
             $1,235        20   3.00%   2.72%     93.28%   Cycle 2
      15     $1,235        25   2.50%   2.63%     93.13%   Cycle 2 High Yr
      16     $1,235        20   2.50%   2.38%     91.56%   Cycle 2 High Yr
      17     $1,241        15   2.50%   2.23%     89.88%   Cycle 2 High
             $1,241        15   3.00%   2.55%     95.03%   Cycle 2
      18     $1,241        25   2.50%   2.47%     94.88%   Cycle 2 High Yr
      19     $1,241        20   2.50%   2.22%     93.30%   Cycle 2 High Yr
      20     $1,247        15   2.50%   2.07%     91.61%   Cycle 2 High
             $1,247        15   3.00%   2.39%     96.79%   Cycle 2
      21     $1,247        25   2.50%   2.30%     96.65%   Cycle 2 High Yr
      22     $1,247        20   2.50%   2.05%     95.06%   Cycle 2 High Yr
      23     $1,247        15   2.50%   2.07%     91.61%   Cycle 2 High Yr
      24     $1,253        10   2.50%   2.18%     88.33%   Cycle 2 High
             $1,253        10   3.00%   2.23%     98.57%   Cycle 2
      25     $1,253        25   2.50%   2.14%     98.42%   Cycle 2 High Yr
      26     $1,253        20   2.50%   1.88%     96.82%   Cycle 2 High
Last Operation
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
Boost - Normal
Reset 2AI
No Boost or Trim
Boost - Normal
Reset 2AI
No Boost or Trim
No Boost or Trim
Boost - Normal
Reset 2AI
No Boost or Trim
No Boost or Trim
Boost - Normal
Reset 2AI
No Boost or Trim
No Boost or Trim
No Boost or Trim
Boost - Normal
Reset 2AI
No Boost or Trim
No Boost or Trim
Loop #   Contrib Special Assmt    Margin1   MinEndBalMargin   1AI Yrs   2AI %
     1    $5,449         $0.00   FALSE            -2263.98%        30   3.50%
     2    $5,503         $0.00   FALSE            -2114.96%        30   3.50%
     3    $5,558         $0.00   FALSE            -1964.45%        30   3.50%
     4    $5,614         $0.00   FALSE            -1812.43%        30   3.50%
     5    $5,670         $0.00   FALSE            -1657.53%        30   3.50%
     6    $5,727         $0.00   FALSE            -1500.32%        30   3.50%
     7    $5,784         $0.00   FALSE            -1341.54%        30   3.50%
     8    $5,842         $0.00   FALSE            -1181.18%        30   3.50%
     9    $5,900         $0.00   FALSE            -1019.21%        30   3.50%
    10    $5,959         $0.00   FALSE             -855.62%        30   3.50%
    11    $6,019         $0.00   FALSE             -690.40%        30   3.50%
    12    $6,079         $0.00   FALSE             -523.52%        30   3.50%
    13    $6,140         $0.00   FALSE             -354.97%        30   3.50%
    14    $6,201         $0.00   FALSE             -184.74%        30   3.50%
    15    $6,263         $0.00   FALSE              -28.90%        30   3.50%
    16    $6,326         $0.00   FALSE              -27.22%        30   3.50%
    17    $6,389         $0.00   FALSE              -25.53%        30   3.50%
    18    $6,453         $0.00   FALSE              -23.81%        30   3.50%
    19    $6,517         $0.00   FALSE              -22.08%        30   3.50%
    20    $6,583         $0.00   FALSE              -20.34%        30   3.50%
    21    $6,648         $0.00   FALSE              -18.57%        30   3.50%
    22    $6,715         $0.00   FALSE              -16.79%        30   3.50%
    23    $6,782         $0.00   FALSE              -14.99%        30   3.50%
    24    $6,850         $0.00   FALSE              -13.17%        30   3.50%
    25    $6,918         $0.00   FALSE              -11.34%        30   3.50%
    26    $6,988         $0.00   FALSE               -9.48%        30   3.50%
    27    $7,057         $0.00   FALSE               -7.61%        30   3.50%
    28    $7,128         $0.00   FALSE               -5.72%        30   3.50%
    29    $7,199         $0.00   FALSE               -3.81%        30   3.50%
    30    $7,271         $0.00   FALSE               -1.88%        30   3.50%
    31    $7,344         $0.00   FALSE                0.07%        30   3.50%
    32    $7,271         $0.00   TRUE                -1.90%        30   3.50%
    33    $7,271         $0.00   TRUE                -1.90%        30   3.50%
     1    $7,307         $0.00   TRUE                -0.92%        30   3.50%
     2    $7,343         $0.00   TRUE                 0.06%        30   3.50%
     1    $7,343         $0.00   TRUE                 0.06%        25   2.50%
     2    $7,343         $0.00   TRUE                 0.06%        20   2.50%
     3    $7,343         $0.00   TRUE                -0.47%        15   2.50%
     4    $7,343         $0.00   TRUE                 0.06%        20   2.50%
     5    $7,343         $0.00   TRUE                 0.06%        20   2.00%
     6    $7,343         $0.00   TRUE                 0.06%        20   1.50%
     7    $7,343         $0.00   TRUE                 0.06%        20   1.00%
     8    $7,343         $0.00   TRUE                 0.06%        20   0.50%
     9    $7,350         $0.00   TRUE                 0.23%        20   0.50%
Cycle #   Last Operation
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-low
Cycle 1   Cycle1-high
Cycle 1   Cycle1-low - Margin1 flag TRUE - Exiting Cycle 1
Cycle 2   Cycle2-Boost
Cycle 2   Cycle2-Boost - Condition met. Exiting Cycle 2
Cycle 3   Cycle3yrs
Cycle 3   Cycle3yrs
Cycle 3   Cycle3yrs
Cycle 3   Cycle3yrs - Post process increment
Cycle 3   Cycle3AI
Cycle 3   Cycle3AI
Cycle 3   Cycle3AI
Cycle 3   Cycle3AI
Cycle 3   Final Candidate

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:14
posted:8/29/2012
language:English
pages:46