Financial Modelling ExerciseHistorical BackgroundXYZ company started its operations in the year $2011.$ The company operates a rent$-$a$-$car business. The company presently owns five cars in the city of Riyadh. They are currently charging SAR $1\mathrm{.}0/$ km$.$ Average historical mileage for each car is $10,000$ km for each month. Direct running costs are approximately SAR $0\mathrm{.}35/$ km$.$ Each car is expected to last for three years with a cost of SAR $100,000$ for each car, and at the end of three years it will have a residual value of SAR $10,000.$The company does not own any other fixed assets and is running its business in one branch. The company has rented a furnished office with a yearly rent of SAR $100,000.$ The company also employs two staff members in their office with a total monthly cost of SAR $5,000.$The company has $30\%$ of its business with corporate client with whom the company has agreed for $30$ days of credit. The remaining sales are to regular cash paying customers.The company also maintains inventories at $2\%$ of the carrying values of the cars fleet.The company enjoys credit period of $15$ days on its direct running costs.Other liabilities only represent end of service benefits which are accrued at $2\mathrm{.}5\%$ of the yearly salary costs.The payout ratio for the year $2011$ has been $50\%$ of the net income.Assumptions for the Financial Forecast The revenue is expected to grow at $10\%$ on yearly basis. The growth is expected to come from increase in the charge$-$out rate The direct costs are expected to increase by SAR $0\mathrm{.}025/$ KM every year Salary costs are expected to increase by $3\%$ each year The company has fixed rent cost for three years after three years the rent cost would increase by $15\%$ which will remain fixed until the end of the final year of the financial forecast The company will replace its fleet every three years Each year the company will also make an addition of one car to its fleet. The addition is assumed to take effect from $1$st January of every year Any shortfall in cash will be financed by the bank loans with a yearly cost of $5\%$ Other working capital and depreciation expense assumptions will remain unchanged as provided above in the historical background All other assumptions are same as given under the historical background Please assume $365$ in a calendar yearRequirementsThe financial statements for the year $2011$ are attached in Microsoft Excel. Please prepare the forecast of financial statement from the year $2012$ to $2018$ illustrating the workings logically$.$Once the financial forecast is complete, please also calculate and comment on financial ratios reflecting the financial performance of the company. The financial ratios should cover various aspects like growth, profitability, activity$/$turnover$,$ liquidity, gearing, returns, etc.Financial ModellingCandidate may like to follow this approach: $($However$,$ they are free to use any other methodology if they wish to$)1$ Prepare a schedule for number of cars from $2012$ to $2018$ with clear mentioning of the additions and the disposals of the cars in each year$2$ Prepare a detailed depreciation expense schedule based on the estimated useful life. The candidate should base this schedule on number of cars purchased in each year$3$ Prepare a schedule for fixed assets costs, accumulated depreciation and net book values$4$ Prepare a schedule for revenue which should include the number of cars, total mileage, charge$-$out rate and revenues$5$ Prepare a similar schedule as the revenue schedule for determination for direct costs$6$ Prepare a schedule or directly input in the income statement other cost items$7$ Prepare a schedule or directly input the numbers in the balance sheet for calculation of working capital elements$8$ Populate the income statement and balance sheet from the above information$9$ Calculate the retained earnings in the balance sheet with reference to its underlying elements$10$ Determine if the company need any external financing and balance the balance sheet with either the cash or the bank loan$11$ Prepare the cash flow statement, and ensure that the closing balance in the cash flow statement matches with the closing balance in the balance sheetFinancial Statements $2011$Income Statement Revenues$600,000$ Direct costs$(210,000)$Depreciation$(150,000)$Gross profit$240,000$ Office rent$(100,000)$Salaries$(60,000)$Other income Financial charges Net income$80,000$ Balance Sheet Current Assets Cash$178,336$ Accounts receivables$14,795$ Inventories$7,000$ Total current assets$200,130$ Operating Fixed Assets Fixed assets$350,000$ Total assets$550,130$ Bank loan$-$ Accounts payables$8,630$ Other liabilities$1,500$ Total liabilities$10,130$ Equity Capital$500,000$ Retained earnings$40,000$ Total equity$540,000$ Total liabilities & equity$550,130-$ Cash Flow Statement Operating cash flows Net income$80,000$ Depreciation$150,000$ Change in accounts receivables$(14,795)$Change in inventories$(7,000)$Change in accounts payables$8,630$ Change in other liabilities$1,500$ Operating cash flows$218,336$ Investing cash flows Fixed assets$(500,000)$Disposals of fixed assets Investing cash flows$(500,000)$Financing cash flows Bank loans$-$ Capital$500,000$ Cash dividend$(40,000)$Financing cash flows$460,000$ Net change in cash$178,336$ Opening balance$-$ Closing balance$178,336$