Question

home / study / business / operations management / operations management questions and answers / eastman publishing company is considering publishing an electronic textbook on spreadsheet ...

Question: Eastman Publishing Company is considering publishing an electronic textbook on spreadsheet applic...

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.

d. Consider the following scenarios:

	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	2500	1000	6000	5000	2000

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?

Answer 1

a) Spreadsheet model to calculate the profit/loss for a given demand is following:

FORMULAS:

b)

To create a one variable data table, execute following steps.

1. Select cell I12 and type =B8 (profit)

2. Type the different percentages in column I.

3. Select the range I2:J28.

4. On the Data tab, in the Forecast group, click What-If Analysis.

5. Click Data Table.

6. Click in the 'Column input cell' box (the percentages are in a column) and select cell B4.

7. Click OK

c) Using Goal seek to determine the optimal access price per copy

Enter following values in Goal Seek window

Click OK

The access price for breakeven is = 51.7143

d) Scenario report:

FORMULAS:

	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	2500	1000	6000	5000	2000
Revenue	=B3*B4	=C3*C4	=D3*D4	=E3*E4	=F3*F4
Total variable cost	=B2*B4	=C2*C4	=D2*D4	=E2*E4	=F2*F4
Fixed cost	160000	160000	160000	160000	160000
Profit	=B5-B6-B7	=C5-C6-C7	=D5-D6-D7	=E5-E6-E7	=F5-F6-F7

Scenario 4 yields the highest profit and scenario 2 yields the lowest profit.