Question

You may use Microsoft Excel or any other technology to complete the question as long as the explanation is clear and a screen shot is included. Please complete the following problems: 1. Eastman Publishing Company is considering publishing an electronic textbook on spreadsheet applications for business. The fixed cost of manuscript preparation, textbook design, and Web site construction is estimated to be $160,000. Variable processing costs are estimated to be $6 per book. The publisher plans to sell access to the book for $46 each. a. Build a spreadsheet model to calculate the profit/loss for a given demand. What profit can be anticipated with a demand of 3500 copies? b. Use a data table to vary demand from 1000 to 6000 increments of 200 to assess the sensitivity of profit to demand. c. Use Goal Seek to determine the access price per copy that the publisher must charge to break even with a demand of 3500 copies.

Answer 1

Requirement a
Spread sheet model to calculate profit
Demand ( copies)		3,500
Selling price per book		46
Less: Variable cost per book		(6)
Contribution per book	Sellin price - Variable cost)	40
Total Contribution	(Demand * Contribution)	140,000
Less: Fixed Costs		(160,000)
Profit/ (Loss)	( Contribution - Fixed costs)	(20,000)

Requirement b
Data table for Sensitivy of profit to Demand
Demand	Contribution	Fixed Costs	Profit /(Loss)
(In Books)	(Demand*$40)
1,000	40,000	160,000	(120,000)
1,200	48,000	160,000	(112,000)
1,400	56,000	160,000	(104,000)
1,600	64,000	160,000	(96,000)
1,800	72,000	160,000	(88,000)
2,000	80,000	160,000	(80,000)
2,200	88,000	160,000	(72,000)
2,400	96,000	160,000	(64,000)
2,600	104,000	160,000	(56,000)
2,800	112,000	160,000	(48,000)
3,000	120,000	160,000	(40,000)
3,200	128,000	160,000	(32,000)
3,400	136,000	160,000	(24,000)
3,600	144,000	160,000	(16,000)
3,800	152,000	160,000	(8,000)
4,000	160,000	160,000	-
4,200	168,000	160,000	8,000
4,400	176,000	160,000	16,000
4,600	184,000	160,000	24,000
4,800	192,000	160,000	32,000
5,000	200,000	160,000	40,000
5,200	208,000	160,000	48,000
5,400	216,000	160,000	56,000
5,600	224,000	160,000	64,000
5,800	232,000	160,000	72,000
6,000	240,000	160,000	80,000

Requirement c
Demand ( copies)		3,500
Selling price per book		51.71
Less: Variable cost per book		(6)
Contribution per book	Sellin price - Variable cost)	45.71
Total Contribution	(Demand * Contribution)	160,000
Less: Fixed Costs		(160,000)
Profit/ (Loss)	( Contribution - Fixed costs)	0