Spreadsheet: 4e1a6524-db9b-4551-8209-8af097b5fbbd
Sheet: Sheet1

COLORADO DIVISION OF HOUSING  *  HOUSING DEVELOPMENT ANALYSIS SPREADSHEET | Unnamed: 1 | Unnamed: 2 | Unnamed: 3 | Unnamed: 4 | Unnamed: 5 | Unnamed: 6 | Unnamed: 7 | Unnamed: 8 | Unnamed: 9 | Unnamed: 10 | Unnamed: 11 | COLORADO DIVISION OF HOUSING  *  HOUSING DEVELOPMENT ANALYSIS SPREADSHEET.1 | Unnamed: 13
Project Name: |  |  |  |  |  |  |  |  |  | Spreadsheet directions are to the right ---> |  | <-- [NAME] is to the left
Date: |  |  | 2011-06-07 00:00:00 |  | PAGE #1
Applicant: |  |  |  |  | Operating Proforma |  |  |  |  |  |  | GENERAL INSTRUCTIONS:
Spreadsheet Version: |  |  |  |  | Required for Project Applications |  |  |  |  |  |  | The spreadsheet is divided up into many tabs - most have linked information.  On each tab, the
 |  |  |  |  |  |  |  |  |  |  |  | spreadsheet is to the left, and the instructions are to the right.
STABILIZED FIRST YEAR INCOME |  |  |  |  |  | EXPENSES
#Bed/Bath | % AMI | #of units | Sq. Ft. | Monthly Rent | Total Annual Rent | Administrative Expenses |  |  |  |  |  | This spreadsheet is primarily designed to analyze the financing of a development project, and to determine the project's financial
 |  |  |  |  | 0 | Management Fee |  |  |  |  |  | feasibility.  APPLICANTS FOR DEVELOPMENT PROJECTS NEED ONLY FILL OUT THE FIRST FOUR PAGES.
 |  |  |  |  | 0 | On-site Personnel Payroll |  |  | FTE |  |  |  | Page 1 - Operating Proforma (OpPro) (Modifications for homeownership projects are explained below)
 |  |  |  |  | 0 | Health Ins. & Benefits |  |  |  |  |  |  | Page 2 - Development Costs (DevCosts)
 |  |  |  |  | 0 | Legal & Accounting |  |  |  |  |  |  | Page 3 - Permanent Financing Sources (Financing)
 |  |  |  |  | 0 | Advertising |  |  |  |  |  |  | Page 4 - Sources & Uses
 |  |  |  |  | 0 | Office Supplies
 |  |  |  |  | 0 | Telephone |  |  |  |  |  | The other pages DO NOT HAVE TO BE FILLED OUT FOR YOUR APPLICATION, although some of them may be useful to you.
 |  |  |  |  | 0 | Audit |  |  |  |  |  | The additional pages are as follows:
 |  |  |  |  | 0 | Other |  |  |  |  |  |  | Ten Year Operation Proforma (10yr Profoma)
 |  |  |  |  | 0 | Total Administrative Expenses | 0 |  |  |  |  |  | Project Assessment for Acqu/Rehab (Assessment, Rehab)
 |  |  |  |  | 0 | Operating Expenses |  |  |  |  |  |  | Project Assessment for New Construction (Assessment, NC)
 |  |  |  |  | 0 | Utilities (owner paid) |  |  |  |  |  |  | Tax Credit Calculation (Tax Credits)
 |  |  |  |  | 0 | Trash Removal |  |  |  |  |  |  | Improvement Costs for LIHTC Projects (LIHTC Basis)
 |  |  |  |  | 0 | Fire & Liability Insurance |  |  |  |  |  |  | Cost Effectiveness Rating (cost effect)
 |  |  |  |  | 0 | Other |  |  |  |  |  |  | Analysis of Rents (rents)
 |  |  |  |  | 0 | Total Operating Expenses | 0 |  |  |  |  |  | Fair Market Rents (FMRs)
 | Total units | 0 |  | Total Rent Income | 0 | Maintenance |  |  |  |  |  |  | Analysis of Sales Prices (sale prices)
 | Total sq ft | 0 |  |  |  | Maintenance |  |  |  |  |  |  | HOME Assisted Units (HOME units)
 |  |  |  | Parking Income |  | Repairs |  |  |  |  |  |  | Section 221(D)(3) Limits for Colorado (221(D)(3)
 |  |  |  | Laundry Income |  | Grounds (inc. snow removal) |  |  |  |  |  |  | Amortization Table (Amortize)
 |  |  |  | Other Income |  | Other
 |  |  |  | Total Income | 0 | Total Maintenance | 0 |  |  |  |  | You can enter information into the cells which are shaded yellow.  All other cells are protected and cannot be changed.
 | Vac. Rate | 0.07 |  | Less Vacancy | 0 | Real Estate Taxes
 |  |  |  | Effective Gross Income | 0 | Operating Reserve |  | unit avg.=
 |  |  |  |  |  | Replacement Reserve |  | unit avg.= |  |  |  | COLORADO DIVISION OF HOUSING  *  HOUSING DEVELOPMENT ANALYSIS SPREADSHEET
 |  |  | DEBT SERVICE |  |  | TOTAL ANNUAL EXPENSES | 0 |  |  |  |  | <-- [NAME] is to the left & up
 |  |  |  | 1st Mortgage | 0 | NET OPERATING INCOME | 0
 |  |  |  | 2nd Mortgage | 0 | P.U.P.A. Expenses * |  |  |  |  |  | OPERATING PROFORMA - PAGE 1 - INSTRUCTIONS:
 |  |  |  | 3rd Mortgage | 0 | * P.U.P.A = Per Unit Per Annum Expenses |  |  |  |  |  | The Operating Proforma page is designed to summarize a rental project's bedroom distribution, income targets, operating income and
 |  |  | TOTAL DEBT SERVICE |  | 0 |  |  |  |  |  |  | expenses, and to show its debt coverage ratio.  It can be modified for homeownership projects.
 | BEP |  |  | Poss D/S @ 1.1 DCR | 0 | *Note:
BEP = Break Even Point |  |  |  | Project Debt Coverage Ratio |  |  |  |  |  |  |  | First enter the Project Name in Cell D2, Developer Name in Cell D4, and your version number (you may want to run a few different
Poss D/S @ 1.1 DCR = Possible Debt Service at a 1.1 Debt Coverage Ratio |  |  |  |  |  |  |  |  |  |  |  | scenarios) in Cell D5.  These entries will be shown at the top of most of the pages in this proforma.
 |  |  |  |  |  |  |  |  |  |  |  | Enter the project's bedroom/bathroom configurations, target AMI's, number of units, square footage, and monthly rents in Cells A9 - E24.
 |  |  |  |  |  |  |  |  |  |  |  | Add any other income to Cells F27 - F29.  The vacancy factor should be added in C31.  This needs to be expressed as
 |  |  |  |  |  |  |  |  |  |  |  | a decimal.  For example, 7% would be .07.
 |  |  |  |  |  |  |  |  |  |  |  | The mortgage amounts in Cells F35 through F37 are read into this schedule from the Financing page (page 3).
 |  |  |  |  |  |  |  |  |  |  |  | The Break Even Point and Debt Coverage Ratio are automatically calculated once you enter all of the Operating Proforma information.
 |  |  |  |  |  |  |  |  |  |  |  | Next, enter your project's expense data in Cells H9 through H33.  You need only enter data for the expenses that pertain to your specific
 |  |  |  |  |  |  |  |  |  |  |  | project, however, if you use the "Other" lines, please provide an explaination.  The program will automatically calculate the [NAME] (Per Unit
 |  |  |  |  |  |  |  |  |  |  |  | Per Annum expenses) for you.
 |  |  |  |  |  |  |  |  |  |  |  | Cells I9 & I18 calculate the management fee and total management expenses as a percent of total income.  Cells J32 and J33 calculate the
 |  |  |  |  |  |  |  |  |  |  |  | [NAME] for operating and replacement reserves.
 |  |  |  |  |  |  |  |  |  |  |  | For Homeownership projects:
 |  |  |  |  |  |  |  |  |  |  |  | Enter the project's bedroom/bathroom configurations, target AMI's, number of units, and square footage in Cells A9 - D24.  [NAME] the
 |  |  |  |  |  |  |  |  |  |  |  | monthly rent column, change "Total Annual Rent" to "Sales Price" and fill in the sales prices.  [NAME] the rest of this spreadsheet.