please complete all parts and show work and formulas in excel.

2. The CEO of Kuehner Development Co. has just come from a meeting with his marketing staff where he was given the latest market study of a proposed new shopping center, Parker Road Plaza. The study calls for a construction phase of one year and an operation phase of five years. The property is to be sold at the end of the fifth year of operation. Part I. Construction Phase: The marketing staff has chosen a 12-acre site for the project that they believe they can acquire for $2.25 million. The initial studies indicate that this shopping center will support a floor- to-area ratio of 36.35 percent and a 92.11 percent leasable area ratio. (This means that the gross building area (GBA) will be 190,000 sq.ft., and the gross leasable area (GLA) will be 175,000 sq. ft.] The head of Kuehner's construction division assures the CEO that construction can keep hard costs to $54 per square foot (GBA) and soft costs (excluding interest carry and all loan fees) to $4.50 per square foot (GBA). The division has decided to subcontract all of the site improve- ments at a total cost of $750,000. The Shawmut Bank has agreed to provide interim financing for the project. The bank will finance all of the construction costs and site improvements at an annual rate of 13 percent plus a loan commitment fee of two points. The construction division estimates that 60 percent of the total direct cost will be taken down evenly during the first six months of the construction phase. Kuehner expects to obtain permanent financing from the Acme Insurance Co. at an interest rate of 12 percent for 20 years with a 2.5 percent prepaid loan fee and a 10-year call. Kuehner is expected to make monthly loan payments. a. What will be the total project cost for Parker Road Plaza (excluding loan commitment fees and interest carry)? What will be the total direct costs? b. What will be the interest carry for the Parker Road Plaza project? What will be the total loan amount that Kuehner must borrow (including interest carry)? What will be the yield to the lender on this construction loan? c. What is the total project cost and how much equity must be put into the project each year during the construction phase? (Kuehner will fund both loan commitment fees from project equity.) Part II. Operations and Final Sale Phase: Kuehner estimates that it can lease Parker Road Plaza for $18.50 per square foot (GLA) base rent with a 3 percent overage on gross sales in excess of $200 per square foot (GLA). The company expects rents to increase by 5 percent per year during the lease period and tenant reimbursements to run $8 per square foot (GLA) and to increase at the same rate as rents. Kuehner expects to have the shopping center 70 percent leased during the first year of operation. After that, vacancies should average about 5 percent per year. The vacancy losses should be cal- culated on the entire gross potential income, which includes minimum rents, percentage rents, and tenant reimbursements. Sales, which are expected to average $210 per square foot (GLA) for the first year of operation, should grow at 6 percent per year. The operating expenses are expected to average S14 per square foot of GLA for the first year and will increase at the same rate as the rents. Kuehner will collect an additional 5 percent of EGI as an annual management fee. The final sales price is expected to be $18.4 million and Kuehner will incur sales expenses of 2 percent. Two schedules provide necessary information about this phase of the project: (1) the gross potential income of Parker Road Plaza for the five-year operation period and (2) the schedule of amortization and depreciation expenses for the project. d. What cash flows would Kuehner Development Co. earn before and after taxes for Parker Road Plaza if it were operated for five years (assuming the marginal tax rate to be 28% for ordinary income and capital gains)? What cash flows will Kuehner realize before and after taxes from the sale of the project after five years? e. Assuming that Kuehner's before-tax required rate of return is 16 percent, should the company develop Parker Road Plaza? Justify your answer based on BTNPV and BTIRR Pro Forma Operating Statement-Parker Road Plaza Cash Flows (End of Year) 2 3 4 5 6 Income Minimum rent $3,237,500 $3,399,375 $3,569,344 $3,747,811 $3,935,201 Overage (% of gross sales) 52,500 118,650 188.769 263,095 341.881 Tenant reimbursements (per GLA) 1.400,000 1,470,000 1.543,500 1,620,675 1.701.709 Potential gross income $4,690,000 $4,988,025 $5,301,613 $5,631,581 $5,978,791 Item Amortization Period Construction loon fees Permanent loan fees 1 year 10 years Capital Improvements (90% of total) Tenant improvements (10% of total) Depreciation Period 31.5 years SL 7 years DDB 2. The CEO of Kuehner Development Co. has just come from a meeting with his marketing staff where he was given the latest market study of a proposed new shopping center, Parker Road Plaza. The study calls for a construction phase of one year and an operation phase of five years. The property is to be sold at the end of the fifth year of operation. Part I. Construction Phase: The marketing staff has chosen a 12-acre site for the project that they believe they can acquire for $2.25 million. The initial studies indicate that this shopping center will support a floor- to-area ratio of 36.35 percent and a 92.11 percent leasable area ratio. (This means that the gross building area (GBA) will be 190,000 sq.ft., and the gross leasable area (GLA) will be 175,000 sq. ft.] The head of Kuehner's construction division assures the CEO that construction can keep hard costs to $54 per square foot (GBA) and soft costs (excluding interest carry and all loan fees) to $4.50 per square foot (GBA). The division has decided to subcontract all of the site improve- ments at a total cost of $750,000. The Shawmut Bank has agreed to provide interim financing for the project. The bank will finance all of the construction costs and site improvements at an annual rate of 13 percent plus a loan commitment fee of two points. The construction division estimates that 60 percent of the total direct cost will be taken down evenly during the first six months of the construction phase. Kuehner expects to obtain permanent financing from the Acme Insurance Co. at an interest rate of 12 percent for 20 years with a 2.5 percent prepaid loan fee and a 10-year call. Kuehner is expected to make monthly loan payments. a. What will be the total project cost for Parker Road Plaza (excluding loan commitment fees and interest carry)? What will be the total direct costs? b. What will be the interest carry for the Parker Road Plaza project? What will be the total loan amount that Kuehner must borrow (including interest carry)? What will be the yield to the lender on this construction loan? c. What is the total project cost and how much equity must be put into the project each year during the construction phase? (Kuehner will fund both loan commitment fees from project equity.) Part II. Operations and Final Sale Phase: Kuehner estimates that it can lease Parker Road Plaza for $18.50 per square foot (GLA) base rent with a 3 percent overage on gross sales in excess of $200 per square foot (GLA). The company expects rents to increase by 5 percent per year during the lease period and tenant reimbursements to run $8 per square foot (GLA) and to increase at the same rate as rents. Kuehner expects to have the shopping center 70 percent leased during the first year of operation. After that, vacancies should average about 5 percent per year. The vacancy losses should be cal- culated on the entire gross potential income, which includes minimum rents, percentage rents, and tenant reimbursements. Sales, which are expected to average $210 per square foot (GLA) for the first year of operation, should grow at 6 percent per year. The operating expenses are expected to average S14 per square foot of GLA for the first year and will increase at the same rate as the rents. Kuehner will collect an additional 5 percent of EGI as an annual management fee. The final sales price is expected to be $18.4 million and Kuehner will incur sales expenses of 2 percent. Two schedules provide necessary information about this phase of the project: (1) the gross potential income of Parker Road Plaza for the five-year operation period and (2) the schedule of amortization and depreciation expenses for the project. d. What cash flows would Kuehner Development Co. earn before and after taxes for Parker Road Plaza if it were operated for five years (assuming the marginal tax rate to be 28% for ordinary income and capital gains)? What cash flows will Kuehner realize before and after taxes from the sale of the project after five years? e. Assuming that Kuehner's before-tax required rate of return is 16 percent, should the company develop Parker Road Plaza? Justify your answer based on BTNPV and BTIRR Pro Forma Operating Statement-Parker Road Plaza Cash Flows (End of Year) 2 3 4 5 6 Income Minimum rent $3,237,500 $3,399,375 $3,569,344 $3,747,811 $3,935,201 Overage (% of gross sales) 52,500 118,650 188.769 263,095 341.881 Tenant reimbursements (per GLA) 1.400,000 1,470,000 1.543,500 1,620,675 1.701.709 Potential gross income $4,690,000 $4,988,025 $5,301,613 $5,631,581 $5,978,791 Item Amortization Period Construction loon fees Permanent loan fees 1 year 10 years Capital Improvements (90% of total) Tenant improvements (10% of total) Depreciation Period 31.5 years SL 7 years DDB