Please complete the form, including the form with formulas, thank you
Instructions: Project #1 Consplete the eash fow projections and find the NPV foe this project. Inirial CAPX are $2000 and depreciated on a 10-year MACRS schedule (the percentass of initil bost allowed for depreciation under the current US. tax system). Net working capial reyuired is 55,000 (just a onetime inerease) over the life of the project. Incremental sales are $22000 por year and incremental operating expenses are $18,000 per year. Assume that plant and equipment is divposed of at the end of year T (is has no resale value). The tax rate is 3R. Ascame a WACC of 93 3. Note that the spreadsheet is set up so that assumptions (things you can change later) go in the green cells, and that the other cells contain formulas hased cel the assumgtions. 1. Assumptions. Fill in the assumptions section first. Par in the tax ratc across the wbole row. Put in the MACRS percentages. (The quickest way to do this as so copy thern from the MACRS schedule, then go to where you want to paste the values, select Paite Specil, click on Values and Transpose, and hit OK.) Enter the WACC and the recale valoe. 2. Balance sheet items. I find the cash flow projectices are casacr if we track two balance shoet ifems, Net Working Capital and Net PPE In the NWC line, etitar the amoont that will be required for the profect in each year. Note that the NWC mas be in place by ithe ced of) Year Q and would be gone by (the end of year 7. Keep in mind that this line is the level of NWC in each year, not the change in NWC. In the Net PPE line, we are going to track the book value of the assets we acquire for this peoject. So in the green cell (Year 09 put the amount of the initial CAPX. In subsoquent years the Net PPE will be Net PPE from the previous year minus depreciatice froen tuar yeat. (Go ahcad and enter this formula even though depreciation isn't entered yec.) 3. Getting EBIT(1- t). Enter sales and expense for Years l-7, Eater depeciution expense for each year, which is the initial value of PPE times the MACRS percentage for that year. Enter the formula for EBIT, which is sales less opcrating expenses and deqeociation. Enter taxes, which is the tax rate times EBIT, and then enter the formala for EBIT(1-4). 1 4. CAPX. In year Q, CAPX is just equal to the initial value of our PPE, so just enter a formula that references that cell. The other thing we want to account for here is selleng PPE at the ent of the project. Put this in the nexs line below CAPX. In Year 7 we get a cash ieflow from siling the equipmens for resule value (RV), and the we also pay takes on the gain over book value (BV). Here the RV is given in the assumptions, and the BV is the value of Net PPE in Year 7.$0 eater a foenula in Yeur 7 under CAPX that reflects: RV (RVBV)t where t is the tax rate. 5. Change in NWC. For the change in NWC (ANWC), for Year Q a jost make it equal to the initial anount of NWC in Year 0 from shove For Years 1-7, the change in NWC is just that year's NWC minus the previous year's NWC 6. Cash Flows. Now we're ready to calculate cash flow each year, and all we do is apply the formula: EBIT(1-t)+Dep.-CAPX- NWC. Enter this formula and copy in across for all years (-7. Then the only change necessary is to also add in the sale of PPE in year 7 is the year 7 cask flows. 7. NPV. Now enter the formula for NPV on the last line. Renember that the NPV function in Excel discounts the first value by coe year, so don't put the inital caah flow inside the NPV function. Add this in scparately. Q1: What is the NPV of the peoject under the given assurngtions? Q2: How low would the initial imestment have to be in ceder to make this project aceeptable
