1

Financial Management I Final Project

Purpose: This almost real financial analysis project requires all knowledge we have learned in this course. It also teaches us how to use Excel to perform financial analysis in a professional way.

Project Capital Budgeting, Breakeven Analysis, and Scenario Analysis

Annie Duncan, CFO of Dummont Inc., opened the company confidential envelope. It contains a draft of a competitive bid for a contract to supply duffel canvas to the Canadian Air Forces. The cover memo from Dummonts CEO asked Mrs. Duncan to review the bid before it was submitted.

The bid and its documents had been prepared by Dummonts sales staff. It called for Dummont to supply 100,000 yards of duffel canvas per year for 5 years. The proposed selling price was fixed at $30 per yard. The variable costs were estimated to be $18 per yard and the fixed costs (excluding depreciation) to be $300,000 in the first year. Moreover, both variable costs and fixed costs are expected to increase at the inflation rate of 4% per year (after the first year), but the yards sold and the price per yard would be fixed by contract.

Mrs. Duncan was not usually involved in sales, but this bid was unusual in at least two aspects. First, if accepted by the client, it would commit Dummont to a fixed price, long-term contract. Second, producing the duffel canvas would require an investment of $1.4 million to purchase machinery and to refurbish Dummonts plant in Halifax, Nova Scotia right now at the beginning of the first year.

Mrs. Duncan started working on the proposal and after a week she had collected the following information about the project:

The plant in Halifax was old since it had been built in the 1920s. It is currently idle and fully depreciated on Dummonts books, except for the purchase cost of the land of $10,000. So, for the base case analysis, Dummont can just ignore the current market value of the land.

Now the land is a valuable shorefront property, and Mrs. Duncan estimates it can be sold for $600,000 at the end of year 5.

Refurbishing the plant would cost $500,000. This investment would be depreciated in an asset class that has a CCA rate of 5%.

The new machinery would cost $900,000. This investment could be depreciated in an asset class that has a CCA rate of 30%.

The refurbished plant and new machinery would last for many years. However, the remaining market for duffel canvas was small and it was not clear that additional orders could be obtained once the Forces contract was finished. The machinery was custom built and could be used only for duffel canvas. Its second-hand value at the end of five years was probably zero.

2

Finally, Mrs. Duncan estimated that the investment in working capital would average 10% of sales. The working capital remains unchanged over the whole life of the project and will be fully recovered at the end of project life.

The cost of capital (i.e., required rate of return) for Dummont is 12% and the tax rate is 35%.

The 50% of capital gain is taxable. In other words, the half of tax on capital gain is waived. For example, if the original land cost is $10,000 and now its market value is $50,000, then the cash flow received by the seller is $43,000 [= 50,000 (50,000 10,000) 0.5 35%].

With all this information on hand, Mrs. Duncan constructed a spreadsheet to calculate the NPV of the duffel canvas project, assuming that Dummonts bid would be accepted by the Forces. Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard?

Requirements

1. Using an Excel Spreadsheet:

Find the NPV, IRR, MIRR, PI, Payback, and Discounted Payback of Dummonts project using the pro forma financial statement method to determine cash flows.

Enter the input variables in cells of their own at the top of the spreadsheet (so it is easier to do calculations).

Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven; do not put any numbers in equations, only cell references.

Use Excels built-in functions wherever possible (e.g. PV and IRR functions).

2. Breakeven Analysis

Find the NPV breakeven points in the number of units, the sales price per unit, and the cost of capital (i.e., discount rate) for the base case. (Hint: use the Goal Seek tool built in Excel)

3. Scenario Analysis: Repeat the analysis for two different scenarios. (Use a separate spreadsheet)

Consider the following two independent scenarios for the company. For each scenario, calculate the NPV, IRR, MIRR, PI, Payback, and Discounted Payback, and include these analyses in your final recommendation. Find the NPV breakeven points in the number of units sold, the sales price per unit, and the cost of capital (i.e., discount rate) for the following two cases.

(a) Mrs. Duncan had just finished reviewing the spreadsheet with her financial analysis of the project when another confidential envelope arrived from Dummonts CEO. It contained a firm offer from a Halifax real estate developer to purchase Dummonts land and plant TODAY for $1.5 million in cash (consider that the plant and land can still be sold at the end of year 5 for only $600,000). Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed

3

price of $30 per yard anyway? (Hint: The opportunity cost of $1.5 million should be accounted for at the beginning of the project.)

(b) Now suppose Dummont has a firm offer from the real estate developer to purchase Dummonts land and plant TODAY for $ 1 million OR in 5 years for $1.5 million. The CEO also considers that 12% return is not correctly reflecting the firms cost of capital (i.e., required rate of return), so Mrs. Duncan has estimated that the firms cost of capital is actually 15% rather than 12%. Should the company proceed with the project considering the new discount rate and the real estate offers? (Hint: $1 million and $1.5 million are the opportunity cost and salvage value respectively.)

4. Sensitivity Analysis (N/A)

5. Recommendation

Use the results you obtained in the NPV, IRR, MIRR, PI, Payback, and Discounted Payback, breakeven, and scenario analyses above to write a one-page report on your findings and recommend whether the company should proceed with the project.

You can discuss the project with your study partner and share thoughts. However, you have to submit your own work individually to the course website.

Due day: Dec 04, 2020 Submitted online. No late submission is acceptable.

6. Present this final project in a professional way in your writing and Excel file. It is your responsibility to communicate clearly to the marker.

7. Hand in (FOLLOW THESE INSTRUCTIONS STRICTLY):

Page 1, cover page with your name(s), course number and signature(s) (if you can do it electronically.)

Page 2, Excel spreadsheet (export to PDF or Word file) showing base case NPV, IRR, MIRR, PI, Payback, and Discounted Payback, and breakeven values (Requirements 1 and 2)

Page 3, Excel spreadsheet (export to PDF or Word file) showing formulas in the above spreadsheet. Use Page Preview to make sure that this is printing on one page.

Page 4 and 5 scenario analysis (one for each scenario in Requirement 3)

Excel spreadsheet (export to PDF or Word file) showing each scenario.

Page 6, Executive Summary of interpretation and recommendation. Try not to exceed one page.

Please PUT all pages together in one file.

Upload the electronic Excel spreadsheet also to the dropbox on the course website. The electronic spreadsheet will not be graded, but it is the supporting evidence of your work and shows how you reach the result (formulas included). The electronic file should be the same as your hardcopy (PDF or Word file that is ready for print).

4

8. You must put your name(s) on the cover page of the assignment.

PROJECT EVALUATION GUIDE: Analysis and Technology
Excel Spreadsheet (NPV, IRR, B/E, etc. formulas, supporting formula sheet, organization of your spreadsheet), including break-even analysis	20
Scenario Analysis	10
Interpretation and Results Recommendation	10
Writing
Writing Format and Excel Layout (logic, spelling, grammar, organization, and conciseness). Note: Essentially this is not a writing assignment, so the grading is not based on the beauty of your writing. However, it should be professional. Put a table on one page instead of two or even more.	5
TOTAL	45