Developing the 2018 forecast

Work required: This project can be done alone or in teams with up to 2 people. If you are going
to work as part of a team, I must receive an email by Friday (2/15/2019) with the
name of both team members; otherwise, I will expect a solo submission (No
exceptions). I would prefer that you work with someone who is NOT currently on
your Homework Assignment 1 team. Feel free to email to me your questions on
the homework, or ask questions before, during, or after class.
Submission: Upload the file with your solutions (.doc or .pdf) on our Blackboard class page
along with the Excel file showing the raw data. The write up should be submitted
in Times New Roman or Calibri with an 11 font and single spaced. If you work in
a team, put both names on the front page of your solutions, and decide which
one of you will upload it on Blackboard.
Purpose: The purpose of this homework assignment is to give you the opportunity to
review and show your skills with the calculations we learned regarding different
ways how time series data can be forecast.
If you are comfortable with Excel, it will speed things up for you, as Excel is
required for this assignment. Be sure to copy your Excel tables into Word and
provide a detailed explanation as to how all answers were developed. If you
followed everything we are doing in class, have reviewed the Power Points, and
did the chapter reading, you should be able to complete this assignment in a few
hours. Otherwise, you will need to review our slides as well as the assigned
reading and may need to plan to spend a day or two on the assignment. Plan to
spend some time getting the required data online first, although I do explain in
the assignment which website you should use and how to navigate the site.
Task: Solve 3 numerical problems given on the ensuing pages. I will do the grading on
Blackboard within one (1) week, and we can go over any questions in class.
Grading criteria: Each Problem is worth 10 points. See next page for grading rubric and examples.
Below is the rubric I will be using when grading your solutions to each problem:
I expect to see the
following: 0 points 1 point 2 points
Show correct numerical
Incorrect Numerical answer is incorrect
because of a typo somewhere in the
calculations OR
Numerical answer is correct but only
in some intermediate steps
Show correct formulas Not shown Shown for some, but not all, steps Shown for all
Show correct numbers that
go into the formulas
Not shown Shown for some, but not all, steps Shown for all
Brief verbal explanation:
why you’re doing what
you’re doing in each step
Not shown Shown for some, but not all, steps Shown for all
Appearance Handwritten Partially hand-written and partially
You are offered an investment that guarantees to pay you back $200 per month for the next 5 years.
The annual rate of return for similar investments is 12%. What is the most you should be willing to pay
for it today?
Great answer!
(2 + 2 + 2 + 2 + 2 = 10 points)
Needs-some-work answer
(1 + 1 + 1 + 0 + 2 = 5 points)
This is an annuity problem because the problem is talking about the
same cash flow ($200) repeating a number of times.
The problem is essentially asking to calculate the Present Value of
this annuity. The formula is: PV = C x (1/R) x (1 – 1/(1+R)T). (OR: I
can use the financial calculator and enter PMT, N, and I/Y keys to
solve for PV.)
First, $200 is repeating monthly, and so I need to use the monthly
rate and the number of months. Monthly R = annual R / 12 months
= 12% / 12 = 1%. Number of months = 5 years x 12 months per year
= 60 months.
Then, I plug in the numbers into the annuity formula where I use
C=200, R=0.01, T=60. (OR: Then, I plug all resulting numbers into
the financial calculator: PMT=-200, I/Y=1, N=60, CPT PV.) This gives
So, I would pay no more than $8,991.01 for this investment.
N = 5
I/Y = 12
PMT = -200
Q: What’s missing?
A: Intermediate steps
explaining & calculating
the right numbers to be
used for “N” and “I/Y”.
Explanation in own words
showing you know what
you’re doing.
The questions in this homework are based on data that I would like you to get from 2 publicly available
secondary data sources: (1) US Census Bureau ( and (2) Bureau of Labor Statistics
For each question, show in your write up all numbers you are working with, and explain all relevant
calculations. Be sure to show the data that were downloaded in your Excel sheet, so I can verify your
numbers, and upload the Excel document to Blackboard along with the write up portion.
Problem #1 (10 points)
The manager of a shopping mall has noticed a continuous drop in overall mall sales in the last few
years. Therefore, she decided that she might need to attract some new tenant shops. Her quick glance
at the tenant mix indicated that shops which sell clothing and footwear appear to be underrepresented
in her tenant clientele. She knows that the majority of shoppers who come to her mall are people
between 25 and 34 years old. The manager would like to identify the best rent per square foot of
leasable area that she should offer to new tenants in the clothing and footwear sector. For that, she
hired you as a real estate market analyst and asked you to calculate the purchasing potential of
consumers in this age bracket for 2018.
(a) On the Bureau of Labor Statistics (BLS) website go to “Data Tools”. Once there, search the database
on consumer spending collected from BLSs Consumer Expenditure (CE) Survey. Both “one-screen
data search” (green button) and “multi-screen data search” (yellow button), among others, should
be able to help you find the data needed to solve this problem. Spending on clothing and footwear
is labeled as “apparel and services”. What are the annual expenditures for 2007-2017? Show
results in a table and in a time series graph.
(b) Forecast the expenditure for 2018 using the four moving-average (MA) methods below.

  1. 2-year simple MA,
  2. 3-year simple MA,
  3. 2-year weighted MA,
  4. 3-year weighted MA
    Based on your analysis of the methods above, which one should be used in developing the 2018
    forecast? Why? Show and explain all calculations and show the numbers used in performing your
    analysis and calculations.
    Problem #2 (10 points)
    A real estate investment company in Southern California is applying for a construction loan for a
    proposed new gated community with 250 single-family detached houses. If the loan is approved, it can
    start the construction as early as next week and have all houses completed and available for sale in
  5. The company’s research department recently performed a market study and identified Orange
    County, CA to be the main trade area that will draw the majority of households interested in buying
    the houses.
    For both parts (a) and (b), provide an excel worksheet that shows and explains your calculations, and
    show the numbers you plugged in to do your calculations.
    For the source of data, go to the US Census website, on the main page scroll all the way down and click
    on the Explore Data tab, select Data Tools and Apps, select American FactFinder, then go to
    Community Facts (Enter Community Name) or Advanced Search, and select your search criteria.
    (a) What does the company’s research team project for the total number of households in 2018 &
    2019? The team used the regression forecasting technique. It obtained information on the
    historical numbers of households in Orange County, CA for recent three years, 2015, 2016, 2017,
    from the US Census Bureau website, 1-year estimates, based on the American Community Survey
    (b) What is the required capture rate for this project, assuming the demand for new housing comes
    only from the increase in the number of households in the trade area?
    Problem #3 (10 points)
    Parts (a) and (b) of this problem are looking at the Location Quotient technique that is commonly used
    by real estate market analysts to see how concentrated a particular occupation, industry, demographic
    group, etc. is in a specific region as compared to a larger geographic area, such as the entire nation.
    For both parts (a) and (b), show and explain your calculations, and show the numbers that you plugged
    in to do your calculations. (Hint: Read Chapter 3 of the textbook used in this course, as it will provide
    insight to location quotients and various NAICS codes and where to find existing location quotients
    using the (BLS) website. How does this compare to your calculations?)
    For the source of data, go to the US Census website, on the main page scroll all the way down and click
    on the Explore Data tab, select Data Tools and Apps, select American FactFinder, then go to Advanced
    Search, and select your search criteria.
    You are interested in the concentration of employees in different industry sectors – in a metropolitan
    statistical area of your choice as compared to the entire USA. The US Census website has information
    on the number of employees in your base area in different NAICS sectors. When you do your analysis
    pick the most recent available year. Which year is that? What do the results tell you about potential
    real estate investment opportunities in your picked analysis area? What type of investments would
    instead not be a good idea?
    (a) Now, do similar Location Quotient calculations, except this time based on the population
    distribution by age rather than employment by industry. Compare any county of your choice with
    the entire state in which it is located – what are the relative concentrations of the population in
    different age groups? What does this tell you about potential real estate investment opportunities?
    For example, you may find that your chosen county has a high concentration of the population
    above 65 compared to the state statistic, which may suggest a good potential for senior housing
    developments; and so on. Again, show the numbers for the most recent year with available data
    (which year is it?).