Business Finance
Boston University Capital Budgeting Time Value of Money Exercises Analysis

Boston University

Question Description

Can you help me understand this Excel question?

General Directions:In this assignment, you’ll be using Excel to do a capital budgeting analysis and a couple of time value of money exercises and a bit of financial statement analysis.You can do this assignment in groups of up to three.The choice of group members (or doing it by yourself) is yours.You can work with people in sections other than yours if you wish.In doing this assignment, I ask that you hand in the solutions (with row and column headings printed) and print out the formulas that generated the solutions (also with row and column headings printed).This will include some financial functions which you may (or may not) recall from Digital Strategies, for those of you in CSOM.

Capital Budgeting

You’re considering a project that will last for 8 years.You have to invest $600 million in fixed assets, plus an additional $8 million in net working capital initially.The fixed assets will be depreciated as a 10 year asset using MACRS rules.A copy of the MACRS tables may be found on the reverse side of this page.Sales in the first year are expected to be $160 million, with operating expenses excluding depreciation expected to be $70 million.During the project, you expect that you can increase sales by 4% per year, while operating expenses will rise by 3% per year.Net working capital will be 5% of sales.The NWC increase to support year 1 sales is addressed in the initial investment.NWC is needed one year in advance (NWC needed to support year 2 sales must be funded in year 1, for example).At the end of the project, you can sell the equipment for $70,000,000.Let your firm’s cost of capital be 7% and the firm’s tax rate be 20%.Find the NPV, the IRR and the MIRR of the project.Do you accept or reject the project?Please note that this will be much easier to do if you give a bit of thought as to how to address the growth rates in setting up the spreadsheet.

The Time Value of Money (do with your own formulas, not tables)

  • Suppose you want to live in a home in Tucson, AZ upon      retirement.You plan to set aside $25,000      one year from now and you plan to increase that annual contribution by 4%      per year.You earn 7% on your      investments.You have $125,000      saved.You want to retire in 40      years.In today’s dollars, how much      will you have put toward your estate if the last payment is on the day you      retire?How much will this be on      the day you retire and actually buy the estate?
  • You are following Ehrlich’s Excellent Edifices (3E – a very successful construction firm) stock.You’re asked to value the firm’s stock.You’re told the firm will pay a dividend of $2 next quarter.You estimate the firm’s dividends will grow at 3% per quarter for 3 years; then at 2% quarterly for 6 years; then at 1% quarterly thereafter.The discount rate is 9% per year, compounded annually.Find the stock price.
  • You plan to be a doting parent for your three adorable tots (you      love to plan ahead).You plan to      set up a fund to pay for their law school educations immediately after      their undergraduate studies.The      fund will be set up to pay each little one $175,000 for the first year of      school, then increase at 4% per year through graduation.Assume the kids graduate after 3 years      of law school.The oldest tot      begins law school in 18 years; the second one starts two years later and      the last little one starts one year after the second.You have set aside $75,000 thus      far.You earn 8% on your      investments.Next year’s salary is      expected to be $220,000.What      fraction of your salary must you set aside if you get raises of 2% per      year to make your vision a reality?You start your savings based on income one year from now and you      make your last payment on the first child’s first day at law school.

Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

Final Answer

Attached.

Project duration (Years)
Investment
NWC
Depreciation (Years) (MACRS)
Year 1 sales
Operating expenses
Annual Sale increase
Annual operating expense increase
NWC as % of sales
Salvage Value
Cost of Capital
Tax rate

8
$600,000,000
$8,000,000
10
$160,000,000
$70,000,000
4%
3%
5%
$70,000,000
7%
20%

Year
Sales
Operation expense
Operating Profit
Depreciation
EBIT
Taxes
Income
Income + Non-cash expense
Change in NWC
Operating Cashflow
Salvage Value
Net Casflows
PV
NPV

NPV
IRR
MIRR

($6,071,419)
6.76%
4.00%

Decision

Reject

Capital Budgeting
0

1
$160,000,000
$70,000,000
$90,000,000

2
$166,400,000
$72,100,000
$94,300,000

3
$173,056,000
$74,263,000
$98,793,000

4
$179,978,240
$76,490,890
$103,487,350

$60,000,000
$30,000,000
$6,000,000

$108,000,000
($13,700,000)
$0

$86,400,0...

UIUC

Anonymous
I was on a very tight deadline but thanks to Studypool I was able to deliver my assignment on time.

Anonymous
The tutor was pretty knowledgeable, efficient and polite. Great service!

Anonymous
I did not know how to approach this question, Studypool helped me a lot.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Questions
Related Tags