Capital Budgeting, Breakeven Analysis, and Sensitivity 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 Ms. 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 variablFe 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.

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

Ms. 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 Ms. 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. Finally, Ms. 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, Ms. Duncan constructed a spreadsheet to calculate the NPV of the duffel canvas project, assuming that Dummonts bid would be accepted by the Forces. Should Ms. 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 sensitivity 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).

IF POSSIBLE GIVE DETAILS ON WHAT TO ENTER IN EACH CELL