You have been given the role as project manager for developing a new office building project in New Tampa. Your

company president has asked you to study trends in the Hillsborough County office market, with regards to

development by time period, and average value per square foot for this development. You have been provided 3 pieces

of information to do this:

1. The Hillsborough County nonresidential tax roll from the County Property Appraiser (Hillsborough County

Nonresidential Property Appraiser Data.xlsx)

2. A copy of the Florida Department of Revenue Property Tax Data Files User’s Guide

(2018_NAL_SDF_NAP_Users_Guide.docx)

3. A copy of a summary market analysis table for the Miami-Dade office market conducted by Florida Economic

Advisors in 2019 (Miami-Dade Office Market Study.pdf)

To study office trends in the Hillsborough market, you need to replicate the FEA table, but using the Hillsborough

property appraiser data. This is going to require a lot of data sorting, cell referencing, summations, count functions, and

calculating percentages and averages. There are some things you need to know about the property appraiser data in

order to complete these sorts and calculate data:

The Department of Revenue use codes for “office” uses are on pages 5 and 6 of the User’s Guide. They are:

017, 018, 019, 023, and 024. As a hint (the only one I will give), the DOR column in the spreadsheet is Column H.

You don’t need any of the other properties with other DOR use codes besides these 5. You can delete the rows

that have other DOR use codes.

Once you have isolated the office properties, sort again to break them into two groups. Regional office space

(buildings greater than 50,000 square feet), and professional office space (buildings 50,000 square feet and less).

The column that references “total living or usable area” is the one you need to sort on. Find out what column

this is in the spreadsheet by looking in the user’s guide (I will not give you help to find this).

Once you have the office data broken into these two groups, sort each group by “effective year built” (see the

User’s Guide again) to get the office properties arranged by time period. Make sure you define your sort ranges

correctly.

At this poin, you will have the property base data sorted to make cell references and calculations to replicate

the table FEA conducted. The building valuation data is included in the column referencing “Just Value” (User’s

Guide). Make sure the table is in a different worksheet (not a different workbook, or file) from the base data.

You may wish to refer to the 2-4 workbook located in the “In Class Examples” module within Canvas as a guide

on how to do this.

SO: Now that you have the table finished, you have a basic profile of the office market in Hillsborough County. The

company president wants to develop a 300,000 square foot regional office building, and you have to conduct a present

value/discounted cash flow analysis to see if the project will be profitable, based on the following:

Development Cost Per Square Foot: The average value per square foot you calculated for regional office space

developed in Hillsborough County during the 2010-2019 period, in your previous market study workbook.

Cash Investment: 20%

Financing: 80%

Term of Development Loan: 15 years

Interest Rate: 8%

Annual % Increase in Building Value: 2.5%

Sell building in Year 15

Gross Leasable Area: 88% of building size

Lease Rate: $30 per square foot

Annual Lease Escalation Rate: 2.2%

Building Operations & Maintenance Costs: 11% of lease revenues



2 Year Building Lease Out: 75% Year 1, 25% Year 2



Use a 20% discount rate when conducting the NPV assessment

To complete this analytical assignment, you must do the following:

1. Conduct the market analysis

A. Use the base data from the property appraiser spreadsheet and complete the sorts

B. Develop the summary market analysis table in a separate worksheet within the same workbook

2. Conduct the present value/DCF analysis

A. Use the general framework from the model developed in the 2-6 in-class example (See Canvas), but keep in

mind you have different assumptions, and a different time horizon, than in that example

B. Calculate the NPV for the project at a 20% discount rate

C. Calculate the IRR for the project

Your final submittal should be 2 Excel file uploads. One for the market analysis, one for the Present Value/DCF analysis

IMPORTANT CONSIDERATIONS IN THIS EXERCISE

:



This is a heavy substance-driven exercise, but style and formatting, especially for your summary tables, still matter.

SUBSTANTIAL POINT REDUCTIONS WILL BE GIVEN FOR INCOMPLETE AND POOR FORMATTING.