I’m studying for my Excel class and don’t understand how to answer this. Can you help me study?
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
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
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%
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.