Financial Forecast and Projection: FNS60217 Advanced Diploma of Accounting
Financial Forecast and Projection
Undertake your own research to obtain an organisation’s LATEST THREE- YEAR annual reports. You can use one from an organisation with which you are familiar or one which is freely available on the Internet (company’s website) or from ASIC or ASX.
I.e. ASX Listed companies include:
- Woolworths Ltd • Toll Holdings
- Qantas Airways • Lend Lease Group
- Goodman Group • JB HI-FI Ltd
You are the Financial Planning and Analysis manager of the company you have chosen. In order to invest a project, the company is planning to apply for a loan from the bank. You were assigned by the CEO to do the task of advising the Board of Directors about the financial forecasts and the company’s ability to meet the long-term obligations. The proposed loan details are shown below:
| Proposed Loan Details | |
| Loan Amount | $5,000,000 |
| Loan Term | 4 years |
| Interest Rate | 6.50% |
| Payment Frequency | Monthly |
Required:
- You are required to undertake your own research from the company’s website and read all the relevant information from the annual reports to understand and identify the organisational policy and procedures in regards to (not limited to) sales, inventory management, collection policy, credit, purchasing and payment policy and cost structures.
- Calculate the financial ratios for the actual three years by using the following template. You can calculate the relevant ratios based on a year-end balance instead of the average beginning and ending year balance.
- Briefly comments on the profitability, liquidity and solvency of the company based on the financial ratios you calculated in Part 2.
- You are required to identify timeframes and parameters to be used for the financial forecasts.
- You are required to use the EXCEL to include all the parameters or assumptions (i.e. key forecast drivers) used and prepare a pro forma income statement and balance sheet.
- You are required to forecast the free cash flow for the company.
- You are required to write a REPORT (refer to Notes to the Students for more information) to include all the information and workings used in the preparation of pro forma income statement and balance sheet, and free cash flow forecast
Solution
Introduction:
This report is to show if the Qantas Airways has the potential to take up the loan of $50,00,000 for four years at the rate of 6.5%. The company cash flows, and operational efficiency is judged by looking at the forecasted statements, and free cash flow of the company. The rations for profitability, solvency, and liquidity are also computed to measure the potential of its future operational efficiency and cash flows. The forecasted statements have used the method of linear trend analysis, and percentage of sales and total assets method.
Analysis:
1-Organizational Policy:
The sales of the company are generated from the passenger and freight revenue, frequent flyer marketing revenue, and redemption revenue. The company also generates revenue from contracts with customers. The company has made good progress in the market concerning the four global trends of the market. These have been to focus on the Asian markets and develop their Pilot Academy. The company has a policy of paying off the debts on time, as depicted form its payable days and inventory days.
2-Financial ratios:
| Financial Ratios | Actual Year 1-2016 | Actual Year 2-2017 | Actual Year 3-2018 |
| Return on Equity | 32% | 24% | 25% |
| Return on Assets | 6% | 5% | 5% |
| Debt to Equity Ratio | 4.13 | 3.87 | 3.71 |
| Time Interest Earned | 3.81 | 4.39 | 5.05 |
| Days in Receivables | 18.38 | 18.25 | 19.93 |
| Days in Inventory | 18.55 | 19.79 | 18.76 |
| Days in Payable | 18.12 | 17.74 | 18.71 |
| Current Ratio | 0.49 | 0.44 | 0.49 |
| Quick Ratio | 0.44 | 0.39 | 0.44 |
3-Comments on Financial Analysis:
Profitability:
In terms of profitability of the company, the company’s return on equity has been quite impressive, which is one important measure to look at It helps in understanding how much of the returns the company has returned to its shareholders. In terms of the Return on Assets, however, the return is quite lower, showing that most of the company capital is financed from debt sources and not equity sources.
Liquidity:
In terms of the liquidity of the firm, the company’s current and quick ratios show that its current assets are half the worth of its current liabilities as the ratio is below 1. Company liquidity is one important measure of identifying the strength of its ability to payback its current obligations in the future, which seems to be not good for Qantas Airways.
Solvency:
The solvency ratios of Time interest earned shows that Qantas was able to earn around 4 times more as compared to its interest expense, which is a good sign. In terms of the Days Receivables, the company receives its collections in 18 to 19 days and pays earlier than it receives, which is also one good sign of its cash flow. The company inventory is converted into sales in 19 days as well. This is also a good sign. In terms of the debt-to-equity ratio, the company debt is very high, which shows it has already acquired high debt loans.
4-Timeframes and parameters to be used for the financial forecasts
The projected income statement and balance sheet are done for the three upcoming years of 2019, 2020, and 2021. The forecasts are based on the method of the percentage of sales and assets method. The linear excel trend method is used for the computations of revenue forecasts.
5-Forecasted Income Statement & Balance Sheet Statements
Income Statement of the firm is shown below.
| QANTAS AIRWAYS LTD INCOME STATEMENT | |||
| Fiscal year ends in June. AUD in millions except per share data. | 2016 | 2017 | 2018 |
| Revenue | AUD 15,784 | AUD 15,680 | AUD 16,628 |
| Cost of revenue | AUD 6,612 | AUD 6,475 | AUD 6,828 |
| Gross profit | AUD 9,172 | AUD 9,205 | AUD 9,800 |
| Operating expenses | |||
| Sales, General and administrative | AUD 5,393 | AUD 5,335 | AUD 5,500 |
| Depreciation and amortization | AUD 1,224 | AUD 1,382 | AUD 1,528 |
| Restructuring, merger and acquisition | AUD 56 | AUD 48 | |
| Other operating expenses | AUD 1,416 | AUD 1,409 | AUD 1,611 |
| Total operating expenses | AUD 8,089 | AUD 8,174 | AUD 8,639 |
| Operating income | AUD 1,083 | AUD 1,031 | AUD 1,161 |
| Interest Expense | AUD 284 | AUD 235 | AUD 230 |
| Other income (expense) | AUD 625 | AUD 385 | AUD 460 |
| Income before income taxes | AUD 1,424 | AUD 1,181 | AUD 1,391 |
| Provision for income taxes | AUD 395 | AUD 328 | AUD 411 |
| Net income from continuing ops | AUD 1,029 | AUD 853 | AUD 980 |
| Other | AUD (1) | ||
| Net income | AUD 1,029 | AUD 852 | AUD 980 |
| Net income available to common shareholders | AUD 1,029 | AUD 852 | AUD 980 |
| Earnings per share | |||
| Basic | AUD 0 | AUD 0 | AUD 1 |
| Diluted | AUD 0 | AUD 0 | AUD 1 |
| Weighted average shares outstanding | |||
| Basic | AUD 2,083 | AUD 1,853 | AUD 1,756 |
| Diluted | AUD 2,083 | AUD 1,853 | AUD 1,756 |
| EBITDA | AUD 2,932 | AUD 2,798 | AUD 3,149 |
The percentage of the sales is computed for each of the items of the income statement as below.
| QANTAS AIRWAYS LTD INCOME STATEMENT | |||
| Fiscal year ends in June. AUD in millions except per share data. | 2016 | 2017 | 2018 |
| Revenue | 100% | 100% | 100% |
| Cost of revenue | 42% | 41% | 41% |
| Gross profit | 58% | 59% | 59% |
| Operating expenses | 0% | 0% | 0% |
| Sales, General and administrative | 34% | 34% | 33% |
| Depreciation and amortization | 8% | 9% | 9% |
| Restructuring, merger and acquisition | 0% | 0% | 0% |
| Other operating expenses | 9% | 9% | 10% |
| Total operating expenses | 51% | 52% | 52% |
| Operating income | 7% | 7% | 7% |
| Interest Expense | 2% | 1% | 1% |
| Other income (expense) | 4% | 2% | 3% |
| Income before income taxes | 9% | 8% | 8% |
| Provision for income taxes | 3% | 2% | 2% |
| Net income from continuing ops | 7% | 5% | 6% |
| Other | 0% | 0% | 0% |
| Net income | 7% | 5% | 6% |
| Net income available to common shareholders | 7% | 5% | 6% |
The growth rate or change in the items of the income statement are calculated, and a mean amount is computed as follows.
| Revenue | 100% | 100% | 100% | Mean |
| Cost of revenue | 42% | 41% | 41% | 41% |
| Gross profit | 58% | 59% | 59% | 59% |
| Operating expenses | 0% | 0% | 0% | 0% |
| Sales, General and administrative | 34% | 34% | 33% | 34% |
| Depreciation and amortization | 8% | 9% | 9% | 9% |
| Restructuring, merger and acquisition | 0% | 0% | 0% | 0% |
| Other operating expenses | 9% | 9% | 10% | 9% |
| Total operating expenses | 51% | 52% | 52% | 52% |
| Operating income | 7% | 7% | 7% | 7% |
| Interest Expense | 2% | 1% | 1% | 2% |
| Other income (expense) | 4% | 2% | 3% | 3% |
| Income before income taxes | 9% | 8% | 8% | 8% |
| Provision for income taxes | 3% | 2% | 2% | 2% |
The revenu
