Question

Eastman Publishing Company is considering publishing an electronic textbook on spreadsheet applications for business. The fixed cost of manuscript preparation, textbook design, and Website 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. Eastman has created a predictive model that estimates demand as a function of price. The predictive model is demand = 4000-6*p where p is the price of the e-book.

Build a spreadsheet model to calculate the profit/loss for a given price.

Use goal seek to compute the price that results in break even

Use a data table that varies price from $50 to $400 increment of $25 to find the price that maximizes profit.

 

Answer 1

Formula sheet

A	B	C	D	E	F
2
3		Price of E book	46
4
5		Demand for ebook	=4000-6*p
6			=4000-6*D3	=4000-6*D3
7
8		Revenue	=Demand*Price
9			=D6*D3	=D6*D3
10
11		Variable cost per Book	6
12
13		Total Variable Cost	=Demand*Variable Cost per book
14			=D6*D11	=D6*D11
15
16		Fixed cost	160000
17
18		Total cost	=Variable Cost + Fixed Cost
19			=D14+D16	=D14+D16
20
21		Total Profit	=Revenue - Total Cost
22			=D9-D19
23
24		Data Table
25
26		=D22	=D11
27		50	=TABLE(D11,D3)
28		=C27+25	=TABLE(D11,D3)
29		=C28+25	=TABLE(D11,D3)
30		=C29+25	=TABLE(D11,D3)
31		=C30+25	=TABLE(D11,D3)
32		=C31+25	=TABLE(D11,D3)
33		=C32+25	=TABLE(D11,D3)
34		=C33+25	=TABLE(D11,D3)
35		=C34+25	=TABLE(D11,D3)
36		=C35+25	=TABLE(D11,D3)
37		=C36+25	=TABLE(D11,D3)
38		=C37+25	=TABLE(D11,D3)
39		=C38+25	=TABLE(D11,D3)
40		=C39+25	=TABLE(D11,D3)
41		=C40+25	=TABLE(D11,D3)
42
43		Maximum Profit	=MAX(D27:D41)	=MAX(D27:D41)
44		Price at which maximum profit occur	=INDEX(C27:C41,MATCH(D43,D27:D41,0))	=INDEX(C27:C41,MATCH(D43,D27:D41,0))
45
46		Hence Price at which maximum profit occur	=D44
47