Use the information below to answer the questions that follow: Month Procedures
ID: 2567339 • Letter: U
Question
Use the information below to answer the questions that follow:
Month
Procedures
Electricity
Jan
1,000
4,500
Feb
800
3,500
Mar
700
3,025
Apr
900
3,250
May
950
3,300
Jun
1,000
4,400
Jul
1,100
4,200
Aug
1,250
4,800
Sep
1,200
5,000
Oct
1,050
4,500
Nov
1,100
4,250
Dec
900
3,200
Separate the fixed and variable components.
Are the number of procedures a good cost driver and why?
Assuming 1,000 procedures next month, what would be the projected total cost of electricity?
Use regression in Excel to solve this problem.
Month
Procedures
Electricity
Jan
1,000
4,500
Feb
800
3,500
Mar
700
3,025
Apr
900
3,250
May
950
3,300
Jun
1,000
4,400
Jul
1,100
4,200
Aug
1,250
4,800
Sep
1,200
5,000
Oct
1,050
4,500
Nov
1,100
4,250
Dec
900
3,200
Explanation / Answer
Month Procedures Electricity SUMMARY OUTPUT Jan 1,000 4,500 Feb 800 3,500 Regression Statistics Mar 700 3,025 Multiple R 0.868331066 Apr 900 3,250 R Square 0.753998841 May 950 3,300 Adjusted R Square 0.729398725 Jun 1,000 4,400 Standard Error 360.9175218 Jul 1,100 4,200 Observations 12 Aug 1,250 4,800 Sep 1,200 5,000 ANOVA Oct 1,050 4,500 df SS MS F Significance F Nov 1,100 4,250 Regression 1 3992541.674 3992541.674 30.65021496 0.000248829 Dec 900 3,200 Residual 10 1302614.576 130261.4576 Total 11 5295156.25 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 99.0% Upper 99.0% Intercept 215.0450789 690.4432748 0.311459445 0.761847573 -1323.358407 1753.448564 -1973.157924 2403.248082 X Variable 1 3.794515402 0.685392831 5.536263628 0.000248829 2.267365006 5.321665798 1.622318632 5.966712172 1 Separate the fixed and variable components. As can be seen from regression output from excel regression function of data analysis Equation will be as follows: Y = 3.7945X + 215.0451 Here 3.7945 is variable cost per procedures and 25.0451 is fixed cost per month 2 Are the number of procedures a good cost driver and why? No, Number of procedures is not a good driver , since Standard deviation is very high 69% for variable cost. And R square is also 0.75 its should be near about 1 for perfect regression fitting. 3 For 1000 procedures cost will be: Y = 3.7945*1000 + 215.0451 Y = 4009.54 or 4010 rounded
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.