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

Question

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 75% and Equity is 25% of this firm’s capital structure. Interest rate on the debt is 7.5%, firm’s tax rate is 30%. Firm’s beta is 1.25, Risk Free Rate is 2.0%, Market Return Rate is 11.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

Explanation / Answer

Interest rate on debt = 7.5%. Tax rate = 30%. After tax cost of debt = (1-0.3)*7.5% = 5.25%

cost of equity = risk free rate+beta*(market return - risk free rate)

= 2+1.25(11-2) = 2+11.25 = 13.25%

WACC = weight of debt*cost of debt+weight of equity*cost of equity = 0.75*5.25+0.25*13.25 = 3.94+3.31 = 7.25%

Revenue projections:

Price in the first year = $40. This will increase by 3% every year due to inflation. The same has been incorporated in the projections:

NPV:

IRR is the rate which makes NPV as zero. Using trial and error method, we get IRR as 41.88%.

Profitability index = PV of inflows/investment = (2556957.44-1000000)/1000000 = 1.56

Payback:

As NPV is positive, IRR is greater than cost of capital, profitability index is greater than 1, and there is an early payback (payback happens in 3rd year), the project should be executed. Its a "go".

S. No Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 1 Units sold 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 2 Selling price 40.00 41.20 42.44 43.71 45.02 46.37 47.76 49.19 50.67 52.19 3 Revenue 1,600,000.00 1,648,000.00 1,697,440.00 1,748,363.20 1,800,814.10 1,854,838.52 1,910,483.67 1,967,798.18 2,026,832.13 2,087,637.09 4 Fixed costs -175,000.00 -175,000.00 -175,000.00 -175,000.00 -175,000.00 -175,000.00 -175,000.00 -175,000.00 -175,000.00 -175,000.00 5 Cash needed for working capital (10% of revenue) -160,000.00 -164,800.00 -169,744.00 -174,836.32 -180,081.41 -185,483.85 -191,048.37 -196,779.82 -202,683.21 -208,763.71 6 Variable cost per unit 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 7 Total variable cost -1,200,000.00 -1,200,000.00 -1,200,000.00 -1,200,000.00 -1,200,000.00 -1,200,000.00 -1,200,000.00 -1,200,000.00 -1,200,000.00 -1,200,000.00 8 Depreciation -100,000.00 -100,000.00 -100,000.00 -100,000.00 -100,000.00 -100,000.00 -100,000.00 -100,000.00 -100,000.00 -100,000.00 9 Profit before tax (revenue - fixed costs - variable costs - depreciation 125,000.00 173,000.00 222,440.00 273,363.20 325,814.10 379,838.52 435,483.67 492,798.18 551,832.13 612,637.09 10 Tax @30% -37,500.00 -51,900.00 -66,732.00 -82,008.96 -97,744.23 -113,951.56 -130,645.10 -147,839.46 -165,549.64 -183,791.13 11 Profit after tax 87,500.00 121,100.00 155,708.00 191,354.24 228,069.87 265,886.96 304,838.57 344,958.73 386,282.49 428,845.97 12 Operating cash flow (profit after tax+depreciation+cash needed for working capital) 347,500.00 385,900.00 425,452.00 466,190.56 508,151.28 551,370.82 595,886.94 641,738.55 688,965.70 737,609.68

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.