E-Book break-even analysis. 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 $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.a. Build a spreadsheet model to calculate the profit/loss for a given demand. What profit can be anticipated with a demand of 3,500 copies?b. Use a data table to vary demand from 1,000 to 6,000 in 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 3,500 copies.d. Consider the following scenarios: Scenario 1Scenario 2Scenario 3Scenario 4Scenario 5Variable Cost/Book$6$8$12$10$11Access Price$46$50$40$50$60Demand2,5001,0006,0005,0002,000 For each of these scenarios, the fixed cost remains $160,000. Use Scenario Manager to generate a summary report that gives the profit for each of these scenarios. Which scenario yields the highest profit? Which scenario yields the lowest profit?

The break-even analysis is an approach to determining the level of output at which the fixed and variable costs associated with the product are equal to the selling price of the same. This approach is used by marketers to know the number of units that should be sold to cover all the costs. In the given case the publisher wishes to know the change in net profit at different levels of demand. Since you have asked for multiple subparts, we have answered the first three subparts for you as per Bartleby guidelines. Given: Fixed Cost = $160,000Variable Cost = $6Selling Price Per Unit = $46Demand= 3500 Units With the help of the given information, the following spreadsheet can be prepared: Total Cost Fixed Cost+Number of Units×Variable Costs160,000+3500×6 181,000 Revenue Selling Price×Number of Units46×3,500 161,000 Net Profit Total Revenue-Total Cost181,000-161,000 -20,000 A negative net profit represents losses. Thus, the publisher will be facing a loss of $20,000 if he sells 3,500 copies.The following data table is prepared to determine the responsiveness of net profit to the change in quantity sold. Fixed Cost Variable Cost Selling Price Number of Copies sold Net Profit 160000 6 46 1000 -120000 160000 6 46 1200 -112000 160000 6 46 1400 -104000 160000 6 46 1600 -96000 160000 6 46 1800 -88000 160000 6 46 2000 -80000 160000 6 46 2200 -72000 160000 6 46 2400 -64000 160000 6 46 2600 -56000 160000 6 46 2800 -48000 160000 6 46 3000 -40000 160000 6 46 3200 -32000 160000 6 46 3400 -24000 160000 6 46 3600 -16000 160000 6 46 3800 -8000 160000 6 46 4000 0 160000 6 46 4200 8000 160000 6 46 4400 16000 160000 6 46 4600 24000 160000 6 46 4800 32000 160000 6 46 5000 40000 160000 6 46 5200 48000 160000 6 46 5400 56000 160000 6 46 5600 64000 160000 6 46 5800 72000 160000 6 46 6000 80000 The formula used to compute Net Profit is: Net Profit=Total Revenue-Total Cost=(Selling Price×Number of Units)-(Variable Cost×Number of Units+Fixed Cost) The following conclusions can be drawn on the basis of the data table: At the given selling price and variable cost the publisher will face losses if he sells less than 4000 copies. However, it can be observed that the amount of loss is decreasing with an increase in the number of units sold. The publisher is required to sell at least 4000 copies to meet the total costs. He will be operating at the break-even point (no profit no loss situation) if exactly 4000 copies are sold. Lastly, the publisher can earn profits if more than 4000 copies are sold. It can be observed that the amount of profit is increasing with an increase in the number of units sold beyond 4000.Use the following steps to use the goal-seek approach: 1. Compute the Total cost by using the formula (B4): Fixed Cost+Variable Cost×Number of Units 2. Compute Total Revenue by using the formula (B7): Selling Price×Number of Units Note: Keep cell B5 blank. 3. Compute Net Profit by inserting the formula (B8): Total Revenue-Total Cost 4. Select Data Tab>What-if Analysis>Goal Seek 5. Insert the following values in the dialogue box that will appear: Set Cell: B8 To Value: 0 By changing the cell: B5 The final table will appear as follows: A B 1 Fixed Cost 1,60,000 2 Variable Cost 6 3 Number of Units 3,500 4 Total Cost 181,000 5 Selling Price 52 6 Number of Units 3,500 7 Total Revenue 181,000 8 Net Profit 0 With the above information, it can be noted that the publisher needs to change the selling price from $46 to $52 to meet the costs and operate at the break-even point.

Answered: E-Book break-even analysis. Eastman…

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

	Scenario 1	Scenario 2	Scenario 3	Scenario 4	Scenario 5
Variable Cost/Book	$6	$8	$12	$10	$11
Access Price	$46	$50	$40	$50	$60
Demand	2,500	1,000	6,000	5,000	2,000