In: Accounting
1. Understand how to use EXCEL Spreadsheet | |||||||
(a) Develop proforma Income Statement Using Excel Spreadsheet | |||||||
(b) Compute Net Project Cashflows, NPV, and IRR | |||||||
(c) Develop problem-solving and critical thinking skills | |||||||
and make long-term investment decisions | |||||||
1) Life Period of the Equipment = 4 years | 8) Sales for first year (1) | $200,000 | |||||
2) New equipment cost | $(200,000) | 9) Sales increase per year | 5% | ||||
3) Equipment ship & install cost | $(35,000) | 10) Operating cost (60% of Sales) | $(120,000) | ||||
4) Related start up cost | $(5,000) | (as a percent of sales in Year 1) | -60% | ||||
5) Inventory increase | $25,000 | 11) Depreciation (Straight Line)/YR | $(60,000) | ||||
6) Accounts Payable increase | $5,000 | 12) Marginal Corporate Tax Rate (T) | 21% | ||||
7) Equip. salvage value before tax | $15,000 | 13) Cost of Capital (Discount Rate) | 10% | ||||
ESTIMATING Initial Outlay (Cash Flow, CFo, T= 0) | |||||||
CF0 | CF1 | CF2 | CF3 | CF4 | |||
Year | 0 | 1 | 2 | 3 | 4 | ||
Investments: | |||||||
1) Equipment cost | |||||||
2) Shipping and Install cost | |||||||
3) Start up expenses | |||||||
Total Basis Cost (1+2+3) | |||||||
4) Net Working Capital | |||||||
Total Initial Outlay | |||||||
Operations: | |||||||
Revenue | |||||||
Operating Cost | |||||||
Depreciation | |||||||
EBIT | |||||||
Taxes | |||||||
Net Income | |||||||
Add back Depreciation | |||||||
Total Operating Cash Flow | XXXXX | XXXXX | XXXXX | XXXXX | |||
Terminal: | |||||||
1) Change in net WC | $- | $- | $- | $20,000 | |||
2) Salvage value (after tax) | Salvage Value Before Tax (1-T) | XXXXX | |||||
Total | XXXXX | ||||||
Project Net Cash Flows | $- | $- | $- | $- | $ | ||
NPV = | IRR = | Payback= | |||||
Q#1 | Would you accept the project based on NPV, IRR? | ||||||
Would you accept the project based on Payback rule if project cut-off | |||||||
is 3 years? | |||||||
Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and | |||||||
Capital Budgeting (Investment ) Decisions | |||||||
(a) | Estimate NPV, IRR and Payback Period of the project if equipment is fully | ||||||
depreciated in first year and tax rate equals to 21%. Would you | |||||||
accept or reject the project? | |||||||
( b) | As a CFO of the firm, which of the above two scenario (a) or (b) | ||||||
would you choose? Why? | |||||||
Q#3 How would you explain to your CEO what NPV means? | |||||||
Q#4 What are advantages and disadvantages of using only Payback method? | |||||||
Q#5 What are advantages and disadvantages of using NPV versus IRR? | |||||||
Q#6 Explain the difference between independent projects and mutually exclusive projects. | |||||||
When you are confronted with Mutually Exclusive Projects and have coflicts | |||||||
with NPV and IRR results, which criterion would you use (NPV or IRR) and why? *****SHOW WORK PLEASE !!!! |
Revenue in year 2 | 210000 | (200000*1.05) | |||||||||
Revenue in year 3 | 220500 | (210000*1.05) | |||||||||
Revenue in year 4 | 231525 | (220500*1.05) | |||||||||
ESTIMATING Initial Outlay (Cash Flow, CFo, T= 0) | |||||||||||
CF0 | CF1 | CF2 | CF3 | CF4 | |||||||
N | Year | 0 | 1 | 2 | 3 | 4 | |||||
Investments: | |||||||||||
1) Equipment cost | ($200,000) | ||||||||||
2) Shipping and Install cost | ($35,000) | ||||||||||
3) Start up expenses | ($5,000) | ||||||||||
Total Basis Cost (1+2+3) | ($240,000) | ||||||||||
4) Net Working Capital(25000-5000) | ($20,000) | ||||||||||
A | Total Initial Outlay | ($260,000) | |||||||||
Operations: | |||||||||||
B | Revenue | $200,000 | $210,000 | $220,500 | $231,525 | ||||||
C=0.6*B | Operating Cost | $120,000 | $126,000 | $132,300 | $138,915 | ||||||
D=(240000/4) | Depreciation | $60,000 | $60,000 | $60,000 | $60,000 | ||||||
E=B-C-D | EBIT | $20,000 | $24,000 | $28,200 | $32,610 | ||||||
F=E*0.21 | Taxes | $4,200 | $5,040 | $5,922 | $6,848 | ||||||
G=E-F | Net Income | $15,800 | $18,960 | $22,278 | $25,762 | ||||||
D | Add back Depreciation | $60,000 | $60,000 | $60,000 | $60,000 | ||||||
H=G+D | Total Operating Cash Flow | $75,800 | $78,960 | $82,278 | $85,762 | ||||||
Terminal: | |||||||||||
I | 1) Change in net WC | $- | $- | $- | $20,000 | ||||||
J=15000*(1-0.21) | 2) Salvage value (after tax) | Salvage Value Before Tax (1-T) | $ 11,850 | ||||||||
K=H+I+J | Total | $75,800 | $78,960 | $82,278 | $117,612 | ||||||
L=A+K | Project Net Cash Flows | ($260,000) | $75,800 | $78,960 | $82,278 | $117,612 | |||||
Cumulative Cash Flow | ($260,000) | ($184,200) | ($105,240) | ($22,962) | $94,650 | ||||||
Present Value (PV) of Cash Flow: | |||||||||||
(Cash Flow)/((1+i)^N) | |||||||||||
i=Discount Rate=10%=0.1 | |||||||||||
N=Year of Cash Flow | |||||||||||
SUM | |||||||||||
PV=L/(1.1^N) | Present Value of Cash Flow | $ (260,000) | $ 68,909 | $ 65,256 | $ 61,817 | $ 80,331 | $ 16,312 | ||||
NPV | Net Present Value=Sum of PV of cash flows | $ 16,312 | |||||||||
Payback Period= Period at which cumulative cash flow=Zero | |||||||||||
Payback Period | 3.20 | Years | (3+(22962/117612) | ||||||||
IRR | Internal Rate of Return | 12.67% | (Using IRR function of excel over the Projected Net Cash Flows) | ||||||||
Based on NPV, Project is accepted | NPV is Positive | ||||||||||
Based on IRR, Project is accepted | IRR is higher than the cost of capital | ||||||||||
Project is not acceptable if required payback period is 3 years | |||||||||||