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.

## 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...