Question

Toby wants to perform some CVP analysis on fertilizing jobs. On one job, she used 40 pounds of fertilizer that she bought in bulk ($1,000 for 2,000 pounds). It took her employee 1.5 hours to spread the fertilizer (she is paid $15/hour) with the motorized spreader. Toby paid $4,000 for the spreader three years ago and it is estimated to last 10 years. To get to the job site, a company truck and trailer was used (cost: $40,000 expected to last 10 years). This was the only use of the truck and trailer that day. Additional employee costs include 10% payroll tax and $730 per year in worker’s compensation insurance. You may assume an employee works 2,000 hours per year on various tasks (not exclusively on fertilizing). Toby currently charges her customers $2.12 per pound to spread fertilizer.

Toby has collected some cost data over the past year (costs and pounds of fertilizer spread by month – per the table below).

1) Use account analysis to estimate variable and fixed costs to complete the analysis.

2) Use regression to estimate variable and fixed costs to complete the analysis.

3) Use calculations done by formula within Excel (including cell references).

4) Separate and label the parts of the calculation as Variable costs (VC), Contribution Margin (CM), Fixed Cost (FC) as annual breakeven (BE) point for fertilizer (in pounds) is calculated.

5) How many pounds must be spread to earn a target profit of $1,000?

6) What is the margin of safety on that target profit?

7) How many pounds must be sold (spread) if Toby wants a margin of safety of 10% of sales?

8) Is there any additional information that can be made when making these calculations?

Month	Units	Costs
Jan	200	5360
Feb	150	5408
Mar	250	5510
Apr	450	5704
May	350	5550
Jun	300	5405
Jul	200	5305
Aug	250	5509
Sep	375	5458
Oct	425	5700
Nov	175	5300
Dec	100	5250

Answer 1

1		Estimation of Variable and Fixed Costs
	A	Direct Material Cost per pound	$0.50	(1000/2000)
	B	Direct labor payment per pound	$0.56	(1.5*15)/40
	C=B*0.1	Additional payroll tax	$0.06
	D	Workers compensation insurance per hour	$0.37	(730/2000)	0.365
	E	Workers compensation insurance per pound	$0.01	(0.37*1.5)/40
	F=B+C+E	Total direct labor cost per pound	$0.63
	G=A+F	Estimated Variable cost per pound	$1.13
		Estimated Fixed Cost per month
		Depreciation of motorized spreader	$33.33	(4000/10)/12
		Depreciation of truck& trailer	$333.33	(40000/10)/12
		Total Fixed cost per month	$367
2		REGRESSION
		Month	Units	Costs
		Jan	200	5360
		Feb	150	5408
		Mar	250	5510
		Apr	450	5704
		May	350	5550
		Jun	300	5405
		Jul	200	5305
		Aug	250	5509
		Sep	375	5458
		Oct	425	5700
		Nov	175	5300
		Dec	100	5250
		Using 'Regression' data analysis toolof excel:
		SUMMARY OUTPUT
		Regression Statistics
		Multiple R	0.878244946
		R Square	0.771314185
		Adjusted R Square	0.748445603
		Standard Error	73.97368871
		Observations	12
		ANOVA
			df	SS	MS	F	Significance F
		Regression	1	184563.8505	184563.9	33.72812	0.000171
		Residual	10	54721.06621	5472.107
		Total	11	239284.9167
			Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
		Intercept	5143.727961	57.6815115	89.17464	7.69E-16	5015.206	5272.25	5015.206	5272.25
		X Variable 1	1.157911464	0.199378952	5.807591	0.000171	0.713667	1.602155	0.713667	1.602155
		Estimated Variable Cost	$               1.16	(X Variable)
		Estimated Fixed Cost	$      5,143.73	(Intercept)
		Sales Price	$2.12
		Contribution Margin per pound	$0.96	(2.12-1.16)
		Annual Fixed cost=5143.73*12=	$    61,724.74
		Annual Break even point in pounds	64,157	(61724.74/0.96)
		AnnualBreakeven sales	$        136,013	(64157*2.12)
	5)	Sales Required for target Profit of $1000
		64157+1000/0.96-	65,196	Pounds
		Sales in dollars=65196*2.12=	$        138,216