In: Finance
I would like some explanation on where values come from and not just a spread sheet with answers like the rest of the similar problems). i am trying to create this in excel for future use. Thank you.
Arnold Inc. is considering a proposal to manufacture high-end protein bars used as food supplements by body builders. The project requires use of an existing warehouse, which the firm acquired three years ago for $2 million and which it currently rents out for $112,000 Rental rates are not expected to change going forward. In addition to using the warehouse, the project requires an upfront investment into machines and other equipment of $1.5 million. This investment can be fully depreciated straight-line over the next 10 years for tax purposes. However, Arnold Inc. expects to terminate the project at the end of eight years and to sell the machines and equipment for $598,000. Finally, the project requires an initial investment into net working capital equal to 10 percent of predicted first-year sales. Subsequently, net working capital is 10 percent of the predicted sales over the following year. Sales of protein bars are expected to be $4.9 million in the first year and to stay constant for eight years. Total manufacturing costs and operating expenses (excluding depreciation) are 80 percent of sales, and profits are taxed at 30 percent.
a. What are the free cash flows of the project?
b. If the cost of capital is 15 % what is the NPV of the project?
First We will Calculate After Tax Cash Flow from Sales of the Machine
Initial Investment = $ 1500,000
Useful Life of the Machine = 10 Years
Depreciation each year = Initial Investment / Years = 1500,000 / 10 = 150,000
Book Value of the Machine after 08 Years = Initial Investment - Years * Depreciation each year
= 1500,000 - 8* 150,000 = 300,000.
Sales Value of the Machine after 08 Years = $598,000
Since Sales Value Higher than Book Value, there will be a tax on Sales
Taxable Value = Sales Value -- Book Value = $598,000 - $ 300,000 = $ 298,000
Tax = Taxable Value * Tax Rate = $ 298,000 * 30% = 89,400
After-Tax Cash flow from Sale of the Machine = Sales Value - Tax = $598,000 - $ 89,400 = $ 508,600
Cash Flow
Fixed Cost each Year = $112,000 ( Loss in Rental each Year)
Sales Each Year = $4.9 million = $ 4900,000
manufacturing costs and operating expense each year = 80% of Sales = 80% * $ 4900,000 = 3920,000
Investment in Working Capital
In Initial Year ( Year 0) = 10% of First-Year Sales = 10% * 4900,000 = 490,000
Subsequent Investment in Working Capital = 10% of Projected Next Years Sales = 10% *4900,000 = 490,000
[ Keep in Mind this will continue to Year 07 : In Year 08 is the last Year Project ]
Cash Inflow from Working Capital at end of the Projected = Total Investment in Working Capital = 490,000 * ( No of Years Investment) = 490,000 * 08 = 3920,000
[ 08 Due to considering Year 0 to Till Year 07 ]
free cash flows calculation :
Free Cash flow in Each Year = EBIT - Tax Payment + Depreciation
[ We add back depreciation in Operating Cash flow as it is a non-cash expense. First, we need to subtract depreciation for calculating EBIT and Tax Payment ]
Present Value of Free Flow
Discounting free cash flows = Free Cash flow / ( 1+ r)^Year [ r = 10% ]
NPV
Sum of Present Value of Free Flow = 6,74,683 (Ans)
Ans : Free Cash Flows for the Project : (Ans)
Year : 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
-1890000 | 3,09,600 | 3,09,600 | 3,09,600 | 3,09,600 | 3,09,600 | 3,09,600 | 3,09,600 | 39,05,200 |