In: Finance
PLEASE POST IN 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 POST IN EXCEL WITH SOLUTIONS!
Operating cash flow (OCF) each year = income after tax + depreciation
profit on sale of equipment at end of year 4 = sale price - book value
book value is zero as the equipment is fully depreciated
after-tax salvage value = salvage value - tax on profit on sale of equipment
First, we assume a bid price of $90.00 and calculate the NPV.
NPV is calculated using NPV function in Excel
NPV is -$354,196
NPV is -$354,196
Next, we use GoalSeek in Excel to calculate the bid price at which NPV equals $100,000
The bid price at which NPV equals $100,000 is $99.13
The bid price at which NPV equals $100,000 is $99.13
The bid price at which NPV equals $100,000 is $99.13
The price (per textbook) you should bid on the commitment is $99.13