corporate finance.

corporate finance

SECTION 1: 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 (50 marks) 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 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 Group all the different parts of the financial statements as well as the equity and key ratio sections on the financial model sheet. (1 mark) (15 marks) (7 marks) (21 marks) (2 marks) (3+1=4 marks) 2 6 7 10 11 12 Operating costs before depreciation 13 EBITDA 14 Depreciation 15 16 16 17 17 EBIT Interest Payments EBT 18 Taxation 19 Net Profit (EAC) 20 Ordinary share dividend 21 22 24 27 28 29 30 31 32 33 34 35 36 40 41 41 42 12 43 45 53 54 37 38 39 Total Current Liabilities 56 57 58 59 60 61 62 69 69 70 Turnover Cost Of Sales Gross Profit 71 72 73 78 79 80 81 82 83 A Addition to retained earnings GP Margin Net Profit Margin 99 100 101 102 103 104 Assets Trade Debtors 48 49 50 Depreciation Expense 51 52 105 106 107 108 109 110 111 Inventory Cash and cash equivalents Total Current Assets Property, plant and equipment Accumulated Depreciation. Total Non-Current Assets Total Assets Liabilities and equity Trade Creditors Other Short Term Finance 55 Cash Flows from Investing Acquisition of fixed assets Total Cash Flows from Investing Cash Flows from Financing Change in Other Short Term Finance Change in Long term bonds Dividend payment Change in Common Stock 63 Total Cash Flows from Financing 64 Net Change in Cash Balance 65 66 67 68 68 Long term bonds Total Non-Current Liabilities Ordinary Share Capital Retained income Total ordinary equity Total Liabilities and Equity Outstanding ordinary shares. Market Value per Share 74 75 Book Value per Share 76 Earnings per Share Cash Flows from Operations Net Income Changel Accounts Receivable Change in Inventory Change in Accounts Payable Total Cash Flows from Operations Cash at the beginning of the year Cash at the end of the year 84 85 Inventory Turnover 86 87 88 89 90 Times Interest Earned 91 Return on Assets 92 Return on equity 93 Price/ earnings 94 Market/book 95 96 97 98 Currennt Ratio Profit Margin Days Sales Outstanding Total Assets Turnover Fixed asset turnover Debt Ratio D 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 1201,350 5,549,850 35,480,789 14,805,090 226 200 15.031.290 4.634 550 4,634,550 15.000.000 814949 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) 282,750 5,793,188 (143,814) 18,750,000 24.682,123 2,238,749 Actual 2020 2,238,749 150,000.00 77,876,760 68,760,960 9,115,800 6,120,936 2.994,864 Equity Value (R) 120.00 Cash Flow Statement 961.080 2,033,784 1.188.960 844.824 228,102 616,722 92,508 524213 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 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 10,752.084 11.501,088 300,072 475.925 11.052.156 11.977.013 1.876 225 1,876,225 15,000,000 109 276,335 96,300,870 12,975,465 8,865,975 4,109,490 1201,350 2908.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 17.756,022 31,609,260 9212,610 (6 201,362) 150,000.00 180.00 0.00X 0% 0.00X 0.00 Days 0.00X 0.00X 0% 0.00X 0% 0% F Difference 2021 Forecast vs Industry Average #N/A #N/A #N/A #N/A #N/A #N/A #NA 0.00X 0.00X Forecast 2022 #NA #N/A 150,000.00 #N/A #VA #N/A 200.00 Y A 1 2 3 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 Turnover Cost Of Sales Inventory Property, plant al Accumulated De Trade Creditors Other Short Tern Long term bonds B No Dilution -0.50% 5% 5% 0% 20% -0.50% -100% -10.00% Ungqvod phongoo C 50% Dilution and sale of all shares 32% 30% 15% -5% 32% 32% 30% 36% 30% -100% -10.00% Sove D SECTION 1: 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 (50 marks) 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 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 Group all the different parts of the financial statements as well as the equity and key ratio sections on the financial model sheet. (1 mark) (15 marks) (7 marks) (21 marks) (2 marks) (3+1=4 marks) 2 6 7 10 11 12 Operating costs before depreciation 13 EBITDA 14 Depreciation 15 16 16 17 17 EBIT Interest Payments EBT 18 Taxation 19 Net Profit (EAC) 20 Ordinary share dividend 21 22 24 27 28 29 30 31 32 33 34 35 36 40 41 41 42 12 43 45 53 54 37 38 39 Total Current Liabilities 56 57 58 59 60 61 62 69 69 70 Turnover Cost Of Sales Gross Profit 71 72 73 78 79 80 81 82 83 A Addition to retained earnings GP Margin Net Profit Margin 99 100 101 102 103 104 Assets Trade Debtors 48 49 50 Depreciation Expense 51 52 105 106 107 108 109 110 111 Inventory Cash and cash equivalents Total Current Assets Property, plant and equipment Accumulated Depreciation. Total Non-Current Assets Total Assets Liabilities and equity Trade Creditors Other Short Term Finance 55 Cash Flows from Investing Acquisition of fixed assets Total Cash Flows from Investing Cash Flows from Financing Change in Other Short Term Finance Change in Long term bonds Dividend payment Change in Common Stock 63 Total Cash Flows from Financing 64 Net Change in Cash Balance 65 66 67 68 68 Long term bonds Total Non-Current Liabilities Ordinary Share Capital Retained income Total ordinary equity Total Liabilities and Equity Outstanding ordinary shares. Market Value per Share 74 75 Book Value per Share 76 Earnings per Share Cash Flows from Operations Net Income Changel Accounts Receivable Change in Inventory Change in Accounts Payable Total Cash Flows from Operations Cash at the beginning of the year Cash at the end of the year 84 85 Inventory Turnover 86 87 88 89 90 Times Interest Earned 91 Return on Assets 92 Return on equity 93 Price/ earnings 94 Market/book 95 96 97 98 Currennt Ratio Profit Margin Days Sales Outstanding Total Assets Turnover Fixed asset turnover Debt Ratio D 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 1201,350 5,549,850 35,480,789 14,805,090 226 200 15.031.290 4.634 550 4,634,550 15.000.000 814949 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) 282,750 5,793,188 (143,814) 18,750,000 24.682,123 2,238,749 Actual 2020 2,238,749 150,000.00 77,876,760 68,760,960 9,115,800 6,120,936 2.994,864 Equity Value (R) 120.00 Cash Flow Statement 961.080 2,033,784 1.188.960 844.824 228,102 616,722 92,508 524213 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 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 10,752.084 11.501,088 300,072 475.925 11.052.156 11.977.013 1.876 225 1,876,225 15,000,000 109 276,335 96,300,870 12,975,465 8,865,975 4,109,490 1201,350 2908.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 17.756,022 31,609,260 9212,610 (6 201,362) 150,000.00 180.00 0.00X 0% 0.00X 0.00 Days 0.00X 0.00X 0% 0.00X 0% 0% F Difference 2021 Forecast vs Industry Average #N/A #N/A #N/A #N/A #N/A #N/A #NA 0.00X 0.00X Forecast 2022 #NA #N/A 150,000.00 #N/A #VA #N/A 200.00 Y A 1 2 3 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 Turnover Cost Of Sales Inventory Property, plant al Accumulated De Trade Creditors Other Short Tern Long term bonds B No Dilution -0.50% 5% 5% 0% 20% -0.50% -100% -10.00% Ungqvod phongoo C 50% Dilution and sale of all shares 32% 30% 15% -5% 32% 32% 30% 36% 30% -100% -10.00% Sove D