NOVA SOUTHEASTERN UNIVERSITY
H. Wayne Huizenga School of Business and Entrepreneurship
MBA Program
ECN 5050 - Economic Thinking
Data Exercise 3
This exercise is designed to assess your ability to manipulate, analyze, and present data in accordance with Course Competencies
#5: Locate and download appropriate macroeconomic data
#6: Use Microsoft Excel to manipulate and analyze data
#7: Graph macroeconomic data effectively.
Instructions:
_Put all of your work in one file;
_Write your name in the header;
_We need to see your work in Excel (i.e., formulas). Do not copy and paste numbers from
your original worksheet to a new worksheet. When submitting, make sure that the cells
in your worksheet show formulas.
_After you finish the assignment, name your file LastnameDE1.xls (or .xlsx, depending on
the version of Excel). Note that you do not have to write the .xls suffix by yourself -
Excel will do it automatically, and
_Upload it to the Data Exercise Section of the Blackboard.
Background:
You have learned that price-quantity points normally represent equilibrium points because transactions usually occur at equilibrium. You have also learned that equilibrium points change in markets as a result of shifts in demand, or shifts in supply or both. In particular, equilibrium points moving in a northeasterly direction may reflect increasing demand; equilibrium points moving in a southwesterly direction may reflect decreasing demand. Also, equilibrium points moving in a southeasterly direction may reflect increasing supply; equilibrium points moving in a northwesterly direction may reflect decreasing supply.
Assignment:
This exercise asks you to look at what happened to equilibrium points in the Broward County Real Estate market during the period since 2004. This period covered the end of the Real Estate Boom, the collapse induced by the Great Recession and the post-recession Recovery. You are asked to identify the shifts that might have dominated in the market during selected periods.
a. Most of the data on the number of sales and median prices of used single family homes are given to you in the next file below the Instructions. It is an Excel File entitled Data on Real Estate Sales in Broward County 2004-14 Shell. It is a shell because you will have to fill in a number of calculations that are started for you. You should download the file to your computer.
b. Go to the website of the Greater Fort Lauderdale Realtors and click on the RESEARCH tab on the ORANGE line. Under the Broward & Palm Beach County Heading, change the month in 2014 for Broward County from January to March. Click on View. Open the pdf file that downloads. Enter the figure for March 2014 Closed Sales into the appropriate Sales cell in the Shell file. Enter the Median Sale Price for March 2014 into the appropriate Median Price cell in the Shell File. Repeat for April 2014 and so on. When no file opens, you have reached the limit of the data.
c. Select the 12-mo trend, the 24-mo trend and the Sales/24-mo trend for August 2004 Sales. Drag the 3 formulas down until you get to the month that is 7 months earlier than the latest month for which you have data. (Aug 2013 if Feb 2014 is the latest month). Next select the 12-mo trend, the 24-mo trend and the Prices/24-mo trend for the August 2004 Median Price. Drag the 3 formulas down until you get to the month that is 7 months earlier than the latest month for which you have data.
d. The columns labeled Sales/24-mo trend and Prices/24-mo trend are Seasonal Residuals which you will need to transfer to the second tab in the worksheet labeled Derive Seasonal Factors. You will need to array the single column of data Sales/24-mo trend into 12 columns of data, with one column for each month. All the January Sales/24-mo trend figures go in the January column, all the February Sales/24-mo trend figures go in the February column and so on. Do the same for the Prices/24-mo trend figures in the Prices Seasonal Residuals table. The first 6 months of so of 2004 are blank because you have no data; some of the last months in 2013 and all of 2014 are also blank. (As time progresses, all of 2013 will be filled in.)
e. Calculate seasonal factors as centered three year averages down each month column. 2006 is the earliest year that you will calculate the factors as a centered moving average over 2005, 2006, 2007 values. 2011 is the latest year that you will calculate the factors as a centered moving average over 2010, 2011, 2012 values. Make the 2004, 2005 monthly seasonal factors the same as 2006; make the 2012, 2013, 2014 monthly seasonal factors the same as 2011. Derive factors for median prices as well as sales.
f. Return to the Data Tab in the worksheet. The seasonal factors are divided into the original monthly data for sales and for median prices to get the seasonally adjusted values.
g. Do three charts for each series (sales and median prices). The charts need to look professional. The charts are for 2004-07 (The End of the Boom), 2008-09 (The Crash), and 2010-14 (The Recovery). What shifts occurred in demand or supply in each period?