Project: 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 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. 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, longterm 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 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 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 Excel's built-in functions wherever possible (e.g. PV and IRR functions). 2. Breakeven Analysis for each of three scenarios (the basic and two others stated by Part 3. below.) Find the accounting breakeven points in the number of units and selling price. 3. Scenario Analysis: Repeat the analysis for two different scenarios. (Use a separate spreadsheet in the same Excel workbook for each scenario. You can additionally add a summary table as shown by the example) 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 accounting breakeven points in the number of units and selling price. a. Ms. 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 Ms. 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 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 Ms. 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 for each of the three scenarios (the basic and two others stated by Part 3. above.) Do the complete sensitivity analysis by following the example, using +/- 15% and 30%. Compare how NPV is sensitive to the same percentage changes in the values of key input variables: units sold, sales price per unit, and cost of capital (i.e., discount rate). 5. Recommendation Use the results you obtained in the NPV, IRR, MIRR, PI, Payback, and Discounted Payback, breakeven, scenario and sensitivity analyses above to write a one-page report on your findings and recommend whether the company should proceed with the project. 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 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 Excel's built-in functions wherever possible (e.g. PV and IRR functions). 2. Breakeven Analysis for each of three scenarios (the basic and two others stated by Part 3. below.) Find the accounting breakeven points in the number of units and selling price. 3. Scenario Analysis: Repeat the analysis for two different scenarios. (Use a separate spreadsheet in the same Excel workbook for each scenario. You can additionally add a summary table as shown by the example) 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 accounting breakeven points in the number of units and selling price. a. Ms. 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 Ms. 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 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 Ms. 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 for each of the three scenarios (the basic and two others stated by Part 3. above.) Do the complete sensitivity analysis by following the example, using +/- 15% and 30%. Compare how NPV is sensitive to the same percentage changes in the values of key input variables: units sold, sales price per unit, and cost of capital (i.e., discount rate). 5. Recommendation Use the results you obtained in the NPV, IRR, MIRR, PI, Payback, and Discounted Payback, breakeven, scenario and sensitivity analyses above to write a one-page report on your findings and recommend whether the company should proceed with the project