Question
In: Finance
For the following project, calculate the undiscounted cash flow. Put result in Excel
For the following project, calculate the undiscounted cash flow. Put result in Excel
|
Year |
0 |
1 |
2 |
3 |
4 |
5 |
|
Unit Price |
- |
$105 |
$110 |
$115 |
$120 |
$125 |
|
Units Sold |
- |
900 |
1000 |
1100 |
1200 |
1300 |
|
Net Sales |
- |
|||||
|
Variable Costs |
- |
$54,000 |
$60,000 |
$66,000 |
$72,000 |
$78,000 |
|
Fixed Costs |
- |
$15,000 |
$15,750 |
$16,538 |
$17,364 |
$18,233 |
|
Depreciation |
- |
$10,000 |
$10,000 |
$10,000 |
$10,000 |
$10,000 |
|
PBIT |
- |
|||||
|
Tax@40% |
- |
|||||
|
NOPAT |
- |
Adjustments
|
Add back Depreciation |
- |
|||||
|
CapEx |
-$100,000 |
- |
- |
- |
- |
- |
|
Salvage value |
- |
- |
- |
- |
- |
$40,000 |
|
Net cash flow |
||||||
|
Cumulative cash flow |
Solutions
Expert Solution
| Year | 0 | 1 | 2 | 3 | 4 | 5 |
| Unit Price | - | $ 105 | $ 110 | $ 115 | $ 120 | $ 125 |
| Units Sold | - | 900 | 1000 | 1100 | 1200 | 1300 |
| Net Sales | - | $ 94,500 | $ 1,10,000 | $ 1,26,500 | $ 1,44,000 | $ 1,62,500 |
| Variable Costs | - | $ 54,000 | $ 60,000 | $ 66,000 | $ 72,000 | $ 78,000 |
| Fixed Costs | - | $ 15,000 | $ 15,750 | $ 16,538 | $ 17,364 | $ 18,233 |
| Depreciation | - | $ 10,000 | $ 10,000 | $ 10,000 | $ 10,000 | $ 10,000 |
| PBIT | - | $ 15,500 | $ 24,250 | $ 33,962 | $ 44,636 | $ 56,267 |
| Tax@40% | - | $ 6,200 | $ 9,700 | $ 13,585 | $ 17,854 | $ 22,507 |
| NOPAT | - | $ 9,300 | $ 14,550 | $ 20,377 | $ 26,782 | $ 33,760 |
| Adjustments | ||||||
| Add back Depreciation | - | $ 10,000 | $ 10,000 | $ 10,000 | $ 10,000 | $ 10,000 |
| CapEx | $ -1,00,000 | $ - | $ - | $ - | $ - | $ - |
| Salvage value | $ - | $ - | $ - | $ - | $ - | $ 40,000 |
| Net cash flow | $ -1,00,000 | $ 19,300 | $ 24,550 | $ 30,377 | $ 36,782 | $ 83,760 |
| Cumulative cash flow | $ -1,00,000 | $ -80,700 | $ -56,150 | $ -25,773 | $ 11,009 | $ 94,769 |
jeff jeffy answered 3 years agoRelated Solutions
Using Excel (if applicable), A company is considering a project that has the following cash flow...
Using Excel (if applicable),
A company is considering a project that has the following cash
flow and WACC data. Note: CF0, CF2, and CF4 are negative Should the
company take on this project if its WACC is 15? Justify your answer
using NPV, IRR, and MIRR.
WACC:
15%
Year
0
1
2
3
4
Cash flows
- $15,000
$25,000
- $2,000
$12,000
- $1,000
calculate the NPV for the following: the project costs $50,000 upfront and cash flow is $13,000...
calculate the NPV for the following: the project costs $50,000
upfront and cash flow is $13,000 per year for the next six years.
Use a 9% discount rate.
Calculate the project cash flow generated for Project A and Project B using the NPV method....
Calculate the project cash flow generated for
Project A and Project B using the NPV method.
Which project would you select, and why?
Which project would you select under the payback method? The
discount rate is 10% for both projects.
Use Microsoft® Excel® to prepare your
answer.
Note that a similar problem is in the textbook in Section
5.1.
Sample Template for Project A and Project B:
“Table showing investments and returns for Project A and
Project B. Project A...
Calculate the project cash flow generated for Project A and Project B using the NPV method...
Calculate the project cash flow generated for Project A and
Project B using the NPV method (Show your calculations): . Project
A: Invest $10k today and receive $5k at the end of this year and
for another 2 years (in 12 mos, in 24 mos, in 36 mos), for total
cash inflows of $15k. Project B: Invest $55k today and receive $20k
at the end of this year and for another 2 years (in 12 mos, in 24
mos, in...
Forecasting Cash Flow and Burn Rate Create a Cash Flow Forecast on Excel using the following...
Forecasting Cash Flow and Burn Rate
Create a Cash Flow Forecast on Excel using the following
assumptions:
Forecast duration: Years 0 through 5, then Exit
Unit Sales: Sell 2000 units your first year and increase 30% per
year
Price: $100/unit first year and increase 5% per year
COGS: Calculate based on a 75% Gross Profit Margin
NOTE: to complete the Operating Expense section, break it into
two lines: Payroll and Other
Payroll: Start with 2 employees in year 0 paid...
Calculate the following project, at cost of capital of 10% Years Cash Flow (RM) 0 -10,000...
Calculate the following project, at cost of capital of 10%
Years
Cash Flow (RM)
0
-10,000
1
2000
2
2000
3
4000
4
4000
5
5000
i) calculate NPV (8marks)
ii) calculate PI
iii) Justified should we accept or reject the project?
You are considering a project with the following cash flows: YEAR PROJECT CASH FLOW ...
You are considering a project with the following cash
flows:
YEAR
PROJECT CASH FLOW
0
-60,000
1
25,000
2
25,000
3
25,000
4
25,000
If the appropriate discount rate is 11 percent, what is the
project's discounted payback period in years?
Consider the following two mutually exclusive projects: Year Cash Flow (Project I) Cash Flow (Project II)...
Consider the following two mutually exclusive projects:
Year
Cash Flow (Project I)
Cash Flow (Project II)
0
-$12,300
-$44,000
1
$1,800
$14,000
2
$6,000
$30,000
3
$2,000
$5,000
4
$5,000
$10,000
5
$7,000
$5,000
The required return is 10% for both projects. Assume that the
internal rate of return (IRR) of Project I and Project II is 18%
and 15%, respectively.
a) Which project will you choose if you apply the NPV criterion?
Why?
b) Which project will you choose...
Another utilization of cash flow analysis is setting the bid price on a project. To calculate...
Another utilization of cash flow analysis is setting the bid
price on a project. To calculate the bid price, we set the project
NPV equal to zero and find the required price. Thus the bid price
represents a financial break-even level for the project. Guthrie
Enterprises needs someone to supply it with 146,000 cartons of
machine screws per year to support its manufacturing needs over the
next five years, and you’ve decided to bid on the contract. It will
cost...
Another utilization of cash flow analysis is setting the bid price on a project. To calculate...
Another utilization of cash flow analysis is setting the bid
price on a project. To calculate the bid price, we set the project
NPV equal to zero and find the required price. Thus the bid price
represents a financial break-even level for the project. Guthrie
Enterprises needs someone to supply it with 146,000 cartons of
machine screws per year to support its manufacturing needs over the
next five years, and you’ve decided to bid on the contract. It will
cost...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- QUESTION 4 Discuss the sanctity of life versus the quality of life debate as presented by...
- 0.560 g He, 20.482 g F2, and 13.960 g Ar are placed in a 18.4-L container...
- Let X ∼ Pois(4), Y ∼ Pois(12), U ∼ Pois(3) be independent random variables. a) What...
- which one is more nucleophilic: hydroxide ion (OH) , or ammonia (NH3)? Explain why
- A sample mixture of ethyl benzoate (bp 212 C) and dodecane (bp 216.2 C) is injected...
- Consider the markets for fossil fuels (oil, coal, natural gas) and alternative energy sources (solar, wind,...
- Which notion from below best explains people's decision-making process from an economic perspective? People's behavior reflects...
ADVERTISEMENT