Small Company is investigating cost behavior. One method might be to look at the specific accounts like Wage Expense, Supplies Expense, and the other expenses and attempt to determine how much fixed and how much variable cost is in each of those accounts.
We haven't done that here. Instead, the total operating expenses have been aggregated into a single account. Use least squares regression to identify the fixed and variable costs for Small Company. Operating expenses are on the 'Y' axis. Services provided are the activity driver on the 'X' side.
You might try the 'Data Analysis' add-in for excel. Alternatively you can do it the hand-calculator way.
Hand calculations to find the cost equation numbers:
1. Find the average value for x and average value for y. Let's call them "ave(x)" and "ave(y)."
2. Slope (variable cost)= (∑¦?(??-??????(??))(??-??????(??))?)/(∑¦(??-??????(??))2)
3. Intercept (fixed cost):= ave(y) - Slope * ave(x)
Required:
1. Find the cost equation for total cost: Total cost = Fixed Cost + Variable Cost*Activity
2. Predict the total cost for 4,000 services
3. Comment on the quality of the prediction. If you used a regression package like the 'Data Analysis' add-in, what was the R2 for the regression and what does that mean? Whether you used a regression package or not,were the actual costs for the months nearest 4,000 services about where they should be using the cost predictions?
4. If you had enough influence over the local economy that you could set prices, at what retail price would you set services if you wished to make $10,000 in a month where there were 4,000 services?