MUST BE DONE ON EXCEL

Case Study Assignment No. 2

Create a cash flow excel spreadsheet for each of the two Alternative Models (Pulte and Shea) using the cost and sales data from Table 1 and production rates and sales rates from Table 2A and 2B. Using the net amount from each quarter, calculate the following for each Alternative development including land purchase, site development, and precon costs- as it was built at the time for each (do not adjust the project costs and income for inflation):

a)NPV using excel function =NPV()

b)AW using Excel =PMT ()

c)IRR using excel function =IRR()

Table 1

Cost and sales data for each Alternative in costs at the time. (Do not adjust these data for current, inflated costs.) Pulte homes: a)Cordoba house cost to build is $274,500 and sells for $330,000 b)Seville house cost to build is $327,700 and sells for $410,000 c)Toledo house cost to build is $380,800 and sells for $495,000 Shea Apartments: a)Studio cost to build is $117,800 and sells for $135,000 b)1 Bedroom cost to build is $158,300 and sells for $200,000 c)2 Bedroom cost to build is $257,800 and sells for $330,000

Pulte Homes

Project Location is Victorville, in San Bernardino County, California. Project Description(Do NOT adjust the size of this project to account for the size of the current planned project. Also, do NOT adjust costs and income for inflation into todays dollars.) 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 Bedroom and twobathrooms 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 quarterly payments over the next nine months. The construction costs and income from sales are as indicated in Table 1 and Table 2B. The project schedule had first turnover of units 6 months prior to completion. The construction and sales of these homes were completed over ten quarters with a completion date of December 2006 for a total development, construction, and sales time of three years and three months.

Table 2B

Quarters

Plute		1	2	3	4	5	6	7	8	9	10
Cordoba	Built	15	15	23	23	23	23	15	15	0	0
	Sold	0	0	7	8	23	23	23	23	23	22
Serville	Built	15	15	23	23	23	23	16	15	0	0
	Sold	0	0	7	8	23	23	23	23	23	23
Toledo	Built	15	15	23	23	23	23	16	15	0	0
	Sold	0	0	7	8	23	23	23	23	23	23

CASE Study 1 Information (Needed for this problem)

Given: Pulte Development Date

Land Purchase 4351000 1-oct-03

Site Development 22024678 1-jul-04

Construction 78918480 1-0ct-06

Interest Rate is 8% per year or 2% per quarters

Development Under Review is 1/4 smaller than this opportunity

Determine:

b) Equivalent cost for the entire project for 1-jul-21

d) Equivalent cost for the entire project for 1-jan-26

Solution:

1. Translate all time to quarters and use 2%

2. Bring the Land and Site development cost to future and add to construction

1-oct-03 to 1-oct-06 is 12 quarters

Land 4351000

Future Cost 5518120 =FV(E7,G18,0,F19,0)

1-jul-04 to 1-oct-06 is 9 quarters

Site Development 22024678

Future Cost 26321529 =FV(E7,G22,0,F23,0)

Total cost effective 1-oct-06 is future land + precon + construction

Land 5518120

Precon 26321529

Construction 78918480

Total 110758129

A) Need to move total cost from oct 1, 2006 to july 1, 2021

1-oct-06 to 1-jul-21 is 59 quarters

"Current" 1-oct-06 110758129

Future Cost 1-jul-21 356275325 =FV(E7,G37,0,F39,)

Adjustment due to size of Future Development 356275325 x 1/4 = 127212224

B) Need to move total cost from oct 1, 2016 to January 1, 2026

1-oct-06 to 1-jan-26 is 77 quarters

"Current" 1-oct-06 110758129

Future Cost 1-jan-26 508848896 =FV(E7,G47,0,F49,0)

Adjustment due to the size of future development 508848896 x 1/4 = 127212224