corporate finance, ignore the 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. 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 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. Be sure to include the following as part of your section 1 answer: Grouped view - Financial model sheet Normal view - Scenario 1 - Financial model sheet Normal view - Scenario 2 - Financial model sheet Formula view - Financial model sheet Formula view - Forecast Coefficient sheet Presentation and Layout (1 mark) (15 marks) (7 marks) (21 marks) (2 marks) (3+1=4 marks) 3 4 Turnover Cost Of Sales 11 Gross Profit 10 12 Operating costs before depreciation 13 EBITDA 14 Depreciation 15 EBIT 16 Interest Payments EBT 17 18 19 20 21 22 23 24 25 26 20 27 28 29 30 31 32 33 34 39 20 40 41 42 43 44 45 46 47 48 49 50 51 35 36 Liabilities and equity 37 38 56 57 58 59 60 61 62 63 64 F 65 66 66 52 Change in Inventory 67 67 68 53 Change in Accounts Payable 54 Total Cash Flows from Operations 69 70 Taxation Net Profit (EAC) Ordinary share dividend Addition to retained earnings 55 Cash Flows from Investing Acquisition of fixed assets Total Cash Flows from Investing Cash Flows from Financing Change in Other Short Term Finance GP Margin Net Profit Margin Assets Trade Debtors Inventory Cash and cash equivalents Total Current Assets Property, plant and equipment Accumulated Depreciation Total Non-Current Assets Total Assets Trade Creditors C Other Short Term Finance Total Current Liabilities 94 95 96 Long term bonds Total Non-Current Liabilities Ordinary Share Capital Retained income. 99 100 Total ordinary equity Total Liabilities and Equity 102 103 104 105 106 107 Cash Flows from Operations Net Income Depreciation Expense Change in Accounts Receivable 108 109 Change in Long term bonds Dividend payment 73 Market Value per Share 74 75 76 77 77 78 Change in Common Stock Total Cash Flows from Financing Net Change in Cash Balance Cash at the beginning of the year Cash at the end of the year 79 80 81 82 83 Currennt Ratio 84 Profit Margin BS Inventory Turnover 86 Days Sales Outstanding 87 Total Assets Turnover 88 Fixed asset turnover Outstanding ordinary shares 89 Debt Ratio Book Value per Share Earnings per Share 90 91 Return on Assets 92 Return on equity 93 Price/ earnings Market/book Times Interest Earned 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 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 4,634.550 15,000,000 814 949. 15.814,949 35,480,789 958,763 1.501 688 (20,251 313) (14,719,875) 18,506, 363 (14,004,374) (8,439,000) (8,439,000) Actual 2020 282,750 5,793,188 (143 814) 18,750,000 24,682,123 2,238,749 2,238,749 Equity Value (R) 150,000.00 120.00 77 876,760 68,760,960 9,115,800 6,120.936 Cash Flow Statement 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 Relevant Charts 12,000,000 26,053,224 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 4 204 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 9212,610 (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 2021 Forecast vs Industry Average #N/A #N/A #N/A #N/A #N/A #NA #NA 150,000.00 #NA #NA #NA 200.00 1 2 3 4 Operating costs 5 Interest Paymen Trade Debtors 6 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 A 50 Turnover Cost Of Sales Inventory Property, plant al Accumulated De No Dilution -0.50% 5% 5% 0% 20% Trade Creditors -0.50% Other Short Tern -100% Long term bonds -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. 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 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. Be sure to include the following as part of your section 1 answer: Grouped view - Financial model sheet Normal view - Scenario 1 - Financial model sheet Normal view - Scenario 2 - Financial model sheet Formula view - Financial model sheet Formula view - Forecast Coefficient sheet Presentation and Layout (1 mark) (15 marks) (7 marks) (21 marks) (2 marks) (3+1=4 marks) 3 4 Turnover Cost Of Sales 11 Gross Profit 10 12 Operating costs before depreciation 13 EBITDA 14 Depreciation 15 EBIT 16 Interest Payments EBT 17 18 19 20 21 22 23 24 25 26 20 27 28 29 30 31 32 33 34 39 20 40 41 42 43 44 45 46 47 48 49 50 51 35 36 Liabilities and equity 37 38 56 57 58 59 60 61 62 63 64 F 65 66 66 52 Change in Inventory 67 67 68 53 Change in Accounts Payable 54 Total Cash Flows from Operations 69 70 Taxation Net Profit (EAC) Ordinary share dividend Addition to retained earnings 55 Cash Flows from Investing Acquisition of fixed assets Total Cash Flows from Investing Cash Flows from Financing Change in Other Short Term Finance GP Margin Net Profit Margin Assets Trade Debtors Inventory Cash and cash equivalents Total Current Assets Property, plant and equipment Accumulated Depreciation Total Non-Current Assets Total Assets Trade Creditors C Other Short Term Finance Total Current Liabilities 94 95 96 Long term bonds Total Non-Current Liabilities Ordinary Share Capital Retained income. 99 100 Total ordinary equity Total Liabilities and Equity 102 103 104 105 106 107 Cash Flows from Operations Net Income Depreciation Expense Change in Accounts Receivable 108 109 Change in Long term bonds Dividend payment 73 Market Value per Share 74 75 76 77 77 78 Change in Common Stock Total Cash Flows from Financing Net Change in Cash Balance Cash at the beginning of the year Cash at the end of the year 79 80 81 82 83 Currennt Ratio 84 Profit Margin BS Inventory Turnover 86 Days Sales Outstanding 87 Total Assets Turnover 88 Fixed asset turnover Outstanding ordinary shares 89 Debt Ratio Book Value per Share Earnings per Share 90 91 Return on Assets 92 Return on equity 93 Price/ earnings Market/book Times Interest Earned 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 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 4,634.550 15,000,000 814 949. 15.814,949 35,480,789 958,763 1.501 688 (20,251 313) (14,719,875) 18,506, 363 (14,004,374) (8,439,000) (8,439,000) Actual 2020 282,750 5,793,188 (143 814) 18,750,000 24,682,123 2,238,749 2,238,749 Equity Value (R) 150,000.00 120.00 77 876,760 68,760,960 9,115,800 6,120.936 Cash Flow Statement 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 Relevant Charts 12,000,000 26,053,224 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 4 204 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 9212,610 (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 2021 Forecast vs Industry Average #N/A #N/A #N/A #N/A #N/A #NA #NA 150,000.00 #NA #NA #NA 200.00 1 2 3 4 Operating costs 5 Interest Paymen Trade Debtors 6 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 A 50 Turnover Cost Of Sales Inventory Property, plant al Accumulated De No Dilution -0.50% 5% 5% 0% 20% Trade Creditors -0.50% Other Short Tern -100% Long term bonds -10.00% Unsaved changes 50% Dilution and sale of all shares 32% 30% 15% -5% 32% 32% 30% 36% 30% -100% -10.00% Save