Question

Scatter Diagrams and High-Low Cost Estimation Assume the local Pearle Vision has the following information on the number of sales orders received and order-processing costs. Month Sales Orders Order-Processing Costs 3,000 $ 82,700 1,500 50,375 4,400 120,700 2,800 2,300 1,200 2,000 81,900 69,775 43,100 62,500 Required (a.) Use information from the high- and low-volume months to develop a cost-estimating equation for monthly order-processing costs. = $ 14,000 ~ +$ 24.25 ♥ (b.) Plot the data on a scatter diagram. Using the information from representative high-and low-volume months, develop a cost estimating equation for monthly production costs. If needed, an Excel worksheet is provided in the following link in order to create the scatter diagram: Scatter_Diagram + $ Check

Answer 1

Part 1

Level	Months	Sales orders	Order-processing costs
High	3	4,400	$                 120,700
Low	6	1,200	$                   43,100
Difference		3,200	$                   77,600
Variable cost per unit (X) (77600/3200)			$                     24.25
Total cost at high level			$                 120,700
Less: Variable cost at high level (4400*24.25)			$                 106,700
Fixed cost			$                   14,000
Formula using High low method
$14,000	+	$24.25

Part 2

? X GS HOMEINSERT 2019 10 01.xlsx - Microsoft Excel DEVELOPER - 2 Sign in FILE PAGE LAYOUT FORMULAS DATA REVIEW VIEW C20 C D E F G H L M N O P Q A Scatter diagram y = 23.93x + 14202 $140,000 Sales orders 3,000 1,500 4,400 2,800 2,300 1,200 2,000 Order-processing costs $82,700 $50,375 $120,700 $81,900 $69,775 $43,100 $62,500 $120,000 $100,000 $80,000 Remarks 12 Intercept $14,202 =INTERCEPT(B2:38,A2:A8) 13 Slope $23.93 ESLOPE(B2:38,A2:48) 14 15 Formula using Scatter diagram $14,202 $23.93 Order-Processing Costs $60,000 $40,000 $20,000 - 500 1,000 1,500 2,000 3,000 3,500 4,000 4,500 5,000 2,500 Sales Orders ... Sheet151 Sheet152 Sheet153 Sheet154 Sheet155 | Sheet156 Sheet157 Sheet158 + 1 Activate Windows Go to Settings to activate Windows 0 0 0 - + READY 100% AF ENG Search the web and Windows 10:17 US 01-11-19