You may use Microsoft Excel or any other technology to compl

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.

Solution

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