Please fill out the excel table
The following are assumptions about the data and company:
Net Income 58 Addback Depreciation 59 Cash Impact of Change in Net Working Capital 60 Capital Expenditure (1,540,000) FCF Cash SURPLUS/(DEFICIT) \begin{tabular}{llllll} \hline(1,540,000) & & & & & \\ \hline \end{tabular} Terminal Value Leave Until the End FCF Cash SURPLUS/(DEFICIT) with T.V. (1,540,000) NPV IRR a. How much External Investment they would need and in which years would it be needed? b. Could the business pay any dividends, when and how much? And, c. What is the revised IRR and NPV? The only assumptions he needed to change to his original forecast are: - There will be a 4-month start-up period to get the new equipment installed in the factory and calibrated for production. As a result, they will pay an additional $100K of rental expenses in this start-up period (Time 0). For simplicity, ignore any tax credits/tax loss carryforward in Time 0. - The business would need a Cash Balance (Current Asset) to operate the business and it would be equal to 7% of Revenues. He can consider it just part of Net Working Capital. - Their Accounts receivable would be 30 days of sales (DSO) and stay constant throughout. - In the startup period they would need $35,000 of inventory, but thereafter their Inventory Days (DOH) would stay constant at 60 days. - Their Accounts Payable Days would be 25 days and stay constant throughout. - Instead of using Salvage Value of the Equipment in year 5, he would use a Terminal Value. The assumption he would use is that the FCF in Year 6 would decline by 10% from Year 5 and decline at 10% for every year in perpetuity thereafter. That is: it will be a Growing Perpetuity, but with a negative 10% growth rate. - If the Company has a shortfall (Deficit) in Cash Flow in any given year it would be funded by an External Investor through equity. - If the Company has an excess (Surplus) in Cash Flow in any given year it would pay all the excess cash out as a dividend to shareholders. New Retained Earnings = Old Retained Earnings + New Net Income New Dividends Paid Growing Perpetuity: PVT1=(rg)CT Net Income 58 Addback Depreciation 59 Cash Impact of Change in Net Working Capital 60 Capital Expenditure (1,540,000) FCF Cash SURPLUS/(DEFICIT) \begin{tabular}{llllll} \hline(1,540,000) & & & & & \\ \hline \end{tabular} Terminal Value Leave Until the End FCF Cash SURPLUS/(DEFICIT) with T.V. (1,540,000) NPV IRR a. How much External Investment they would need and in which years would it be needed? b. Could the business pay any dividends, when and how much? And, c. What is the revised IRR and NPV? The only assumptions he needed to change to his original forecast are: - There will be a 4-month start-up period to get the new equipment installed in the factory and calibrated for production. As a result, they will pay an additional $100K of rental expenses in this start-up period (Time 0). For simplicity, ignore any tax credits/tax loss carryforward in Time 0. - The business would need a Cash Balance (Current Asset) to operate the business and it would be equal to 7% of Revenues. He can consider it just part of Net Working Capital. - Their Accounts receivable would be 30 days of sales (DSO) and stay constant throughout. - In the startup period they would need $35,000 of inventory, but thereafter their Inventory Days (DOH) would stay constant at 60 days. - Their Accounts Payable Days would be 25 days and stay constant throughout. - Instead of using Salvage Value of the Equipment in year 5, he would use a Terminal Value. The assumption he would use is that the FCF in Year 6 would decline by 10% from Year 5 and decline at 10% for every year in perpetuity thereafter. That is: it will be a Growing Perpetuity, but with a negative 10% growth rate. - If the Company has a shortfall (Deficit) in Cash Flow in any given year it would be funded by an External Investor through equity. - If the Company has an excess (Surplus) in Cash Flow in any given year it would pay all the excess cash out as a dividend to shareholders. New Retained Earnings = Old Retained Earnings + New Net Income New Dividends Paid Growing Perpetuity: PVT1=(rg)CT
