Question

Please explain how problems solved with Excel steps shown/displayed.

2. SHELFISH 1: As the finance manager of Shelfish Incorporated, you are considering purchasing a new piece of equipment for which the company estimates: • Equipment Cost $290,000 with useful life of 7 years; straight line depreciation to a salvage value of $10,000 with 2 year convention for depreciation • Equipment purpose: to produce 10,000 shelves per year for 5 years and expects to sell all of them Equipment annual maintenance expenses of $10,000 per year • Shelf sale price $25 each; Variable Costs per shelf: $15 • Working Capital requirements (balance) for the project are as follows: * Year 0 = $10,000 Q? Year 1 = $15,000 * Year 2 = $17,000 * Year 3 = $15,000 @ Year 4 = $10,000 At the end of the five-year period, the project will end. The company will be able to sell the equipment for $50,000. The company has a 35% marginal tax rate and a required rate of return of 15%. What is the NPV of this project? Should you undertake it? 3. SHELFISH 2: Repeat SHELFISH 1, but change the following: Depreciate over 7 years using MACRS (the salvage value is not considered when using this method). Depreciation rates are as follows: o Year 1: 14.29% o Year 2: 24.49% o Year 3: 17.49% o Year 4: 12.49% o Year 5: 8.93% o Year 6: 8.92% o Year 7: 8.93% o Year 8: 4.46% 4. SHELFISH 3: Repeat SHELFISH 1, but change the following: • Marginal Tax Rate of 21% 5. SHELFISH 4: Repeat SHELFISH 1, but change the following: Depreciate over 7 years using MACRS (the salvage value is not considered when using this method). • Marginal Tax Rate of 21%

Answer 1

Q1.

Revenue FC VC Depriciation 250000 10000 150000 40000 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 -290000 NPV $ 33,316.80 Component Cost Revenue FC 250000 -10000 - 150000 - 40000 250000 -10000 - 150000 - 40000 VC Deprciation 250000 -10000 - 150000 -40000 250000 -10000 - 150000 - 40000 250000 -10000 -150000 -40000 Operating Income before tax Tax @ 35% 50000 -17500 50000 -17500 50000 -17500 50000 -17500 50000 -17500 Operating Income after tax Adding back depriciation Working Capital Outflow 32500 40000 - 15000 32500 40000 -17000 32500 40000 - 15000 32500 40000 -10000 32500 40000 -10000 MV of equipment sold BV of equipment sold Tax Savings Working Capital Inflow Cash Flows 50000 90000 14000 67000 203500 -300000 57500 55500 57500 62500

Q2.

Revenue FC 250000 10000 150000 VC Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 -290000 NPV $-30,353.25 Component Cost Revenue FC 250000 -10000 - 150000 -40012 250000 -10000 - 150000 -68572 250000 -10000 - 150000 -48972 250000 -10000 - 150000 -34972 VC Deprciation 250000 -10000 - 150000 -25004 Operating Income before tax Tax @ 35% 49988 -17495.8 21428 -7499.8) 41028 -14359.8 55028 -19259.8 64996 -22748.6 Operating Income after tax Adding back depriciation Working Capital Outflow 32492.2 40012 - 15000 13928.2 68572 -17000 26668.2 48972 - 15000 35768.2 34972 -10000 42247.4 25004 -10000 MV of equipment sold BV of equipment sold Tax Savings Working Capital Inflow Cash Flows 50000 72468 7863.8 67000 192115.2 -300000 57504.2 65500.2 60640.2 60740.2

Q3.

With a tax rate of 21%

Revenue FC VC Depriciation 250000 10000 150000 40000 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 -290000 NPV $ -12,636.00 Component Cost Revenue FC 250000 -10000 - 150000 - 40000 250000 -10000 - 150000 - 40000 VC Deprciation 250000 -10000 - 150000 -40000 250000 -10000 - 150000 -40000 250000 -10000 -150000 -40000 Operating Income before tax Tax @ 21% 50000 -10500 50000 -10500 50000 -10500 50000 -10500 50000 -10500 Operating Income after tax Adding back depriciation Working Capital Outflow 39500 40000 - 15000 39500 40000 -17000 39500 40000 - 15000 39500 40000 -10000 39500 40000 -10000 MV of equipment sold BV of equipment sold Tax Savings Working Capital Inflow Cash Flows 50000 90000 8400 67000 204900 -300000 64500 62500 64500 69500

Q4.

With a tax rate of 21% as well as 7yr MACRS:

Revenue 250000 10000 150000 VC Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 -290000 NPV $ -10,857.00 Component Cost Revenue FC VC Deprciation 250000 -10000 - 150000 -40012 250000 -10000 - 150000 -68572 250000 -10000 - 150000 -48972 250000 -10000 -150000 -34972 250000 -10000 - 150000 -25004 Operating Income before tax Tax @ 21% 49988 -10497.48 21428 -4499.88 41028 -8615.88 55028 -11555.88 64996 -13649.16 Operating Income after tax Adding back depriciation Working Capital Outflow 39490.52 40012 - 15000 16928.12 68572 -17000 32412.12 48972 - 15000 43472.12 34972 -10000 51346.84 25004 -10000 MV of equipment sold BV of equipment sold Tax Savings Working Capital Inflow Cash Flows 50000 72468 4718.28 67000 198069.12 - 300000 64502.52 68500.12 66384.12 68444.12