Question

In: Accounting

Based on the inputs below prepare a capital budget analysis for this Base Case using the...

Based on the inputs below prepare a capital budget analysis for this Base Case using the Net Present Value, Internal Rate of Return, Profitability Index and Payback in years methods, determining whether the project is feasible. Please show your spreadsheet calculations and your final determinations of “go” or “no go” on the project.

Project Inputs:

WACC – Debt is 70% and Equity is 30% of this firm’s capital structure. Interest rate on the debt is 7.5%, firm’s tax rate is 22%. Firm’s beta is 1.50, Risk Free Rate is 3.0%, Market Return Rate is 9.0%.

Project Investment Outlay, Year 0 - $1,000,000

Project Investment Life – 10 years

Project Depreciation - $100,000 / year

Project Salvage Value - $30,000

Working Capital Base of Annual Sales – 10%

Expected inflation rate per year – 3.0%

Project Tax Rate – 30%

Units sold per year – 40,000

Selling Price per Unit, Year 1 - $40.00

Fixed operating costs per year excluding depreciation - $175,000

Manufacturing (Variable) costs per unit, Year 1 - $30.00

Solutions

Expert Solution

Year 0 1 2 3 4 5 6 7 8 9 10
1.Units sold/yr. 40000 40000 40000 40000 40000 40000 40000 40000 40000 40000
2.S.P./unit 40 41.2 42.436 43.70908 45.02035 46.37096 47.76209 49.19495 50.6708 52.1909
3.Total sales value(1*2) 1600000 1648000 1697440 1748363 1800814 1854839 1910484 1967798 2026832 2087637
Less:
4.Fixed opg. Costs/yr. -175000 -180250 -185658 -191227 -196964 -202873 -208959 -215228 -221685 -228335
5.Variable costs(30*40000) -1200000 -1236000 -1273080 -1311272 -1350611 -1391129 -1432863 -1475849 -1520124 -1565728
6.Depn. -100000 -100000 -100000 -100000 -100000 -100000 -100000 -100000 -100000 -100000
7.EBT(3+4+5+6) 125000 131750 138702.5 145863.6 153239.5 160836.7 168661.8 176721.6 185023.3 193574
8.Tax at 30%*EBT(7*30%) -37500 -39525 -41610.8 -43759.1 -45971.8 -48251 -50598.5 -53016.5 -55507 -58072.2
9.EAT(7-8) 87500 92225 97091.75 102104.5 107267.6 112585.7 118063.2 123705.1 129516.3 135502
10.Add Back :depn.(row 6) 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000
11.Operating cash flows(9+10) 187500 192225 197091.8 202104.5 207267.6 212585.7 218063.2 223705.1 229516.3 235502
CAPEX & WC cash flows
12.Investment outlay -1000000
13.After-tax salvage(30000*(1-30%)) 21000
14.Beginning W/C 0 160000 164800 169744 174836.3 180081.4 185483.9 191048.4 196779.8 202683.2 208764
15.Ending W/c(10%*Sales) 160000 164800 169744 174836.3 180081.4 185483.9 191048.4 196779.8 202683.2 208763.7 0
16.Change in w/c -160000 -4800 -4944 -5092.32 -5245.09 -5402.44 -5564.52 -5731.45 -5903.39 -6080.5 208764
17. Total CAPEX & WC cash flows(12+13+16) -1160000 -4800 -4944 -5092.32 -5245.09 -5402.44 -5564.52 -5731.45 -5903.39 -6080.5 229764
18. Total annual cash flows(11+17) -1160000 182700 187281 191999.4 196859.4 201865.2 207021.2 212331.8 217801.7 223435.8 465265
19.PV F at 7.7%(1/1.077^No.of yr.) 1 0.92851 0.86212 0.80048 0.74325 0.69012 0.64078 0.59496 0.55243 0.51293 0.47626
20.PV at 7.7%(18*19) -1160000 169637.9 161459 153692.6 146316.5 139310.2 132654 126329.6 120319.4 114607.1 221587
21. NPV(Sum of Row 20) 325913.1
-160000 -4456.82 -4262.33 -4076.32 -3898.43 -3728.31 -3565.6 -3410 -3261.19 -3118.87 99425.5
22.Profitability Index= 1+(NPV/Initial Investment) -94352.3
1+(325913/1000000)=
1.33
23.IRR(of row 18) 13%
24.Pay back in Yrs.
18. Total annual cash flows(11+17) -1160000 182700 187281 191999 196859 201865 207021 212332 217802 223436 465265
Cumulative cash flows -1160000 -977300 -790019 -598020 -401160 -199295 7726 220058 437860 661296 1126561
Payback period=
5+(199295/207021)=
5.96
Years
Summary
NPV 325913
PI 1.33
IRR 13%
Pay back in Yrs. 5.96
The project is highly feasible & hence recommended as its NPV is POSITIVE & PI > 1 , IRR> WACC,7.7%,& pays back with-in 6 years.

ekkarill92 answered 2 months ago
Next > < Previous

Related Solutions

Based on the inputs below prepare a capital budget analysis for this Base Case using the...
Based on the inputs below prepare a capital budget analysis for this Base Case using the Net Present Value, Internal Rate of Return, Profitability Index and Payback in years methods, determining whether the project is feasible. Please show your spreadsheet calculations and your final determinations of “go” or “no go” on the project. Use your Investment Return Analysis as an example for this capital budget analysis. PLEASE show formulas Project Inputs: WACC – Debt is 70% and Equity is 30%...
WACC & Capital Budget Analysis Based on the inputs below prepare a capital budget analysis for...
WACC & Capital Budget Analysis Based on the inputs below prepare a capital budget analysis for this Base Case using the Net Present Value, Internal Rate of Return, Profitability Index and Payback in years methods, determining whether the project is feasible. Please show your spreadsheet calculations and your final determinations of “go” or “no go” on the project. Project Inputs: WACC – Debt is 70% and Equity is 30% of this firm’s capital structure. Interest rate on the debt is...
WACC & Capital Budget Analysis Based on the inputs below prepare a capital budget analysis for...
WACC & Capital Budget Analysis Based on the inputs below prepare a capital budget analysis for this Base Case using the Net Present Value, Internal Rate of Return, Profitability Index and Payback in years methods, determining whether the project is feasible. Please show your spreadsheet calculations and your final determinations of “go” or “no go” on the project. Use your Investment Return Analysis as an example for this capital budget analysis. Project Inputs: WACC – Debt is 70% and Equity...
Question #1: WACC & Capital Budget Analysis – Based on the inputs below prepare a capital...
Question #1: WACC & Capital Budget Analysis – Based on the inputs below prepare a capital budget analysis for this Base Case using the Net Present Value, Internal Rate of Return, Profitability Index and Payback in years methods, determining whether the project is feasible. Please show your spreadsheet calculations and your final determinations of “go” or “no go” on the project. Use your Investment Return Analysis as an example for this capital budget analysis. Project Inputs: WACC – Debt is...
Capital Budgeting Analysis - Use the information below to prepare for cash flow analysis in a...
Capital Budgeting Analysis - Use the information below to prepare for cash flow analysis in a table for both scenarios Please create your table from Colume L and keep this statement here as it is. You must show the analysis and results from both scenarios based on your cash flow analysis table and make your decision Decision without data support will be given 0 points. Micro-Technologies is a Bio Tech research firm that is conducting research on a cure for...
Prepare a horizontal analysis of the balance sheet data for Nike, using 2016 as a base.
Exercise 9-5 Suppose the comparative balance sheets of Nike, Inc. are presented here. Prepare a horizontal analysis of the balance sheet data for Nike, using 2016 as a base. (If amount and percentage are a decrease show the numbers as negative, e.g. -55,000, -20% or (55,000), (20%). Round percentages to 1 decimal place, e.g. 12.2%)
Prepare a horizontal analysis of the segment data using the prior year as the base year....
Prepare a horizontal analysis of the segment data using the prior year as the base year. Round all percents to one decimal place. Enter all amounts in millions. If required, use minus sign to indicate the decreasing values. Starbucks Corporation Horizontal Analysis Recent Year (in millions) Prior Year (in millions) Increase (Decrease) Amount Increase (Decrease) Percent Americas $8,887 $7,648 $ % China/Asia Pacific 2,396 1,130 Channel Development 1,731 1,546 EMEA 1,217 1,295 Other 1,924 1,606 Total revenues $16,155 $13,225 $...
Prepare a horizontal analysis of the balance sheet data for Nike, using 2019 as a base....
Prepare a horizontal analysis of the balance sheet data for Nike, using 2019 as a base. (Show the amount of increase or decrease as well.) b. Prepare a vertical analysis of the balance sheet data for Nike for 2020. Prepare horizontal and vertical analyses. E13.6 (LO 2) Here are the comparative income statements of Delaney Corporation. Delaney Corporation Comparative Income Statements For the Years Ended December 31      2020      2019 Net sales      $598,000      $500,000 Cost of goods...
Prepare a cash budget (using info from the Sales Budget and purchases budget shown below) for...
Prepare a cash budget (using info from the Sales Budget and purchases budget shown below) for January through March and for the first quarter in total. The company maintains a minimum cash balance of $50,000.00, and this was the balance in the cash account on January 1. Past experience shows that 30% of sales are collected in the month of the sale, and 70% in the month following the sale. Labor cost is $15 per unit. Other expenses include $37,000...
Prepare a horizontal analysis of the income statement data for Delaney Corporation, using 2016 as a base.
*Exercise 9-6 Here are the comparative income statements of Delaney Corporation, DELANEY CORPORATION Comparative Income Statements For the Years Ended December 31 20172016Net sales   $598,000$500,000Cost of goods sold  477,000420,000Gross Profit  121,00080,000Operating expenses  80,00044,000Net income  $ 41,000$36,000   Prepare a horizontal analysis of the income statement data for Delaney Corporation, using 2016 as a base. (If amount and percentage are a decrease show the numbers as negative, e.g. -55, 000, -20% or (55,000), (20%). Round percentages to 1 decimal place, e.g. 12.1%.) Prepare a vertical analysis of the income statement...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT