Please solve the following case using Excel Jack Tar, CFO of Sheetbend & Halyard
ID: 2780554 • Letter: P
Question
Please solve the following case using Excel
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, for $800,000 after 5 years. (Hint: do not forget to consider taxable gain on the sale. Tax due to sale equals: 0.35x(sale value –book value)).
• Refurbishing the plant would cost $500,000. This investment would be depreciated over 10 years as shown in the table below. The new machinery would cost $1 million. This investment would be depreciated over 5 years as shown in the table below.
• 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 won’t be fully depreciated. The tax loss on plant will equal the book value since the market price of each asset is zero. Therefore, savings in year 5 equals: 0.35 x book value of the plant (i.e., original investment minus 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 assumed a working capital investment of 10% of sales. Investment in working capital will be fully recovered at the end of the project in year 5. However, when he finished debugging the spreadsheet, another confidential envelope arrived from Sheetbend’s CEO. It contained a firm offer from a Maine real estate developer to purchase Sheetbend’s Pleasantboro land and plant for $1.5 million in cash.
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.
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).
TABLE 1 Forecast income statement for the U.s nvas project (dollar value in thousands, except price per yard) Year: 1 1. Yards sold 2. Price per yard 3. Revenue (1 x 2) 4. Cost of goods sold 5. Operating cash flow (3 -4) 6. Depreciation 7. Income (5 6) 8. Tax at 35% 9. Net income (7-8) Notes: 100.00 100.00 100.00 100.00 100.00 30.00 30.00 30.00 30.00 30.00 3,000.00 3,000.00 3,000.00 3,000.00 3,000.00 2,100.00 2184.00 2,271.36 2.362.21 2456.70 900.00 816.00 728.64 637.79 543.30 250.00 250.00 250.00 250.00 250.00 650.00 566.00 478.64 387.79 293.30 227.50 198.10 167.52 135.72 $422.50 $367.90 S311.12 $252.07 $190.65Explanation / Answer
Year 0 1 2 3 4 5
1. Yards sold 100 100 100 100 100
2. Price per yard 30 30 30 30 30
3. Revenue in (000) 3,000.00 3,000.00 3,000.00 3,000.00 3,000.00
4. Cost of goods sold (000) 2,100.00 2,184.00 2,271.36 2,362.21 2,456.70
5. Operating cash flow (000) 900 816 728.64 637.79 543.3
6. Depreciation-machine* (000) 200 200 200 200 200
7. Depreciation on Plant**(000) 50 50 50 50 50
8. Income (5 – 6 – 7) (000) 650 566 478.64 387.79 293.3
9. Tax at 35% (000) 227.5 198.1 167.52 135.72 102.65
10. Net Income (000) 422.5 367.9 311.12 252.07 190.65
11. Cash flow - operations (000) 672.5 617.9 561.12 502.07 440.65
12. Capital investment –1,500.00
13. Investment in working capital (10% on sales ie., 3000000) –300.00 300
14. Tax savings on machine ----
15. Tax savings on plant 87.5
16. Sale of land (after tax) 393.5
17. TOTAL CASH FLOW –1,800.00 672.5 617.9 561.12 502.07 1,187.77
working notes:
Row no:4 Cost of goods sold Fixed cost 3,00,000
Every year 4% inflation variable cost 18x100000 1800000
I year 2100000
Add: 4% 84000
II year 2184000
Add: 4% 87360
III year 2271360
Add: 4% 90854.4
IV year 2362214.4
Add: 4% 94488.576
V year 2456703
Row no:5 Operating cash flow 4th row – 3rd row
Row no:6 Depreciation on machine ( on 1 million i.e., original cost) Straight line method Depreciation
I year 200000
II year 200000
III year 200000
Iv year 200000
V year 200000
Row no:7 Depreciation on plant for 10 years ( on 500,000 i.e., original cost) Straight line method Depreciation
I year 50,000
II year 50000
III year 50000
Iv year 50000
V year 50000
Row no:8 Operating cash flow 5th row – (6th row + 7th row)
Row no:9 Tax at 35% 5th row – (6th row + 7th row)
I year 35% on 650000 227500
Row no:10 Net Income 8th row –9th row (i.e., for I year 650000-227500 = 422500)
Row no:11 Cash flow ( Depreciation + Net income ) 10th row + (6th row + 7th row) (i.e., for I year 422500+200000+50000 = 672.50)
Row no:12 Initial investment 1500000
Row no:14 Tax saving on machine Total amount written off i.e, 1000000 (dep 5yrs X 200000
Row no:15 Tax saving on Plant 0.35x500000-250000 = 87500
The capital investment shows the initial $ 1.5 million investment for plant restructuring and the purchase of new machinery.
When the project closes after five years, the machinery and plant will have no value. But they will not be fully depreciated. The tax loss in each of them will be equal to the book value because the market price of each asset is zero. Therefore, the fiscal savings of year 5 (lines 14 and 15) are equal to:
0.35 x remaining book value (i.e., initial investment less accumulated depreciation)
Investment in working capital is initially set at $ 300,000, but in year 5, when the project is closed, investment in working capital is considered recovered.
If the project continues, the land can not be sold until the end of year 5. If the land is sold for $ 600,000 (as can be expected by Mr. Tar, the taxable profit on the sale is $ 590,000, that land be transported to books at $ 10,000. Therefore, cash flow from the sale of land, net of taxes to 35%, is $ 393,500.
If the land can be sold for $ 1.5 million immediately, the next income will be as follows:
$ 1.500.000 - [0.35 x ($ 1.500.000 - $ 10.000)] = $ 978.500.
Selling is the best option.
The project is a positive NPV project and should be considered, but the effort and investment for a project that does not offer long term employment for the plant has limited appeal. Recruiting people for a five-year production is not a good result when they are to be fired. Small cities (hypothesized) do not respond well to short term contracts like this. People buy homes and cars once they have a job. Those people lose their homes and cars when layoffs begin at the end of production. When these jobs are over, there is no guarantee that the new production will be found for the plant. It does not open the plant without a plan for a longer term job, perhaps another canvas contract or another use of the facility.