Question

PLEASE post USing EXCEL WITH SOLUTIONS!

You’re a publisher of University Mac Books. A teacher at MSU has written a new Finance text book & has asked your firm to prepare & print the text book. She is willing to commit to purchasing 22,000 text books over four years. You are at full production, so you need to purchase new printing equipment at a cost of $3.6Million which will be depreciated on a straight line basis over the life of the project & can be sold for $275,000 at the end of 4 years. Additionally, you will need $130,000 in excess working capital which you can recoup later. Fixed costs are $730,000 and variable costs are $50. While this is a customized version, you believe you can sell the text book to other universities as well and, project those sales at 4,500, 12,500, 15,000, & 7,500 at $150 per unit. Your CFO requires that all projects have an NPV of $100,000. Your firms tax rate is 24% and cost of capital is 13%. What price (per textbook) should you bid on the commitment of 22,000? PLEASE USE EXCEL WITH SOLUTIONS!

Answer 1

Step 1 : Lets first find the present value of all cash-flows except the sales of 22,000 text books

Particulars	Year 0	Year 1	Year 2	Year 3	Year 4	Workings
Sale Units		5,500	5,500	5,500	5,500	(A) = Refer (1) below
Additional books sold		4,500	12,500	15,000	7,500	(B) Given in question
Total books sold		10,000	18,000	20,500	13,000	(C) = (A)+(B)
Selling Price of additional books		150	150	150	150	(D) Given in question
Sales of additional books		675,000	1,875,000	2,250,000	1,125,000	(E) = (B)*(D)
Variable cost per unit		50	50	50	50	(F) Given in question
Total Variable cost		500,000	900,000	1,025,000	650,000	(G) = (F)*(C)
Fixed Cost		730,000	730,000	730,000	730,000	(H) Given in question
Depreciation		900,000	900,000	900,000	900,000	(I) = Refer (2) below
Total Costs		2,130,000	2,530,000	2,655,000	2,280,000	(J)=(G)+(H)+(I)
Net Profit		(1,455,000)	(655,000)	(405,000)	(1,155,000)	(K)=(E) - (J)
Tax rate at 24%		(349,200)	(157,200)	(97,200)	(277,200)	(L)=(K)*24%
Net Profit after tax		(1,105,800)	(497,800)	(307,800)	(877,800)	(M)=(K)-(L)
Add: Depreciation		900,000	900,000	900,000	900,000	(N)=(I)
Cash-flows after tax		(205,800)	402,200	592,200	22,200	(O)=(M)+(N)
Original Investment	3,600,000					(P) Given in question
Salvage value					209,000	(Q) = Refer (3) below
Working Capital	130,000					(R) Given in question
Working Capital recoup					130,000	(S) Given in question
Total Cash-flows	(3,730,000)	(205,800)	402,200	592,200	361,200	(T)=(O)-(P)+(Q)-(R)+(S)
Discounting factor at 13%	1.000	0.885	0.783	0.693	0.613	(U) = Refer (4) below
Present Value of Cash-flows	(3,730,000)	(182,124)	314,982	410,424	221,531	(V) = (T)*(U)
Total Present Value of cash-flow	(2,965,187)					(W) = Total of present value of cash-flows across 4 years

Workings:

1.  Sale Units = 22,000 books to be sold over 4 years. It is assumed it will be sold equally in 4 years. Thus, sale units per annum = 22,000/4=5,500

2.  Depreciation =

Equipment cost = $3,600,000

Life of equipment = 4 years

Depreciation method = Straight line

Thus, Depreciation per annum = $3,600,000 / 4 = $900,000

3. Salvage value

Equipment cost = $3,600,000

Depreciation for 4 years = $900,000 * 4 = $3,600,000

Thus, book value at the end of 4 years = 0 (equipment cost - depreciation for 4 years)

Sale Value at the end of year 4 = $275,000

Profit on sale at the end of year 4 = Sale Value - Book Value = $275,000 - 0 = $275,000

Tax on profit on sale = $275,000*24% = $66,000

Thus, net salvage value at the end of year 4 = Sale Value - Tax on profit on sale = $275,000-$66,000 = $209,000

4. Discounting factor at 13%

Year 1 = 1/(100%+13%)^1

Year 2 = 1/(100%+13%)^2

Year 3 = 1/(100%+13%)^3

Year 4 = 1/(100%+13%)^4

Step 2 : Lets find the present value of for the sales of 22,000 text books

Assume selling price per unit is X. And as per note 1 above, we assume 22,000 books are sold equally every year, thus 5,500 (22,000/4) books are sold every year.

Particulars	Year 1	Year 2	Year 3	Year 4	Workings
Sale Units	5,500	5,500	5,500	5,500	(A)
Sale Price per unit	X	X	X	X	(B)
Sale Value	5,500X	5,500X	5,500X	5,500X	(C)=(A)*(B)
Tax rate at 24%	1,320X	1,320X	1,320X	1,320X	(D) = (C)*24% = (5,500*24%)*X
Net Sales after tax	4,180X	4,180X	4,180X	4,180X	(E)=(C)-(D) = (5,500-1,320)*X
Discounting factor at 13%	0.885	0.783	0.693	0.613	(F) = Refer (4) above
Present Value of Net Sales after tax	3,699X	3,274X	2,897X	2,564X	(G)=(E)*(F)
Total Present Value of Net Sales after tax	12,433X				(H) = Total of present value of sales in (G) across 4 years

Step 3 : Bid price (sale price) for the 22,000 textbooks to be sold over 4 years if NPV is $100,000:

NPV = Total Present Value of cash-flows (as per step 1) + Total Present Value of Net Sales after tax (as per step 2)

$100,000 = -$2,965,187+12,433X =

12,433X = $100,000+$2,965,187

12,433X = $3,065,187

X=$3,065,187/12,433 = $246.54

Thus, 22,000 text books should be sold at $246,54 per book if the NPV equals $100,000.