Instructions Complete the Module 4 Assignment using the included Excel Spreadsheet template. This assignment focuses on financial statement analysis and financial ratios. All assignments must be submitted by 11:59 pm on Sunday. Late assignments will be graded per the Late Assignment Policy posted within the syllabus. The grading rubric is listed below. Prompt Case Study of Castles in the Air published by ModelOff (Links to company website, for problem see below). Directions for Cases: a Review the information and questions for the case Castles in the Air, posted as a link below Using the NPV example provided below as a template, or by creating your own, develop your model for this case o TIP: If you use the NPV example as a template, update ALL figures to match the actual problem - this is the biggest error made by students, failing to update the template completely. Answer all of the questions listed for this model, found in the case entitled Case Study 1 Castles in the Air a Section 2: Case Study Castles In The Air - INTRODUCTION All the inputs mentioned below are provided in the workbook for this case study. You are working for a company which is considering purchasing a number of properties. You have been asked to model each of the available investments to assist in choosing a portfolio (up to a maximum purchase price of $1,700,000) that maximizes the value to the company, as measured by an increase in net present value. The company's cost of capital is 8%. AVAILABLE INVESTMENTS Full details of the investments may be found on the table on the subsequent page . The purchase price for each property should be paid on 31 December 2017. The company holds the property for a number of years (the investment length). . During the investment length, the company receives rental revenue and pays operating costs. Where amounts are indexed the base date is 1 January 2018 and the index should step annually (i.e. a full year of indexation should first be applied on 1 January 2019). DO NOT round inflated prices to whole cents in interim calculations. At the end of the investment length, the company will sell the property for the terminal value. The terminal value is not indexed. . For property 4, the company has the option of overhauling the property. Details of the property without overhaul are listed under property 4a, Details of the property with overhaul are listed under property 4b. The overhaul cost should not be considered in the purchase price constraint. It is NOT possible to invest in both property 4a and property 4b. The overhaul cost (which is not indexed). Prepare your model and then use it to answer the given questions. When finished, please upload your workbook. See questions page for further directions. INVESTMENT DETAILS Property 1 $450,000 Property 2 $550,000 Property 3 $500,000 Property 4a $470,000 Purchase Price Investment length Overhaul cost 5 years 5.5 years 6 years 4 years N/A N/A N/A N/A Property 4b As 4a As 4a $125,000 paid 31 Dec 2019 $675,000 Up to overhaul as 4a Terminal value Rental revenue $500,000 $45,000 per year Paid monthly $575,000 $60,000 per year Paid quarterly (starting March) Indexed at 3% $550,000 $55,000 per year Paid quarterly (starting January) Indexed at 2% $570,000 $55,000 per year Paid monthly Indexed at 2.5% Not indexed Afterwards: $75,000 per year Paid monthly Not indexed Up to overhaul as 4a Operating costs 5% of revenues $4,500 per year Paid monthly Indexed at 3% $1,000 in April $3,000 in October Indexed at 2% $3,000 per year Paid monthly Indexed at 1% Afterwards: 8% of revenues Case Questions Directions: Using the data provided, and your knowledge of finance, answer all questions listed below. Qualitative questions can be addressed in a word document; calculations, including all work required for each question, should be submitted on an Excel spreadsheet. Note on Question #6: NPV calculations should use the formula for TVM based on =NPV(rate, value 1, value 2...)+original purchase price as a negative. You DO NOT need to calculated the XNPV values. Question #1 What are the total revenues for Property 1? Question #2 What are the revenues for Property 2 in September 2019? Question #3 What are the costs for Property 3 in October 2020? Question #4 What are total revenues less total costs for Property 4a? Question #5 What is the absolute value of difference in operating costs between Properties 4a and 4b? Question #6 Using the provided data, calculate the NPV for each project. Question #7: Analyzing your data from Property 4, should you complete the overhaul (choice B) or continue to rent as is (choice A). Explain your reasoning. Question #8: Which properties should the company invest in subject to the constraint on purchase price in order to maximize their increase in net present value? Question #9: Based on the provided information, which property would you choose to move forward with if you were choosing an investment? Why would you choose this property? Question #10: Based on the provided information, which property is the worst choice for investment? Why is it the worst choice? Question #11: What additional information would you want to know before making a final selection for a property investment? Instructions Complete the Module 4 Assignment using the included Excel Spreadsheet template. This assignment focuses on financial statement analysis and financial ratios. All assignments must be submitted by 11:59 pm on Sunday. Late assignments will be graded per the Late Assignment Policy posted within the syllabus. The grading rubric is listed below. Prompt Case Study of Castles in the Air published by ModelOff (Links to company website, for problem see below). Directions for Cases: a Review the information and questions for the case Castles in the Air, posted as a link below Using the NPV example provided below as a template, or by creating your own, develop your model for this case o TIP: If you use the NPV example as a template, update ALL figures to match the actual problem - this is the biggest error made by students, failing to update the template completely. Answer all of the questions listed for this model, found in the case entitled Case Study 1 Castles in the Air a Section 2: Case Study Castles In The Air - INTRODUCTION All the inputs mentioned below are provided in the workbook for this case study. You are working for a company which is considering purchasing a number of properties. You have been asked to model each of the available investments to assist in choosing a portfolio (up to a maximum purchase price of $1,700,000) that maximizes the value to the company, as measured by an increase in net present value. The company's cost of capital is 8%. AVAILABLE INVESTMENTS Full details of the investments may be found on the table on the subsequent page . The purchase price for each property should be paid on 31 December 2017. The company holds the property for a number of years (the investment length). . During the investment length, the company receives rental revenue and pays operating costs. Where amounts are indexed the base date is 1 January 2018 and the index should step annually (i.e. a full year of indexation should first be applied on 1 January 2019). DO NOT round inflated prices to whole cents in interim calculations. At the end of the investment length, the company will sell the property for the terminal value. The terminal value is not indexed. . For property 4, the company has the option of overhauling the property. Details of the property without overhaul are listed under property 4a, Details of the property with overhaul are listed under property 4b. The overhaul cost should not be considered in the purchase price constraint. It is NOT possible to invest in both property 4a and property 4b. The overhaul cost (which is not indexed). Prepare your model and then use it to answer the given questions. When finished, please upload your workbook. See questions page for further directions. INVESTMENT DETAILS Property 1 $450,000 Property 2 $550,000 Property 3 $500,000 Property 4a $470,000 Purchase Price Investment length Overhaul cost 5 years 5.5 years 6 years 4 years N/A N/A N/A N/A Property 4b As 4a As 4a $125,000 paid 31 Dec 2019 $675,000 Up to overhaul as 4a Terminal value Rental revenue $500,000 $45,000 per year Paid monthly $575,000 $60,000 per year Paid quarterly (starting March) Indexed at 3% $550,000 $55,000 per year Paid quarterly (starting January) Indexed at 2% $570,000 $55,000 per year Paid monthly Indexed at 2.5% Not indexed Afterwards: $75,000 per year Paid monthly Not indexed Up to overhaul as 4a Operating costs 5% of revenues $4,500 per year Paid monthly Indexed at 3% $1,000 in April $3,000 in October Indexed at 2% $3,000 per year Paid monthly Indexed at 1% Afterwards: 8% of revenues Case Questions Directions: Using the data provided, and your knowledge of finance, answer all questions listed below. Qualitative questions can be addressed in a word document; calculations, including all work required for each question, should be submitted on an Excel spreadsheet. Note on Question #6: NPV calculations should use the formula for TVM based on =NPV(rate, value 1, value 2...)+original purchase price as a negative. You DO NOT need to calculated the XNPV values. Question #1 What are the total revenues for Property 1? Question #2 What are the revenues for Property 2 in September 2019? Question #3 What are the costs for Property 3 in October 2020? Question #4 What are total revenues less total costs for Property 4a? Question #5 What is the absolute value of difference in operating costs between Properties 4a and 4b? Question #6 Using the provided data, calculate the NPV for each project. Question #7: Analyzing your data from Property 4, should you complete the overhaul (choice B) or continue to rent as is (choice A). Explain your reasoning. Question #8: Which properties should the company invest in subject to the constraint on purchase price in order to maximize their increase in net present value? Question #9: Based on the provided information, which property would you choose to move forward with if you were choosing an investment? Why would you choose this property? Question #10: Based on the provided information, which property is the worst choice for investment? Why is it the worst choice? Question #11: What additional information would you want to know before making a final selection for a property investment