Eastbay 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 $160,000. Variable processing costs are estimated to be $6 per book. The publisher plans to sell single-user access to the book for $46.

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,500 copies?

Solution and Explanation:

Profit at 3,500 copies

Sales = $ 46 multiply 3,500 161,000

Less: Variable cost = $ 6 multiply 3500 21,000

Contribution 140,000

Less: Fixed Cost 160,000

Net Income (20,000)

Demand Sales = $ 46 per unit Variable cost = $ 6 per unit Contribution Fixed cost = $ 160,000 Net Profit

1,000 46,000 6,000 40,000 160,000 (120,000)

1,200 55,200 7,200 48,000 160,000 (112,000)

1,400 64,400 8,400 56,000 160,000 (104,000)

1,600 73,600 9,600 64,000 160,000 (96,000)

1,800 82,800 10,800 72,000 160,000 (88,000)

2,000 92,000 12,000 80,000 160,000 (80,000)

2,200 101,200 13,200 88,000 160,000 (72,000)

2,400 110,400 14,400 96,000 160,000 (64,000)

2,600 119,600 15,600 104,000 160,000 (56,000)

2,800 128,800 16,800 112,000 160,000 (48,000)

3,000 138,000 18,000 120,000 160,000 (40,000)

3,200 147,200 19,200 128,000 160,000 (32,000)

3,400 156,400 20,400 136,000 160,000 (24,000)

3,600 165,600 21,600 144,000 160,000 (16,000)

3,800 174,800 22,800 152,000 160,000 (8,000) 4,000 184,000 24,000 160,000 160,000 -

4,200 193,200 25,200 168,000 160,000 8,000

4,400 202,400 26,400 176,000 160,000 16,000

4,600 211,600 27,600 184,000 160,000 24,000

4,800 220,800 28,800 192,000 160,000 32,000

5,000 230,000 30,000 200,000 160,000 40,000

5,200 239,200 31,200 208,000 160,000 48,000

5,400 248,400 32,400 216,000 160,00 56,000

5,600 257,600 33,600 224,000 160,000 64,000

5,800 266,800 34,800 232,000 160,000 72,000

6,000 276,000 36,000 240,000 160,000 80,000