Question

In: Accounting

1. Understand how to use EXCEL Spreadsheet (a)  Develop proforma Income Statement Using Excel Spreadsheet (b)  Compute  Net Project...

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 !!!!

Solutions

Expert Solution

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

Related Solutions

1. Learning Objectives (a) Develop proforma Project Income Statement Using Excel Spreadsheet (b) Compute Net Project...
1. Learning Objectives (a) Develop proforma Project Income Statement Using Excel Spreadsheet (b) Compute Net Project Cash flows, NPV, IRR and PayBack Period (c) Develop Problem-Solving and Critical Thinking Skills 1) Life Period of the Equipment = 4 years 8) Sales for first year (1) 200000 2) New equipment cost -200000 9) Sales increase per year 0.05 3) Equipment ship & install cost -35000 10) Operating cost: -120000 4) Related start up cost -5000 (60 Percent of Sales) -0.6 5)...
(a) Develop proforma Income Statement Using Excel Spreadsheet (b) Compute Net Project Cashflows, NPV, and IRR...
(a) Develop proforma Income Statement Using Excel Spreadsheet (b) Compute Net Project Cashflows, NPV, and IRR 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 Use...
(a) Develop proforma Project Income Statement Using Excel Spreadsheet (b) Compute Net Project Cash flows, NPV,...
(a) Develop proforma Project Income Statement Using Excel Spreadsheet (b) Compute Net Project Cash flows, NPV, IRR and PayBack Period (c) Develop Problem-Solving and Critical Thinking Skills 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: $ (120,000) 4) Related start up cost $       (5,000)     (60 Percent of...
Capital Budgeting Decisions Instructor: FINC 33100 Learning Objectives 1. Understand how to use EXCEL Spreadsheet (a)...
Capital Budgeting Decisions Instructor: FINC 33100 Learning Objectives 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)...
Develop a spreadsheet to determine the net present value or present worth of the following project:...
Develop a spreadsheet to determine the net present value or present worth of the following project: Bonus Depreciation: 0% Investment: 140,000 Revenue/Savings: 25,000 Incremental Expense/Cost: 5,000 Salvage Value: 25,000 Project Life: 10 years MACRS Schedule: 7 years Tax Rate: 25% MARR: 12% Inflation: 3% Is this a good investment to make? Rework the problem with Bonus Depreciation of 50% and 100% Determine the internal rate of return for the project in the previous problem with all three levels of Bonus...
1) Use Excel to develop a regression model for the Consumer Food Database (using the “Excel...
1) Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls” file on Blackboard) to predict Annual Food Spending by Annual Household Income for those living in the Metro area only.    Suppose a household in the metro area has an annual income of $60,000. Predict how much they spend on food per year. Write your answer as a number (do not include the $ sign or comma) and round to 2 decimal places....
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed in parts a, b, and d. Be sure to compute the Yield-to-Maturity (YTM) and Yield-to-Call (YTC) for each of years 5, 6, 7, 8, and 9. Kaufman Enterprises has bonds outstanding with a $1,000 face value and 10 years left until maturity. They have an 11% annual coupon payment, and their current price is $1,175. The bonds may be called in 5 years at...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed in parts a, b, and d. Be sure to compute the Yield-to-Maturity (YTM) and Yield-to-Call (YTC) for each of years 5, 6, 7, 8, and 9. Kaufman Enterprises has bonds outstanding with a $1,000 face value and 10 years left until maturity. They have an 11% annual coupon payment, and their current price is $1,185. The bonds may be called in 5 years at...
Using the financial statement provided (an Excel spreadsheet with the data is available on Blackboard), prepare...
Using the financial statement provided (an Excel spreadsheet with the data is available on Blackboard), prepare a complete Statement of Cash Flows that shows both the direct method and the indirect method for summarizing cash flow from operating activities. Note to complete the assignment you will need to calculate items such as dividends paid (Retained Earnings & AOCI2019 – Net income2019 – Retained Earnings & AOCI2018) and capital expenditures (Net PP&E2018 – Book Value of PP&E Sold2019 – Depreciation Expense2019...
Risk Assesment Project 2.Fill in industry comparisons for Macy in the Excel Spreadsheet. Compute missing financial...
Risk Assesment Project 2.Fill in industry comparisons for Macy in the Excel Spreadsheet. Compute missing financial statement ratios for Macy and describe Macy’s profitability, efficiency, liquidity.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT