CSM 731 MIDTERM TEST PART B Instructions: There are TWO parts to this test. Both involve the same project. Project: Fields marked $ will be values you were provided in the ONLINE TEST. 8 You are going to DEVELOP the small CONDO building in the attached Drawings. 9 You will buy the LAND for a BUILDABLE SQUARE FOOT price of $170 10 Your Foundation costs($450,000) are set but you will look up the HARD COSTRUCTION COSTS in the provided COST REFERENCE (Wood Framed Condo, GTA - Top of range but NO UPGRADE) 1 1 Development Charges are in the Supplied Reference Document. 12 13 Bank Borrowing Rate will be 8.5 Percent BUT You are only borring money for the purchase of the LAND. 14 15 You will sell the Units for a $/saft Price of $700 16 17 90% of mark is for correct answers and methods. 18 10% of mark is for GOOD SPREADSHEET technique and clarity etc.. 19 ANY EVIDENCE OF CHEATING = ZERO ON THIS PART OF TEST! NO WARNINGS.Make a QUICK calculation to see if the project makes sense. Note any assumptions you have made. NOTES SIDE CALCS Revenue Development Charges # Units $/unit Total Saleable SQFT Insert Value 3 Bedroom Units 3 77679 233037 Insert Values/Calculations Selling Price $/sqft Insert Value 2 Bedroom Units 5 77679 388395 Insert Values/Calculations Gross Sale Price Input Formula 1 Bedroom Units 2 38968 77936 Insert Values/Calculations Less Commission 5% Commission rate and Formula TOTAL ######## Net Revenue Input Formula Expenses Lot Size SQFT Insert Value Buildable SQFT Insert Value Land Price $/Buildable Sqft Insert Value LAND COST $ Input Formula Desing Planning and Approval $ 1,50,000 GIVEN VALUE Development Charges $ 6,99,368 See SIDE CALCULATION Foundation Construction Costs $ 4,50,000 Construction Cost $/SQFT Insert Value Construction Costs TOTAL Input Formula SOFT COSTS Estimate and Input Total Expenses Input Formula Loan Calculations Principal Input Formula for 75% of Land purchase Rate, /a Duration, yrs 2 INTEREST EXPENSES Input Formula Down Payment on Land $ TOTAL INVESTMENTIncluding interest $ 12,99,368 PROFIT Includes cost of interestA B C D E G H J K L M N O P Q R S T U V W X 16 17 Desing Planning and Approval $ 1,50,000 GIVEN VALUE 18 Development Charges $ 6,99,368 See SIDE CALCULATION 19 Foundation Construction Costs $ 4,50,000 20 Construction Cost $/SQFT Insert Value 21 Construction Costs TOTAL S Input Formula 22 SOFT COSTS Estimate and Input 23 Total Expenses Input Formula 24 25 Loan Calculations 26 Principal Input Formula for 75% of Land purchase 27 Rate, /a 28 Duration, yrs 2 29 INTEREST EXPENSES Input Formula 30 31 Down Payment on Land 32 33 TOTAL INVESTMENTIncluding interest $ 12,99,368 34 35 PROFIT Includes cost of interest 36 as percent of Revenue Input Formual 37 as percent of investment Input Formual 38 39 CONCLUSION: 40 Does it make sense to continue to explore this project? 41 Make a NOTE HERE 42 43 44 45 46 47 48 49 50 Introduction Part A 10pts Part B 10pts1 Looking at this project a little closer...... 2 Given the Following Project Schedule, WHAT SALES PRICE DO YOU NEED to make a 15% IRR? 1 A W Revenues TOTALS YOQ4 Y1Q1 Y1Q2 Y1Q3 Y104 Y2Q1 Y2Q2 Y2Q3 Y204 NOTES Sale of Condos at Completion so 8 LESS COMMISIONS 5% Input Calculation for commission 9 Net Revenue Input Calculation for Revenue 10 11 Expenses 12 Land Purchase so 13 Design/Planning Approval SO SO so Cost Spread over 3 Quarters 14 Development Charges 15 Foundation $4,50,000 $4,50,000 16 Above Grade Construction Cost Spread over 4 Quarters 17 Soft Costs Cost Spread over 4 Quarters 18 Net Expenses SO SO SO SO $4,50,000 SO 19 20 CASH on CASH (No Financing) 21 Profit/Cash Flow ($4,50,000) SO SO so SO ($4,50,000) so so SO SO 22 IRR/YR #NUM! 23 24 FINANCED 25 Principal so SO Calculate the Loan Principal 26 Rate 27 Interest So Input Calculation for INTEREST ONLY 28 29 30 Profit/Cash Flow $0 Input Calculation for Cash Flow per Quarter. 31 32 Calculated IRR on an ANNUAL BASE Don't Forget to MAKE IT ANNUAL! Insert Formula for IRR. Note it will be per quarter 33 34 What does the Discounted Cash Flow show you about the Project? 35