please ignore first 3 points

You are presented with an Excel sheet with the required financial information. The first sheet needs to be edited to indicate what the effect will be if the share dilution and share sales were to be completed, or if not. Your forecast column will thus need to be able to change its calculation between the two scenarios. Complete the following procedures on the workbook: Amend the income statement section to show what the earnings before interest, taxes and depreciation for each year amounted to. Add a line below the last line of the income statement to add the gross profit and net profit margin for each year respectively and calculate these numbers for each year. Add a drop-down list on the cover of the Financial Model sheet, in row 5, which can be used to navigate between the two scenarios - No dilution and 50% dilution. Complete the highlighted cell on the Forecast Coefficient sheet by calculating the compound annual rate of growth in sales by making use of the relevant Excel function. 6 Use an Excel formula in the Forecast 2022 column to calculate the relevant line item based on the change in scenario and linking it to the Forecast Coefficient sheet. Complete the key ratio section of the Financial Model and add a column in which you need to make use of spark lines to visually compare the 2022 forecasted ratios with the industry averages, which is given on the Industry Averages sheet. Add a section which includes the following charts: O A line chart to indicate the change in net profit margin for the four years. Add a trend line to the chart. o A pie chart to indicate the split in capital structure. HPFM440-1-JanJun2022-SuppSA2-GC-V1-04022022 Group all the different parts of the financial statements as well as the equity and key ratio sections on the financial model sheet. 1 2 3 4 5 6 10 11 12 Operating costs before depreciation 13 EBITDA 14 Depreciation 15 FRIT 16 Interest Payments EBT 17 18 Taxation 19 Net Profit (EAC) 20 21 22 22 23 24 25 26 27 28 32 33 34 35 36 47 48 29 Inventory 30 Cash and cash equivalents 31 Total Current Assets Property, plant and equipment Accumulated Depreciation Total Non-Current Assets Total Assets Liabilities and equity 49 50 37 Trade Creditors 38 Other Short Term Finance 39 Total Current Liabilities 40 Long term bonds 41 42 43 44 45 46 53 54 Turnover Cost Of Sales Gross Profit 63 64 65 66 67 68 69 70 71 Ordinary share dividend Addition to retained earnings 51 Change in Accounts Receivable 52 Change in Inventory Change in Accounts Payable Total Cash Flows from Operations GP Margin Net Profit Margin 72 23 Assets Trade Debtors 55 Cash Flows from Investing 56 Acquisition of fixed assets 57 Total Cash Flows from Investing 58 Cash Flows from Financing 59 Change in Other Short Term Finance 78 79 60 Change in Long term bonds 61 Dividend payment 62 Change in Common Stock Total Non-Current Liabilities Ordinary Share Capital. Retained income Total ordinary equity Total Liabilities and Equity 98 99 Cash Flows from Operations Net Income Depreciation Expense 100 101 102 103 104 105 Outstanding ordinary shares Market Value per Share 74 75 Book Value per Share 76 77 Earnings per Share Total Cash Flows from Financing Net Change in Cash Balance 80 81 82 83 Currennt Ratio 84 Profit Margin 85 Inventory Turnover Cash at the beginning of the year Cash at the end of the year 86 Days Sales Outstanding 87 Total Assets Turnover 88 Fixed asset turnover 89 Debt Ratio 90 Times Interest Earned 91 Return on Assets 92 93 94 Market/book 95 96 97 Return on equity Price/ earnings B Gabex (Pty) Ltd Financial Model of Gabex (Pty) Ltd For the year ended,2019 to 2022 Actual 2019 Income Statement 87,013 200 75 380,100 11,633,100 7,896,353 3,736,748 1,201,350 2.535,398 1,470,105 1.065.293 298 282 767,011 115.052 651,959 13.37% 0.88% Balance Sheet 16,201,050 11,775,900 1,953,989 29,930,939 6,751 200 1,201 350 5.549,850 35,480,789 14,805,090 226 200 15,031,290 4,634 550 (8,439,000) (8,439,000) 282,750 5.793,188 (143.814) 18,750,000 24,682,123 2,238,749 Actual 2020 2,238,749 Equity Value (R) 150,000.00 4,634,550 15,000,000 814,949 15,814.949 35,480,789 Cash Flow Statement 958,763 1.501,688 (20,251,313) (14,719,875) 18,506 363 (14,004,374) 120.00 77,876.760 68,760,960 9,115,800 6,120,936 2,994,864 961,080 2,033,784 1,188,960 844 824 228,102 616.722 92,508 524,213 10,500,108 10,620,480 11.71% 0.79% 21,120,588 5,400,950 2,162,430 3,238 520 24,359,108 10,752,084 300,072 11,052.156 3,001,068 3,001,068 12,000,000 12,000,000 26,053,224 Relevant Charts 1.953.989 1,953,989 150,000.00 144.00 Key Ratios Actual 2020 Forecast 2021 Industry Average Actual 2021 109 276,335 96,300 870 12,975,465 8,865,975 4,109,490 1201,350 2,908,140 1,493,670 1.414.470 396,052 1,018.418 152,763 865 656 00 11.87% 0.93% 8.594,750 12,380,625 30,187,985 5,626,000 4204,725 1,421,275 31,609,260 11.501,088 475 925 11,977,013 1,876 225 1,876,225 15,000,000 17,756,022 31,609,260 9212610 (6 201,362) 150.000.00 180.00 Difference 0.00X 0% 0.00X 0.00 Days 0.00X 0.00X 0% 0.00X 0% 0% 0.00X 0.00X Forecast 2022 #N/A #NA #N/A #N/A #NA 150,000.00 2021 Forecast vs Industry Average #N/A #NA #NA #NA #N/A #N/A #NA 200.00 G 1 2 Turnover 3 Cost Of Sales 4 Operating costs 5 Interest Paymen 6 Trade Debtors 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Inventory Property, plant a Accumulated De Trade Creditors Other Short Tern Long term bonds No Dilution -0.50% 5% 5% 0% 20% -0.50% -100% -10.00% Unsaved changes 50% Dilution and sale of all shares 32% 30% 15% -5% 32% 32% 30% 36% 30% -100% -10.00% Save You are presented with an Excel sheet with the required financial information. The first sheet needs to be edited to indicate what the effect will be if the share dilution and share sales were to be completed, or if not. Your forecast column will thus need to be able to change its calculation between the two scenarios. Complete the following procedures on the workbook: Amend the income statement section to show what the earnings before interest, taxes and depreciation for each year amounted to. Add a line below the last line of the income statement to add the gross profit and net profit margin for each year respectively and calculate these numbers for each year. Add a drop-down list on the cover of the Financial Model sheet, in row 5, which can be used to navigate between the two scenarios - No dilution and 50% dilution. Complete the highlighted cell on the Forecast Coefficient sheet by calculating the compound annual rate of growth in sales by making use of the relevant Excel function. 6 Use an Excel formula in the Forecast 2022 column to calculate the relevant line item based on the change in scenario and linking it to the Forecast Coefficient sheet. Complete the key ratio section of the Financial Model and add a column in which you need to make use of spark lines to visually compare the 2022 forecasted ratios with the industry averages, which is given on the Industry Averages sheet. Add a section which includes the following charts: O A line chart to indicate the change in net profit margin for the four years. Add a trend line to the chart. o A pie chart to indicate the split in capital structure. HPFM440-1-JanJun2022-SuppSA2-GC-V1-04022022 Group all the different parts of the financial statements as well as the equity and key ratio sections on the financial model sheet. 1 2 3 4 5 6 10 11 12 Operating costs before depreciation 13 EBITDA 14 Depreciation 15 FRIT 16 Interest Payments EBT 17 18 Taxation 19 Net Profit (EAC) 20 21 22 22 23 24 25 26 27 28 32 33 34 35 36 47 48 29 Inventory 30 Cash and cash equivalents 31 Total Current Assets Property, plant and equipment Accumulated Depreciation Total Non-Current Assets Total Assets Liabilities and equity 49 50 37 Trade Creditors 38 Other Short Term Finance 39 Total Current Liabilities 40 Long term bonds 41 42 43 44 45 46 53 54 Turnover Cost Of Sales Gross Profit 63 64 65 66 67 68 69 70 71 Ordinary share dividend Addition to retained earnings 51 Change in Accounts Receivable 52 Change in Inventory Change in Accounts Payable Total Cash Flows from Operations GP Margin Net Profit Margin 72 23 Assets Trade Debtors 55 Cash Flows from Investing 56 Acquisition of fixed assets 57 Total Cash Flows from Investing 58 Cash Flows from Financing 59 Change in Other Short Term Finance 78 79 60 Change in Long term bonds 61 Dividend payment 62 Change in Common Stock Total Non-Current Liabilities Ordinary Share Capital. Retained income Total ordinary equity Total Liabilities and Equity 98 99 Cash Flows from Operations Net Income Depreciation Expense 100 101 102 103 104 105 Outstanding ordinary shares Market Value per Share 74 75 Book Value per Share 76 77 Earnings per Share Total Cash Flows from Financing Net Change in Cash Balance 80 81 82 83 Currennt Ratio 84 Profit Margin 85 Inventory Turnover Cash at the beginning of the year Cash at the end of the year 86 Days Sales Outstanding 87 Total Assets Turnover 88 Fixed asset turnover 89 Debt Ratio 90 Times Interest Earned 91 Return on Assets 92 93 94 Market/book 95 96 97 Return on equity Price/ earnings B Gabex (Pty) Ltd Financial Model of Gabex (Pty) Ltd For the year ended,2019 to 2022 Actual 2019 Income Statement 87,013 200 75 380,100 11,633,100 7,896,353 3,736,748 1,201,350 2.535,398 1,470,105 1.065.293 298 282 767,011 115.052 651,959 13.37% 0.88% Balance Sheet 16,201,050 11,775,900 1,953,989 29,930,939 6,751 200 1,201 350 5.549,850 35,480,789 14,805,090 226 200 15,031,290 4,634 550 (8,439,000) (8,439,000) 282,750 5.793,188 (143.814) 18,750,000 24,682,123 2,238,749 Actual 2020 2,238,749 Equity Value (R) 150,000.00 4,634,550 15,000,000 814,949 15,814.949 35,480,789 Cash Flow Statement 958,763 1.501,688 (20,251,313) (14,719,875) 18,506 363 (14,004,374) 120.00 77,876.760 68,760,960 9,115,800 6,120,936 2,994,864 961,080 2,033,784 1,188,960 844 824 228,102 616.722 92,508 524,213 10,500,108 10,620,480 11.71% 0.79% 21,120,588 5,400,950 2,162,430 3,238 520 24,359,108 10,752,084 300,072 11,052.156 3,001,068 3,001,068 12,000,000 12,000,000 26,053,224 Relevant Charts 1.953.989 1,953,989 150,000.00 144.00 Key Ratios Actual 2020 Forecast 2021 Industry Average Actual 2021 109 276,335 96,300 870 12,975,465 8,865,975 4,109,490 1201,350 2,908,140 1,493,670 1.414.470 396,052 1,018.418 152,763 865 656 00 11.87% 0.93% 8.594,750 12,380,625 30,187,985 5,626,000 4204,725 1,421,275 31,609,260 11.501,088 475 925 11,977,013 1,876 225 1,876,225 15,000,000 17,756,022 31,609,260 9212610 (6 201,362) 150.000.00 180.00 Difference 0.00X 0% 0.00X 0.00 Days 0.00X 0.00X 0% 0.00X 0% 0% 0.00X 0.00X Forecast 2022 #N/A #NA #N/A #N/A #NA 150,000.00 2021 Forecast vs Industry Average #N/A #NA #NA #NA #N/A #N/A #NA 200.00 G 1 2 Turnover 3 Cost Of Sales 4 Operating costs 5 Interest Paymen 6 Trade Debtors 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Inventory Property, plant a Accumulated De Trade Creditors Other Short Tern Long term bonds No Dilution -0.50% 5% 5% 0% 20% -0.50% -100% -10.00% Unsaved changes 50% Dilution and sale of all shares 32% 30% 15% -5% 32% 32% 30% 36% 30% -100% -10.00% Save