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 Dummont's CEO asked Mrs. Duncan to review the bid before it was submitted.

The bid and its documents had been prepared by Dummont's 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, longterm contract. Second, producing the duffel canvas would require an investment of $1.4 million to purchase machinery and to refurbish Dummont's 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 Dummont's 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., discount rate) 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, the original land cost is $10,000, 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 Dummont's 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 Dummont's 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 Excel's 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 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 Dummont's CEO. It contained a firm offer from a Halifax real estate developer to purchase Dummont's 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 price of $30 per yard anyway? (Hint: The opportunity cost of $1.5 million should be accounted for at the beginning of the project.)

3

(b) Now suppose Dummont has a firm offer from the real estate developer to purchase Dummont's 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 firm's cost of capital (i.e., discount rate), so Mrs. Duncan has estimated that the firm's cost of capital (i.e., discount rate) 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