Create the pro forma income statement and estimate the cash flows for the following project. Decide whether to accept this project based on an analysis of the NPV, Profitability Index, and IRR.

Project Assumptions (Base Case)

Equipment Life	6	Years
Initial Equipment Cost	$2,500,000	in year 0
Depreciation	Straight Line	Method
Initial Revenue	$1,000,000	in year 1
Revenue growth rate year 2	10%
Revenue growth rate year 3	15%
Revenue growth rate year 4	10%
Revenue growth rate year 5	5%
Variable Costs	60%	of this year's revenue
Fixed Costs	$75,000	in year 1
Fixed Costs Inflation Rate	3%	per year
Long-term growth rate	2%	per year
Net Working Capital	4%	of next year's revenue
Tax Rate	35%
Discount Rate	18%

Model Structure

Since this project does not have an end date we need to decide how many years of detailed analysis we will conduct. For this assignment, we will estimate detailed cash flows for 5 years and estimate the terminal value at the end of year 5.

The Income Statement is the building block for cash flow estimation. Your income statement should contain the following items. You may include additional items if you find them useful in your model.

Revenue

Variable Cost

Gross Profit

Cash Fixed Cost

Depreciation

EBIT (Earnings Before Interest and Tax)

Tax

Net Income

Cash Flows:

· Operating Cash Flow = EBIT + Depreciation – Taxes

· Other cash flow items

o initial investment

o change in Net Working Capital

o Terminal value

Analyses

1. Scenario Analysis: prepare a scenario summary report. Use the values in the original assumption as the base case and add the following two cases.

Case 1 (worst case)

Variable Costs	65%	of this year's revenue
Fixed Costs	$80,000	in year 1
Fixed Costs Inflation Rate	5%	per year
Long-term growth rate	1%	per year

Case 2 (best case)

Variable Costs	55%	of this year's revenue
Fixed Costs	$70,000	in year 1
Fixed Costs Inflation Rate	3%	per year
Long-term growth rate	3%	per year

2. Sensitivity Analysis: prepare a one-way data table. Make sure to use the base case values. Allow the long-term growth rate to vary from -3.0% to +3.0% in increments of 0.5%. Show the impact on NPV and IRR.

3. Breakeven Analysis: identify the initial revenue level that will result in $0 NPV.

Things to turn in:

A one-page memo explaining the results of your analysis and your recommendation. The memo should include important results of your analysis such as a summary table or graph. The memo is limited to one page so be very selective on what information to include.

An Excel spreadsheet showing the following:

The entire model for the base case

Scenario Analysis (Scenario Summary Report)

Sensitivity Analysis (Data Table)

Breakeven Analysis (Goal Seek result)

Check Figures (Base case):

Model
Year	0	1	5	6
Pro Forma Incremental Income Statement
Revenue		1,000,000	1,461,075	1,490,297
Net Income		(59,583)	54,178
Pro Forma Incremental Balance Sheet
Net Working Capital	40,000	44,000	59,612
Pro Forma Incremental Cash Flows
Total Net AT CF	(2,540,000)	353,083	3,463,856

Answer rating: 100% (QA)

Lets consider the question 1 Year 2 A Project Cost Working Capital Increas... View the full answer