pkjain, please help! I am getting incorrect values for sections a, c, and f; sections b,d and e are correct. I think my debt value is skewed. Please check the excel functions values and correct the values for sections a, c, and f; and post the corrected excel back to course hero. Here is the question: Suppose you have been hired as a financial consultant to Defense Electronics, Inc. (DEI), a large, publicly traded firm that is the market share leader in radar detection systems (RDSs). The company is looking at setting up a manufacturing plant overseas to produce a new line of RDSs. This will be a five-year project. The company bought some land three years ago for $4.9 million in anticipation of using it as a toxic dump site for waste chemicals, but it built a piping system to safely discard the chemicals instead. The land was appraised last week for $5.7 million. In five years, the aftertax value of the land will be $6.1 million, but the company expects to keep the land for a future project. The company wants to build its new manufacturing plant on this land; the plant and equipment will cost $32.32 million to build. The following market data on DEI?s securities are current: Debt: 234,000 7.4 percent coupon bonds outstanding, 25 years to maturity, selling for 109 percent of par; the bonds have a $1,000 par value each and make semiannual payments. Common stock: 9,200,000 shares outstanding, selling for $71.40 per share; the beta is 1.2. Preferred stock: 454,000 shares of 6 percent preferred stock outstanding, selling for $81.40 per share. Market: 8 percent expected market risk premium; 6 percent risk-free rate. DEI uses G.M. Wharton as its lead underwriter. Wharton charges DEI spreads of 9 percent on new common stock issues, 7 percent on new preferred stock issues, and 5 percent on new debt issues. Wharton has included all direct and indirect issuance costs (along with its profit) in setting these spreads. Wharton has recommended to DEI that it raise the funds needed to build the plant by issuing new shares of common stock. DEI?s tax rate is 38 percent. The project requires $1,400,000 in initial net working capital investment to get operational. Assume Wharton raises all equity for new projects externally and that the NWC does not require floatation costs.. a. Calculate the project?s initial time 0 cash flow, taking into account all side effects. (Negative amount should be indicated by a minus sign. Enter your answer in dollars, not millions of dollars, i.e. 1,234,567. Do not round intermediate calculations and round your final answer to the nearest whole dollar amount.) Cash flow $ b. The new RDS project is somewhat riskier than a typical project for DEI, primarily because the plant is being located overseas. Management has told you to use an adjustment factor of 3 percent to account for this increased riskiness. Calculate the appropriate discount rate to use when evaluating DEI?s project. (Do not round intermediate calculations and round your final answer to 2 decimal places. (e.g., 32.16)) Discount rate % c. The manufacturing plant has an eight-year tax life, and DEI uses straight-line depreciation. At the end of the project (that is, the end of year 5), the plant and equipment can be scrapped for $4.9 million. What is the aftertax salvage value of this plant and equipment? (Enter your answer in dollars, not millions of dollars, i.e. 1,234,567. Do not round intermediate calculations.) Aftertax salvage value $ d. The company will incur $7,200,000 in annual fixed costs. The plan is to manufacture 19,000 RDSs per year and sell them at $11,000 per machine; the variable production costs are $9,600 per RDS. What is the annual operating cash flow (OCF) from this project? (Enter your answer in dollars, not millions of dollars, i.e. 1,234,567.) Operating cash flow $ e. DEI?s comptroller is primarily interested in the impact of DEI?s investments on the bottom line of reported accounting statements. What will you tell her is the accounting break-even quantity of RDSs sold for this project? (Do not round intermediate calculations and round your final answer to nearest whole number.) Break-even quantity units f. Finally, DEI?s president wants you to throw all your calculations, assumptions, and everything else into the report for the chief financial officer; all he wants to know is what the RDS project?s internal rate of return (IRR) and net present value (NPV) are. (Enter your answer in dollars, not millions of dollars, i.e. 1,234,567. Do not round intermediate calculations and round your final answers to 2 decimal places. (e.g., 32.16)) IRR % NPV $

SOLUTION: a) Initial Time Cash Flow: Total Capital to be raised would be Funds Required for Cost of Building Plant and Net Working Capital i.e. 32,320,000+1400000 33,720,000.00 We assume that this capital will be raised in the current weights for Capital i.e. Source of Capital Weight Capital Required Net of Floatation Cost Flotation Cost Gross Capital to be issued Common Stock 69.01% 23,271,053.32 2,276,516.09 25,547,569.41 Preferred Stock 4.19% 1,413,027.09 77,781.31 1,490,808.39 Debt 26.80% 9,035,919.59 331,593.38 9,367,512.97 Total 33,720,000.00 2,685,890.77 36,405,890.77 Initial Time Cash Flow: Particulars Amount Cost of building plant Net Working Capital Opportunity Cost of Land $32,320,000.00 $1,400,000.00 $5,700,000.00 Flotation Costs: Common Stock Preferred Stock Debt Total Flotation Costs $2,276,516.09 $77,781.31 $331,593.38 $2,685,890.77 Total Initial Time Cash Flow 639000000 $36,405,890.77 Note: The cost of land is a sunk cost. Therefore, not considered in decision making. But at present it can be sold for $5.7 million if not used in project. Therefore, it is considered as opportunity costs. b) Calculation of Weighted Average Cost of Capital: Cost of Debt: Par Value Market Price (109% * $1,000) Future Value Coupon Rate (7.4%/2) Nper / Time (25 years * 2) Flotation Cost $1,000.00 $1,090.00 $1,000.00 3.70% 50.00 5.00% PMT = Par Value * Coupon Rate PMT = $1,000 * 3.70% PMT $37.00 Flotation Costs = Flotation Costs Percentage * Market Price Flotation Costs = 5% * $1090 Flotation Costs $43.60 Present Value = Market Price - Flotation Costs Present Value = $1090 - $37.00 Present Value $1,046.40 Using excel formula, Rate (Yield Semi-annual) Annual Yield (4.26% * 2) 3.50% 7.00% After tax Cost of debt = Pretax Cost of Debt * (1 - Tax Rate) After tax cost of debt = 8.53% * (1 - 38%) After tax cost of debt 4.34% Cost of Preferred Stock: Par Value Market Price Dividend Rate Flotation Costs $100.00 $81.40 6.00% 6.00% Dividend = Par Value * Dividend Rate Dividend = $100 * 6.00% Dividend $6.00 Flotation Costs = Flotation Costs Percentage * Market Price Flotation Costs = 6% * $81.40 Flotation Costs $4.88 Net Proceeds = Market Price - Flotation Costs Net Proceeds = $81.40 - $4.88 Net Proceeds $76.52 Cost of Preferred Stock = Dividend / Net Proceeds Cost of Preferred Stock = $6.00 / $76.52 Cost of Preferred Stock 7.84% Cost of Equity: Cost of Equity = Risk Free Rate + Beta * Market Risk Premium Cost of Equity = 6% + 1.2 * 9% Cost of Equity 16.80% Calculation of Weights: Source Market Value Weight Debt Preferred Stock Common Stock $255,060,000.00 $39,886,000.00 $656,880,000.00 26.80% 4.19% 69.01% Total $951,826,000.00 100.00% Calculation of Weighted Average Cost of Capital: Source Weights Cost Debt Preferred Stock Common Stock 26.80% 4.19% 69.01% 4.34% 7.84% 16.80% WACC 1.16% 0.33% 11.59% WACC 13.09% Appropriate Weighted Average Cost of Capital = 13.09% + 2% Appropriate WACC c) 15.09% Depreciation = Purchase Cost / Useful Life Depreciation = $35,000,000 / 8 years Depreciation $4,040,000.00 Written Down Value = Purchase Cost - Total Depreciation in 5 years Written Down Value = $35,000,000 - ($4,375,000 * 5 years) Written Down Value $12,120,000.00 After tax Salvage Value: Particulars Amount Salvage Value Written Down Value Loss Tax Savings @ 38% $6,100,000.00 $12,120,000.00 $(6,020,000.00) $(2,287,600.00) After tax Salvage Value d) $8,387,600.00 Annual Operating Cash Flow: Particulars Amount Sales Revenue Less: Variable Cost Less: Fixed Costs Less: Depreciation $209,000,000.00 $182,400,000.00 $7,200,000.00 $4,040,000.00 Profit before tax Less: Taxes @ 38% $15,360,000.00 $5,836,800.00 Profit after tax Add: Depreciation $9,523,200.00 $4,040,000.00 Operating Cash Flow e) $13,563,200.00 Accounting and Financial Breakeven Quantity Breakeven Point of Sales is that Quantity where Sales would be equal to all the expenses including Variable Cost, Fixed Cost, Depreciation Breakeven Point (in units) Contribution / Selling Price - Variable Cost Contribution= (Fixed Costs + Depreciation) Breakeven Point (in uints) Breakeven Point (in Sales) f) $11,240,000.00 8,029 $88,314,286 Calculation of Net Present Value Net Present Value = Present Value of Cash Inflows Less Present Value of Cash Outflows Present Value of Cash Inflows Operating Cash Inflows (Year 1 to Year 5) Working Capital Opportunity Cost of Land (5th Year) Present Value of Cash Inflows Present Value of Cash Outflows {from a)} Amount per annum Present Value Fact @14.06% $13,563,200.00 3.35 $1,400,000.00 0.50 $6,100,000.00 0.50 -$36,405,890.77 Present Value $45,369,234.73 $693,330.15 $3,020,938.50 $49,083,503.38 1 -$36,405,890.77 Net Present Value $12,677,612.61 Calculation of Internal Rate of Return Err:523 For Computing IRR we would interpolare above computed Cash Flows along with the Cash flows discounted at 28% Cash flows @ 28% Present Value of Cash Inflows Amount per annum Present Value Fact @28% Present Value Operating Cash Inflows (Year 1 to Year 5) $13,563,200.00 0.00 $0.00 Working Capital $1,400,000.00 0.00 $0.00 Opportunity Cost of Land (5th Year) $6,100,000.00 0.00 $0.00 Present Value of Cash Inflows $0.00 Present Value of Cash Outflows {from a)} -$36,405,890.77 1 -$36,405,890.77 Net Present Value -$36,405,890.77 Difference Interpolating, NPV Cost of Capital NPV 15.09 28 12.91 $12,677,612.61 -$36,405,890.77 -$49,083,503.38 IRR= 18.42 As NPV is positive , we recommend to invest in the project. g) Calculation of NPV at Selling Price as $10355 Revised cash flows Particulars Amount Sales Revenue Less: Variable Cost Less: Fixed Costs Less: Depreciation $186,390,000.00 $169,200,000.00 $7,000,000.00 $6,100,000.00 Profit before tax Less: Taxes @ 35% $4,090,000.00 $1,431,500.00 Profit after tax Add: Depreciation $2,658,500.00 $6,100,000.00 Operating Cash Flow $8,758,500.00 NPV Present Value of Cash Inflows Operating Cash Inflows (Year 1 to Year 5) Working Capital Opportunity Cost of Land (5th Year) Present Value of Cash Inflows Present Value of Cash Outflows {from a)} Amount per annum Present Value Fact @14.06% $8,758,500.00 3.43 $1,300,000.00 0.52 $6,000,000.00 0.52 -$36,405,890.77 Present Value $30,025,330.30 $673,405.28 $3,108,024.36 $33,806,759.93 1 -$36,405,890.77 Net Present Value -$2,599,130.84 IRR As NPV is negative, we would compute the Discounted Cash Flows @ 8% Present Value of Cash Inflows Amount per annum Present Value Fact @8% Operating Cash Inflows (Year 1 to Year 5) $8,758,500.00 3.99 Working Capital $1,300,000.00 0.68 Opportunity Cost of Land (5th Year) $6,000,000.00 0.68 Present Value of Cash Inflows Present Value of Cash Outflows {from a)} -$36,405,890.77 Present Value $34,970,150.86 $884,758.16 $4,083,499.18 $39,938,408.20 1 -$36,405,890.77 Net Present Value $3,532,517.43 Difference Interpolating, NPV Cost of Capital NPV 14.06 8 -6.06 IRR= -$2,599,130.84 $3,532,517.43 $6,131,648.26 11.49 As NPV is negative we would not recommend to invest h) As per the results observed in f and g above, it is very clear that in case the Selling Price is been changed even by 5% the result got negative. The Venture is very sensitive towards Selling Price, Variable Cost and other variable factors whose change can hit the organisation a lot. Sensetivity Analysis has following advantages: 1. It compels the decision maker to identify the variables which affect the cashflow forecasts. This helps him in understanding the investment project in totality. 2. It indicates the critical variables for which additional information may be obtained. The decision maker can consider actions which may help in strengthening the "weak spots" in the project. 3. It helps to expose inappropriate forecasts and thus guides the decision maker to concentrate on relevant variables. Suppose you have been hired as a financial consultant to Defense Electronics, Inc. (DEI), a large, publicly traded firm that is the market share leader in radar detection systems (RDSs). The company is looking at setting up a manufacturing plant overseas to produce a new line of RDSs. This will be a fiveyear project. The company bought some land three years ago for $4.9 million in anticipation of using it as a toxic dump site for waste chemicals, but it built a piping system to safely discard the chemicals instead. The land was appraised last week for $5.7 million. In five years, the aftertax value of the land will be $6.1 million, but the company expects to keep the land for a future project. The company wants to build its new manufacturing plant on this land; the plant and equipment will cost $32.32 million to build. The following market data on DEI's securities are current: Debt: 234,000 7.4 percent coupon bonds outstanding, 25 years to maturity, selling for 109 percent of par; the bonds have a $1,000 par value each and make semiannual payments. Common stock: 9,200,000 shares outstanding, selling for $71.40 per share; the beta is 1.2. Preferred stock: 454,000 shares of 6 percent preferred stock outstanding, selling for $81.40 per share. Market: 8 percent expected market risk premium; 6 percent risk-free rate. DEI uses G.M. Wharton as its lead underwriter. Wharton charges DEI spreads of 9 percent on new common stock issues, 7 percent on new preferred stock issues, and 5 percent on new debt issues. Wharton has included all direct and indirect issuance costs (along with its profit) in setting these spreads. Wharton has recommended to DEI that it raise the funds needed to build the plant by issuing new shares of common stock. DEI's tax rate is 38 percent. The project requires $1,400,000 in initial net working capital investment to get operational. Assume Wharton raises all equity for new projects externally and that the NWC does not require floatation costs.. a. Calculate the project's initial time 0 cash flow, taking into account all side effects. (Negative amount should be indicated by a minus sign. Enter your answer in dollars, not millions of dollars, i.e. 1,234,567. Do not round intermediate calculations and round your final answer to the nearest whole dollar amount.) Cash flow $ Cash flow: -42,172,078.43 b. The new RDS project is somewhat riskier than a typical project for DEI, primarily because the plant is being located overseas. Management has told you to use an adjustment factor of 3 percent to account for this increased riskiness. Calculate the appropriate discount rate to use when evaluating DEI's project. (Do not round intermediate calculations and round your final answer to 2 decimal places. (e.g., 32.16)) Discount rate % Discount rate: 15.10 c. The manufacturing plant has an eight-year tax life, and DEI uses straight-line depreciation. At the end of the project (that is, the end of year 5), the plant and equipment can be scrapped for $4.9 million. What is the aftertax salvage value of this plant and equipment? (Enter your answer in dollars, not millions of dollars, i.e. 1,234,567. Do not round intermediate calculations.) Aftertax salvage value d. $ The company will incur $7,200,000 in annual fixed costs. The plan is to manufacture 19,000 RDSs per year and sell them at $11,000 per machine; the variable production costs are $9,600 per RDS. What is the annual operating cash flow (OCF) from this project? (Enter your answer in dollars, not millions of dollars, i.e. 1,234,567.) Aftertax salvage value: 7,643,600.0 0 Operating cash flow $ Cash flow: 13,563,200.00 e. DEI's comptroller is primarily interested in the impact of DEI's investments on the bottom line of reported accounting statements. What will you tell her is the accounting break-even quantity of RDSs sold for this project? (Do not round intermediate calculations and round your final answer to nearest whole number.) Break-even quantity units Units: 8029 f. Finally, DEI's president wants you to throw all your calculations, assumptions, and everything else into the report for the chief financial officer; all he wants to know is what the RDS project's internal rate of return (IRR) and net present value (NPV) are. (Enter your answer in dollars, not millions of dollars, i.e. 1,234,567. Do not round intermediate calculations and round your final answers to 2 decimal places. (e.g., 32.16)) IRR NPV IRR: 24.23 NPV: 9,281,343.80 % $