Balance Sheet of New Project for 5 Year

Document Sample
Balance Sheet of New Project for 5 Year Powered By Docstoc
					11/3/2008                                                                                                      5:42 PM


                                             Bent Cycling
                                             Author    Star Student
                                             Date      11/3/2008
                                             Purpose   To analyze different investment and loan scenarios




SS Investment Analysis.xlsx, Documentation                            Page 1 of 3                           Star Student
11/3/2008                                                                                                                            5:42 PM


      Bent Cycling                                                                                                  Prepared: 11/7/2009
      Investment Analysis

                                 Initial Investment     Investment     Annual  Months       Rate per   Months               Monthly
                Calculate                 (PV)           Goal (FV)      Rate   per Year   Month (RATE) (NPER)   Years    Payment (PMT)
      Monthly Payment (PMT)                ($400,000)     $1,200,000     5.30%    12         0.44%        18     1.5          ($41,033)
      Investment Goal (PV)                 ($400,000)       $993,780     5.30%    12         0.44%        18     1.5          ($30,000)
      Months (NPER)                        ($400,000)     $1,200,000     5.30%    12         0.44%     23.9317 1.99431        ($30,000)
      Initial Investment (PV)              ($590,493)     $1,200,000     5.30%    12         0.44%        18     1.5          ($30,000)
      Rate per Month (RATE)                ($400,000)     $1,200,000    22.95%    12         1.91%        18     1.5          ($30,000)




SS Investment Analysis.xlsx, Investment Analysis                       Page 2 of 3                                                Star Student
11/3/2008                                                                                                                                         5:42 PM


               Bent Cycling                                                                                              Prepared: 11/7/2009
               Loan Schedule

                                                      Quarters per    Rate per Quarter                                   Quarterly Payment
                    Loan (PV)        Annual Rate         Year              (RATE)      Quarters (NPER)       Years             (PMT)
                      $1,200,000             9.50%         4                    2.38%        20                5                   ($76,071)

               Amortization Schedule
                                                      Remaining          Interest         Principal
                      Year             Quarter         Principal         Payment          Payment        Total Payment
                                 1                1      $1,200,000          ($28,500)       ($47,571)       ($76,071)
                                 1                2     $1,152,429           ($27,370)       ($48,700)       ($76,071)
                                 1                3     $1,103,729           ($26,214)       ($49,857)       ($76,071)
                                 1                4     $1,053,872           ($25,029)       ($51,041)       ($76,071)
                                 2                5     $1,002,831           ($23,817)       ($52,253)       ($76,071)
                                 2                6       $950,578           ($22,576)       ($53,494)       ($76,071)
                                 2                7       $897,083           ($21,306)       ($54,765)       ($76,071)
                                 2                8       $842,318           ($20,005)       ($56,066)       ($76,071)
                                 3                9       $786,253           ($18,674)       ($57,397)       ($76,071)
                                 3               10       $728,856           ($17,310)       ($58,760)       ($76,071)
                                 3               11       $670,096           ($15,915)       ($60,156)       ($76,071)
                                 3               12       $609,940           ($14,486)       ($61,585)       ($76,071)
                                 4               13       $548,355           ($13,023)       ($63,047)       ($76,071)
                                 4               14       $485,308           ($11,526)       ($64,545)       ($76,071)
                                 4               15       $420,764            ($9,993)       ($66,077)       ($76,071)
                                 4               16       $354,686            ($8,424)       ($67,647)       ($76,071)
                                 5               17       $287,039            ($6,817)       ($69,253)       ($76,071)
                                 5               18       $217,786            ($5,172)       ($70,898)       ($76,071)
                                 5               19       $146,888            ($3,489)       ($72,582)       ($76,071)
                                 5               20        $74,306            ($1,765)       ($74,306)       ($76,071)

                                                                           Year
               Yearly Payments            1                2                 3                4               5                Total
               Interest                 ($107,113)        ($87,704)         ($66,385)        ($42,966)       ($17,243)            ($321,412)
               Principal                ($197,169)       ($216,578)        ($237,898)       ($261,316)      ($287,039)          ($1,200,000)
               Total                    ($304,282)       ($304,282)        ($304,282)       ($304,282)      ($304,282)          ($1,521,412)


SS Investment Analysis.xlsx, Loan Schedule                                Page 3 of 3                                                          Star Student
11/3/2008                                                                                                                          5:42 PM


            Bent Cycling                                                                                     Prepared: 11/7/2009
            Projected Five-Year Income Statement

                                                                                             Percent Cost of Goods          72%
                                                                                                 Assumed Tax Rate           33%
                                                        Year 1         Year 2         Year 3           Year 4         Year 5
            Sales Revenue                                3,200,000      3,805,463      4,525,483        5,381,737      6,400,000
            Cost of Goods Sold                           2,304,000      2,739,933      3,258,348        3,874,851      4,608,000
            Gross Profit                            $      896,000 $    1,065,530 $    1,267,135 $      1,506,886 $    1,792,000

            Accounting                                      7,000          8,050          9,258          10,646          12,243
            Advertising & Promotion                        21,000         24,150         27,773          31,938          36,729
            Insurance                                       2,000          2,300          2,645           3,042           3,498
            Maintenance                                     4,800          5,520          6,348           7,300           8,395
            Utilities                                       7,600          8,740         10,051          11,559          13,292
            Miscellaneous                                   2,400          2,760          3,174           3,650           4,198
            Total General Expenses                  $      44,800 $       51,520 $       59,248 $        68,135 $        78,355

            Earnings before Interest, Depr. & Tax         851,200      1,014,010      1,207,887       1,438,751       1,713,645
            Depreciation Expense                           53,333         53,333         53,333          53,333          53,333
            Operating Profit                        $     797,867 $      960,676 $    1,154,554 $     1,385,418 $     1,660,311

            Interest Expense                              107,113         87,704         66,385          42,966          17,243
            Earnings Before Taxes                   $     690,754 $      872,972 $    1,088,169 $     1,342,452 $     1,643,068

            Estimated Tax                                 227,949        288,081        359,096         443,009         542,213
            Net Income                              $     462,805 $      584,891 $      729,073 $       899,443 $     1,100,856




SS Income Projection.xlsx, Income Statement                      Page 1 of 1                                                   Star Student
11/3/2008                                                                                                                        5:42 PM


                     Bent Cycling                                                                       Prepared: 11/7/2009
                     Return on the Building Project

                                                                                   Yearly Return
                     Initial Investment                Year 1          Year 2           Year 3       Year 4      Year 5
                                     ($1,200,000)        $150,000       $250,000         $400,000    $550,000    $750,000
                     Cumulative Net Cash Flow         ($1,050,000)     ($800,000)       ($400,000)   $150,000    $900,000

                     Return on Investment
                     Desired Rate of Return                  12%
                     Net Present Value                  $193,044
                     Internal Rate of Return              16.89%




SS Return Analysis.xlsx, Return on Building Project                  Page 1 of 1                                              Star Student
11/3/2008                                                                                                                            5:42 PM


         Bent Cycling                                                                                          Prepared: 11/7/2009
         Summary of Five-Year Projections for the Building Project

         Income Projections                            Year 1            Year 2         Year 3         Year 4          Year 5
         Gross Profit                                    896,000          1,065,530      1,267,135      1,506,886       1,792,000
         Total General Expenses                           44,800             51,968         60,283         69,928          81,117
         Operating Profit                                745,600            917,254      1,119,020      1,356,855       1,637,829
         Pre-Tax Earnings                                632,765            824,670      1,048,789      1,311,298       1,619,507
         Net Income                                $     423,952 $          552,529 $      702,689 $      878,569 $     1,085,070

         Cash Flows                                    Year 1            Year 2         Year 3         Year 4          Year 5
         Total Cash Inflows                              851,200          1,013,562      1,206,852      1,436,958       1,710,883
         Total Cash Outflows                             516,719            580,047        654,007        740,634         842,344
         Net Cash Flow                             $     334,481 $          433,514 $      552,846 $      696,324 $       868,540

         Balance Sheet                                 Year 1            Year 2         Year 3         Year 4          Year 5
         Total Current and Noncurrent Assets            2,678,881         3,016,089      3,481,102      4,097,323       4,892,809
         Total Liabilities and Equity                   2,678,881         3,016,089      3,481,102      4,097,323       4,892,809




SS Financial Report.xlsx, Summary                                    Page 1 of 1                                                Star Student
11/3/2008                                                                                                                                                                5:42 PM



E-Park Real Estate

Assumptions                                                   Return on Investment
Cost of Condo                     $325,000                    Discount Rate                      7.00%
Lifetime of Investment                   10                   NPV of Investment             $85,543.28
Salvage Value                           $0                    IRR                               10.88%
Sale Value                        $450,000
Tax Rate                               38%

Annual Income Statement           Initial         Year 1         Year 2       Year 3          Year 4       Year 5      Year 6      Year 7      Year 8      Year 9      Year 10
Annual Rental Income                        $0     $42,000        $43,556      $45,111         $46,667      $48,222     $49,778     $51,333     $52,889     $54,444     $56,000
Proceeds from Condo Sale                    $0                                                                                                                         $450,000
Total                                       $0    $42,000        $43,556      $45,111          $46,667     $48,222     $49,778     $51,333     $52,889     $54,444     $506,000

Expenses
  Condo Purchase                  $325,000
  Property Taxes                        $0          $2,000         $2,222         $2,444        $2,667       $2,889     $3,111      $3,333      $3,556      $3,778       $4,000
  Expenses                              $0          $1,500         $2,000         $2,500        $3,000       $3,500     $4,000      $4,500      $5,000      $5,500       $6,000
  Total                           $325,000          $3,500         $4,222         $4,944        $5,667       $6,389     $7,111      $7,833      $8,556      $9,278      $10,000

Initial Earnings                 ($325,000)       $38,500         $39,333     $40,167          $41,000     $41,833     $42,667     $43,500     $44,333     $45,167     $496,000
Depreciation of Condo                   $0        $32,500         $32,500     $32,500          $32,500     $32,500     $32,500     $32,500     $32,500     $32,500      $32,500
Taxable Income                   ($325,000)        $6,000          $6,833      $7,667           $8,500      $9,333     $10,167     $11,000     $11,833     $12,667     $463,500

Tax on Rental Income                        $0      $2,280         $2,597         $2,913        $3,230       $3,547      $3,863      $4,180      $4,497     $4,813       $5,130
Tax on Condo Sale                           $0                                                                                                                         $171,000
Total                                       $0      $2,280         $2,597         $2,913        $3,230       $3,547     $3,863      $4,180      $4,497      $4,813     $176,130

Net Income                       ($325,000)         $3,720         $4,237         $4,753        $5,270       $5,787     $6,303      $6,820      $7,337      $7,853     $287,370



Annual Cash Flow                  Initial         Year 1         Year 2       Year 3          Year 4       Year 5      Year 6      Year 7      Year 8      Year 9      Year 10
Net Income                       ($325,000)         $3,720         $4,237       $4,753          $5,270       $5,787      $6,303      $6,820      $7,337      $7,853    $287,370
Depreciation Add Back                     0        $32,500        $32,500      $32,500         $32,500      $32,500     $32,500     $32,500     $32,500     $32,500     $32,500
Net Cash Flow                    ($325,000)        $36,220        $36,737      $37,253         $37,770      $38,287     $38,803     $39,320     $39,837     $40,353    $319,870

Cumulative Cash Flow             ($325,000)      ($288,780)     ($252,043)   ($214,790)      ($177,020)   ($138,733)   ($99,930)   ($60,610)   ($20,773)   $19,580     $339,450




SS Condo Investment.xlsx, Investment Analysis                                              Page 1 of 1                                                                Star Student
11/3/2008                                                                                                                       5:42 PM


                    Witte Limestone

                                          Cash Flow Projections for the New Berlin Quarry
                       Year          Cash Flow            IRR Guess         Calculated IRR
                               0        ($3,500,000)               1%                     2.37%
                               1           $150,000              10%                     12.34%
                               2           $234,763
                               3           $367,423     Discount Rate   NPV (Immediate Inv.)      NPV (Inv. In One Year)
                               4           $575,049                1%            ($703,987.83)               ($697,017.66)
                               5           $900,000                2%            ($153,111.13)               ($150,108.96)
                               6           $910,000                3%             $212,020.81                 $205,845.44
                               7           $920,000                4%             $436,422.85                 $419,637.36
                               8           $930,000                5%             $554,861.42                 $528,439.45
                               9           $940,000                6%             $594,171.58                 $560,539.23
                              10           $950,000                7%             $575,044.27                 $537,424.55
                              11           $910,000                8%             $513,407.12                 $475,376.97
                              12           $870,000                9%             $421,492.97                 $386,690.80
                              13           $830,000               10%             $308,667.70                 $280,607.00
                              14           $790,000               11%             $182,072.63                 $164,029.40
                              15           $750,000               12%              $47,123.39                  $42,074.45
                              16           $436,358               13%             ($92,102.21)                ($81,506.38)
                              17           $253,878               14%            ($232,562.33)               ($204,002.05)
                              18           $147,709               15%            ($372,016.19)               ($323,492.34)
                              19            $85,939               16%            ($508,840.98)               ($438,656.02)
                              20            $50,000               17%            ($641,889.32)               ($548,623.35)
                              21       ($10,000,000)              18%            ($770,378.17)               ($652,862.86)
                    Total               ($1,498,882)              19%            ($893,802.35)               ($751,094.41)
                                                                  20%          ($1,011,867.22)               ($843,222.68)




SS Witte Limestone.xlsx, Investment Analysis                  Page 1 of 2                                                    Star Student
11/3/2008                                                                                               5:42 PM




                                                       Net Present Values
                                  800000



                                  600000



                                  400000



                                  200000



                                       0
             Net Present Value




                                            0   0.05       0.1                      0.15   0.2   0.25

                                  -200000



                                  -400000



                                  -600000



                                  -800000



                                 -1000000



                                 -1200000
                                                                   Rate of Return




SS Witte Limestone.xlsx, NPV Chart                           Page 2 of 2                           Star Student
   Home Loan Estimate                                                                                                Eason Financial Services
                                                                                                                          100 Forward Drive
                                                                                                                         Meridian, ID 83642

   Name      Tony Bocelli
   Date      Monday, November 03, 2008

       Current Revenue and Debts

         1   Current Monthly Revenue                                             2   Current Monthly Debts
             Salary                                     5,400.00                     Car Loans                         ($250.00)
             Other Salary or Wages                      1,200.00                     Student Loans                     ($150.00)
             Investment Income                            150.00                     Credit Card Payments                 $0.00
             Additional Income                              0.00                     Additional Loan Payments             $0.00
             Total                                     $6,750.00                     Total                              -$400.00

       Qualified Monthly Payments                                            Estimates of Monthly Housing Expenses

         3                                                                       4
             Revenue Qualification Test                                              Qualified Monthly Payment        $1,890.00
             Income Ratio                                   28%                      Property Tax Escrow                (100.00)
             Qualified Monthly Payment                $1,890.00                      Homeowners Insurance                 (75.00)

                                                                                     Misc. Fees and Expenses            (125.00)
             Debt Qualification Test                                                 Amount Remaining for             $1,590.00
             Debt Ratio                                     36%                      Mortgage
             Qualified Monthly Payment                $2,030.00

             Final Qualification*                   $1,890.00
             *Minimum of the Income and Debt Qualifications

       Mortgage Information
         5                                                                   Maximum Qualified Loan
             Annual Interest Rate                         6.50%
             Compounded per Year                             12
                                                                                                    $213,259
             Duration of Loan (Years)                        20
             Total Payments                                 240              Prepared By
             Monthly Mortgage Payment               ($1,590.00)




SS Eason Financial Services.xlsx, Loan Calculator                  Page 1 of 1                                                  Star Student
11/3/2008                                                                                                                                                    5:42 PM



                   Blue Marlin Car Wash

                   Assumptions
                   Cost of Building               $250,000
                   Lifetime of Investment                15
                   Salvage Value                   $35,000
                   Tax Rate                            33%

                   Return on Investment
                   Discount Rate                      7.00%
                   NPV of Investment             $17,392.76
                   IRR                                8.06%

                   Annual Income Statement        Initial        Year 1         Year 2         Year 3     Year 4     Year 5      Year 6      Year 7
                   Annual Income from Car Wash              $0    $62,000        $64,000        $66,000   $68,000     $70,000    $72,000     $74,000
                   Total                                    $0    $62,000        $64,000        $66,000   $68,000     $70,000    $72,000     $74,000

                   Expenses
                     Purchase of Car Wash        ($250,000)            $0              $0            $0        $0          $0          $0          $0
                     Operating Expenses                 $0        ($8,000)        ($9,214)     ($10,429) ($11,643)   ($12,857)   ($14,071)   ($15,286)
                     Total                       ($250,000)       ($8,000)        ($9,214)     ($10,429) ($11,643)   ($12,857)   ($14,071)   ($15,286)

                   Initial Earnings              ($250,000)       $54,000         $54,786       $55,571 $56,357       $57,143     $57,929     $58,714
                   Depreciation of Equipment            $0       ($14,333)       ($14,333)     ($14,333) ($14,333)   ($14,333)   ($14,333)   ($14,333)
                   Taxable Income                ($250,000)       $39,667         $40,452       $41,238 $42,024       $42,810     $43,595     $44,381

                   Taxes Due                                $0   ($13,090)      ($13,349)      ($13,609) ($13,868)   ($14,127)   ($14,386)   ($14,646)

                   Net Income                    ($250,000)      $26,577         $27,103       $27,630    $28,156    $28,682     $29,209     $29,735



                   Annual Cash Flow               Initial        Year 1         Year 2         Year 3     Year 4     Year 5      Year 6      Year 7
                   Net Income                    ($250,000)       $26,577        $27,103        $27,630   $28,156     $28,682    $29,209     $29,735
                   Depreciation Add Back                  0       $14,333        $14,333        $14,333   $14,333     $14,333    $14,333     $14,333
                   Net Cash Inflow               ($250,000)       $40,910        $41,436        $41,963   $42,489     $43,016    $43,542     $44,069

                   Tax Payments                             $0   ($13,090)      ($13,349)      ($13,609) ($13,868)   ($14,127)   ($14,386)   ($14,646)
                   Net Cash Outflow                         $0   ($13,090)      ($13,349)      ($13,609) ($13,868)   ($14,127)   ($14,386)   ($14,646)

                   Net Cash Flow                 ($250,000)      $27,820         $28,087       $28,354    $28,621    $28,889     $29,156     $29,423



SS Blue Marlin.xlsx, Investment Analysis                                     Page 1 [2] of 2                                                             Star Student
11/3/2008                                                                                                                                                        5:42 PM



              Blue Marlin Car Wash

              Assumptions
              Cost of Building               $250,000
              Lifetime of Investment                15
              Salvage Value                   $35,000
              Tax Rate                            33%

              Return on Investment
              Discount Rate                      7.00%
              NPV of Investment             $17,392.76
              IRR                                8.06%

              Annual Income Statement        Initial        Year 8      Year 9      Year 10      Year 11     Year 12     Year 13     Year 14     Year 15
              Annual Income from Car Wash              $0   $76,000     $78,000      $80,000      $82,000     $84,000     $86,000     $88,000     $90,000
              Total                                    $0   $76,000     $78,000      $80,000      $82,000     $84,000     $86,000     $88,000     $90,000

              Expenses
                Purchase of Car Wash        ($250,000)            $0          $0           $0          $0          $0          $0          $0          $0
                Operating Expenses                 $0       ($16,500)   ($17,714)    ($18,929)   ($20,143)   ($21,357)   ($22,571)   ($23,786)   ($25,000)
                Total                       ($250,000)      ($16,500)   ($17,714)    ($18,929)   ($20,143)   ($21,357)   ($22,571)   ($23,786)   ($25,000)

              Initial Earnings              ($250,000)       $59,500     $60,286      $61,071     $61,857     $62,643     $63,429     $64,214     $65,000
              Depreciation of Equipment            $0       ($14,333)   ($14,333)    ($14,333)   ($14,333)   ($14,333)   ($14,333)   ($14,333)   ($14,333)
              Taxable Income                ($250,000)       $45,167     $45,952      $46,738     $47,524     $48,310     $49,095     $49,881     $50,667

              Taxes Due                                $0   ($14,905)   ($15,164)    ($15,424)   ($15,683)   ($15,942)   ($16,201)   ($16,461)   ($16,720)

              Net Income                    ($250,000)      $30,262     $30,788      $31,315     $31,841     $32,367     $32,894     $33,420     $33,947



              Annual Cash Flow               Initial        Year 8      Year 9      Year 10      Year 11     Year 12     Year 13     Year 14     Year 15
              Net Income                    ($250,000)      $30,262     $30,788      $31,315      $31,841     $32,367     $32,894     $33,420     $33,947
              Depreciation Add Back                  0      $14,333     $14,333      $14,333      $14,333     $14,333     $14,333     $14,333     $14,333
              Net Cash Inflow               ($250,000)      $44,595     $45,121      $45,648      $46,174     $46,701     $47,227     $47,754     $48,280

              Tax Payments                             $0   ($14,905)   ($15,164)    ($15,424)   ($15,683)   ($15,942)   ($16,201)   ($16,461)   ($16,720)
              Net Cash Outflow                         $0   ($14,905)   ($15,164)    ($15,424)   ($15,683)   ($15,942)   ($16,201)   ($16,461)   ($16,720)

              Net Cash Flow                 ($250,000)      $29,690     $29,957      $30,224     $30,491     $30,759     $31,026     $31,293     $31,560



SS Blue Marlin.xlsx, Investment Analysis                                   Page 1 [2] of 2                                                                   Star Student

				
DOCUMENT INFO
Description: Balance Sheet of New Project for 5 Year document sample