Exam 2 – Troubleshooting

March 9, 2015

Here are a few common issues people are running into:

  • When creating Dummy variables in Excel many are using the IF function but not getting it to work. You need to format the statement with double quotes around the neighborhood name like the following: =IF(G2=”Eastside”,1,0)
  • Non-numeric data found and LINEST() errors seem to be related to the use of Filters in combination with the Regression function. To remedy, follow these steps.
    1. Copy your data
    2. Paste (values only) into a brand new worksheet
    3. Re-run regression

 

Please let me know if there are other issues to post here.

Justin

Justin Holman is CEO of Aftermarket Analytics, where he leads efforts to develop cutting edge sales forecasting and inventory optimization technology for the Automotive Aftermarket. Prior to joining Aftermarket Analytics, Justin managed corporate consulting for the Strategy & Analytics division at MapInfo Corporation, leading major projects for retail clients including The Home Depot, Darden Restaurants, Bridgestone-Firestone, Sainsbury’s and New York & Company. Before that, Justin served as Vice President of Software Development at LogicTools, now part of IBM's supply chain application software group. Justin holds a B.A. from Claremont McKenna College, a Ph.D. from the University of Oregon and an Executive Management certificate from Northwestern University's Kellogg School of Management.

3 Comments

  • Reply
    Dylan 10 years ago

    Thank You

  • Reply
    Shannon Roppolo 10 years ago

    Professor,

    This afternoon you should me a formula for 2015 forecasting that referenced a dummy for the neighborhood that I chose, could you show me that formula again? This is in reference to the y equation.

    • Reply
      Justin 10 years ago

      Hi Shannon,

      Let’s say your model uses Square Footage (SqFt), Beds, Baths and two neighborhood “dummy” variables, “Aberdeen” and “Westside”.

      And, the coefficients produced by your final model are as follows:
      Intercept: 2377.2
      SqFt: 49.7
      Beds: 4189.5
      Baths: 16001.0
      Aberdeen: 12499.4
      Westside: -15043.8

      This would translate to a regression equation, as follows (* means multiply):

      y = 49.7*SqFt + 4189.5*Beds + 16001.2*Baths + 12499.4*Aberdeen + (-15043.8)*Westside + 2377.2

      To use the same equation to generate a forecast you would plug in the values for each variable. For example..

      SqFt=1500; Beds=3; Baths=2; Aberdeen=1; Westside=0
      y = 49.7*(1500) + 4189.5*(3) + 16001.2*(2) + 12499.4*(1) + (-15043.8)*(0) + 2377.2
      y = 74550 + 12568.5 + 32002.4 + 12499.4 + 0 + 2377.2
      y = 133997.5

      In other words, the forecast for a 1500 sqft, 3 bed, 2 bath house in the Aberdeen neighborhood would be $133,997.50.

      Hope this helps!

Leave a Reply