Course: Financial Modeling Perform on Excel kindly!!

Expected life of new business is 15 years. US$ Break up of initial investment: Capital expenditure License Working capital (all as current assets) 90,000,000 50,000,000 5,000,000 All of initial investment is contributed by Equity. Revenue: 500,000 in year 1 and is expected to grow by 10% during first 5 years and by 6% thereafter, however, volume cannot exceed beyond 900,000 per year. Average starting price per ticket is set as $55 with 5% increment on annual basis. . . . Direct and operating costs for running the business are as follows: Direct costs -20% of revenue. Royalty - Minimum of $2,500,000 or 5% of revenue. General and administration - $1,000,000 in first year with 5% yearly increase. Marketing - $3.0 per ticket with 5% yearly increase. Routine operation & maintenance - 12% of revenues. Depreciation - Based on 10 years property class. License amortization - 15 years. Tax - 30%, chargeable to profits only. . . Assets and liabilities: . Current assets 25% of revenue. Current liabilities -15% of first five cost elements stated above. All of the current assets and liabilities will be reversed to zero in the final year of the project. Dividends are paid @90% of net cashflow from operating activities as per cashflow statement. . Required: An integrated financial statements model to be developed, with separate areas earmarked for inputs, working model and outputs. Output which is the information to BoD should include; a. Financial evaluation indictors comprised of NPV, IRR and payback. The company uses a discount rate of 8%. b. Bar graphs for revenue development, ticket volumes, net profit and cumulative cashflow. Expected life of new business is 15 years. US$ Break up of initial investment: Capital expenditure License Working capital (all as current assets) 90,000,000 50,000,000 5,000,000 All of initial investment is contributed by Equity. Revenue: 500,000 in year 1 and is expected to grow by 10% during first 5 years and by 6% thereafter, however, volume cannot exceed beyond 900,000 per year. Average starting price per ticket is set as $55 with 5% increment on annual basis. . . . Direct and operating costs for running the business are as follows: Direct costs -20% of revenue. Royalty - Minimum of $2,500,000 or 5% of revenue. General and administration - $1,000,000 in first year with 5% yearly increase. Marketing - $3.0 per ticket with 5% yearly increase. Routine operation & maintenance - 12% of revenues. Depreciation - Based on 10 years property class. License amortization - 15 years. Tax - 30%, chargeable to profits only. . . Assets and liabilities: . Current assets 25% of revenue. Current liabilities -15% of first five cost elements stated above. All of the current assets and liabilities will be reversed to zero in the final year of the project. Dividends are paid @90% of net cashflow from operating activities as per cashflow statement. . Required: An integrated financial statements model to be developed, with separate areas earmarked for inputs, working model and outputs. Output which is the information to BoD should include; a. Financial evaluation indictors comprised of NPV, IRR and payback. The company uses a discount rate of 8%. b. Bar graphs for revenue development, ticket volumes, net profit and cumulative cashflow