Assignment 1 Forecasting
Fall 2012
Objective
Finance is based on forecasted “benefits” and “costs” which generally take the form of cash flows. The objective of this assignment is for you to apply techniques of forecasting to obtain the cash flows necessary to analyze a project using capital budgeting (finance) techniques.
You must develop your forecast in a new, clean completely blank Excel spreadsheet - do not be tempted to try to brute force this projection into the class exercise - it will not work. Do not copy and paste the class exercise into your clean spreadsheet. You are expected to do this forecast from scratch. You may not use any templates from any source including any you find on the internet.
Deliverable
You are to develop a fully flexible 5 year forecast. Your deliverable is an Excel Workbook that contains your forecasts.
To copy the historical financials from this document to an Excel spreadsheet, highlight the entire chart with the Income Statements and Balance Sheets, then copy the chart and paste it into a clean Excel Workbook. You should check to make sure that all values have been transferred as numbers (sometimes values transfer as text although typically that is only with “0”).
Case
You have been given the historical Financial Statements for a start-up company. They feel that they have reached a point of stability in year 2 and that this year will be a good indicator of their future performance. They have asked you to provide them with a spreadsheet that projects the Income Statement, Balance Sheet and Cash Flow for the next 5 years.
You must use the ratio analysis approach that we used in class. You have decided to use the company's historical ratios from the company’s most recent year of operations to project the results (not industry benchmarks). You need to set up a schedule that contains the calculations of your ratios and your projected financial statements must link to the ratios in that schedule. You should include a line for each account that you need to forecast in your schedule. If you are leaving the balance the same as the prior year, you do ont have to have a line in your schedule for that account. There should be no values hard-coded into your cell formulas - only cell references (a fully flexible model).
Your cash balance must be calculated from your Cash Flow projection and your Balance Sheet must balance -there will be a 10 point deduction if it does not.
You will have to construct the Cash Flow Statement from scratch. Remember to include all appropriate Balance Sheet accounts.
Some additional Information: * The company has set a strategic sales growth rate of 7%. * Note that only Net PP&E is given (not Gross PP&E and Accumulated Depreciation) and depreciation expense is not given. This is not unusual when pulling financial statements of other companies. The impact is that when calculating cashflows, you will not add depreciation expense into operating cashflow and will have to use Net PP&E in Investing cash flows. * You should not forecast any cashflows for accounts related to Financing Activities. * All expenses other than taxes are forecasted as common size. * You should average all balance sheet number in turnover ratios and in your forecast. * Other current assets, Prepaid Expenses, and Accrued Expenses should be forecasted using the account’s year over year growth rate * Income Tax Payable is the same as the Income Tax Expense (Note this in your schedule but you can link directly to Income Tax Expense in the Income Statement). * Goodwill and Investments should be