Financial Model

profiledday336
cashflow.xlsx
Home>Business & Finance homework help>Financial Model

Cash Flow

Baldair Shopping Center Software: ARGUS Ver. 15.0.1.26
2401 Baldair Road File: Baldair Shopping Center
Baltimore, MD 21213 Property Type: Retail
Portfolio:
Date: 6/30/14
Time: 7:29 pm
Ref#: AAB
Page: 1
Schedule Of Prospective Cash Flow
In Inflated Dollars for the Fiscal Year Beginning 6/1/2014
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
For the Years Ending May-2015 May-2016 May-2017 May-2018 May-2019 May-2020 May-2021 May-2022 May-2023 May-2024
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Potential Gross Revenue
Base Rental Revenue $1,414,696 $1,541,986 $1,642,217 $1,674,436 $1,716,759 $1,747,691 $1,836,410 $1,892,746 $2,010,476 $2,234,427
Absorption & Turnover Vacancy (60,000) (27,205) (105,803) (45,270) (179,155) (39,758)
Base Rent Abatements (20,000) (6,801) (35,268) (14,758) (59,718) (38,473)
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Scheduled Base Rental Revenue 1,334,696 1,507,980 1,501,146 1,674,436 1,716,759 1,747,691 1,776,382 1,892,746 1,771,603 2,156,196
Expense Reimbursement Revenue
Landscaping 7,275 8,518 10,751 11,521 11,521 11,521 11,243 11,362 10,241 11,284
Lot Cleaning 28,112 32,915 41,549 44,523 44,523 44,523 43,447 43,907 39,575 43,604
Snow Removal 13,567 15,884 20,051 21,486 21,486 21,486 20,967 21,189 19,099 21,043
Trash Removal 2,946 3,449 4,354 4,666 4,666 4,666 4,553 4,601 4,147 4,570
Storm Water Management 24,323 28,478 35,950 38,523 38,523 38,523 37,592 37,990 34,242 37,729
Facade Repairs 29,176 34,160 43,121 46,208 46,208 46,208 45,091 45,569 41,073 45,254
Lot Repairs & Striping 6,390 7,482 9,445 10,121 10,121 10,121 9,877 9,981 8,997 9,912
Snow Removal
Electric & Gas 17,109 20,032 25,287 27,097 27,097 27,097 26,442 26,722 24,087 26,538
Water & Sewer 6,930 8,112 10,241 10,974 10,974 10,974 10,708 10,822 9,754 10,747
Roof Repairs
Real Estate Taxes 176,062 199,540 217,817 233,342 233,342 233,342 230,670 233,342 209,557 230,075
Insurance 19,108 23,773 30,500 32,732 32,732 32,732 32,554 32,957 29,706 32,731
Management Fee 116,564 131,273 135,372 156,800 159,301 161,143 163,528 172,291 155,362 180,204
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Reimbursement Revenue 447,562 513,616 584,438 637,993 640,494 642,336 636,672 650,733 585,840 653,691
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Potential Gross Revenue 1,782,258 2,021,596 2,085,584 2,312,429 2,357,253 2,390,027 2,413,054 2,543,479 2,357,443 2,809,887
General Vacancy (23,124) (23,573) (23,900) (25,435)
Collection Loss (17,823) (20,216) (20,856) (23,124) (23,573) (23,900) (24,131) (25,435) (23,574) (28,099)
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Effective Gross Revenue 1,764,435 2,001,380 2,064,728 2,266,181 2,310,107 2,342,227 2,388,923 2,492,609 2,333,869 2,781,788
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Operating Expenses
Landscaping 9,972 9,972 9,972 9,972 9,972 9,972 9,972 9,972 9,972 9,972
Lot Cleaning 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542
Snow Removal 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600
Trash Removal 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039
Storm Water Management 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348
Facade Repairs 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000
Lot Repairs & Striping 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762
Snow Removal 500 500 500 500 500 500 500 500 500 500
Electric & Gas 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458
Water & Sewer 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500
Roof Repairs 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000
Real Estate Taxes 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378
Insurance 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931
Management Fee 105,866 120,083 123,884 135,971 138,606 140,534 143,335 149,557 140,032 166,907
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Operating Expenses 558,896 573,113 576,914 589,001 591,636 593,564 596,365 602,587 593,062 619,937
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Net Operating Income 1,205,539 1,428,267 1,487,814 1,677,180 1,718,471 1,748,663 1,792,558 1,890,022 1,740,807 2,161,851
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Leasing & Capital Costs
Tenant Improvements 225,000 19,380 64,676 488,191 77,681
Leasing Commissions 85,354 69,915 104,447 247,213 84,994
Capital Reserves 20,469 20,469 20,469 20,469 20,469 20,469 20,469 20,469 20,469 20,469
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Leasing & Capital Costs 330,823 109,764 20,469 20,469 20,469 20,469 189,592 20,469 755,873 183,144
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Cash Flow Before Debt Service $874,716 $1,318,503 $1,467,345 $1,656,711 $1,698,002 $1,728,194 $1,602,966 $1,869,553 $984,934 $1,978,707
& Taxes =========== =========== =========== =========== =========== =========== =========== =========== =========== ===========

&G

Expense Reimbursement

Baldair Shopping Center Software: ARGUS Ver. 15.0.1.26
2401 Baldair Road File: Baldair Shopping Center
Baltimore, MD 21213 Property Type: Retail
Portfolio:
Date: 6/30/14
Time: 7:29 pm
Ref#: AAB
Page: 2
Schedule Of Expense Reimbursement Revenue
Fiscal Year Reimbursable Operating Expenses Adjusted for Actual Occupancy
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
For the Years Ending May-2015 May-2016 May-2017 May-2018 May-2019 May-2020 May-2021 May-2022 May-2023 May-2024
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Reimbursable Expenses
Landscaping $9,972 $9,972 $9,972 $9,972 $9,972 $9,972 $9,972 $9,972 $9,972 $9,972
Lot Cleaning 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542
Snow Removal 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600
Trash Removal 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039
Storm Water Management 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348
Facade Repairs 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000
Lot Repairs & Striping 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762
Snow Removal 500 500 500 500 500 500 500 500 500 500
Electric & Gas 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458
Water & Sewer 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500
Roof Repairs 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000
Real Estate Taxes 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378
Insurance 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931
Management Fee 105,838 120,052 123,851 135,931 138,566 140,493 143,294 149,513 139,995 166,866
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Reimbursable Expenses $558,868 $573,082 $576,881 $588,961 $591,596 $593,523 $596,324 $602,543 $593,025 $619,896
=========== =========== =========== =========== =========== =========== =========== =========== =========== ===========
Calendar Year Reimbursable Operating Expenses used for Reimbursement Calculations
For the Years Ending Dec-2014 Dec-2015 Dec-2016 Dec-2017 Dec-2018 Dec-2019 Dec-2020 Dec-2021 Dec-2022 Dec-2023
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Reimbursable Expenses
Landscaping $9,972 $9,972 $9,972 $9,972 $9,972 $9,972 $9,972 $9,972 $9,972 $9,972
Lot Cleaning 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542 38,542
Snow Removal 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600 18,600
Trash Removal 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039 4,039
Storm Water Management 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348 33,348
Facade Repairs 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000
Lot Repairs & Striping 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762 8,762
Snow Removal 500 500 500 500 500 500 500 500 500 500
Electric & Gas 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458 23,458
Water & Sewer 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500 9,500
Roof Repairs 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000
Real Estate Taxes 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378 231,378
Insurance 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931 28,931
Management Fee 100,674 112,187 118,743 135,396 137,681 139,700 140,718 148,199 152,087 147,766
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Reimbursable Expenses $553,704 $565,217 $571,773 $588,426 $590,711 $592,730 $593,748 $601,229 $605,117 $600,796
=========== =========== =========== =========== =========== =========== =========== =========== =========== ===========
Resulting Fiscal Year Property Expense Reimbursement Revenue
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
For the Years Ending May-2015 May-2016 May-2017 May-2018 May-2019 May-2020 May-2021 May-2022 May-2023 May-2024
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Expense Reimbursements
Landscaping $7,275 $8,518 $10,751 $11,521 $11,521 $11,521 $11,243 $11,362 $10,241 $11,284
Lot Cleaning 28,112 32,915 41,549 44,523 44,523 44,523 43,447 43,907 39,575 43,604
Snow Removal 13,567 15,884 20,051 21,486 21,486 21,486 20,967 21,189 19,099 21,043
Trash Removal 2,946 3,449 4,354 4,666 4,666 4,666 4,553 4,601 4,147 4,570
Storm Water Management 24,323 28,478 35,950 38,523 38,523 38,523 37,592 37,990 34,242 37,729
Facade Repairs 29,176 34,160 43,121 46,208 46,208 46,208 45,091 45,569 41,073 45,254
Lot Repairs & Striping 6,390 7,482 9,445 10,121 10,121 10,121 9,877 9,981 8,997 9,912
Snow Removal
Electric & Gas 17,109 20,032 25,287 27,097 27,097 27,097 26,442 26,722 24,087 26,538
Water & Sewer 6,930 8,112 10,241 10,974 10,974 10,974 10,708 10,822 9,754 10,747
Roof Repairs
Real Estate Taxes 176,062 199,540 217,817 233,342 233,342 233,342 230,670 233,342 209,557 230,075
Insurance 19,108 23,773 30,500 32,732 32,732 32,732 32,554 32,957 29,706 32,731
Management Fee 116,564 131,273 135,372 156,800 159,301 161,143 163,528 172,291 155,362 180,204
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Expense Reimbursement $447,562 $513,616 $584,438 $637,993 $640,494 $642,336 $636,672 $650,733 $585,840 $653,691
=========== =========== =========== =========== =========== =========== =========== =========== =========== ===========
Percentage of Reimbursable Expenses Collected as Expense Reimbursement
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
For the Years Ending May-2015 May-2016 May-2017 May-2018 May-2019 May-2020 May-2021 May-2022 May-2023 May-2024
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Expense Reimbursements
Landscaping 72.95% 85.42% 107.81% 115.53% 115.53% 115.53% 112.75% 113.94% 102.70% 113.16%
Lot Cleaning 72.94% 85.40% 107.80% 115.52% 115.52% 115.52% 112.73% 113.92% 102.68% 113.13%
Snow Removal 72.94% 85.40% 107.80% 115.52% 115.52% 115.52% 112.73% 113.92% 102.68% 113.13%
Trash Removal 72.94% 85.39% 107.80% 115.52% 115.52% 115.52% 112.73% 113.91% 102.67% 113.15%
Storm Water Management 72.94% 85.40% 107.80% 115.52% 115.52% 115.52% 112.73% 113.92% 102.68% 113.14%
Facade Repairs 72.94% 85.40% 107.80% 115.52% 115.52% 115.52% 112.73% 113.92% 102.68% 113.14%
Lot Repairs & Striping 72.93% 85.39% 107.80% 115.51% 115.51% 115.51% 112.73% 113.91% 102.68% 113.12%
Snow Removal
Electric & Gas 72.93% 85.40% 107.80% 115.51% 115.51% 115.51% 112.72% 113.91% 102.68% 113.13%
Water & Sewer 72.95% 85.39% 107.80% 115.52% 115.52% 115.52% 112.72% 113.92% 102.67% 113.13%
Roof Repairs
Real Estate Taxes 76.09% 86.24% 94.14% 100.85% 100.85% 100.85% 99.69% 100.85% 90.57% 99.44%
Insurance 66.05% 82.17% 105.42% 113.14% 113.14% 113.14% 112.52% 113.92% 102.68% 113.13%
Management Fee 110.13% 109.35% 109.30% 115.35% 114.96% 114.70% 114.12% 115.23% 110.98% 107.99%
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Expense Reimbursement 80.08% 89.62% 101.31% 108.33% 108.27% 108.22% 106.77% 108.00% 98.79% 105.45%
=========== =========== =========== =========== =========== =========== =========== =========== =========== ===========

&G

Sources and Uses

Baldair Shopping Center Software: ARGUS Ver. 15.0.1.26
2401 Baldair Road File: Baldair Shopping Center
Baltimore, MD 21213 Property Type: Retail
Portfolio:
Date: 6/30/14
Time: 7:29 pm
Ref#: AAB
Page: 3
Schedule Of Sources & Uses Of Capital
Equity is Based on Property Value, Leverage and Operating Requirements
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
For the Years Ending May-2015 May-2016 May-2017 May-2018 May-2019 May-2020 May-2021 May-2022 May-2023 May-2024
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Sources Of Capital
Net Operating Gains $1,205,539 $1,428,267 $1,487,814 $1,677,180 $1,718,471 $1,748,663 $1,792,558 $1,890,022 $1,740,807 $2,161,851
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Sources Of Capital $1,205,539 $1,428,267 $1,487,814 $1,677,180 $1,718,471 $1,748,663 $1,792,558 $1,890,022 $1,740,807 $2,161,851
=========== =========== =========== =========== =========== =========== =========== =========== =========== ===========
Uses Of Capital
Tenant Improvements 225,000 19,380 64,676 488,191 77,681
Leasing Commissions 85,354 69,915 104,447 247,213 84,994
Capital Costs & Reserves 20,469 20,469 20,469 20,469 20,469 20,469 20,469 20,469 20,469 20,469
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Defined Uses Of Capital 330,823 109,764 20,469 20,469 20,469 20,469 189,592 20,469 755,873 183,144
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Cash Flow Distributions 874,716 1,318,503 1,467,345 1,656,711 1,698,002 1,728,194 1,602,966 1,869,553 984,934 1,978,707
___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________ ___________
Total Uses Of Capital $1,205,539 $1,428,267 $1,487,814 $1,677,180 $1,718,471 $1,748,663 $1,792,558 $1,890,022 $1,740,807 $2,161,851
=========== =========== =========== =========== =========== =========== =========== =========== =========== ===========

&G

Rent Roll Summary

Baldair Shopping Center Software: ARGUS Ver. 15.0.1.26
2401 Baldair Road File: Baldair Shopping Center
Baltimore, MD 21213 Property Type: Retail
Portfolio:
Date: 6/30/14
Time: 7:31 pm
Ref#: AAB
Page: 1
Presentation Rent Roll & Current Term Tenant Summary
As of Jun-2014 for 204,690 Square Feet
Tenant Name Floor Rate & Amount CPI & Current Months Pcnt Description of Imprvmnts Commssns Assumption about
Type & Suite Number SqFt per Year Changes Changes Porters' Wage to to Operating Expense Rate Rate subsequent terms
Lease Dates & Term Bldg Share per Month on to Miscellaneous Abate Abate Reimbursements Amount Amount for this tenant
_____________________ ___________ _____________ _________ ______ ________________ _______ _____ __________________ _________ _________ ________________
1 Food Depot $0.00 Feb-2013 $7.39 - - - See method: NNN + - - Market
Retail, Suite: 100 49,833 $0 Feb-2018 $7.76 10.00% See assumption:
Feb-2003 to Jan-2023 24.35% $0.00 or - Food Depot
240 Months $0
2 Upscale Beauty $0.00 Jan-2014 $9.82 - - - See method: NNN + - - Market
Retail, Suite: 102 13,000 $0 Jan-2015 $10.11 15% - Upscale See assumption:
Jul-2011 to Dec-2023 6.35% $0.00 Jan-2016 $10.42 Beauty -line - Upscale
150 Months $0 Jan-2017 $10.73
Jan-2018 $11.05
Jan-2019 $11.38
Jan-2020 $11.73
Jan-2021 $12.08
Jan-2022 $12.44
Jan-2023 $12.81
3 Dental Dreams $19.00 Dec-2015 $21.28 - - - See method: NNN + - - Market
Retail, Suite: 104 3,600 $68,400 15.00% See assumption:
Dec-2010 to Nov-2020 1.76% $1.58 - Dental Dreams
120 Months $5,700
4 Social Security Admin $16.87 - - - - - See method: - - Market
Retail, Suite: 106 6,800 $114,716 Modified Gross - See assumption:
Sep-2010 to Aug-2015 3.32% $1.41 SSA Medium In-Line
60 Months $9,560
5 B. Green Cash & Carry $0.00 Jun-2014 $3.00 - - - See method: NNN + - - Market
Retail, Suite: 108 34,335 $0 10.00% See assumption:
Feb-2003 to Jan-2023 16.77% $0.00 Rear Space
240 Months $0
6 Wireless World $15.48 - - - - - See method: NNN + - - Market
Retail, Suite: 110 1,600 $24,768 15.00% See assumption:
Aug-2010 to Jul-2015 0.78% $1.29 Line - Wireless
60 Months $2,064
7 Dollar General $10.25 Jul-2015 $11.28 - - - See method: NNN - - - Market
Retail, Suite: 116 10,586 $108,507 Dollar General See assumption:
Jul-2010 to Jun-2020 5.17% $0.85 Dollar General
120 Months $9,042
8 Maxway $3.33 - - - - - See method: - - Market
Retail, Suite: 200 30,000 $99,900 Maxway See assumption:
Jan-2009 to Dec-2014 14.66% $0.28 Anchor - Maxway
72 Months $8,325
9 Forman Mills $6.03 Jun-2014 $6.18 - - - See method: - - Market
Retail, Suite: 202 54,936 $331,264 Jun-2015 $6.33 Forman Mills See assumption:
Jun-2013 to May-2016 26.84% $0.50 Anchor - Forman
36 Months $27,605
Total Occupied SqFt 204,690
Total Available SqFt 0

&G