Question

Mason Office Services color copies for various customers. The company has gathered the following data:

Month                         # Copies                      Copy Department Costs

July                             17,000                                     $1,445

August                        21,250                                       1,685

September                   22,950                                       1,785

October                       18,700                                       1,530

November                   20,400                                       1,615

December                   25,500                                       2,040

Required:

(a) Enter the above data onto an Excel worksheet. Then, using the High-Low method, build the applicable regression equation, and

                        (i)    Solve for “f.”

                        (ii)   Solve for “v”

                        (iii) Estimate Copy Department Costs, assuming the number of copies is 23,000.

(b) On the same worksheet, using Excel’s Least Squares Regression application,

                        (i) Solve for “f.”

                        (ii) Solve for “v.”

                        (iii) Estimate Copy Department Costs, assuming the number of copies is 23,000.

                        (iv) Based on the r² measure (coefficient of determination), explain what it indicates about how accurate (or inaccurate) the Least Squares equation is at predicting total costs.

Answer 1

Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks!

Mason Office Services
Answer a
High Low method
Variable cost=	(Highest activity cost- Lowest activity cost)/ (Highest activity units- Lowest activity units)
	Month	Costs	Copies
Highest activity	December	2,040.00	25,500.00
Lowest activity	July	1,445.00	17,000.00
Difference		595.00	8,500.00
Variable cost per unit		0.07	This is answer ii.
Fixed cost	Highest activity cost- (Highest units * Variable cost per unit)
Fixed cost	2040-(25500*0.07)
Fixed cost	255.00		This is answer i.
	23000 copies
Copies	23,000.00
Variable cost per unit	0.07
Total Variable cost	1,610.00
Fixed cost	255.00
Estimated total cost	1,865.00		This is answer iii.

Answer b

Least Squares Regression

The equation of a line is y=mx+b

X= number of copies

y=total cost

m= (N Σ(xy) − Σx Σy)/ (N Σ(x2) − (Σx)2)

Month	Copies (x)	Costs (y)	x2	xy
July	17,000.00	1,445.00	289,000,000.00	24,565,000.00
August	21,250.00	1,685.00	451,562,500.00	35,806,250.00
September	22,950.00	1,785.00	526,702,500.00	40,965,750.00
October	18,700.00	1,530.00	349,690,000.00	28,611,000.00
November	20,400.00	1,615.00	416,160,000.00	32,946,000.00
December	25,500.00	2,040.00	650,250,000.00	52,020,000.00
Total	125,800.00	10,100.00	2,683,365,000.00	214,914,000.00

N= number of months= 6.
m=	(N Σ(xy) − Σx Σy)
	(N Σ(x2) − (Σx)2)
m=	6*214914000- 125800*10100
	6*2683365000- 125800^2
m=	1289484000- 1270580000
	16100190000-15825640000
m=	18,904,000.00
	274,550,000.00
m=	0.07
b= (Σy − m Σx)/N
b=	10100- 0.07*125800
	6.00
b=	10100- 8661.89473684211
	6.00
b=	1,438.11
	6.00
b=	239.68
The equation of a line is y=mx+b
y=	0.07x+239.68
Answer i	f= $ 239.68
Answer ii	v= $ 0.07
Answer iii
Cost of 23000 copies	0.07*23000+239.68
Cost of 23000 copies	1,823.34