Jack Tar, CFO of Sheetbend & Halyard Inc. opened the company confidential envelope. It contained a draft of a competitive bid for a contract to supply duffel canvas to the U.S. Navy. The cover memo from Sheetbend's CEO asked Mr. Tar to review the bid before it was submitted.

The bid and its supporting documents had been prepared by Sheetbend's sales staff. It called for Sheetbend to supply 100,000 yards of duffel canvas per year for 5 years. The proposed selling price was fixed at $30 per yard.

Mr. Tar was not usually involved in sales, but this bid was unusual in at least two respects. First, if accepted by the navy, it would commit Sheetbend to a fixed-price, long-term contract. Second, producing the duffel canvas would require an investment of $1.5 million to purchase machinery and to refurbish Sheetbend's plant in Pleasantboro, Maine.

Mr. Tar set to work and by the end of the week had collected the following facts and assumptions:

The plant in Pleasantboro had been built in the early 1900s and is now idle. The plant was fully depreciated on Sheetbend's books, except for the purchase cost of the land (in 1947) of $10,000.

Now that the land was valuable shorefront property, Mr. Tar thought the land could be sold, immediately for $600,000. If the land is sold after the project is shut down (at the end of year 5) its value is expected increase at rate of 4% per year (Hint: do not forget to consider taxable gain on the sale. Tax due to sale equals (tax rate:30%): tax rate x (sale value book value)).

Refurbishing the plant would cost $500,000. This investment would be depreciated as shown in the spreadsheet (straight-line over 10 years). The new machinery would cost $1 million. This investment would be depreciated as shown in the spreadsheet (straight-line over 5 years).

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 navy contract was finished. The machinery was custom-built and could be used only for duffel canvas. Its secondhand value at the end of 5 years was probably zero.

Hint: When the project is shut down after five years, the machinery and plant will be worthless. The machinery will be fully depreciated, but the plant wont be fully depreciated. The tax loss on

plant will be equal the book value since the market price of each asset is zero. Therefore, tax savings in year 5 = tax rate x book value of the plant

(book value of the plant = original investment - accumulated depreciation)

Table 1 shows the sales staff's forecasts of income from the navy contract. Mr. Tar reviewed this forecast and decided that its assumptions were reasonable.

But the forecast income statement contained no mention of working capital. Mr. Tar thought that 20% of each years sales would not be paid for until the following year. Mr. Tar also assumed that 20% of each years expenses would represent an investment that took place in the previous year.

Hint: For year 0 there will be $2,100,000x0.20=$420,000 investment in inventory). Investment in working capital will be fully recovered by year 6 (acct. rec. at the end of year 5 will be collected in year 6).

Should Mr. Tar pursue the project if the discount rate for the project is 12%? Show all your work in excel sheet. Please enter the formulas (not the numbers) in the cells.

PROBLEM:

Notes: 1. Yards sold and price per yard would be fixed by contract. 2. Cost of goods includes fixed cost of $300,000 per year plus variable costs of $18 per yard. Costs are expected to increase at the inflation rate of 4% per year. 3. Depreciation: A $1 million investment in machinery is depreciated straight-line over 5 years ($200,000 per year). The $500,000 cost of refurbishing the Pleasantboro plant is depreciated straight-line over 10 years ($50,000 per year).

PLEASE SHOW CLEAR CALCULATIONS FOR EXCEL SO I KNOW HOW YOU GET THE ANSWERS :)

Should Mr. Tar pursue the project if the discount rate for the project is 12%? Show all your work in excel sheet. Please enter the formulas (not the numbers) in the cells. TABLE 1 Forecast income statement for the U.S. Navy duffel canvas project (dollar values in thousands, except price per yard) Year: 1 2 3 4 5 1. Yards sold 100.00 100.00 100.00 100.00 100.00 2. Price per yard 30.00 30.00 30.00 30.00 30.00 3. Revenue (1 x 2) 3,000.00 3,000.00 3,000.00 3,000.00 3,000.00 4. Cost of goods sold 2,100.00 2,184.00 2,271.36 2,362.21 2,456.70 5. Operating cash flow (3 4) 900.00 816.00 728.64 637.79 543.30 6. Depreciation 250.00 250.00 250.00 250.00 250.00 7. Income (5-6) 650.00 566.00 478.64 387.79 293.30 8. Tax at 30% 195.00 169.80 142.59 116.34 87.99 9. Net income (7 8) $455.00 $396.20 $335.05 $271.45 $205.31 A B C D E F G H 4 5 6 100 100 30 30 3,000.00 2,362.21 3,000.00 2,456.70 543.30 200 637.79 200 50 1 Forecast Cash Flows for the U.S. Navy duffel canvas project (dollar values in thousands, except price per yard) 2 Year 0 1 2 3 3 1. Yards sold 100 100 100 4 2. Price per yard 30 30 30 5 3. Revenue 3,000.00 3,000.00 3,000.00 64. Cost of goods sold 2,100.00 2,184.00 2,271.36 7 5. Operating cash flow 900.00 816.00 728.64 8 6. Depreciation on machine 200 200 200 9 7. Depreciation on Plant 50 50 50 10 8. Income (5 6 7) 650 566 478.64 11 9. Tax at 30% 195.00 169.80 143.59 12 10. Net Income (8 9) 455.00 396.20 335.05 13 11. Cash flow from operations ? ? ? 14 12. CF.invest. in Working Capital ? ? ? ? 15 13. Capital investment ? 16 14. Tax savings on plant 17 15. Opportunity cost of land (after tax) ? 18 16. Sale of land in year 5 (after tax) 19 17. TOTAL CASH FLOW ? ? ? ? 20 18. NPV (@12%) ? 50 387.79 116.34 293.3 87.99 271.45 205.31 ? ? ? ? ? ? ? ? ? ? 21 Forecast Working Capital (dollar values in thousands) Year 0 1 2 3 4 5 6 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1. Receivables (0.20 x revenues) 2. Inventories (0.20 x following year's expenses) 3. Working Capital 4. Change in Working Capital 5. CF.invest. in Working Capital ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Should Mr. Tar pursue the project if the discount rate for the project is 12%? Show all your work in excel sheet. Please enter the formulas (not the numbers) in the cells. TABLE 1 Forecast income statement for the U.S. Navy duffel canvas project (dollar values in thousands, except price per yard) Year: 1 2 3 4 5 1. Yards sold 100.00 100.00 100.00 100.00 100.00 2. Price per yard 30.00 30.00 30.00 30.00 30.00 3. Revenue (1 x 2) 3,000.00 3,000.00 3,000.00 3,000.00 3,000.00 4. Cost of goods sold 2,100.00 2,184.00 2,271.36 2,362.21 2,456.70 5. Operating cash flow (3 4) 900.00 816.00 728.64 637.79 543.30 6. Depreciation 250.00 250.00 250.00 250.00 250.00 7. Income (5-6) 650.00 566.00 478.64 387.79 293.30 8. Tax at 30% 195.00 169.80 142.59 116.34 87.99 9. Net income (7 8) $455.00 $396.20 $335.05 $271.45 $205.31 A B C D E F G H 4 5 6 100 100 30 30 3,000.00 2,362.21 3,000.00 2,456.70 543.30 200 637.79 200 50 1 Forecast Cash Flows for the U.S. Navy duffel canvas project (dollar values in thousands, except price per yard) 2 Year 0 1 2 3 3 1. Yards sold 100 100 100 4 2. Price per yard 30 30 30 5 3. Revenue 3,000.00 3,000.00 3,000.00 64. Cost of goods sold 2,100.00 2,184.00 2,271.36 7 5. Operating cash flow 900.00 816.00 728.64 8 6. Depreciation on machine 200 200 200 9 7. Depreciation on Plant 50 50 50 10 8. Income (5 6 7) 650 566 478.64 11 9. Tax at 30% 195.00 169.80 143.59 12 10. Net Income (8 9) 455.00 396.20 335.05 13 11. Cash flow from operations ? ? ? 14 12. CF.invest. in Working Capital ? ? ? ? 15 13. Capital investment ? 16 14. Tax savings on plant 17 15. Opportunity cost of land (after tax) ? 18 16. Sale of land in year 5 (after tax) 19 17. TOTAL CASH FLOW ? ? ? ? 20 18. NPV (@12%) ? 50 387.79 116.34 293.3 87.99 271.45 205.31 ? ? ? ? ? ? ? ? ? ? 21 Forecast Working Capital (dollar values in thousands) Year 0 1 2 3 4 5 6 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1. Receivables (0.20 x revenues) 2. Inventories (0.20 x following year's expenses) 3. Working Capital 4. Change in Working Capital 5. CF.invest. in Working Capital