You have been retained to assist a regional food marketer, FoodKing, to forecast the demand for pies that are mass-produced and marketed under the name Ms. Smith's Homemade Pies. To assists with your analysis, you are provided with data that was collected for 8 consecutive quarters and 6 geographic markets. If you use Excel or any other statistical packet, be sure to attach a copy of the output. See the attached excel file with data & address the following:
1) Can you estimate a regression model that expresses sales as a function of price, income and competitors Price and population? Is so, give a clear interpretation of the R-square, discuss the statistical significant of the overall regression and indicate whether each coefficient is statistically significant or not.
2) Can you provide a clear, concise interpretation for the slope coefficients on price and on income?
(3) FoodKing is considering entering the market in a new city. In this market, the population is 2,200,000, the average income is $42,400 and the competitor's product sells for $4.15. You expect to spend $5,000 on advertising. Using this regression model, if you price your product at $6.00, how many pies will you sell?
(4) Given the information in (3), at what price would you maximize the TOTAL REVENUE that the firm receives? (hint: one approach is to use SOLVER). Based on this answer, was demand elastic or inelastic in a neighborhood of the original $6 price? Explain your answer.
(5) One of your managers says that pie sales are higher during the fourth quarter of the year. Based on the data that you have, does that appear to be true? If so, how much higher are they in the fourth quarter than for a typical quarter?
Full solution in excel file.