Assignment Learning Objectives:

(a) Develop a proforma project income statement using an Excel spreadsheet

(b) Compute net project cash flows, NPV, IRR and Payback period

(c) Perform sensitivity and scenario analyses

(d) Develop problem-solving and critical thinking skills

Assignment Instructions:

There are 3 parts to this assignment, which combines using Excel to perform calculations and answering the eight questions on the "Additional Questions" tab. The use of formulas and cell references (rather than hard typing in numbers) should be used in all cases where they can. To guide you, the cells shaded gray are where formulas, numerical inputs or calcuations are needed in order to complete the assignment.

In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial details are as follows:

1) Useful life of the equipment (years)

4

2) New equipment cost

$ 240,000

5) Inventory increase

$ 25,000

6) Accounts payable increase

$ 5,000

7) Equipment salvage value (end of life)

$ 15,000

8) Projected sales for year 1

$ 200,000

9) Projected sales growth per year

Low Case

2%

Base Case

5%

High Case

8%

10) Operating cost (as a % of sales)

60%

11) Depreciation (straight line) per year

12) Corporate marginal tax rate (T)

21%

13) Cost of capital or discount rate (r)

10%

PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue growth. Depreciation is straight line over the useful life of the equipment. Solve for the NPV, IRR and Payback period. Round off NPV to the nearest dollar, IRR to 1 decimal place and Payback to 2 decimals.

CF(0) = Cash Flow at Time 0

CF(0)

CF(1)

CF(2)

CF(3)

CF(4)

Year

0

1

2

3

4

Investments:

New equipment cost

Net Working Capital needs at start

Total Initial Outlay

$ -

$ -

$ -

$ -

Operations:

Revenue

Operating Cost

Depreciation

EBIT

Taxes

Net Income

Profit Margin (%)

Add back Depreciation

Total Operating Cash Flow

Terminal Cash Flows (end of Year 4)

Change in net WC

Salvage value (after tax) [= Salvage value before tax * (1-T)]

Total

Project Net Cash Flows

NPV (Base Case) =

IRR =

Payback =