Question
Instruction Create the pro forma income statement and estimate the cash flows for the following project. Decide whether to accept this project based on analysis
Instruction
Create the pro forma income statement and estimate the cash flows for the following project. Decide whether to accept this project based on 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:
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 |
|
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started