Financial Modeling
zack1011
Assignment1-CapitalprojectanalysisHomework.xlsxSheet1
| Pakco Corp. - CAPITAL PROJECT ANALYSIS | ||||||||||||
| $ in thousand | ||||||||||||
| Cost of machine | $ 15,500.0 | |||||||||||
| The machine will have no sale/residual value at the end of year 10 | ||||||||||||
| Tax Rate | 25% | |||||||||||
| Required Rate of Return | 12% | |||||||||||
| PERIOD | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |
| Revenue | $ - 0 | |||||||||||
| Revenue Growth Assumptions | n/a | 5.0% | 5.0% | 5.0% | 5.0% | 5.0% | -10.0% | -25.0% | -30.0% | -35.0% | ||
| COGS | ||||||||||||
| COGS as a % of Revenue | 65.0% | 65.0% | 65.0% | 65.0% | 65.0% | 65.0% | 65.0% | 65.0% | 65.0% | 65.0% | ||
| SG&A Expenses | ||||||||||||
| SG&A Expenses as a % of Revenue | 2.5% | 2.5% | 2.5% | 2.5% | 2.5% | 2.5% | 2.5% | 2.5% | 2.5% | 2.5% | ||
| Depreciation Expense | - 0 | - 0 | - 0 | - 0 | ||||||||
| Tax Depreciation MACRS % | 20.0% | 32.0% | 19.0% | 12.0% | 12.0% | 5.0% | ||||||
| Pre-Tax Income from Project | ||||||||||||
| Taxes | ||||||||||||
| After-Tax Income | ||||||||||||
| Add: Depreciation | ||||||||||||
| Cash Flow | ||||||||||||
| Net Present Value | ||||||||||||
| Internal Rate of Return | ||||||||||||
| DATA TABLE | ||||||||||||
| COGS % | ||||||||||||
| NPV | ||||||||||||
| 62.0% | 63.0% | 64.0% | 65.0% | 66.0% | 67.0% | |||||||
| 11.0% | ||||||||||||
| 11.5% | ||||||||||||
| Required Return | 12.0% | |||||||||||
| 12.5% | ||||||||||||
| 13.0% |
Pakco Corp. is a manufacturer of travel bags. It is evaluating entering into the manufacture of a new line of backpacks. This will require the investment in a new manufacturing machine and hiring additional employees. You are a financial analyst in the Financial Planning & Analysis Department. The product development team has supplied you with the below growth rates and margins and it expects year 1 revenues to be $10,000.0 thousand. You are to complete an analysis to determine the net present value and internal rate of return on the project. You are also to complete the below data table for differtent levels of COGS % and required rates of return as outlined. As shown to the left the cost of the machine is $15,500 thousand, Tax rate is 25% and the required rate of return is 12%.
