1. **Problem Statement:** We have monthly data on overhead costs and billable hours. We want to: a. Develop a trendline (linear regression) to model overhead costs as a function of billable hours. b. Interpret the coefficients of the regression model. c. Decide if a special job with 1,000 billable hours and a margin of 38,000 before overhead is attractive. 2. **Formula and Approach:** We use the simple linear regression model: $$\text{Overhead Cost} = b_0 + b_1 \times \text{Billable Hours}$$ where $b_0$ is the fixed overhead (intercept) and $b_1$ is the variable overhead cost per billable hour (slope). 3. **Calculate the coefficients:** Given data points $(x_i, y_i)$ where $x_i$ = billable hours and $y_i$ = overhead costs: Calculate means: $$\bar{x} = \frac{3000 + 4000 + 5000 + 6000 + 7000 + 8000}{6} = 5500$$ $$\bar{y} = \frac{365000 + 400000 + 430000 + 477000 + 560000 + 587000}{6} = 469833.33$$ Calculate slope $b_1$: $$b_1 = \frac{\sum (x_i - \bar{x})(y_i - \bar{y})}{\sum (x_i - \bar{x})^2}$$ Calculate numerator: $$(3000-5500)(365000-469833.33) + (4000-5500)(400000-469833.33) + (5000-5500)(430000-469833.33) + (6000-5500)(477000-469833.33) + (7000-5500)(560000-469833.33) + (8000-5500)(587000-469833.33)$$ $$= (-2500)(-104833.33) + (-1500)(-69833.33) + (-500)(-39833.33) + (500)(7166.67) + (1500)(90166.67) + (2500)(117166.67)$$ $$= 262083333.33 + 104750000 + 19916666.67 + 3583333.33 + 135250000 + 292916666.67 = 818500000$$ Calculate denominator: $$(3000-5500)^2 + (4000-5500)^2 + (5000-5500)^2 + (6000-5500)^2 + (7000-5500)^2 + (8000-5500)^2$$ $$= 2500^2 + 1500^2 + 500^2 + 500^2 + 1500^2 + 2500^2 = 6,250,000 + 2,250,000 + 250,000 + 250,000 + 2,250,000 + 6,250,000 = 17,500,000$$ So, $$b_1 = \frac{818500000}{17500000} = 46.77$$ Calculate intercept $b_0$: $$b_0 = \bar{y} - b_1 \times \bar{x} = 469833.33 - 46.77 \times 5500 = 469833.33 - 257235 = 212598.33$$ 4. **Regression model:** $$\text{Overhead Cost} = 212598.33 + 46.77 \times \text{Billable Hours}$$ 5. **Interpretation:** - The fixed component $b_0 = 212598.33$ represents the overhead costs that occur regardless of billable hours (e.g., rent, salaries). - The slope $b_1 = 46.77$ means each additional billable hour adds approximately 46.77 to overhead costs. 6. **Evaluate the special job:** - Additional overhead for 1000 hours: $$46.77 \times 1000 = 46770$$ - Margin before overhead: 38000 - Net margin after overhead: $$38000 - 46770 = -8770$$ Since the net margin is negative, the job is not attractive financially. **Final answer:** The regression model is $$\text{Overhead Cost} = 212598.33 + 46.77 \times \text{Billable Hours}$$ The fixed overhead is about 212598.33, representing costs independent of hours. The job with 1000 billable hours is not attractive as it results in a loss of 8770 after overhead costs.