this expmles
this is Q
this are exmples
REQUIRED: You must highlight your answers in Parts 2 to 5 : You are not to alter the Given information or format in any way. You must use Order of Operations in making determinations: DO NOT USE AVERAGE. 1. Rename the 4 sheets as: Balance Sheet Income Statement Ratio Analysis Loan Amort Table 2. On the Balance Sheet, perform Vertical Analysis for 2022 for: Merchandise inventory 3. On the Balance Sheet, determine both the Dollar Change and the Percentage Change for: Total Long Term Assets 4. On the Income Statement, perform Vertical Analysis for 2022 for: Total operating expenses 5. On the Income Statement, perform the Dollar Change and the Percentage Change for: Net Sales 6. Perform 2022 Ratio Analysis for: Net Profit Margin Return on Assets Quick Ratio (Acid Test) Total Asset Turnover 7. Use Sheet\#4 and prepare a Loan Amortization Table per the Given information. Sheet\#4 has the Loan problem information. Label sheet\#4 "Loan Amort Table." ACT202L-A: Fall 2023: Final Exam: Version A: Comparative lincome Statements: 2021 and 2022 \begin{tabular}{|c|c|c|} \hline \multicolumn{3}{|l|}{ Given: } \\ \hline Face Value or Par Value: & $ & 800,000.00 \\ \hline No. of years of bond: & & 12 \\ \hline No. of periods: & & 24 \\ \hline Coupon Rate: & & 7.5% \\ \hline Semi-annual coupon rate: & & 3.8% \\ \hline Market Rate: & & 6% \\ \hline Semi-annual market rate: & & 3.0% \\ \hline PMT: & s & 30,000.00 \\ \hline Sales Price of Bond: & $ & 901,613.25 \\ \hline PV of the Face Value: & $ & 393,546.99 \\ \hline PV of the Coupon PMTs & $ & 508,066.26 \\ \hline Sales Price of Bond: & $ & 901,613.25 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline Date & & PMT & & \begin{tabular}{l} Interest \\ Expense \end{tabular} & & \begin{tabular}{l} Premium \\ Amortization \end{tabular} & & \begin{tabular}{l} namortized \\ Premium \end{tabular} & & rrying Value \\ \hline 1/1/2023 & & & & & & & 3 & 101,613.25 & $ & 901,613.25 \\ \hline 6/30/2023$ & $ & 30,000.00 & $ & 27,048.40 & $ & 2,951.60 & $ & 98,661.65 & $ & 898,661.65 \\ \hline 12/31/2023$ & $ & 30,000.00 & $ & 26,959.85 & $ & 3,040.15 & $ & 95,621.50 & $ & 895,621.50 \\ \hline 6/30/2024$ & $ & 30,000.00 & $ & 26,868.64 & $ & 3,131,36 & $ & 92,490.14 & $ & 892,490,14 \\ \hline 12/31/2024$ & $ & 30,000.00 & $ & 26,774.70 & $ & 3,225.30 & $ & 89,264.85 & $ & 889,264.85 \\ \hline 6/30/2025$ & $ & 30,000.00 & $ & 26,677.95 & $ & 3,322.05 & $ & 85,942.79 & $ & 885,942.79 \\ \hline 12/31/2025$ & $ & 30,000.00 & $ & 26,578.28 & $ & 3,421.72 & $ & 82,521.08 & $ & 882,521.08 \\ \hline 6/30/2026$ & $ & 30,000.00 & $ & 26,475.63 & $ & 3,524.37 & $ & 78,996.71 & $ & 878,996.71 \\ \hline 12/31/2026$ & $ & 30,000.00 & $ & 26,369.90 & $ & 3,630.10 & $ & 75,366.61 & $ & 875,366.61 \\ \hline 6/30/2027$ & $ & 30,000.00 & $ & 26,261.00 & $ & 3,739.00 & $ & 71,627.61 & $ & 871,627.61 \\ \hline 12/31/2027$ & $ & 30,000,00 & $ & 26,148.83 & $ & 3,851.17 & $ & 67,776,44 & $ & 867,776.44 \\ \hline 6/30/2028$ & $ & 30,000.00 & $ & 26,033.29 & $ & 3,966.71 & $ & 63,809.73 & $ & 863,809.73 \\ \hline 12/31/2028$ & $ & 30,000.00 & $ & 25,914.29 & $ & 4,085.71 & $ & 59,724.02 & 3 & 859,724.02 \\ \hline 6/30/2029$ & $ & 30,000.00 & $ & 25,791.72 & $ & 4,208.28 & $ & 5,515.74 & s & 855,515.74 \\ \hline 12/31/2029$ & $ & 30,000.00 & $ & 25,665.47 & $ & 4,334.53 & $ & 51,181.22 & $ & 851,181.22 \\ \hline 6/30/2030$ & $ & 30,000.00 & $ & 25,535.44 & $ & 4,464,56 & $ & 46,716.65 & $ & 846,716.65 \\ \hline 12/31/2030$ & $ & 30,000,00 & $ & 25,401.50 & s & 4,598.50 & $ & 42,118.15 & $ & 842,118.15 \\ \hline 6/30/2031$ & $ & 30,000.00 & $ & 25,263.54 & $ & 4,736.46 & $ & 37,381.70 & $ & 837,381.70 \\ \hline 12/31/2031$ & $ & 30,000,00 & $ & 25,121.45 & $ & 4,878.55 & $ & 32,503.15 & $ & 832,503.15 \\ \hline 6/30/2032$ & $ & 30,000.00 & $ & 24,975.09 & $ & 5,024.91 & $ & 27,478.24 & $ & 827,478.24 \\ \hline 12/31/2032$ & $ & 30,000,00 & $ & 24,824.35 & s & 5,175,65 & $ & 22,302.59 & $ & 822,302.59 \\ \hline 6/30/2033$ & $ & 30,000.00 & $ & 24,669.08 & $ & 5,330.92 & $ & 16,971.67 & $ & 816,971.67 \\ \hline 12/31/2033$ & $ & 30,000.00 & $ & 24,509.15 & $ & 5,490.85 & $ & 11,480.82 & $ & 811,480.82 \\ \hline 6/30/2034$ & $ & 30,000.00 & $ & 24,344,42 & $ & 5,655.58 & $ & 5,825.24 & $ & 805,825.24 \\ \hline \multirow{2}{*}{\multicolumn{2}{|c|}{12/31/2034$$}} & 30,000.00 & $ & 24,174.76 & $ & 5,825.24 & $ & 0.00 & $ & 800,000.00 \\ \hline & & 20,000.00 & $ & 618,386.75 & $ & 01,613.25 & & & & \\ \hline \end{tabular} 1/1/2023 Cash 6/30/2023 Interest expense Premium on BP Cash 12/31/2023 Interest expense Premium on BP Cash 12/31/2034 Interest expense Premium on BP Cash Bond Payable 1/1/2027 Call Price: Bond Payable Premium on BP 1/1/2027 Call Price: Bond Payable Premium on BP Loss on retirement Cash \$ 901,613.25 $800,000.00 $101,613.25 \$ 27,048.40 $2,951.60 \$ 30,000.00 \$ 26,959.85 $3,040.15 $30,000.00 \$ 24,174.76 \$ 5,825.24 $30,000.00 $800,000.00 $800,000.00 $870,000.00 $800,000.00 $75,366.61 $870,000.00 CashGainonretirement$870,000.00$5,366.61 110 1.10 $800,000.00 $75,366.61 $4,633.39 $880,000.00 \begin{tabular}{lrr} Given: & & \\ Installment Loan: & $ & 500,000.00 \\ No. of Year/Periods: & & 10 \\ Interest Rate: & & 7.0% \\ Future Value: & $ & \\ Cash PMT: & $ & 71,188.75 \\ & $ & 71,188.75 \\ & $ & 71,188.75 \\ & $ & 71,188.75 \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline \multirow[t]{2}{*}{ PMTH } & \multirow{2}{*}{\begin{tabular}{l} Date \\ 1/1/2023 \end{tabular}} & \multirow[t]{2}{*}{ PMT } & \multicolumn{2}{|c|}{ Interest Expense } & \multicolumn{2}{|c|}{\begin{tabular}{c} Note Payable or \\ Principal \end{tabular}} & \multicolumn{2}{|c|}{\begin{tabular}{l} Outstanding \\ Balance \end{tabular}} \\ \hline & & & & & & & $ & 500,000,00 \\ \hline 1 & 12/31/2023$ & 71,188.75 & $ & 35,000.00 & $ & 36,188.75 & $ & 463,811.25 \\ \hline 2 & 12/31/2024$ & 71,188.75 & $ & 32,466.79 & $ & 38,721.96 & $ & 425,089.28 \\ \hline 3 & 12/31/2025$ & 71,188.75 & $ & 29,756.25 & $ & 41,432.50 & $ & 383,656,78 \\ \hline 4 & 12/31/2026$ & 71,188.75 & $ & 26,855.97 & $ & 44,332.78 & $ & 339,324.01 \\ \hline 5 & 12/31/2027$ & 71,188.75 & $ & 23,752.68 & $ & 47,436.07 & $ & 291,887.94 \\ \hline 6 & 12/31/2028$ & 71,188.75 & $ & 20,432.16 & $ & 50,756.60 & $ & 241,131.34 \\ \hline 7 & 12/31/2029$ & 71,188.75 & $ & 16,879.19 & $ & 54,309.56 & $ & 186,821.78 \\ \hline 8 & 12/31/2030$ & 71,188.75 & $ & 13,077.52 & $ & 58,111.23 & $ & 128,710.56 \\ \hline 9 & 12/31/2031$ & 71,188.75 & $ & 9,009.74 & $ & 62,179.01 & $ & 66,531.54 \\ \hline \multirow[t]{2}{*}{10} & 12/31/2032$ & 71,188.75 & $ & 4,657.21 & $ & 66,531.54 & $ & 0.00 \\ \hline & $ & 711,887.51 & $ & 211,887.51 & $ & 500,000.00 & & \\ \hline \end{tabular} 1/1/2023 Cash 12/31/2023 Interest Expense Note Payable $500,000.00 Note Payable \begin{tabular}{ll} $ & 35,000.00 \\ $ & 36,188.75 \end{tabular} $ 71,188.75 ACT202L-A: Fall 2023 - Final Exam: Prepare Installment Loan Table: Version A: GIVEN: On January 1, 2023, the National Company signed a $200,000,5.0% installment note, to be repaid over 20 years once monthly with the first payment on December 31, 2023, and then at the end of every following year thereafter until the loan is paid in full. REQUIRED: 1) List the Given information and determine the annual cash payment. 2) Prepare the Loan Amortization Table. Prepare the Loan Amortization Table on either this worksheet or use an additional worksheet in this Excel file and label the tab appropriately. Comparative Balance Sheets: 2021 and 2022 Comparative Income Statements: 2021 and 2022 Graded CaseH2: Fall 2023: Ratio Analysis Graded Casen2: Fall 2023: Profitability fatios Gross Profit Ratio Net Profit Margin Return on Assets Return on Equity Earnings per Share Price Eanings Ratie Liguiffity Feties Current Ratio Quick Ratio (Adid-Test) Receivables Tumever Days Sales Outstanding Imventory Tumover Days Sales in inventory Solvency Patios Debt to Assets Debt to Equity Times interest Earned Gross profit/Net sales revenue Net income/Net sales revenue Net income/Ave total ansets Net income/Ave shareholders' equity Net income-pS OW/Ave Shares O/S Price/tes 2022 30.6 3.2x 4.8x 13.3% 3.n 19.6 Current assets/Current liabilites Quick assets/Current Liabilities Net sales revectue/Ave Ah 365/Receivabies tumover coos/Ave inventory 365/ Imventory tumover 1.12 0.37 14.69 24.9 6.42 56.9 Opereting Efficiency Total Asset Turnover Net sales/ Ave total asiets 1.54 0.34 Total debt/Total shareholders' equity 0.62 0.88 Operating income/linterest expense 1.60 0.88 6.36
