Discuss your assumptions underlying the sales forecasts and the underlying relationships. Explain how your financial plan impacts the companys need for external capital. Your discussions should consider the internal growth rate and sustainable growth rate, in comparison to the growth rate in sales.
AutoSave OFF BE SU: Corporate Finance-G View Tell me Home Insert Draw Page Layout Formulas Data Review Calibri (Body) V 11 V ab Wrap Text Paste BI V A V E Merge & Centre V D14 fx =D11-06 A B E F C D Historical financial statements (past 4 years) 2018 2019 2017 2020 118,467,000 -72,264,870 -6,160,284 40,041,846 -1,826,594 38,215,252 -11,464,576 26,750,676 -22,738,075 4,012,601 32,910,960 122,294,000 -72,887,224 -6,726,170 42,680,606 -1,775,656 40,904,950 -12,271,485 28,633,465 -24,338,445 4,295,020 35,359,635 126,702,000 -76,147,902 -6,879,919 43,674,179 -1,784,673 41,889,507 -12,566,852 29,322,655 -24,924,256 4,398,398 36,202,573 130,011,000 -74,496,303 -7,202,609 48,312,088 -1,810403 46,501,685 -13,950,505 32,551,179 -27,668,502 4,882,677 39,753,789 23,211,675 123,258,325 146,470,000 23.439,644 127,570,356 151,010,000 23,964,358 132,265,642 156,230,000 24,200,330 132,799,770 157,000,100 2 Item 3 Income Statement 4 Revenue/Sales/ Income 5 Operating Expenses (includes Cost of goods sold) 6 Depreciation 7 Earnings Before Interest and Taxes (EBIT) 8 Interest Expense 9 Earnings Before Taxes (EBT) 10 Tax Expense (30% Au corp. rate) 11 Net Income 12 Dividends Paid 13 Reinvested earnings 14 Operating cash flow 15 16 Balance Sheet (year-end) 17 Assets 18 Networking capital (Current Assets - Current Liabilities) 19 Fixed assets 20 Total net assets 21 Labilities and Shareholders' Equity 22 Long-term debt 23 Shareholders' equity 24 Total liabilities and shareholders' equity 25 26 Sources and Uses of Funds 27 Operating cash flow 28 Increase in working capital 29 Investments in fixed ssets 30 Dividends 31 Total uses of cash 32 Required external capital 33 34 Ratios 35 Debt/Equity 36 Debt/Assets 37 Interest coverage 38 39 Other information you might find useful 40 Shares Outstanding at Period End 65,469,316 81,000,684 146,470,000 63,872,503 87,137,497 151,010,000 62,620,101 93,609,899 156,230,000 62,000,100 95,000,000 157,000,100 32,910,960 n/a n/a 22,738,075 35,359,635 227,969 11,038,201 24,338,445 35,604,615 244,980 36,202,573 524,714 11,575,205 24,924,256 37,024,175 821,602 39,753,789 235,972 7,736,737 27,668,502 35,641,212 4,112,577 80.83% 44.70% 21.92 73.30% 42.30% 24.04 66.89% 40.08% 24.47 65.26% 39.49% 26.69 324,254,097 324,254,097 324,254,097 323,726,756 Model inputs for 2021 and beyond Tax rate Interest rate of debt NWC/ Sales Fixed assets/ sales Op Ex/ sales IPayout ratio Depreciation/ Last year foxed assets Assumptions 30% AU corporate tax rate 4.00% Long-term debt loan interest rate 19% Average of 2017-2020 104% Average of 2017-2020 59.5% Average of 2017-2020 85% Average of 2017-2020 5.43% Average of 2017-2020 Enter your assumed rates as % Justify with notes on why you have chosen these rates 0.082 22.58% n/a 18.26% 0.070 85.00% 15.00% 0.088 23.41% 35.35% 18.96% 0.075 85.00% 15.00% 0.090 23.14% 33.65% 18.77% 0.077 85.00% 15.00% 0.101 25.04% 34.77% 20.73% 0.085 85.00% 15.00% 41 Earnings Per Share (EPS, cents per share) 42 Net Profit Margin(%) 43 ROE (%) 44 ROA (%) 45 Dividend per share (DPS, cents per share) 46 Dividend payout ratio (%) 47 Plowback ratio (%) 48 49 Internal growth rate = reinv/ net assets 50 GR = PlowratROEx[egetassets) 51 52 Sustainable growth rate = plowratROE 53 54 Actual Growth rate in revenue/ sales 55 56 2.74% n/a 2.84% 3.06% 2.82% 3.02% 3.11% 3.16% n/a 5.30% 5.05% 5.22% n/a 3.23% 3.60% 2.61% G H I Pro-forma financial statement, % of sales model, plug = debt Notes 2021 2022 2023 2024 2025 Forecast from base case revenues x% of sales (cell 08) x% of net foxed assets end of previous year (cell 010) Rev - COGS - Dep x% of debt at end of previous year (cell 05) EBIT-interest 30% of EBT (cell 04) EBT - Tax x% of net income (cell 09) Net income - dividends paid Operating cash flow, net income + deprec 144,000,000 -85,680,000 -7,211,028 51,108,972 -2,480,004 48,628,968 -14,588,691 34,040,278 -28,930,832 5,109,446 41,251,305 150,111,360 -89,316,259 -8,110,074 52,685,027 -3,068,326 49,616,700 -14,885,010 34,731,690 -29,518,464 5,213,227 42,841,765 156,482,086 -93,106,841 -8,454,266 54,920,979 -3,159,963 51,761,016 -15,528,305 36,232,711 -30,794,182 5,438,530 44,686,977 163,123,186 -97,058,296 -8,813,065 57,251,825 -3,255,326 53,996,499 -16,198,950 37,797,550 -32,124,137 5,673,412 46,610,614 170,046,134 -101,177450 -9,187,091 59,681,593 -3,354,574 56,327,019 -16,898,106 39,428,913 -33,510,633 5,918,280 48,616,005 x% of sales (cell 06) x% of sales (cell 07) NWC + Fixed assets 27,460,800 149,356,800 176,817,600 28,626,236 155,695,503 184,321,739 29,841,134 162,303,220 192,144,354 31,107,592 169,191,368 200,298,960 32,427,798 176,371,850 208,799,648 76,708,154 100,109,446 176,817,600 78,999,067 105,322,672 184,321,739 81,383,151 110,761,202 192,144,354 83,864,345 116,434,615 200,298,960 86,446,753 122,352,895 208,799,648 plug: increases by external financing increases by reinvested earnings should equal to total net assets Sources and Uses of Funds Operating cash flow Increase in working capital Investments in foed ssets Dividends Total uses of cash Required external capital 41,251,305 3,260,470 23,768,058 28,930,832 55,959,360 14,708,054 42,841,765 1,165,436 14,448,777 29,518,464 45,132,677 2,290,912 44,686,977 1,214,897 15,061,983 30,794,182 47,071,062 2,384,085 46,610,614 1,266,458 15,701,213 32,124,137 49,091,809 2.481,194 48,616,005 1,320,206 16,367,573 33,510,633 51,198,413 2,582,408 Ratios Debt/Equity Debt/Assets Interest coverage 76.62% 43.38% 20.61 75.01% 42.86% 17.17 73.48% 42.36% 17.38 72.03% 41.87% 17.59 70.65% 41.40% 17.79 Other information Shares Outstanding at Period End 323,726,756 323,726,756 323,726,756 323,726,756 323,726,756 Earnings Per Share (EPS, cents per share) Net Profit Margin (%) ROE (%) ROA (%) Dividend per share (DPS, cents per share) Dividend payout ratio (%) Plowback ratio (%) 0.105 23.64% 35.83% 19.25% 0.089 84.99% 15.01% 0.107 23.14% 34.69% 18.84% 0.091 84.99% 15.01% 0.112 23.15% 34.40% 18.86% 0.095 84.99% 15.01% 0.117 23.17% 34.13% 18.87% 0.099 84.99% 15.01% 0.122 23.19% 33.86% 18.88% 0.104 84.99% 15.01% Internal growth rate= reinv/ net assets IGR = Plowratxo Ex[egetassets) 2.89% 3.05% 2.83% 2.98% 2.83% 2.98% 2.83% 2.98% 2.83% 2.98% Sustainable growth rate = plowratxROE 5.38% 5.21% 5.16% 5.12% 5.08% Growth rate in forecast revenue/ sales 10.76% 4.24% 4.24% 4.24% 4.24% AutoSave OFF BE SU: Corporate Finance-G View Tell me Home Insert Draw Page Layout Formulas Data Review Calibri (Body) V 11 V ab Wrap Text Paste BI V A V E Merge & Centre V D14 fx =D11-06 A B E F C D Historical financial statements (past 4 years) 2018 2019 2017 2020 118,467,000 -72,264,870 -6,160,284 40,041,846 -1,826,594 38,215,252 -11,464,576 26,750,676 -22,738,075 4,012,601 32,910,960 122,294,000 -72,887,224 -6,726,170 42,680,606 -1,775,656 40,904,950 -12,271,485 28,633,465 -24,338,445 4,295,020 35,359,635 126,702,000 -76,147,902 -6,879,919 43,674,179 -1,784,673 41,889,507 -12,566,852 29,322,655 -24,924,256 4,398,398 36,202,573 130,011,000 -74,496,303 -7,202,609 48,312,088 -1,810403 46,501,685 -13,950,505 32,551,179 -27,668,502 4,882,677 39,753,789 23,211,675 123,258,325 146,470,000 23.439,644 127,570,356 151,010,000 23,964,358 132,265,642 156,230,000 24,200,330 132,799,770 157,000,100 2 Item 3 Income Statement 4 Revenue/Sales/ Income 5 Operating Expenses (includes Cost of goods sold) 6 Depreciation 7 Earnings Before Interest and Taxes (EBIT) 8 Interest Expense 9 Earnings Before Taxes (EBT) 10 Tax Expense (30% Au corp. rate) 11 Net Income 12 Dividends Paid 13 Reinvested earnings 14 Operating cash flow 15 16 Balance Sheet (year-end) 17 Assets 18 Networking capital (Current Assets - Current Liabilities) 19 Fixed assets 20 Total net assets 21 Labilities and Shareholders' Equity 22 Long-term debt 23 Shareholders' equity 24 Total liabilities and shareholders' equity 25 26 Sources and Uses of Funds 27 Operating cash flow 28 Increase in working capital 29 Investments in fixed ssets 30 Dividends 31 Total uses of cash 32 Required external capital 33 34 Ratios 35 Debt/Equity 36 Debt/Assets 37 Interest coverage 38 39 Other information you might find useful 40 Shares Outstanding at Period End 65,469,316 81,000,684 146,470,000 63,872,503 87,137,497 151,010,000 62,620,101 93,609,899 156,230,000 62,000,100 95,000,000 157,000,100 32,910,960 n/a n/a 22,738,075 35,359,635 227,969 11,038,201 24,338,445 35,604,615 244,980 36,202,573 524,714 11,575,205 24,924,256 37,024,175 821,602 39,753,789 235,972 7,736,737 27,668,502 35,641,212 4,112,577 80.83% 44.70% 21.92 73.30% 42.30% 24.04 66.89% 40.08% 24.47 65.26% 39.49% 26.69 324,254,097 324,254,097 324,254,097 323,726,756 Model inputs for 2021 and beyond Tax rate Interest rate of debt NWC/ Sales Fixed assets/ sales Op Ex/ sales IPayout ratio Depreciation/ Last year foxed assets Assumptions 30% AU corporate tax rate 4.00% Long-term debt loan interest rate 19% Average of 2017-2020 104% Average of 2017-2020 59.5% Average of 2017-2020 85% Average of 2017-2020 5.43% Average of 2017-2020 Enter your assumed rates as % Justify with notes on why you have chosen these rates 0.082 22.58% n/a 18.26% 0.070 85.00% 15.00% 0.088 23.41% 35.35% 18.96% 0.075 85.00% 15.00% 0.090 23.14% 33.65% 18.77% 0.077 85.00% 15.00% 0.101 25.04% 34.77% 20.73% 0.085 85.00% 15.00% 41 Earnings Per Share (EPS, cents per share) 42 Net Profit Margin(%) 43 ROE (%) 44 ROA (%) 45 Dividend per share (DPS, cents per share) 46 Dividend payout ratio (%) 47 Plowback ratio (%) 48 49 Internal growth rate = reinv/ net assets 50 GR = PlowratROEx[egetassets) 51 52 Sustainable growth rate = plowratROE 53 54 Actual Growth rate in revenue/ sales 55 56 2.74% n/a 2.84% 3.06% 2.82% 3.02% 3.11% 3.16% n/a 5.30% 5.05% 5.22% n/a 3.23% 3.60% 2.61% G H I Pro-forma financial statement, % of sales model, plug = debt Notes 2021 2022 2023 2024 2025 Forecast from base case revenues x% of sales (cell 08) x% of net foxed assets end of previous year (cell 010) Rev - COGS - Dep x% of debt at end of previous year (cell 05) EBIT-interest 30% of EBT (cell 04) EBT - Tax x% of net income (cell 09) Net income - dividends paid Operating cash flow, net income + deprec 144,000,000 -85,680,000 -7,211,028 51,108,972 -2,480,004 48,628,968 -14,588,691 34,040,278 -28,930,832 5,109,446 41,251,305 150,111,360 -89,316,259 -8,110,074 52,685,027 -3,068,326 49,616,700 -14,885,010 34,731,690 -29,518,464 5,213,227 42,841,765 156,482,086 -93,106,841 -8,454,266 54,920,979 -3,159,963 51,761,016 -15,528,305 36,232,711 -30,794,182 5,438,530 44,686,977 163,123,186 -97,058,296 -8,813,065 57,251,825 -3,255,326 53,996,499 -16,198,950 37,797,550 -32,124,137 5,673,412 46,610,614 170,046,134 -101,177450 -9,187,091 59,681,593 -3,354,574 56,327,019 -16,898,106 39,428,913 -33,510,633 5,918,280 48,616,005 x% of sales (cell 06) x% of sales (cell 07) NWC + Fixed assets 27,460,800 149,356,800 176,817,600 28,626,236 155,695,503 184,321,739 29,841,134 162,303,220 192,144,354 31,107,592 169,191,368 200,298,960 32,427,798 176,371,850 208,799,648 76,708,154 100,109,446 176,817,600 78,999,067 105,322,672 184,321,739 81,383,151 110,761,202 192,144,354 83,864,345 116,434,615 200,298,960 86,446,753 122,352,895 208,799,648 plug: increases by external financing increases by reinvested earnings should equal to total net assets Sources and Uses of Funds Operating cash flow Increase in working capital Investments in foed ssets Dividends Total uses of cash Required external capital 41,251,305 3,260,470 23,768,058 28,930,832 55,959,360 14,708,054 42,841,765 1,165,436 14,448,777 29,518,464 45,132,677 2,290,912 44,686,977 1,214,897 15,061,983 30,794,182 47,071,062 2,384,085 46,610,614 1,266,458 15,701,213 32,124,137 49,091,809 2.481,194 48,616,005 1,320,206 16,367,573 33,510,633 51,198,413 2,582,408 Ratios Debt/Equity Debt/Assets Interest coverage 76.62% 43.38% 20.61 75.01% 42.86% 17.17 73.48% 42.36% 17.38 72.03% 41.87% 17.59 70.65% 41.40% 17.79 Other information Shares Outstanding at Period End 323,726,756 323,726,756 323,726,756 323,726,756 323,726,756 Earnings Per Share (EPS, cents per share) Net Profit Margin (%) ROE (%) ROA (%) Dividend per share (DPS, cents per share) Dividend payout ratio (%) Plowback ratio (%) 0.105 23.64% 35.83% 19.25% 0.089 84.99% 15.01% 0.107 23.14% 34.69% 18.84% 0.091 84.99% 15.01% 0.112 23.15% 34.40% 18.86% 0.095 84.99% 15.01% 0.117 23.17% 34.13% 18.87% 0.099 84.99% 15.01% 0.122 23.19% 33.86% 18.88% 0.104 84.99% 15.01% Internal growth rate= reinv/ net assets IGR = Plowratxo Ex[egetassets) 2.89% 3.05% 2.83% 2.98% 2.83% 2.98% 2.83% 2.98% 2.83% 2.98% Sustainable growth rate = plowratxROE 5.38% 5.21% 5.16% 5.12% 5.08% Growth rate in forecast revenue/ sales 10.76% 4.24% 4.24% 4.24% 4.24%
