Question

The controller of WatoWato Co. has collected the following data for analyzing the cost behavior -of utility costs. Total Machine HoursTotal Utility Costs 300 350 2500 3000 3600 4500 3200 4900 4100 3800 5100 4200 3300 January February March June 520 September October November December 630 350 720 (a) Determine the fixed- and variable-cost components using regression analysis (you are required to use MS-Excel (employ either Data Anaysis Pack or excel tormulas) (b) Prepare a scatter plot using Excel. Present the cost equation line estimated in part (a) (c) Determine the fixed- and variable-cost components using High-Low method. Compare the fked costs between two methods Is the fixed cost calculated using High-Low method different than the one calculated using regression analysis? If yes, which one is more accurate and reliable?

Answer 1

Solution Total Machine Hours Total utility Cost Month January February March April May June July August September October November December 3000 3600 690 3200 4900 4100 3800 5100 4200 3300 3000 320 630 Regression Analysis

SUMMARY OUTPUT The Higher the value of R square is the more accurate it gives. It shows 46% times the Fixed Cost is 2057 and the Variable Cost is 3.26. R square measure the fittnes of the line. Here the fitness is very less. It means there is no relationship between the cost and activity X Variable 1 Line Fit Plot Regression Statistics 6000 4000 2000 0 Multiple P R Square Adjusted R Square Standard Error Observations 0.681667672 0.464670816 0.411137897 622.8444554 12 46% Predicted Y 0 200 400 600 800 ANOVA X Variable 1 Significance F 3367314.51 3367314.51 8.68009496 0.014629471 MS Regression Residual Total 10 3879352.156 387935.2156 11 7246666.667 Coefficients Standard Errort Stat .0% 2057.466516 607.3607318 3.387552747 0.006914226704.182472 3410.75056 704.182472 3410.75056 3.2607952 1.106779955 2.946200088 0.0146294710.794735783 5.726854618 0.7947357835.726854618 Lower 95% er 95% Lower 95.0% P-value U Upper 95 Intercept X Variable 1 Fixed Component = 2057.466516 Variable Component 3.2607952 As per regression analysis

Scatter Plot Total Utility Cost 6000 5000 3000 2000 1000 0 100 200 300 400500607800 Cost equation line estimated in part (a) If a straight line drawn from the first Plot to x axis it intersects @ 300 and intersects Y axis @ 2,100. It shows at 0 activity the fixed cost will be 2100

Hight-Low Method Need to find the highest level of activity and lowest level of activity Highest activity took place in December, where Machine Hours 720 and highest Utility Cost-3000 Lowest activity took place in January, where Machine Hours 300 and Utility Cost 2500 High-Low Activity 3000-2500/720-300 $1.91/hour Variable Cost $1.91/hour Total Cost Variable Cost+Fixed Cost Therefore, Fixed Cost-Total Cost-Variable Cost Fixed Cost-3000 (1.91X 720) $1,625 The Fixed Cost calculation using High-Low Method is different from the Fixed Cost what we calculated using Regression analysis. The Fixed cost using Regression analysis is considered as more acurate when compared with Fixed Cost calculated using High-Low method. When Fixed Cost is calculated using regression analysis, it consider all the factors. It examines the influence of one or more independent variables on a dependent variables and gives a detailed insight that will further improve the product and services. It also gives the R square value that shows the fitness of the linear regression. If fitness is less than 60%, it means there is no relationship between the cost and activity

Please find the solution. If you have any further question, please mention it in comment section. Will be happy to look into and answer. Thanks.