1. You-Bee Enterprises, Inc. is considering a new three-year expansion project with the relevant cash flows below.

Calculate the projects NPV and IRR. Should You-Bee accept or reject this project based on your calculations? Please use the Excel template provided to answer this problem.

• The required return is 12%.
• The tax rate is 21%.
• The project requires an initial fixed asset investment of $2.32 million, and the fixed asset will have a market value of $180,000 at the end of the project (salvage value). The fixed asset falls into the three-year MACRS class. [This is the fixed asset part of the cash flow.]
• The project is estimated to generate $1.735 million in annual sales, with costs of $650,000. [This is the OCF part of cash flow.]
• The project requires an initial investment in net working capital of $250,000, which will be fully recovered at the end of the project. [This is the change in net working capital part of cash flow.]
• Note: the Excel template provides a step-by-step analysis to accepting or rejecting this project. The first spreadsheet calculates the depreciation using MACRS. Then the pro forma income statement calculates the projected net income for the project for the three years of the life of the project. Then the projected cash flows spreadsheet combines all of the relevant cashflow from the three components of project cash flow and calculates the NPV and IRR.

Please fill out the following spreadsheets:

Input the cost of fixed asset into the cell below: Cost of Fixed Asset to be Depreciated = Here in cell D4, enter the cost of the fixed asset. Year MACRS % Depreciation 1 33.33% $ 2 44.45% $ 3 14.81% $ 4 7.41% $ Ending Book Value $ $ $ $ The ending book value in year 3 is different from salvage value. You will need to calculate the after-tax salvage value to get an accurate cash flow for year 3. Enter market value for year 3 here in cell D17. After-tax salvage value Market value at year 3 Difference between market and book Tax Rate After-tax salvage value $ 21% $ Year 1 2 3 Revenue Input the revenue per year here. Input the costs per year here. - Costs - Deprecition EBIT - Taxes (21%) Net Income $ $ $ $ $ $ $ $ $ $ Year 0 1 2. 3 1. Operating Cash Flow = EBIT + Depreciation - Taxes EBIT Deprecition Taxes Operating Cash Flow $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2. Net Working Capital (NWC) Changes in NWC NWC Recovery Total Changes in NWC Here in cell B10, input the initial investment in net working capital. It should be a negative number because it is a cash outflow at the beginning of the project. In cell E11, enter the net working capital that is fully recovered. It should be a positive number because at the end of the project, the amount is fully recovered. $ $ $ $ $ $ Here in cell B15, input the initial fixed cost investment. It is a negative number since it is a cash outflow. 3. Capital Spending Initial outlay Aftertax salvage Total Capital Spending $ $ $ $ $ $ Total project cash flow $ $ $ $ NPV at 12% $ IRR #NUM! Accept or reject project based on NPV? Accept or reject project based on IRR? Input the cost of fixed asset into the cell below: Cost of Fixed Asset to be Depreciated = Here in cell D4, enter the cost of the fixed asset. Year MACRS % Depreciation 1 33.33% $ 2 44.45% $ 3 14.81% $ 4 7.41% $ Ending Book Value $ $ $ $ The ending book value in year 3 is different from salvage value. You will need to calculate the after-tax salvage value to get an accurate cash flow for year 3. Enter market value for year 3 here in cell D17. After-tax salvage value Market value at year 3 Difference between market and book Tax Rate After-tax salvage value $ 21% $ Year 1 2 3 Revenue Input the revenue per year here. Input the costs per year here. - Costs - Deprecition EBIT - Taxes (21%) Net Income $ $ $ $ $ $ $ $ $ $ Year 0 1 2. 3 1. Operating Cash Flow = EBIT + Depreciation - Taxes EBIT Deprecition Taxes Operating Cash Flow $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2. Net Working Capital (NWC) Changes in NWC NWC Recovery Total Changes in NWC Here in cell B10, input the initial investment in net working capital. It should be a negative number because it is a cash outflow at the beginning of the project. In cell E11, enter the net working capital that is fully recovered. It should be a positive number because at the end of the project, the amount is fully recovered. $ $ $ $ $ $ Here in cell B15, input the initial fixed cost investment. It is a negative number since it is a cash outflow. 3. Capital Spending Initial outlay Aftertax salvage Total Capital Spending $ $ $ $ $ $ Total project cash flow $ $ $ $ NPV at 12% $ IRR #NUM! Accept or reject project based on NPV? Accept or reject project based on IRR