Your company is going to purchase new equipment with a price of $500,000, which the manufacturer is willing to finance, and you are trying to work out a payment schedule. Due to cash flow needs elsewhere in your company, your payment budget per month is $12,000. After 36 months, you have the ability to add a balloon payment of up to $50,000; however, the manufacturer will only allow a balloon payment with your last monthly payment. They will allow you to make smaller additional principal payments throughout the life of the loan, but they must be the same amount each month (other than with the last payment, when you can make the large balloon payment). You have the choice to finance for 36, 48, or 60 months, at an annual interest rate of 5%. But you want to pay off the loan as quickly as possible. What length of financing do you choose? What is the normal monthly required payment? Do you pay any extra per month, and if so, how much? What is the earliest month in which you can pay off the loan (meaning, in which month does the final payment occur)? What is the amount of the balloon payment? What is the total interest paid? Create the full amortization schedule (including any additional payment, such as the balloon payment). Stop the schedule with the month that has a beginning balance of zero, and show only the beginning balance for that month on the schedule (meaning, dont show payments for that month).

Your company also needs you to analyze the impact of a new project that will cause your cash flows to increase $5,500 over last years, and continue to grow at a constant rate of 10% per year for the foreseeable future. The discount rate is 20%. You need analyze this in three ways. First, calculate the NPV and IRR of this new project based on an initial investment cost of $42,000 and the change in cash flows each year, assuming the growth continues forever. Second, find the NPV of this project if the project only generates cash flows for 20 years. Third, use an embedded function in Excel to calculate the NPV of the project if the cash flows had zero growth, and the project only generates cash flows for 40 years.

Next, your company is considering going public, and wants an idea of what its stock price should be. You need to forecast out the companys financial statements for the next five years. Also create two FCF/DCF analyses, one using the perpetuity method and one using the EMM method, to calculate the stock price for the company (the EMM sheet is started for you, but you must create the perpetuity sheet). Make sure to include option dilution, the implied perpetuity growth rate (for the EMM sheet), and the implied EBITDA Exit Multiple (for the perpetuity sheet). The discount rate is again 20%.

Lastly, create a sensitivity analysis using a one-way data table on both the perpetuity DCF analysis and the EMM DCF analysis sheets. For the perpetuity sheet, create the data table to determine the impact of an 18%, 19%, 20%, 21%, or 22% discount rate on the implied stock price. For the EMM sheet, create a data table to determine the impact of an EBITDA multiple of 7.25, 7.5, 8.0, 8.5, or 8.75 on the implied stock price.

All information not provided here is included in the starting workbook for the project, which you should use to create your entire analysis. Remember, do not copy/paste anything from another workbook. Everything for this project must be done from scratch and on an individual basis (no working in together). You can ask me for clarification if you need it, or if you think I forgot a piece of information.