Sokolsky, Eugene & Associates, a consulting firm, is interested in predicting its monthly overhead costs. The controller imported its monthly occupancy data from the past two years into Excel. The monthly data included month, year, billable attorney hours, administrative staff hours, number of client jobs, and overhead costs.

The following is a sample of the records in that Excel file. Access the full data set online in the MyLab Accounting exercise.

Requirements
1. Using billable attorney hours as the independent variable and Overhead costs as the dependent variable, create a scatterplot using Excel that includes a regression line, cost equation, and R-squared value.

2. Using administrative staff hours as the independent variable and Overhead costs as the dependent variable, create a scatterplot using Excel that includes a regression line, cost equation, and R-squared value.
3. Using number of client jobs as the independent variable and Overhead costs as the dependent variable, create a scatterplot using Excel that includes a regression line, cost equation, and R-squared value.
4. Among billable attorney hours, administrative staff hours, and number of client jobs, which independent variable would most likely be the best predictor of overhead costs? Explain.
5. Assume that management wants to predict overhead costs in a month when billable attorney hours are 1,720, administrative staff hours are 1,250, and the number of client jobs is 23. Predict the firm’s overhead costs, using the cost equation in the scatterplot for the independent variable that you found to be the best predictor for overhead costs in your analysis for Requirement 4.

Transcribed Image Text:

1 23 45 A Date Jan-16 Feb-16 Mar-16 5 Apr-16 6 May-16 7 Jun-16 B Billable attorney hours 1,625 1,627 1,562 1,631 1,684 1,583 Admin staff hours 1,300 1,302 1,250 1,305 1,347 1,266 Number of client jobs 14 21 18 22 19 20 $ $ $ $ $ $ E Overhead costs 67,100 69,159 68,235 70,278 68,846 68,857