Use comparable to start to develop a plan for a current project using data from previous projects. As we start to develop this proforma to evaluate alternative cost proposals, we are going to build on the previous spreadsheet developed for Case Study #1. This time we are going to use both cost and income data from the previous projects to determine the rate of return earned by each. We will use the costs given for the comparable projects as given at that time not the inflated costs in todays dollars. We will leave the size of each project the same as it was originally built not adjusted for the size of our current project.

Case Study #1 (based on my first case study I need help with the next one)

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 anticipated rate of return for two different options from the perspective of this developer. See pages 3 and 5 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. The company has a MARR of 12%.

Therefore, 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()

See example sheet at end of assignment for example calculation spreadsheet. Use this as a guideline only. This example used an annual cashflow you will use a quarterly cashflow.

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

(Hint: Start with your Excel spreadsheet from Case Study #1 and modify as appropriate for this Case Study. Use the modified spreadsheet to develop a net cashflow for each quarter including costs before the start of construction. Use this quarterly net cashflow to calculate the NPV, NAW (by quarter), and the IRR for each Alternative project. The IRR will be expressed as a percentage per quarter convert that rate to a nominal annual rate for each project.All formulas should reference other cells; do not type values into the formulas.)

Shea Information and Table 2A:

(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.)

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 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 and income from sales are as indicated in Table 1 and Table 2A.The project schedule had first turnover of units 6 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.) The final sales of the units went on for one quarter past completion until the end of 2016 so the 8 quarters shown in the table below were from the start of construction until the end of sales.

Table 2A: Construction and sales production

Pulte Information:

(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 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 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: Construction and sales production

0.02 A) Shea Multifamily in Aliso Viejo, California Present Value $ (174,554,157.90) Interest Rate Periods 27 Payment 0 Type 0 Quarters Initial Cost Preconstruction Cost Contruction Cost Contruction Cost (7 Quarters) Interest Rate Present Value 8 16500000.0 1500000.0 225000000.0 32142857.1 0.08 ($174,554,157.90) 0.02 Cashflows by Quarter (Shea) 0 16500000.0 1 1500000.0 2 32142857.1 3 32142857.1 4 32142857.1 5 32142857.1 6 32142857.1 7 32142857.1 8 32142857.1 Total 243000000.0 30375000 Future Value Formula FV(B5,B6,B4,B7,B8)= $6,169,498,683.09 End of the current quarter-July 1, 2021 B) Pulte Homes in Victorville, California Present Value $ (16,531,353.95) Interest Rate 0.02 Periods 71 Payment 0 Type 0 Quarters Initial Cost Preconstruction Cost Contruction Cost Contruction Cost (3 Quarters) Precontruction Cost (9 Quarters) Interest Rate Present Value 12.0 1087750.0 5506169.5 19729620.0 1835389.8 2192180.0 0.08 ($16,531,353.95) 0.02 Future Value Formula FV(B17,318,316,319,820)= $2,545,456,045.71 End of the current quarter-July 1, 2021 Cashflows by Quarter (Pulte) 0 1087750.0 1 1835389.8 2 1835389.8 3 1835389.8 4 2192180.0 5 2192180.0 6 2192180.0 7 2192180.0 8 2192180.0 9 2192180.0 10 2192180.0 11 2192180.0 12 2192180.0 Total 26323539.5 2193628 C) Shea Multifamily in Aliso Viejo, California Present Value $6,169,498,683.09 Interest Rate 0.02 Periods 10 Payment 0 Type 0 Future Value Formula FV(B33,B34,B32,B35,B36)= ($67,554,289,288.14) Beginning of the year-2024 D) Pulte Homes in Victorville, California Present Value $2,545,456,045.71 Interest Rate 0.02 Periods 22 Payment 0 Type 0 Future Value Formula FV(B44,345,343,B46,847)= ($69,488,362,690.58) Beginning of the year-2026 Quarters Shea 1 2 3 4 5 6 7 CO Studio Built Sold 2 0 3 0 mo Ow 3 4 2 5 |0 0 2 1 BR Built Sold 36 0 36 0 48 0 49 0 36 50 36 0 84 72 0 35 2BR o 0 Built Sold 26 0 27 0 35 0 36 0 27 27 34 63 53 28 Quarters Pulte 1 2 3 4 5 5 6 7 8 9 10 Cordoba 3 Built Sold 15 0 15 0 23 7 23 8 23 23 23 23 15 23 15 23 0 23 22 Seville 15 15 Built Sold 15 0 23 7 23 8 23 23 23 23 16 23 0 23 0 23 0 23 Toledo . 23 23 15 Built Sold 15 0 15 0 23 7 23 8 16 23 0 23 23 23 23 23 Example of Spreadsheet Cordoba Yr 1 Yr 2 Yr 3 Yr 4 Income Cost $ 4,950,000 $ 15,180,000 $ 8,235,000 $ 12,627,000 Net $ (3,285,000) $ 2,553,000 $2,553,000 $ 6,615,000 NPV $5,123,327.32 =NPV(0.12,V14:Y14) Result Of Formula Interest rate Input range EXAMPLE OF FORMULA INPUTS 12% MARR Cordoba Yr 1 Yr 2 Yr 3 Yr 4 Income Cost Net $ 4,950,000 $ 15,180,000 $ 8,235,000 $ 12,627,000 $ (3,285,000) $ 2,553,000 $2,553,000 $ 6,615,000 NPV $ 5,123,327 AW $ 1,686,776 IRR 82% =NPV(0.12,V14:Y14) --PMT(R15,4, R22) =IRR(S20:V20) 0.02 A) Shea Multifamily in Aliso Viejo, California Present Value $ (174,554,157.90) Interest Rate Periods 27 Payment 0 Type 0 Quarters Initial Cost Preconstruction Cost Contruction Cost Contruction Cost (7 Quarters) Interest Rate Present Value 8 16500000.0 1500000.0 225000000.0 32142857.1 0.08 ($174,554,157.90) 0.02 Cashflows by Quarter (Shea) 0 16500000.0 1 1500000.0 2 32142857.1 3 32142857.1 4 32142857.1 5 32142857.1 6 32142857.1 7 32142857.1 8 32142857.1 Total 243000000.0 30375000 Future Value Formula FV(B5,B6,B4,B7,B8)= $6,169,498,683.09 End of the current quarter-July 1, 2021 B) Pulte Homes in Victorville, California Present Value $ (16,531,353.95) Interest Rate 0.02 Periods 71 Payment 0 Type 0 Quarters Initial Cost Preconstruction Cost Contruction Cost Contruction Cost (3 Quarters) Precontruction Cost (9 Quarters) Interest Rate Present Value 12.0 1087750.0 5506169.5 19729620.0 1835389.8 2192180.0 0.08 ($16,531,353.95) 0.02 Future Value Formula FV(B17,318,316,319,820)= $2,545,456,045.71 End of the current quarter-July 1, 2021 Cashflows by Quarter (Pulte) 0 1087750.0 1 1835389.8 2 1835389.8 3 1835389.8 4 2192180.0 5 2192180.0 6 2192180.0 7 2192180.0 8 2192180.0 9 2192180.0 10 2192180.0 11 2192180.0 12 2192180.0 Total 26323539.5 2193628 C) Shea Multifamily in Aliso Viejo, California Present Value $6,169,498,683.09 Interest Rate 0.02 Periods 10 Payment 0 Type 0 Future Value Formula FV(B33,B34,B32,B35,B36)= ($67,554,289,288.14) Beginning of the year-2024 D) Pulte Homes in Victorville, California Present Value $2,545,456,045.71 Interest Rate 0.02 Periods 22 Payment 0 Type 0 Future Value Formula FV(B44,345,343,B46,847)= ($69,488,362,690.58) Beginning of the year-2026 Quarters Shea 1 2 3 4 5 6 7 CO Studio Built Sold 2 0 3 0 mo Ow 3 4 2 5 |0 0 2 1 BR Built Sold 36 0 36 0 48 0 49 0 36 50 36 0 84 72 0 35 2BR o 0 Built Sold 26 0 27 0 35 0 36 0 27 27 34 63 53 28 Quarters Pulte 1 2 3 4 5 5 6 7 8 9 10 Cordoba 3 Built Sold 15 0 15 0 23 7 23 8 23 23 23 23 15 23 15 23 0 23 22 Seville 15 15 Built Sold 15 0 23 7 23 8 23 23 23 23 16 23 0 23 0 23 0 23 Toledo . 23 23 15 Built Sold 15 0 15 0 23 7 23 8 16 23 0 23 23 23 23 23 Example of Spreadsheet Cordoba Yr 1 Yr 2 Yr 3 Yr 4 Income Cost $ 4,950,000 $ 15,180,000 $ 8,235,000 $ 12,627,000 Net $ (3,285,000) $ 2,553,000 $2,553,000 $ 6,615,000 NPV $5,123,327.32 =NPV(0.12,V14:Y14) Result Of Formula Interest rate Input range EXAMPLE OF FORMULA INPUTS 12% MARR Cordoba Yr 1 Yr 2 Yr 3 Yr 4 Income Cost Net $ 4,950,000 $ 15,180,000 $ 8,235,000 $ 12,627,000 $ (3,285,000) $ 2,553,000 $2,553,000 $ 6,615,000 NPV $ 5,123,327 AW $ 1,686,776 IRR 82% =NPV(0.12,V14:Y14) --PMT(R15,4, R22) =IRR(S20:V20)