Excel – Statistical Applications

/in /by

Excel – Statistical Applications

Instructions for Correlation Worksheet

Widgco has provided the sales and advertising budget from the previous year. Both the Chief Financial Officer and Vice President of Marketing would like to know if there is any relationship between the typically increasing advertising budget and the sales generated.

  1. In cell E4, enter the function that determines the correlation between Sales and Advertising.
  2. In Cell F4, display the formula in E4, using the function FORMULATEXT.
  3. In G4, comment on the correlation, what is your analysis between sales and advertising spending.
  4. Add sparklines to further support your analysis.

Instructions for Frequency Worksheet

  1. Copy range A1:C15 from Correlation Worksheet to Histogram Worksheet.
  2. Create seven bins (7) for sales starting at $100,000 and incrementing $5,000 for each bin. Therefore the final bin is $130,000. Use cells E4…E10.
  3. Create six bins (6) for advertising starting at $5,500 and incrementing $500 for each bin. Therefore the final bin is $8,000. Use cells H4…H9.
  4. Determine the frequency of Sales and Advertising dollars in each bin, in cells F4…F10 and cells I4…I9 respectively.
  5. Create Histograms for Sales and Advertising in cells E18…F26 and E31… F38 respectively. Output both a chart and graph.
  6. Create header labels and titles to clearly explain your work.

Instructions for Regression Worksheet

  1. Copy range A1:C15 from Correlation Worksheet to Regression Worksheet. Copy Advertising into Column B and Sales Dollars into Column C, in other words switch the columns.
  2. Create a scatter plot using both sales and advertising.
  3. Limit the horizontal axis to showing values from $5,000 to $7,500 and the vertical axis to showing values from $100,000 to $130,000.
  4. Add headers and format the chart to look similar to Figure 1.
  5. Display a linear trend line by selecting the data point that represents (5775, 105000).
  6. Display the formula of the linear line.
  7. Predict the sales, based on the trend line, if advertising dollars were set at $5,800, $6,800 and 12,000 respectively. Create a chart with your answers and clearly denote your answers.