Please can you help me?
Not sure what I am doing wrong and could you please fill in the formulas for me so I understand where I am going wrong?
PROBLEM 2-3 Given 5% 100,000 400,000 $ $ Growth rate in EBIT for years 1-5 EBIT (year 1) CAPEX for year o Depreciable life of fixed assets Tax rate New working capital for years 1-5 5 years 30% 20% of EBIT Solution Year 0 $ 1 100,000 $ 30,000 2 105,000 $ 31,500 3 110,250 $ 33,075 4 115,763 $ 34,729 5 121,551 36,465 Note 1: Time line: year 1 means the end of year 1. Year O is the beginning (same as beginning of year 1) Note 2: The Net working capital needs was calculated as "=-(E15-D15)*$C$10" in cell D20. The formula is the same as "=-(E15*$C$10-D15*$C$10)". The first term E15*$C$10 means the NWC needed for year 2, which has to be invested at the beginning of year 2 (the same as end of year 1). The second term D15*$C$10 is the recovered NWC from year 1 operation. Therefore the difference between these two means the addtional NWC (same as net working captial needs) is recorded at end of year 1. Negative sign means it is an expense. The same meaning goes to cells C20 to H20. 80,000 80,000 80,000 80,000 EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1 to 3) Plus: Salvage value of the fixed assets in year 5 80,000 (20,000) (1,000) (1,050) (1,103) (1,158) 24,310 Note 3. Year 0 (cell C20) has formula "=-(D15-C15)*$C$10". It means to begin the project, the firm has to invest 20% of EBIT as NWC and it has to be made at the beginning of year 1 (i.e. year 0). Cell Hz should be postive ber since at the end of year 5 the firm liquidates all of it's investment in net operating working capital. Firm Free Cash Flow (FFCF) 149,000 $ 152,450 $ 156,073 $ 159,876 $ 189,396 $ Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) 80,000 438,000 $ 80000 447,900 $ 80,000 458,295 $ 80,000 469,210 $ 80,000 531,722 $ $ PROBLEM 2-3 Given 5% 100,000 400,000 $ $ Growth rate in EBIT for years 1-5 EBIT (year 1) CAPEX for year o Depreciable life of fixed assets Tax rate New working capital for years 1-5 5 years 30% 20% of EBIT Solution Year 0 $ 1 100,000 $ 30,000 2 105,000 $ 31,500 3 110,250 $ 33,075 4 115,763 $ 34,729 5 121,551 36,465 Note 1: Time line: year 1 means the end of year 1. Year O is the beginning (same as beginning of year 1) Note 2: The Net working capital needs was calculated as "=-(E15-D15)*$C$10" in cell D20. The formula is the same as "=-(E15*$C$10-D15*$C$10)". The first term E15*$C$10 means the NWC needed for year 2, which has to be invested at the beginning of year 2 (the same as end of year 1). The second term D15*$C$10 is the recovered NWC from year 1 operation. Therefore the difference between these two means the addtional NWC (same as net working captial needs) is recorded at end of year 1. Negative sign means it is an expense. The same meaning goes to cells C20 to H20. 80,000 80,000 80,000 80,000 EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1 to 3) Plus: Salvage value of the fixed assets in year 5 80,000 (20,000) (1,000) (1,050) (1,103) (1,158) 24,310 Note 3. Year 0 (cell C20) has formula "=-(D15-C15)*$C$10". It means to begin the project, the firm has to invest 20% of EBIT as NWC and it has to be made at the beginning of year 1 (i.e. year 0). Cell Hz should be postive ber since at the end of year 5 the firm liquidates all of it's investment in net operating working capital. Firm Free Cash Flow (FFCF) 149,000 $ 152,450 $ 156,073 $ 159,876 $ 189,396 $ Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) 80,000 438,000 $ 80000 447,900 $ 80,000 458,295 $ 80,000 469,210 $ 80,000 531,722 $ $
