Background

A local development firm has an option to purchase and wants to develop a new 25-acre property in Irish Hills area in San Luis Obispo. The area is zoned agriculture currently, but the local planning commission is very receptive to developing more housing in this area. You have been asked to help determine the current construction costs for two options from the perspective of this developer. See Shea and Pulte for information on the two projects that the company is evaluating as Alternative Models for this new Irish Hills project. Each Alternative Models has three different options such as home style or number of bedrooms.

Develop an Excel spreadsheet to calculate a lump sum equivalent cost a Net Present Value of each project at the beginning of its development and then use a Future Value to determine an equivalent cost today or at some point in the future.

Complete a quarterly cashflow for analysis and then determine a PW as of October 1, 2014before adjusting the costs for inflation to current dollars.

Excel

Create an excel spreadsheet to project the current costs for each of the two Alternative Models (one built by Shea and one by Pulte). The size of each project reported will have to be adjusted to fit the size of the property currently being considered. Apply an average inflation rate of 8% per year to adjust the costs reported to current dollars and calculate the following:

a)What is the equivalent cost in todays dollars of the Shea Homes project (as of the end of the current quarter July 1, 2021)?

b)What is the equivalent cost in todays dollars of the Pulte Homes project (as of the end of the current quarter July1, 2021)?

c)What is the projected cost of the Shea Homes project if it was built in 2024 (as of the beginning of the year)?

d)What is the projected cost of the Pulte Homes project if it was built in 2026(as of the beginning of the year)?

Shea Information

This project was developed on an 8 acres site and has 435 luxury apartments. This apartment community is located in Aliso Viejo's mixed-use development known as Vantis. The apartments are adjacent to a new Homewood Suites by Hilton hotel, retail, office and for-sale residential. The community offers a resident clubhouse, business center, fitness center, resort-style pool with cabanas, rooftop deck, and open space. Individual units feature a contemporary floor plan, wood-style plank flooring, quartz countertops, stainless appliances and high-end finishes throughout. Units floorplans range from 567 square feet for a studio to 1260 square feet for a 3-bedroom unit. The development consists of four floors with 559,152 gross square feet. Project is wrapped around a 6-tier, 756 space mechanically ventilated concrete framed parking structure. Exterior is plaster, metal panels, stone, and wood siding. The cost for the purchase and development of the site was $5,500,000 credited at the beginning of the preconstruction period. The preconstruction work cost was $500,000 credited three months after the start of preconstruction. The construction costs of $75,000,000 can be assumed to be credited in a uniform series of costs at the end of every quarter from January 2015 through September 2016 (7 quarters). The project schedule had first turnover of units 3 months prior to completion. Preconstruction started in October 2014 and final turnover and completion was September 2016 (a total of 8 quarters or two years.)

Note that the current project being considered at 25 acres is on a site around three times the size of this comparable development. All costs and numbers of developed units will have to be adjusted proportionally to this increase in size. Multiply all costs reported by three.

Pulte Information

The 101-acre development consists of 458 single-family residents with three different models with approximately 1/3 of each model. Model one, Cordoba, has 1550 sq. feet with three bedrooms and two bathrooms with a 2-car garage. Model 2, Seville, has 1850 sq. feet with three Bedroom and two bathrooms with a 2-car garage. Model 3, Toledo, has 2150 sq. feet with four bedroom and two and baths. The development was within a few miles of schools, and is close to shopping, restaurants, and hospitals. The target audience was entry-level home purchase. The land cost for the site was $4,351,000 credited at the beginning of site development. Site development cost was $22,024,678 credited in three equal quarterly payments over the next nine months. The total home construction costs were $78,918,480. The construction was completed in 9 equal phases with construction cycle time of 3 months with completion date of September 2006. The construction costs can be credited at the end of each of nine quarters for a total development and construction time of three years.

Note that the current project being considered at 25 acres is on a site around one-fourth the size of this comparable development. All costs and numbers of developed units will have to be adjusted proportionally to this decrease in size. Divide all costs reported by four.

Excel Sheet Example Format

I am so confused about this question. All the information for this question is provided. Assistance is greatly appreciated.

B A 1 Present value 2 Interest Rate 3 Periods 4 Payments 5 type 6 7 Future value 8 formula D 100 10% 2 years 0 0 0 calculate ($121.00) =FV(C2,C3,C4,C1,C5) 8