Financial Modelling Exercise Historical Background XYZ 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.0 / km. Average historical mileage for each car is 10,000 km for each month. Direct running costs are approximately SAR 0.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.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.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 1st 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 year Requirements The 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 Modelling Candidate 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 sheet