Laksmana, Li & Associates, a consulting firm, is interested in predicting its monthly overhead costs. The controller imported its monthly cost 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,820, administrative staff hours are 1,350, and the number of client jobs is 25. 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:

A B E Billable Admin Number attorney staff of client Overhead 1 Date hours hours jobs costs 2 Jan-16 1,662 1,329 23 $ 72,299 3 Feb-16 1,707 1,365 22 $ 72,586 45 4 Mar-16 1,731 1,385 27 $ 74,350 5 Apr-16 1,629 1,303 14 $ 68,186 19 6 May-16 1,706 1,365 19 $ 70,455 7 Jun-16 1,708 1,366 22 $ 72,617