Assumptions
The depreciation rate as used in the table is provided by the American house of Congress.
Tax rate incurred on a new project as per IRS rates is 50% as of 2017
If the company owns assets that could otherwise be sold in case the project is rejected, these will appear in the table as opportunity cost
No sinking fund incurred as no expenditure has taken place of yet
In case cash flow reduces in one of the departments in the company, it will reflect as cannibalization in the table
The straight line depreciation uses a steady value of 25%
Analysis of DCF
Since the net present value is greater than zero, the project should be accepted. If the value of NPV was a negative value, then the project would have been cancelled. Since the payback period for the project is lesser than its actual time of consideration for computational purposes (i.e. 5 years) then the project should be pushed forward. The net cash flows for the project increase steadily year after year and hence it is a viable project. The company intends to part with $5million for the piece of land, while the lands broker valuates the land at $20 million, therefore, it is good practice to reopen the floor for discussion because the financial stability of this company is well above par.
Formulas
E7: 700000*1.2^E2 – this implies that the number of units produced improve every year
E11 = E7:E8 – sales revenue equals units times the sales price
E12: unit sales E7 times cost per unit E10
E14 and E27: Depreciation is given using the accelerated method whereby depreciation for the current year is given as rate times net working capital
E15: total operating cost is given by sum of variable, fixed costs and depreciation.
E16: EBIT is sales revenue minus operating cost
E17: rate times operating income
E19: this is equal to depreciation