Question

Eastman Publishing Company is considering publishing an electronic textbook about spreadsheet applications for business. The fixed cost of manuscript preparation, textbook design, and web-site construction is estimated to be $150,000. Variable processing costs are estimated to be $7 per book. The publisher plans to sell single-user access to the book for $49.

(a)	Build a spreadsheet model in Excel to calculate the profit/loss for a given demand. What profit can be anticipated with a demand of 3,400 copies?
	For subtractive or negative numbers use a minus sign.
	$
(b)	Use a data table to vary demand from 1,000 to 6,000 in increments of 200 to test the sensitivity of profit to demand. Breakeven occurs where profit goes from a negative to a positive value, that is, breakeven is where total revenue = total cost yielding a profit of zero. In which interval of demand does breakeven occur?
	(i) Breakeven appears in the interval of 3,000 to 3,200 copies.
	(ii) Breakeven appears in the interval of 3,400 to 3,600 copies.
	(iii) Breakeven appears in the interval of 3,600 to 3,800 copies.
	(iv) Breakeven appears in the interval of 3,800 to 4,000 copies.
	- Select your answer -Option (i)Option (ii)Option (iii)Option (iv)Item 2
(c)	Use Goal Seek to answer the following question. With a demand of 3,400 copies, what is the access price per copy that the publisher must charge to break even?
	If required, round your answers to two decimal places.

Answer 1

ANSWER A PART		CALCULATION of PROFIT and LOSS
		DEMAND	3400copies
		SALE PRICE	$49 per copy
		variable cost	$7per copy
		fixed cost	$150,000
		Particulars	Amount ($)
	A	sales(3400*49)	$166,600
	B	variable cost(3400*$7)	$23,800
	C = A-B	Contribution(SALES-VC)	$142,800
	D	FIXED COST	$150,000
	E=C-D	NET PROFIT/LOSS	($7,200)
		Hence there will be LOSS of -$7200
	ANSWER B PART		SALE PRICE	$49 per copy
			variable cost	$7per copy
			fixed cost	$150,000
		SALE VALUE= DEMAND*SP		CONTRIBUTION= SALES- VARIBALE COST			PROFIT/LOSS= CONTRIBUTION - FIXED COST
		DEMAND	SALES VALUE ($)	VARIABLE COST ($)	CONTRIBUTION ($)	FIXED COST ($)	PROFIT($)
		1000	49000	7000	42000	150000	-108000
		1200	58800	8400	50400	150000	-99600
		1400	68600	9800	58800	150000	-91200
		1600	78400	11200	67200	150000	-82800
		1800	88200	12600	75600	150000	-74400
		2000	98000	14000	84000	150000	-66000
		2200	107800	15400	92400	150000	-57600
		2400	117600	16800	100800	150000	-49200
		2600	127400	18200	109200	150000	-40800
		2800	137200	19600	117600	150000	-32400
		3000	147000	21000	126000	150000	-24000
		3200	156800	22400	134400	150000	-15600
		3400	166600	23800	142800	150000	-7200	breakeven range
		3600	176400	25200	151200	150000	1200
		3800	186200	26600	159600	150000	9600
		4000	196000	28000	168000	150000	18000
		4200	205800	29400	176400	150000	26400
		4400	215600	30800	184800	150000	34800
		4600	225400	32200	193200	150000	43200
		4800	235200	33600	201600	150000	51600
		5000	245000	35000	210000	150000	60000
		5200	254800	36400	218400	150000	68400
		5400	264600	37800	226800	150000	76800
		5600	225400	39200	186200	150000	36200
		5800	284200	40600	243600	150000	93600
		6000	294000	42000	252000	150000	102000
		BREAKEVEN LIES BETWEEN RANGE 3400- 3600 copies as it is going from loss towards Profit, in between it will be zero.
		Answer is option (ii)
	ANSWER C PART		At BREAKEVEN Profit will be equal to Cost, Hence zero.
			AMOUNT
	A	SALES	126200
	B	VARIABLE COST (3400*7)	23800
	C=A-B	CONTRIBUTION(SALES- VC)	150000
	D	FIXED COST	150000
	E=C-D	PROFIT	0
		Hence we have to go in reverse with profit 0,fixed cost $150000, variable cost $23800, the sales value is $126200 for 3400 copies.
		hence sales price for break even $126200/3400 = $37.1176
				Approx $37.12