I need help with capital budget analysis, mostly recommendations andthe top part of tab2.

Instructions: Using the company information provided below, complete the following two tabs in this MS Excel Workbook: - Computation of the company's estimated cost of equity capital, rE, and weighted average cost of capital, rWACC - NPV (capital budgeting) analysis of the company's proposed investment in a new Product B The background paper, Capital Budgeting and the Cost of Capital, provides useful guidance for completing this assignment. Based on the results of your NVP Analysis, summarize your recommendations to management regarding its contemplated introduction of the new pro Limit the length of your response to 75 words. Replace the text in this cell with your response. Company information: North American Manufacturing Company is a U.S.-based publicly traded company, whose stock is listed on a national securities exchange. Management looked up the stock's historical (beta) at a popular financial Web search engine and obtained this additional information: 1.20 Historical (beta) of the company's common stock Current market interest rate on the company's new borrowings, rD 0.095 (9.5 percent) Company's combined effective income tax rate, t 0.400 (40.0 percent) Management's estimate of the expected rate of return on the \"market portfolio,\" rM 0.125 (12.5 percent) Management's estimate of the risk-free interest rate, rF 0.040 (4.0 percent) The balance sheet of the company as of its most recent fiscal year end reflects management's targeted capital structure for the company. That balance sheet reports the following liability and shareholders' equity balances: ### Notes payable to banks - current portion Bonds payable - current portion 65,000,000 Notes payable to banks - noncurrent portion ### Bonds payable - noncurrent portion ### Common stock, at par 30,000,000 ### Additional paid-in capital Treasury stock (45,000,000) ### Retained earnings Product-investment information: Cost of recently completed test-marketing of Product B $ Costs of previously incurred Product B research and development (R&D) costs $ 3,000,000 Management's estimate of the economic life of Product B Cost of additional machinery and equipment (M&E) needed to manufacture Product B Fair value of vacant building owned, to be used as Product B manufacturing facility Estimated residual (fair) value of M&E at end of investment (Product B's economic life) 220,000 5.0 years ### $ 2,000,000 (Note 1) ### Note 1 - The vacant building is fully depreciated; no significant changes in the value of building over Product B investment period expected As such, the projected net proceeds from the assumed end-of-investment disposal of the building is $1,200,000 [i.e., $2.0 million x (1 - 0.4 Management's projections: Probability-weighted expected sales of Product B: Year 1 Units ### Revenue ### Variable cost (VC) per unit $ 7.00 Incremental fixed costs (FC), other than depreciation of M&E ### Erosion of existing Product A (contribution margin) (Note 2) Required end-of-year balance of net working capital Year 2 Year 3 3,600,000 ### $ 7.30 ### $ Year 4 ### ### ### ### 7.70 $ 8.00 ### ### ### $ 5,500,000 ### ### ### $ 6,000,000 ### ### Note 2 - Projected adverse effects on the profitability of the company's existing Product A, resulting from introduction of new Product B The facilitator will grade this assignment, assigning up to 100 points for it as follows: Maximum Earned Accuracy, completeness, and clear presentation of: - Business' cost of capital, including related information input and computations 25 25 points - Incremental cash flows attributable to proposed investment and capital budgeting analysis, including related information input and computations, and investment decision reached Total points 75 100 75 100 bs in this MS Excel Workbook: weighted average cost of capital, rWACC in a new Product B useful guidance for completing this assignment. ns to management regarding its contemplated introduction of the new product. pany, whose stock is listed on a national securities exchange. eb search engine and obtained this additional information: (9.5 percent) (40.0 percent) (12.5 percent) (4.0 percent) s management's targeted capital structure for the company. nces: years (Note 1) e value of building over Product B investment period expected nt disposal of the building is $1,200,000 [i.e., $2.0 million x (1 - 0.40)] Year 5 ### ### $ 8.30 ### ### $ 's existing Product A, resulting from introduction of new Product B - North American Manufacturing Company Capital Budgeting Analysis - Proposal for New Product B Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Cash flows other than depreciation tax shield: Machinery and equipment (M&E): (A) - Acquisition and installation costs ("original tax basis") (B) - Proceeds from disposal, net of capital gain tax (Note 1) (C) Opportunity cost of existing facility, net of capital gain tax (D) Incremental (investment) or reduction in net working capital (E) After-tax income before tax-basis "cost recovery" of M&E (Note 2) (F) Total cash flows other than depreciation tax shield (G) Discount rate (H) Discount factors 1.0000 (I) Discounted present value (PV) of cash flows (J) Total discounted PV Depreciation tax shield: (K) Depreciation tax shield on M&E $ - (L) Discount rate (M) Discount factors (N) Discounted present value (PV) of cash flows (O) Total discounted PV (P) Net present value (NPV) of investment $ - (Q) Total undiscounted net cash flows (R) Total discounted net cash flows (S) Internal rate of return (IRR) Err:523 (T) Profitability index (PI) #DIV/0! Note 1 - Complete the Computation of Projected Proceeds and Capital Gain Tax from Assumed End-of-Investment Sale of M&E, below Note 2 - Complete the Projection of Income and Depreciation Tax Shield, below Product B Proposal - Projection of Income and Depreciation Tax Shield Note 1 Year 1 Year 2 Year 3 Year 4 Year 5 (A) Projected number of units produced and sold (B) Projected sales revenue (C) Projected variable cost (VC) per unit (D) Projected total VC (E) Projected incremental fixed costs, other than depreciation of M&E (F) Total operating expenses, other than depreciation of M&E (G) Erosion of existing Product A (contribution margin) (H) Income before tax-basis "cost recovery" of M&E (I) Combined effective income tax rate (J) Taxes on income before tax-basis "cost recovery" of M&E (K) After-tax income before tax-basis "cost recovery" of M&E (L) Tax-basis "cost recovery" percentage of M&E (M) Tax-basis "cost recovery" Note 2 14.28% $ (L) x Original cost of (investment in) M&E: - $ 24.49% - 17.49% $ - 12.50% $ - 8.92% $ - $ - (N) Tax benefit of tax-basis "cost recovery" (i.e., depreciation tax shield) (O) Cumulative tax-basis cost recovery of M&E Note 3 Note 1 - This capital budgeting analysis uses nominal (rather than real) cash flows and discount rates, as is common practice Note 2 - The cost recovery percentages included in this analysis are those set forth by IRS regulations for "7-year Class Life" property) Note 3 - Use the cumulative tax-basis cost recovery amount to compute the proceeds, net of capital gain tax, from assumed disposal of M&E in Year 5 (below) Proposal for New Product B - Computation of Projected Proceeds and Capital Gain Tax from Assumed End-of-Investment Sale of M&E (A) Projected fair value of M&E in final period of NPV analysis (assumed gross proceeds) $ - (B) Original cost (taxable basis) of M&E (C) Cumulative allowable \"cost recovery\" through final period of NPV analysis (D) Adjusted taxable basis of M&E (E) Taxable "capital gain" (F) Combined effective income tax rate (G) Income tax on "capital gain" (H) Projected net proceeds from assumed end-of-investment sale of M&E $