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 8 months agoRelated 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 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 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 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
ADVERTISEMENT
Latest Questions
- Compare the MOSFET, JFET , and BJT and identify the reasons these devices behave differently. What...
- How would you use SAS Enterprise Miner to create a neural network model of your example?
- Explain the use of neural networking modeling in predictive analytics.
- Current Market Mix of Netflix, as detail as possible, please.
- This is all one question, thank you so much in advance! You must evaluate a proposal...
- X Company is considering the purchase of a new processor that costs $200,000. Shipping and setup...
- There are a large number of tasks involved in information technology and programming, and many of...
ADVERTISEMENT