Accounting Analytics Case

$($THIS IS AN INDIVIDUAL ASSIGNMENT, SO PLEASE TURN IN YOUR OWN WORK USING YOUR OWN WORDING, ETC.$)$

A firm is analyzing the relationship between advertising expense and sales in order to determine whether to increase or scale back on advertising in the future.

They collect the following information from the prior year:

Month Advertising Exp. Revenue

January $$3,740$ $$85,550$

February $8,65091,220$

March $6,900127,220$

April $7,50094,460$

May $3,150124,780$

June $4,60089,600$

July $9,25090,100$

August $6,000126,500$

September $5,300110,250$

October $7,15098,400$

November $9,800108,650$

December $4,500130,750$

Required:

$1.$ Use the data above to generate the regression analysis output in Excel when comparing each month's advertising expense to the same month's revenue.

Include a plot$/$graph of the regression line in Excel showing the equation$/$function and R$-$squared. What do you conclude based upon the regression analysis?

$2.$ The firm realizes that there may be a lag between the advertising incurred and the timing of sales revenue. They modify the analysis by comparing each

month's sales revenue to the prior month's advertising expense. Generate another regression analysis after discarding January sales revenue and December

advertising expense in order to compare each month's sales to the prior month's advertising expense. What would be the function $($equation$)$ for this regression line?

Include a plot$/$graph of the regression line in Excel showing the equation$/$function and R$-$squared.

$3.$ Is the coefficient for advertising expense statistically significant in requirements #$1$ and #$2?$ Briefly explain how you came to your conclusion.

$4.$ What proportion of changes in sales revenue is explained by the amount of advertising expense in both requirements #$1$ and #$2?$

Briefly explain how you came to your conclusion.