MGT 3062 Project Instructions Spring 2014 A + GRAD

The purpose of this project is to: 
•	Analyze a capital budgeting decision using financial analysis tools, including free cash flow analysis, NPV, IRR, Profitability Index, payback period, and MIRR.
•	Learn Excel functions and techniques useful for project analysis,
•	Use Excel spreadsheets to perform sensitivity and scenario analysis,
Learn how to communicate a project recommendation in a clear, logical, and professional manner

General Instructions:
•	This is a group project.  You may work together in groups of 2 to 4.

•	Projects with excessive spelling or grammar mistakes will be rejected.

Turn in your analysis as follows:

**failure to follow this format exactly will result in a substantial reduction in grade**


Page 1:  Title Page.
Include your name, the date, and the title of your project.

Page 2:  Executive Summary.
This is a ONE PAGE brief description of the project and your recommendation. 

Paragraph 1:  Describe the project.
Paragraph 2:  Discuss the risks and rewards involved.
Paragraph 3:  Make your recommendation.  Include numbers to justify your recommendation.  For example, “I recommend that the board reject this project because the expected NPV is negative five million dollars and the IRR is only 8%” 

You can add more paragraphs if it makes your write up clearer.

Page 3 - X:  Your analysis in words.  
Take as many pages as you need to justify your recommendation.  This should be a very detailed and well reasoned analysis.  Do not just attach spreadsheets.  Explain the work that you have done.  At a minimum, compute NPV, IRR, MIRR, PI, and payback period.  Briefly discuss each metric that you use.  For example, “I compute the profitability index because it shows how much value is created for every dollar invested.  It is calculated by summing the discounted future cash flows…..”

DOCUMENT ANY ASSUMPTIONS THAT YOU MAKE.

Describe the sensitivity analysis.  Why did you do it that way?  
Describe your scenarios.  Why do you think this is the best or worst case?  Under what conditions do best or worst cases materialize.  Be creative.

REMEMBER: The spreadsheets are NOT your analysis.  You must explain what you did and why you did it.  Refer to the spreadsheets in the write up ("as I show on Exhibit A..").  

This section should be long enough to cover the topic, but short enough to be interesting.  Think like an attorney and build your case.  

When you have completed your analysis, estimate an expected NPV of the project.

Page X+1, X+2, and beyond:  A copy of your spreadsheets.  
This is an Excel project.  All values must be calculated in the spreadsheet.  Your calculator should not be used at all (except perhaps to verify your Excel formulas).  A change in any one input must result in a change in the NPV, IRR, MIRR, etc.  Do your WACC calculations on a separate worksheet, but have the main worksheet pull the RACC (required rate of return) from the WACC worksheet.

Show the base scenario, and other scenarios of interest.  Other scenarios of interest may be the "worst case" or the "best case."  You may also like to present cases where NPV is zero because one variable changed (sensitivity analysis).  Use your own judgment.  Your job is to build a case to justify your recommendation.  Suggestion: save interesting scenarios on separate worksheets so that you can easily refer to them while writing your analysis.  

Reduce spreadsheets to ONE PAGE landscape view.  The command sequence is: File, Page Preview, Setup, and “Adjust to fit 1 page".  

Last Pages:
Include a printout of your formulas.  Excel formulas can be viewed by pressing "ctrl ~" (that is two keys held together). Reduce the formula view to ONE PAGE landscape before printing.  I understand that this results in very small print.
 
The Scenario

Your company is considering spending $550 million to purchase and install equipment to build state-of-the-art rophemeric induction spheres.  These personal protection devices have been used successfully by soldiers on the battlefield for over a decade.   However, recent technological advances (coupled with unfortunate intelligence breeches) have rendered the devices useless for battlefield operations.  The top-secret technology has been declassified.  

Firms are rushing to bring commercial applications to the market.  Our company is the first to develop a commercially viable consumer product.  Two pieces of equipment are needed to go into full production:  $380 million dollar rophemerization chamber and a $170 million dollar industrial sphereinator.  The two pieces of equipment must be depreciated using 10 year and 15 year MACRS, respectively.  Assume that the equipment will be put in service within the first six months of the project.  This assumption allows you to use the MARCS table in the Ross Westerfield and Jordan textbook.   The MARCS table is also found here: http://en.wikipedia.org/wiki/MACRS

Assume that you continually update or replace equipment and spend capital equal to that year- total depreciation.

Your initial price point is $1,550 and your first year- projected sales volume is 80,000 units.  You expect the law enforcement market to be strong.   

Initial fixed costs are $800,000 and each system costs $675 in parts and labor.  Subsequent year's projections are shown on the next page.  After the 8th year assume that the demand for the product will persist and free cash flows of the project will grow at 3.5% annually.

Your marginal tax rate is 30%.  Assume that this is one of many projects for the company.  No special tax treatments are required for years of negative earnings.

An initial net working capital investment of $6,000,000 is required.

You will need to upgrade your equipment in 5 years when the competition has "leap-frogged" your unit.  At the end of year four, you will invest an additional $11 million in 5 year MACRS equipment and you will invest an additional net working capital of $1 million into the project.  The equipment purchased at this time will be installed by the middle of year five (use standard MACRS tables with year 5 being the first depreciation year) and it will have a salvage value of about a third of its original value at the end of the project.

Is this project worth doing?  The market risk premium is 6% and the risk free rate is 3.5%.  The project requires a risk premium (over the firm's WACC) of 8% for management approval.  

What is your decision: DEAL OR NO DEAL?

Different stakeholders may be interested in different numbers, so at a minimum, justify your decision by calculating the payback period, the NPV, the IRR, the MIRR, and the Profitability Index.


To further justify your decision, conduct scenario and sensitivity analysis.  Use your own best judgment to estimate the project- sensitivity to the company- projected sales and costs.  How accurate do the estimates have to be for the project to remain profitable?  What input value or combinations of input values drives NPV to zero?  What is the best case scenario?  What causes this best case?  What is the worst case scenario?  What causes the worst case?


Forecasted Price, Sales, and Costs


 	Unit Price	Unit Sales	Variable Cost	Fixed Costs
Year 1	1,550	80,000	675	800,000
Year 2	1,473	160,000	662	816,000
Year 3	1,399	240,000	649	832,320
Year 4	1,329	250,000	636	848,966
Year 5	1,263	300,000	623	865,945
Year 6	1,200	320,000	611	883,264
Year 7	1,140	360,000	599	900,929
Year 8	1,083	390,000	587	918,948

The company has spent a lot of time and effort developing these forecasts. 
Your sensitivity and scenario analysis and recommendations should treat these numbers with due respect. 
As in any forecast, the company has more confidence in the nearer term numbers.


Estimating Weighted Average Cost of Capital (WACC)

Log on to a Bloomberg Terminal.  You may have to create a user id.  Choose a fortune 500 company that has at least one bond.  Do not choose a debt free company.  Use Bloomberg to discover your firm- WACC.  Here is a link that may be helpful:

researchguides.dartmouth.edu/content.php?pid=382082&sid=3131277

Here is a list of Fortune 500 companies:

http://money.cnn.com/magazines/fortune/fortune500/2013/full_list/index.html?iid=F500_sp_full


In your write up, describe in great detail the components of WACC and how it was calculated.  

Assume that you are analyzing this project for your chosen company and make your decision (Deal or No Deal) accordingly.