Please show step by step so I can understand the process and attach excel spreadsheet. You will need data analysis add-on or stat tool. Use the output from the regression analysis to calculate the elasticity.

In this project, you are asked to take sales data, estimate a demand function, and make a management decision based on your calculations.

Background

Your company has tasked you with estimating demand for a new type of frozen snack food that the company has developed.The existing snacks your company is competing with are well established in the market and rarely change price.However, you are not sure how much your consumer will be willing to pay relative to the existing snacks.

One thing you are confident in is that people will want to buy more of your new frozen snack on hot days.

Your company has varied the price of the product each of the last 10 weeks in a test location and collected data on sales.The results are in Elasticity Exercise.xlsx, and include price charged, units sold per hour, and the average temperature for that day.

Your manager has informed you that your company has another team working on minimizing production costs, so you don't have that data yet."Just find the best price to charge to drive the highest revenuewe'll deal with cost data later."

Instructions

Use the data in attachments to perform a multiple regression analysis, with Sales per Hour as your dependent variable and 'Price' and 'Avg Temp' as independent variables (use analysis took pack add in -->regression analysis in excel ). Use the results to answer the following questions.

Questions

1.You have hypothesized that the general form of the demand function is Q_d = f(P, T) where P is the price of ice cream cones and T is average temperature.Based on your regression analysis, write out your estimate of the specific form of the demand for your company's new snack.In other words, if Q_d=a + b₁P + b₂T, what values do a, b₁ and b₂ take?(Hint: what should the sign on b₁ be, based on the Law of Demand?)

2.Assume the average high temperature will be 80 next year.Draw the demand curve.Be careful to put P on the vertical axis and Q on the horizontal axis.This will probably require rearranging your data so that P looks like a function of Q.Excel, for example, will by default assume that if your data is in two columns, the first column is your X or independent variable.

Please label the vertical intercept (above what price will 0 units be sold) and the horizontal intercept (if P=0, how many units are 'sold').

Create a graph in Excel and embed the image in the space provided below.Do not submit a photo.

3.A co-worker suggests charging $2.50 per snack.Do you agree?Calculate the price elasticity of demand if you charge $2.50 (still assuming an average temperature of 80 degrees).Use that calculation to argue whether you should charge a price of $2.50, more than $2.50, or less than $2.50.

Use this formula E=alphax * (Px/Qx) and Obtain Qx

4.What price would maximize revenue if your estimate of demand is correct? How did you arrive at this price?