Really need help on this question! It is due in a couple hours and I cant figure out how to do the last page of the attached excel. PLEASE HELP

Instructions: Using the company and market information provided below, complete the following two tabs in this MS Excel Workbook: - Computation of the estimated current fair value of the note issued by the company in 20X3 - Computation of the estimated current fair value of the company's "portfolio" of debt (notes and bonds) outstanding, as reported in its fiscal year end (FYE) 20X4 balance sheet The background papers, Present Value Concepts and Bond Valuation, and Financial Statement Concepts and Financial Reporting provide useful guidance for completing this assignment. Based on the results of your computed estimate of the fair value of the company's debt "portfolio" (the second tab following this one) and the additional information provided below, indicate (i) the apparent total fair value ($US) of assets unrecognized or under-valued in the FYE 20X4 balance sheet and (ii) the possible kinds or categories of assets that could be unrecognized or under-valued and the apparent reasons for this. (In formulating your response, it may be helpful to review pages 17 - 18 of the background paper, Present Value Concepts and Bond Valuation, including the illustration on those pages and pages 38 and 41 - 47 of the background paper, Financial Statement Concepts and Financial Reporting.) Limit the length of your response to 150 words. The obtained results will assist in estimating the current value of the note issued by the company in 20x3 as well as the estimated current faire value of the company's portifolio of debt(notes and bonds) outstanding as reported in the year 20x4. Advanced Technology and Services Company Consolidated Balance Sheet December 31, 20X4 $US in millions Assets: Liabilities and shareholders' equity: Cash and cash equivalents $ 15 Accounts payable and accrued expenses $ 170 Investments securities, at cost (to be held to maturity) 75 Income taxes payable 20 Accounts receivable, net 18 Notes payable - current portion (Note 5) 10 Inventory, at lower of LIFO cost or market value 192 Bonds payable - current portion (Note 5) 50 Total current assets 300 Total current liabilities Property, plant, and equipment, at cost Less accumulated depreciation Property, plant, and equipment, net 250 1,045 Notes payable - noncurrent portion (Note 5) 100 (380) Bonds payable - noncurrent portion (Note 5) 250 665 Total liabilities Investments in "strategic partner" suppliers 600 35 Total common shareholders' equity (Note 7) Total assets $ 1,000 Total liabilities and shareholders' equity 400 $ 1,000 Excerpts from FY 20X4 financial statement footnotes . . . Note 5 - Debt The company's debt at December 31, 20X4 is comprised of notes and The amounts of debt outstanding as of December 31, 20X4 bonds payable, as follows: that is payable in each of following five years and for all remaining years thereafter in the aggregate are: ($US in millions) Weighted average Principal FYE Dec. 31, Principal due interest balance 20X5 $ 60 rate outstanding Notes payable to banks 4.00% Notes payable to other lenders 5.00% $ 54 20X6 65 56 20X7 60 6.0 percent serial bonds 300 20X8 60 Total 410 20X9 55 Less current portion (60) Thereafter 350 Total Noncurrent portion 6.00% $ 110 $ 410 In January 20X3, the company issued a $30 million note to a syndicate of banks in connection with a seven-year term loan that bears a fixed 4.25 percent interest rate. The loan is secured by the company's assets and subject to financial and other covenants, including a requirement that the company maintain a total debt ratio not exceeding 1.5:1 (or 1.50). Note 7 - Shareholders' equity The company's articles of incorporation authorize it to issue up to 25 million shares of the company's common stock, par value $5 per share. At December 31, 20X4, 22.0 million shares of the company's common stock were issued and 2.154 million of those shares were held by the company as treasury stock. The company's articles authorize it to issue up to 2.5 million shares of 8 percent preferred stock, $100 par value, for which dividends shall be cumulative. At December 31, 20X4, the company had issued no shares of preferred stock. Market information The company's 6.0 percent serial bonds are currently rated "single A" (Standard & Poors) and "A2" (Moody's Investor Services) Current market yields on "single A" corporate securities, by term to maturity are: 1 year 4.625% 6 years 6.500% 2 years 5.000% 7 years 6.875% 3 years 5.375% 8 years 7.250% 4 years 5.750% 9 years 7.625% 5 years 6.125% 10 years 8.000% The company's common stock currently trades at $25 per share on the NASDAQ. The facilitator will grade this assignment, assigning up to 100 points for it as follow Maximum Earned Complete, accurate, and clear presentation of calculations of the estimated fair value of - The specified individual company bond or note issue 10 - The company's aggregate debt \"portfolio\" 75 (1) Clear and accurate indication of the apparent total fair value ($US) of assets unrecognized or under-valued in the balance sheet and (2) clear, concise, and complete description of the possible kinds or categories of assets that could be unrecognized or under-valued and the apparent reasons for this 15 Total points 100 points - Instructions: Use the information in the first worksheet tab (Instructions and company information) to complete the analysis in this tab. Show all computations in good form and label properly all amounts presented. Compute the current fair value of the bank note syndicated in 20X3, showing separately to the nearest whole $US dollar (1) the present value of the principal (face) amount of the note and (2) the present value of related interest payments due under the note. (It may be helpful to review pages 15 - 16 of the background paper, Present Value Concepts and Bond Valuation, including the illustration on those pages.) The notes payable in 20x3 was $10 million. (1) The present value of the principal amount of the note can be obtained as follows; The PV of the principal amount= 0.8887 x $ 10 million= $8.887 million. (11) The PV of the related interest payments under under the note The related interest payments is 6% of $10 million = $600,00. Remaining semiannual interest payment periods Year of (Years to maturity maturity (1) TIMES 2) Estimated fair value (PV of debt principal Current yield on comparably outstanding and interest rated debt Semi-annual Semi- payments) Interest Annual annual ($US Rate Payment Term yield yield millions) Outstandin g debt principal at Dec. 31, 20X4 ($US millions) A Thereafter B Years (B / A) Total (1) Assume all debt outstanding requires semi-annual interest payments and matures December 31 of years disclosed. (2) Show your computation of the estimated weighted average interest rate on the debt "portfolio" using footnote information, below: Debt footnote information Weight ed averag Principal Wghtd avg e balance int rate X interes outstand Percent Percent of t rate ing of total total